An opinionated guide to the medallion architecture in Microsoft Fabric

Your silver layer is probably doing your gold layer’s job. That’s why nobody trusts the numbers and every change request takes three weeks. It’s probably not your fault either, because the documentation points you there.

I’ve built and inherited a lot of these platforms, and rebuilt quite a few of them, almost always for the same reason. I’m a consultant, so I say “it depends” in every other sentence. In this article I’m going to try not to. I’ll tell you exactly what I put in each layer of the medallion architecture, and why. You’ll probably disagree with me on at least one rule, and I’d rather have you disagree than nod along and forget about it.

What the documentation says about silver and gold

Nobody agrees on what goes in bronze, silver and gold, and that isn’t your fault either. A lot of sources write about it, and a lot of them contradict themselves. I think silver is where things really go wrong, so I’ll start there.

Microsoft Learn says silver is where you “fix errors, standardize formats, and remove duplicates.” Databricks says it’s “where you perform data cleansing, deduplication, and normalization.” You’re probably nodding along, because that’s exactly what you do in silver. Microsoft’s worked examples then spell out what it means in practice: standardise date formats across all sources, convert all currency to USD, remove test transactions, and match customer records across systems.

On gold, Microsoft says: “Organize for reports and dashboards.” Databricks says gold “consists of aggregated data tailored for analytics and reporting.”

Aggregated. I think that word is where it starts to go wrong, and everything that has gone wrong with your silver layer follows from it.

Two of those four are business rules

Currency is the clearest case. Say you have sales in euros and report in US dollars. (Microsoft only mentions dollars, but it could just as well be pounds, or francs.) Which rate do you take? The daily rate, the monthly average, the forecasting rate? I don’t know, and whatever the answer is today, it will probably change. Do not put it in silver.

Define gold as an aggregate layer for reporting and your business logic has nowhere to live. Aggregates are an output, not a place to put logic. The way the vendors write their documentation leaves a vacuum, and silver fills it, even though silver is the one layer I think should never hold any logic. “Cleansed” should mean the same date formats and the same data types everywhere, and nothing more.

Business rules change, history doesn’t

What I do with all of my customers is make gold a dimensional model at atomic grain. It can contain aggregates, but it isn’t only aggregates, and it’s the only layer where business decisions go. Once gold is a real modelling layer, silver doesn’t have to be one.

Business and technical are two different languages, and they belong in two different parts of the medallion, because business rules change and history shouldn’t. Put your business rules in a layer that also stores history and you get conflicts, and every change to a rule gets hard.

Say finance redefines what an “active customer” is. If the rule lives in gold, you change one notebook and rerun it. You’re probably done within 20 minutes by hand, or 2 minutes with Claude Code if we’re honest, and not one byte is reloaded from the source.

If the rule lives in silver, you have two options. You rewrite history, so the past now says something it never said and your audit trail is gone. Or you keep both rules forever. We’ve probably all seen that in practice, and a few years and many changes later nobody knows which version of the column is in use or what it means.

And you can’t tell finance to stop changing its definitions.

One rule: whose language is this column in?

If a column is in the source’s language, it can live in bronze and silver. If it’s in the business’s language, it goes in gold. The moment you start translating between the two, you’re in gold.

Casting CREDIT_LIMIT to a decimal is fine in bronze. Maybe a REST API sent JSON without a schema, so it arrived as text, but the source always meant a number. Deduplicating on the source’s business key and tracking when a row changed are silver’s job. That’s the key the source system uses, not the one the business thinks of. Renaming NO_ to CustomerNumber, filtering out test customers and converting currency are translations or rules that will change, so they go in gold. So does matching customers across CRM and ERP: that’s a conformed dimension, and Kimball wrote a lot of pages about it.

Three layers, three questions

Each layer is allowed to answer one question.

• Bronze: what did the source actually send us, and when?
• Silver: what does the data look like now, or on any date in the past? It’s a time travel mechanism, though not Delta time travel (more on that below).
• Gold: what does the business want to know?

When things change, they change in gold. History doesn’t change in silver, and what the source sent doesn’t change in bronze. If you take one thing from this article, take this: gold speaks the business’s language, and bronze and silver speak the source’s. Don’t mix the two, because a layer that tries to answer two of these questions answers neither of them well.

Bronze: six rules

Most of these will make you think “yes Bas, we know”. That’s fine: they set the stage.

