Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 36 ยท Projects and Practice

Project: A Medallion Pipeline for Online Orders

Build a complete bronze, silver and gold pipeline for online orders: ingestion, cleaning, deduplication, quality checks and daily revenue, with a runnable Python version.

This project ties the chapters together. You will design a medallion architecture for an online shop: raw files land, are cleaned and typed, and finally become tables that analysts and dashboards use. You will write the real Databricks code (read-only, because Databricks cannot run on this site) and a complete runnable Python version of the same logic, so you can check the rules with real data.

The goal

The shop exports order files every day as JSON lines. Problems you must handle: duplicate deliveries, missing IDs, amounts as strings, refunds with negative amounts, and late corrections of the same order.

flowchart LR
    F["/Volumes/prod/shop/raw/orders"] --> B["bronze.orders (as received)"]
    B --> S["silver.orders (typed, deduplicated, valid)"]
    B --> Q["silver.orders_rejected"]
    S --> G1["gold.daily_revenue"]
    S --> G2["gold.customer_summary"]

Bronze keeps everything; silver keeps good rows; gold answers questions.

Layer Contains Rule
Bronze Raw rows plus file name and load time Never drop or fix data here
Silver One clean row per order with correct types Validate, deduplicate, keep rejects
Gold Business aggregates Small, fast, documented

Step 1: namespace and permissions

SQL
CREATE CATALOG IF NOT EXISTS prod;
CREATE SCHEMA IF NOT EXISTS prod.bronze;
CREATE SCHEMA IF NOT EXISTS prod.silver;
CREATE SCHEMA IF NOT EXISTS prod.gold;
CREATE VOLUME IF NOT EXISTS prod.bronze.raw_orders;

GRANT USE CATALOG ON CATALOG prod TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA prod.gold TO `analysts`;
GRANT ALL PRIVILEGES ON SCHEMA prod.silver TO `shop-etl-sp`;

Analysts see only gold; the pipeline's service principal builds the rest (see Grants and Privileges).

Step 2: bronze with Auto Loader

Python
from pyspark.sql import functions as F

bronze = (spark.readStream.format("cloudFiles")
          .option("cloudFiles.format", "json")
          .option("cloudFiles.schemaLocation", "/Volumes/prod/bronze/chk/orders_schema")
          .option("cloudFiles.inferColumnTypes", "false")      # keep strings at bronze
          .load("/Volumes/prod/bronze/raw_orders/")
          .select("*", F.col("_metadata.file_name").alias("source_file"),
                  F.current_timestamp().alias("ingested_at")))

(bronze.writeStream
   .option("checkpointLocation", "/Volumes/prod/bronze/chk/orders")
   .trigger(availableNow=True)
   .toTable("prod.bronze.orders"))

See Auto Loader for why bronze keeps strings and a rescue column.

Step 3: silver with validation and deduplication

Python
from pyspark.sql.window import Window

typed = (spark.read.table("prod.bronze.orders")
    .select(
        F.col("order_id").cast("bigint"),
        F.col("customer_id").cast("bigint"),
        F.col("amount").cast("decimal(12,2)"),
        F.to_timestamp("order_ts").alias("order_ts"),
        "ingested_at"))

is_valid = F.col("order_id").isNotNull() & F.col("customer_id").isNotNull() \
           & F.col("amount").isNotNull() & F.col("order_ts").isNotNull()

rejected = typed.where(~is_valid)
valid = typed.where(is_valid)

latest = Window.partitionBy("order_id").orderBy(F.col("ingested_at").desc())
silver = (valid.withColumn("rn", F.row_number().over(latest))
               .where("rn = 1").drop("rn"))

rejected.write.mode("append").saveAsTable("prod.silver.orders_rejected")
silver.write.mode("overwrite").saveAsTable("prod.silver.orders")

In production you would use MERGE INTO for incremental upserts or a declarative pipeline with AUTO CDC.

Step 4: gold

SQL
CREATE OR REPLACE TABLE prod.gold.daily_revenue AS
SELECT date(order_ts) AS day,
       count(*)       AS orders,
       sum(amount)    AS revenue
FROM prod.silver.orders
GROUP BY date(order_ts);

The same logic, runnable

Below, the same pipeline in plain Python (not PySpark). Fix nothing in the data by hand: let the rules do it.

Python Stand-in: bronze to silver to gold in Python

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.

Work through the result: order 2 uses the corrected amount, order 1 appears once, the null ID and the "abc" amount went to rejects, and the refund is kept (it is valid data; whether it counts as revenue is a business decision).

Step 5: data-quality checks

A pipeline you cannot trust is worse than none. Add checks as a separate task:

Python Stand-in: quality gates before publishing gold

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 refund check fails on purpose: refunds are 10 against 70.5 of sales, above the 5 percent limit, so gold is not published until someone decides whether that is normal. A failing gate is the point of having one.

Step 6: orchestrate

Create a job with four tasks: ingest bronze, build silver, run checks, build gold; each depends on the previous one (see Multi-Task Workflows). Schedule it daily, run as a service principal, retry transient failures twice and email a group on failure. Package it as a bundle (see Asset Bundles and CI/CD).

Extensions to try

  1. Add a gold.customer_summary with lifetime value and last order date.
  2. Add an expectation that drops rows older than a year.
  3. Mask customer_id for the analysts group using a column mask.
  4. Replace the silver overwrite with MERGE INTO keyed by order_id.
  5. Add an alert when rejected / bronze exceeds 10 percent.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why does bronze keep data exactly as received?

    Raw history lets you rebuild silver and gold at any time.
  2. 2. In the project, how are duplicate order deliveries resolved?

    row_number over order_id ordered by ingestion time keeps the newest.
  3. 3. Where do rows with null keys or non-numeric amounts go?

    Rejects are kept so quality problems can be fixed at the source.

Summary

  • Medallion layers separate raw capture, clean data and business aggregates.
  • Bronze is raw; silver types, validates and deduplicates; gold answers questions.
  • Quality gates, orchestration, permissions and CI make it production-ready.

Next: Project: A Governed Sales Mart