Medallion architecture: bronze, silver, gold, and when layers are just bureaucracy

A data pipeline can have bronze, silver, and gold tables, beautifully organized schemas, dozens of scheduled jobs, and still produce numbers nobody trusts.

The problem is rarely the absence of layers. It is the absence of clear contracts between layers.

Medallion architecture offers a useful way to organize data engineering systems: preserve source information, transform it into reliable data, and publish business-ready datasets. But adopting three layers without understanding their responsibilities can introduce unnecessary storage, latency, operational complexity, and duplicated business logic.

The right question is not “How do we implement bronze, silver, and gold?” It is:

What reliability or usability problem does each additional layer solve?

This tutorial answers that question from both an architectural and implementation perspective.

We will build a working retail order pipeline using DuckDB and Python, deliberately introduce corrupted and duplicate records, enforce contracts, calculate business metrics, and examine the situations where three layers are genuinely valuable—or simply bureaucracy.

1. Understanding the medallion architecture

Medallion architecture organizes data processing into stages of increasing quality and business usefulness.

The conventional flow is straightforward:

BRONZE  ->  raw source records, ingestion metadata, replayability
   |
   v
SILVER  ->  validated, typed, deduplicated, reusable data
   |
   v
GOLD    ->  business metrics, reporting models, consumer-facing datasets

The important distinction is that these are logical quality boundaries, not necessarily three storage systems, three catalogs, or even three physically materialized tables.

The Databricks medallion architecture documentation, updated in September 2026, describes the approach as a recommended design pattern rather than an architectural requirement.

That distinction matters. A small DuckDB database, a managed warehouse, and a distributed lakehouse can all apply the same concepts without implementing them identically.

Bronze: preserve what happened

Bronze is the ingestion and recovery layer.

Its responsibility is to preserve source records with enough information to understand where they came from and replay them when necessary.

Typical bronze information includes:

• The original event payload or source columns.

• Source file, topic, partition, or offset.

• Ingestion timestamp and batch identifier.

• Source-system identifiers.

• Schema version, when available.

Bronze should avoid transformations that destroy information.

If an incoming record contains an invalid price, silently dropping the record during ingestion removes the evidence needed to investigate the failure.

A useful principle is: bronze prioritizes fidelity over cleanliness.

Silver: make records trustworthy

Silver transforms raw data into reusable, validated representations.

Typical responsibilities include:

• Parsing and enforcing data types.

• Validating identifiers and required fields.

• Deduplicating retried events.

• Standardizing timestamps and currencies.

• Normalizing values across source systems.

• Separating invalid records for investigation.

• Applying consistent entity definitions.

Silver is often the most valuable layer in a larger data platform because multiple downstream consumers can reuse its work.

Without a shared silver representation, several teams may independently implement slightly different definitions of the same customer, order, or transaction.

Gold: make data useful to consumers

Gold turns validated data into explicitly defined products.

A gold dataset might provide daily sales, order conversion metrics, customer segments, inventory availability, or model-ready features.

Its central responsibility is not aggregation alone. It is semantic consistency.

For example, a column called revenue requires answers to questions such as:

• Are cancelled orders excluded?

• Are refunds represented?

• Are taxes and shipping included?

• Which currency is used?

• Does the metric represent booked, paid, or recognized revenue?

• Can historical values change when late events arrive?

A gold table without those definitions may be convenient to query but still unreliable.

2. Contracts matter more than layer names

Before writing SQL, define what each boundary guarantees.

For our example, the source is a JSON Lines file containing retail order events.

The phrase data grain deserves attention.

Grain specifies what one row represents.

Bronze contains received source records. Silver contains unique business events. Gold contains daily summaries.

Confusing these grains causes subtle bugs.

If a downstream query counts silver events as orders, an order that was placed and later cancelled might count twice. If it counts bronze records, network retries could inflate the number even further.

A strong architecture states grain explicitly for every published dataset.

