Home › Learn › Data platform architecture › Bronze to silver

Data platform architecture · component · the same on every platform

Bronze to silver: clean, dedupe, keep history

The bronze-to-silver hop: common transformations, quarantine, dedupe by key, SCD Type 1 and Type 2 with MERGE, and the traps that make history hard.

↑ The whole pictureMetadata storeIngestionBronze to silverGold

In plain words

From a faithful record to one clean row per real thing

Bronze is everything as it arrived: strings, duplicates, bad rows and all. Silver is the first layer with an opinion: one clean, typed row per real thing (an order, a customer), with history kept the way the business needs, and with every row that breaks the rules set aside with its reason.

Control plane: manifest → metadata DB → generators → pipelinesSourcesLandingBronzeSilverGoldSemanticExperiencesyou are here: Bronze + Silver
This component is the hop from bronze to silver.

The steps

Common transformations, in order

StepWhat it doesExample
Select and renameKeep the agreed columns, give them their silver namescust → customer_id
Cast typesText to numbers, dates, timestamps; a failed cast goes to quarantine'1,250.00' → 1250.00
Trim and standardiseRemove stray spaces, one case, one code list'PAID ' → 'paid'; 'inr' → 'INR'
Fix timeParse with the source's time zone, store UTC27/10/2026 08:58 IST → 03:28 UTC
Handle nullsDefaults only where the contract allows; otherwise quarantinecountry null → 'unknown' (allowed)
Check the contractRequired fields, ranges, allowed values, referential checksamount ≥ 0; customer_id not null
DedupeKeep the latest row per business keyorder 1001 sent twice → one row
Keep historySCD Type 1 (overwrite) or Type 2 (new version)customer moves city
Mask personal dataPer the column's PII class and the reader's rolephone → ******3210

Each step is driven by column-level settings in the metadata store (silver name and type, PII class, transforms), not hand-written per table.

Bad rows

Quarantine, never drop

A row that fails a cast or a contract rule is written to a quarantine table with the rule it broke, the batch it came from and when. Silver gets only good rows. The owner sees a count and the reasons. Two rules: a row is never silently dropped, and a rule is never loosened just to make a job pass. A check that looked at zero rows is an error, not a pass.

Duplicates

Latest row per key

from pyspark.sql import Window, functions as F

w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc(), F.col("_ingested_at").desc())
latest = (bronze_batch
          .withColumn("_rn", F.row_number().over(w))
          .filter("_rn = 1")
          .drop("_rn"))

Keep one row per business key: the most recently updated, and if two tie, the most recently ingested.

History: Type 1

SCD Type 1: overwrite

When only the current value matters (an order's status, a corrected email), update the row in place. MERGE on the business key makes it safe to run twice: the same change applied again changes nothing. A row_hash of the tracked columns skips rows that didn't really change.

MERGE INTO silver.orders AS t
USING staged_orders AS s                      -- one row per order_id, already cleaned
ON t.order_id = s.order_id
WHEN MATCHED AND t.row_hash <> s.row_hash THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Delta Lake / Spark SQL.

History: Type 2

SCD Type 2: keep every version

When history matters (which city a customer lived in when they ordered), a change closes the current row and opens a new one. One MERGE does both, using a well-known trick: changed rows are sent twice, once with the key (to close the old version) and once without (so it is inserted as the new version).

MERGE INTO silver.customer AS t
USING (
  -- every staged row, keyed by customer_id: closes the current version if it changed
  SELECT s.customer_id AS merge_key, s.* FROM staged_customer s
  UNION ALL
  -- a second copy of changed rows, with no key, so it is inserted as the new version
  SELECT NULL AS merge_key, s.* FROM staged_customer s
  JOIN silver.customer c ON c.customer_id = s.customer_id AND c.is_current
  WHERE c.row_hash <> s.row_hash
) AS u
ON t.customer_id = u.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> u.row_hash THEN
  UPDATE SET is_current = false, valid_to = u.changed_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, city, email, row_hash, valid_from, valid_to, is_current)
  VALUES (u.customer_id, u.name, u.city, u.email, u.row_hash, u.changed_at, NULL, true);

Delta Lake / Spark SQL. staged_customer has one row per customer_id, with changed_at = when the change happened in the source.

customer_id | city   | valid_from          | valid_to            | is_current
C-77        | Pune   | 2024-03-02 10:00:00 | 2026-11-15 09:00:00 | false
C-77        | Sydney | 2026-11-15 09:00:00 | NULL                | true

Asha (C-77) moves from Pune to Sydney on 15 November. Last year's orders still join to Pune.

What makes Type 2 hard

More: slowly changing dimensions, idempotency.

What goes wrong

Common problems

Two current rows for one customer

Reports double-count; joins fan out.

Naive fix

Pick one at random in the query.

Right fix

Close and open in one MERGE; test 'one current row per key' after every run.

A column renamed upstream

The silver column goes all null overnight.

Naive fix

Map the new name by hand in production.

Right fix

The contract check fails the hop and quarantines; the mapping is fixed in the manifest by pull request.

Bad rows silently dropped

Totals are a little low and nobody knows why.

Naive fix

A WHERE clause that filters them out.

Right fix

Quarantine with a reason; reconcile counts: bronze = silver + quarantine.

Re-running adds rows

A retried job inserts the batch again.

Naive fix

Delete the batch by hand, then re-run.

Right fix

MERGE on the business key; INSERT never appears in silver code.

Concept vs product

On each platform

JobAWSAzureGoogle CloudDatabricksOpen source
Transform engineGlue or EMR Spark; dbt on AthenaFabric Spark; dbt on FabricDataproc; Dataform or dbt on BigQueryLakeflow Declarative Pipelines or jobsPySpark; dbt + DuckDB
MERGE / upsertIceberg MERGEDelta MERGEBigQuery MERGEDelta MERGE; AUTO CDC (formerly APPLY CHANGES) for SCDDelta or Iceberg MERGE
Contract and quality checksGlue Data QualityPurview data qualityDataplex data qualityPipeline expectationsGreat Expectations or Soda
MaskingLake FormationPurview + SQL maskingPolicy tagsUnity Catalog column masksviews per role

Product names change often; check the current docs before you build.

FAQ

Questions people ask

Should silver be modelled as a star schema?

No: silver is one clean row per real thing, close to the source's entities. Shaping for questions (facts and dimensions) is gold's job.

SCD Type 1 or Type 2?

Type 2 where history changes the meaning of past numbers (a customer's region, a product's category). Type 1 where only the current value matters or the old one was simply wrong.

Where do quality checks run?

The contract is enforced at this hop, before rows reach silver. Gold adds reconciliation.

Can I use AUTO CDC (APPLY CHANGES) instead of writing MERGE?

Yes on Databricks: Lakeflow Declarative Pipelines' AUTO CDC, formerly called APPLY CHANGES, handles Type 1 and Type 2, including out-of-order changes ordered by a sequence column. The rules on this page still apply.

Next

Where to go next

↑ The whole picture · The medallion layers · Key concepts · The Data Engineering path

See it run: DataTwin Studio plays this component step by step on a real recorded run, including what happens when the same file arrives twice.