Mid-week: Big Data on Databricks

Week 13 lock-in session

Where are you mid-week?

(Quick sense of the room: who has finished Ch1–Ch3? Who has dbt debug green on Databricks? Who has already created a Job?)

You are already on the shared workspace. Keep it open in a tab; if a login expired, refresh now. Stuck on access? Raise a hand.

This session locks the through-line, demos the hard bits live, and clears blockers before the PR.

New platform, familiar craft

Weeks 6-12 ran on one machine. This week: Databricks + the lakehouse at 128M rows. Week 10 craft (fct_trips, ref(), tests) mostly travels; adapter + profile switch.

Week 10: dbt models + tests          (56K rows, Postgres)
Week 11: dashboards on the marts
Week 12: orchestrate + secrets
Week 13: Databricks + dbt at scale   (128M rows; port, incremental, Jobs)

Centre of gravity: Ch4 (incremental at 128M). Ch5 closes with a Git-backed Job. Today we re-anchor Ch1–Ch3 so Tasks 2–3 land.

Today's agenda

Time	Activity	Duration
0:00	Progress check	8 min
0:08	Agenda + live-exercise frame (Tasks 1–3)	7 min
0:15	§1 Ch1 Lakehouse recap + live check	10 min
0:25	§2 Ch2 lock-in + 128M-row proof	15 min
0:40	§3 Ch3 + Task 1 mini	20 min
1:00	Break	10 min
1:10	§4 Ch4 + dbt debug green + build it twice	37 min
1:47	§5 Ch5 + Run now, then pause	18 min
2:05	§6 Live Quiz (Ch1-Ch5)	20 min
2:25	Assignment walkthrough + buffer	35 min

Ch1: One machine vs many

A single machine has fixed memory and CPUs. pandas / one Postgres server hit that wall. Distributed computing spreads work across machines - with coordination cost and billable cluster time.

At 56K rows, the cluster is often slower. At 128M rows, the parallel path earns its keep.

Reach for distributed computing when the data genuinely does not fit, not because it sounds impressive.

Ch1: History of data warehouses and data lakes

If you already have the lake, why also pay for the warehouse?

Ch1: What ACID means (Database / Postgres)

	Holds?	Means	Taxi example on Postgres
Atomic		All-or-nothing	Load of 10k Jan 15 trips crashes mid-insert: 0 new rows, not 4k
Consistent		A commit only succeeds if declared DB constraints still hold	Primary key trip_id cannot be null: an insert without a trip id is refused
Isolated		Concurrent work does not see half-done writes	Dashboard select during the load sees yesterday or full Jan 15, never half
Durable		A commit survives a crash	After commit, restart Postgres: those trips are still on disk

You already relied on this on the shared Azure Postgres. Cloud warehouses keep strong A/I/D; C is often weaker (FKs may be informational). See the eras slide next.

Ch1: ACID across the eras

The lakehouse pitch is not "forget ACID." It is "keep ACID without the warehouse copy."

Ch1: The lakehouse collapses the split

Same cheap files. Warehouse guarantees live on the files - no second copy.

Ch1: Why a folder of Parquet is not a table

Two nightly jobs write the same folder; a dashboard reads mid-write and sees half of January 15. The log makes that impossible.

MERGE is what makes the second build-it-twice run fast.

Ch2: The five places

• Workspace · Catalog · Compute · SQL · Workflows (Jobs; §5)

Ch2: Databricks for data engineers

One workspace, many capabilities. The sidebar is the entry; this is the fuller map.

Week 13 trains the DE core path: tables, compute, transform, Jobs. The rest exists so you know the platform is bigger than one tool.

Ch2: Cluster vs serverless warehouse

Ch2: Cluster vs warehouse (when to use which)

Rule of thumb this week: PySpark/Python → cluster; SQL work (dbt, SQL Editor, SQL notebooks) → warehouse.

Clusters bill per minute and auto-terminate. On this subscription a cold start can take ten minutes. The shared cluster should already be running for today's demos.

Ch2: catalog.schema.table

Every table has a three-part name. The shared taxi data:

hyf . nyc_yellow . raw_trips
 │         │            │
catalog  schema       table

Unity Catalog is the shared namespace across every workspace, cluster, and warehouse. Same name from a notebook and from dbt.

Ch2: Find the table in Catalog Explorer

Expand hyf → nyc_yellow → raw_trips.

Ch2: Create → Query

From the raw_trips table page: Create → Query.

Ch2: Attach hyf-dbt-warehouse

Before you run anything, attach the shared warehouse: SQL Warehouse → hyf-dbt-warehouse.

The 128M-row proof

Start: SQL editor attached to hyf-dbt-warehouse.

Do:

select count(*) from hyf.nyc_yellow.raw_trips

Success check: you see about 128,202,548. You can name the three-part path: catalog · schema · table.