3. Our runnable DuckDB pipeline

We’ll implement the full flow with DuckDB’s Python client and SQL.

DuckDB is particularly useful for this exercise because it supports persistent databases, JSON processing, analytical window functions, transactions, and Parquet interoperability without needing a distributed compute cluster.

The example uses the current stable DuckDB 1.5 series and standard APIs documented in the DuckDB Python client reference.

Step 1: Set up the environment

Run these commands in a Bash-compatible shell:

mkdir -p medallion-demo
cd medallion-demo

python -m venv .venv
source .venv/bin/activate

python -m pip install "duckdb==1.5.5"

The directory will contain our Python pipeline, synthetic JSON Lines input, and persistent DuckDB database.

No external dataset or cloud credentials are required.

Step 2: Implement the complete pipeline

Create a file named pipeline.py and add the following code.

This is one self-contained implementation. It generates its own input data on the first run, preserves raw records, rebuilds the downstream tables, and checks expected results.

import json
from pathlib import Path

import duckdb

ROOT = Path(__file__).resolve().parent
SOURCE = ROOT / "orders.jsonl"
DATABASE = ROOT / "shop.duckdb"

# Synthetic retail order events.
# Includes valid events, a replay, and invalid values.
sample = [
    ("e001", "o100", "2026-10-01T10:00:00Z", "placed", "40.00"),
    ("e002", "o101", "2026-10-01T11:00:00Z", "placed", "25.50"),
    ("e003", "o102", "2026-10-01T12:00:00Z", "placed", "12.00"),
    ("e004", "o101", "2026-10-02T08:00:00Z", "cancelled", "25.50"),
    ("e005", "o103", "2026-10-02T09:00:00Z", "placed", "80.00"),
    ("e005", "o103", "2026-10-02T09:00:00Z", "placed", "80.00"),
    ("e006", "o104", "2026-10-02T10:00:00Z", "placed", "-5.00"),
    ("e007", "o105", "2026-10-02T10:30:00Z", "placed", "not-a-number"),
    ("e008", "o106", "2026-10-02T11:00:00Z", "shipped", "34.00"),
    ("e009", "o107", "2026-10-02T12:00:00Z", "placed", "15.00"),
    ("e010", "o108", "2026-10-03T12:00:00Z", "placed", "5.00"),
]

# Create immutable sample input only if it does not exist.
if not SOURCE.exists():
    rows = [
        dict(
            event_id=event_id,
            order_id=order_id,
            occurred_at=occurred_at,
            status=status,
            amount=amount,
        )
        for event_id, order_id, occurred_at, status, amount in sample
    ]

    lines = [json.dumps(row, sort_keys=True) for row in rows]

    # One deliberately malformed JSON line.
    lines.append('{"event_id": "broken"')

    SOURCE.write_text(
        "\n".join(lines) + "\n",
        encoding="utf-8",
    )

con = duckdb.connect(str(DATABASE))
con.execute("SET TimeZone = 'UTC'")

try:
    con.execute("BEGIN TRANSACTION")

    # -----------------------------------------
    # BRONZE: preserve raw source records
    # -----------------------------------------
    con.execute("""
        CREATE TABLE IF NOT EXISTS bronze_orders (
            source_file VARCHAR NOT NULL,
            source_line BIGINT NOT NULL,
            payload VARCHAR NOT NULL,
            ingested_at TIMESTAMPTZ NOT NULL
                DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (source_file, source_line)
        )
    """)

    source_rows = [
        (SOURCE.name, line_no, payload)
        for line_no, payload in enumerate(
            SOURCE.read_text(encoding="utf-8").splitlines(),
            start=1,
        )
    ]

    # The source-file/line key makes re-ingestion
    # of this immutable input idempotent.
    con.executemany("""
        INSERT INTO bronze_orders (
            source_file, source_line, payload
        )
        VALUES (?, ?, ?)
        ON CONFLICT DO NOTHING
    """, source_rows)

    # -----------------------------------------
    # SILVER STAGING: parse and validate
    # -----------------------------------------
    con.execute("""
        CREATE OR REPLACE TEMP VIEW staged_events AS
        WITH decoded AS (
            SELECT
                source_file,
                source_line,
                payload,
                TRY_CAST(payload AS JSON) AS raw_json
            FROM bronze_orders
        ),
        typed AS (
            SELECT
                source_file,
                source_line,
                payload,
                raw_json,
                json_extract_string(
                    raw_json, '$.event_id'
                ) AS event_id,
                json_extract_string(
                    raw_json, '$.order_id'
                ) AS order_id,
                TRY_CAST(
                    json_extract_string(
                        raw_json, '$.occurred_at'
                    ) AS TIMESTAMPTZ
                ) AS occurred_at,
                json_extract_string(
                    raw_json, '$.status'
                ) AS status,
                TRY_CAST(
                    json_extract_string(
                        raw_json, '$.amount'
                    ) AS DECIMAL(12, 2)
                ) AS amount
            FROM decoded
        )
        SELECT *,
            CASE
                WHEN raw_json IS NULL
                    THEN 'invalid_json'
                WHEN event_id IS NULL
                     OR trim(event_id) = ''
                    THEN 'missing_event_id'
                WHEN order_id IS NULL
                     OR trim(order_id) = ''
                    THEN 'missing_order_id'
                WHEN occurred_at IS NULL
                    THEN 'invalid_timestamp'
                WHEN status IS NULL
                     OR status NOT IN (
                         'placed', 'cancelled'
                     )
                    THEN 'invalid_status'
                WHEN amount IS NULL OR amount < 0
                    THEN 'invalid_amount'
                ELSE NULL
            END AS reject_reason
        FROM typed
    """)

    # Store invalid records for investigation.
    con.execute("""
        CREATE OR REPLACE TABLE silver_rejects AS
        SELECT
            source_file,
            source_line,
            reject_reason,
            payload
        FROM staged_events
        WHERE reject_reason IS NOT NULL
    """)

    # Rank valid events for deterministic deduplication.
    con.execute("""
        CREATE OR REPLACE TEMP VIEW numbered_valid_events AS
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY event_id
                ORDER BY source_file, source_line
            ) AS duplicate_rank
        FROM staged_events
        WHERE reject_reason IS NULL
    """)

    # Canonical, typed events.
    con.execute("""
        CREATE OR REPLACE TABLE silver_order_events AS
        SELECT
            event_id,
            order_id,
            occurred_at,
            status,
            amount,
            source_file,
            source_line
        FROM numbered_valid_events
        WHERE duplicate_rank = 1
    """)

    # Preserve evidence of replayed event identifiers.
    con.execute("""
        CREATE OR REPLACE TABLE silver_duplicate_events AS
        SELECT
            event_id,
            source_file,
            source_line,
            payload
        FROM numbered_valid_events
        WHERE duplicate_rank > 1
    """)

    # -----------------------------------------
    # GOLD: business-ready daily summary
    # -----------------------------------------
    con.execute("""
        CREATE OR REPLACE TABLE gold_daily_order_summary AS
        WITH first_placements AS (
            SELECT
                order_id,
                MIN(occurred_at) AS placed_at
            FROM silver_order_events
            WHERE status = 'placed'
            GROUP BY order_id
        ),
        ranked_events AS (
            SELECT *,
                ROW_NUMBER() OVER (
                    PARTITION BY order_id
                    ORDER BY occurred_at DESC, event_id DESC
                ) AS latest_rank
            FROM silver_order_events
        ),
        current_orders AS (
            SELECT
                CAST(p.placed_at AS DATE) AS order_date,
                e.status AS current_status,
                e.amount AS latest_amount
            FROM ranked_events e
            JOIN first_placements p USING (order_id)
            WHERE e.latest_rank = 1
        )
        SELECT
            order_date,
            COUNT(*) AS orders_created,
            COUNT(*) FILTER (
                WHERE current_status = 'cancelled'
            ) AS cancelled_orders,
            COUNT(*) FILTER (
                WHERE current_status = 'placed'
            ) AS active_orders,
            CAST(
                SUM(
                    CASE
                        WHEN current_status = 'placed'
                            THEN latest_amount
                        ELSE 0
                    END
                ) AS DECIMAL(18, 2)
            ) AS retained_order_value
        FROM current_orders
        GROUP BY order_date
    """)

    # -----------------------------------------
    # DATA CONTRACT TESTS
    # -----------------------------------------
    counts = tuple(
        con.execute(
            f"SELECT COUNT(*) FROM {table}"
        ).fetchone()[0]
        for table in (
            "bronze_orders",
            "silver_order_events",
            "silver_rejects",
            "silver_duplicate_events",
        )
    )

    assert counts == (12, 7, 4, 1), counts

    actual = con.execute("""
        SELECT
            CAST(order_date AS VARCHAR),
            orders_created,
            cancelled_orders,
            active_orders,
            retained_order_value
        FROM gold_daily_order_summary
        ORDER BY order_date
    """).fetchall()

    expected = [
        ("2026-10-01", 3, 1, 2, 52),
        ("2026-10-02", 2, 0, 2, 95),
        ("2026-10-03", 1, 0, 1, 5),
    ]

    assert actual == expected, actual

    con.execute("COMMIT")

