Week 13 - Big Data on Databricks
In Week 10, you built a dbt project against 56,000 rows of taxi data in PostgreSQL using the dbt-postgres adapter. One of dbt's biggest strengths is warehouse portability: your SQL transformations and models stay the same whether you target PostgreSQL, Snowflake, BigQuery, or Databricks. Only the database adapter and connection profile change.
In this chapter, you will take that same dbt project and point it at Databricks using the dbt-databricks adapter to query 100M+ rows of taxi data on Delta Lake. Because rebuilding a 100M-row table from scratch on every dbt run is slow and expensive, you will also learn how to configure incremental models so dbt processes only new records on each run.
By the end of this chapter, you should be able to:
hyf-dbt-warehouse), not the PySpark clusterdbt-databricks adapter and configure a Databricks profilefct_trips as an incremental model using the merge strategy on DeltaIn Week 10, you built a dbt project in the nyc-taxi-dbt-reference repository. You will use that same repository again for this chapter.
Open nyc-taxi-dbt-reference (or clone again if you no longer have it locally) in VS Code and switch to the branch for this week:
git checkout week-13-ch-4-dbt
In Week 10, dbt talked to Postgres through the dbt-postgres adapter, a Postgres profile, and a _sources.yml file pointing at local Postgres schemas. To talk to Databricks, you install a different adapter, write a Databricks profile, and point _sources.yml at Unity Catalog (hyf.nyc_yellow). The SQL models themselves (stg_trips.sql, fct_trips.sql) do not change.
Install the dbt-databricks (the database adapter connecting dbt to Databricks SQL warehouses) adapter:
uv pip install dbt-databricks
# or if using standard pip:
pip install dbt-databricks
The week-13-ch-4-dbt branch includes a committed profiles.yml configured for Databricks. Because all sensitive values use env_var(...), profiles.yml contains zero hardcoded secrets and can safely remain committed in git. The connection details (host, HTTP path, and token) are loaded from environment variables:
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
Next, update models/staging/_sources.yml so {{ source('nyc_taxi', 'raw_trips') }} resolves to hyf.nyc_yellow.raw_trips in Unity Catalog instead of searching Postgres:
version: 2
sources:
- name: nyc_taxi
description: Raw NYC yellow taxi trip records and zone lookup on Databricks (Unity Catalog).
database: hyf
schema: nyc_yellow
tables:
- name: raw_trips
- name: raw_zones
<aside>
📘 Recap from Week 12: Secrets stay out of committed files. The token lives in DATABRICKS_TOKEN and profiles.yml reads it with env_var(), just like you kept the Postgres password out of your DAG code via the azure_pg Airflow Connection in Sequential Pipelines.
</aside>
In PySpark in Databricks your notebook ran on a cluster. dbt does not. dbt sends SQL to a serverless SQL warehouse.
dbt build runs.Rule of thumb: use a cluster for PySpark/Python work, and use the SQL warehouse (hyf-dbt-warehouse) for SQL work (dbt CLI builds, SQL Editor queries, or SQL notebook cells).
The four env_var values in the profile are the only things you fill in: three come from the warehouse, and one is your own schema.
Step 1: Find the warehouse connection details. In the sidebar, open SQL → SQL Warehouses, click the class warehouse (hyf-dbt-warehouse), and open its Connection details tab. Copy the Server hostname into DATABRICKS_HOST and the HTTP path into DATABRICKS_HTTP_PATH. If the list shows another warehouse (for example a Databricks default starter), ignore it: dbt must use hyf-dbt-warehouse.
The SQL warehouse Connection details tab: the Server hostname and HTTP path to copy into profiles.yml
Step 2: Generate your personal Databricks token. A personal access token (PAT) is how dbt authenticates as you. Click your username in the top bar → Settings → Developer → next to Access tokens, click Manage → Generate new token. Give it a short comment (for example dbt-local) and a lifetime of 90 days, then copy the value once: Databricks will not show it again.
Databricks Access tokens page with the Generate new token button
Treat this token like any database password: paste it only into your local .env. Never paste it in Slack, GitHub, or a PR.
Step 3: Save connection variables in .env. Copy .env.example to .env (which is gitignored) and fill in the four environment variables. For DATABRICKS_TOKEN, paste the token you just generated (not a shared or classmate token):
DATABRICKS_HOST="adb-xxxx.azuredatabricks.net"
DATABRICKS_HTTP_PATH="/sql/1.0/warehouses/xxxxxxxx"
DATABRICKS_TOKEN="dapi..." # paste the token you just generated
DBT_SCHEMA="dev_yourname" # your assigned Unity Catalog schema
Load .env into your shell session, confirm dbt connects as you, then run dbt debug:
source .env
dbt show --inline "select session_user() as connected_as"
dbt debug
The connected_as email must be yours. If it shows someone else's account, you pasted the wrong token: generate a new one and update .env.
That is the whole adapter switch. Run dbt debug and it connects to Databricks through the warehouse. Your stg_trips, stg_zones, and fct_trips models, with their ref() calls and schema tests, are identical to Week 10.
https://gist.githack.com/lassebenni/4ac97c5b7a6820747ee6140588068f81/raw/week_13__dbt_databricks_debug_terminal.html
Try the connection test in your terminal to confirm your warehouse environment variables are set correctly.
<aside>
💡 Recap: You have checked out week-13-ch-4-dbt in nyc-taxi-dbt-reference, installed dbt-databricks, updated _sources.yml to target Unity Catalog (hyf.nyc_yellow), configured your .env variables, and verified the connection with dbt debug. All checks passed without changing a single model SQL file!
</aside>
The models port as-is, but pointing them at 128M rows of real data instead of a clean 57K sample surfaces things the small dataset hid. Two Week 10 tests often flag: payment_type gains a code 0 (a value the green sample never had), and a handful of rows have a pickup timestamp after their dropoff. That is not a porting bug: it is what real data at scale looks like.
<aside>
📘 Going further: On Databricks you will hear bronze / silver / gold (medallion architecture). That is the same raw → staging → marts idea from Weeks 9–10 under different names: raw_* ≈ bronze, stg_* ≈ silver, fct_* ≈ gold. Optional depth: Medallion architecture. This chapter does not ask you to rebuild those layers.
</aside>
Those test failures are noisy but not blockers for the incremental work that follows.
<aside>
⚠️ Do not stall here. You can keep going on the incremental config even if those tests warn or fail. Adjusting accepted_values or severity is normal analytics-engineering work, and Gotchas covers the fix when you are ready.
</aside>
In Week 10, fct_trips was materialized as a table: every dbt build you ran dropped the existing table and rebuilt the entire dataset from scratch (DROP TABLE ... CREATE TABLE ...). At 56K rows in Postgres, that took under a second.
At 100M+ rows on Databricks, doing a full rebuild on every run is extremely wasteful in both execution time and cloud compute costs. This is where incremental models (models that process only new or changed rows after the initial build) earn their keep.
An incremental model relies on three building blocks:
Building Block 1: Materialization Config
Tell dbt to use incremental materialization with Delta Lake's merge strategy (which performs an atomic update-or-insert) and specify a unique_key to identify matching rows:
{{
config(
materialized='incremental',
incremental_strategy='merge',
unique_key='trip_id'
)
}}
Building Block 2: Unique / Surrogate Key
To match incoming new rows against existing table rows during a MERGE, dbt needs a stable primary key. Since raw_trips does not have a single unique ID column, we generate a surrogate key (a hashed ID built from several columns) in stg_trips using dbt_utils.generate_surrogate_key:
{{ dbt_utils.generate_surrogate_key([
'pickup_datetime', 'dropoff_datetime', 'pickup_location_id',
'dropoff_location_id', 'fare_amount', 'total_amount', 'passenger_count'
]) }} as trip_id,
generate_surrogate_key comes from dbt_utils. Your Week 10 project should already have packages.yml and a dbt deps run from dbt Tests; if dbt_utils is missing, add the package and run dbt deps before building.
Building Block 3: The is_incremental() filter and {{ this }} reference
On subsequent runs, you must filter out historical rows so dbt only scans new data. The is_incremental() macro returns true when the target table already exists and you are not forcing a rebuild. It returns false on the first build (table missing) and whenever you pass --full-refresh (more on that flag after you run the model twice). The {{ this }} variable refers to the existing target table in the database:
select
t.trip_id,
t.pickup_datetime,
-- ... remaining columns ...
pz.borough as pickup_borough
from {{ ref('stg_trips') }} t
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() %}
-- On incremental runs, only process trips newer than what we already have
where t.pickup_datetime > (select max(pickup_datetime) from {{ this }})
{% endif %}
The merge strategy uses Delta Lake's atomic MERGE (the primitive from The lakehouse idea) to update-or-insert those rows into the existing table. The unique_key (trip_id) tells dbt how to match a new row against an existing one.
merge strategy relies on Delta Lake rather than plain Parquet files?<aside>
⚠️ Boundary filter gotcha (> vs >=): Note the strict > operator in the filter. Using >= re-reads rows that share the exact maximum timestamp already present in {{ this }}, inserting duplicate rows on every incremental run. Using > ensures dbt scans only strictly newer records.
</aside>
Explore the same model as a compile fork: toggle first build vs 2nd+ build and watch is_incremental() delete or keep the watermark filter in the SQL the warehouse actually runs:
https://gist.githack.com/lassebenni/d7cf2df641dfc7a15d1312dcebb37e81/raw/week_13__fct_trips_incremental_code_explainer.html
The compile fork shows the SQL dbt sends. The next widget shows what happens to rows in fct_trips. Click Next → through each beat and read the takeaway under the legend before advancing:
fct_trips unchanged (the fast run).trip_id with a corrected fare (MATCH → update) plus a brand-new trip_id (NO MATCH → insert).--full-refresh drops the table and rebuilds from the full select (the expensive clean slate).https://hackyourfuture.github.io/Learning-Resources/data-track/embeds/week-13-slides/week_13__fct_trips_merge_minisim.html
All the core pieces of an incremental model are now assembled in code: the incremental materialization config, the unique_key setting, the stg_trips surrogate key, and the is_incremental() boundary filter.
<aside>
💡 Recap: You have configured materialized='incremental' with incremental_strategy='merge' and unique_key='trip_id', generated trip_id in stg_trips.sql, and added the max(pickup_datetime) boundary filter in fct_trips.sql.
</aside>
This is the whole point of the chapter, so do it deliberately.
<aside>
⌨️ Hands on: In your local nyc-taxi-dbt-reference repository, run dbt build --select fct_trips twice in a row. Observe how the second run detects the existing table and completes in a fraction of the time.
</aside>
Step 1: the first build. Run the model against the full history. Because the table does not exist yet, is_incremental() is false, so dbt reads all 100M+ rows and builds the table from scratch.
dbt build --select fct_trips
https://gist.githack.com/lassebenni/a13c1d557e803b9701b056c3c241dcfd/raw/week_13__dbt_run_step1_terminal.html
Against the full 128M rows on the class 2X-Small serverless warehouse, this first build took 84 seconds. (The exact time depends on the warehouse size; treat the numbers here as approximate and confirm them against your own run.)
Step 2: the second build. Run the exact same command again.
dbt build --select fct_trips
https://gist.githack.com/lassebenni/7d5c2d53cb7e8540fa3de1511c72e952/raw/week_13__dbt_run_step2_terminal.html
This time the table exists, so is_incremental() is true, the {{ this }} filter kicks in, and dbt reads only the handful of rows newer than what is already there. The same run that took 84 seconds now finishes in 24 seconds.
Nothing about your model logic changed between the two runs. The only difference is that the second run knew it had already done the expensive work. That is incremental materialization, and now you have felt the thing Week 10 could only describe.
<aside>
💡 Recap: By running dbt build --select fct_trips twice back to back, you experienced the core benefit of incremental models: Step 1 built the table from scratch (84s), while Step 2 activated is_incremental() and finished in seconds (24s) with zero model changes.
</aside>
Two dbt build runs of fct_trips: the first full build takes about a minute, the incremental rerun about ten seconds
--full-refreshA normal second dbt build stays incremental on purpose: it keeps history and only processes what is newer than {{ this }}. Sometimes you want the opposite: throw the existing table away and rebuild everything.
Pass --full-refresh when:
unique_key) and old rows would stay wrong under a plain incremental run--full-refresh build, then a normal incremental rerun)dbt build --select fct_trips --full-refresh
What the flag does: dbt forces is_incremental() to false, drops (or replaces) the existing fct_trips, and runs the full select again. Same shape as the first build, even though the table already existed. That is the last beat in the MERGE mini-sim above.
Do not confuse the two "full" cases:
| --- | --- | --- | --- |
Use --full-refresh on purpose, not as your daily schedule. On 100M+ rows it costs the long run again. For everyday pipelines, prefer incremental and reserve full refresh for logic changes, clean baselines, or recovery.