Build a Delta Lakehouse on Databricks

Build, break, and recover a Delta Lake table using MERGE and time travel.

Introduction

30 Second Summary

Imagine you accidentally delete a column of data at work. In a regular database, that data is gone forever. A lakehouse gives you a time machine for your data, letting you rewind to any previous version and restore what was lost.

In this project, you will build a Databricks notebook that creates a Delta Lake table, intentionally breaks it twice, and recovers it each time using real-world data engineering patterns that employers expect you to know.

What You'll Build

You will have a working notebook that tells the story of your data from ingestion through disaster recovery. Run it in front of someone and they will see you query millions of taxi records, create duplicates on purpose, fix them with a single MERGE command, corrupt an entire table, and restore it by travelling back in time.

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

  • A notebook mixing SQL and PySpark DataFrame cells that query NYC taxi trip data on serverless compute.
  • Your own managed Delta table in Unity Catalog that you created, broke, and recovered using MERGE INTO and time travel.
  • Inline visualizations that let you explore patterns in the data directly from your notebook cells.
  • Secret Mission: Add interactive dropdown widgets to parameterize your analysis by pickup zone.

Are there any prerequisites?

You need basic SQL knowledge (SELECT, WHERE, GROUP BY) and basic Python familiarity (variables, functions, f-strings). You also need a free Databricks account, which you will create in Step 1 if you do not already have one.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters for your data engineering skills.

Set Up Your Notebook and Explore the Workspace

Before you can write any data engineering code, you need a notebook connected to live compute. Databricks organizes everything inside a workspace, and your notebook is where every command runs.

You'll start by exploring how Unity Catalog organizes data assets into a three-level hierarchy. Understanding this structure now makes every later step easier to follow.

In this step, get ready to:
  • Explore the Unity Catalog sidebar and locate the sample NYC taxi data.
  • Create a new Python notebook connected to serverless compute.
  • Run your first cell to confirm everything is working.
Explore the Unity Catalog sidebar

Unity Catalog is how Databricks organizes all your data assets. It uses a three-level namespace: catalog, schema, then table.

  • Sign into your Databricks Free Edition workspace.
  • In the left sidebar, click Catalog.
  • You'll see the Unity Catalog hierarchy listing your available catalogs.
  • Expand the samples catalog by clicking the arrow next to it.
  • Inside samples, expand the nyctaxi schema.
  • Confirm you can see the trips table listed inside nyctaxi.

This trips table contains taxi trip records for New York City. It is the dataset you will query and transform throughout this project.

What is the samples catalog?

Databricks provides a read-only samples catalog with pre-loaded datasets for learning and experimentation. You can query anything here, but you cannot modify it.

Later, you'll create your own writable table in your workspace catalog so you can practice INSERT, UPDATE, and MERGE operations.

Create a new notebook

Notebooks are the primary coding environment in Databricks. Each notebook can mix Python and SQL cells in the same document.

  • Click New in the left sidebar.
  • Click Notebook.
  • A new untitled notebook opens. Click the title at the top (it says Untitled Notebook) and rename it to delta-lakehouse-workflow.

On Free Edition, the notebook auto-attaches to serverless compute. You do not need to configure or start a cluster manually.

What is serverless compute?

Serverless compute means Databricks manages the infrastructure for you. When you run a cell, a compute session starts automatically in the background. You never provision, configure, or pay for idle machines.

Don't see the New button?

Make sure the left sidebar is expanded. If you see only icons, click the hamburger menu (three horizontal lines) at the top left to expand it.

help me find the New button in my Databricks sidebar

Run your first cell

Your notebook is open but you have not proven that compute is actually live. A single print statement confirms the connection works.

  • In the first cell of your notebook, type the following code:
# Cell 1: Verify compute is running
print("Hello Lakehouse!")

What does this code do?

The comment labels this as Cell 1 so you can track your notebook's structure as it grows across steps. The print() function sends text to the cell output area, confirming your compute session is alive and connected.

Before you run this cell, what do you expect to see appear below it?

  • Press Shift+Enter to run the cell.

You should see Hello Lakehouse! printed directly below the cell. This confirms serverless compute attached successfully and your notebook is ready for real work.

Not seeing any output?

The first cell run on serverless compute can take a few seconds while the session spins up. Wait up to 30 seconds for the output to appear.

