Medallion Architecture ETL in Databricks

Introduction

30 Second Summary

Every time you check a weather app or see a product recommendation, you are looking at the end result of data that has been collected, cleaned, and summarized through multiple stages. In data engineering, this layered approach is called the medallion architecture: data flows through three layers — bronze (raw ingestion), silver (cleaned and validated), and gold (aggregated for business use) — so each stage has a single, clear job.

In this project, you will build a complete medallion architecture pipeline in Databricks using SQL. Starting from raw NYC taxi trip data, you will create three layers of progressively cleaner data and automate the entire flow with a single click.

What You'll Build

When you click Run now on your finished Lakeflow Job, three SQL notebooks execute in sequence and raw taxi data flows through bronze, silver, and gold layers to arrive as pre-computed business summaries.

By the end of this project, you'll have:

• A complete data quality investigation where you query raw taxi data in a bronze table, discover hidden problems like null fares and zero-distance trips, then watch those problems disappear in your cleaned silver table.
• A business-ready gold table where you answer questions like "which time slot generates the most revenue?" with a single query instead of scanning millions of raw records.
• An automated Lakeflow Job that runs your entire pipeline with one click and shows green checkmarks when every stage completes.
• Secret Mission: Build a zone performance leaderboard gold table that ranks pickup zones by revenue and connect it to a pipeline health dashboard monitoring data quality across all three layers.

Are there any prerequisites?

You need a Databricks Free Edition account with access to the samples.nyctaxi.trips dataset. You should be comfortable with SQL queries including JOINs, GROUP BY, and CASE statements. Completing a prior Databricks dashboard project is recommended but not required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This step is your chance to commit to the goal before any technical work begins.

Set Up Your Medallion Schema Structure

Every medallion pipeline needs a place to store its data at each layer. In Databricks, Unity Catalog schemas act as containers that organize your tables into logical groups.

You will create three separate schemas under your workspace catalog. One for bronze, one for silver, one for gold. This mirrors how production data teams structure their lakehouse.

In this step, get ready to:

• Create a SQL notebook and verify your workspace connection.
• Create three Unity Catalog schemas for your medallion layers.
• Confirm all three schemas exist with a verification query.

Create a SQL notebook and verify your workspace

Your first task is getting a notebook running and confirming you can reach the sample data. This notebook will be your development workspace for the entire pipeline.

• Sign in to your Databricks Free Edition workspace.
• In the left sidebar, click + New.
• Click Notebook.

A blank notebook opens and automatically connects to serverless compute. The notebook's default language appears next to the notebook name at the top of the page.

• If the language is not already set to SQL, click the language button next to the notebook name and select SQL from the dropdown.
• Verify which catalog your workspace is using by pasting this query into the first cell and pressing Shift+Enter to run it:

SELECT current_catalog();

You should see your workspace catalog name in the output. This is the catalog where all your schemas and tables will live.

• Verify access to the sample dataset by hovering below the current cell, clicking + Code to add a new cell, and running:

SELECT * FROM samples.nyctaxi.trips LIMIT 5;

You should see five rows of NYC taxi trip data with columns like tpep_pickup_datetime, fare_amount, and trip_distance. This is the same dataset you worked with in your dashboard projects.

What is a workspace catalog?

In Unity Catalog, every table lives inside a three-level namespace: catalog.schema.table. Your workspace catalog is the default catalog assigned to your Databricks workspace.

All the schemas you create in this step will live under that catalog. When you reference a table like medallion_bronze.raw_trips, Databricks automatically prepends your workspace catalog name.

🙋‍♀️ Not seeing any data?

Check that your notebook is connected to compute. Look for a green dot next to the compute indicator at the top of the notebook. If it says Connect, click it to attach to serverless compute.

If the query runs but returns no results, make sure you typed samples.nyctaxi.trips exactly as shown. This is a built-in sample dataset available in all Databricks workspaces.

Help me connect my Databricks notebook to compute and query the sample data.

Create three schemas for your medallion layers

Each layer of a medallion architecture gets its own schema. This keeps bronze, silver, and gold tables organized and easy to manage independently.

• Add a new cell and create the three schemas by running:

-- Create separate schemas for each medallion layer
CREATE SCHEMA IF NOT EXISTS medallion_bronze;
CREATE SCHEMA IF NOT EXISTS medallion_silver;
CREATE SCHEMA IF NOT EXISTS medallion_gold;

What does this code do?

• CREATE SCHEMA IF NOT EXISTS creates a new schema under your workspace catalog only if it does not already exist. This makes the command safe to re-run.
• Each schema maps to one layer: medallion_bronze for raw data, medallion_silver for cleaned data, and medallion_gold for business-ready aggregates.

After running the cell, you should see a success confirmation for each statement.

Why separate schemas instead of one?

Production data teams use separate schemas for each medallion layer to control access permissions independently. An analyst might have read access to gold tables but not silver.

This pattern matches the official Databricks medallion architecture example, which uses separate schemas like ops.bronze, ops.silver, and ops.gold.

🙋‍♀️ Seeing a permission error?

Creating schemas requires CREATE SCHEMA and USE CATALOG privileges on the workspace catalog. Workspace admins have these by default on Free Edition. If you are not a workspace admin, ask the account owner to grant you these privileges.

Help me fix a permission error when creating schemas in Databricks.

Verify your schemas exist

Before moving on, confirm that all three schemas were created successfully.

Before you run the next query, think about what you expect to see. How many new schemas should appear in the results alongside any that already existed?

• Add a new cell and confirm the schemas by running:

SHOW SCHEMAS;

You should see medallion_bronze, medallion_silver, and medallion_gold in the results list, alongside the default schema that already existed.

Missing a schema?

