Home › Data engineering › Gold data modelling › Silver to gold › Databricks declarative pipeline?

Silver to gold · Decide · 9 min read

Can silver to gold be a Databricks declarative pipeline?

AUTO CDC and materialized views can build gold. Here is what the example looks like that way, what you gain, what you give up, and a verdict for each kind of gold table. This is one page of Silver to gold.

Part C · 1

Can silver to gold be a Databricks declarative pipeline?

Short answer. It can run as a Lakeflow Spark Declarative Pipeline, but we do not recommend it for this layer. Gold is where the bookkeeping lives: choosing which days to rebuild, checking before writing, completeness, restatements, correction plans, the row ledger and stable keys. A declarative pipeline gives none of these. Our default is the harness, run as a Databricks job (or on any Spark). Two optional exceptions: materialized views for some aggregates, where a Databricks team wants them, and AUTO CDC for dimensions, but only if silver does not already keep history. Go fully declarative only for small marts where nobody audits past numbers.

Why dimensions stay in the harness. In our platform, silver already keeps SCD Type 2 history (our own PySpark engine). Gold only adds stable keys (1, 2, 3 from a key map) on top of those versions. AUTO CDC would redo history that silver already holds, and give hash keys instead of stable integers. Declarative pipelines do have a natural home in our platform: bronze to silver, as an optional engine for teams that ask for it.

First, what "declarative" means: you say what each table is ("fact_otif is build_otif over these silver tables"), and the engine works out how: order, what is new, retries, refresh. The harness is the opposite. You (well, the shared code) say exactly how each night runs. More on the idea itself: Declarative or not.

1. Harness (our default)facts, dims, aggregates: harnessrun by a Databricks jobor any Spark, any cloudsame code on a laptopPursue2. Hybridfacts and dims: the harnesssome aggregates: materializedviews, where a team wants themone job runs harness, then MVsPursue with care3. All declarativedim_dealer: AUTO CDC (SCD2)fact_otif: materialized viewaggregates: materialized viewschecks: expectationsOnly small marts nobody audits

Three ways to run this layer. The harness is the default; the other two are options.

Part C · 2

What Part A's example looks like as a declarative pipeline

The same tyre example, written for Lakeflow. Read it next to Part A. The build is the same function. What changes is everything around it.

Status: this sketch was written for this page and has not been run on Databricks. Function names are from the Lakeflow Python API as documented in November 2026; check the current docs before copying.
# Lakeflow Spark Declarative Pipelines (Databricks). A sketch written for this page: NOT run on Databricks yet.
from pyspark import pipelines as dp
from pyspark.sql import functions as F
from builds import build_otif                      # the same pure build as Part A, unchanged

# Dimension: SCD Type 2 from the dealer change feed. The engine handles order, repeats and deletes.
dp.create_streaming_table("dim_dealer")
dp.create_auto_cdc_flow(
    target="dim_dealer",
    source="dealer_changes",                       # a streaming view over silver dealer changes
    keys=["dealer_id"],
    sequence_by=F.col("effective_date"),           # versions are stamped with THIS column (see the cons)
    stored_as_scd_type=2,
)

# Dealer versions with a key and a date range, shared by the fact and the aggregate.
@dp.materialized_view(name="dealer_versions")
def dealer_versions():
    return (spark.read.table("dim_dealer")
            .select("dealer_id", "region",
                    F.xxhash64("dealer_id", "__START_AT").alias("dealer_key"),   # a hash, not 1, 2, 3
                    F.col("__START_AT").alias("vf"),
                    F.coalesce("__END_AT", F.lit("9999-12-31").cast("date")).alias("vt")))

# Fact: a materialized view. The engine decides each night whether it can refresh
# incrementally or must recompute; you do not choose which days are rebuilt.
@dp.materialized_view(name="fact_otif")
@dp.expect_or_fail("dealer looked up", "dealer_key IS NOT NULL")
def fact_otif():
    lines = spark.read.table("silver.order_lines").where("dealer_id NOT IN ('TEST01')")
    dels = spark.read.table("silver.deliveries")
    built = build_otif(lines, dels, {})
    dim = spark.read.table("dealer_versions").drop("region")
    on = ((built.dealer_id == dim.dealer_id)
          & (built.promised_date >= dim.vf) & (built.promised_date < dim.vt))
    return (built.join(dim, on, "left").drop(dim.dealer_id, "vf", "vt")
                 .withColumn("dealer_key", F.coalesce("dealer_key", F.lit(-1))))

# Grain check: expectations look at one row at a time, so a check across rows needs its own table.
@dp.materialized_view(name="check_fact_otif_grain")
@dp.expect_or_fail("one row per line", "n = 1")
def check_fact_otif_grain():
    return spark.read.table("fact_otif").groupBy("line_id").agg(F.count("*").alias("n"))

# Aggregate: the best fit for a materialized view.
@dp.materialized_view(name="otif_by_region_day")
def otif_by_region_day():
    return (spark.read.table("fact_otif")
            .join(spark.read.table("dealer_versions").select("dealer_key", "region"), "dealer_key", "left")
            .groupBy("promised_date", "region")
            .agg(F.count("*").alias("lines"), F.sum("otif").alias("otif_lines")))

Each harness step, and what the declarative pipeline does instead

Harness step (Part A)In a declarative pipelineCompared with the harness
1-2. Bookmarks, changed rowsThe engine keeps them (checkpoints, refresh tracking). You write none.Better: less code
3. Which windows to rebuildA materialized view refreshes as the engine decides: incrementally when it can, otherwise a full recompute. You cannot say 'rebuild only 11 and 13 Nov'.Engine's choice, not yours
4. The buildThe same pure function, called inside the decorated function.Same
5. Keys as of the eventYour own range join to the AUTO CDC table (__START_AT, __END_AT). No key map, so keys are hashes or business key plus start date.Possible, with work
5b. Late dealer re-keyedIn a materialized view, yes, on the next recompute. In a streaming table, no: a stream joined to a dimension sees the dimension as it was when each row was processed.Depends on the table type
6. CompletenessA column you compute in the view from a table of received files.Possible
6b. ChecksExpectations check one row at a time. Grain, totals and ledger balance need extra check tables, as in the sketch.Partly
6c. Row ledgerThe event log has rows written per table and expectation pass and fail counts. Counts per filter and per join are not there.Partly
RestatementsA view keeps no record of the number it used to show. Comparing with the last published version needs a step outside the pipeline.Missing
Correction plans for closed monthsNo such idea. You change silver, or force a full refresh.Missing
7. Write, then move the bookmarkEach table's refresh is all or nothing. But the pipeline as a whole is not: for a while, readers can see the new fact with the old aggregate.Better per table; watch across tables

Part A's nights, replayed in your head on Lakeflow

  • Night 1: fact_otif is computed. L5 gets -1, 13 Nov is flagged incomplete (if you added the completeness column). No bookmark code was needed.
  • Night 2: the view refreshes. L3 shows 8 delivered, L5 becomes key 4 by itself. Good. But nobody records that 11 Nov and 13 Nov changed after they were shown. Was it a refresh of three days or of all history? The event log tells you afterwards; you did not choose.
  • The crash: the engine retries, and each table's refresh is all or nothing. Good, and no code needed.
  • The careless join: caught only if you wrote the grain check view. Without it, the doubled L3 goes straight to the dashboard.

Part C · 3

Pros and cons

Pros

