Build a Medallion Lakehouse Pipeline

Transform raw music data into analytics-ready insights using Databricks.

Introduction

30 Second Summary

Every company collects data, but raw data is messy. Fields are missing, values are wrong, and columns are misspelled. Trying to run analytics on raw data gives you garbage results.

In this project, you will build a three-layer data pipeline in Databricks that takes 10,000+ raw music records and progressively refines them into clean, aggregated analytics tables you can query and visualize.

What You'll Build

By the end, you will query your Gold table in the SQL Editor and see a chart of how music tempo has changed decade by decade, powered by a pipeline you built from scratch.

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

A working medallion pipeline (Bronze, Silver, Gold) where you can query decade-by-decade music trends including average tempo, song duration, and number of unique artists from a clean Gold table.
The ability to compare any two versions of your data using Delta Lake time travel, seeing exactly what changed and when your pipeline modified each table.
A SQL Editor query running against your pipeline output on a separate SQL Warehouse compute engine, demonstrating how Databricks decouples storage from compute.
Secret Mission: Automate your pipeline as a scheduled Databricks Job so it runs without you clicking a button.

Are there any prerequisites?

This project assumes basic familiarity with Python and SQL. You do not need prior experience with Databricks, PySpark, or Delta Lake. The project uses the free Databricks Free Edition, so no credit card is required.

Before We Start

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

Set Up Your Databricks Notebook

Before you can write any pipeline code, you need somewhere to write it. A Databricks notebook connected to compute is your development environment for the entire project.

In this step, you'll create a Python notebook, verify that serverless compute is working, and define the table names your pipeline will use across all three medallion layers. Catching compute issues now saves you from mysterious failures later.

In this step, get ready to:

Create a Python notebook in your Databricks workspace.
Verify your serverless compute connection.
Define table name variables for your Bronze, Silver, and Gold layers.

Create a new Python notebook

Databricks notebooks are interactive documents where you write and run code one cell at a time. On Free Edition, your notebook automatically connects to serverless compute the first time you run a cell.

In the left sidebar of your Databricks workspace, click New.
Click Notebook from the menu.
A blank notebook opens. Check the language button just below the notebook name at the top of the page.
If it does not already say Python, click the language button and select Python from the dropdown.

What is serverless compute?

Serverless compute means Databricks manages the underlying machines for you. You do not need to create or configure a cluster.

When you run your first cell, Databricks automatically provisions compute resources behind the scenes. This is the only compute type available on Free Edition.

Verify your compute connection

Your first cell confirms that serverless compute is attached and working by printing your user context. If anything is misconfigured, you will see an error here rather than deep into the pipeline.

Confirm your compute connection by pasting this code into the first cell of your notebook:

# Cell 1: Verify compute and user context
spark.sql("SELECT current_user() AS user, current_catalog() AS catalog, current_schema() AS schema").show()

Before you run this, what do you expect to see if your compute is connected successfully?
Press Shift+Enter to run the cell.

You should see a table with three columns: your email address under user, your default catalog name under catalog, and your default schema under schema.

What does this code do?

The spark.sql() function runs a SQL query on your connected compute. The three functions inside the query return your current username, catalog, and schema from the Databricks session.

If this cell runs successfully, it proves that PySpark is ready and your notebook is connected to compute. Every cell you write from here on uses this same connection.

Cell stuck on connecting?

Serverless compute can take 10-20 seconds to attach on the first run. If it stays stuck for over a minute, refresh your browser tab and try running the cell again.

If you see a quota error, you may have exceeded the Free Edition daily usage limit. Wait until the next day and try again.

Help me debug my Databricks compute connection

Define your table names

Every table you create in Databricks lives in a shared catalog. To avoid conflicts with other users, you will prefix each table name with a sanitized version of your username.

Click the + button below your first cell to add a new cell.
Define your table names by pasting this code into the new cell:

# Cell 2: Define table names
username = spark.sql("SELECT regexp_replace(session_user(), '[^a-zA-Z0-9]', '_')").first()[0]
bronze_table = f"{username}_songs_bronze"
silver_table = f"{username}_songs_silver"
gold_table = f"{username}_songs_gold"