Scroll through the full results list. Your workspace catalog may contain other schemas from previous projects, so the three medallion schemas might not be at the top.

If a schema is missing, go back to the previous cell and re-run the CREATE SCHEMA IF NOT EXISTS statements. The IF NOT EXISTS clause means running it again will not cause any errors.

Help me figure out why my SHOW SCHEMAS output is missing one of my medallion schemas.

✔️ Awesome, I see all three schemas!

Great. Your medallion schema structure is ready. All the tables you build in the next steps will land in these three schemas.

ⓧ I'd like to double check the full code

Your notebook should have four cells at this point. Scroll through them and confirm each one is present, in order:

• Cell 1: SELECT current_catalog();
• Cell 2: SELECT * FROM samples.nyctaxi.trips LIMIT 5;
• Cell 3: The three CREATE SCHEMA IF NOT EXISTS statements for medallion_bronze, medallion_silver, and medallion_gold.
• Cell 4: SHOW SCHEMAS;

Your workspace is organized and ready to fill. Next up, you will build the bronze layer and discover what data quality problems are hiding in the raw taxi data.

Build the Bronze Layer and Discover Data Quality Issues

Your three schemas are set up and ready to hold data. The first one to fill is the bronze layer, which captures raw data exactly as it exists in the source, with zero transformations.

"Exactly as it exists" includes the messy parts. In your earlier dashboard projects, you likely filtered out bad records with WHERE clauses before they caused problems. This time, you will load everything first. Then you will run queries that reveal the data quality issues hiding in the raw data.

In this step, get ready to:

• Discover the date range of the source data and load it into bronze in three simulated batches with ingestion metadata.
• Run data quality checks to discover invalid and extreme values.
• Query specific problematic rows to see the issues firsthand.

Discover the data range and create the bronze table

In production, data does not arrive all at once. It lands in batches over time: yesterday's transactions, this morning's sensor readings, last hour's clickstream events. Before you build the bronze table, you need to know the date range of the source data so you can split it into realistic batches.

• In your SQL notebook from the previous step, hover below your last cell and click + Code to add a new cell.
• Check the date range of the source data by pasting the following query and pressing Shift+Enter:

-- Check the date range of the source data
SELECT
  MIN(tpep_pickup_datetime) AS earliest_pickup,
  MAX(tpep_pickup_datetime) AS latest_pickup,
  COUNT(*) AS total_rows
FROM samples.nyctaxi.trips;

What does this query do?

MIN and MAX on tpep_pickup_datetime reveal the earliest and latest trip in the dataset. You need these boundaries to split the data into date-based batches.

COUNT(*) gives you the total row count so you can verify later that all rows made it into bronze after the batch loads.

You should see the data spans from early January 2016 through late February 2016, with the total row count in the tens of thousands. These month boundaries give you a natural way to split the data into batches.

Why load in batches?

Loading everything with a single SELECT * is the simplest approach, but it builds a habit that breaks the moment your data grows. In production pipelines, new data arrives continuously and you append each batch rather than rebuilding the entire table.

By splitting the taxi data into date-based batches now, you are simulating exactly the scenario that incremental ingestion tools like Auto Loader handle automatically. The ingested_at and source_batch metadata columns you are about to add are the same kind of tracking Auto Loader does under the hood.

• Add a new cell and create the bronze table with the first batch of January trips by pasting this code and pressing Shift+Enter:

-- Bronze Layer: Create table with the first batch (January trips)
CREATE TABLE IF NOT EXISTS medallion_bronze.raw_trips AS
SELECT
  *,
  current_timestamp() AS ingested_at,
  'batch_1_jan' AS source_batch
FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime < '2016-02-01';

What does this code do?

CREATE TABLE IF NOT EXISTS creates the table on the first run and silently skips if it already exists. This is the append-friendly alternative to CREATE OR REPLACE TABLE, which nukes and rebuilds the entire table every time.

The two extra columns track how each row arrived: ingested_at records the exact timestamp of this load, and source_batch labels which batch the row belongs to. Later batches will have different timestamps and labels, so you can always trace any row back to the load that brought it in.

• Add another new cell and append the remaining two batches by pasting this code and pressing Shift+Enter:

-- Batch 2: February trips
INSERT INTO medallion_bronze.raw_trips
SELECT
  *,
  current_timestamp() AS ingested_at,
  'batch_2_feb' AS source_batch
FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime >= '2016-02-01'
  AND tpep_pickup_datetime < '2016-03-01';

-- Batch 3: Remaining trips
INSERT INTO medallion_bronze.raw_trips
SELECT
  *,
  current_timestamp() AS ingested_at,
  'batch_3_remaining' AS source_batch
FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime >= '2016-03-01';

What does INSERT INTO do differently?

INSERT INTO appends rows to a table that already exists. Each batch gets its own ingested_at timestamp and source_batch label, so the bronze table remembers which load delivered each row.

Notice the date filters on tpep_pickup_datetime split the source data into non-overlapping windows. Each batch covers a distinct month, which means zero duplicate rows across batches.

• Verify the batch loads by adding a new cell and running the following query:

-- Verify ingestion by batch
SELECT
  source_batch,
  COUNT(*) AS row_count,
  MIN(ingested_at) AS batch_ingested_at
FROM medallion_bronze.raw_trips
GROUP BY source_batch
ORDER BY source_batch;

You should see rows for batch_1_jan and batch_2_feb, each with its own row count and a distinct batch_ingested_at timestamp. The batch_3_remaining row may show a small count or may not appear if the dataset has no trips after February. The row counts across all batches should add up to the total_rows from your discovery query.

Row counts don't add up?