except Exception:
    con.execute("ROLLBACK")
    raise

print("bronze, silver, rejected, replayed:", counts)

for row in actual:
    print(*row, sep=" | ")

con.close()

Step 3: Execute and rerun it

python pipeline.py
python pipeline.py

Both executions should produce the same logical results:

bronze, silver, rejected, replayed: (12, 7, 4, 1)
2026-10-01 | 3 | 1 | 2 | 52.00
2026-10-02 | 2 | 0 | 2 | 95.00
2026-10-03 | 1 | 0 | 1 | 5.00

The second execution is important.

The pipeline does not ingest the original 12 lines again as new bronze records. Its primary key identifies the same immutable source positions, while the silver and gold tables are deterministically rebuilt.

This is a small but meaningful demonstration of idempotence: rerunning a processing job does not multiply its business results.

The sample assertions are intended for this exact synthetic dataset. Production pipelines should replace hard-coded counts with general invariants and monitored expectations.

4. Why the bronze implementation works

Our bronze table deliberately stores the input payload as a string.

That choice prevents malformed JSON, unknown source fields, and failed type conversions from destroying the evidence.

Consider the record containing a negative order amount. It stays in bronze exactly as received.

The silver transformation rejects it, but data engineers can still inspect its source position and original value.

The important bronze design decisions are:

Raw payload retention. We keep the original line instead of reconstructing it from parsed columns. This supports later investigations and transformation changes.

Ingestion metadata. The file name and line number identify the source position; the ingestion timestamp records when that row entered the database.

Idempotent ingestion. Repeated processing of the same immutable source file does not create additional bronze rows.

Minimal semantic validation. Bronze does not decide which business statuses or amounts are acceptable.

There is an important limitation in our demonstration: a file name and line number work as a stable ingestion key only when source files are immutable.

If a producer overwrites a file in place, the same source position could contain different data. Our conflict-handling logic would retain the earlier payload rather than notice the mutation.

A production design should enforce immutable source objects or use stronger source identity, such as versioned object identifiers, content hashes, or message offsets.

Likewise, a production bronze layer might store original object bytes externally and maintain pointers inside the database instead of copying everything into a relational table.

The contract remains the same: preserve an independently recoverable source representation.

5. Silver is where correctness becomes enforceable

The silver stage performs several different tasks that are easy to confuse.

Parsing is not validation

A JSON object can be syntactically correct and still be invalid for the business.