ProWhat it givesMatters most when
Far less plumbingNo bookmarks, run order, retries or checkpoints to write and testSmall teams, many simple tables
The dependency graph is drawn for youTables run in the right order; lineage and a UI come freeMany tables depending on each other
SCD Type 2 dimensions in a few linesAUTO CDC handles out-of-order changes, repeats and deletesSilver does not keep history already
Late dimension members fixed by themselvesA materialized view is recomputed against today's dimension, so L5's -1 becomes 4 with no extra codeFacts built as materialized views
Quality rules and run history in one placeExpectations with keep, drop or fail; a queryable event logTeams without their own run log
Aggregates kept up to date cheaplyIncremental refresh of materialized views on serverless, when the engine canDashboards over big facts
Catches broken pipelines before they runValidate (dry-run) finds missing tables, columns and cycles with no dataEvery pipeline, in CI
Your build still worksThe pure build from Part A runs inside unchanged, and is still tested on a laptopKeeping the option to leave

Cons

ConWhat goes wrongWhat to do about it
You do not control how gold is writtenThe engine chooses incremental or full recompute. A fact that silently falls back to full recompute costs more as history grows, and you may only notice on the bill.Check the refresh technique in the event log after every change
No restatement logPublished numbers change silently when late data arrivesAdd a job step that compares with the last published version
No correction plansFixing a closed month means changing silver or a full refreshKeep corrections in silver as records, never edits
Keys are hashes, not a key mapAUTO CDC does not hand out 1, 2, 3. Hash keys work, but are big and cannot be re-pointedFine for most; finance-grade marts may want a key map
AUTO CDC stamps versions with the sequence columnIf you sequence by a change number, __START_AT is that number, not a date, and you cannot look up 'as of 13 Nov'Sequence by an effective date, or keep a separate date column
Checks across rows need extra tablesExpectations see one row at a time; grain and totals need check viewsWrite them, as in the sketch
Streaming-table facts freeze the dimensionRows keep the dimension as it was when processed; a late dealer is never re-keyed without a full refreshBuild facts as materialized views, not streaming tables
Full refresh is a big hammerIt rereads sources from the start; if old bronze or silver files have been removed, history is lostProtect important tables from full refresh
Price on classic computeDeclarative is 1.3 to 2.4 times the jobs rate per DBU on classic compute; the same rate on serverlessUse serverless, or pick the edition per pipeline
Tied to Databricks for the best partsExpectations, snapshot CDC and the event log are Lakeflow only; open-source Spark Declarative Pipelines has the engine, not theseKeep builds as plain functions so you can leave
Harder to see the middleDeclarative hides steps by design; seeing one join's output means extra temporary viewsUse views for steps you need to inspect

Verdict per kind of table

Gold tableDeclarative wayVerdict
SCD Type 2 dimensionsAUTO CDCDrop when silver keeps history (ours does): use the harness's dimension pipeline for stable keys. Pursue with care otherwise
Aggregate factsMaterialized viewsPursue with care: optional, on serverless, check the refresh technique
Factless factsMaterialized viewsPursue with care
Transaction facts, small to medium history, no audit needMaterialized views over silverPursue with care: watch the refresh technique
Transaction facts, large or finance-grade (restatements, correction plans)Harness as a job taskKeep the harness
Periodic snapshotsMaterialized view if small; a carried-forward level over years is costly to recomputePursue with care
Accumulating snapshotsAUTO CDC Type 1 keyed by line, ignoring empty values in updates, can fill milestones inPursue with care: test that known milestones are never blanked
Consolidated factsMaterialized viewsPursue with care: same grain traps

How to choose

  • Harness (our default) for every gold table: facts, dimensions and aggregates. It runs as a Databricks job, or on any Spark, on any cloud, with Delta or Iceberg, and the same code is tested on a laptop. It is the only choice that gives every table the same audit trail.
  • Hybrid when a team is Databricks-only and wants some aggregates as materialized views. Facts and dimensions stay in the harness; one Databricks job runs the harness, then refreshes the views.
  • All declarative only for a small mart on Databricks where nobody needs a record of changed past numbers and the team wants the least code.
  • In all three, keep the build a plain pure function. Then moving between them is a change of wrapper, not a rewrite.

Cost per DBU, open source vs Lakeflow, and where declarative helps in bronze to silver: Declarative or not. SCD Type 2 with AUTO CDC against our own code: SCD Type 2 on Databricks.