Lesson 07 SQL for Data Engineers
Theme:

Lab 07 — Analytical SQL against the GitHub events dataset

Lesson 07 · Data Engineering with Python · ~45 min lab

Goal: by the end of this lab you will have (a) loaded the Lesson 02 Parquet output into DuckDB without copying any files, (b) written five queries using CTEs, window functions, QUALIFY, and GROUPING SETS, and (c) read an EXPLAIN plan and identified partition pruning in action.

Prerequisites

Step 1 — Confirm DuckDB is installed

python -c "import duckdb; print(duckdb.__version__)"

Expected: a version string in the 1.x range.

The lab uses both the interactive DuckDB CLI for ad-hoc queries (Steps 2–8) and Python for the final script (Step 9).

Step 2 — Open DuckDB on the Parquet partitions

From the repo root, change into the pipeline directory and open an in-memory DuckDB session: 

cd data-engineering/sample-pipeline
duckdb

Inside the DuckDB prompt, create a view over the partitioned Parquet files:

CREATE OR REPLACE VIEW events AS
SELECT * FROM read_parquet('data/parquet/date=*/*.parquet', hive_partitioning => true);

SELECT COUNT(*) FROM events;

Expected: the row count matches the total written by the Lesson 02 lab. DuckDB scans the Parquet files in place — nothing is copied or imported. The partition column date (a string like '2024-01-01') and the payload columns type, actor, repo, and created_at are all available.

Quick schema check

Run DESCRIBE events; to confirm the column names and types before writing the queries below.

Step 3 — Query 1: top event types overall (warm-up)

SELECT type, COUNT(*) AS n
FROM events
GROUP BY type
ORDER BY n DESC
LIMIT 10;

Expected: PushEvent dominates, followed by CreateEvent, IssueCommentEvent, etc. This is the pandas-shaped query — the rest of the lab moves beyond it.

Step 4 — Query 2: first event per actor (window function + QUALIFY)

SELECT
  actor,
  type,
  created_at
FROM events
QUALIFY ROW_NUMBER() OVER (PARTITION BY actor ORDER BY created_at) = 1
ORDER BY created_at
LIMIT 20;

Expected: 20 rows, each a distinct actor's first event in the dataset. Notice you didn't need a CTE — QUALIFY filters on the window result directly.

✓ Self-check: every actor value in the result should be unique.

Step 5 — Query 3: top 3 event types per actor (CTE + window)

WITH ranked AS (
  SELECT
    actor,
    type,
    COUNT(*) AS n,
    ROW_NUMBER() OVER (PARTITION BY actor ORDER BY COUNT(*) DESC) AS rn
  FROM events
  GROUP BY actor, type
)
SELECT actor, type, n
FROM ranked
WHERE rn <= 3
ORDER BY actor, rn
LIMIT 30;

Expected: 30 rows, 3 per actor for the first 10 actors alphabetically.

✓ Self-check: rewrite the same logic using QUALIFY rn <= 3 instead of the CTE + WHERE (one-line change). Enable timing with .timer on and compare wall-clock times — they should be roughly equal.

Step 6 — Query 4: hourly events with a 7-row rolling window

WITH hourly AS (
  SELECT
    date_trunc('hour', created_at) AS hour,
    COUNT(*) AS n
  FROM events
  GROUP BY 1
)
SELECT
  hour,
  n,
  AVG(n) OVER (ORDER BY hour ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7h_avg
FROM hourly
ORDER BY hour;

Expected: every hour gets a 7-hour trailing average. The first six rows of the result will have a smaller effective window (only the rows so far contribute).

✓ Self-check: verify the first row's rolling_7h_avg equals n (window of 1). The explicit ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame is what makes this a rolling average instead of a running total.

Step 7 — Query 5: GROUPING SETS for a multi-level dashboard

SELECT
  date_trunc('day', created_at) AS day,
  type,
  COUNT(*) AS n,
  GROUPING(date_trunc('day', created_at)) AS day_rolled_up,
  GROUPING(type) AS type_rolled_up
FROM events
GROUP BY GROUPING SETS (
  (date_trunc('day', created_at), type),
  (date_trunc('day', created_at)),
  (type),
  ()
)
ORDER BY day, type;

Expected: one query produces detail rows (day + type), per-day subtotals (type IS NULL), per-type subtotals (day IS NULL), and one grand total (both NULL). The GROUPING() flags tell you which dimension was rolled up — useful for display logic ("show 'All days'" instead of NULL).

✓ Self-check: count the rows where day_rolled_up = 1 AND type_rolled_up = 1 — there should be exactly 1 (the grand total).

Step 8 — Read an EXPLAIN plan

EXPLAIN ANALYZE
SELECT actor, COUNT(*) AS n
FROM read_parquet('data/parquet/date=*/*.parquet', hive_partitioning => true)
WHERE date = '2024-01-15'
GROUP BY actor
ORDER BY n DESC
LIMIT 10;

Expected: the plan output shows a READ_PARQUET operator restricted to one partition (date=2024-01-15) — partition pruning is working. If you see a scan of all partitions, check that hive_partitioning => true is set in the read_parquet call; without it the engine doesn't know the directory structure is partitioned.

✓ Self-check: remove the WHERE clause and re-run. The plan now scans every partition. Compare the two wall-clock times — the pruned query should be noticeably faster on larger datasets.

Step 9 — Wrap it in a Python script

Move the most useful query (top-3 event types per actor) into a reusable Python script. Create data-engineering/sample-pipeline/transform/sql_demo.py: 

import duckdb

con = duckdb.connect()
con.execute("""
    CREATE OR REPLACE VIEW events AS
    SELECT * FROM read_parquet(
        'data/parquet/date=*/*.parquet',
        hive_partitioning => true
    );
""")

df = con.execute("""
    WITH ranked AS (
      SELECT
        actor,
        type,
        COUNT(*) AS n,
        ROW_NUMBER() OVER (PARTITION BY actor ORDER BY COUNT(*) DESC) AS rn
      FROM events
      GROUP BY actor, type
    )
    SELECT actor, type, n
    FROM ranked
    WHERE rn <= 3
    ORDER BY actor, rn
    LIMIT 30;
""").fetchdf()

print(df)

Run from data-engineering/sample-pipeline/:

python transform/sql_demo.py

Expected: a pandas DataFrame printed with 30 rows. DuckDB did all the analytical heavy lifting; Python only received the small result.

✓ Self-check: the boundary is clean — the Python script never loads the raw Parquet into memory. All filtering, grouping, and windowing happened inside DuckDB.

What you should leave with

Troubleshooting

Common errors