Roman Klimenko
DA

Seven ways to build a medallion architecture in Databricks

databricksdatasql

Databricks offers several ways to build a Bronze, Silver, and Gold pipeline. Each approach has its own trade-offs, but the documentation rarely compares them side by side.

I built the same pipeline seven ways and compared the results. Every version uses the same CSV data and schema and produces the same Kimball star schema. The project is deployed as a Databricks Asset Bundle, and the code is available in this repository.

The sections below move from the most manual approach to the most declarative. The final section summarizes when I would use each one.

The test case

The dataset is deliberately small. It contains customers, products, orders, and order lines split across two batches. Batch 1 is the initial load. In batch 2, two customers change their details, one customer is added, and a new order appears. These changes exercise the SCD2 logic.

The Gold layer is a classic Kimball dimensional model with dim_customer (including SCD2 history), dim_product, dim_date, and fact_order_line. After processing both batches, every approach must produce exactly 8 customer rows (6 current rows, including the new customer, and 2 historical rows), 5 products, 91 dates covering January through March 2024, and 11 fact rows.

If the numbers don't match, something is wrong with the implementation. That constraint keeps the comparison honest.

1. Python notebooks: the manual baseline

Code: bronze.py, silver.py, gold.py

This approach uses three PySpark notebooks, one for each layer. The Bronze notebook reads CSV files and adds metadata. Silver deduplicates the records, using row_number over a window so the latest batch wins, then casts types and standardizes text. Gold builds the dimensional model.

window = (Window
    .partitionBy("customer_id")
    .orderBy(F.col("_batch_id").desc()))

silver_customers = (bronze_customers
    .withColumn("rn", F.row_number().over(window))
    .where("rn = 1"))

The SCD2 logic in Gold is the hardest part. You must compare batch snapshots, build historical rows, and maintain valid_from, valid_to, and is_current yourself. The result works, but it is verbose and easy to get wrong.

This gives you full control, but you must write and maintain logic that the platform could otherwise handle.

2. SQL notebooks with COPY INTO

Code: bronze.sql, silver.sql, gold.sql

This version keeps the same architecture but uses SQL throughout. Bronze uses COPY INTO instead of spark.read. Silver uses CREATE OR REPLACE TABLE AS SELECT with CTE-based deduplication. Gold builds SCD2 history with LEFT ANTI JOIN and change detection. The logic is still manual, but it may be easier to read for a team that works mainly in SQL.

COPY INTO bronze_orders
FROM '/Volumes/.../orders'
    FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true');

For SQL-heavy teams, this approach feels natural and can make code reviews easier. The underlying limitations remain: orchestration is task by task, incremental processing is manual, and SCD2 still requires careful merge logic.

3. Materialized Views + Streaming Tables

Code: setup.sql, scd2_merge.sql

Materialized views and streaming tables are more declarative. You describe what each table should contain, and Databricks decides how to produce it.

CREATE OR REFRESH STREAMING TABLE bronze_orders AS
SELECT *
FROM STREAM read_files('/Volumes/.../orders', format => 'csv');

CREATE OR REPLACE MATERIALIZED VIEW silver_orders AS
SELECT ...
FROM bronze_orders;

Bronze tables become streaming tables backed by Auto Loader, which tracks the files already processed. Silver tables become materialized views that Databricks refreshes automatically. The resulting SQL is short and clear.

Materialized views do not natively support slowly changing dimensions. SCD2 therefore needs a separate MERGE notebook, which breaks the declarative pattern. This is a simple option when SCD2 is not required. When it is, the pipeline has to mix two approaches.

4. dbt-core on Databricks

Code: src/dbt_project

dbt organizes the work around models: SQL SELECT statements that define what a table should contain. dbt handles dependency ordering, testing, and documentation.

SCD2 is handled via dbt snapshots, which compare row states between runs:

{`{% snapshot snap_dim_customer %}
{{ config(strategy='check', unique_key='customer_id',
          check_cols=['email','address','city','country','segment']) }}
SELECT * FROM {{ ref('silver_customers') }}
{% endsnapshot %}`}

The workflow is a bit unusual. Because dbt snapshots capture changes between runs (not between batches), the bundle runs a two-phase process: first load batch 1, snapshot, then load batch 2, snapshot again, then build gold. The valid_from / valid_to values are derived from _batch_id rather than snapshot timestamps, since we need deterministic business dates, not execution timestamps.