# Clean up any previous runs
spark.sql(f"DROP TABLE IF EXISTS {bronze_table}")
spark.sql(f"DROP TABLE IF EXISTS {silver_table}")
spark.sql(f"DROP TABLE IF EXISTS {gold_table}")

print(f"Bronze: {bronze_table}")
print(f"Silver: {silver_table}")
print(f"Gold: {gold_table}")

Press Shift+Enter to run the cell.

You should see three lines printed with your personalized table names, like john_doe_songs_bronze, john_doe_songs_silver, and john_doe_songs_gold.

What does this code do?

regexp_replace(session_user(), '[^a-zA-Z0-9]', '_') takes your email address and replaces special characters (like @ and .) with underscores, making it safe for table names.
The three f-strings build unique table names by combining your sanitized username with _songs_bronze, _songs_silver, and _songs_gold.
The DROP TABLE IF EXISTS lines clean up any leftover tables from a previous run so you start fresh every time.

Seeing a permission error?

If you cannot drop tables, your default schema may have restricted permissions. On Free Edition this is uncommon. Try refreshing your browser and running Cell 1 again to re-establish the session, then re-run Cell 2.

Help me fix table permission errors in Databricks Free Edition

✔️ Awesome, I've got everything!

Great. Make sure your notebook has two cells, both showing successful output with no errors.

ⓧ I'd like to double check the full code

Here is the full code you should have across both cells in your notebook:

# Cell 1: Verify compute and user context
spark.sql("SELECT current_user() AS user, current_catalog() AS catalog, current_schema() AS schema").show()

# Cell 2: Define table names
username = spark.sql("SELECT regexp_replace(session_user(), '[^a-zA-Z0-9]', '_')").first()[0]
bronze_table = f"{username}_songs_bronze"
silver_table = f"{username}_songs_silver"
gold_table = f"{username}_songs_gold"

# Clean up any previous runs
spark.sql(f"DROP TABLE IF EXISTS {bronze_table}")
spark.sql(f"DROP TABLE IF EXISTS {silver_table}")
spark.sql(f"DROP TABLE IF EXISTS {gold_table}")

print(f"Bronze: {bronze_table}")
print(f"Silver: {silver_table}")
print(f"Gold: {gold_table}")

Your notebook is connected and your table names are ready. Next up, you'll load raw music data into the Bronze layer and see firsthand why raw data needs cleaning before it can answer real questions.

Ingest Raw Data into the Bronze Layer

Your notebook is connected to serverless compute and your table names are locked in.

Now it's time to bring real data into Databricks. In a medallion architecture, the Bronze layer is your landing zone. It stores raw data exactly as it arrives, giving you an audit trail and the ability to reprocess from scratch if anything goes wrong downstream.

In this step, get ready to:

Load raw song data into a Bronze Delta table.
Explore the first rows and confirm your record count.
Attempt a business query and discover why raw data needs cleaning.

Define the schema and ingest raw data

The Million Song Dataset subset lives at /databricks-datasets/songs/data-001/. It's a tab-separated file with 20 columns covering song metadata like artist name, tempo, duration, and year.

To load it reliably, you'll define an explicit schema rather than letting Spark guess column types. This is standard practice for Bronze layer ingestion because it prevents silent type-coercion errors.

Create a new cell in your notebook (click the + button below your last cell) and paste the following imports and schema definition:

# Cell 3: Read raw data and create Bronze table
from pyspark.sql.types import StructType, StructField, StringType, DoubleType, IntegerType
from pyspark.sql.functions import current_timestamp

file_path = "/databricks-datasets/songs/data-001/"

schema = StructType([
    StructField("artist_id", StringType(), True),
    StructField("artist_lat", DoubleType(), True),
    StructField("artist_long", DoubleType(), True),
    StructField("artist_location", StringType(), True),
    StructField("artist_name", StringType(), True),
    StructField("duration", DoubleType(), True),
    StructField("end_of_fade_in", DoubleType(), True),
    StructField("key", IntegerType(), True),
    StructField("key_confidence", DoubleType(), True),
    StructField("loudness", DoubleType(), True),
    StructField("release", StringType(), True),
    StructField("song_hotnes", DoubleType(), True),
    StructField("song_id", StringType(), True),
    StructField("start_of_fade_out", DoubleType(), True),
    StructField("tempo", DoubleType(), True),
    StructField("time_signature", DoubleType(), True),
    StructField("time_signature_confidence", DoubleType(), True),
    StructField("title", StringType(), True),
    StructField("year", IntegerType(), True),
    StructField("partial_sequence", IntegerType(), True)
])

