SAP Medallion Architecture Patterns: What Bronze, Silver and Gold Have to Do Differently

AFDev — Mon, 14 Sep 2026 22:45:35 GMT

Answer first

A medallion architecture over SAP data is the same three layers everyone draws, with three jobs that are specific to the source. Bronze has to preserve deletes. Silver has to rebuild a business document that lives in five tables. Gold has to translate an ERP dictionary into words a human reads. Skip any of the three and the layers are decoration.

If you have SAP tables landing in Unity Catalog and somebody has just asked you for a gold layer over them, this is written for you. It assumes you have built a medallion before and that this is your first one over an ERP, which is exactly the combination where the usual advice stops working.

I have built this pipeline over the same SAP SD tables on Databricks, on Snowflake, on Flink and with dbt. What follows is the five patterns that survived every one of them, written here as Lakeflow Declarative Pipelines code with the modern dp API. They are not a framework and there is nothing to install: they are five places to put the SAP knowledge you are about to need.

That the same five held across four engines with four different primitive sets is the reason I think they are worth writing down. The parts that changed were the syntax. The parts that did not were the parts SAP forced.

Environment. SAP ECC and SAP S/4HANA, SD module, sales order tables landing into Unity Catalog as Delta tables. Databricks side: Lakeflow Declarative Pipelines, Python, from pyspark import pipelines as dp. Catalog and schema names below are illustrative.

Disclosure. I work at Onibex, an SAP and Confluent partner that builds SAP-to-lakehouse pipelines, so the examples reflect one implementation I have worked on. This post is about the engineering patterns, not a product, and it recommends no vendor. Code is illustrative and sanitized.

Why does SAP break the tutorial version of the medallion?

Because every medallion tutorial starts from a source that already behaves. Clean column names, dates that are dates, one row per thing. SAP is a transactional ERP that spent decades optimizing for fast writes, and it owes your lakehouse nothing.

The first time I opened an SAP table in Databricks I thought I was looking at a corrupted dump. The columns were VBELN, KWMENG, VDATU. Dates arrived as text in character fields. Amounts arrived as FLOAT. One row had a delivery date of 0000-00-00, which is not a date, it is SAP saying "I do not know yet".

And the shape is wrong too. A single sales order is not a row, it is a join: VBAK the header, VBAP the line items, VBPA the partners, VBFA the document flow, VBKD the business data. SAP's own descriptions for those five are Sales Document Header Data, Item Data, Partner, Document Flow and Business Data.

So the medallion still applies. What changes is what each layer is responsible for.

What does bronze actually have to guarantee?

One thing, and it is not cleanliness. Bronze has to be source faithful and it has to carry deletes, because in an ERP a deleted sales order is information, not an absence.

That means the Change Data Feed has to be on before anything downstream can work:

TBLPROPERTIES (
  'delta.enableChangeDataFeed' = 'true',
  'delta.enableDeletionVectors' = 'true',
  'delta.enableRowTracking'     = 'true'
)

enableChangeDataFeed is the load bearing one. It makes Delta Lake record every insert, update and above all every delete into a feed the silver can read. Whatever lands your bronze, batch extract or streaming connector, check this property first. If it is off, everything below is a pattern you cannot implement, and you will not find out until the first deletion in production.

One thing bronze should not do is enforce a strict schema on an ERP source. Schema enforcement belongs further up, once you have decided what each field is for, and the last pattern in this post explains why.

What does the silver have to do that ordinary CDC does not?

Keep a current-state snapshot per table, keyed on SAP's real business keys, with deletes applied. An ordinary pipeline would do an upsert and stop there. This one has to do an upsert that can also remove a row, which is the part a plain merge into a target table quietly skips.

The pattern is one temporary view over the change feed, one streaming table, one auto CDC flow. Here it is for VBAK, the order header:

from pyspark import pipelines as dp
from pyspark.sql import functions as F

@dp.temporary_view(name="vbak_cdf")
def vbak_cdf():
    return (
        spark.readStream.format("delta")
        .option("readChangeFeed", "true")
        .option("startingVersion", "0")
        .table("sap.bronze.vbak")
        .select(
            F.col("mandt").cast("string").alias("mandt"),
            F.col("vbeln").cast("string").alias("vbeln"),
            F.col("kunnr").cast("string").alias("kunnr"),
            F.col("vdatu").cast("string").alias("vdatu"),
            F.col("gbstk").cast("string").alias("gbstk"),
            # the rest of the columns, all cast to string, see the last pattern
            F.col("event_ts").cast("timestamp").alias("event_ts"),
            F.col("_change_type"),
            F.col("_commit_version"),
            F.col("_commit_timestamp"),
        )
    )

dp.create_streaming_table(
    name="vbak_cur",
    comment="Type 1 snapshot of VBAK, SAP sales order header",
)