If you see an error about compute, make sure you are on Databricks Free Edition (serverless is auto-attached). You should not need to manually attach a cluster.

help me troubleshoot why my Databricks notebook cell is not producing output

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Your notebook should have one cell with this exact code:

# Cell 1: Verify compute is running
print("Hello Lakehouse!")

Your workspace is set up, you know where the data lives, and your notebook is connected to compute. Next up, you'll query millions of taxi trip records using PySpark DataFrames and build your first visualization.

Query Sample Data with PySpark DataFrames

Your notebook is live and connected to serverless compute. Now it is time to get data on screen.

Running SQL is something any database can do. PySpark DataFrames are what make Databricks powerful for data engineering at scale. In this step you will query NYC taxi trip data with both SQL and the DataFrame API, then build a visualization from the results.

In this step, get ready to:
  • Query the sample taxi data with SQL to see what columns and values exist.
  • Load the same data into a PySpark DataFrame and compute an aggregation.
  • Create an inline bar chart visualization from the aggregation results.
Query the sample data with SQL and PySpark DataFrames

The samples catalog contains pre-loaded datasets you explored in the Catalog sidebar. You will start by running a quick SQL query to see the raw taxi trip columns, then switch to the PySpark DataFrame API for a richer aggregation.

  • Hover below Cell 1 and click + Code to add a new cell.
  • Click the language selector at the top-left of the new cell (it says Python by default) and change it to SQL.
  • Query the sample taxi data by typing the following in the new cell:
-- Cell 2: Query sample data with SQL
SELECT * FROM samples.nyctaxi.trips LIMIT 10
  • Press Shift+Enter to run the cell.

You should see a formatted table of 10 taxi trip rows with columns like tpep_pickup_datetime, fare_amount, trip_distance, and pickup_zip displayed below the cell.

Why change the cell language?

Databricks notebooks let each cell run in its own language. By switching a cell to SQL, everything you type in that cell is executed as SQL while the rest of your notebook stays Python.

Not seeing results?

Make sure you changed the cell language to SQL using the dropdown. If the cell still shows Python, the SQL statement will throw a syntax error.

If you see a connection error, your serverless compute may have gone idle. Click the cell and press Shift+Enter again to reattach.

Help me troubleshoot this

SQL gave you a quick peek at the data. Now load it into a PySpark DataFrame to unlock programmatic transformations.

  • Add another new cell below Cell 2 by hovering below it and clicking + Code.
  • Leave this cell's language as Python (the default).
  • Load the taxi data into a DataFrame and inspect its shape by typing the following:
# Cell 3: Load data with PySpark DataFrame API
trips_df = spark.read.table("samples.nyctaxi.trips")
print(f"Total rows: {trips_df.count()}")
trips_df.printSchema()
  • Press Shift+Enter to run the cell.

You should see the total row count printed (millions of rows) followed by the table schema listing every column name and data type.

What does this code do?

spark.read.table("samples.nyctaxi.trips") loads the entire table into a distributed DataFrame without copying data. The DataFrame is a lazy reference to the underlying Delta table.

.count() triggers computation and returns the total number of rows. .printSchema() displays column names and types so you know what you are working with.

Seeing a long wait on count?

The .count() call scans all rows. On serverless compute this may take 10-30 seconds for millions of records. This is normal for the first heavy operation in a session.

Help me check if this is expected

You have the raw data loaded. Now compute an aggregation to answer a real question: which pickup zip codes see the highest fare amounts?

  • Add one more cell below Cell 3 by clicking + Code.
  • Compute average fare and distance grouped by pickup zip by typing the following:
# Cell 4: Aggregate with DataFrame API
from pyspark.sql.functions import avg, col, count

fare_by_zip = (
    trips_df
    .filter(col("fare_amount") > 0)
    .groupBy("pickup_zip")
    .agg(
        avg("fare_amount").alias("avg_fare"),
        avg("trip_distance").alias("avg_distance"),
        count("*").alias("trip_count")
    )
    .orderBy(col("trip_count").desc())
    .limit(20)
)

display(fare_by_zip)
  • Press Shift+Enter to run the cell.

You should see a formatted table with 20 rows showing pickup_zip, avg_fare, avg_distance, and trip_count columns.