What does this code do?

You're importing PySpark's type system and defining a StructType with all 20 columns from the dataset. Each StructField specifies the column name, its data type, and whether it allows nulls.

The file_path points to a built-in Databricks sample dataset. Every workspace includes it, so there's nothing to download.

Below the schema definition in the same cell, add the code that reads the file and writes it as a Delta table:

raw_df = (spark.read
    .format("csv")
    .schema(schema)
    .option("sep", "\t")
    .load(file_path)
    .withColumn("ingestion_timestamp", current_timestamp())
)

raw_df.write.format("delta").mode("overwrite").saveAsTable(bronze_table)
print(f"Bronze table created: {bronze_table}")
print(f"Record count: {spark.read.table(bronze_table).count()}")

What does this code do?

spark.read.format("csv").option("sep", "\t") reads the tab-separated file using the schema you defined above.
.withColumn("ingestion_timestamp", current_timestamp()) stamps every row with the exact time you loaded it. This is a Bronze layer best practice for tracking when data arrived.
.write.format("delta").mode("overwrite").saveAsTable(bronze_table) writes the DataFrame as a Delta Lake table. Delta is the default table format in Databricks, giving you versioning and time travel automatically.

Run the cell with Shift+Enter.
You should see two printed lines confirming the table name and a record count of 10,000+.

Seeing an AnalysisException or empty count?

If you see a table-already-exists error, confirm your Cell 2 (which drops old tables) ran successfully before this cell.
If the record count is 0, check that your schema has exactly 20 fields in the same order shown above. A mismatch causes all rows to be silently dropped.
Make sure you pasted both code sections into the same cell. The second section references schema and file_path defined in the first.

Help me debug my Bronze table creation.

Explore the Bronze table

You have a record count, but you haven't actually seen the data yet. Displaying a sample lets you verify that columns loaded correctly and the ingestion timestamp is present.

Create a new cell and paste the following to display the first 10 rows:

# Cell 4: Explore Bronze table
display(spark.read.table(bronze_table).limit(10))

Run the cell with Shift+Enter.
You should see a formatted table with 10 rows. Look for columns like artist_name, title, year, duration, and an ingestion_timestamp column at the far right.

What should I see?

You'll see a rich table visualization rendered by Databricks. Scroll horizontally to find all 21 columns (20 from the dataset plus your ingestion timestamp). Some values may look odd. Notice any rows where the year column shows 0. That detail becomes important in a moment.

Attempt a business query on raw data

Your Bronze table has over 10,000 songs. Let's try answering a real business question directly from it: which artists produced the most songs in each decade?

Create a new cell and paste the following query that groups artists by decade:

# Cell 5: Attempt business query on Bronze (observe the data quality problem)
top_artists_raw = spark.sql(f"""
    SELECT 
        artist_name,
        (year - (year % 10)) AS decade,
        COUNT(*) AS song_count
    FROM {bronze_table}
    GROUP BY artist_name, decade
    ORDER BY song_count DESC
    LIMIT 20
""")
display(top_artists_raw)

What does this query do?

(year - (year % 10)) AS decade rounds the year down to its decade (1997 becomes 1990, 2003 becomes 2000).
The query then groups by artist and decade, counts songs per group, and returns the top 20.

Before you run this, what decade do you think will dominate the results? Think about it for a second.

Run the cell with Shift+Enter.

Look at the results. You'll see that decade = 0 dominates the output. Thousands of records in this dataset have year = 0, meaning the year is unknown. The decade calculation turns those into 0, completely polluting your results.

This is the raw data quality problem in action. Your Bronze layer preserves the data faithfully, but faithful preservation means you inherit every flaw. You cannot build reliable analytics on top of unvalidated data.

Why not just fix it in the Bronze layer?