dp.create_auto_cdc_flow(
    target="vbak_cur",
    source="vbak_cdf",                             # a name, never a DataFrame
    keys=["mandt", "vbeln"],                       # SAP's real primary key, client included
    sequence_by="_commit_timestamp",
    apply_as_deletes="_change_type = 'delete'",    # the delete propagates
    except_column_list=["_change_type", "_commit_version", "_commit_timestamp"],
    ignore_null_updates=False,
)

Three things in that block are worth stealing. mandt belongs in the key, because the client is part of SAP's identity and leaving it out works right up until somebody opens a second client. The source of an auto CDC flow is a string identifier, so the change feed is wrapped in a temporary view rather than passed as a DataFrame. And apply_as_deletes is the whole reason bronze needed the change feed: without it, an order deleted in SAP lives forever in your dashboards.

The other four tables follow the same shape with their own keys. On VBPA I also filter parvw = 'WE' to keep only the ship-to partner, because that table packs every partner role into the same rows.

If you are porting older code: dp.create_auto_cdc_flow is what dlt.apply_changes became, it is called at top level, and it does not return a value.

How do you rebuild a document that lives in five tables?

Join the snapshots into one consolidated row, and decide two things most examples skip: what the columns are called, and what "fresh" means for a row assembled from five sources.

@dp.materialized_view(
    name="silver_sd_sales_order",
    comment="Consolidated SAP SD sales order: VBAK x VBAP + VBPA(WE) + VBFA + VBKD",
)
def silver_sd_sales_order():
    vbak = spark.read.table("vbak_cur").alias("vbak")
    vbap = spark.read.table("vbap_cur").alias("vbap")
    vbpa = spark.read.table("vbpa_we_cur").alias("vbpa")
    vbfa = spark.read.table("vbfa_cur").alias("vbfa")
    vbkd = spark.read.table("vbkd_cur").alias("vbkd")

    return (
        vbak
        .join(vbap, ["mandt", "vbeln"], "inner")   # no header without items
        .join(vbpa, ["mandt", "vbeln", "posnr"], "left")
        .join(vbfa, ["mandt", "vbeln", "posnr"], "left")
        .join(vbkd, ["mandt", "vbeln", "posnr"], "left")
        .select(
            F.col("vbak.vbeln").alias("vbeln_vbak"),   # <col>_<table>, on purpose
            F.col("vbap.posnr").alias("posnr_vbap"),
            F.col("vbap.matnr").alias("matnr_vbap"),
            F.col("vbak.kunnr").alias("kunnr_vbak"),
            # the freshness of the row is the freshest of its sources
            F.greatest(
                F.col("vbak.event_ts"),
                F.coalesce(F.col("vbap.event_ts"), F.col("vbak.event_ts")),
                F.coalesce(F.col("vbpa.event_ts"), F.col("vbak.event_ts")),
                F.coalesce(F.col("vbfa.event_ts"), F.col("vbak.event_ts")),
                F.coalesce(F.col("vbkd.event_ts"), F.col("vbak.event_ts")),
            ).alias("event_ts"),
        )
    )

Inner join on items, left join on everything else, so an order with no partner or no flow does not vanish. The <col>_<table> convention is verbose and it pays for itself the first time two columns called netwr disagree and you need to know which one came from the header. The silver stays faithful to the source, the makeup happens in gold.

The greatest over coalesced timestamps answers a question a single-table pipeline never has to ask. If a row is five rows, its freshness is the most recent of them.

What is the gold layer actually adding?

Meaning, not data. Three operations, and two of them are SAP specific.

@dp.materialized_view(name="gold_sd_open_order_tracker")
def gold_sd_open_order_tracker():
    sso = spark.read.table("sap.silver.silver_sd_sales_order").alias("sso")

    # SAP text tables are per language. Without the filter you multiply rows.
    makt = (spark.read.table("sap.bronze.makt")
            .where(F.col("spras") == "E")
            .select(F.col("matnr").alias("matnr_makt"),
                    F.col("maktg").alias("material_text")).alias("makt"))

    # "0000-00-00" is not a date, it is "not yet". It becomes NULL here or it breaks later.
    delivery_date = F.when(
        F.col("sso.vdatu_vbak").isNull() | (F.col("sso.vdatu_vbak") == "0000-00-00"),
        F.lit(None).cast("timestamp"),
    ).otherwise(F.to_timestamp(F.col("sso.vdatu_vbak"), "yyyy-MM-dd"))

    return (
        sso.join(makt, F.col("makt.matnr_makt") == F.col("sso.matnr_vbap"), "left")
        .select(
            F.col("sso.vbeln_vbak").alias("sales_order"),
            F.col("sso.posnr_vbap").alias("item"),
            F.col("makt.material_text").alias("material"),
            delivery_date.alias("delivery_date_ts"),
            F.when(F.col("sso.gbstk_vbak") == "C", F.lit("CLOSE"))
             .otherwise(F.lit("OPEN")).alias("order_status"),
        )
    )