What does this aggregation do?

  • .filter(col("fare_amount") > 0) removes trips with zero or negative fares (bad data).
  • .groupBy("pickup_zip") groups all trips by their pickup zip code.
  • .agg(...) computes average fare, average distance, and total trip count for each group.
  • .orderBy(col("trip_count").desc()).limit(20) sorts by busiest zip codes and keeps the top 20.
  • display() renders the result as a rich, formatted table directly in the notebook.

Seeing a NameError for trips_df?

Cell 4 depends on the trips_df variable created in Cell 3. Make sure you ran Cell 3 first. If you restarted your session, re-run Cell 3 before Cell 4.

Help me fix this NameError

Create an inline visualization

The table from Cell 4 answers your question numerically. A bar chart makes the pattern jump off the screen.

  • In the Cell 4 results area, click the + icon next to the Table tab.
  • Select Visualization.
  • Choose Bar as the chart type.
  • Set the X axis to pickup_zip and the Y axis to avg_fare.
  • Click Save to keep the visualization attached to the cell.

You should now see a bar chart showing average fare amount by pickup zip code. This is your first visible deliverable.

Why use inline visualizations?

Databricks notebooks store visualizations alongside the code that produced them. Anyone who opens your notebook sees both the logic and the result without re-running cells. This makes notebooks self-documenting artifacts.

Don't see the + icon next to Table?

The visualization controls only appear after a cell has been run and results are displayed. Make sure Cell 4 has finished executing and you can see the table output.

Help me find the visualization option

Before you move on, predict what you think will happen if you change the Y axis to trip_count instead. Do you expect the same zip codes to dominate?

✔️ Awesome, I've got everything!

Great. You have four cells in your notebook (including the original Hello Lakehouse! cell) and a bar chart visualization. Make sure all cells have run successfully.

ⓧ I'd like to double check the full code

Here is what each cell should contain. Compare yours against these:

Cell 1 (Python):

# Cell 1: Verify compute is running
print("Hello Lakehouse!")

Cell 2 (SQL):

-- Cell 2: Query sample data with SQL
SELECT * FROM samples.nyctaxi.trips LIMIT 10

Cell 3 (Python):

# Cell 3: Load data with PySpark DataFrame API
trips_df = spark.read.table("samples.nyctaxi.trips")
print(f"Total rows: {trips_df.count()}")
trips_df.printSchema()

Cell 4 (Python):

# Cell 4: Aggregate with DataFrame API
from pyspark.sql.functions import avg, col, count

fare_by_zip = (
    trips_df
    .filter(col("fare_amount") > 0)
    .groupBy("pickup_zip")
    .agg(
        avg("fare_amount").alias("avg_fare"),
        avg("trip_distance").alias("avg_distance"),
        count("*").alias("trip_count")
    )
    .orderBy(col("trip_count").desc())
    .limit(20)
)

display(fare_by_zip)

You have data on screen and a visualization telling a story. Next up, you will create your own Delta table so you can practice write operations like INSERT, UPDATE, and MERGE.

Create a Managed Delta Table in Unity Catalog

You have been querying NYC taxi data from the samples catalog. That catalog is read-only. You can read from it all day, but you cannot INSERT, UPDATE, or DELETE anything.

To practice real data engineering operations like MERGE and RESTORE, you need your own table in a catalog where you have write access. In this step, you'll create a managed Delta table inside your workspace catalog using Unity Catalog's three-level namespace.

In this step, get ready to:
  • Identify your workspace catalog name using SELECT current_catalog().
  • Create a managed Delta table with 10,000 rows from the NYC taxi sample data.
  • Verify the table exists in the Catalog Explorer UI.
Identify your workspace catalog name

Unity Catalog organizes every data asset in a three-level namespace: catalog.schema.table. Free Edition workspaces come with a workspace catalog (named after your workspace) and a default schema where you can create tables.

Your first task is to find out what your workspace catalog is called.

  • Hover below Cell 4 in your notebook until the + icon appears, then click it to add a new cell.
  • At the top of the new cell, type %sql on the first line to switch the cell language to SQL.
  • Add the following query below the %sql line and press Shift+Enter to run the cell:
-- Cell 5: Identify your workspace catalog
SELECT current_catalog()

What does this query do?

current_catalog() returns the name of the catalog your session is using by default. On Free Edition, this is your workspace catalog. It shares the same name as your workspace.