1. The raw file lands in Files/ first. Always. That includes data from a database. A copy activity could go straight from a database table to a lakehouse table, but we write Parquet files first. If ingest breaks, I already have the payload and don’t have to go back to the source to reprocess. Think of it as insurance.
2. Bronze Delta tables mirror source objects one to one. Same grain, same names, and no unions, splits or renames. You get an ugly bronze layer, and I’m perfectly fine with that.
3. Typing happens here, not in silver. At least wherever we can. A lot of data types can be parsed automatically: source-native types from a database, or from CSV and JSON files that carry a schema. Casting isn’t a business decision. A sales amount is a number, and your finance department isn’t going to change that. The earlier you type, the easier the data is to manage downstream, so do it as early as you reliably can.
4. Append-only. Nothing is ever updated or deleted. Well, we delete raw files, but that’s the next rule, and it doesn’t throw away any information. Bronze is a log of arrivals, not a picture of the source.
5. Raw files are kept 30 days, then aged out. Yours might be 90 or 20, and I don’t really care, as long as it’s a number you stick to. After that the data lives in the bronze tables, and you can reprocess the whole warehouse from there.
6. Nobody builds anything on bronze. Not a report, not a semantic model, not even a “quick look”. Once people can query it, they report on it and start relying on it, and that’s not bronze’s job.

So bronze does two small jobs. The raw files are the arrival record: byte for byte what the source sent, so if processing into Delta fails you can replay the load without going back to the source. The Delta table is the arrival log: typed, append-only, one row per arrival. It tells you when you last pulled the data, how many rows came in, and what they looked like at load time. Bronze is also where we compare each arrival against silver to decide what happens downstream: insert, update, ignore or delete.

Silver: a versioned mirror, nothing more

This is the part you’re going to argue with me about, and that’s fine. We can all learn from it.

Silver is a faithful, versioned, schema-managed mirror of the source, and nothing more. Versioned means time travel that we build ourselves (again, not Delta time travel). Schema-managed means schema evolution, and that part is really important.

• One table per source object, with column names exactly as the source spells them. That can look ugly, but translation has to happen somewhere, and the work is the same wherever you put it.
• Deduplicated on the source’s business key. I don’t pick the winner; the source’s own ordering does. A customer number is unique in the CRM, yet that customer appears many times in silver, because we keep its versions.
• Schema evolution is automated. When a new column appears in the source, silver picks it up and keeps running. A column that disappears stops being loaded, but its history stays.
• Full history in columns: every version of every row.

These system columns are the only schema change silver makes to the source:

• Sys_ValidFrom and Sys_ValidTo say when a version became true and when it stopped being true. The first version gets the placeholder 1900-01-01, and the open row gets 9999-12-31, never null.
• Sys_IsActive is a convenience boolean for the current version. Sys_ValidTo already tells you which row is current, but a boolean is easier to filter on.
• Sys_IsDeleted means the source stopped sending this row.
• Sys_Timestamp is the load id (you might call it an ETL id or a run id). It’s in yyyymmddhhmmss format because a load starts at a certain time. It traces back into logging and telemetry, so we can see what was loaded when, in which package, and with which warnings or errors.

Why not Delta time travel?

Delta time travel is a file-level retention feature, and it’s really useful: you can point at earlier versions of a table. We use it to mimic SQL Server-style transaction rollbacks. If an update or insert goes wrong, we roll back to the previous version of the table. But I won’t trust it to hold a decade of history. You can’t join to it, you can’t govern it, and as soon as somebody runs an optimize or a vacuum, your history is gone. So we keep history in columns, in Sys_ValidFrom and Sys_ValidTo.

What I refuse to put in silver

• No joins, across source systems or within one. No flattening header and line tables either.
• No calculated columns besides the system columns.
• No aggregations.
• No filtering that encodes a business decision.
• No renaming. NO_ stays NO_.
• No PII masking. The source shouldn’t send us anything we aren’t allowed to see. If it lands anyway, we kick it out in bronze, or if we do need it, we mask it in gold so people only see what they’re allowed to see. Fix PII as far upstream as possible and as far downstream as necessary.

So yes, my silver layer is ugly. It’s a mirror, and a mirror that flatters you is broken. Nobody enjoys querying it, and to be fair, we don’t let end users query it at all. Silver has exactly one consumer, and that’s gold.

A customer from bronze to silver

Here’s one customer in bronze.custtable. The loaded column is metadata the platform generates; it doesn’t come from the source.