Already did the 128M-row proof earlier this week? Confirm the number once, then help a neighbor.

Ch3: What is a notebook?

A notebook is cells: type code, run that cell, see the result under it. Variables stay in memory. On Databricks a cell can be Python (PySpark) or SQL. Unlike a .py script, the session stays up - load once, try filters many times.

Cell order is the order you ran, not the order on screen. When unsure, run top to bottom.

Follow along if you already have one:

1. Open (or create) a Python notebook under Workspace.
2. Attach hyf-dbt-warehouse, run a SQL count(*) cell.
3. Attach the shared (pre-started) cluster.
4. Run: print("hello from the cluster").

Forgetting to attach is still the most common reason a cell does nothing.

Ch3: Create notebook → attach warehouse

New Python notebook (empty cell, compute dropdown in the toolbar):

Ch3: Attach hyf-dbt-warehouse

Compute type → SQL Warehouse → hyf-dbt-warehouse → Attach. Run a SQL count(*) cell (same ballpark as the 128M-row proof).

Ch3: Then attach the cluster + hello

Switch the notebook to the shared cluster (warehouse for SQL/dbt; cluster for PySpark - same notebook, different compute):

Ch3: Python, Spark, and PySpark

Keep three names separate (plus the language you already know):

Plain Python runs in one process. PySpark looks like Python DataFrame code, but the heavy work runs across the cluster.

Ch3: Your cell talks to Spark through PySpark

You write PySpark. Spark does the distributed work. Databricks hosts Spark (and more than Spark).

Ch3: DataFrame operations (1/2) - transformations

A transformation describes work. It returns another DataFrame (or plan) and does not scan the cluster yet. That delay is intentional: Spark needs the full chain before it optimizes (next slides).

Ch3: DataFrame operations (2/2) - actions

An action needs a real answer. That is when Spark runs the plan on the cluster.

Never collect() the raw trips table. Use show / count / write instead.

Ch3: Why Spark waits (unlike pandas)

Week 4 pandas usually runs each line right away on one machine. That is fine when the frame fits in RAM.

Spark uses lazy evaluation on purpose. Moving data across a cluster is expensive. If every filter / join ran immediately, Spark would write huge intermediate tables, then read them again for the next step.

By waiting for an action, Spark sees the full plan first. Its query optimizer (Catalyst) can then:

• reorder steps (for example filter before join)
• drop unused columns
• combine work into fewer stages

before any bytes move across the network.

Ch3: Lazy evaluation - plan first, run on action

Transformations return instantly (build the plan). An action (show / count / write) is when the optimizer finishes and the cluster runs.

Ch3: Read the shared tables

trips = spark.read.table("hyf.nyc_yellow.raw_trips")
zones = spark.read.table("hyf.nyc_yellow.raw_zones")

trips.printSchema()
print(trips.count())  # action: expect roughly 128_000_000

zones is for the join in Task 1 mini. count() returns one integer, not 128M rows to the driver.

Ch3: trips is a handle, not a download

spark.read.table(...) does not pull 128M rows to the driver. It gives you a handle (trips) on the cluster.

From that same handle: count() → one integer, or filter + show(5) → five rows. Neither path downloads the raw table. That is why both are safe.

Ch3: What F.col("payment_type") == 2 means

PySpark conditions look like Python, but they are plan language for Spark:

Not pandas (df["payment_type"] == 2 runs now) and not SQL (WHERE payment_type = 2). Same idea as SQL, different spelling so Catalyst can optimize it.

Ch3: filter + show(5) (the lower branch)

from pyspark.sql import functions as F

# Transformation: builds a plan, does not run yet
cash_trips = trips.filter(F.col("payment_type") == 2)

# Action: runs the plan and prints a few sample rows
cash_trips.select("pickup_datetime", "total_amount", "payment_type").show(5)

Needs trips (and usually F) from earlier cells.

Ch3: What that show(5) looks like

Five sample cash rows - not the full filtered table on the driver.

Live: Top pickup borough (call out + teacher codes)

Goal: which borough has the most pickups? One row via show(1).

We already have: trips, zones, from pyspark.sql import functions as F.

1. How do we get borough onto a trip?
2. How do we count trips per borough?
3. How do we keep only the top?

Already finished Task 1? Help a neighbor, or write the PySpark-vs-dbt note.

Solution: Top pickup borough

from pyspark.sql import functions as F

borough_trips = (
    trips.join(zones, trips.pickup_location_id == zones.location_id)
    .groupBy("borough")
    .agg(F.count("*").alias("trip_count"))
    .orderBy(F.desc("trip_count"))
)
borough_trips.show(1)

raw_trips has location ids; zones brings the borough name. show(1) after orderBy is the top borough.

Live: Avg total_amount by payment_type

Goal: average total_amount for each payment_type. Small table via show().