For example, a status of “shipped” may be valid in another application, but our example accepts only “placed” and “cancelled”.

That is a domain-specific contract, not a JSON parsing rule.

Our staging view distinguishes these concerns by extracting values, converting them into expected types, and assigning rejection reasons.

DuckDB’s TRY_CAST behavior is useful here. Failed conversions return null rather than throwing an exception that aborts the whole transformation.

That allows malformed timestamps and amounts to become inspectable rejects.

However, using TRY_CAST without checking the result would be dangerous. A failed conversion could silently turn into a missing value that downstream aggregates ignore.

The pattern should always be:

1. Attempt the conversion.

2. Determine whether the result satisfies the contract.

3. Accept, reject, or explicitly default the record.

Quarantine is better than silent deletion

The example creates a separate table for invalid records with a rejection reason and original payload.

Four records are rejected:

• A negative amount.

• A nonnumeric amount.

• An unsupported status.

• Malformed JSON.

Those records are not available in the canonical silver event table, but they remain recoverable from bronze and visible in the rejection table.

In a larger pipeline, rejection reasons should also become metrics. A sudden increase in invalid timestamps may indicate a broken upstream deployment rather than an isolated bad record.

Deduplication requires identity

Our sample includes two identical events with the same event identifier.

Silver retains one canonical event and records the other in a duplicate audit table.

Why deduplicate by event identifier rather than order identifier?

Because a single order can legitimately have multiple events.

Order o101, for example, was placed and later cancelled. Those two records must remain separate in the event history.

Deduplicating by order identifier would lose an important business change.

There is another production consideration: the example uses deterministic first-arrival selection when identifiers are repeated.

That is appropriate for the exact duplicate replay in our sample. It is not sufficient for conflicting payloads that share the same event identifier.

A hardened implementation should distinguish exact replays from identity collisions, flag conflicting event contents, and apply a defined remediation policy.

6. Gold is where business semantics become explicit

The gold calculation contains a subtle but important design decision.

We do not simply group events by their event timestamps.

Instead, we derive the date when each order was first placed, identify its latest valid status, and aggregate the resulting order states by their creation dates.

This handles a common event-data problem.

On October 1, order o101 was placed for 25.50. On October 2, it was cancelled.

If we summed only placement events for October 1, we would report 77.50 for that day:

40.00 + 25.50 + 12.00 = 77.50

But our business metric is the currently retained value of those original orders. The cancelled order no longer contributes.

Therefore, the October 1 value becomes:

40.00 + 12.00 = 52.00

The daily summary retains three created orders but reports one cancellation and two active orders.

This is why gold requires more than a convenient SQL aggregation. It requires a clear definition of what a metric means.

Why we call it retained order value

Our table deliberately avoids naming the amount revenue.

An order being placed does not necessarily mean it has been paid, fulfilled, or recognized as revenue.

The example has no payment ledger, tax adjustments, refund settlements, or accounting recognition rules.

Calling the amount revenue would imply guarantees the data cannot provide.

Good metric naming is part of data quality.

A related consequence is that this gold table represents the latest known state, not an immutable historical snapshot.

If another cancellation for an October 1 order arrives later, a subsequent refresh may change the previously published October 1 total.

That can be desirable for an operational dashboard, but a financial report requiring reproducible as-of figures needs versioned snapshots or explicitly defined cutoff times.

7. Testing the boundaries, not just the SQL

The most important tests in this example validate architectural invariants rather than implementation details.

The fixed dataset establishes expected facts:

Tests like these catch bugs that can survive ordinary schema validation.

A column may have the correct numeric type while containing a duplicated business event.

A gold table may have the expected columns while accidentally calculating order counts from event counts.

A production test suite should cover several categories:

• Schema tests: Column presence, types, nullability, and valid enumerations.

• Identity tests: Unique event identifiers and legitimate entity relationships.

• Reconciliation tests: Accepted, rejected, and duplicate records account for input volumes.

