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.
- Can silver to gold be a Databricks declarative pipeline?
- What Part A's example looks like as a declarative pipeline
- Pros and cons
Part C · 1
Can silver to gold be a Databricks declarative pipeline?
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.
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.
# 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 pipeline | Compared with the harness |
|---|---|---|
| 1-2. Bookmarks, changed rows | The engine keeps them (checkpoints, refresh tracking). You write none. | Better: less code |
| 3. Which windows to rebuild | A 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 build | The same pure function, called inside the decorated function. | Same |
| 5. Keys as of the event | Your 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-keyed | In 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. Completeness | A column you compute in the view from a table of received files. | Possible |
| 6b. Checks | Expectations check one row at a time. Grain, totals and ledger balance need extra check tables, as in the sketch. | Partly |
| 6c. Row ledger | The event log has rows written per table and expectation pass and fail counts. Counts per filter and per join are not there. | Partly |
| Restatements | A 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 months | No such idea. You change silver, or force a full refresh. | Missing |
| 7. Write, then move the bookmark | Each 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
| Pro | What it gives | Matters most when |
|---|---|---|
| Far less plumbing | No bookmarks, run order, retries or checkpoints to write and test | Small teams, many simple tables |
| The dependency graph is drawn for you | Tables run in the right order; lineage and a UI come free | Many tables depending on each other |
| SCD Type 2 dimensions in a few lines | AUTO CDC handles out-of-order changes, repeats and deletes | Silver does not keep history already |
| Late dimension members fixed by themselves | A materialized view is recomputed against today's dimension, so L5's -1 becomes 4 with no extra code | Facts built as materialized views |
| Quality rules and run history in one place | Expectations with keep, drop or fail; a queryable event log | Teams without their own run log |
| Aggregates kept up to date cheaply | Incremental refresh of materialized views on serverless, when the engine can | Dashboards over big facts |
| Catches broken pipelines before they run | Validate (dry-run) finds missing tables, columns and cycles with no data | Every pipeline, in CI |
| Your build still works | The pure build from Part A runs inside unchanged, and is still tested on a laptop | Keeping the option to leave |
Cons
| Con | What goes wrong | What to do about it |
|---|---|---|
| You do not control how gold is written | The 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 log | Published numbers change silently when late data arrives | Add a job step that compares with the last published version |
| No correction plans | Fixing a closed month means changing silver or a full refresh | Keep corrections in silver as records, never edits |
| Keys are hashes, not a key map | AUTO CDC does not hand out 1, 2, 3. Hash keys work, but are big and cannot be re-pointed | Fine for most; finance-grade marts may want a key map |
| AUTO CDC stamps versions with the sequence column | If 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 tables | Expectations see one row at a time; grain and totals need check views | Write them, as in the sketch |
| Streaming-table facts freeze the dimension | Rows keep the dimension as it was when processed; a late dealer is never re-keyed without a full refresh | Build facts as materialized views, not streaming tables |
| Full refresh is a big hammer | It rereads sources from the start; if old bronze or silver files have been removed, history is lost | Protect important tables from full refresh |
| Price on classic compute | Declarative is 1.3 to 2.4 times the jobs rate per DBU on classic compute; the same rate on serverless | Use serverless, or pick the edition per pipeline |
| Tied to Databricks for the best parts | Expectations, snapshot CDC and the event log are Lakeflow only; open-source Spark Declarative Pipelines has the engine, not these | Keep builds as plain functions so you can leave |
| Harder to see the middle | Declarative hides steps by design; seeing one join's output means extra temporary views | Use views for steps you need to inspect |
Verdict per kind of table
| Gold table | Declarative way | Verdict |
|---|---|---|
| SCD Type 2 dimensions | AUTO CDC | Drop when silver keeps history (ours does): use the harness's dimension pipeline for stable keys. Pursue with care otherwise |
| Aggregate facts | Materialized views | Pursue with care: optional, on serverless, check the refresh technique |
| Factless facts | Materialized views | Pursue with care |
| Transaction facts, small to medium history, no audit need | Materialized views over silver | Pursue with care: watch the refresh technique |
| Transaction facts, large or finance-grade (restatements, correction plans) | Harness as a job task | Keep the harness |
| Periodic snapshots | Materialized view if small; a carried-forward level over years is costly to recompute | Pursue with care |
| Accumulating snapshots | AUTO CDC Type 1 keyed by line, ignoring empty values in updates, can fill milestones in | Pursue with care: test that known milestones are never blanked |
| Consolidated facts | Materialized views | Pursue 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.