The Data Engineering Career Track

The build

Trace GAP Commerce's order flow end to end, then write 20 real business queries against the seed schema.

What breaks

A query returns fewer rows than expected. An implicit NULL exclusion in a WHERE clause silently drops matching rows.

NULL semantics don't behave like other comparisons. This is the first of many.

Interview angle

"Walk me through what happens when a customer places an order."

Does the candidate see the whole system, or only the table in front of them?

Deliverable

20 annotated SQL queries answering real GAP Commerce business questions.

sql/week01-business-queries.sql

The build

Answer real questions across the full customers -> orders -> order_items -> products chain.

What breaks

A LEFT JOIN's row count looks wrong. The join fans out against a one-to-many relationship, silently duplicating rows.

Join cardinality has to be reasoned about explicitly, not assumed.

Interview angle

"When would you use a LEFT JOIN instead of an INNER JOIN?"

Does the candidate understand join semantics, or just the syntax?

Deliverable

15 multi-table queries, each with a one-line join-choice justification.

sql/week02-relational-queries.sql

The build

Build daily sales, customer lifetime value, top products, and repeat-customer reports.

What breaks

A 'top order per customer' query returns ties it shouldn't. RANK() was used where ROW_NUMBER() was needed.

Window functions aren't interchangeable. The choice changes the result, not just the style.

Interview angle

"Find each customer's most recent order using SQL."

Fluency with window functions under a real constraint (exactly one row per customer).

Deliverable

Six analytics reports, spot-checked against the seed data.

sql/week03-analytics-reports.sql

The build

Diagnose and fix a query that regressed from 2 seconds to 40.

What breaks

The morning revenue report now takes 40 seconds. A missing index turns a sequential scan into the bottleneck.

"It returns the right answer" and "it's production-ready" are different claims.

Interview angle

"How would you investigate a slow query in production?"

A real diagnostic process (EXPLAIN ANALYZE first), not a guess-and-check habit.

Deliverable

A before/after EXPLAIN ANALYZE writeup with a measured improvement.

sql/week04-optimization-writeup.md

The build

Validate a batch of GAP Commerce order records, field by field.

What breaks

The validator misclassifies some valid orders. A type mismatch (string vs. int comparison) silently produces the wrong result.

Python won't stop you from comparing the wrong types. You have to.

Interview angle

"What's the difference between `is` and `==` in Python?"

Whether the candidate understands identity vs. equality, not just syntax.

Deliverable

A validator that correctly flags every deliberately-seeded bad record.

python/week05_order_validator.py

The build

Process a nested GAP Commerce customer JSON export into clean, flat records.

What breaks

Deduplicating 1,000,000 customer IDs the naive way takes minutes. An `in` check against a list is O(n) per lookup; a set makes it O(1).

The right data structure isn't a style choice. It's a measured performance decision.

Interview angle

"Deduplicate this list of IDs. What's the time complexity of your approach, and can you do better?"

Real complexity reasoning, applied, not recited.

Deliverable

A JSON processor plus a measured (not guessed) complexity comparison.

python/week06_json_processor.py

The build

Split the growing GAP Commerce script into a real, modular pipeline.

What breaks

The 400-line script needs a change and nobody can safely make it. Refactor it into modules under time pressure without breaking the given test cases.

A script that does five things isn't a pipeline; four functions that each do one thing is.

Interview angle

"How would you structure a data pipeline's codebase, and why?"

Real architectural instinct, not just working code.

Deliverable

A `pipeline/` package with the four-file structure and a README explaining the design.

pipeline/{extract,transform,validate,load}.py

The build

Pull paginated order data from the GAP Commerce Orders API into raw JSON.

What breaks

The API is made to return 429, 500, a timeout, malformed JSON, a missing field, and a duplicate record. A single blanket `except` treats every failure mode identically, which is wrong for all of them.

Different failures need different responses. Retry, skip, quarantine, or fail loudly are all valid, situationally.

Interview angle

"Your API call just got rate-limited. What do you do?"

Real retry/backoff design, not "try again immediately."

Deliverable

An ingestion script that survives every injected failure mode without losing or duplicating data.

pipeline/extract_api.py

The build

Add retries, structured logging, and idempotent writes to the API pipeline.

What breaks

The pipeline ran twice by accident. 100 records became 200: a blind insert, not an upsert.

A pipeline that silently duplicates data is worse than one that crashes loudly.

Interview angle

"How do you make an ingestion job safe to re-run?"

Whether idempotency is a designed property or an afterthought.