• Semantic tests: Business measures match known scenarios, including cancellations and corrections.

• Freshness tests: Published data reflects source changes within the agreed latency.

• Replay tests: Retrying the same batch does not change results unexpectedly.

Quality thresholds also require judgment.

A critical finance dataset may need publication blocked when a source reconciliation fails.

A telemetry dataset may tolerate a small invalid-record rate while separately alerting on quality degradation.

Not every rejection needs to stop all consumers, but the behavior should be intentional.

8. Late-arriving data, CDC, and the limits of full rebuilds

Our implementation fully rebuilds silver and gold from bronze on every run.

For a small DuckDB project, this is an attractive choice. It is straightforward, reproducible, and avoids the complexity of incremental state management.

For hundreds of millions of records arriving continuously, repeatedly scanning the complete history could become unnecessarily expensive.

At that scale, the architecture needs additional mechanisms.

Incremental ingestion and processing

Production pipelines often record processing checkpoints so that previously processed source records do not need to be scanned repeatedly.

Incremental processing becomes harder when events update old business entities.

For example, a cancellation arriving today might affect an order created several months earlier.

Updating only today’s gold partition would miss the change.

A correct incremental pipeline must identify the affected business keys and refresh the relevant aggregates, even if their dates are outside the newest ingestion window.

Event time versus ingestion time

These are different concepts.

Event time describes when a business event occurred. Ingestion time describes when the data platform received it.

A delayed event can have an old event timestamp but a recent ingestion timestamp.

The silver contract should define whether business ordering relies on event time, source version, sequence number, or another authoritative ordering mechanism.

Our demonstration orders events by timestamp and then event identifier to produce deterministic results. For a real event stream, a lexicographic event identifier should not be mistaken for a guaranteed causal ordering sequence.

Change data capture and historical states

For database change streams, silver may need to interpret inserts, updates, deletes, and source transaction ordering rather than simply deduplicate events.

If consumers require historical entity states, one current row per entity is not enough.

A slowly changing dimension, versioned event history, or temporal model may be appropriate.

The key design choice is whether consumers need the latest known state, the state at a historical point, or both.

Medallion architecture does not answer that question automatically. Data modeling does.

9. When three layers earn their keep

Three distinct layers become valuable when they reduce duplicated work, protect recoverability, and establish useful ownership boundaries.

Consider an e-commerce platform with several upstream systems.

Orders arrive from a transactional database, payment events come from a payment service, and refunds are reported by another provider.

Data engineering must reconcile identifiers, currencies, timestamps, corrections, and delivery delays.

Meanwhile, finance, operations, analytics, and machine learning teams need different representations of the same information.

In that environment, the layers provide real benefits.

Bronze offers a stable replay point, silver handles reusable source integration and validation, and gold publishes metrics tailored to different stakeholders.

The added boundaries reduce the risk that every downstream team interprets raw data differently.

Three layers are especially defensible when:

• Multiple independent source systems feed the same business entities.

• Different consumer teams need a shared cleaned representation.

• Data must be replayed after corrections or rule changes.

• Source schemas evolve frequently.

• Governance and auditability justify separate access boundaries.

• Business-facing datasets require stable, documented contracts.

• Expensive transformations are reused by several consumers.

But these benefits should be measurable in reduced rework, improved reliability, easier recovery, or predictable consumer interfaces—not merely the number of tables created.

10. Cherry on the cake: three layers can be two too many

Here is the surprising part: adding layers can make a data product less trustworthy when it introduces unnecessary synchronization boundaries.

Imagine a small system with one already validated Parquet source and one internal report.

The source is updated every hour, and the report contains a handful of straightforward aggregations.

A team decides to introduce an elaborate medallion pipeline.

It copies the source into bronze, copies most of those records into silver, then materializes the final report in gold.

The three-table design appears architecturally mature.

But what has actually improved?

Perhaps nothing.

The source was already validated. No other consumer needs silver. The report’s logic is simple enough for a documented view.

