Homework 11 — Cloud Warehouse Extensions

Task 1 — Add a top_repos_weekly materialised aggregate

Extend the adapter you ran in the lab with a second materialised aggregate: top_repos_weekly(iso_week, repo, n) showing the top 50 repositories by event count per ISO week.

Implement this on your chosen track:

• Track A (Snowflake): add a CREATE OR REPLACE VIEW top_repos_weekly AS … statement that uses QUALIFY row_number() OVER (PARTITION BY iso_week ORDER BY n DESC) <= 50 to limit to 50 rows per week.
• Track B (DuckDB local): add a CREATE OR REPLACE TABLE top_repos_weekly AS … block to warehouse/duckdb_local.py (or run it interactively) using a CTE with row_number() and a WHERE rk <= 50 filter.

Self-check: query the view or table for the two most-recent distinct ISO weeks in your data. You should get exactly 100 rows (50 per week). If you get more, the PARTITION BY clause is missing or wrong.

-- Track A (Snowflake / BigQuery) check:
SELECT count(*) FROM (
  SELECT DISTINCT iso_week FROM top_repos_weekly ORDER BY iso_week DESC LIMIT 2
) w
JOIN top_repos_weekly t ON t.iso_week = w.iso_week;
-- Expected: 100

# Track B (DuckDB local) check:
import duckdb
con = duckdb.connect("warehouse/duckdb_local.duckdb")
weeks = [r[0] for r in con.execute(
    "SELECT DISTINCT iso_week FROM top_repos_weekly ORDER BY iso_week DESC LIMIT 2"
).fetchall()]
for w in weeks:
    n = con.execute(
        "SELECT count(*) FROM top_repos_weekly WHERE iso_week = ?", [w]
    ).fetchone()[0]
    print(f"{w}: {n} rows")
# Expected: 50 rows per week

Task 2 — Cost-model estimate

Write a file data-engineering/sample-pipeline/warehouse/cost_estimate.md that walks through the cost of serving 1000 daily queries against daily_counts. Show two estimates side-by-side: external-table (1000 × Iceberg scan cost) vs materialised (1000 × warehouse-native scan cost, plus the one-time materialisation cost per day).

Base your estimates on the actual benchmark numbers from materialize_vs_query.py that you recorded during the lab. For Track A, use Snowflake’s published X-Small warehouse rate ($2.00 / credit, 1 credit/hour) or BigQuery’s on-demand rate ($5 / TB scanned). For Track B, show the DuckDB timings directly without a dollar figure (DuckDB is free; substitute CPU-seconds as the cost unit).

The file should contain:

1. Your benchmark numbers (external scan time, materialised scan time, speedup).
2. The pricing assumption(s) you used.
3. The two cost estimates in the same unit (dollars/day for Track A; CPU-seconds/day for Track B).
4. A summary line in the form: materialised is X× cheaper for this access pattern.

Self-check: the file exists at the expected path, contains all four elements listed above, and the summary line clearly states the materialised advantage. A peer should be able to reproduce your estimate by plugging your benchmark numbers into your formula.

Task 3 — Refresh-vs-staleness analysis

Materialised views have to be refreshed; staler data costs less. Write a file data-engineering/sample-pipeline/warehouse/refresh_policy.md that proposes and justifies a refresh cadence for daily_counts.

The gharchive source publishes one JSON dump per hour with roughly a 30-minute lag. Your daily_counts aggregate rolls up to the day. Use these facts to reason about the right cadence.

Your policy document must reference at least three of the following factors:

• Data freshness SLO (how stale can daily_counts be before downstream consumers notice?)
• Warehouse credit cost per refresh run.
• Source publish cadence (gharchive: one file per hour, ~30-minute lag).
• Downstream consumer expectations (e.g., the analytics dashboard refreshes at 09:00 UTC).
• On-call cost of breakage (how many minutes to recover if a refresh fails?).

Self-check: the file lives at data-engineering/sample-pipeline/warehouse/refresh_policy.md, proposes a specific cadence (e.g., “every 6 hours”), and explicitly references at least three of the five factors listed above. The cadence is justified with a sentence connecting it to at least one quantitative input (e.g., gharchive’s hourly publish rate or a specific SLO time window).