dbt's main advantage is organizational. It enforces a project structure, ref-based dependencies, and a testing culture (not_null, unique, relationships). If your team already uses dbt, or you value portable SQL skills, this is a strong choice.

That structure comes with another tool to maintain, including dbt_project.yml, profiles.yml, and schema.yml. The dbt model also does not map perfectly to Databricks streaming and pipeline-native features. The dbt-databricks adapter is the place to start if you want to try it.

5. Delta Live Tables: the classic syntax

Code: pipeline.sql

Delta Live Tables (DLT) was Databricks' first real attempt at declarative pipelines. It introduced some genuinely useful ideas: built-in data quality expectations, native SCD2 via APPLY CHANGES, and a managed runtime that handles incremental processing for you.

CREATE STREAMING LIVE TABLE bronze_customers AS
SELECT * FROM STREAM read_files('/Volumes/.../customers', format => 'csv');

APPLY CHANGES INTO LIVE.dim_customer
FROM STREAM(LIVE.bronze_customers)
KEYS (customer_id)
STORED AS SCD TYPE 2;

SCD2 support is the highlight. Two lines replace dozens of lines of manual merge logic. The runtime produces __START_AT / __END_AT columns automatically (slightly different naming from the valid_from / valid_to convention, but semantically equivalent).

Databricks has since renamed and modernized this syntax. DLT keywords such as CREATE STREAMING LIVE TABLE and APPLY CHANGES INTO still work, but they are legacy syntax. New projects should use the current names.

6. Lakeflow pipelines: the recommended approach

Code: SQL version, Python version

The product formerly known as DLT is now called Lakeflow pipelines. It keeps the same runtime and capabilities but updates the syntax to align with standard SQL conventions:

CREATE OR REFRESH STREAMING TABLE bronze_customers AS
SELECT * FROM STREAM read_files('/Volumes/.../customers', format => 'csv');

CREATE FLOW scd2_dim_customer AS AUTO CDC INTO dim_customer
FROM STREAM(bronze_customers)
KEYS (customer_id)
STORED AS SCD TYPE 2;

The SQL version describes the pipeline directly. The Python version in the repository uses the compatible dlt module with @dlt.table and create_auto_cdc_flow(). For new code, Databricks now recommends from pyspark import pipelines as dp and @dp.table.

Lakeflow has several practical benefits:

  • Incremental by default. Streaming tables track what has been processed. You do not write checkpoint logic.
  • Native SCD2. AUTO CDC INTO handles history tracking without a manual merge or snapshot workaround.
  • Data quality built in. CONSTRAINT ... EXPECT ... ON VIOLATION DROP ROW validates data inline.
  • Single pipeline definition. The entire Bronze, Silver, and Gold flow lives in one file. Databricks handles dependency resolution and execution ordering.
  • Less code overall. The SQL pipeline is one file that replaces three notebooks worth of manual logic.

The pipeline syntax has its own semantics to learn. Debugging happens in the pipeline runtime rather than in a notebook that you can step through. The __START_AT and __END_AT column names also differ from the valid_from and valid_to convention, which is a small difference worth documenting for the team.

For most teams building new medallion pipelines on Databricks, this is where I would start.

So which one should you use?

My choice would depend on the team and the project:

Start with Lakeflow pipelines for a new Databricks project. They balance readability, built-in SCD2 support, data quality checks, and incremental processing while requiring the least custom code.

Use dbt if your team is already invested in the dbt ecosystem, or if portability across platforms matters. dbt's testing and documentation culture is a real advantage, even if it adds project overhead.

Use Python or SQL notebooks for prototypes, experiments, or when you need full control over the processing logic. They are fast to start and easy to understand, but they accumulate complexity quickly in production.

Use Materialized Views and Streaming Tables for compact, SQL-only pipelines where SCD2 is not required. In that setting, it is the simplest option.

Avoid classic DLT syntax for new projects. It still runs fine, but there is no reason to start with deprecated keywords when the modern equivalents do the same thing.

The full source code is at github.com/romaklimenko/databricks-medallion. If you want to dig into a specific approach, the README has detailed notes on each one.