Building a Production-Grade Data Lakehouse on Labour Force Data — Without a Cloud Bill

A walkthrough of how modern data pipelines actually work, built end-to-end on a laptop.

— -

## Why I Built This

Most portfolios show fragments — a Spark job here, a dbt model there. What engineers actually love to see is whether you can think end-to-end: ingest raw data, enforce quality at every layer, model it historically, and serve it analytically — with the same rigour you’d apply in a production environment.

So I built exactly that. A complete medallion lakehouse pipeline on real Canadian government employment data, running entirely locally, using the same architectural patterns used at scale in the industry.

No cloud account. No managed services. Just the stack.

— -

The Dataset — Why Statistics Canada

Statistics Canada publishes the Labour Force Survey (LFS) — monthly employment data broken down by province, industry, age group, sex, and labour force characteristics. Two tables:

• 14–10–0287–03 — employment by province and demographic
• 14–10–0355–02 — employment by industry and province

It’s messy, real-world government data. Suppressed values, multi-dimensional rows, no pre-joined model. Exactly the kind of raw input a data engineer deals with in production.

— -

The Architecture — Medallion, End to End

The pipeline follows the medallion pattern: Bronze → Silver → Gold, with a data quality gate between Silver and the dimension layer.

Fig 1 — Architecture

Each layer has one responsibility. Bronze never transforms. Silver never serves queries. Gold never ingests raw data. This separation is what makes a pipeline debuggable, re-processable, and scalable.

— -

The Stack

• Apache Spark (PySpark) — distributed compute for ingestion, transformation, and SCD2 merges
• Delta Lake — ACID transactions, schema enforcement, and time travel on local storage
• dbt (Spark adapter) — declarative SQL for Gold models with built-in test contracts
• Pandas — lightweight dataset exploration before Spark ingestion
• Python — custom pipeline orchestrator with fail-fast, per-stage timing, and .env management

— -

The Engineering Decisions That Matter

1. Why SCD Type 2?

The LFS publishes monthly snapshots. Without SCD2, every pipeline run overwrites the current state — you lose the ability to answer ”what was the employment rate in Ontario in March 2022?” after the data updates.

SCD2 is implemented using a deliberate two-operation pattern. A single Delta MERGE cannot handle both expiring old rows and inserting new versions in one pass — because Delta evaluates all match conditions against the original table state atomically. A changed entity is always MATCHED (same natural key, is_current=true), so whenNotMatchedInsert never fires for it.

The solution:

• Op 1 expires old rows via MERGE
• Op 2 appends new versions via a direct write.

# Op 1 — MERGE: expire old current rows for changed keys
dim_table.alias("t").merge(incoming_df.alias("s"), match_condition) \
    .whenMatchedUpdate(
        condition=expire_condition,
        set={
            "t.end_date": "date_sub(current_date(), 1)",
            "t.is_current": "false"
        }).execute()

# Op 2 — WRITE: append new versions + new entities directly
# (cannot use whenNotMatchedInsert — Delta MERGE evaluates against
#  original table state, so changed rows always appear as MATCHED)
to_insert.write.format("delta").mode("append").save(DIM_EMPLOYMENT_PATH)

“A single Delta MERGE cannot handle both expiring old rows and inserting new versions in one pass — Delta evaluates all match conditions against the original table state atomically, so changed rows always appear as MATCHED and whenNotMatchedInsert never fires for them. The two-operation pattern solves this correctly.”

2. Data Quality as a Pipeline Gate, Not an Afterthought

Most pipelines add quality checks as an afterthought — a few dbt tests at the end. This project adds a dedicated quality gate at Silver, before any dimension or Gold data is written.

Six checks run every pipeline execution:

NULL_THRESHOLD   = 0.3   # max 5% nulls allowed on key columns
FRESHNESS_MONTHS = 6      # Silver data must have a row within last 6 months
VALUE_MIN        = 0      # employment values must be >= 0

If any check fails, the pipeline halts with a non-zero exit code. Bad data never reaches the SCD2 history.

3. dbt and Spark — Two Quality Layers, Not One

A common question: ”if you have dbt tests, why do you need a separate Spark quality layer?”

They serve different purposes:

• 03_data_quality.py — validates Silver before transformation. Catches row drops, nulls, stale data, and referential integrity failures before they corrupt SCD2 history.
• dbt tests — validate Gold models after they are built. Enforce schema contracts: uniqueness, not-null, accepted values on the final analytical layer.

Both are necessary. Removing either creates a blind spot.

— -

The Orchestrator — One Command to Run Everything

A custom pipeline runner (06_pipeline_runner.py) ties all stages together with a single command:

# Full pipeline
python spark/06_pipeline_runner.py
# Skip bronze if already loaded
python spark/06_pipeline_runner.py --skip-bronze
# Debug a single stage
python spark/06_pipeline_runner.py --stage dq

It fails fast — if Silver fails, SCD2 never runs. It logs per-stage timing. It loads .env automatically so subprocesses inherit environment variables without manual sourcing. The stage structure maps directly to a Step Functions state machine for AWS EMR deployment.

— -

What This Demonstrates

This project isn’t about the StatCan dataset. It’s about proving that a complete, production-aware data pipeline can be designed, built, and documented by someone who understands:

• Architectural separation — why Bronze, Silver, and Gold exist as distinct layers
• Historical modelling — SCD2 is a first-class engineering concern, not a nice-to-have
• Quality as a gate — not a log message at the end, but a hard stop in the middle
• Real data is messy — and the pipeline should be calibrated to that reality, not fight it
• Local first, cloud ready— the same code runs on a laptop and on AWS EMR

The full source code, pipeline runner, dbt models, and setup guide are on GitHub:data-engineering-blogs

— -

Built with PySpark · Delta Lake · dbt · Python