Silver layer

Overview

The silver layer is the second layer of a medallion architecture. It takes raw records from the bronze layer and turns them into clean, consistent business entities. Data is validated, cast to proper types, deduplicated, standardized and joined with reference data. The result is a trusted base that analysts, engineers and machine learning teams can all use.

Silver is where most of the real data work happens. It is detailed rather than aggregated, so it keeps the grain of the source events or records. That detail is what makes it reusable: many different gold layer tables, reports and models can be built from the same silver tables without repeating the cleaning.

For anyone who trains or evaluates models on tables, silver is usually the most important layer. It has stable column types, one row per entity or event, documented meaning, and a clear record of what was rejected. It is also where decisions about missing values are made, so those decisions deserve care.

What it is

Silver is the cleansed, conformed and enriched zone of a lakehouse. It sits between the raw record in bronze and the business views in gold.

Analogy: a kitchen prep station. Bronze is the delivery of raw vegetables. Silver is the washed, peeled and chopped ingredients, sorted into labeled containers. Gold is the finished dishes. A cook can make many dishes from the same prepared ingredients, which is why prep is shared.

What silver contains

• One table per business entity, such as customers, orders, devices or accounts.
• Event tables at the grain of the source, such as page views or payments.
• Typed columns with agreed names, units and time zones.
• Technical columns, such as update time, source system and a record hash.
• Quarantine tables with rows that failed checks, plus the reason.
• Optional history tables that track changes over time.

What silver does

What silver does not do

• It does not aggregate for specific reports. That belongs in gold.
• It does not hide bad data. It quarantines and reports it.
• It does not replace the raw record. Bronze stays as the source of replay.

Where it came from

The idea of a cleaned, integrated core layer is long established. Classic warehouses had a staging area, a core integrated model and presentation marts. The integrated core was where data from many systems was reconciled. Silver plays that role in a lakehouse.

In early data lakes, cleaning happened in scattered scripts with no shared rules. Different teams cleaned the same data in different ways and got different answers. The medallion naming gave teams a simple way to say: clean once, in a shared place, and reuse.

Table formats with transactions, such as Delta Lake and Apache Iceberg, made silver practical. They support merge operations for upserts, schema enforcement, time travel and row level deletes. Transformation tools such as dbt and engines such as Apache Spark gave teams ways to express and test the logic.

How it works

Step 1: Read new bronze data

The silver job reads only what is new since last time. It can use a high water mark on ingestion time, table snapshots or a streaming checkpoint. Reading everything each time wastes compute.

Step 2: Parse and cast types

Bronze often holds text. Silver parses it into proper types. Parsing should be explicit about formats, such as yyyy-MM-dd, decimal separators and time zones. Values that cannot be parsed go to quarantine rather than becoming silent nulls.

Analogy: a customs desk. Each parcel is opened, checked against its label and either cleared or set aside with a note.

Step 3: Standardize

Make equivalent values look the same. Examples are trimming spaces, fixing case, mapping US, USA and United States to one code, converting units, and moving timestamps to UTC.

Step 4: Validate

Apply rules. Some are structural: required fields present, numbers in range, dates plausible. Others are relational: the customer id must exist in the customer table. Decide for each rule whether failure means reject, fix, flag or just warn.

Step 5: Quarantine failures

Rows that fail go to a quarantine table with the rule name, the reason and the original values. This preserves evidence and allows repair later.

Step 6: Deduplicate

Find the business key for each entity and keep one row per key, usually the newest by source time. Ties are broken by ingestion time. Exact duplicates are dropped. Near duplicates, such as the same person with slightly different names, need matching rules and are a harder problem.

Step 7: Conform across sources

Different systems use different ids and names for the same thing. Silver builds mapping tables or uses matching logic so that one customer in two systems becomes one conformed customer. Shared definitions, such as what counts as an active user, are fixed here.

Step 8: Enrich

Join reference data such as product catalogs, geographies and currency rates. Keep the keys as well as the descriptions.

Step 9: Merge into the silver table

Use an upsert to apply new and changed rows. Handle deletes according to the source: a hard delete event may mark a row as deleted, or remove it, or move it to history.

Step 10: Track history where needed

For attributes that change and matter over time, such as customer address or product price, use a slowly changing dimension pattern. Type 2 history adds a new row with valid-from and valid-to dates and marks the older row as no longer current.

Step 11: Test and publish

Run tests: key uniqueness, not null, accepted values, row count reconciliation against bronze. Only then mark the data as published. Record lineage.

Key concepts and terms

A worked example

This example builds a silver customer table from two bronze sources using PySpark and SQL. Names are placeholders.

Inputs

Two bronze tables hold customer data.

• bronze.crm_customers_raw has fields cust_no, full_name, email, country, signup, _ingested_at.
• bronze.shop_users_raw has fields user_id, email, country_code, created_at, _ingested_at.