Say we load every day. I’ve left out the rest of January, where nothing changed, and the February load didn’t change anything either. Bronze kept it anyway, because it’s a log of arrivals and storage is cheap. In practice you’ll have hundreds of rows for one customer.

The same customer in silver.custtable:

Three versions, for three changes: the customer appeared, the credit limit went to 25,000, then to 40,000. The first row starts at 1900-01-01 because we don’t know when the customer entered the source system. (The slide in the video shows the arrival date there. That’s a typo.)

The four loads here, or the 150 to 200 you’d have in reality, became three versions. It still says NO_ and CREDIT_LIMIT in capitals, and I didn’t make it prettier, on purpose: making it prettier changes the language, and that’s a business decision. Silver speaks the source’s language.

Time travel is a where clause

What was Acme’s credit limit on 15 May?

select  no_, credit_limit
from    silver.custtable
where   no_ = 'C10042'
  and   sys_validfrom <= '2026-05-15'
  and   '2026-05-15'  <  sys_validto

The answer is 25000.00. There’s no vacuum horizon and no version numbers, and gold can run this today or years from now. We materialised that history ourselves, so nobody optimising a Delta table can throw it away. (We still have to worry about people optimising our Delta tables, just not about losing history.) Gold notebooks use exactly this pattern, which is what makes reprocessing gold easy, slowly changing dimensions and snapshots included.

Answering the objections

I’ve learned that if you want to make a strong argument, you should make the counterargument yourself. Here are three objections you might have.

“Gold becomes a monolith”

All the complexity ends up in one layer, yes. But spreading complexity over two layers only hides it. If you have twelve transformations and do half in silver and half in gold, you still have twelve. The only question is whether you can find them. I’d rather have all the logic in one layer, where I can find it and predict what’s going on.

Gold is forty small notebooks, or twenty, one per dimension or fact. Each has the same four-cell skeleton and reruns on its own, which is the opposite of how a monolith fails. And “which layer is it in?” is an expensive question, because data engineers are expensive. I’m not sure about other countries, but they certainly are here in the Netherlands. This architecture doesn’t have that question. There’s one place to look, and it’s always the same place.

It only works with discipline: the same notebook structure, the same naming, the same code review. Without that, gold will rot, and it will rot visibly.

“Silver is a wasted layer. Just query bronze”

Bronze is a log of loads; silver is a picture of the entities in a source system.

If you query bronze, every consumer writes the same window function over load timestamps, and each one gets it subtly wrong in its own way.

Then there are deletes. A full-load source expresses a delete by absence: the same thousand records every day, then one goes missing. Detecting that absence in an append-only log is real work. Silver does it once, between bronze and silver processing, and exposes Sys_IsDeleted.

And point in time becomes a where clause for everyone downstream. Without silver, every notebook, Power BI semantic model and Fabric data agent has to reinvent how to get at history.

To be fair, there are platforms with one source, all full loads, ten minutes of processing, no slowly changing dimensions or snapshots, and nobody ever asking what last quarter looked like. There silver is really thin and you could skip it. I haven’t come across one of those myself, but they exist, and for those a semantic model straight on the source API might be enough. Once a platform gets a little more complicated, silver is where the history keeping happens.

“Microsoft and Databricks say otherwise”

We agree more than you’d think. Microsoft, Databricks and I agree on bronze. On silver we half agree: deduplication on the source’s key and normalising schema changes and data types, yes. Currency conversion and removing test transactions, no. Those are business rules.

The disagreement that matters is gold. Define gold as aggregates and business logic has nowhere to live but silver, where it gets mixed up with history, and you rewrite history every time a definition changes. Define gold as your dimensional model and silver is free to stay dumb: follow the source, evolve the schema, type what bronze didn’t, keep history. That comes with a big plus: you can generate the whole silver layer from metadata and configuration files instead of programming it.

The documentation describes a lakehouse without a modelling layer. Those vendor definitions are naming conventions, not specifications for your platform. If you don’t model downstream, your business rules have to go somewhere in silver. If you do, put them anywhere but your historical layer.

Gold: Kimball at atomic grain

Gold is the only layer where a business term is allowed to appear. Some of these rules are more gold housekeeping than medallion, but this is how I build it:

1. Kimball, at atomic grain. Not aggregates. Aggregates can exist, but they’re not why gold exists.
2. One notebook per gold table. If you come from SQL Server, like me, read it as one stored procedure per table. Lakehouse, warehouse or database, I don’t really care; today we mostly use a lakehouse for gold, so it’s notebooks. Every one has the same structure: parameters, schema, mapping, upsert. It’s the same skeleton forty times (or a hundred, or three), so I can template it and teach my AI agent to write a proper gold notebook.
3. Surrogate keys are hashes, not lookups: sha2(concat_ws('~', <natural keys>), 256). No key-management table, no sequence, no lookup join. The key is deterministic, so I can compute it before the dimension row exists, and facts build in parallel with their dimensions.
4. Slowly changing dimensions only when the business asks. Type 1, overwrite in place, is the default: if a customer’s credit limit changes, we overwrite it. Type 2 is per field and on request, like the account manager of a customer at a point in time. With all history in silver, adding it later is easy.
5. No null foreign keys, ever. Unknown rows instead. Kimball advocates the same. A fact that can’t find its dimension points at a real row that says so. Nulls in a star schema are how you get two different totals for the same measure.
6. Gold is derivable. If anything in gold can’t be reconstructed from silver, your history is in the wrong layer. Gold holds business logic and silver holds history. After ten years you should be able to make account manager a type 2 column on your customer dimension, all ten years included, from silver.
7. All the ugliness from silver lands here, in one visible place: the renames, the case decodes, the coalesce(..., 'Unknown'). Complexity you can see is not a monolith.

Hashed keys and the birthday problem

Here’s an excerpt from the mapping cell of a fact table:

-- nb_factSales, cell 3 (excerpt)
select  sha2(concat_ws('~', l.document_no_, l.line_no_), 256)                  as SalesLineKey
     ,  sha2(concat_ws('~', coalesce(h.sell_to_customer_no_, 'Unknown')), 256) as CustomerKey
     ,  sha2(concat_ws('~', coalesce(l.no_, 'Unknown')), 256)                  as ProductKey
     ,  l.quantity                                                            as Quantity
     ,  l.amount_lcy                                                          as Amount
from    silver.salesline l
join    silver.salesheader h on h.no_ = l.document_no_
-- plus the same @loaddatetime window on both tables

A 256-bit hash isn’t the most efficient key. We’re looking at xxhash64, which gives you a bigint, but the collision risk goes up quite a bit. If you like maths, look up the birthday problem: the chance that two members of a population share a number grows fast as the population grows. We don’t want two objects sharing a key, so for now we’re sticking with 256 bits.

Atomic grain, and what sits on top

Level one is factSales (I’d rather call it fact sales invoice lines, but that doesn’t fit on a slide): one row per order line, at the atomic grain of the source. When a query pattern needs aggregates, we build a level two fact like factSalesMonthly that reads from level one. The dependency sits in metadata, so level one always runs first.

Level two never reads silver, because business logic should live in one place. (And that place is never silver, I know I keep repeating it.) If net sales is defined in factSales, the monthly fact sums that column and doesn’t redefine it. Let level two read silver and you have two definitions of one measure, and the next time it changes I’m lost again. I’ve built the same mess. So “gold is not aggregates” means aggregates are never the foundation.

Derivable, not disposable

Reloading a type 1 table is the happy flow: truncate, rerun the notebook, get the same output byte for byte. That’s minutes of work, maybe an hour for a big table, and most of our tables are type 1.

Type 2 is a hassle, I’ll be honest. You replay every silver load timestamp in order, one run per historical load, with a for-each loop in a notebook. It’s slower, but nothing is lost, because the history lives in silver. That will save your ass when something got botched beyond repair, or when you decide you want a slowly changing dimension or snapshot starting five years ago.

The question to ask is whether there’s anything in gold you couldn’t reconstruct from silver. If there is, your history is in the wrong layer.

Point in time is a parameter

Every gold query filters on the valid from and valid to columns, and the moment it looks at is a parameter, @loaddatetime, never a hard-coded “today”. Hard-code it and reprocessing history becomes a code change. Make it a parameter and you only feed the notebook a new value.

Write the filter as a half-open interval. between Sys_ValidFrom and Sys_ValidTo is inclusive at both ends, so a load date of 2026-03-01 matches version one and version two, and the row is silently counted twice. >= Sys_ValidFrom and < Sys_ValidTo matches version two only. That needs each version’s valid to to equal the next one’s valid from, with 9999-12-31 on the open row, which is exactly what silver gives you.