The Bronze layer's job is to be a complete, unmodified record of what arrived. If you edit Bronze, you lose the ability to reprocess or audit the original source. The fix belongs in the next layer: Silver.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your notebook (Databricks auto-saves, but you can press Cmd+S or Ctrl+S to be sure).

ⓧ I'd like to double check the full code

Your notebook should now have 5 cells total. Scroll through and confirm each one is present in this order:

Cell 1: Verify compute and user context (from Step 1).
Cell 2: Define table names (from Step 1).
Cell 3: Read raw data and create Bronze table. Includes imports, schema definition (20 StructFields), reading the CSV with tab separator, adding ingestion_timestamp, and writing as a Delta table.
Cell 4: Explore Bronze table. A single display() call showing the first 10 rows.
Cell 5: Attempt business query on Bronze. A SQL query grouping by artist and decade, limited to 20 results.

You've loaded raw data into your Bronze layer and discovered firsthand why raw data is unusable for analytics. Next up, you'll build the Silver layer to clean up those quality issues and make the data actually useful.

Clean and Validate into the Silver Layer

In the previous step, you tried to answer a business question directly from your Bronze table. The decade=0 problem made the results meaningless.

The Silver layer exists to solve exactly this kind of problem. It applies data quality rules to produce a validated, analysis-ready dataset where every record meets a minimum quality bar.

In this step, get ready to:

Run a data quality report to measure how many records are problematic.
Build the Silver table by filtering out bad records and cleaning column names.
Re-run the decade query against Silver to prove the fix worked.

Run a data quality report on Bronze

Before you start filtering, you need to know the scale of each data quality issue. A quality report counts exactly how many records fail each rule.

Create a new cell below your existing code and paste the following data quality report:

# Cell 6: Data quality report
from pyspark.sql.functions import col

bronze_df = spark.read.table(bronze_table)

print("=== Data Quality Report ===")
print(f"Total records: {bronze_df.count()}")
print(f"Records with year = 0 (unknown): {bronze_df.filter(col('year') == 0).count()}")
print(f"Records with null artist_name: {bronze_df.filter(col('artist_name').isNull()).count()}")
print(f"Records with null song_id: {bronze_df.filter(col('song_id').isNull()).count()}")
print(f"Records with duration <= 0: {bronze_df.filter(col('duration') <= 0).count()}")

What does this code do?

This cell reads your Bronze table and counts records that fail each quality rule.

col('year') == 0 catches songs with an unknown release year. This is the exact problem that broke your decade query.
isNull() checks catch records missing essential identifiers like artist name or song ID.
duration <= 0 flags records that are physically impossible (a song cannot have zero or negative length).

Run the cell with Shift+Enter.
You should see a printed report with the total record count and the number of bad records for each category.

Seeing a NameError for bronze_table?

This means Cell 2 (where you defined the table name variables) has not been run in this session. Scroll up to Cell 2 and run it again, then re-run Cell 6.

Help me debug my data quality report cell.

Build the Silver table with quality filters

Now that you know exactly how many records are problematic, you can build the Silver table by chaining PySpark filters that remove every bad record in one pass.

Create a new cell and paste the Silver layer transformation:

# Cell 7: Build Silver table with data quality filters
from pyspark.sql.functions import col

bronze_df = spark.read.table(bronze_table)

silver_df = (bronze_df
    .filter(col("year") > 0)
    .filter(col("artist_name").isNotNull())
    .filter(col("song_id").isNotNull())
    .filter(col("duration") > 0)
    .withColumnRenamed("song_hotnes", "song_hotness")
    .select(
        "song_id",
        "title",
        "artist_id",
        "artist_name",
        "artist_location",
        "year",
        "duration",
        "tempo",
        "loudness",
        "key",
        "time_signature",
        "song_hotness"
    )
)

silver_df.write.format("delta").mode("overwrite").saveAsTable(silver_table)

silver_count = spark.read.table(silver_table).count()
bronze_count = spark.read.table(bronze_table).count()
print(f"Bronze records: {bronze_count}")
print(f"Silver records: {silver_count}")
print(f"Records filtered out: {bronze_count - silver_count}")
print(f"Data quality improvement: {((bronze_count - silver_count) / bronze_count * 100):.1f}% of records removed")

What does this code do?