We already have: trips, F. No join this time: payment_type is already on trips.

1. How do we split trips by payment type?
2. How do we get the average total_amount per group?
3. How do we see the result without pulling raw rows?

Stay on this slide until show() prints. Solution is next if you get stuck.

Solution: Avg total_amount by payment_type

payment_avg = (
    trips
    .groupBy("payment_type")
    .agg(F.avg("total_amount").alias("avg_total_amount"))
)
payment_avg.show()

groupBy + agg only: no zones join. show() returns one row per payment type.

Task 1 mini: what a successful show() looks like

Small aggregated table after join / groupBy - that is the Task 1 mini success shape.

Ch3: PySpark or dbt?

For analytics engineering batch marts, dbt SQL is the default. Chapter 4 is dbt. Reach for PySpark when you need streaming or real Python.

Ch4: Port dbt (adapter + profile)

pip install dbt-databricks

nyc_taxi:
  target: databricks
  outputs:
    databricks:
      type: databricks
      catalog: hyf
      schema: "{{ env_var('DBT_SCHEMA') }}"
      host: "{{ env_var('DATABRICKS_HOST') }}"
      http_path: "{{ env_var('DATABRICKS_HTTP_PATH') }}"
      token: "{{ env_var('DATABRICKS_TOKEN') }}"
      threads: 4

Models, ref(), tests, YAML: unchanged. Incremental config is the Week 13 twist.

Ch4: Connection details → env vars

SQL → SQL Warehouses → hyf-dbt-warehouse → Connection details:

• Server hostname → DATABRICKS_HOST
• HTTP path → DATABRICKS_HTTP_PATH
• Token → DATABRICKS_TOKEN (env var only, never in git)
• DBT_SCHEMA=dev_yourname

Ch4: Connection details in the UI

Ch4: Replace your Databricks token (action required)

Step 1: Databricks → Settings → Developer → Access tokens → Generate new token

Step 2: Copy .env.example to .env (gitignored). Paste your new token into DATABRICKS_TOKEN.

Step 3: Load .env and check who dbt connects as:

source .env
dbt show --inline "select session_user() as connected_as"

Step 4: Confirm the connected-as email is yours, then run dbt debug.

Never paste the token in Slack, GitHub, or a PR. Same secret hygiene as Week 12.

dbt debug green

Start: your Week 10 port or assignment task-2/ with env vars set.

Do: dbt debug (or uv run dbt debug).

Success check: All checks passed! against catalog hyf and schema dev_<name>.

Stuck on the port? Diff against nyc-taxi-dbt-reference branch week-13-ch-4-dbt-solution (catch-up only, not the class spine).

Ch4: Where did the millions of rows come from?

Same NYC TLC trip records. Different slice:

Source is public: NYC TLC trip records (monthly yellow_tripdata_YYYY-MM.parquet). Teachers loaded those files into Unity Catalog once; you read the shared table.

Ch4: Make fct_trips incremental

Week 10:

{{ config(materialized='table') }}

On Databricks:

{{
  config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='trip_id'
  )
}}

Add a surrogate trip_id in stg_trips (dbt_utils.generate_surrogate_key). Guard new rows with is_incremental() and > (not >=).

Ch4: What is an incremental strategy?

materialized='incremental' means: do not drop and rebuild. Only process new/changed rows.

incremental_strategy answers: how do those rows land in the existing table?

On Databricks we use merge because:

1. Delta supports atomic MERGE (Ch1). Plain Parquet cannot update-or-insert
2. Taxi facts can be corrected (same trip_id, new fare/tip). append would duplicate
3. unique_key='trip_id' is how MERGE knows which row to update

Ch4: The incremental filter

{% if is_incremental() %}
    where t.pickup_datetime > (select max(pickup_datetime) from {{ this }})
{% endif %}

• merge uses Delta's atomic MERGE (Ch1).
• unique_key='trip_id' tells dbt how to match rows.
• > avoids re-reading the boundary and creating duplicates.

Ch4: fct_trips (full model, commented)

{{
  config(
    materialized='incremental',    -- update table; don't drop/rebuild
    incremental_strategy='merge',  -- Delta MERGE INTO
    unique_key='trip_id'           -- match key for MERGE
  )
}}

select
    t.trip_id,  -- surrogate key from stg_trips (dbt_utils)
    t.pickup_datetime, t.dropoff_datetime,
    t.fare_amount, t.tip_amount, t.trip_distance,
    t.trip_duration_minutes, t.tip_pct, t.fare_per_mile,
    t.payment_type_label,
    pz.borough as pickup_borough, pz.zone as pickup_zone,
    dz.borough as dropoff_borough, dz.zone as dropoff_zone
from {{ ref('stg_trips') }} t              -- DAG → your schema
left join {{ ref('stg_zones') }} pz
    on t.pickup_location_id = pz.location_id
