Bronze, Silver, Gold: A Practical Guide to Medallion Architecture

How does raw data from a system like Salesforce become a dashboard your team can actually trust? On this episode of the Dashboard Effect, Brick and Landon walk through medallion architecture and the three stages data moves through on its way to becoming useful.

They keep it simple: the first stage holds an exact copy of your source data, the middle stage cleans it up and combines data from different systems, and the final stage delivers a polished, analytics-ready view for tools like Power BI and Tableau. Landon also makes the case for keeping things lean rather than forcing every dataset through every step, and shares where speed and scale change the approach.

If you're building or refining how your data flows, this is a clear, approachable starting point.

About Blue Margin -

Blue Margin is a fractional data and analytics team for mid-market and PE-backed companies. Acting as an extension of your team, they build and manage your data platform, shape your data strategy, and deliver Power BI dashboards that give leaders a clear, real-time view of what's driving the business. The goal is simple: turn scattered data into decisions and build a culture of accountability and growth along the way.

Transcript

SPEAKER_00 0:05

Welcome to the Dashboard Effect Podcast. I'm Brick Thompson. And I'm Landon Oaks. Wendon, today I wanted to record a few short videos explaining some things that we've been talking about technically over the last couple of months, just to make sure people understand what we're talking about when we use some of these terms. And I wanted to start with medallion architecture in the context of a data lake house. So why don't you take a couple of minutes and explain what that is?

SPEAKER_01 0:31

Yeah, definitely. So medallion architecture is a framework of how you will actually move data through your solution, right? And so there's three main layers that that has. And so just off the top, like quickly, it's bronze, silver, and gold. And so your data starts with bronze, ends up in gold. Okay. And each layer kind of has its own distinct purpose for why it exists.

SPEAKER_00 0:57

Okay. So you're bringing data in from a transactional source, say an ERP system.

SPEAKER_01 1:02

Yeah.

SPEAKER_00 1:02

It comes into your data lake house and it needs to go through those three phases.

SPEAKER_01 1:07

Yeah, not always, but we'll need another. So essentially Yeah, you'll pull it from like let's say Salesforce in this example, right? I'm going to go grab my opportunity data. Um I will load that as a one-to-one replica in bronze. So if there's nothing else, it's exactly how it is in the source. You know, if the source is a little bit more. Source dirty, it's dirty. Okay. Source clean, it's clean, right? So it's just one-to-one. Nothing else there. Um so you keep that up to date because it's really useful to have. And then from there, you kind of either can go into silver or straight to gold. So you'll have some purists, right? That that think it has to go through every layer no matter what. We're a little bit more in the boat of why introduce unnecessary layers to your solution if you don't need it.

SPEAKER_00 1:52

Got it. But you start in bronze no matter what. No matter what. Yeah. Silver is a silver. Silver is okay, gotcha.

SPEAKER_01 1:58

Yeah, for us anyway.

SPEAKER_00 2:00

Okay. So what is silver then?

SPEAKER_01 2:01

Yeah, silver is going to be another layer of transformation. So, you know, you'll think of things like deduplication. If my source has a bunch of duplicates, I'll uh work on that there. Another common thing is if you have multiple sources. So you have like Salesforce and HubSpot, for instance, maybe you have two different wings of your company that are using it. So it's the same type of data, but different sources, right? So that's a good place to combine HubSpot and Salesforce into one uh table.

SPEAKER_00 2:28

Make them look like each other.

SPEAKER_01 2:30

Exactly. Yeah. So that's that's our main case. And part of the thing is like if you have one data source and you want an account dimension, just like here's all of my customers, right? From Salesforce. And your your logic is literally just select customer name, customer ID from account. Why make that go through a silver layer then to a gold layer? Like it's super simple. Okay. Just throw that directly into gold.

SPEAKER_00 2:55

Okay. Um then what's gold used for then?

SPEAKER_01 2:58

Yeah. Gold is gonna be the analytics ready data set, right? So it's gonna be into a nice schema, star schema, usually, um, with your dimensions, your fact tables, and that's really the area where you're gonna point Power BI at it. Um, or Excel or Tableau or whatever, yeah. So it's like the trusted area, right? So anything analytics-wise is usually pointed there. Um for business users, anyways.

SPEAKER_00 3:23

Okay. So as you're moving through these layers, are these all just schemas? Uh you might have a bronze schema, you might have a silver schema, gold schema that have views that are making those transforms, or are you doing any materialization of that data back to the lake house?

SPEAKER_01 3:40

There's a lot of different ways you can do it, right? So um you can, you know, for us we have schemas. We have bronze schema, silver schema, gold schema. Then we'll have views in the silver schema if we need to combine two tables or do some deduplication or some some really repeatable work. Um, and then those will be referenced with views again in the gold layer. Um, so your views are either gonna be pointed up silver, then back to bronze, or just straight from gold to bronze. Um the reason we do views is we need to just be really fast, right? So we need to constantly be able to just make a change and see how it looks on the report and keep moving. Gotcha. And uh views are very performant. Now there will be a time where we might materialize data if it's really big and really complicated. Okay. Which we've seen before, but not often.

SPEAKER_00 4:31

It's actually I just realized I introduced another term, so maybe you can explain materialize data.

SPEAKER_01 4:37

Yeah, really materialize is just essentially, you know, I have like let's say I'm combining three tables, right? Like an opportunity and opportunity line and some uh alternative table that goes to it. I'm gonna take those, I'm gonna write my query to uh get the data that I need, make sure uh that I need, and then I'm gonna save it somewhere. And then now my next layer is just it's a physical, right? It's been physically saved. So the challenge there is that that introduces a couple of things you gotta think about, right? So do I just rewrite it and like do the entire thing full every night, which depending on your data size, can be unrealistic.

SPEAKER_00 5:15

Yeah.

SPEAKER_01 5:15

If you don't, then you gotta worry about deletions, updates, inserts, make sure those are handled well, which that's where a lot of times things can break down.

SPEAKER_00 5:22

Okay, we can keep going down this rabbit hole. Oh, we don't. All right, I think we covered medallion architecture. I appreciate it. We'll be back. Thank you.