This cell does three things: filter, rename, and write.

The four .filter() calls chain together to keep only records that pass ALL quality checks. Any record failing any single check is dropped.
.withColumnRenamed("song_hotnes", "song_hotness") fixes the misspelled column name from the raw dataset so downstream consumers get clean, consistent naming.
.select() keeps only the 12 columns relevant for analytics, dropping internal metadata columns that don't add value.
The final .write.format("delta").mode("overwrite").saveAsTable(silver_table) persists the cleaned data as a new Delta Lake table.

Run the cell with Shift+Enter.
You should see four printed lines: the Bronze count, the Silver count, how many records were filtered out, and the percentage of records removed.

Seeing a table already exists error?

The code uses mode("overwrite") so it should replace the table on re-runs. If you still see an error, scroll up to Cell 2 and run the cleanup commands again, then re-run Cell 7.

Help me troubleshoot my Silver table creation.

Verify Silver with the decade query

The real test of your Silver layer is whether it solves the problem you discovered in Step 2. Time to re-run the same "top artists by decade" query, but pointed at your clean Silver table.

Create a new cell and paste the verification query:

# Cell 8: Verify Silver - re-run top artists query
top_artists_clean = spark.sql(f"""
    SELECT 
        artist_name,
        (year - (year % 10)) AS decade,
        COUNT(*) AS song_count
    FROM {silver_table}
    GROUP BY artist_name, decade
    ORDER BY song_count DESC
    LIMIT 20
""")
display(top_artists_clean)

What does this code do?

This is the exact same query you ran against Bronze in Cell 5. The only difference is the table name: silver_table instead of bronze_table. If your Silver filters worked, the decade=0 rows are gone and you see real decades.

Before you run this cell, think about what you expect to see. Will decade=0 still appear in the results, or will every row show a real decade like 1960, 1970, or 1980?

Run the cell with Shift+Enter.

You should see a table of results where every decade column shows a real decade (1960, 1970, 1980, etc.) with meaningful artist counts. No decade=0 rows polluting the output.

Why does this matter?

You just proved the core value of the medallion architecture. The same query that produced garbage results on Bronze now produces clean, trustworthy analytics on Silver. Anyone querying Silver can trust the data without knowing the cleanup rules.

Still seeing decade=0 in results?

Make sure your query references silver_table and not bronze_table. If you accidentally queried Bronze again, update the table name in the SQL string and re-run.

Help me figure out why my Silver query still shows bad data.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook with all three new cells (6, 7, and 8) visible in order.

ⓧ I'd like to double check the full code

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

Cell 6: Data quality report printing counts for each issue category.
Cell 7: Silver table creation with four filters, the column rename, the select, and the write to Delta. Prints record counts and a percentage.
Cell 8: The top artists by decade query against your Silver table with a display() call.

If any cell is missing, scroll up to the relevant substep heading and re-paste the code.

Your Silver layer is complete. You now have validated, clean data that produces trustworthy analytics. Next up, you will aggregate this data into a Gold table and query it from the SQL Editor using a completely separate compute engine.

Build the Gold Analytics Layer and Query with SQL

Your Silver table proved that data quality rules work. The decade query now returns meaningful results with no year=0 pollution.

The Gold layer takes this further by pre-computing the aggregations that business users and dashboards need. You will also query your Gold table from a completely different compute engine to see a key Databricks concept in action: decoupled compute.

In this step, get ready to:

Create a Gold analytics table with decade-level aggregations.
Query the Gold table from the SQL Editor using a SQL Warehouse.
Visualize music trends as a bar chart.

Create the Gold analytics table

The Gold layer contains pre-computed aggregations tailored to specific business questions. Instead of running complex GROUP BY queries every time a dashboard loads, the Gold table stores the answer once.

Create a new cell in your notebook (Cell 9) and paste the following code to aggregate Silver data by decade:

# Cell 9: Build Gold analytics table
gold_df = spark.sql(f"""
    SELECT 
        (year - (year % 10)) AS decade,
        COUNT(*) AS total_songs,
        COUNT(DISTINCT artist_id) AS unique_artists,
        ROUND(AVG(duration), 2) AS avg_duration_sec,
        ROUND(AVG(tempo), 2) AS avg_tempo_bpm,
        ROUND(AVG(loudness), 2) AS avg_loudness_db
    FROM {silver_table}
    GROUP BY (year - (year % 10))
    ORDER BY decade
""")

