Build something you can put on your CV.

Capstone: ingest script end to end

Bring the track together in one small ingest job: fetch API pages, validate each row, log accepted and rejected counts, and return clean order IDs.

Capstone: model a star from orders

Declare grain, aggregate payments first, and project a fact table from source headers.

Capstone: messy data to analysis-ready

Duplicate keys, mixed types, gnarly dates, nulls: bronze to silver in pandas, same instinct as Spark medallion.

LakeBench SQL: filters and joins

First LakeBench SQL drills on employees, departments, and customers. Filters, aggregates, and beginner joins.

• Filter Engineering salariesFree
• Average salary by departmentFree
• Employee department namesFree

LakeBench Python: core drills

First LakeBench Python drills: flatten lists, hash maps, merge sorted lists, and two sum.

• Flatten Nested ListsFree
• First Occurrence MapFree
• Two SumFree

LakeBench Python: arrays and windows

Hashing, two pointers, and sliding windows from the LakeBench Python 70 set.

• Contains DuplicateFree
• Valid PalindromeLocked
• Longest Substring No RepeatLocked

Clickstream to silver

Dedup replayed events, then sessionize them. A beginner production ticket plus a LakeBench SQL session drill.

• Incremental clickstream dedupFree
• Sessionize events with 30-minute gapsFree

Pandas wrangling sprint

Vectorized cleaning, groupby GMV, and melt/pivot reshapes on order frames. Five pandas drills end to end.

• Clean messy text columnsFree
• Share of group and above-group-meanLocked
• Wide to long with quarter-over-quarter growthLocked
• Keep the latest row per keyFree
• Forward-fill per deviceFree

Capstone part 2: incremental MERGE

Keep bronze → silver → gold, then apply a second micro-batch with a Delta-style latest-wins MERGE.

Medallion pipeline project

A medallion pipeline moves from raw bronze data to cleaned silver data and then to a business-ready gold result. Build the 3 steps and check the grain at each one.

Capstone: a reliable night job

Walk extract → load in order, skip duplicate keys, retry a flaky extract. Still a simulation. Still the right habits.

Capstone: staging, mart, test

Layer a staging CTE, project a paid-orders mart, and return unique-test violators on order_id (zero in this warehouse).

Capstone: a publish suite

Wire not-null, unique, and row-count on df_orders. Print a summary dict of booleans. Project the frame you would actually publish.

LakeBench SQL: windows and CTEs

Running totals, ranks, sessionization, and multi-CTE business questions from the LakeBench SQL set.

• Running total by customerLocked
• Sessionize events with 30-minute gapsFree
• Customers above average spendLocked

LakeBench Python: trees and graphs

Binary trees, BFS/DFS islands, and course schedule from the LakeBench Python set.

• Invert Binary TreeLocked
• Number of IslandsLocked
• Course ScheduleLocked

Production on-call pack

Silver dupes, FinOps scan, driver OOM and a DAG ordering bug. Four tickets that mirror a bad Friday deploy.

• Broken DAG: silver_events unique test redFree
• FinOps: dashboard GMV is scanning the lakeLocked
• Broken job: driver OOM from .collect()Locked
• Order DAG tasks so every dependency runs firstLocked

PySpark memory and skew

Fix a collect() out-of-memory crash and a broken skew fix, then practise broadcast joins and partition tuning on simulated Spark.

• Broken job: driver OOM from .collect()Locked
• Broken gold: salted country rollup dropped bucketsLocked
• Broadcast JoinFree
• Repartition vs CoalesceLocked

Full interview loop

One LakeBench SQL filter drill and one Python hash map drill, back to back. A short warm-up for a DE screen.

• Filter Engineering salariesFree
• Flatten Nested ListsFree

Data quality incident

A broken pipeline ticket, a duplicate grain test and a daily volume gate. Three data quality checks in one sitting.

• Broken DAG: silver_events unique test redFree
• dbt test: unique on the order_items grainLocked
• Quality gate: days with order volume outside boundsLocked

Capstone: deploy ingest to the cloud

A numbered GCP free-tier checklist: bucket, bronze file, BigQuery, a query, a README screenshot. The last mile is still outside this tab.

Checklist. The cloud and daemon stay on your machine.

Capstone: late messages

3 orders on FakeKafkaTopic, one behind the watermark. Keep the on-time keys and return the offsets you would commit.

Capstone: ship checklist

git commit, docker build, ci test, terraform plan, deploy. The grader checks the order. The daemon, cluster, and cloud stay on your machine.

Checklist. The cloud and daemon stay on your machine.

SCD Type 2 and CDC apply

Merge CDC batches into a slowly changing dimension without overlapping windows. Production ticket plus LakeBench SCD drill.

• SCD Type 2: apply city-change CDCLocked
• Department as of 2025-06-01Free

FinOps and EXPLAIN literacy

Read EXPLAIN output, cut bytes scanned, and pass the FinOps score bar on a GMV aggregation ticket.

• FinOps: dashboard GMV is scanning the lakeLocked

Capstone: full pipeline design

One path from click to dashboard at 500M events/day. Every hop points at a lesson you already did. The whiteboard is the work.