Skip to content
Tutorials Center
My learning

Topics

Lesson 43 of 48 ยท Projects

Project: A Bronze-Silver-Gold ETL Pipeline

Build a medallion ETL pipeline in PySpark: raw bronze data, cleaned and deduplicated silver tables, gold aggregates, quality checks and partitioned output.

Real data pipelines rarely read a file, compute something and stop. Data arrives late, is sent twice, contains typos and gets corrected tomorrow. A well-known way to stay sane is the medallion architecture: keep the data in three layers that get progressively cleaner. Bronze is the raw data exactly as it arrived. Silver is cleaned, typed and deduplicated. Gold is aggregated and shaped for reports and dashboards.

In this project you build the layers for a small orders feed, add data quality checks that stop a bad batch before it reaches the gold tables, and write the results as partitioned files. The Spark code is read-only, because there is no cluster on this site. Every layer also has a runnable stand-in using pandas: the same steps on the same messy sample, so you can watch rows move from layer to layer. This project builds on Reading and Writing Data, window functions and partitioning.

Why three layers

flowchart LR
    S["Source files<br/>(CSV drops)"] --> B["Bronze<br/>raw, append-only"]
    B --> V["Silver<br/>typed, deduplicated, validated"]
    V --> G["Gold<br/>daily revenue, customer totals"]
    V -.->|"rejected rows"| Q["Quarantine"]

Each layer reads only from the one before it; rows that fail validation go to a quarantine table.

The point of keeping the raw layer is replayability. If you discover next month that your cleaning rule was wrong, you do not have to ask the source system to resend everything: you fix the silver logic and rebuild it from bronze. The quarantine table gives the same safety to bad rows: nothing is silently thrown away, and a person can look at why it failed.

  • Bronze: all columns kept as strings, plus metadata such as the source file and the load batch. Append only. No cleaning, so no surprises.
  • Silver: correct types, trimmed and standardised values, one row per business key (here, one row per order), invalid rows moved to quarantine.
  • Gold: business-ready tables: aggregates, joins and summaries. Cheap to query because the hard work is already done.

Our sample feed

Orders arrive as CSV files, one per day. Several things go wrong on purpose: country codes with spaces and mixed case, amounts written as $120.50 or 1,200.00, one amount that is abc, a date in the wrong format, a missing customer, a negative amount and an updated record for order 1002 in the second file.

Bronze: land the data untouched

In Spark, read everything as text, add metadata columns and append:

Python
from pyspark.sql import SparkSession, functions as F, Window

spark = SparkSession.builder.appName("orders-etl").getOrCreate()

bronze = (spark.read.option("header", True).option("inferSchema", False)
          .csv("/lake/landing/orders/")
          .withColumn("_source_file", F.col("_metadata.file_path"))
          .withColumn("_ingested_at", F.current_timestamp()))

bronze.write.mode("append").parquet("/lake/bronze/orders")

inferSchema=False keeps every column as a string, so a stray abc in the amount column cannot make the read fail or silently turn into null. The hidden _metadata.file_path column (available for file sources) records where each row came from.

Silver: clean, validate, deduplicate

Silver has four jobs, and order matters:

  1. Deduplicate first. If an order was sent twice, the latest version wins. A window function numbers the versions per order_id and we keep number one. If you validated first, a bad old version could be quarantined while a good new version is kept, or the other way round, and the counts would never reconcile.
  2. Standardise text: trim and uppercase the country.
  3. Cast to proper types in a way that gives null instead of failing.
  4. Validate and split into good and quarantined rows with a reason.
Python
latest_first = Window.partitionBy("order_id").orderBy(F.col("_ingested_at").desc(), F.col("_source_file").desc())

deduped = (spark.read.parquet("/lake/bronze/orders")
           .withColumn("rn", F.row_number().over(latest_first))
           .filter("rn = 1").drop("rn"))

typed = (deduped
         .withColumn("country", F.upper(F.trim("country")))
         .withColumn("amount", F.expr("try_cast(regexp_replace(amount, '[$,]', '') AS DECIMAL(12,2))"))
         .withColumn("order_date", F.expr("try_to_timestamp(order_date, 'yyyy-MM-dd')").cast("date")))

reason = (F.when(F.col("customer").isNull() | (F.col("customer") == ""), "missing customer")
           .when(F.col("order_date").isNull(), "bad date")
           .when(F.col("amount").isNull(), "amount not a number")
           .when(F.col("amount") <= 0, "amount not positive"))

checked = typed.withColumn("reject_reason", reason)
checked.filter("reject_reason IS NULL").drop("reject_reason").write.mode("overwrite").parquet("/lake/silver/orders")
checked.filter("reject_reason IS NOT NULL").write.mode("overwrite").parquet("/lake/quarantine/orders")

Spark 4 turns ANSI mode on by default, which makes an ordinary cast of invalid text raise an error. The try_cast and try_to_timestamp functions return null instead, which is what a validation step needs. An empty string in a CSV may arrive as null or as an empty value depending on the read options, so the check above covers both.

The first stand-in runs the same steps on the sample feed in pandas. A _batch column plays the role of _ingested_at.

Python Bronze to silver with a quarantine table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The numbers reconcile: 11 raw rows become 6 silver rows, 4 quarantined rows and 1 duplicate (the old version of order 1002). Notice that order 1002 has the corrected amount of 92.00, because the later batch won.

Gold: aggregate for the business

Gold tables answer business questions directly. Here we want daily revenue and order count per country:

Python
silver = spark.read.parquet("/lake/silver/orders")

daily_revenue = (silver.groupBy("order_date", "country")
                 .agg(F.sum("amount").alias("revenue"), F.count("*").alias("orders")))

Before gold is written, the pipeline should check its own work. Quality checks are ordinary code that raises an error, so the scheduler marks the run as failed and the previous gold data stays in place. Typical checks:

  • Uniqueness: no order_id appears twice in silver.
  • Not null: key columns have no nulls.
  • Range: amounts are positive.
  • Reconciliation: bronze rows = silver rows + quarantined rows + duplicates removed, and gold revenue = silver revenue.
  • Volume: today's row count is not suspiciously far from the usual one; a 90% drop usually means a broken upstream feed.

The second stand-in repeats the silver step, builds gold, runs the checks and then shows what happens when someone introduces a bug.

Python Gold aggregates and data quality checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The broken run is caught by the uniqueness check (order 1002 appears twice and would be counted twice in revenue), so the bad numbers never reach the dashboard. In production, the check functions run inside the same job, and the scheduler's failure alert tells the team.

Write partitioned output

Gold tables are usually written partitioned by a column you filter on, often the date. Spark creates one folder per value (order_date=2026-10-01/), and a query with WHERE order_date = '2026-10-02' then reads only that folder (partition pruning).

Python
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(daily_revenue.write
    .mode("overwrite")
    .partitionBy("order_date")
    .parquet("/lake/gold/daily_revenue"))

The setting partitionOverwriteMode=dynamic is the key to idempotent reruns. With the default static mode, mode("overwrite") would delete the whole target folder before writing, so re-running only today's data would wipe every earlier day. In dynamic mode, only the partitions present in the new data are replaced. Choose a partition column with a modest number of values, because thousands of tiny folders and files cause the small-files problem (see Partitions and Partitioning).

The last stand-in writes real files in the browser's temporary file system, as CSV (Spark would write Parquet), and compares the two overwrite modes.

Python Dynamic partition overwrite versus static overwrite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dynamic mode keeps day 1 and replaces day 2 (now three rows); static mode leaves only day 2. That single setting is the difference between a safe rerun and lost history.

Real versus stand-in. The Spark code in this lesson is the real thing but did not run here. The pandas stand-ins reproduce the logic on 11 rows: latest-version-wins deduplication, try_cast-like conversion with errors="coerce", quarantine, reconciliation checks and partition folders. Real Spark runs these steps in parallel over large datasets, writes Parquet, and records the work in a transaction log when you use a table format such as Delta Lake.

Production habits

  • Use a table format such as Delta Lake or Apache Iceberg for silver and gold when you can. They add atomic commits and MERGE for upserts, so a failed job never leaves half-written files.
  • Make every step idempotent: the same input must produce the same output, so a retry is always safe.
  • Process incrementally: read only new files (Structured Streaming with trigger(availableNow=True) can do that), and key work by batch or date.
  • Keep a data dictionary and alerts: log row counts per layer, and alert when quarantine grows suddenly.
  • Test the transformation functions on small data, as described in Testing, Debugging and the Spark UI.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why keep the bronze layer as raw strings instead of cleaning on load?

    Raw, append-only data is the safety net: if a cleaning rule turns out to be wrong, rebuild silver from bronze.
  2. 2. Why does the silver step deduplicate before validating?

    If you validated first, an old bad version and a new good version of the same order could be treated differently, and the layers would not reconcile.
  3. 3. What does spark.sql.sources.partitionOverwriteMode=dynamic change for mode("overwrite")?

    With static mode, overwrite deletes the entire target first. Dynamic mode replaces only the partitions being written, which makes reruns of one day safe.
  4. 4. Which quality check would catch rows that were lost silently between layers?

    Reconciliation counts account for every input row. A mismatch means rows were dropped without a reason.

Summary

  • The medallion pattern keeps raw (bronze), cleaned (silver) and aggregated (gold) data in separate layers, each built only from the layer before.
  • Bronze is raw, all strings and append-only; silver is typed, standardised, deduplicated (latest version wins) and validated, with rejects in quarantine.
  • Quality checks (uniqueness, not null, ranges, reconciliation, volume) run as code and stop the pipeline before bad data reaches gold.
  • Write gold partitioned by a modest-cardinality column, and use dynamic partition overwrite so reruns replace only the partitions they touch.
  • Make every step idempotent, prefer a table format like Delta or Iceberg, and monitor row counts per layer.

Next: process data as it arrives in Project: Real-Time Event Counter.