Deliverable

Proof (in the PR description) that re-running the pipeline changes zero net rows.

pipeline/config.py

The build

Rewrite a 5GB product-catalog reader from `.readlines()` to a generator.

What breaks

The product catalog export grew to 5GB and the pipeline crashed. `.readlines()` loads the entire file into memory before processing a single row.

The dataset just outgrew the naive approach: the first real 'outgrown' moment in the programme.

Interview angle

"Your pipeline just OOM'd on a large file. Walk me through your diagnosis and fix."

Real memory-profile-driven debugging, not a guess.

Deliverable

A generator-based reader plus a before/after memory-usage writeup.

pipeline/extract_large_file.py

The build

Write validated records into a PostgreSQL staging schema, then promote them to production tables.

What breaks

A bulk insert fails halfway through. No transaction boundary: the table is left in a partially-loaded state.

Bulk writes need to be all-or-nothing, not "however far it got."

Interview angle

"How do you prevent SQL injection from a Python data pipeline?"

Parameterized queries as a habit, not a special case.

Deliverable

A load script that leaves a deliberately-interrupted load in a consistent state.

pipeline/load_postgres.py

The build

Merge orders/customers/products into one clean DataFrame and export it.

What breaks

A date-range filter silently returns nothing. Dates were read in as strings, not datetimes: the dtype was never checked.

"It ran" and "it's correct" are different claims when dtypes are involved.

Interview angle

"When would you NOT use Pandas?"

Real judgment about scale, not blanket tool loyalty.

Deliverable

A merged dataset plus a written, scale-threshold-aware rationale for a 200MB test file.

docs/week12-pandas-decision.md

The build

Add a validation layer that quarantines bad records instead of dropping them.

What breaks

10% of yesterday's records have invalid dates. There's no policy for what should happen: reject, coerce, or quarantine.

"It didn't crash" isn't the same as "the data is correct."

Interview angle

"Design a data quality layer for an ingestion pipeline. What do you do with a record that fails validation?"

A real, defensible policy, not "just log it."

Deliverable

Quarantined records logged with a specific, actionable reason code.

pipeline/validate_quality.py

The build

Turn raw_orders/raw_customers/raw_products into dim_customer/dim_product/fact_orders.

What breaks

A customer's address history the business needed is gone. An SCD Type 1 update overwrote history instead of preserving it.

The right SCD type is a requirements decision, not a default.

Interview angle

"Design a star schema for an e-commerce orders system."

Real modeling judgment: grain, keys, and fact/dimension boundaries.

Deliverable

A dimensional model with an explicitly documented, consistent grain.

models/dimensional_model.sql

The build

Open a real PR against the pipeline repo; review a peer's PR with real comments.

What breaks

Two people edited transform.py at the same time. A merge conflict.

Resolving a conflict without losing either side's work is a real, learnable skill.

Interview angle

"Walk me through your PR and code review process."

Whether the candidate has actually worked on a shared codebase, not just alone.

Deliverable

A merged PR with at least one substantive review comment addressed.

One real PR + README.md

The build

Containerize Python + PostgreSQL + pgAdmin as one local stack.

What breaks

A teammate's Compose run fails. An environment variable isn't set the same way as on the author's machine.

Reproducibility has to be designed in (`.env.example`), not assumed.

Interview angle

"Why would you containerize a data pipeline?"

Whether the candidate sees this as boilerplate or as a real reliability decision.

Deliverable

A fresh clone running successfully via docker-compose up, with no manual setup.

Dockerfile, docker-compose.yml

The build

Create a least-privilege role assignment; upload and retrieve a blob via script.

What breaks

A storage operation returns Access Denied. An AuthorizationFailed error from a missing role assignment.

Least-privilege means starting with nothing and adding exactly what's needed, not the reverse.

Interview angle

"Explain RBAC least-privilege to a non-technical stakeholder."

Whether the candidate can explain security concepts plainly, not just implement them.

Deliverable

A role assignment scoped to only the storage actions the pipeline uses.

infra/role-assignment.json

The build

Move the pipeline's raw zone to ADLS Gen2 as partitioned Parquet, and mirror it into a Fabric Lakehouse.

What breaks

Data ends up in the wrong "folder." A hardcoded wrong container/path silently writes to the wrong prefix.

Path logic needs to be traced and verified, not assumed correct because nothing errored.

Interview angle

"What problem does OneLake solve that a plain data lake doesn't?"

Whether Fabric's actual value proposition (one copy, many engines) was understood, not just the product name.