left join {{ ref('stg_zones') }} dz
    on t.dropoff_location_id = dz.location_id

{% if is_incremental() %}  -- true on 2nd+ runs only
    -- only trips newer than what we already have
    where t.pickup_datetime > (
        select max(pickup_datetime) from {{ this }}  -- this = fct_trips
    )
{% endif %}

Build it twice (live)

Start: dbt debug green; fct_trips incremental config in place on nyc-taxi-dbt-reference branch week-13-ch-4-dbt (catch-up: week-13-ch-4-dbt-solution).

Do:

# in nyc-taxi-dbt-reference on week-13-ch-4-dbt (or -solution)
dbt build --select fct_trips   # full history over 128M (often ~1 min; illustrative)
dbt build --select fct_trips   # incremental (often much faster when few/no new rows)

Success check: two wall-clock times. Expect a clear drop when the watermark works (screenshot: ~62s → ~10s). Name is_incremental() and {{ this }}. If run 2 is still ~2 min, open compiled SQL and confirm the where … > max(pickup_datetime) filter is present; shared warehouse load also stretches both runs.

Build it twice: what the timings look like

Illustrative on a quiet warehouse: model ~62s → ~10s. Shared-class load can stretch both runs; the story is still "filter via {{ this }}", not a fixed 10s promise.

Ch4: Prove it in Delta history

Ch4: What to paste in WRITEUP.md

In Catalog Explorer or SQL: DESCRIBE HISTORY hyf.dev_yourname.fct_trips.

Expect a full create/replace, then MERGE versions. Paste into WRITEUP.md for Task 2.

Ch5: Why schedule here?

Local dbt build is for development. Production needs the same build without an open laptop.

Workflows / Jobs is a first-class part of Databricks. Task 3 is often the mid-week gap: leave with a green Run URL.

Ch5: How a dbt Job runs

Schedule or Run now → Job pulls Git → dbt CLI → warehouse → Unity Catalog.

Ch5: Git provider, not Workspace upload

Live demo: Git-backed dbt Job

Start: Workflows → open the pre-built teacher Job (or Create job if cold).

Do (screen-share):

1. Source = Git provider → https://github.com/lassebenni/nyc-taxi-dbt-reference.git · branch week-13-ch-4-dbt-solution
2. SQL warehouse = hyf-dbt-warehouse · commands = dbt deps then dbt build --select fct_trips
3. Run now → wait for green → show the Run URL

Ch5: Demo Job in the UI

Run now, then pause

Start: Workflows → your Job (after the demo Job).

Do:

1. Run now once → wait for green.
2. Copy the Job Run URL into SCHEDULING.md.
3. Add a schedule for UI proof, then pause the trigger.

Success check: green run + paused schedule + Run URL saved. Shared bill: no nightly runs left overnight.

Run now, then pause: green run

Run now, then pause: schedule, then pause

Run now, then pause: paused schedule

Ch5: Jobs vs Airflow (30 seconds)

• Databricks Jobs when the work is already on Databricks (dbt, notebooks, SQL).
• Airflow when you orchestrate many systems (blob, Postgres, Databricks, …).

Live Quiz · one round, 12 questions (Ch1-Ch5)

20 minutes.

Open our Live Q&A site on your phone (URL + QR code on screen).

Game code: (your teacher will project it)

A retrieval check on this week's material: lakehouse, Unity Catalog, PySpark, dbt incremental, and Git-backed Jobs. No notes. Use it to find gaps before you open the PR.

Still catching up on a chapter? Observe and listen on those questions. Don't guess-answer just to participate.

Your Week 13 assignment

Today's live work is the assignment spine. Open a fork of data-assignment-week-13 and ship three required folders in a PR:

1. task-1/ - PySpark notebook: explore trips safely with show() (not raw collect()), plus a short note on when PySpark beats dbt.
2. task-2/ - Your dbt port: green dbt debug, incremental fct_trips, two build timings + Delta DESCRIBE HISTORY in WRITEUP.md.
3. task-3/ - Git-backed Job on your fork: one green Run now, schedule paused, evidence in SCHEDULING.md.

Optional stretch: Task 4 extras under task-4/ (alerting, governance, streaming, local PySpark+pytest).

Never commit a Databricks token. Stuck more than 10 minutes? Ask in the buffer or Slack now.

Next steps

By now you can: explain lakehouse + Delta; navigate Databricks; run lazy PySpark safely; port dbt incremental; schedule a Git-backed Job and pause it.

• Close Tasks 1–3 and open the PR before the deadline.
• Pause every Job schedule; let clusters auto-terminate.
• Week 14: infrastructure as code for the workspace, warehouse, and policies.

Well done: Week 10's models still travel at real scale. Finish the Job + PR.

Thank you

Questions? Open a thread in Slack or drop them on the Live Q&A board.