Now consider a cancellation that arrives at 10:05.

Silver refreshes at 10:06, but gold was refreshed at 10:00 and will not refresh again until 11:00.

A direct query against the validated source could see the cancellation immediately after source publication. The materialized gold report remains stale.

The extra layer has created a new freshness problem.

This is not an argument against gold materialization generally. Materialization can dramatically improve performance, isolate workloads, and stabilize consumer interfaces.

It is an argument against adding materialization without a specific reason.

For this particular system, a single canonical table and a reporting view may deliver better results with fewer failure modes.

Even the bronze copy might be unnecessary if the original files are durably retained, immutable, and directly replayable.

The honest engineering decision is to keep the fewest boundaries that satisfy the contracts.

A practical decision framework

These are starting points rather than rigid rules.

A small dataset can justify three layers because of audit requirements. A large but simple dataset may need only one durable source and a carefully designed serving model.

The number of layers should follow the contracts, not precede them.

11. Production hardening: what the demo deliberately leaves out

Our DuckDB example demonstrates the logical boundaries, but several features need strengthening before using the same design for a critical production workload.

Atomic publication and recovery

DuckDB supports ACID transactions, including explicit begin, commit, and rollback behavior, as described in its transaction documentation.

Our example wraps ingestion and downstream rebuilding in one database transaction. If an assertion fails, the transaction rolls back rather than committing a partially updated pipeline.

However, this protects operations inside that DuckDB transaction only.

If production processing spans object storage, external warehouses, message queues, and independent jobs, a DuckDB transaction cannot make those systems globally atomic.

You may instead need staged outputs, atomic table replacement, versioned publication, or a coordinated workflow that exposes only completed datasets.

Explicit ownership and access controls

Not every consumer should be able to read every stage.

Raw source data can contain sensitive attributes or operational identifiers that are unnecessary in published analytics.

An enterprise design should define access controls appropriate to the platform and data classification.

A useful ownership model distinguishes who can modify ingestion logic, who approves canonical business definitions, and who owns published metrics.

Governance should protect real boundaries, not merely decorate schema names.

Observability and service expectations

A production job needs more than a success exit code.

Capture source arrival time, processing duration, validation failures, duplicate counts, output volumes, and the latest successfully published data version.

Freshness should be defined relative to the source and consumer requirements.

A daily finance report and a near-real-time operational dashboard have very different tolerances for delay.

Schema evolution and source changes

New source fields are not necessarily errors. Incompatible type changes and altered field meanings may be.

Bronze should preserve incoming information so that parser changes can be investigated and replayed.

Silver should make schema changes deliberately, with compatibility tests for downstream consumers.

Gold changes require particular care because a familiar column name with changed semantics can break trust even if its SQL type stays identical.

Efficient physical storage

Logical layer names do not determine optimal file layouts.

For larger datasets, physical design may include Parquet storage, appropriately sized files, partitioning or clustering, and selective materialization.

Avoid partitioning every table by several high-cardinality dimensions simply because the platform supports it. Excessive partition counts and tiny files can make reads and maintenance worse.

In DuckDB, Parquet can be queried directly, so not every intermediate transformation needs to be copied into a native database table.

Handling duplicate conflicts

Production-grade deduplication should distinguish:

• Exact event replays.

• Different events for the same entity.

• Conflicting payloads sharing an event identifier.

• Corrected records with explicit source versions.

• Late events that modify already published aggregates.

Our example handles the first two and audits repeated identifiers. It intentionally does not implement a complete conflict-resolution or change-data-capture framework.

Those rules belong in the silver contract before the pipeline becomes a critical source of truth.

12. How to decide whether to materialize a layer

An important architectural distinction is the difference between a logical boundary and a stored table.

You can maintain a raw ingestion table and express a silver transformation as a view, provided the underlying data and performance requirements permit that approach.

Similarly, a gold reporting view can expose stable business semantics without storing another full copy of the dataset.