Step 1: Standardize each source

from pyspark.sql import functions as F

crm = (
    spark.table("lake.bronze.crm_customers_raw")
    .select(
        F.col("cust_no").cast("string").alias("source_key"),
        F.lit("crm").alias("source_system"),
        F.initcap(F.trim("full_name")).alias("full_name"),
        F.lower(F.trim("email")).alias("email"),
        F.upper(F.trim("country")).alias("country"),
        F.to_date("signup", "yyyy-MM-dd").alias("signup_date"),
        "_ingested_at",
    )
)

shop = (
    spark.table("lake.bronze.shop_users_raw")
    .select(
        F.col("user_id").cast("string").alias("source_key"),
        F.lit("shop").alias("source_system"),
        F.lit(None).cast("string").alias("full_name"),
        F.lower(F.trim("email")).alias("email"),
        F.upper(F.trim("country_code")).alias("country"),
        F.to_date("created_at").alias("signup_date"),
        "_ingested_at",
    )
)

Step 2: Validate and quarantine

unioned = crm.unionByName(shop)

rules = (
    F.col("email").rlike(r"^[^@\\s]+@[^@\\s]+\\.[^@\\s]+$")
    & F.col("source_key").isNotNull()
)

valid = unioned.filter(rules)
rejected = (
    unioned.filter(~rules | rules.isNull())
    .withColumn("_reason", F.lit("bad_email_or_missing_key"))
)

rejected.writeTo("lake.silver.customers_quarantine").append()

Step 3: Deduplicate and conform

from pyspark.sql import Window

w = Window.partitionBy("email").orderBy(
    F.col("signup_date").asc_nulls_last(), F.col("_ingested_at").desc()
)

conformed = (
    valid
    .withColumn("rn", F.row_number().over(w))
    .withColumn("customer_id", F.sha2(F.col("email"), 256))
)

Here the email address is used as the matching key to build one conformed customer from both systems. In practice, matching may use more fields and a review step.

Step 4: Merge into silver

latest = conformed.filter("rn = 1").drop("rn")
latest.createOrReplaceTempView("customers_latest")

MERGE INTO lake.silver.customers AS t
USING customers_latest AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s._ingested_at > t._ingested_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Step 5: Test

-- Key must be unique
SELECT customer_id, COUNT(*) AS n
FROM lake.silver.customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- Reconcile counts
SELECT
  (SELECT COUNT(*) FROM lake.bronze.crm_customers_raw)  AS crm_rows,
  (SELECT COUNT(*) FROM lake.bronze.shop_users_raw)     AS shop_rows,
  (SELECT COUNT(*) FROM lake.silver.customers)          AS silver_rows,
  (SELECT COUNT(*) FROM lake.silver.customers_quarantine) AS quarantined;

The first query should return no rows. The second should show that silver plus quarantine plus removed duplicates equals the bronze input.

Numeric walk-through

Suppose 5,000 CRM rows and 8,000 shop rows arrive.

• 150 rows have invalid emails, so 150 go to quarantine and 12,850 remain.
• 2,000 people appear in both systems, so after matching on email, 12,850 minus 2,000 gives 10,850 distinct customers.
• Silver therefore holds 10,850 customers, quarantine holds 150, and the audit trail explains the difference between 13,000 raw rows and 10,850 customers.

A history table with SCD type 2

-- Close the old version when the address changes
MERGE INTO lake.silver.customer_history AS t
USING customer_changes AS s
ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND t.address <> s.address THEN
  UPDATE SET is_current = false, valid_to = s.change_ts
WHEN NOT MATCHED THEN
  INSERT (customer_id, address, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.address, s.change_ts, NULL, true);

A second statement inserts the new current row for customers whose old row was just closed. Many teams run both steps inside one transaction or use a pattern that does both at once.

Strengths and limitations

Strengths

• A single place for cleaning rules, so everyone gets the same answer.
• Detailed grain keeps flexibility for many downstream uses.
• Quality is visible through tests and quarantine tables.
• Easier debugging, since you can compare silver to bronze row by row.
• A natural base for machine learning features and training sets.
• Reduces repeated work across teams.
• Supports incremental processing and history.

Limitations

• Cleaning rules embed assumptions that may be wrong. Fixing them means reprocessing.
• Entity matching across systems is hard and never perfect.
• Over-cleaning can remove useful signal, such as the pattern of missing values.
• Large silver tables can be costly to maintain and merge.
• Late and out-of-order data need careful handling.
• Schema changes at the source can break jobs.
• Teams may argue over definitions, which is a social problem more than a technical one.

How it compares with alternatives

Silver can be implemented with SQL models in dbt, Spark jobs, streaming pipelines, or a mix. See Medallion architecture for the full picture.