Every table you create in the next cells will live at <your_catalog>.default.<table_name>. This is the three-level namespace that Unity Catalog enforces.

You should see a single row with your workspace catalog name in the output. Make a mental note of this value. You'll use it in the next cell.

Seeing an empty result?

Make sure you typed %sql on the very first line of the cell, before the comment. Without it, the notebook tries to run SQL as Python and returns an error.

If you see an error about compute not being available, wait a few seconds and try again. Serverless compute occasionally takes a moment to resume.

Still stuck? Help me figure out why my SQL cell returns an error in Databricks.

Create your managed Delta table

The samples catalog is shared and locked down. Creating your own table gives you a sandbox where you can INSERT, UPDATE, break things, and recover them.

  • Add a new cell below Cell 5 by hovering below it and clicking the + icon.
  • Type %sql on the first line to set the cell to SQL.
  • Create your managed Delta table by adding this query below the %sql line. Replace <your_catalog> with the catalog name you saw in Cell 5:
-- Cell 6: Create a managed Delta table from sample data
-- Replace <your_catalog> with the result from Cell 5
CREATE TABLE IF NOT EXISTS <your_catalog>.default.nyc_taxi_subset
AS SELECT * FROM samples.nyctaxi.trips LIMIT 10000
  • Press Shift+Enter to run the cell.

What does this code do?

CREATE TABLE IF NOT EXISTS creates a new managed Delta table only if it does not already exist. This makes the cell safe to re-run without errors.

AS SELECT * FROM samples.nyctaxi.trips LIMIT 10000 copies 10,000 rows from the shared sample dataset into your new table. You now own this data and can modify it freely.

Before you verify the row count, what number do you expect to see?

  • Add one more cell below Cell 6 using the same process (hover, click +, type %sql on the first line).
  • Run this query to verify your table was created. Replace <your_catalog> with your catalog name:
-- Cell 7: Verify the table exists and has data
SELECT COUNT(*) AS row_count FROM <your_catalog>.default.nyc_taxi_subset

You should see row_count of 10000 in the output. That confirms your table exists and has the expected data.

Seeing 0 rows or a table-not-found error?

Double-check that the catalog name in Cell 6 and Cell 7 matches exactly what Cell 5 returned. Even a small typo creates a different namespace path.

If you see a permission error, confirm you are writing to the default schema inside your workspace catalog. Free Edition gives you write access there automatically.

Need help? My CREATE TABLE in Databricks Free Edition is not working.

Verify the table in Catalog Explorer

The query confirmed the row count. Now confirm the table is visible in the Catalog Explorer UI, which is where you browse all data assets in your workspace.

  • Click Catalog in the left sidebar.
  • Expand your workspace catalog (the name from Cell 5).
  • Expand the default schema underneath it.
  • Confirm that nyc_taxi_subset appears in the list as a managed Delta table.

You should see nyc_taxi_subset listed alongside any other tables in the schema. This confirms your three-level namespace is working: your catalog, the default schema, and your new table.

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook with all seven cells.

ⓧ I'd like to double check the full code

Your notebook should now contain the following cells in order. Scroll through your notebook and confirm each one is present:

  • Cell 1 (Python): print("Hello Lakehouse!")
  • Cell 2 (SQL): SELECT * FROM samples.nyctaxi.trips LIMIT 10
  • Cell 3 (Python): loads trips_df with spark.read.table(), prints row count and schema.
  • Cell 4 (Python): imports avg, col, count, builds fare_by_zip aggregation, calls display().
  • Cell 5 (SQL): SELECT current_catalog().
  • Cell 6 (SQL): CREATE TABLE IF NOT EXISTS <your_catalog>.default.nyc_taxi_subset AS SELECT * FROM samples.nyctaxi.trips LIMIT 10000.
  • Cell 7 (SQL): SELECT COUNT(*) AS row_count FROM <your_catalog>.default.nyc_taxi_subset.

Your table is live and you own it. Next up, you'll experience what happens when duplicate data arrives in a real pipeline and learn the MERGE pattern that prevents it.

Break Your Table with Duplicates, Then Fix with MERGE

Your table is live with 10,000 rows. In real data pipelines, the same records often arrive more than once. A late-running job retries, a source system replays events, or a scheduler fires twice.

