Lesson 11 Cloud Warehouse
Theme:

Solutions — Homework 11

Lesson 11 · Data Engineering with Python · Cloud Warehouse

Task 1 — top_repos_weekly materialised aggregate

Track A — Snowflake

CREATE OR REPLACE VIEW top_repos_weekly AS
SELECT
  date_trunc('week', event_date) AS iso_week,
  repo.name AS repo,
  count(*) AS n
FROM events
GROUP BY iso_week, repo
QUALIFY row_number() OVER (PARTITION BY iso_week ORDER BY n DESC) <= 50;

The QUALIFY clause is Snowflake’s post-aggregation window filter — it runs after GROUP BY, making it the idiomatic way to rank within a group without a subquery.

Track A — BigQuery

CREATE OR REPLACE VIEW my_dataset.top_repos_weekly AS
SELECT
  DATE_TRUNC(event_date, WEEK) AS iso_week,
  repo AS repo,
  count(*) AS n
FROM my_dataset.events
GROUP BY iso_week, repo
QUALIFY ROW_NUMBER() OVER (PARTITION BY iso_week ORDER BY count(*) DESC) <= 50;

BigQuery supports QUALIFY as well; DATE_TRUNC syntax differs slightly.

Track B — DuckDB local

import duckdb
con = duckdb.connect("warehouse/duckdb_local.duckdb")
con.execute("""
    CREATE OR REPLACE TABLE top_repos_weekly AS
    WITH ranked AS (
      SELECT
        date_trunc('week', event_date::DATE) AS iso_week,
        actor AS repo,
        count(*) AS n,
        row_number() OVER (
          PARTITION BY date_trunc('week', event_date::DATE)
          ORDER BY count(*) DESC
        ) AS rk
      FROM events
      GROUP BY iso_week, actor
    )
    SELECT iso_week, repo, n FROM ranked WHERE rk <= 50;
""")

DuckDB does not have QUALIFY (as of 0.10), so the equivalent is a CTE with row_number() followed by a WHERE rk <= 50 filter.

Common mistake: forgetting PARTITION BY iso_week on the row_number() window. Without it, row_number() assigns ranks globally across all weeks — you get the top-50 repositories across all time instead of top-50 per week. The self-check (100 rows for two weeks) catches this immediately.

Task 2 — Cost-model estimate

Sample cost_estimate.md (Track A — Snowflake X-Small):

# daily_counts serving cost — 1000 queries/day

## Benchmark (from materialize_vs_query.py, Track A — Snowflake X-Small)
  external scan:      0.84s per query, ~12 MB scanned
  materialised scan:  0.013s per query, ~0.05 MB scanned

## Pricing assumptions
  Snowflake X-Small:  1 credit/hour = $2.00/hour = $0.000556/sec
  S3 GET:             $0.0004 / 1000 GETs (negligible at this scale)
  BigQuery on-demand: $5/TB scanned (alternative pricing row below)

## Snowflake — external path
  1000 queries × 0.84s × $0.000556/s = $0.47/day (compute)
  S3 metadata GETs: negligible
  Total: ~$0.47/day = ~$14/month

## Snowflake — materialised path
  1000 queries × 0.013s × $0.000556/s = $0.007/day (compute)
  1× CTAS refresh/day × ~1s            = $0.0006/day
  Storage (~10 MB materialised table):  negligible
  Total: ~$0.008/day = ~$0.25/month

## Summary
materialised is ~60× cheaper for this access pattern.

Track B equivalent (CPU-seconds as the cost unit):

# daily_counts serving cost — 1000 queries/day (Track B — DuckDB)

## Benchmark
  external scan:      0.31s per query (local Iceberg + DuckDB)
  materialised scan:  0.004s per query (warehouse/duckdb_local.duckdb)

## Cost unit: CPU-seconds (DuckDB is free)
  external path: 1000 × 0.31s  = 310 CPU-seconds/day
  materialised:  1000 × 0.004s = 4 CPU-seconds/day + ~0.3s refresh

## Summary
materialised is ~77× cheaper in CPU-seconds for this access pattern.

Common mistake: comparing only compute cost and forgetting the storage delta. For small aggregates like daily_counts (~10 MB) the warehouse storage cost is negligible. For wide materialisations (e.g., a full-row copy of events) storage cost can flip the trade-off — always include the storage line even if it is “negligible” so a reviewer can verify the assumption.

Task 3 — Refresh-vs-staleness analysis

Sample refresh_policy.md:

# daily_counts refresh policy

- **Cadence: refresh every 6 hours** (00:00, 06:00, 12:00, 18:00 UTC).
- **Source freshness:** gharchive publishes one file per hour with ~30-minute
  lag. The aggregate rolls up to the day, so sub-hourly refresh adds no
  new information.
- **Consumer SLO:** the analytics dashboard runs at 09:00 UTC and accepts
  data up to 9 hours stale. A 6-hour cadence guarantees at most 6 hours
  of staleness at dashboard load time.
- **Credit cost:** 4 refreshes/day × ~1s Snowflake X-Small CTAS =
  negligible compute cost vs the 24× cost of hourly refreshes.
- **On-call cost:** a missed refresh affects at most 6 hours of dashboard
  freshness. Recovery is a single command:
  python -m warehouse.snowflake_adapter
  (or warehouse.duckdb_local for Track B) — MTTR under 5 minutes.

Common mistake: defaulting to hourly refresh without checking the consumer SLO. Hourly is the intuitive answer but is often 4–6× more expensive than what the actual downstream SLO requires. Always start from “how stale can this data be before someone notices?” and work backwards to the cadence — not the other way around.

Module 5 takes this stack into production: observability and SLAs on top of daily_counts, schema-evolution and lineage tracking that survives warehouse migrations, and cost governance — the next-level treatment of the cost model you just sketched in these homework exercises.