The Gold Layer: Your Best Data, Ready to Report

The gold layer is where cleaned, validated data lives in a medallion architecture, ready for reporting and analysis. In this episode, Brick and Landon break down what actually makes a gold layer good.

Landon covers what "good" means in practice: deduplicated data, validated numbers, and a structure business users can trust without checking behind the curtain. They discuss why star schema still holds up as the standard modeling approach, how to handle naming conventions that make sense to analysts instead of source systems, and what happens when six different systems all claim to have "the customer table."

They also get into the messier parts of building a gold layer: matching source system reports to catch hidden filters, deciding how to relate keys across systems without collapsing records that only look the same, and building exception reports to flag duplicates back to the business instead of quietly fixing them downstream.

If you've ever wondered what separates a gold layer that works from one that just exists, this episode covers the fundamentals.

Key Moments:

0:36 — What is the gold layer? 
1:00 — Combining data silos
2:01 — Modeling the gold layer
2:22 — A preview of the platinum layer
2:30 — Naming conventions
3:39 — D_ and F_ naming standards
4:08 — Validating the gold layer
4:36 — Hidden filters and reporting errors
5:13 — Relating fact tables
5:57 — Handling duplicates
6:52 — Exception reports

About Blue Margin

Blue Margin is a Microsoft Fabric and Power BI consultancy based in Fort Collins, Colorado. We help mid-market and private equity-backed companies build data platforms that hold up: clean architecture, trustworthy reporting, and dashboards teams actually use. The Dashboard Effect is where we talk through the technical decisions behind that work.

Learn more: https://bluemargin.com

Transcript

SPEAKER_01 0:06

Welcome to the Dashboard Effect Podcast. I'm Brick Thompson.

SPEAKER_00 0:09

And I'm Landon Oaks.

SPEAKER_01 0:10

Landon, we're going to continue on our path of doing a few short videos on some technical terms and what they mean. We talked recently about medallion architecture in the context of data lake houses. One of the uh layers in that medallion architecture is a gold layer. I want to just get a little more into that. What is the gold layer? And then how do you build a really good gold layer?

SPEAKER_00 0:36

Yeah, it's a good question. So in general, layman's terms, the gold layer is your your best data, right? Essentially.

SPEAKER_01 0:46

Cleaned up, ready to report on. Okay.

SPEAKER_00 0:48

Yeah, there's no duplication in there. You validated it, you're confident in the numbers, it's returning, right? So it's the area you can go to trust.

SPEAKER_01 0:54

Aaron Powell You've already combined all of your different silos of data and bubbled it all up to the gold layer.

SPEAKER_00 1:00

Yeah, and that's can be the one of the biggest ones, right? Is combining different data sets, et cetera. It's all done there. You don't have to think about it if you're pulling your data from that area. Um so part of the essentially what you're doing that for is to point any sort of analytics at it, which whether that be Power BI, Excel, um what have you, tons of different solutions out there to do that. But that that's really its main purpose is for that gold layer. Sometimes you will have some people doing some ad hoc data polls too to be able to go do what they need with it elsewhere, um, just with the understanding that this is good data and I can trust it.

SPEAKER_01 1:38

Okay. So some of the things you need to do to have a good gold layer, obviously good modeling. Maybe we can talk a minute about that. Uh first thing you said was it's deduplicated, it's validated. Um you've already done checks with uh SMEs in the business to know that the data coming out of that is good. So all right, so how do you model it? What's a what's good modeling in a gold layer?

SPEAKER_00 2:01

Aaron Powell Yeah, in a gold layer, I mean obviously this is gonna change with AI and how fast technology is moving. Yeah. But right now, it is still um typically star schema that you're gonna want, right? Um there can be some variations on that, but at the end of the day, star schema is gonna be the best bet.

SPEAKER_01 2:17

Yeah.

SPEAKER_00 2:17

Um how long that'll be the case, we'll find out.

SPEAKER_01 2:20

Yeah, and we'll we'll talk actually we'll do another episode where we talk about what we're calling platinum layer, which is sort of the gold layer made friendly for an AI, an LLM.

SPEAKER_00 2:30

Yeah, exactly. So in terms of that, you know, kind of the idea is like you want to just make it really clean, really um business friendly, right? So you just want like dimension customer, um, D customer, dim customer, however you want to call it. Um, even though Dim Customer might be made up of six different source systems, one with a nasty name like you know, J26Y in the source, right? You've you've dealt with all of that. Um you've flipped it.

SPEAKER_01 2:57