• If you re-ran the notebook, the CREATE TABLE IF NOT EXISTS cell is silently skipped on the second run (the table already exists), but the two INSERT INTO cells append duplicate rows. Drop the table with DROP TABLE medallion_bronze.raw_trips; and re-run all three cells from scratch.
• Make sure you used the fully qualified table name medallion_bronze.raw_trips. If you omit the schema, the table is created in the default schema instead.
• Confirm your notebook is connected to serverless compute. Check the compute selector in the top-right of the notebook. If it shows disconnected, click it and select serverless.
• help me troubleshoot why my bronze batch row counts don't match the total from the discovery query

If you see a permission error, check the compute selector and schema name. Help me troubleshoot a permission error when creating a table in my Databricks bronze schema.

✔️ Awesome, I've got everything!

Great. Scroll up through your notebook and confirm you have four cells for this substep: the discovery query, the CREATE TABLE IF NOT EXISTS with batch 1, the two INSERT INTO statements for batches 2 and 3, and the batch verification query.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm these four cells appear in order:

• Cell 1: The SELECT MIN(...), MAX(...), COUNT(*) discovery query reading from samples.nyctaxi.trips.
• Cell 2: The CREATE TABLE IF NOT EXISTS medallion_bronze.raw_trips AS statement with ingested_at, source_batch, and the January date filter.
• Cell 3: Two INSERT INTO statements for batch_2_feb and batch_3_remaining with their respective date filters.
• Cell 4: The GROUP BY source_batch verification query showing row counts and timestamps per batch.

Each cell should have a green checkmark or completion indicator showing it ran successfully.

Run data quality checks against the bronze table

In your dashboard projects, you wrote WHERE clauses to work with clean subsets of this data. But you never checked what you were filtering out. Time to find out what is actually hiding in the raw data.

Before you run the next query, take a guess: do you think the raw taxi data is perfectly clean, or are there records with missing values, zero distances, or negative fares?

• Add a new cell and run this data quality check:

-- Check for data quality issues in the bronze table
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN fare_amount IS NULL THEN 1 ELSE 0 END) AS null_fares,
  SUM(CASE WHEN trip_distance IS NULL THEN 1 ELSE 0 END) AS null_distances,
  SUM(CASE WHEN fare_amount <= 0 THEN 1 ELSE 0 END) AS non_positive_fares,
  SUM(CASE WHEN trip_distance <= 0 THEN 1 ELSE 0 END) AS non_positive_distances,
  MIN(fare_amount) AS min_fare,
  MAX(fare_amount) AS max_fare,
  MIN(trip_distance) AS min_distance,
  MAX(trip_distance) AS max_distance
FROM medallion_bronze.raw_trips;

What does this query do?

Each SUM(CASE WHEN ...) line counts how many rows match a specific problem. For example, null_fares counts rows where fare_amount is NULL.

The MIN and MAX columns reveal the extremes. If the minimum fare is negative or the maximum is suspiciously high, those are outliers that would skew any analysis.

The results show non-zero counts for null_fares, non_positive_fares, and non_positive_distances. This is real-world data with real problems.

Why does this matter?

These are the records that would silently corrupt any dashboard or analysis built directly on the raw table. A $0 fare dragging down an average, a negative amount inflating a revenue total, or a zero-distance trip producing a divide-by-zero error.

Databricks recommends never writing directly from ingestion to silver. Capturing the raw data in bronze first lets you see the problems before you fix them.

See the problematic rows firsthand

You know problems exist. Now look at specific examples so you can see exactly what needs fixing.

• Add a new cell and run this query to view the actual bad records:

-- See specific problematic rows
SELECT
  fare_amount,
  trip_distance,
  pickup_zip,
  dropoff_zip
FROM medallion_bronze.raw_trips
WHERE fare_amount <= 0
   OR trip_distance <= 0
   OR fare_amount > 500
LIMIT 20;

What should I see?

You should see rows with $0 fares, 0-mile distances, negative amounts, or fares over $500. Each of these rows would corrupt a downstream calculation if left in place.

These are the exact records that your silver layer will filter out in the next step. Without seeing them, cleaning the data would feel like busywork. Now you know why every single filter matters.

Not seeing any problematic rows?

• Make sure your FROM clause references medallion_bronze.raw_trips (the bronze table), not samples.nyctaxi.trips directly.
• Check that the WHERE clause uses OR between the conditions, not AND. Using AND would require all conditions to be true at once, which is much more restrictive.
• help me troubleshoot why my data quality query is not returning any rows from my bronze table

Your bronze layer is complete. You have a raw copy of the taxi data and clear evidence of why it cannot be used directly for analysis. Next up, you will build the silver layer to permanently fix these problems and enrich the data with derived columns.

Build the Silver Layer

In the previous step, you queried the bronze table and found real problems hiding in the raw data: null fares, zero-distance trips, and negative amounts. These are exactly the kind of issues that silently corrupt dashboards and reports.

The silver layer permanently fixes these problems and adds derived columns so every downstream consumer gets clean, enriched data. After this step, no one querying your silver table will ever need to write their own WHERE clauses to filter out garbage records.

In this step, get ready to:

• Create the silver table with cleaning filters and three derived columns.
• Verify the silver table has fewer rows than bronze and zero quality issues.

Create the silver table with transformations and filters

The silver table pulls from the bronze table, removes invalid records, and adds three new columns that enrich every row with time-based context. You will build this as a single CREATE OR REPLACE TABLE statement.

• Hover over the bottom of the last cell in your notebook and click + Code to add a new cell.
• Start the silver table transformation by pasting this code into the new cell:

-- Silver Layer: Clean, validate, and enrich
CREATE OR REPLACE TABLE medallion_silver.clean_trips AS
SELECT
  tpep_pickup_datetime,
  tpep_dropoff_datetime,
  fare_amount,
  trip_distance,
  pickup_zip,
  dropoff_zip,
  ROUND(
    (unix_timestamp(tpep_dropoff_datetime) - unix_timestamp(tpep_pickup_datetime)) / 60,
    2
  ) AS trip_duration_minutes,
  CASE
    WHEN hour(tpep_pickup_datetime) BETWEEN 6 AND 9 THEN 'Morning Rush'
    WHEN hour(tpep_pickup_datetime) BETWEEN 10 AND 15 THEN 'Midday'
    WHEN hour(tpep_pickup_datetime) BETWEEN 16 AND 19 THEN 'Evening Rush'
    ELSE 'Off-Peak'
  END AS time_of_day,
  CASE
    WHEN dayofweek(tpep_pickup_datetime) IN (1, 7) THEN 'Weekend'
    ELSE 'Weekday'
  END AS day_type

What do the derived columns do?

This SELECT statement keeps the six original columns you need and adds three new ones:

• trip_duration_minutes calculates how long each trip took by subtracting the pickup timestamp from the dropoff timestamp, converting the result from seconds to minutes.
• time_of_day categorizes each trip into Morning Rush, Midday, Evening Rush, or Off-Peak based on the hour of pickup.
• day_type flags each trip as Weekday or Weekend using DAYOFWEEK, where 1 is Sunday and 7 is Saturday.

• Continue in the same cell by pasting the FROM and WHERE clauses directly below the code you just added:

FROM medallion_bronze.raw_trips
WHERE fare_amount > 0
  AND fare_amount < 200
  AND trip_distance > 0
  AND trip_distance < 100
  AND tpep_pickup_datetime IS NOT NULL
  AND tpep_dropoff_datetime IS NOT NULL
  AND pickup_zip IS NOT NULL;

What do the filters remove?

Each WHERE condition targets a specific quality problem you discovered in the bronze table:

• fare_amount > 0 removes the zero and negative fares.
• fare_amount < 200 removes extreme outliers like $500+ fares that skew averages.
• trip_distance > 0 and trip_distance < 100 remove zero-distance trips and unrealistic 100+ mile entries.
• The three IS NOT NULL checks remove rows with missing timestamps or zip codes.

• Run the cell by pressing Shift+Enter.

The cell completes without returning rows because CREATE OR REPLACE TABLE creates the table silently. You will verify the table contents in the next substep.

Seeing a syntax error?

• Make sure the SELECT portion and the FROM/WHERE portion are in the same cell with no gap between them. They form one SQL statement.
• Check that the semicolon appears only at the very end of the WHERE clause, after pickup_zip IS NOT NULL.
• Confirm medallion_silver is spelled exactly as it was when you created the schema in Step 1.

Still stuck? Help me debug a syntax error in my silver layer CTAS statement.

Verify the silver layer is clean

Time to prove the silver layer actually fixed the problems you found in the bronze table. You will compare row counts and re-run the same quality checks from Step 2.

• Add a new cell by hovering below the current cell and clicking + Code.
• Compare bronze and silver row counts side by side by pasting this query and running the cell:

-- Verify cleaning
SELECT 'Bronze' AS layer, COUNT(*) AS row_count FROM medallion_bronze.raw_trips
UNION ALL
SELECT 'Silver' AS layer, COUNT(*) AS row_count FROM medallion_silver.clean_trips;

You should see two rows in the results: one for Bronze and one for Silver. The Silver row count is lower than Bronze, confirming that your filters removed the invalid records.

Why use UNION ALL for this comparison?

UNION ALL stacks the two count results into a single result table so you can see both numbers at a glance. Each SELECT runs independently and the results appear as labeled rows.

Before you run the next query, predict: do you expect to see any quality issues in the silver table, or should every count be zero?

• Add another new cell and run the same quality-check query from Step 2, this time against the silver table:

-- Verify no quality issues remain
SELECT
  SUM(CASE WHEN fare_amount IS NULL THEN 1 ELSE 0 END) AS null_fares,
  SUM(CASE WHEN fare_amount <= 0 THEN 1 ELSE 0 END) AS non_positive_fares,
  SUM(CASE WHEN trip_distance <= 0 THEN 1 ELSE 0 END) AS non_positive_distances
FROM medallion_silver.clean_trips;

Every column should read 0. The filters you wrote in the CTAS statement eliminated every quality issue you discovered in the bronze layer.

Seeing non-zero counts?

• Check that your WHERE clause uses strict inequality > 0 for fare_amount and trip_distance. Using >= 0 would let zero values slip through.
• Verify that tpep_pickup_datetime IS NOT NULL and tpep_dropoff_datetime IS NOT NULL are both present in your WHERE clause.
• If you edited the CTAS, re-run that cell first, then re-run this check. CREATE OR REPLACE TABLE will rebuild the silver table with your corrected filters.

Still seeing unexpected numbers? Help me figure out why my silver table still has quality issues after filtering.

✔️ Awesome, I've got everything!

Great. Double check you have three cells for this step: the CTAS statement, the row count comparison, and the quality check.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm you have these three cells in order:

• Cell 1: The CREATE OR REPLACE TABLE medallion_silver.clean_trips AS statement with the full SELECT, CASE expressions, FROM, and WHERE clause.
• Cell 2: The UNION ALL row count comparison between Bronze and Silver.
• Cell 3: The quality-check query with three SUM(CASE ...) columns reading from medallion_silver.clean_trips.

Each cell should have a green checkmark or completion indicator showing it ran successfully.

Your silver table is clean, enriched, and ready for downstream consumers. Next up, you will build the gold layer that aggregates this data into business-ready daily summaries that analysts can query in seconds.

Build the Gold Layer