What happens if you just INSERT those repeated records? You get duplicates. In this step, you will create that problem on purpose, feel the pain, then learn the correct fix: MERGE INTO, the industry-standard pattern for safely upserting data without creating duplicates.

In this step, get ready to:
  • Simulate duplicate data arriving and observe the row count inflate.
  • Use RESTORE to undo the damage and return to a clean state.
  • Apply a MERGE statement that handles duplicates correctly.
Simulate duplicate data arriving

Imagine a source system replays 500 records that your table already contains. A naive approach is to INSERT them directly.

  • Add a new SQL cell below Cell 7 and paste this code to insert 500 existing rows back into your table:
-- Cell 8: Simulate duplicate data arriving (THE PROBLEM)
INSERT INTO <your_catalog>.default.nyc_taxi_subset
SELECT * FROM <your_catalog>.default.nyc_taxi_subset LIMIT 500

What does this code do?

This query reads 500 rows from your own table and inserts them right back in. Every inserted row is an exact copy of one that already exists. This is the simplest possible simulation of a source system replaying data.

  • Press Shift+Enter to run Cell 8.

Before you check the row count, predict: how many rows do you think the table has now?

  • Add another SQL cell and run this query to check the count:
-- Cell 9: Observe the duplicates
SELECT COUNT(*) AS row_count FROM <your_catalog>.default.nyc_taxi_subset
-- Expected: 10500 (duplicates created!)

The count jumped from 10,000 to 10,500. Those 500 extra rows are pure duplicates. This is exactly the problem that plagues real data pipelines.

Still seeing 10,000 rows?

Make sure you ran Cell 8 before Cell 9. Check the cell output area of Cell 8 for a success message confirming rows were written.

help me figure out why my INSERT didn't change the row count

Fix the table with RESTORE, then view its history

Your table is now corrupted with duplicates. Before you learn the correct way to handle incoming data, you need to undo the damage. Delta Lake keeps a version log of every change, so you can roll back to any previous state.

  • Add a new SQL cell and run this to view your table's version history:
-- Cell 10: View table history
DESCRIBE HISTORY <your_catalog>.default.nyc_taxi_subset

You should see two versions listed: version 0 (the original CREATE) and version 1 (the INSERT that added duplicates).

What is DESCRIBE HISTORY?

Every operation on a Delta table (INSERT, UPDATE, DELETE, MERGE, RESTORE) creates a new version in the Delta log. DESCRIBE HISTORY shows you the full audit trail: version numbers, timestamps, operation types, and who made each change.

  • Now restore your table to version 0 (the clean state before duplicates) by adding a new SQL cell and running:
-- Cell 11: Restore to the clean state (version 0)
RESTORE TABLE <your_catalog>.default.nyc_taxi_subset TO VERSION AS OF 0

What does RESTORE do?

RESTORE TABLE rolls your table back to a previous version. It does not delete the history. It creates a NEW version that matches the old state. Your Delta log now has version 0 (CREATE), version 1 (INSERT), and version 2 (RESTORE to v0).

  • Verify the restoration by adding a new SQL cell and running:
-- Cell 12: Verify restoration
SELECT COUNT(*) AS row_count FROM <your_catalog>.default.nyc_taxi_subset
-- Expected: 10000 (back to original)
  • Confirm the output shows 10,000 rows. Your table is clean again.

Still seeing 10,500 rows?