Role in tabular foundation models

Tabular foundation models learn from raw tables: rows and columns with mixed types, missing values and sometimes weak or missing column names. Silver is where raw tables become well behaved enough to use at scale, and where cleaning choices can help or hurt such a model.

Typed, consistent columns

A model that handles mixed types still benefits when a date is a date and a decimal is a decimal. Silver fixes the types and units so that columns mean one thing. This reduces noise during fine-tuning and evaluation. It also makes it possible to compare tables across sources.

Missing values: preserve the signal

Missingness is often informative. A blank income field may correlate with the outcome. If silver fills every gap with a mean or zero, the model loses that signal and may learn false patterns. A safer approach is to keep nulls as nulls, standardize the different ways of writing "missing" into one true null, and let the model or a documented step handle imputation. When imputation is done in silver, add an indicator column that records which values were filled.

For research on imputation, silver is also a good test bed. Take a complete silver table, hide values at random or by a pattern, ask the model to fill them, and score the answers against the truth.

Rows, entities and grain

Models read rows as the unit of learning. If silver has duplicates, the model sees the same example many times and may overfit. If it has the wrong grain, such as order lines mixed with order headers, labels and features no longer match. Deduplication and a clearly documented grain make training sets sound.

Column names and semantic labels

Some models are designed to work even when column names are missing or unhelpful. Silver is where a data dictionary is written: each column gets a business name, a type, a unit and a description. These records can serve as ground truth for tasks such as semantic type detection and sector classification, where a model tries to guess what a column or table is about from its values. See Data catalog.

Pre-training, fine-tuning and evaluation

Pre-training on real tables benefits from variety, and silver supplies tables in a form that can be loaded in bulk. Fine-tuning on one domain uses a domain's silver tables with labels. Evaluation needs fixed, versioned datasets, which table snapshots provide. Record the snapshot id with every result. See /model/research, /model/architecture and /model/benchmarks.

In-context learning

Models such as TabPFN take a labeled table as context at prediction time and do not update their weights. A clean silver slice is a natural context: rows of one entity type, typed columns, a label column and a modest size. A service can pull a recent slice from silver and pass it to the model.

Continual learning

Continual learning updates a model as new data arrives. Incremental silver jobs create a steady stream of new, validated rows. The history tables show how entities change, which helps test whether a model keeps old knowledge. Record which snapshot each update used.

Anomaly detection

Quarantine tables hold labeled examples of failed rules, which makes them a free source of test cases for anomaly detection. Silver can also hold the reference distributions against which new batches are compared. See Anomaly detection and Schema drift detection.

Leakage and fairness

Silver tables often include fields that are known only after the outcome, such as a cancellation date. Using them as features leaks the label. Build training sets with point-in-time logic that only uses information available at prediction time. Also review sensitive attributes and proxies, and apply masking or tokenization where required. See Data tokenization and Data lineage.

Practical guidance and best practices

Define the grain and key for every table

Write down what one row is and what makes it unique. Test uniqueness on every run.

Be explicit about types and formats

Never rely on automatic type guessing. Parse with explicit formats and treat failures as quarantine cases.

Quarantine, count and report

Count rows at each step and reconcile. Publish the quarantine rate as a quality metric.

Keep nulls honest

Do not replace missing values with made-up numbers without a record. If you impute, add a flag column and document the method.

Make merges idempotent

Rerunning a job should not change the result. Merge by key, compare timestamps and avoid appending blindly.

Use contracts and drift checks

Define expected schemas and ranges with the source teams. Alert when they change. See Data contract validation and Schema evolution.

Handle late data deliberately

Order by the source event time, not just the arrival time. Reprocess affected partitions when late data arrives.

Choose history carefully

Not every table needs full history. Keep it where the business or the model needs to know what was true at a past time.

Keep silver reusable

Avoid shaping silver for one report. Keep detailed grain and generic names. Specialization belongs in gold.

Keep physical layout healthy

Merges create many small files and delete files. Compact on a schedule and expire old snapshots. See Iceberg compaction.

Document and register

Describe each table and column in a catalog, name an owner, and record lineage back to bronze. See Data catalog.

Version your logic

Store transformation code in version control, tag releases and note which version produced which data. When you change a rule, reprocess from bronze and compare the results before switching.

Silver quality metrics worth tracking

Choosing between current-state and history tables

A current-state table has one row per entity and answers "what is true now?" A history table has many rows per entity with date ranges and answers "what was true on a given day?"

Use current-state when users only need the latest values and storage or merge cost matters. Use history when reports must be reproducible for past dates, when audits ask what was known at a time, or when a model needs point-in-time features. Many teams keep both: a history table as the source, and a current-state view on top of it.

CREATE OR REPLACE VIEW lake.silver.customers_current AS
SELECT * FROM lake.silver.customer_history
WHERE is_current = true;