Your silver table is clean, enriched, and free of quality issues. But right now, anyone who wants a daily revenue summary has to write their own GROUP BY query against millions of individual trip records.

The gold layer solves this by pre-computing business-ready aggregations into their own table. Reports and dashboards query the gold table directly, returning results in seconds instead of minutes. This is the layer that business users actually see.

In this step, get ready to:

• Create a daily_trip_summary gold table with pre-computed revenue and trip metrics.
• Query the gold table to answer real business questions about revenue patterns.
• Cross-check gold table numbers against the silver table to verify accuracy.

Create the daily trip summary table

The gold table aggregates millions of cleaned trip records down to one row per combination of date, day type, and time-of-day slot. Each row holds totals and averages that answer common business questions without touching the underlying data.

Why tables instead of views?

You might wonder why the gold layer uses a Delta Lake managed table instead of a SQL view. A view re-executes its underlying query every time someone reads from it. For aggregations over millions of rows, that means every dashboard refresh re-scans the entire silver table.

A materialized table stores the pre-computed results. Queries against it read finished rows, not raw data. The trade-off is that you re-run the table creation when source data changes, but that cost is paid once at pipeline time instead of on every dashboard load.

• Hover below the last cell in your notebook and click + Code to add a new cell.
• Create the gold table by pasting this SQL into the new cell and pressing Shift+Enter to run it:

-- Gold Layer: Business-ready daily summary
CREATE OR REPLACE TABLE medallion_gold.daily_trip_summary AS
SELECT
  DATE(tpep_pickup_datetime) AS trip_date,
  day_type,
  time_of_day,
  COUNT(*) AS total_trips,
  ROUND(AVG(fare_amount), 2) AS avg_fare,
  ROUND(AVG(trip_distance), 2) AS avg_distance,
  ROUND(AVG(trip_duration_minutes), 2) AS avg_duration_minutes,
  ROUND(SUM(fare_amount), 2) AS total_revenue
FROM medallion_silver.clean_trips
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

What does this code do?

• DATE(tpep_pickup_datetime) AS trip_date extracts just the date from each pickup timestamp, collapsing all trips on the same day into one group.
• GROUP BY 1, 2, 3 groups by trip_date, day_type, and time_of_day. Each unique combination becomes one summary row.
• The aggregate functions (COUNT, AVG, SUM) compute totals and averages per group. ROUND(..., 2) keeps currency values to two decimal places.
• total_revenue sums all fares for each group. This is the column analysts and dashboards care about most.

The cell output confirms the table was created. You now have a gold table in your medallion_gold schema.

Seeing a table or schema not found error?

• Make sure you ran the CREATE SCHEMA IF NOT EXISTS medallion_gold command from Step 1. Without the schema, there is nowhere to put the table.
• Confirm the silver table exists by running SELECT COUNT(*) FROM medallion_silver.clean_trips in a separate cell. If this fails, re-run your silver layer code from Step 3 first.
• Still stuck? Help me debug a table not found error when creating my gold table in Databricks.

Query the gold table for business insights

The whole point of a gold table is that it answers business questions instantly. Before you query it, think about what you expect: do weekdays or weekends generate more taxi revenue?

• See the top revenue rows by adding a new cell with + Code and running this query:

-- Verify gold table
SELECT * FROM medallion_gold.daily_trip_summary
ORDER BY total_revenue DESC
LIMIT 10;

You should see 10 rows of clean, aggregated data. Each row shows a trip_date, day_type, time_of_day, total_trips, avg_fare, and total_revenue. No nulls, no messy raw records.

✔️ Awesome, I've got everything!

Great. Double check you have both cells: the CREATE OR REPLACE TABLE cell and the SELECT * ... ORDER BY total_revenue DESC verification cell.

ⓧ I'd like to double check the full code

Here is the full gold layer code across both cells. Make sure yours matches:

-- Gold Layer: Business-ready daily summary
CREATE OR REPLACE TABLE medallion_gold.daily_trip_summary AS
SELECT
  DATE(tpep_pickup_datetime) AS trip_date,
  day_type,
  time_of_day,
  COUNT(*) AS total_trips,
  ROUND(AVG(fare_amount), 2) AS avg_fare,
  ROUND(AVG(trip_distance), 2) AS avg_distance,
  ROUND(AVG(trip_duration_minutes), 2) AS avg_duration_minutes,
  ROUND(SUM(fare_amount), 2) AS total_revenue
FROM medallion_silver.clean_trips
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

-- Verify gold table
SELECT * FROM medallion_gold.daily_trip_summary
ORDER BY total_revenue DESC
LIMIT 10;

Now let's put the gold table to work. The two biggest questions any taxi operations manager asks are: do weekdays or weekends bring in more money, and which time slot commands the highest fares?

• Find out which day type generates more revenue by adding a new cell and running:

-- Which day type generates more revenue?
SELECT
  day_type,
  ROUND(SUM(total_revenue), 2) AS revenue
FROM medallion_gold.daily_trip_summary
GROUP BY day_type;

The results show the total revenue split by Weekday and Weekend. Notice which one is higher. This is exactly the kind of answer a gold table delivers instantly.

• Find out which time slot has the highest average fare by adding another cell and running:

-- Which time slot has the highest average fare?
SELECT
  time_of_day,
  ROUND(AVG(avg_fare), 2) AS overall_avg_fare
FROM medallion_gold.daily_trip_summary
GROUP BY time_of_day
ORDER BY overall_avg_fare DESC;

You should see four rows, one for each time slot: Morning Rush, Midday, Evening Rush, and Off-Peak, sorted from highest to lowest average fare.

Why are gold queries so fast?

Both of those queries returned instantly because they scanned a small summary table instead of millions of individual trip records. That is the gold layer's job: pay the computation cost once at pipeline time so every subsequent query is cheap.

Cross-check gold against silver

A gold table is only useful if its numbers are correct. The best way to trust your aggregations is to pick a specific date and verify the gold table's revenue total matches a manual sum on the silver table.

• Look at the results from your top-10 query above.
• Pick any trip_date value from those results. You will use it to compare gold and silver totals.

Before you run the next query, predict: should the gold total and the silver total match exactly, or will they be slightly different?

• Run the cross-check by adding a new cell and pasting this query, replacing the date placeholder with your chosen date:

-- Cross-check: gold total vs silver total for one date
SELECT 'Gold' AS source,
  SUM(total_revenue) AS total_revenue
FROM medallion_gold.daily_trip_summary
WHERE trip_date = '[[TRIP_DATE="a trip_date from your results"]]'
UNION ALL
SELECT 'Silver' AS source,
  ROUND(SUM(fare_amount), 2) AS total_revenue
FROM medallion_silver.clean_trips
WHERE DATE(tpep_pickup_datetime) = '[[TRIP_DATE="a trip_date from your results"]]';

What does this query do?

The query uses UNION ALL to stack two results side by side. The first half sums total_revenue from the gold table for that date. The second half sums fare_amount directly from the silver table for the same date.

If your gold aggregation is correct, both rows will show the exact same number. A mismatch would mean the gold table's GROUP BY logic missed or double-counted something.

Both rows should show the exact same total_revenue value. The gold aggregation is accurate.

Numbers don't match?

• Make sure you used the same date in both WHERE clauses. A typo in one date will compare two different days.
• Check that you re-ran the gold table creation (CREATE OR REPLACE TABLE) after any changes to the silver table. Stale gold data from an earlier run will not match the current silver table.
• Still seeing a discrepancy? Help me figure out why my gold and silver revenue totals don't match for the same date.

Your medallion pipeline now has all three layers in place: raw bronze data, cleaned and enriched silver data, and business-ready gold aggregations. Next up, you will wire these layers into an automated job that runs the entire pipeline end-to-end with a single click.

Automate the Pipeline with a Lakeflow Job

In the last step, you built the gold layer that pre-computes daily revenue summaries for business users. Your pipeline works end-to-end, from raw data all the way to business-ready aggregates.

Right now, running the pipeline means opening your notebook and manually executing each layer in the right order. Real data teams do not operate this way. Lakeflow Jobs orchestrate your notebooks as a multi-task workflow where each task depends on the previous one completing successfully. In this step, you'll create dedicated notebooks for each layer and chain them into an automated job.

In this step, get ready to:

• Create three dedicated SQL notebooks for your pipeline layers.
• Build a Lakeflow Job with chained task dependencies.
• Run the job and verify the pipeline works end-to-end.

Create dedicated notebooks for each pipeline layer

Lakeflow Jobs run entire notebooks as tasks, so each layer of your pipeline needs its own standalone notebook. You'll create three new notebooks and add the pipeline code you've already written into each one.

• In the sidebar, click New, then Notebook.
• Click the notebook title at the top and rename it to bronze-ingest.
• Set the default language to SQL using the language dropdown next to the notebook name.
• Paste the following code into the first cell:

-- Bronze Layer: Ingest raw data with zero transformations
CREATE OR REPLACE TABLE medallion_bronze.raw_trips
AS SELECT * FROM samples.nyctaxi.trips;

-- Verify ingestion
SELECT COUNT(*) AS bronze_row_count FROM medallion_bronze.raw_trips;

What does this notebook do?

This notebook copies the entire raw taxi dataset into your bronze table with zero transformations. The verification query at the end confirms how many rows were ingested.

• Click Run all to execute the notebook.
• You should see a row count for medallion_bronze.raw_trips in the output.

Not seeing a row count?

Make sure the default language is set to SQL. If the notebook defaulted to Python, the SQL commands will fail.

Check that you pasted the code into the first cell, not below an existing empty cell.

Why isn't my bronze-ingest notebook running?

Now create the silver notebook. This one is longer because it includes the cleaning logic and verification queries.

• In the sidebar, click New, then Notebook.
• Click the notebook title and rename it to silver-transform.
• Set the default language to SQL.
• Paste the silver layer CTAS statement into the first cell:

-- Silver Layer: Clean, validate, and enrich
CREATE OR REPLACE TABLE medallion_silver.clean_trips AS
SELECT
  tpep_pickup_datetime,
  tpep_dropoff_datetime,
  fare_amount,
  trip_distance,
  pickup_zip,
  dropoff_zip,
  ROUND(
    (unix_timestamp(tpep_dropoff_datetime) - unix_timestamp(tpep_pickup_datetime)) / 60,
    2
  ) AS trip_duration_minutes,
  CASE
    WHEN hour(tpep_pickup_datetime) BETWEEN 6 AND 9 THEN 'Morning Rush'
    WHEN hour(tpep_pickup_datetime) BETWEEN 10 AND 15 THEN 'Midday'
    WHEN hour(tpep_pickup_datetime) BETWEEN 16 AND 19 THEN 'Evening Rush'
    ELSE 'Off-Peak'
  END AS time_of_day,
  CASE
    WHEN dayofweek(tpep_pickup_datetime) IN (1, 7) THEN 'Weekend'
    ELSE 'Weekday'
  END AS day_type
FROM medallion_bronze.raw_trips
WHERE fare_amount > 0
  AND fare_amount < 200
  AND trip_distance > 0
  AND trip_distance < 100
  AND tpep_pickup_datetime IS NOT NULL
  AND tpep_dropoff_datetime IS NOT NULL
  AND pickup_zip IS NOT NULL;

What does this code do?

This is the same cleaning and enrichment logic you wrote in Step 3. It filters out invalid records and adds three derived columns: trip_duration_minutes, time_of_day, and day_type.

• Click the + button below the first cell to add a new cell.
• Paste the verification queries into this second cell:

-- Verify cleaning
SELECT 'Bronze' AS layer, COUNT(*) AS row_count FROM medallion_bronze.raw_trips
UNION ALL
SELECT 'Silver' AS layer, COUNT(*) AS row_count FROM medallion_silver.clean_trips;

-- Verify no quality issues remain
SELECT
  SUM(CASE WHEN fare_amount IS NULL THEN 1 ELSE 0 END) AS null_fares,
  SUM(CASE WHEN fare_amount <= 0 THEN 1 ELSE 0 END) AS non_positive_fares,
  SUM(CASE WHEN trip_distance <= 0 THEN 1 ELSE 0 END) AS non_positive_distances
FROM medallion_silver.clean_trips;

Why include verification in the notebook?

When this notebook runs as a Lakeflow Job task, you won't be watching it live. The verification queries record their results in the run output, so you can check them later in the Runs tab.

• Click Run all to execute the notebook.
• You should see the bronze vs. silver row counts and all zeros for quality issues, matching what you saw in Step 3.

Seeing errors in the silver notebook?

Make sure the CTAS statement is in the first cell and the verification queries are in a separate second cell. Databricks runs one SQL statement per cell.

Help me debug my silver-transform notebook

✔️ Awesome, I've got everything!

Great. Make sure both cells ran successfully and you can see the row count comparison in the output.

ⓧ I'd like to double check the full code

Your silver-transform notebook should have two cells. Scroll up and confirm:

• Cell 1 contains the CREATE OR REPLACE TABLE medallion_silver.clean_trips CTAS statement with all the filtering and derived columns.
• Cell 2 contains the two verification queries: the UNION ALL row count comparison and the quality-check SUM(CASE ...) query.

One more notebook to go.

• In the sidebar, click New, then Notebook.
• Click the notebook title and rename it to gold-aggregate.
• Set the default language to SQL.
• Paste the following code into the first cell:

-- Gold Layer: Business-ready daily summary
CREATE OR REPLACE TABLE medallion_gold.daily_trip_summary AS
SELECT
  DATE(tpep_pickup_datetime) AS trip_date,
  day_type,
  time_of_day,
  COUNT(*) AS total_trips,
  ROUND(AVG(fare_amount), 2) AS avg_fare,
  ROUND(AVG(trip_distance), 2) AS avg_distance,
  ROUND(AVG(trip_duration_minutes), 2) AS avg_duration_minutes,
  ROUND(SUM(fare_amount), 2) AS total_revenue
FROM medallion_silver.clean_trips
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

-- Verify gold table
SELECT * FROM medallion_gold.daily_trip_summary
ORDER BY total_revenue DESC
LIMIT 10;

What does this notebook do?

This is the same aggregation you built in Step 4. It groups the cleaned silver data by date, day type, and time slot, then calculates trip and revenue metrics.

• Click Run all to execute the notebook.
• You should see the top 10 rows by total_revenue, matching the results from Step 4.

Gold notebook not returning results?

Confirm the default language is set to SQL. Also verify that the silver-transform notebook ran successfully first, since the gold table reads from medallion_silver.clean_trips.

Help me debug my gold-aggregate notebook

✔️ Awesome, I've got everything!

Great. Make sure you saved this notebook and saw the top 10 revenue rows in the output.

ⓧ I'd like to double check the full code

Your gold-aggregate notebook should contain exactly this code in a single cell:

-- Gold Layer: Business-ready daily summary
CREATE OR REPLACE TABLE medallion_gold.daily_trip_summary AS
SELECT
  DATE(tpep_pickup_datetime) AS trip_date,
  day_type,
  time_of_day,
  COUNT(*) AS total_trips,
  ROUND(AVG(fare_amount), 2) AS avg_fare,
  ROUND(AVG(trip_distance), 2) AS avg_distance,
  ROUND(AVG(trip_duration_minutes), 2) AS avg_duration_minutes,
  ROUND(SUM(fare_amount), 2) AS total_revenue
FROM medallion_silver.clean_trips
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

-- Verify gold table
SELECT * FROM medallion_gold.daily_trip_summary
ORDER BY total_revenue DESC
LIMIT 10;

Create a Lakeflow Job with chained tasks

You now have three standalone notebooks that each handle one layer of the pipeline. The next step is to wire them together so they run in sequence automatically.

Why Lakeflow Jobs instead of Lakeflow Pipelines?

Databricks offers Lakeflow Pipelines (formerly DLT) for declarative ETL, but it uses a different Python/SQL API and adds a layer of abstraction over your queries. Lakeflow Jobs let you stay in pure SQL with notebooks you already understand.

Free Edition also limits you to one active pipeline per type, while Jobs support up to 5 concurrent tasks. For learning the medallion pattern, notebooks plus Jobs gives you more control with less overhead.

• In the sidebar, click Jobs & Pipelines.
• Click Create, then Job.
• Click the Notebook tile to configure the first task.

What if I don't see the Notebook tile?

Click Add another task type and search for Notebook.

Configure the first task for your bronze layer.

• Replace the job name (it defaults to New Job followed by a timestamp) by clicking it and typing medallion-pipeline.
• In the Task name field, enter bronze-ingest.
• In the Source dropdown, select Workspace.
• For Path, use the file browser to find your bronze-ingest notebook, click the notebook name, and click Confirm.
• Click Save task.

Now add the silver task so it runs after bronze completes.

• Click Add task in the task graph, then select Notebook.
• In the Task name field, enter silver-transform.
• For Path, browse to your silver-transform notebook and click Confirm.
• In the Depends on field, confirm that bronze-ingest is listed as a dependency.
• Click Save task.