When the same cleaning is needed twice

“But Bas, several gold tables need the same pre-processing.” True.

If only one gold table needs it, it goes in a cell in that notebook, a common table expression in the mapping query, or a temporary view. It lives next to what it serves. If several tables need it, we create a small pre_ table, prepared once in gold and joined by whoever needs it.

Yes, that’s a staging step with a prefix. It’s the same complexity you’d have if you put it in silver as Microsoft suggests, only now I can see it, in the layer it actually changes, instead of having it hidden in my historical layer. The one guardrail: a pre_ table is never read by a report or a semantic model. It only exists to be joined by other gold notebooks.

The four-cell gold notebook

Every gold notebook has the same four cells:

1. Parameters: @loaddatetime, a Python list of the type 2 columns, a list of the business key columns, and whatever else the load needs.
2. Schema: we declare the target table’s column names, data types and nullability ourselves, instead of letting the engine infer them from the query.
3. Mapping: silver in, business names out. All the logic lives here: renames, filters, aggregations, line-level calculations, case when decoding, common table expressions, temporary views. The notebook is PySpark, but I’m a SQL Server guy, so the mapping is Spark SQL. More on that in why I prefer Spark SQL in a Fabric lakehouse.
4. Upsert: a merge on the hashed business key. Write your upsert, insert and truncate-insert patterns once in a Python library, import it, and every notebook only needs a few lines. That’s the DRY principle at work.

The mapping cell of a customer dimension:

-- cell 3: mapping
select  sha2(concat_ws('~', c.no_), 256)   as CustomerKey
     ,  c.no_                              as CustomerNumber
     ,  coalesce(c.name, 'Unknown')        as CustomerName
     ,  case when c.blocked = 0 then 'Active'
             else 'Blocked' end            as CustomerStatus
     ,  coalesce(sp.name, 'Unknown')       as AccountManager
from    silver.custtable c
left join silver.salesperson sp
       on sp.code = c.salesperson_code
      and sp.sys_validfrom <= @loaddatetime and @loaddatetime < sp.sys_validto
where   c.sys_validfrom <= @loaddatetime and @loaddatetime < c.sys_validto

That’s a hashed key, NO_ renamed, a case expression turning blocked into a status, and a join to find the account manager. The only filter is on history, with the same half-open window on both tables.

Who owned it then, who owns it now

I like showing off Dutch names that are unpronounceable in English, so here are two:

scd_columns = ["AccountManager"]

AccountManager is in the list, so a change triggers a new type 2 row: Sanne until 1 March, Joris after that. AccountManagerActual isn’t in the list, so it’s overwritten. “All customers Joris manages now” uses the actual column. “Sales for customers under a given account manager at the time” uses the historical one. If the business asks both questions, it gets both columns. That’s a business decision, so it lives in gold, built from the history in silver.

Finance changes the definition of “active”

This is what the whole architecture is for. Finance sends a new rule: customers blocked only for shipping still count as active. The blocked column is an enumeration: 0 is not blocked, 1 is shipping, 2 is invoicing, 3 is everything.

In nb_dimCustomer, cell 3:

- ,  case when c.blocked = 0       then 'Active' else 'Blocked' end  as CustomerStatus
+ ,  case when c.blocked in (0, 1) then 'Active' else 'Blocked' end  as CustomerStatus

One line, and the number on the dashboard changes. I didn’t reload a byte from the source, I didn’t touch bronze or silver, and I didn’t rewrite a row of history. One notebook, one line, one rerun. If finance changes its mind back next quarter, it’s the same twenty minutes.

What you get

This is the part your management might care about. Semantic models behave: Direct Lake over a real star, with DAX that isn’t compensating for a bad model. AI agents can actually answer, because a star schema with business-named dimensions is the vocabulary an agent needs. Don’t ignore them: Fabric data agents are amazing, and customers are using them. When a number is wrong, there’s one notebook to open. And a new source plugs into the conformed dimensions you already have. Silver is generated, so a third customer source is no harder than the second.

The dashboard is no longer the only thing reading your gold layer. AI agents and more and more other consumers are going to ask it questions, so things will change faster and faster. That’s why it’s worth building gold properly in 2026.

Bronze records what arrived, silver tracks what changed, and gold explains it in the language your users care about. Keep those three jobs separate and the platform stays cheap to change. Everything you’re going to be asked to build next assumes you did.

The slides from this session are in my presentations repository on GitHub.