gold_df.write.format("delta").mode("overwrite").saveAsTable(gold_table)
print(f"Gold table created: {gold_table}")
display(spark.read.table(gold_table))

What does this code do?

This SQL query groups your cleaned Silver records by decade (1920s, 1930s, etc.) and computes five aggregations per decade.

`total_songs` counts how many songs exist in each decade.
`unique_artists` counts distinct artists per decade using `COUNT(DISTINCT artist_id)`.
`avg_duration_sec`, `avg_tempo_bpm`, and `avg_loudness_db` compute averages rounded to 2 decimal places.

The result is written as a Delta table. This is your analytics-ready Gold table that dashboards and SQL queries can hit directly.

Run the cell with Shift+Enter.
You should see a table with one row per decade and columns for decade, total_songs, unique_artists, avg_duration_sec, avg_tempo_bpm, and avg_loudness_db.

Not seeing the Gold table output?

Make sure Cell 7 (the Silver table) ran successfully before this cell. The Gold query reads from the Silver table.
Check that your silver_table variable is still defined. If you restarted your notebook session, re-run Cell 2 first to redefine all table name variables.

Help me debug my Gold table creation.

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved before continuing.

ⓧ I'd like to double check the full code

Your notebook should now have Cells 1 through 9. Scroll up and confirm:

Cell 1: Verify compute and user context.
Cell 2: Define table names.
Cell 3: Read raw data and create Bronze table.
Cell 4: Explore Bronze table.
Cell 5: Attempt business query on Bronze.
Cell 6: Data quality report.
Cell 7: Build Silver table with data quality filters.
Cell 8: Verify Silver with top artists query.
Cell 9: Build Gold analytics table (the cell you just added).

Query the Gold table from the SQL Editor

Until now, every query ran on serverless notebook compute. The SQL Editor uses a separate SQL Warehouse to execute queries. This is decoupled compute: your data lives in one place, but different engines can read it independently.

What is decoupled compute?

In a traditional database, storage and compute are bundled together. In a lakehouse, they are separated. Your Delta tables sit in storage, and any compute engine (a notebook cluster, a SQL Warehouse, a scheduled job) can query them independently.

This means a data engineer can run PySpark transformations on one engine while a business analyst queries the same tables from the SQL Editor on a completely different engine. Neither blocks the other.

Click SQL Editor in the left sidebar of your Databricks workspace.
The workspace provides a 2X-Small SQL Warehouse that starts automatically when you run a query.

Before you run your query, what do you expect to see? Think about what the Gold table contains: one row per decade with pre-computed averages.

Paste the following query into the SQL Editor and run it with Ctrl+Enter (Windows) or Cmd+Enter (macOS):

SELECT * FROM [[GOLD_TABLE="your gold table name from Cell 2 output e.g. john_doe_songs_gold"]] ORDER BY decade

Where do I find my table name?

Your Gold table name was printed in Cell 2 of your notebook. It follows the pattern yourname_songs_gold. Copy it exactly as shown in that output.

You should see one row per decade (1920, 1930, 1940, etc.) with columns for total_songs, unique_artists, avg_duration_sec, avg_tempo_bpm, and avg_loudness_db.

Seeing TABLE_OR_VIEW_NOT_FOUND?

Double-check that you typed the exact table name from Cell 2's output. It is case-sensitive.
Make sure Cell 9 ran successfully in your notebook. If the Gold table was never created, the SQL Editor cannot find it.
Confirm your SQL Warehouse is running. Look for a green status indicator next to the warehouse name in the SQL Editor header.

Help me fix my SQL Editor query.

Create a bar chart visualization

The SQL Editor has a built-in visualization editor. You can turn any query result into a chart without leaving Databricks.

In the results pane below your query, click the + button above the result table.
Select Visualization to open the visualization editor.
In the Visualization Type drop-down, select Bar.
Set the X column to decade.
Set the Y column to avg_tempo_bpm.
Click Save.