Double check that Cell 11 ran successfully. Look for a confirmation message in its output area. If it shows an error, make sure the version number is 0 (the original version from Cell 10's history output).

help me fix my RESTORE TABLE command

Use MERGE to safely upsert without duplicates

The table is clean again. Now imagine those 500 records arrive once more, but this time some of them have updated fare amounts. You need to update the rows that already exist and insert only truly new ones. This is called an upsert (update + insert), and MERGE INTO handles it in a single atomic operation.

  • Add a new Python cell to create the incoming data with modified fares:
# Cell 13: Create incoming data with modified fares for MERGE demo
from pyspark.sql.functions import lit

catalog_name = spark.sql("SELECT current_catalog()").collect()[0][0]
table_name = f"{catalog_name}.default.nyc_taxi_subset"

incoming_df = (
    spark.read.table(table_name)
    .limit(500)
    .withColumn("fare_amount", col("fare_amount") + lit(5.0))
)

incoming_df.createOrReplaceTempView("incoming_trips")
print(f"Incoming rows: {incoming_df.count()}")

What does this code do?

This cell reads 500 rows from your table and adds $5.00 to each fare_amount. It then registers these modified rows as a temporary view called incoming_trips so the next SQL cell can reference them. This simulates incoming data where some values have changed.

  • Press Shift+Enter to run Cell 13. You should see Incoming rows: 500 printed below the cell.

Seeing a NameError for col?

The col function was imported in Cell 4 earlier in your notebook. If you restarted your compute or cleared state, re-run Cell 4 first to make col available again.

help me fix a NameError in my PySpark cell

  • Now add a new SQL cell and run the MERGE statement that matches incoming rows to existing rows and updates them in place:
-- Cell 14: MERGE - upsert without creating duplicates
MERGE INTO <your_catalog>.default.nyc_taxi_subset AS target
USING incoming_trips AS source
ON target.tpep_pickup_datetime = source.tpep_pickup_datetime
   AND target.tpep_dropoff_datetime = source.tpep_dropoff_datetime
   AND target.pickup_zip = source.pickup_zip
   AND target.dropoff_zip = source.dropoff_zip
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

What does MERGE INTO do?

MERGE INTO compares every row in source against target using the ON condition. If a source row matches an existing target row, it updates that row. If no match exists, it inserts the row as new.

The ON clause uses pickup datetime, dropoff datetime, and zip codes as a composite key. All 500 incoming rows match existing rows, so MERGE updates their fares instead of creating duplicates.

  • Press Shift+Enter to run Cell 14.

Before you check the final count, predict: did MERGE add rows to your table, or keep the count at 10,000?

  • Add a final SQL cell and run this to confirm no duplicates were created:
-- Cell 15: Verify no duplicates were created
SELECT COUNT(*) AS row_count FROM <your_catalog>.default.nyc_taxi_subset
-- Expected: 10000 (same count, but 500 rows have updated fares)

The count is still 10,000. MERGE updated the 500 matching rows with the new fare amounts instead of blindly inserting duplicates. This is the correct pattern for incremental data loading.

Why MERGE instead of INSERT with deduplication?

You could INSERT all rows and then deduplicate afterwards with a separate DELETE or DISTINCT query. But MERGE handles both new and updated records in a single atomic operation. No window exists where the table contains duplicates.

This is the pattern that production data pipelines use for exactly this reason.

Seeing more than 10,000 rows?

If your count is higher than 10,000, the MERGE ON condition may not be matching correctly. Double check that all four columns in the ON clause match exactly: tpep_pickup_datetime, tpep_dropoff_datetime, pickup_zip, and dropoff_zip.

help me debug my MERGE statement that created duplicates

✔️ Awesome, I've got everything!

Great. Double check you have Cells 8 through 15 all showing successful output in your notebook.

ⓧ I'd like to double check the full code

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

  • Cell 8: INSERT INTO that duplicates 500 rows.
  • Cell 9: SELECT COUNT(*) showing 10,500 (the problem).
  • Cell 10: DESCRIBE HISTORY showing your version log.
  • Cell 11: RESTORE TABLE back to version 0.
  • Cell 12: SELECT COUNT(*) confirming 10,000 rows restored.
  • Cell 13: Python cell creating incoming_trips temp view with 500 modified-fare rows.
  • Cell 14: MERGE INTO that upserts without duplicates.
  • Cell 15: SELECT COUNT(*) confirming 10,000 rows (no duplicates).

You just experienced the duplicate problem firsthand and solved it with MERGE. Next up, you will intentionally corrupt your table with a bad UPDATE and use Delta Lake's time travel to recover it.

Time Travel and Disaster Recovery

In the last step, you used MERGE to safely upsert data without creating duplicates. Your table is clean and at 10,000 rows.

You used RESTORE once already (to undo the duplicate INSERT), but you have not explored Delta Lake's full versioning power. In this step, you will intentionally corrupt your table with a bad UPDATE, then use time travel to compare versions and restore the data. This is the disaster recovery workflow that makes Delta Lake different from a regular database.

In this step, get ready to:
  • Corrupt your table on purpose with a bad UPDATE statement.
  • Use time travel to query historical versions without modifying the table.
  • Restore the table to its pre-corruption state and view the full audit trail.
Corrupt the table on purpose

In production, bad UPDATE statements happen. Someone forgets a WHERE clause, or a script runs against the wrong table. You are going to simulate exactly this scenario by zeroing out every fare in your table.

  • Create a new cell below Cell 15. Change the cell language to SQL.
  • Run this UPDATE statement to set every fare to zero:
-- Cell 16: DISASTER - corrupt all fares
UPDATE <your_catalog>.default.nyc_taxi_subset SET fare_amount = 0

What does this code do?

This UPDATE has no WHERE clause, so it affects every single row. Every fare in your 10,000-row table is now zero. In a real scenario, this is the kind of mistake that causes data incidents.

Before you check the damage, what do you think the average fare is now?

  • Create a new SQL cell below Cell 16 and run this query to confirm the damage:
-- Cell 17: Confirm the damage
SELECT AVG(fare_amount) AS avg_fare FROM <your_catalog>.default.nyc_taxi_subset
-- Expected: 0.0 (everything is broken)

You should see 0.0 as the average fare. Your table is broken. In a traditional database, this would be a disaster requiring a backup restore. Delta Lake gives you a better option.

Seeing an error instead of 0.0?

Make sure you replaced <your_catalog> with the actual catalog name from Cell 5. Both Cell 16 and Cell 17 need to reference the same table.

Help me debug my UPDATE statement on my Delta table.

Use time travel to peek at the table before corruption

Delta Lake keeps a versioned log of every change. You can query any historical version without modifying the current state. This lets you confirm what the data looked like before the disaster, which builds confidence before you run a RESTORE.

  • Create a new SQL cell and run DESCRIBE HISTORY to see the full version log:
-- Cell 18: View history to find the pre-corruption version
DESCRIBE HISTORY <your_catalog>.default.nyc_taxi_subset

What should I see?

You should see a table listing every operation on your table. Look for these versions:

  • Version 0: CREATE TABLE.
  • Version 1: INSERT (the duplicate batch from Step 4).
  • Version 2: RESTORE (back to v0).
  • Version 3: MERGE (the upsert from Step 4).
  • Version 4: UPDATE (the disaster you just ran).

Version 3 is the last good state before the corruption. That is the version you will query next.

Now you know version 3 is the pre-corruption state. Time travel lets you query that version directly without changing anything about the current table.

  • Create a new SQL cell and query the historical version using VERSION AS OF:
-- Cell 19: Time travel - peek at the table before corruption
SELECT AVG(fare_amount) AS avg_fare
FROM <your_catalog>.default.nyc_taxi_subset VERSION AS OF 3
-- Use the version number BEFORE the UPDATE from Cell 18

What does VERSION AS OF do?

The VERSION AS OF clause reads the table as it existed at a specific version number. This is a read-only operation. Your current table (version 4 with all zeroes) is untouched.

This is the audit and debugging use case for time travel. You can investigate what happened without risk.

You should see a non-zero average fare. The data still exists in the version history. Your current table is still corrupted (all zeroes), but you have just confirmed the good data is recoverable.

Seeing 0.0 from the time travel query?

Double-check the version number. If your DESCRIBE HISTORY output shows the MERGE at a different version than 3, use that version number instead. The version you want is the one immediately before the UPDATE operation.

Help me figure out the right version number for time travel.

Restore the table to its pre-corruption state

You have confirmed the good data exists at version 3. Now you will use RESTORE TABLE to roll the table back to that version. This creates a new version (version 5) whose contents match the old version 3.

  • Create a new SQL cell and run the RESTORE command:
-- Cell 20: Restore to the pre-corruption version
RESTORE TABLE <your_catalog>.default.nyc_taxi_subset TO VERSION AS OF 3
-- Use the same version number as Cell 19

What does RESTORE TABLE do?

RESTORE does not delete history. It creates a brand new version (v5) whose data matches the target version (v3). Every intermediate version (including the disaster at v4) remains in the log for auditing.

Before you verify, what do you expect the average fare to be now? Will it be 0.0, or the non-zero value you saw in the time travel query?

  • Create a new SQL cell and check the current average fare:
-- Cell 21: Verify recovery
SELECT AVG(fare_amount) AS avg_fare FROM <your_catalog>.default.nyc_taxi_subset
-- Expected: Non-zero average (data recovered!)

You should see a non-zero average fare matching the value from Cell 19. Your table is fully recovered.

  • Create one final SQL cell and run DESCRIBE HISTORY to see the complete audit trail:
-- Cell 22: Final audit trail
DESCRIBE HISTORY <your_catalog>.default.nyc_taxi_subset

What should I see?

Your history now shows six versions (v0 through v5). Every operation you performed across this entire project is logged: CREATE, INSERT, RESTORE, MERGE, UPDATE, and RESTORE again. This is the full audit trail that Delta Lake maintains automatically.

Average fare is still 0.0 after RESTORE?

Make sure Cell 20 ran without errors. Check that the version number in your RESTORE matches the version you used in the VERSION AS OF query (Cell 19). If you restored to the wrong version, run DESCRIBE HISTORY again to find the correct one and re-run RESTORE.

Help me restore my Delta table to the correct version.

✔️ Awesome, I've got everything!

Double check you have Cells 16 through 22 all showing successful output in your notebook.

ⓧ I'd like to double check the full code

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

  • Cell 16: UPDATE <your_catalog>.default.nyc_taxi_subset SET fare_amount = 0.
  • Cell 17: SELECT AVG(fare_amount) AS avg_fare showing 0.0.
  • Cell 18: DESCRIBE HISTORY showing versions 0 through 4.
  • Cell 19: SELECT AVG(fare_amount) ... VERSION AS OF 3 showing a non-zero average.
  • Cell 20: RESTORE TABLE ... TO VERSION AS OF 3.
  • Cell 21: SELECT AVG(fare_amount) showing a non-zero average (matching Cell 19).
  • Cell 22: DESCRIBE HISTORY showing versions 0 through 5.

You just intentionally corrupted a 10,000-row table and recovered it in seconds using Delta Lake's time travel and RESTORE. Your notebook now tells a complete story: ingestion, deduplication with MERGE, disaster, and recovery. You have a working demonstration of the data engineering patterns that employers expect for any lakehouse role.

Secret mission

Add Interactive Widgets to Parameterize Your Analysis

Your notebook works, but anyone who wants to analyze a different zip code has to edit the code. Can you add dropdown widgets so the analysis updates with a single click, no code editing required?

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.

Resources you used:

  • One managed Delta table (nyc_taxi_subset) in your workspace catalog's default schema.
  • One notebook (delta-lakehouse-workflow) in your workspace.

Keep everything running

No action needed. Choose this if you are still actively building or want to revisit the notebook later.

  • Your table and notebook persist in your workspace indefinitely at no cost.
  • Serverless compute automatically shuts down when idle, so there is nothing running in the background.
  • The two interactive widgets (pickup_zip and min_fare) remain attached to the notebook and activate again next time you open it.

Pause - I'll come back to this later

Since Databricks Free Edition uses serverless compute that auto-terminates when idle, there is nothing to pause. Your table and notebook remain in your workspace at zero cost until you return.

  • Close the notebook tab in your browser. Your work is auto-saved.
  • When you come back, open the notebook from the workspace browser and re-run cells from the top.

Delete - I don't want to use this again

Remove all project resources from your workspace. This cannot be undone.

  • Delete the Delta table by running this SQL command in any notebook cell:
  • DROP TABLE [[YOUR_CATALOG="your workspace catalog name"]].default.nyc_taxi_subset.
  • Delete the notebook by clicking the workspace browser in the left sidebar.
  • Find delta-lakehouse-workflow, right-click it, and select Move to Trash.

What about my Databricks account?

Your Databricks Free Edition account has no ongoing cost. You can leave it active for future projects or delete it from your account settings if you prefer.

Nice Work!

Nice Work!

You just built a complete data engineering workflow on Databricks, taking a Delta table from creation through intentional destruction and back to full recovery.

You've learned how to:

  • Build a managed Delta table inside Unity Catalog's three-level namespace and use PySpark DataFrames to query millions of rows alongside SQL in the same notebook.
  • Solve the duplicate data problem with MERGE INTO, the industry-standard upsert pattern that handles new and updated records in a single atomic operation.
  • Recover a corrupted table using Delta Lake time travel (DESCRIBE HISTORY, VERSION AS OF, and RESTORE TABLE) to query, compare, and roll back to any previous version.
  • Secret Mission: Added interactive dropdown and text widgets to parameterize your analysis, turning a static notebook into a reusable tool anyone can run with different inputs.

Ready to quiz yourself?