Runnable data-engineering patterns, gotchas and utilities - plus the occasional long-form design paper. Most entries are small enough to read in a sitting and self-contained enough to clone and run in seconds.
Some folders are working utilities I've built and used. Most are minimal, runnable demonstrations of a single idea - a SQL pattern, a Spark internal, a shell habit, a cloud technique - the kind of thing that's easy to assert in a post and far more convincing when you can run it and watch it happen. A few are longer design papers, where the problem is too big for a snippet and the reasoning is the deliverable; those still ship runnable SQL alongside the prose.
Each folder stands alone: its own README, its own sample data or schema, its own captured output. Nothing here depends on anything else here.
The repo grows alongside an ongoing data-engineering writing series - each piece that includes code drops its runnable snippet here, and longer design work lands as a paper. It spans four areas:
- SQL & data modelling - the ANSI core and the dialect differences (Oracle | Teradata | Snowflake | Delta), window functions, dimensional modelling
- Python & PySpark - pipeline craft, Spark internals, testing
- Shell, orchestration & platform - the glue: bash, scheduling, Hadoop, HDFS
- Cloud data platforms - AWS, Snowflake and Databricks
Most demos run on nothing but bash (shell demos) or Python 3 + a local
.venv (Python demos) - no account, no cluster. Each Python demo's run.sh
creates its own .venv on first run and installs into it, so nothing lands in
your system Python. A few cloud techniques genuinely need a Snowflake /
Databricks / AWS account (and PySpark demos need Java); those ship the real code,
sample data and captured real output, clearly labelled, so you can read
exactly what happens without one. No demo here fakes a result.
New to the repo? See SETUP.md for a tested WSL Ubuntu 24.04 +
IntelliJ walkthrough.
Each folder has its own README with the exact command to run it (most are bash run.sh).
| Folder | What it does |
|---|---|
metadata-driven-warehouse-extraction/ |
A 34-page white paper on getting a whole data warehouse out in files - roughly a thousand tables, tens of terabytes, a ten-hour nightly window. Covers the metadata control schema, watermarks that can't skip data, restart and reconciliation. Ships the six-table schema and the operational queries as runnable PostgreSQL. |
| Folder | What it does |
|---|---|
merge-four-engines/ |
The same idempotent upsert in Oracle, Teradata, Snowflake and Delta - plus a runnable proof of the one condition idempotency depends on, and what happens when you break it. |
window-functions-mental-model/ |
The picture behind OVER / PARTITION BY / ORDER BY - why a window keeps every row where GROUP BY collapses them, and the frame trap that turns a group total into a running total the moment you add ORDER BY. |
qualify-clause/ |
Filter a window function directly with QUALIFY instead of wrapping it in a subquery. Shows the WHERE that's rejected, the old subquery, and the QUALIFY that replaces it - plus which engines support it. |
row-number-dedup/ |
The most-typed query in data engineering: latest row per key. Shows the tie-break people miss - two rows with the same timestamp make the kept row arbitrary - and the fix, plus ROW_NUMBER vs RANK. |
snowflake-micro-partitions/ |
Why Snowflake has no indexes: micro-partitions + min/max metadata + pruning. Same 20M rows loaded random vs sorted by date, showing partitions_scanned/total and clustering depth. Needs a Snowflake account; run.sh captures the output automatically. |
null-three-valued-logic/ |
NULL is not a value: why NOT IN against a subquery containing a NULL silently returns zero rows - the bug that quietly breaks reconciliations. Walks the three-valued logic (TRUE/FALSE/UNKNOWN), shows the NOT IN predicate collapsing to UNKNOWN, and the NULL-safe fix (NOT EXISTS). Runnable DuckDB, no account. |
snowflake-streams-tasks/ |
A self-healing CDC pipeline in Snowflake. A stream is a bookmark into Time Travel, not a durable queue - miss the retention window and it goes stale and loses unconsumed changes (classic cause: the CDC task got suspended). The fix, as a pipeline that heals itself: a tiny cdc_watchdog_task that resumes the CDC task whenever it stops, so it's never down long enough for the stream to go stale. One command (bash run.sh) builds it, suspends the CDC task, and watches the watchdog bring it back. Serverless tasks; needs a Snowflake account + the Snowflake CLI (snow). |
anti-join-patterns/ |
Three ways to ask "what's missing" - NOT EXISTS, a LEFT JOIN ... IS NULL anti-join, and NOT IN - look equivalent until the subquery contains a NULL. Then NOT IN silently returns zero rows: id <> NULL is never TRUE under three-valued logic, so a single NULL makes the test fail for every row. NOT EXISTS and LEFT JOIN/IS NULL are NULL-safe (DuckDB rewrites NOT EXISTS to an anti-join). bash run.sh runs all four on the same data. Runs anywhere (auto-installs duckdb), idempotent. |
| Folder | What it does |
|---|---|
config-driven-python/ |
Keep the settings that change between test and live out of your code, so the same pipeline runs anywhere. Shows the four places a setting can come from - and which one wins. |
databricks-auto-loader/ |
Why listing a bucket to find new files stops scaling, and how Auto Loader's checkpoint fixes it. A runnable open-source proof that only new files are processed each run, plus the real cloudFiles code from a Databricks build. |
narrow-vs-wide-transformations/ |
The one Spark idea that explains your runtimes: narrow transformations stay in a partition, wide ones shuffle. Shows which ops shuffle via real explain() plans, plus the coalesce vs repartition trap. |
broadcast-joins/ |
A regular join shuffles both tables; broadcast the small side and the big fact never moves. Reads the physical plan before (sort-merge, two Exchanges) and after (broadcast hash join, one), and covers the 10MB/8GB limits and the driver-collect cost. |
data-skew-salting/ |
Conditional salting: fix a skewed join by salting ONLY the hot key, not the whole table. ACME_CORP holds 1,600,000 of 2,000,000 orders. Normal salting fans the dimension out to 64,016 rows; conditional salting keeps it at 4,016 - same busiest-task drop (1,600,000 -> 100,000) for ~16x less shuffle. Skewed keys found dynamically. |
python-structured-logging/ |
print() is text; structured logs are fields. The same try/except logged as JSON - a run_id on every line plus record_id and step - so you can filter by run, count failures by step, and load the logs into Splunk or a SQL table. Shows exactly how one log.error(extra={...}) call maps to a JSON line (and where ts and run_id come from). Standard library only, no pip. |
dlt-scd2-pipeline/ |
Databricks AUTO CDC (formerly APPLY CHANGES) turns SCD Type 2 into a declaration - but the one decision that makes or breaks it is sequence_by. It must reflect when the change happened at the SOURCE (its updated timestamp), not when your pipeline ingested the row - because delivery order is not source order, so an out-of-order change wins current with a stale value. Same feed builds two dimensions - naive (sequenced by ingestion time) vs guarded (sequenced by the order's own order_updated_at) - plus the guardrails: @dlt.expect_all_or_drop on the feed, track_history_except_column_list, and apply_as_deletes. One-click DLT bundle with captured output. |
spark-lazy-evaluation/ |
Why your Spark job "runs in 0.2s" when nothing ran: transformations (select, withColumn, filter, join, groupBy) are lazy and only build a plan; the work starts at an action (count, collect, write). The costly gotcha - without .cache(), every action recomputes the whole lineage. A UDF counter proves it (0 -> 5 -> 10 uncached vs 5 -> 5 cached), plus a deferred-error demo. Runs anywhere (auto-installs pyspark, local Spark), idempotent. |
| Folder | What it does |
|---|---|
daily-job-status-automation/ |
A pure-shell tool that queries DataStage master sequences and emails a RAG colour-coded daily status report. Runs in a self-contained demo mode out of the box. |
set-euo-pipefail/ |
The same load script twice - one exits 0 after three failures and reports success, the other doesn't. Three characters of insurance. |
event-driven-ingestion-aws/ |
Why polling a bucket on a timer (or keeping a cluster warm) costs you latency and idle compute - and the event-driven fix: S3 -> EventBridge -> Step Functions -> EMR Serverless. A runnable inotify proof of the idea, plus the real AWS wiring. |
cron-environment/ |
Why a script that works by hand dies at 3am under cron: a bare environment and minimal PATH, none of your ~/.bashrc. Reproduces it with env -i and shows the fix. |
s3-prefix-partition-design/ |
How folder layout fixes scan cost before you write a query. Writes the same rows partitioned by dt vs flat, shows PartitionFilters vs DataFilters and ~10x fewer bytes scanned. Retires the old prefix-for-throughput myth. |
awk-one-liners/ |
Ten awk one-liners every data engineer should own - field extraction with a condition, conditional sums, group-by count/sum, dedup without sorting, averages, min/max, deriving a column, and a two-file lookup/join. awk streams line by line, so the same one-liners run on an 8GB file in constant memory (group-bys hold only the distinct keys); bash run.sh stress 8 proves it. Pure shell, no account. |
databricks-asset-bundles/ |
The Asset Bundle gotcha: mode: development prepends [dev you] to a pipeline's NAME - so two developers' pipelines look isolated - but it never touches the pipeline's write TARGET (catalog/schema), so both publish to the same schema and overwrite each other's tables. The one-line fix: make the target schema per-developer (${workspace.current_user.short_name}). Self-contained + idempotent: bash run.sh bootstraps a shared catalog, deploys and runs the pipeline, and verifies bronze/silver land in your own schema. |
quote-your-variables/ |
The space in a filename that broke prod: an unquoted $var is word-split on IFS (space/tab/newline) and then glob-expanded, so rm $file on Q3 report.csv runs rm Q3 report.csv and deletes the wrong files. Fix: quote every expansion ("$var", "$@", "${arr[@]}") and add set -u. bash run.sh fires four traps - a space in a filename, $@ vs "$@", a value containing a glob, and set -u catching a typo - each buggy then fixed, safe in a throwaway temp dir. Runs anywhere, idempotent. |
unity-catalog-governance/ |
Governance you build in, not bolt on. Skip it during development and it becomes debt that comes due at an audit or incident: access sprawl to unwind, no lineage history, audit gaps - "a migration with extra politics." On Unity Catalog it's just the structure: a three-level namespace (catalog.schema.table), least-privilege grants that inherit (GRANT SELECT on a schema covers every current and future table), clear ownership, and automatic lineage + audit. bash run.sh self-provisions a warehouse and builds a governed schema from scratch (grants, ownership, lineage, revoke); bash run.sh teardown drops it all. Idempotent. |
New folders land as the series continues.
Two shapes, depending on what the folder is.
Runnable demos - committed inputs in config/, the captured output in
output/, and anything a run generates in data/, which is git-ignored and
disposable (rm -rf data resets any demo). Nothing a demo needs is ever written
to data/.
Document-led folders - the paper in docs/ (Markdown, PDF and Word), its
diagrams in images/, and any runnable schema in sql/.
Three ideas run through everything here.
Runnable proof over assertion. A claim about data engineering is cheap; a folder you can run and watch is not. Every demo ships the code, its sample data and its captured output - so the point is demonstrated, not just described.
Fundamentals outlast the stack. Engines change every few years; the reasons behind them - set-based thinking, avoiding the shuffle, idempotency, reading less data - don't. The focus here is the transferable idea, not the vendor button. And when a problem's requirements and boundaries are clearly known, a small, well-bounded tool beats a complex one every time.
Show what breaks, not just the happy path. The useful part is usually the failure: the duplicate key that quietly corrupts a MERGE, the script that exits 0 after failing. Several demos deliberately break, because the gotcha is the lesson.
Pavan Kumar Tummala - Senior Data Engineering professional, Melbourne LinkedIn | GitHub
Released under the MIT License.