Look at the contrast between input and output. In: vbeln, matnr, gbstk, 0000-00-00. Out: sales_order, material, order_status, a real timestamp or a NULL. Same information, and now a dashboard or a person can read it without an SAP dictionary open in the next tab.

The language filter is the one that silently ruins numbers. Text tables carry a spras column, so an unfiltered join multiplies every row by the number of configured languages, and your line count looks fine until somebody sums an amount.

Who owns a shared silver table?

Exactly one pipeline, and this is the decision I see skipped most often. A pipeline owns the state of the tables it declares: the checkpoints, the versioning, the lifecycle of the Delta table. Two pipelines declaring the same silver_sd_sales_order is a design you do not want, because each one believes it manages that table.

For a single domain, one pipeline is the simpler answer and the current default. The split becomes worth it when N business domains need the same consolidated sales order. Then the shape is one shared silver pipeline that owns the snapshots and the consolidation, plus one gold pipeline per domain that reads the silver as an external table and owns only its own outputs.

You build the truth once, each domain iterates without breaking what is shared, and the CDC logic exists in one place.

Which primitive belongs in which layer?

Layer Primitive Why this one

Bronze	Delta table with Change Data Feed on	The layer has one job, preserve what happened, deletes included
Silver, per table	dp.create_streaming_table plus dp.create_auto_cdc_flow	Incremental, stateful, delete aware, keyed on SAP business keys
Silver, consolidated	@DP.materialized_view	It is a join over snapshots, recomputed, not a stream
Gold	@DP.materialized_view	Enrichment and renaming, read by dashboards, recomputed on refresh
Across pipelines	spark.read.table on a fully qualified name	Reading something another pipeline owns

What do these patterns not solve?

They do not make the data correct. They make it traceable and delete aware, which is a different and more honest claim. If parvw was configured differently in your system, a correct pipeline gives you a confidently wrong ship-to.

They do not remove SAP domain knowledge. Knowing that WE is ship-to, that spras has to be filtered and that 0000-00-00 means "not yet" is not something a framework supplies. That knowledge is the actual work, and every pattern above is just a place to put it.

And they do not cover schema drift by themselves. Normalizing everything to string in silver and casting in gold is my answer to that, and it is a trade: you get stability when SAP changes a type or a length, you pay by deferring every cast and all real schema enforcement to the layer above. Data quality expectations, @DP.expect_or_drop and its siblings, are where I put the checks that used to live in strict types, because a dropped row with a metric attached is easier to explain on a Monday than a pipeline that stopped.

FAQ

Do I need one pipeline or several for an SAP medallion? Start with one. Split into a shared silver pipeline plus one gold pipeline per domain when a second business domain needs the same consolidated document, because a table should have a single owning pipeline and duplicating the CDC logic per domain is worse than the extra pipeline.

Why cast everything to string in the silver? Schema stability. SAP occasionally changes a type or a length, and a silver full of strict types turns that into a broken pipeline. Casting in gold means you decide the type where you already know what the field is for.

How do I handle deletes from SAP? Enable delta.enableChangeDataFeed on the bronze, read it with readChangeFeed, and pass apply_as_deletes="_change_type = 'delete'" to dp.create_auto_cdc_flow. Without the change feed there is nothing to apply.

What do I do with 0000-00-00 dates? Intercept them before parsing and turn them into NULL. SAP uses that value for "not known yet" in character date fields, and parsing it directly either fails or produces a date nobody meant.

Why is mandt in the primary key? Because the client is part of identity in SAP. A pipeline keyed only on the document number works until a second client exists, and then it silently merges two different orders.

Is this specific to Lakeflow Declarative Pipelines? The primitives are. The five patterns are not: I have built the same shape on Snowflake, on Flink and with dbt, and bronze preserving deletes, silver rebuilding the document and gold carrying the vocabulary stayed the same on all of them. What changed each time was the syntax for expressing them.

Notes on the API: this post uses the modern dp API for Lakeflow Declarative Pipelines, which is what Delta Live Tables was renamed to. If you are reading older material, import dlt maps to from pyspark import pipelines as dp, @Dlt.view to @DP.temporary_view, dlt.apply_changes to dp.create_auto_cdc_flow, and dlt.read to spark.read.table. The legacy names still parse.

SAP, SAP ECC and SAP S/4HANA are trademarks or registered trademarks of SAP SE in Germany and other countries. Apache and Apache Spark are trademarks of The Apache Software Foundation. Databricks, Delta Lake, Lakeflow and Unity Catalog are trademarks of Databricks, Inc.

topic SAP Medallion Architecture Patterns: What Bronze, Silver and Gold Have to Do Differently in Community Articles