Materialization is justified when it solves a concrete problem.

Notice that neither option is universally superior.

A view improves freshness relative to its underlying data but shifts compute work to query time.

A materialized table improves predictable query performance but requires explicit refresh and publication policies.

The correct decision depends on workload characteristics, service expectations, and failure modes.

13. Five architectural mistakes worth avoiding

Mistake 1: Treating bronze as a cleaned source table

If ingestion removes malformed rows before preserving them, replay and forensic analysis become difficult.

Keep a durable raw representation first. Make intentional cleaning decisions downstream.

Mistake 2: Building silver as a duplicate of bronze

A silver table that merely renames columns without enforcing a meaningful contract adds storage without much reliability.

If no independent validation or normalization is necessary, reconsider whether a separate physical table is justified.

Mistake 3: Calling every aggregation gold

A quick analytical summary is not automatically a governed business data product.

A production gold model should have an owner, defined grain, documented measures, and freshness expectations.

Mistake 4: Confusing successful execution with correct data

A transformation can run without errors while double-counting retries, omitting late events, or silently changing metric semantics.

Test business invariants and reconciliation, not only whether SQL executes.

Mistake 5: Turning layer boundaries into organizational bottlenecks

A team may need to wait for three different owners to approve a small correction if responsibilities are fragmented poorly.

Good governance establishes authority and accountability. Bad governance forces unnecessary handoffs.

An architecture that makes safe changes harder without reducing measurable risk has become bureaucracy.

14. A checklist for reviewing an existing medallion pipeline

Before approving a new table or additional processing hop, ask these questions.

• Source fidelity: Can we recover the original records after a transformation bug?

• Replayability: Can we rebuild downstream datasets without asking upstream systems to resend everything?

• Silver correctness: Are types, uniqueness, validation rules, and entity grain explicitly defined?

• Reject visibility: Can we investigate records that fail validation?

• Gold semantics: Do consumers understand precisely what every metric represents?

• Freshness: Is the publication delay necessary and observable?

• Incrementality: Can late corrections affect previously published periods correctly?

• Ownership: Is somebody accountable for each important contract?

• Cost: Does an additional stored copy save more operational or query cost than it creates?

• Simplicity: Could a view or one reusable transformation satisfy the same requirement?

A convincing answer to these questions is much stronger architectural evidence than compliance with a three-layer naming convention.

15. Final takeaways

Medallion architecture is useful because it separates distinct responsibilities: preserving source data, enforcing canonical data quality, and publishing consumer-ready information.

Its strongest benefit is not the bronze-silver-gold vocabulary. It is the ability to establish explicit, testable contracts and make transformations reproducible.

Our DuckDB implementation demonstrated those ideas without needing a distributed platform:

• Bronze preserved all 12 source records, including malformed input.

• Silver accepted seven unique valid events, quarantined four invalid records, and audited one duplicate replay.

• Gold calculated business metrics using the latest known order states.

• Assertions checked both data quality and expected business results.

• Rerunning the pipeline did not duplicate ingested source records.

We also saw why a working demonstration is only a foundation. Real systems need policies for source immutability, event ordering, late data, schema evolution, conflict resolution, publication consistency, and operational monitoring.

Most importantly, we established that an architecture is not better merely because it has more layers.

The goal is not to build three stages. The goal is to build the smallest reliable system that makes data trustworthy, recoverable, and useful.

Put it into practice

Run the DuckDB pipeline twice, inspect the rejected records, and then extend the sample with a late cancellation that changes an earlier day’s retained order value. Verify that the expected gold totals change correctly.

Next, take one real pipeline from your environment and document its grain, validation rules, replay guarantees, freshness contract, and consumer ownership.

Finally, challenge every materialized boundary: if removing this layer would not weaken reliability, correctness, governance, or performance, why are we maintaining it?

That is how medallion architecture becomes an engineering best practice instead of an expensive naming convention.