A point-in-time join for model training

To build training rows without leakage, join each label event to the version of the feature row that was valid at that moment.

SELECT
  e.event_id,
  e.event_ts,
  e.label,
  h.country,
  h.segment
FROM lake.silver.churn_events e
JOIN lake.silver.customer_history h
  ON h.customer_id = e.customer_id
 AND e.event_ts >= h.valid_from
 AND (h.valid_to IS NULL OR e.event_ts < h.valid_to);

Each training row now uses only what was known at the event time.

Common mistakes

• Skipping silver and building gold directly from bronze.
• Silent failures, where unparseable values become nulls with no record.
• Filling missing values with zeros or means and hiding the fact.
• Deduplicating by a weak key and merging different people into one.
• Using arrival time instead of event time to pick the newest record.
• Overwriting entities without history when the business needs history.
• Building silver tables that serve only one report.
• Ignoring referential integrity, leaving orphan keys.
• Not testing, so errors appear first in dashboards.
• Letting silver and gold definitions of the same metric disagree.
• Forgetting to apply privacy deletions to silver after they are applied elsewhere.
• Letting merge jobs create thousands of small files with no maintenance.

Frequently asked questions

What is the silver layer?

The silver layer is the second layer of a medallion architecture. It holds data from bronze that has been validated, typed, deduplicated, standardized and joined into consistent entities. It is the trusted, detailed base for analytics and machine learning.

What is the difference between bronze, silver and gold?

Bronze keeps raw data as it arrived. Silver cleans and conforms it into reliable entities at a detailed grain. Gold shapes silver into business-ready tables, often aggregated, for specific uses such as dashboards and features.

Should silver be aggregated?

Usually not. Silver keeps the detail so that many different gold tables can be built from it. Aggregation loses information and ties the table to one use. Light summaries are fine when they serve many consumers.

How should silver handle missing values?

Convert the many ways of writing "missing" into a true null, and keep nulls unless there is a clear reason to fill them. If you impute, record which values were filled and how. This keeps useful signal available for analysts and models.

What happens to rows that fail validation?

They should go to a quarantine table with the rule that failed and the original values. This keeps an audit trail, allows repairs and lets you track quality over time. Silently dropping rows hides problems.

How do I deduplicate in silver?

Pick a business key, then keep one row per key using a rule such as the newest source timestamp. Break ties with ingestion time. For records without a clean key, use matching logic, and test it carefully because wrong merges are hard to undo.

Do I need history in silver?

Only where it matters. If the business or a model needs to know what was true at a past date, use a slowly changing dimension or an event history table. Otherwise a current-state table is simpler and cheaper.

Is silver good enough for training machine learning models?

Often yes. It has clean types, one row per entity or event and documented meaning. For training, add point-in-time logic to avoid leakage, and consider whether cleaning removed any signal the model could use.

How is silver kept up to date?

Incremental jobs read new bronze data, validate and transform it, then merge it into silver. They can run on a schedule or as streams. Checkpoints or high water marks track progress, and idempotent merges make reruns safe.

What if my cleaning rules turn out to be wrong?

Fix the rules and reprocess silver from bronze. That is the main reason to keep the raw layer. Compare the new output against the old one before switching consumers, and tag the snapshot you replace in case you need to roll back.

Glossary

• Business key: A natural identifier for an entity, such as an order number.
• Conformed data: Data aligned to shared keys and definitions.
• Deduplication: Keeping one row for each distinct entity or event.
• Grain: The meaning of a single row in a table.
• Idempotent: Producing the same result when run again.
• Quarantine table: A table for rows that failed checks, with reasons.
• Reconciliation: Comparing counts or totals across layers.
• Referential integrity: The rule that a foreign key must match an existing row.
• Schema enforcement: Blocking writes that do not match the table schema.
• SCD type 2: A method that keeps old versions of a row with date ranges.
• Silver layer: The cleansed and conformed layer of a medallion architecture.
• Surrogate key: A generated key not taken from the source.
• Upsert: An operation that updates existing rows and inserts new ones.
• Watermark: A time limit used to decide how long to wait for late data.

Summary

The silver layer turns raw bronze records into trusted, reusable entities. It parses types, standardizes values, validates rules, quarantines failures, removes duplicates, conforms keys across sources and keeps history where it matters. Its detailed grain lets many gold tables and models share the same clean base.

For tabular foundation models, silver is the practical home of training and evaluation data. Good silver tables have honest types, true nulls, clear grain, documented columns and versioned snapshots. Be careful not to over-clean, avoid leakage, and keep a complete audit trail so that every number and every model result can be traced back to the raw record.

Related

It belongs to Table formats and lakehouse in my stack, under Data engineering and lakehouse. Others in the same group: