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.
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.
The steps
Common transformations, in order
| Step | What it does | Example |
|---|---|---|
| Select and rename | Keep the agreed columns, give them their silver names | cust → customer_id |
| Cast types | Text to numbers, dates, timestamps; a failed cast goes to quarantine | '1,250.00' → 1250.00 |
| Trim and standardise | Remove stray spaces, one case, one code list | 'PAID ' → 'paid'; 'inr' → 'INR' |
| Fix time | Parse with the source's time zone, store UTC | 27/10/2026 08:58 IST → 03:28 UTC |
| Handle nulls | Defaults only where the contract allows; otherwise quarantine | country null → 'unknown' (allowed) |
| Check the contract | Required fields, ranges, allowed values, referential checks | amount ≥ 0; customer_id not null |
| Dedupe | Keep the latest row per business key | order 1001 sent twice → one row |
| Keep history | SCD Type 1 (overwrite) or Type 2 (new version) | customer moves city |
| Mask personal data | Per the column's PII class and the reader's role | phone → ******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
- Two changes for one key in one batch: the MERGE above takes one row per key. Apply the changes in order (one MERGE per sequence step), or collapse them and lose the in-between version on purpose.
- Changes out of order: use the source's change time, never the load time, for
valid_from; a late, older change must slot into the past, not become 'current'. - No-op updates: compare
row_hash, or every touch of a row opens a new version. - Deletes: close the current row and mark it deleted (a soft delete), so history stays.
- The guarantee to test: exactly one current row per key, and no overlapping valid ranges.
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
| Job | AWS | Azure | Google Cloud | Databricks | Open source |
|---|---|---|---|---|---|
| Transform engine | Glue or EMR Spark; dbt on Athena | Fabric Spark; dbt on Fabric | Dataproc; Dataform or dbt on BigQuery | Lakeflow Declarative Pipelines or jobs | PySpark; dbt + DuckDB |
| MERGE / upsert | Iceberg MERGE | Delta MERGE | BigQuery MERGE | Delta MERGE; AUTO CDC (formerly APPLY CHANGES) for SCD | Delta or Iceberg MERGE |
| Contract and quality checks | Glue Data Quality | Purview data quality | Dataplex data quality | Pipeline expectations | Great Expectations or Soda |
| Masking | Lake Formation | Purview + SQL masking | Policy tags | Unity Catalog column masks | views 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.