Add the final gold task.

• Click Add task in the task graph, then select Notebook.
• In the Task name field, enter gold-aggregate.
• For Path, browse to your gold-aggregate notebook and click Confirm.
• In the Depends on field, confirm that silver-transform is listed as a dependency.
• Click Save task.

Your task graph should now show three tasks chained in sequence: bronze-ingest flows into silver-transform, which flows into gold-aggregate.

What do the task dependencies do?

Each Depends on entry tells the job: do not start this task until the upstream task finishes successfully. If the bronze ingest fails, the silver and gold tasks never run. This prevents your pipeline from processing stale or incomplete data.

🙋‍♀️ Don't see the Depends on field?

Click the task name in the task graph to expand its configuration below. The Depends on field appears in the task settings. If it is empty, manually add the upstream task name.

Help me set up task dependencies in my Lakeflow Job

Run the job and verify the pipeline

Your job is configured and ready to run. Before you click the button, think about what should happen.

Do you expect all three tasks to succeed? What would cause one to fail?

• Click Run now in the upper right corner of the job page.

What happens when you click Run now?

Databricks starts the bronze-ingest task first. Once it completes, silver-transform starts automatically. After silver finishes, gold-aggregate runs. The entire pipeline executes without you touching a single notebook.

• Click the Runs tab to see the job execution.
• Click the link in the Start time column to open the run details.
• Confirm that all three tasks show green checkmarks.

Seeing a failed task?

Click the failed task name in the run details to see the error output. Common causes include a notebook path that points to the wrong location, or a notebook with the default language set to Python instead of SQL.

If the silver or gold task failed, check that the upstream task (bronze or silver) completed successfully first. A dependency failure cascades to all downstream tasks.

Help me debug my failed Lakeflow Job run

Now verify the pipeline produced fresh data by querying the gold table directly.

• Return to your development notebook from earlier (the one you used throughout Steps 1-4).
• Verify the gold table contains fresh data by running this query:

-- Verify the pipeline produced correct data
SELECT * FROM medallion_gold.daily_trip_summary
ORDER BY total_revenue DESC
LIMIT 10;

What should I see?

You should see the same top 10 rows by total_revenue that you saw in Step 4. The data is identical because the pipeline re-created the tables from the same source. In production, each run would pick up new data.

You have a working automated pipeline. Your Lakeflow Job runs all three layers in sequence with a single click. No manual notebook execution required.

Secret mission

Build a Zone Leaderboard and Pipeline Health Dashboard

Your pipeline produces one gold table. Real data products serve multiple business views from the same clean data. In this mission, you will build a second gold table that ranks pickup zones by revenue, wire it into your Lakeflow Job as a parallel task, and create a monitoring dashboard that tracks pipeline health across all three medallion layers.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely on Databricks Free Edition, so there are no ongoing costs for any of the resources you created.

Resources you used:

• Three Unity Catalog schemas (medallion_bronze, medallion_silver, medallion_gold) containing Delta Lake managed tables.
• One Lakeflow Job with four chained notebook tasks.
• Four SQL notebooks (bronze-ingest.sql, silver-transform.sql, gold-aggregate.sql, gold-zone-performance.sql).
• One published AI/BI dashboard (pipeline health).

Keep everything running

No action needed. Choose this if you are still actively building on your medallion pipeline or plan to extend it soon.

• Your schemas, tables, notebooks, job, and dashboard stay in your Databricks Free Edition workspace indefinitely at no cost.
• The samples.nyctaxi.trips source table is a built-in sample and remains available in every workspace.

Pause - I'll come back to this later

All of your resources persist at no cost. There is nothing to shut down or pause.

• When you return, find your notebooks in the workspace sidebar.
• Find your Lakeflow Job under Jobs & Pipelines in the sidebar.
• Your schemas and tables remain queryable in any new notebook.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to clean everything up.

• Drop all three schemas and their managed tables by running these commands in any SQL notebook:

DROP SCHEMA medallion_bronze CASCADE;
DROP SCHEMA medallion_silver CASCADE;
DROP SCHEMA medallion_gold CASCADE;

What does CASCADE do?

The CASCADE keyword deletes both the schema metadata and the underlying data files for every managed table inside it. One command removes the schema and all of its tables at once.

• Delete the Lakeflow Job by clicking Jobs & Pipelines in the sidebar, finding your job, clicking the three-dot menu next to it, and selecting Delete.
• Delete your four SQL notebooks (bronze-ingest.sql, silver-transform.sql, gold-aggregate.sql, gold-zone-performance.sql) by right-clicking each one in the workspace sidebar and selecting Move to Trash.
• Delete your pipeline health dashboard by opening it, clicking the three-dot menu, and selecting Move to Trash.

What about the sample data?

The samples.nyctaxi.trips source table is a built-in sample provided by Databricks. It cannot be deleted and does not count against your storage.

Nice Work!

Nice Work!

Nice work! You just built a complete medallion architecture ETL pipeline in Databricks, from raw data ingestion all the way to automated, business-ready analytics.

You've learned how to:

• Built a three-layer medallion pipeline (bronze, silver, gold) using SQL and Delta Lake managed tables organized into separate Unity Catalog schemas.
• Discovered real data quality issues in raw NYC taxi data (nulls, zero-distance trips, negative fares) and fixed them with targeted silver-layer transformations including filtering, derived columns, and outlier removal.
• Automated the entire pipeline as a Lakeflow Job with chained notebook tasks that run the full bronze-to-silver-to-gold flow with one click.
• Secret Mission: Added a ranked zone performance gold table and built a pipeline health dashboard that monitors row counts and data quality across all three medallion layers.

Ready to quiz yourself?