So you've cleaned it up, you made it so a business analyst will understand what's in that table. Yeah. If you had six different systems with customers in it, you made it, you modeled it so they all represent customers the same way, that type of thing. Trevor Burrus, Jr.

SPEAKER_00 3:09

Yeah, to the to the best of your ability. Yeah, right. You know, there's always gonna be holes when you're talking about a bunch of different systems with the same data. But yeah, that's the end of the day, you're you're getting it so that they don't need to worry about it, right? An analyst at the back, you know, before they had something like this, they'd need to go to all six systems, dump out Excel files, go do that mapping themselves with V lookups, et cetera, it becomes really heavy. And then most of the time it's not gonna update by itself, right? So you're gonna have to do it every single time you want a new analysis. So that's the biggest kind of benefit of the R.

SPEAKER_01 3:39

All right. So you've you've used human-friendly naming, sort of standard naming. Um in our case, I think we use D underscore for dimensions, F underscore for fact tables. Um these are all views, by the way, usually in the gold layer. We talked last last time we met about medallion architecture and the fact that you can materialize those views back into data to make it quicker. But in this case, views. Um what what other considerations do you have to make sure your gold layer is good?

SPEAKER_00 4:08

Yeah, I mean, those are the biggest ones, right? So if you can get like a source system report, et cetera, and I can match those numbers with queries on my gold layer, relatively simple, right? That's the that at the end of the day, that's the goal.

SPEAKER_01 4:22

So you validate it, you're getting correct numbers out of it.

SPEAKER_00 4:24

And you're gonna find all kinds of hidden filters that because a lot of times, you know, if your your source system has a report, you can't peek behind the curtain and see what's going on in there, right? Right. So you're gonna find a lot of filters.

SPEAKER_01 4:36

How's it getting this number?

SPEAKER_00 4:37

Yeah. And sometimes in cases we found that it's wrong, right? It's the source system reporting is wrong. Well, Sean will be like, why isn't this being included in your report? And they'll be like, it's not.

SPEAKER_01 4:46

Yeah. Yeah, actually.

SPEAKER_00 4:48

So uh it's worthwhile exercise. Um, and that's usually where the biggest insights come. And then now you just have it. It's right there at your fingertips.

SPEAKER_01 4:58

And then in your gold layer, so you've got these various star schemas for different fact areas. Um do you do you have any special uh requirements around relating those fact areas? Are you always doing it through through some conform dimension? Or how do you think about that?

SPEAKER_00 5:13

Aaron Ross Powell Yeah, yeah, it's a good question. So there's a super simple way, which is one system, I can just use the ID from the source system. Technically I could make my own IDs, but that increases, you know, that adds more complexity that you're gonna have to now have to manage, right? So we we always try to do less overhead. Um so that's a simple one. Second one is you know, if you have multiple systems that have the same area, like in that example with six customers, um, you know, your key from one system, which might be like, you know, 22, just to keep it simple. Um in Salesforce, for instance, might be also have another 22 in HubSpot, but they're completely different customers because these are internal keys, right? So you can't mix those two together. There's multiple ways of doing it. Um the cleanest way that we like, simplest, and it encourages people to clean up their source systems, which best practice almost all the time, um, is we keep both those versions of the records, right? So we have a column for each delimiter. No, we don't even collapse it. So when you're doing it through Power BI, Excel, other tools, most of them, if they're spelled the same, it collapses it automatically into one row. The only gotcha there is if one has like LTD and the other one's LTD, period.

SPEAKER_01 6:30

Yeah.

SPEAKER_00 6:31

That's now different, right? And there's other ways you can you can go about cleaning that data, but we always encourage people to do it in your source system. It's not only gonna help reporting, it's gonna help almost anything else you try to do between these.

SPEAKER_01 6:42

So you can produce a report that shows you, hey, uh, I think these are duplicate.

SPEAKER_00 6:46

Yeah.

SPEAKER_01 6:46

Um and here's a suggested uh correction for these systems. Go make that correction there. Yeah. And then just do that periodically.

SPEAKER_00 6:52

Yeah. Yeah. We usually have like an exceptions report or something. Yeah, exactly. Yeah.

SPEAKER_01 6:57

Okay. All right. Anything else to consider uh just in a quick definition of the gold layer?

SPEAKER_00 7:03

I mean, from a quick definition, no, I don't think so. You know, we've got more like slowly changing dimensions, etc., that you could get deeper into, but there's plenty of good info out there. All right, good. Excellent. Thank you.