Deliverable

Correctly partitioned Parquet, verified readable back in both ADLS Gen2 and the Fabric Lakehouse.

pipeline/load_azure_storage.py

The build

Query the OneLake Lakehouse data directly through a Fabric Warehouse item, then load the same data into Snowflake and compare the two paths.

What breaks

The Fabric Warehouse query is missing rows a Lakehouse write just added, and separately, a Snowflake load completes but some rows are missing. Fabric's SQL analytics endpoint hasn't synced with the Lakehouse write yet; Snowflake's COPY INTO silently skips malformed rows unless the load history/error log is checked.

A query or a load that "succeeds" still has to be reconciled against the source, in either tool.

Interview angle

"How does a warehouse differ architecturally from an OLTP database like Postgres?"

Real understanding of compute/storage separation and columnar storage, not just "it's for analytics."

Deliverable

A Fabric Warehouse query against the Lakehouse data, plus a Snowflake schema with loaded raw tables and an explicit skip/error reconciliation.

warehouse/fabric_warehouse.sql

The build

Rebuild the Week 3/14 SQL as dbt staging -> intermediate -> mart models against the Fabric Warehouse, tested.

What breaks

A dbt test fails on a null foreign key. The failure has to be traced back through the model lineage to the real source problem.

dbt docs' lineage graph is a real debugging tool, not just documentation.

Interview angle

"What problem does dbt actually solve that raw SQL scripts don't?"

Whether the candidate understands dbt as a discipline, not just a syntax layer over SQL.

Deliverable

dbt test passing clean, dbt docs generate producing a correct lineage graph.

dbt_project/

The build

Convert the full extract -> validate -> transform -> load -> dbt chain into a scheduled Fabric Data Factory pipeline, then rebuild the same dependency chain as an Airflow DAG to compare the two orchestration models.

What breaks

One activity fails mid-pipeline in Fabric Data Factory, and the same failure is reproduced mid-DAG in Airflow. Both failures are deliberately injected; the learner must diagnose from each tool's own logs and recover.

Recovery should re-run only the failed step, not the whole pipeline or DAG from scratch, in either tool.

Interview angle

"What happens if step 2 of your pipeline fails but step 1 already succeeded?"

Real operational thinking about partial failure, not just one tool's syntax.

Deliverable

A deliberately-failed activity/task that recovers correctly on retry without re-running successful upstream steps, in both Fabric Data Factory and Airflow.

pipelines/gap_commerce_pipeline.json

The build

Add unit + data tests, a CI workflow, and a basic pipeline-health check.

What breaks

CI fails even though the tests pass locally. An environment/dependency difference between the local machine and GitHub Actions.

"Works on my machine" applies to CI too, and has to be diagnosed the same way.

Interview angle

"How do you know your pipeline actually worked, not just that it finished?"

Real pipeline-health thinking: expected data, expected volume, expected quality, expected time.

Deliverable

CI that passes on a clean PR and correctly fails on a deliberately broken one.

.github/workflows/ci.yml

The build

Rewrite the Week 12 Pandas transform as a PySpark job against a 50M-row synthetic dataset.

What breaks

The job runs far slower than expected. A skewed join key, diagnosed via the Spark UI (shuffle read/write, task skew), not guessed.

Spark performance problems are diagnosable, not mysterious. The UI tells you exactly where the time went.

Interview angle

"Why would a join in Spark be slow, and how would you fix it?"

Real shuffle/skew/broadcast-join reasoning, not a memorized definition of lazy evaluation.

Deliverable

A measurably improved job (shuffle/skew metrics, with evidence) over the naive version.

spark/transform_orders.py

The build

Route GAP Commerce order events through a Fabric Eventstream into Eventhouse, then rebuild the same producer/consumer idea in Kafka to compare the two models.

What breaks

One Kafka consumer falls behind, and the equivalent lag shows up in Eventhouse's own ingestion metrics. Consumer lag, diagnosed via consumer-group offset lag metrics in Kafka and ingestion lag in Eventhouse.

A slow consumer doesn't lose data in a durable-log model. It just falls behind, and that's a measurable, fixable state, in either tool.

Interview angle

"How is Kafka different from a queue like SQS?"

Whether the candidate understands Kafka's durable-log model, not just "it's for messaging."

Deliverable

A Kafka consumer that processes events in order within a partition and resumes from its offset after a restart, plus a Fabric Eventstream route landing the same events in Eventhouse.

streaming/consumer.py