You should see a bar chart showing how average music tempo has changed across the decades. Each bar represents one decade of music.

What does this chart reveal?

The chart visualizes tempo trends in popular music over time. You might notice tempo generally increasing from the mid-20th century onward. This is the kind of insight a data analyst can get instantly from a well-structured Gold table.

Not seeing the chart options?

Make sure you ran the query first. The + button only appears after results are displayed.
If you do not see the Visualization Type drop-down, make sure you selected Visualization from the menu (not Filter or another option).

Help me create my bar chart visualization.

Your pipeline now flows from raw Bronze data through validated Silver records all the way to pre-computed Gold analytics. Next up, you will explore a superpower that Delta Lake gives you: the ability to travel back in time and query any previous version of your data.

Explore Delta Lake Time Travel

Your Gold table is delivering decade-level music insights, and the SQL Editor proved you can query from a separate compute engine. But what happens when you need to correct data in a table that's already been written?

Delta Lake records every change to a table as a new version. This means you can query any historical state of your data, compare what changed between versions, and even recover deleted records. This capability is what separates Delta from regular Parquet files.

In this step, get ready to:

View the full version history of your Silver table.
Make a data correction by deleting incomplete song records.
Use time travel to query and compare historical versions of your data.

View the Silver table's version history

Every Delta table maintains a transaction log that records each operation. The DESCRIBE HISTORY command lets you inspect this log to see when changes happened, what type of operation occurred, and who triggered it.

In a new cell in your notebook, paste and run this code to view the Silver table's history:

# Cell 10: View Delta Lake table history
display(spark.sql(f"DESCRIBE HISTORY {silver_table}"))

What does this code do?

DESCRIBE HISTORY queries the Delta transaction log for your Silver table. It returns a row for every operation ever performed on the table, including the version number, timestamp, operation type, and the user who ran it.

You should see a table with one row showing a WRITE operation at version 0. This is the initial write you performed when creating the Silver table in the previous step.

Delete short-duration songs as a data correction

Your Silver table currently contains songs of all durations. Songs under 60 seconds are likely incomplete samples or intro tracks that skew your analytics. Removing them is a realistic data correction you might make in production.

In a new cell, paste and run this code to delete short-duration songs and confirm the new count:

# Cell 11: Make a data correction (delete incomplete songs)
spark.sql(f"DELETE FROM {silver_table} WHERE duration < 60")
print(f"Silver records after removing songs under 60 seconds: {spark.read.table(silver_table).count()}")

What does this code do?

The DELETE FROM statement removes all rows where duration < 60 (under 60 seconds). Delta Lake doesn't actually erase the data from disk. It marks those rows as deleted in a new version, preserving the previous state for time travel.

The output prints the new record count after the deletion. You should see a number lower than your previous Silver count.

Seeing an AnalysisException?

Make sure you're referencing silver_table (the variable), not a hardcoded table name string. The f-string substitutes your unique table name at runtime.

help me debug my DELETE query on the Silver table

Query historical versions with time travel

The deleted songs are gone from the current version of your Silver table. But Delta Lake still has them stored in version 0. You can query any previous version using the VERSION AS OF syntax.

In a new cell, paste and run this code to compare record counts between the original version and the current version:

# Cell 12: Use Time Travel to compare versions
previous_count = spark.sql(f"SELECT COUNT(*) AS cnt FROM {silver_table} VERSION AS OF 0").first()["cnt"]
current_count = spark.read.table(silver_table).count()

print(f"Version 0 (original Silver): {previous_count} records")
print(f"Current version (after delete): {current_count} records")
print(f"Records removed: {previous_count - current_count}")

What does VERSION AS OF do?

VERSION AS OF 0 tells Spark to read the table as it existed at version 0 (the first write). The current table reflects version 1 (after the delete). By querying both, you get a precise count of how many records were removed.

You should see three printed lines showing the original count, the current count, and the difference between them.

Before you run the next cell, think about this: if you query version 0 for songs with duration under 60, will those rows appear even though you just deleted them from the current table?

In the same cell, add this code below the existing lines, then re-run the cell to display the deleted short-duration songs from the past:

# View deleted records from the past
display(spark.sql(f"""
    SELECT title, artist_name, duration 
    FROM {silver_table} VERSION AS OF 0 
    WHERE duration < 60 
    ORDER BY duration
    LIMIT 10
"""))

What does this query prove?

This query reads the Silver table at version 0 and filters for the exact records you deleted. The fact that they appear proves Delta Lake preserved the historical state. In production, this means you can always audit what changed, recover accidentally deleted data, or reproduce past analyses.

You should see up to 10 rows of songs with durations under 60 seconds. These rows no longer exist in the current version of the table, but time travel lets you access them from version 0.

Getting an empty result?

Make sure you're querying VERSION AS OF 0 (the original write), not VERSION AS OF 1 (after the delete). Version 1 has already had those short-duration songs removed.

help me understand Delta Lake version numbering

In a new cell, paste and run this code to view the updated history log showing both operations:

# Cell 13: View updated history showing both operations
display(spark.sql(f"DESCRIBE HISTORY {silver_table}"))

What should I see?

The history table now shows two rows. Version 0 is the original WRITE operation from when you created the Silver table. Version 1 is the DELETE operation you just performed. Every future modification will add another version to this log.

✔️ Awesome, I've got everything!

Great. Make sure all your cells are saved in the notebook.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these cells are present in order:

Cell 10: View Delta Lake table history (DESCRIBE HISTORY).
Cell 11: Make a data correction (DELETE FROM with duration < 60).
Cell 12: Use Time Travel to compare versions (VERSION AS OF 0 count comparison and display of deleted records).
Cell 13: View updated history showing both operations (DESCRIBE HISTORY again).

Secret mission

Automate Your Pipeline as a Databricks Job

Your pipeline works, but only when you manually run each cell. In production, data pipelines run on a schedule without human intervention. Can you turn your notebook into a Databricks Job that executes end-to-end with a single click?

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 with no ongoing costs.

Resources you used:

Three Delta tables (bronze_table, silver_table, gold_table) stored in your default catalog.
One Python notebook (medallion_pipeline) in your workspace.
One Databricks Job (medallion_pipeline_job), if you completed the Secret Mission.

Keep everything running

No action needed. Choose this if you want to keep exploring or building on your pipeline.

Your Delta tables persist in your workspace indefinitely on Free Edition at no cost.
Your notebook and Job remain available for future runs.
Free Edition accounts may be deleted after prolonged inactivity, so export any notebooks you want to keep long-term.

Pause - I'll come back to this later

There is nothing to pause in this project. Databricks Free Edition uses serverless compute that only runs when you execute code.

Your tables, notebook, and Job remain exactly as you left them.
No compute charges accrue while you are away.
If your account is inactive for an extended period, Databricks may delete it. Export your notebook if you want a local backup.

Delete - I don't want to use this again

Remove all project resources from your workspace and start fresh.

Return to your notebook and add a new cell at the bottom. Run this code to drop all three Delta tables:

spark.sql(f"DROP TABLE IF EXISTS {bronze_table}")
spark.sql(f"DROP TABLE IF EXISTS {silver_table}")
spark.sql(f"DROP TABLE IF EXISTS {gold_table}")
print("All tables dropped.")

Delete the Databricks Job (Secret Mission only): click Jobs in the left sidebar.
Click medallion_pipeline_job to open the job page. Click the menu icon next to the job name and select Delete job.
Delete the notebook: in the workspace sidebar, right-click your notebook and select Move to Trash. Trash contents are permanently deleted after 30 days.

Nice Work!

You just built a complete medallion lakehouse pipeline that transforms raw music data into analytics-ready insights. From messy records with missing years to decade-level trend visualizations, you proved how data quality improves at every layer of the architecture.

You've learned how to:

Build a three-layer medallion pipeline (Bronze, Silver, Gold) using PySpark and Delta Lake, discovering firsthand why raw data needs cleaning before analytics can work.
Query historical versions of your data using Delta Lake time travel (VERSION AS OF), comparing what changed between writes and recovering deleted records.
Run SQL analytics and create visualizations from the SQL Editor using a separate SQL Warehouse, demonstrating how Databricks decouples compute from storage.
Automated your pipeline as a scheduled Databricks Job with serverless compute and manual triggering (Secret Mission).

Ready to quiz yourself?