Formal, intentional Data Architectures are rare in the wild and for data warehousing they're even rarer. A few textbooks describe principles for managing data in a data warehouse, and there's of course the half-baked "It's all just Kimball!" conventional wisdom. But there's not, as far as I know, a reliable cheat sheet that can help you figure out what your data architecture should look like. And no, the Medallion architecture doesn't count as "reliable."
All this is of course extremely relevant to how you implement AI, however that particular combination of machine-learning algorithms and hype is configured in the year you actually read this post.
This is looong, 8000+ words, so after the jump a table of contents:
We're going to cover these topics below:
- Guidance
- One-Tier Architectures
- Three-Tier Architectures
- Functional Architectures
- Semantic Decisions
A few diagrams here and there too.
Guidance
We want a data architecture to provide guidance on how we should organize our data so it's easy to use and even better easy to exploit. I think that's the most anodyne definition I can give, but it's also pretty specific and so that should make us optimistic, right? Guidance is what we're going to get, and we'll explore what that means below.
First bit of general guidance: Data is not really a thing. Records are, even when they're polystructured or even just randomly structured. Records are what we're managing, and records are what we use to construct the proofs that support the analyses our business depends on. Records are what we pass to the visualization tool too.
Second bit of guidance and then we're on our way. I built my first data management system in 1997, and it was designed to master products, brands, and units of measures across dozens of 20k-line foodservice catalogs. I've worked with a couple of organizations since where the semantic debt was that intense - pharmaceutical companies will run hundreds of product trials and keep all of the records as CSVs, each with its own fun and unique format, for example. That kind of problem is solved easily enough, whether you're Roche or Google, with smart and well-socialized reference architectures.
But you see a kind of unavoidable and sticky semantic debt in Marketing analytics and operations that demands, well, pretty sophisticated entropy management. There may not be a thousand different file-folder structures, like the medical research company. But the only common structure across the whole Marketing ecosystem is a DAG where each node itself has multiple hierarchies attached. Each of those hierarchies will change over time, from top to bottom. 1000 unique campaign DAGs, themselves created often out of nothing using a complex manual workflow with at least three name changes for the whole effort, each run on one or more advertising platforms, often reported at with high degrees of aggregation and through a necessary intermediary, each with necessary invisible variations for even the most meticulous media team. If your experience with semantic debt is primarily the difference between how Product categorizes a SKU and how Finance categorizes it and how Supply Chain does, then Marketing is that problem but to the 4th power. And while less life-and-death than medical coding choices, which are a somewhat similar data modeling problem, there's millions and millions and hundreds of millions more records, and way more money than healthcare, sadly.
Keep these scales in mind when you evaluate these architectures. We've all had to evolve to manage more scale than we expected since the Internet age began in ~1995. People who choose one of the following architectures believe they can scale. Same with me.
One-Tier Architectures
In the beginning there is just the basic One-Tier data architecture. This is what everyone took for granted as the right way to do things in 1998 when I was learning data warehousing, and it's still the data architecture preferred by software engineers. It's the least fancy, from their standpoint, as it really reduces much of the "data" problem to just the design of brilliant software.
Reports point directly at more-or-less active record-creating tables. This is a "One tier" architecture because we think all uses of the data are equally significant. There isn't any formal buffer or transformation layer between the raw data and the end-users, and any of those transformations are done to the records in-flight, from where they're created to where they're used.
More complex Txn dbs may develop a somewhat-latent holding pool of tables designed to avoid contention for resources on the core tables. If business logic is required to make the records in those core tables useful, then this is most often provided in the SQL executed by the report itself (or whatever language is used to query the data store), which is executed by Direct extracts, ultimately just queries that point at an active transaction table.
What's wrong with this architecture? From a software engineering standpoint, technically not a thing. BI use-cases are no different than Supply Chain or Order Management use-cases. The Txn db, whatever modern form it takes, is designed - no engineered!11! - to move the records created by People and machines to the BI use-cases or the Order Management people or whoever else needs it. The engineer doesn't care.
Practically speaking however the One-Tier architecture always runs out of runway and everyone becomes unhappy with it. Engineers have been trying to balance transaction-write and transaction-read for ~5 millenium now. Even when you're writing down the day's balances onto a clay tablet, you can't actually read the tablet safely until it's been dried properly - so basically the next morning. Today high-volume transaction-creating applications (like Amazon or Salesforce) separate those two tasks into separate concerns, as they properly should be. But most software engineers reach for this data architecture when they need one.
One evolutionary phase of the One-Tier Architecture is the development of a control table-based reporting tool, designed to simplify the creation of reports against the core tables using hard-coded joins and pre-determined field names, etc., which theoretically makes server-load tuning easier and more predictable. This solution inevitably causes an explosion of reporting use on those core tables, which will eventually compete with the revenue-generating uses. This is the organization unconsciously reaching for a semantic layer.
The first one of these I saw was written in Perl in 1999, and now they'd be written in Python or Jscript. Because they're "engineered" they theoretically should work with the revenue uses. But they don't. You'll see orgs with One-Tier architectures spending exorbitant amounts of money on tuning and compute infrastructure to be able to balance the two uses. Sometimes this is necessary - someone really does need to report directly off data as it's created. Most of the time it's not necessary at all, but the Engineering team obstinately believes a data warehouse is a weak-willed response to engineering failures that could be fixed if you were just able to defeat Joy's Law.
The only logical internal space in the data warehouse is the Reporting Layer, the space where all the code is intended to be used for "reporting," however that's defined: Oracle Forms, Looker, ORMs, etc. The bottom layer functions as an Ingress Layer, which we'll see defined better later. The records in Ingress are created directly by People and machines interacting with the application software, and made more-or-less available immediately for reporting, as a snapshot of the relevant records at the time of query. This is what the software itself does, to snapshot the current state for a user, but from a data warehouse or even just plain data architecture standpoint this layer is where the data "ingresses" into the ecosystem.
I say records are "more-or-less available" because there's all sorts of uses for a single record at the point of creation, whether it's a singleton in a set of millions of clicks or a request for a refund. "Flash reports" in Financial Services, Finance and Sales are often necessarily reports pointed directly at the tables where the records are created. On the other hand, the application may stack records as they're created so people can respond to the aggregate trend, to the signal generated by comparing thousands or millions of clicks or likes.
As I said, truly obstinate Engineering teams will stick to the One-Tier architecture because BI use-cases are logically no different than Supply Chain or Order Management uses. When software teams are stuck in this mode, believing that all reporting should point to where the records are created, one long-term consequence is a lack of reporting. A One-Tier company may value the "analytics" capabilities built into their app, but it's a safe bet the product managers know intuitively which tables are already too overloaded with read requests, and can't handle more reporting. This creates a structural semantic debt, what I've called Type 4 debt or "epistemic bubble" debt.
Everyone should be able to draw the absolutely latest data at the source for all of their uses, whether it's reacting to a single field change or a million of them. But we live in the real world. So move on. We need a data warehouse.
Three-Tier or Operations Architectures
It was about two years into my career when I realized life would be a whole lot easier as a data guy if I built a "sewage treatment plant" into my data warehouses. I needed a space where I could manage complex transformations and reusable behind-the-scenes plumbing. At the very least an intermediate layer between the source data and the reporting layer would help me avoid the obvious bone-headed "data quality" mistakes that a travelling Sales VP doing demos never recovers from. At best I could build some infrastructure that I could reuse, to save time and make the tables I had collected more productive.
A data warehouse that includes an "Operations" layer, where we remix records we copied from some application data store into records that can be used by the Reporting layer, is a Three-Tiered data warehouse architecture. Records are copied into and deduped in the Raw layer. Those records are then transformed in the Operations layer into tables that can be used by end-users, and (ideally!) tested for consistency. Finally, the outputs of the Operations transformations are copied into the Analytical layer, for use by business people. Notice that we define a layer in terms of user base: Raw is for data engineers or ETL developers (or worst case the software engineers running the app), Operations is for data modelers and data analysts, and the Analytical layer is for data analysts, report developers, and business users.
The Three-Tiered architecture is the most flexible, simplest and still often the most effective data architecture. It checks the security NFR box in terms that Finance and IT Security people can understand. Teams often turn to it the first time the data warehouse needs to combine data from multiple sources, say the CRM and data from the ERP system. One-Tier architecture adherents will try to glue records from multiple systems together in custom data pipelines, working to manage the asynchronous consistency and semantic debt using clever and compute-intensive tricks and a lot of variation in business logic. This "glue" may last through the second and third reports, but pretty soon the intense problem of maintaining consistency in the transformations between raw source and polished target leads a smart team to realize they need some kind of a buffer, and they invent the Operations layer.
I've used the Three-Tier/Operational architecture repeatedly, and friends with judgment I trust believe it's all that's necessary to scale a warehouse. If we expect a data architecture to provide guidance on how to organize data, a Three-Tier system does exactly that, and no more than necessary. We can argue about details - should there be business logic in the Reporting tool or the dashboard? Is Raw just views on a source, or maybe Operations/Stage can be just views and we can run everything as still basically One-Tier? - but I strongly recommend it as a first step in warehouse design.
The guidance is generally pretty simple and logical, because we're encapsulating the architectural principle Separation of Concerns in our design. We use the concept of "user base" to define the Concerns we're Separating. People who need to transform records need dedicated compute and storage, decoupled from the people who need to analyze records, and also the people who need to copy the data into the warehouse to begin with. This is one of the first lessons you learn scaling your warehouse across more users and more sources. So data modeling, a combination of reads and complex writes, is done in the Operations layer. If you need to look at records for business purposes, which is all complex reads and absolutely zero writes, you do that in the Analytical layer. If your job is to copy data, which is just writes, then you do that in the Raw layer.
These layers may be in separate logical databases or all in the same logical database, or implemented in a variety of shapes and configurations. The basic requirement however is that compute and storage be dedicated to those three use-cases. Just this statement of a logical set of requirements is usually sufficient to get the IT team to detach the data warehouse system from the central application database stack, because it sounds like an architecture to senior IT people.
A Three-Tier architecture will scale through most of the analytics needs for a functional unit, and if the team that runs it is diligent and attentive, provide clean, inspiring service to the end-users for its whole lifespan. One reason it scales is simply because of the decoupling of compute and storage across those three use-cases. Three tiers lets you copy, load and transform records near-continuously without interrupting reporting uses. (This is weirdly the basic principle the One-Tier people don't get.) Because you decouple transformation steps from analytical steps, you can manually persist and version business logic like _TYPE and _CATEGORY assignments or grain mismatches.
That decoupling makes reporting more reliable and consistent, and permits the human division of labor across the copy, transform and report steps. Data modelers can focus on business logic and not worry about Python copy scripts; Data Analysts and BI developers can focus on the visualization and explanation parts of their job without worrying about a data model; and data engineers don't have to futz around with Tableau. One-Tier advocates will worry that it takes so long to move records from the source through all those layers or that there's no "single source of truth." But in practice, because we've got our own compute, we can go as fast as we need to.
Where it fails is to tame organic growth. A data warehouse with three layers defined by user group/use-case works when there's not too many sources and/or too much diversity in source semantics. At a certain point, to use a very modern metaphor, a Three-Tiered architecture that includes data across an enterprise - Marketing, Product, Finance, etc. - risks context collapse. To organize all that data, the Operations and Analytical layers inevitably become a rigid structure that can never be changed, or a hippie-commune cross-section of all the Conway boundaries, with business logic scattered here and there in temporary, under-the-desk or legacy semantic pipelines. Developers pile onto one pipeline and tightly couple a range of metrics, or build their own with their own understanding of the architecture.
When lots of Raw tables are shared across multiple Analytical tables, an Operations layer without its own organization will begin to look tangled. A layer that was intended to be just "plumbing" for the warehouse has to take on a more foundational role. Most often we discover we need to persist and encapsulate some business logic as a table we can use for multiple Analytical users, to permanently retire some semantic debt - we need a CAMPAIGN table, for example, that can integrate records from Facebook Ads and (Google Ads and PMAX), with their different kinds of hierarchies because we need to know how many campaigns are active right now, because Finance and Marketing are in contention in the Analytical layer.
It's often not clear how to reconcile the rules we start with in a Three-Tier architecture for a "Stage" or "Operations" layer, such as "all tables should be temporary," with the eventual realities of the warehouse and complex transformations. A permanent table in our Operations layer that contains all the customers and their keys, or all the campaigns, turns out to be a really useful thing in maintaining consistency in the Analytical layer. But teams are reluctant to turn the scratch CTEs and temp tables they use to implement transformations over to analysts and report users who might not like the unfinished nature of the Operations tables. And these tables end up looking too much like entity-relationship tables, after all, when what we're really doing is just building star schemas. (Hah!11!!) We definitely don't want to have to build an entity-relationship data model in Operations, because that's hard and no one knows how to do it. If only there was an easier way to assemble a dimension?
So then maybe we just polish that Operations CAMPAIGN or CUSTOMER table and copy it to the Analytical layer so business people don't get confused?
What about context collapse in the Analytical layer? We have all kinds of tables: Some designed to support PowerBI dashboards and others, with the same data, designed for Tableau. We have scratch tables created by Analysts powering through their work who forgot to drop them, and polished star schemas, and 1000-field Customer360s, and weird stuff the data scientists are doing. There are multiple Analytical user groups, and they each need their own tables for their dashboards suites.
Eventually all of the layers become dense and impenetrable, or rigidly coupled. The third option, a well-tended space where everyone puts their toys back when they're done and no one hogs the black crayon, requires a concerted effort to coordinate and document pipelines and transformations from source to end-user, and a determination to centralize business logic, somehow, in the Operations layer. This is hard to do when BI people have little to no voice in the organization, and Data people in general are at the bottom of the hierarchy in IT teams. The majority of Three-Tier warehouses end up as an organic mess. The internal operational data models in the Operations and Analytical layers drift, the temp tables and CTEs and CTA-pyramids that support each Analytical customer group eventually fragment. No one is completely consistent about how they interpret "Operations" and "Analytical" use-cases, after all, and so with even five teams we'll end up with five different sets of conventions.
It's worth calling that last point out explicitly. Architectures are for guidance. How does the Three-Tier architecture answer questions like: Are persisted ER domains allowed in an Operations layer? Or are those Analytical layer things? If you don't persist those domains somewhere so you can reuse the business logic consistently, how do you ensure business logic is reused consistently across Operations? Is anything persisted in the Operations layer? Is anything not persisted in the Analytical layer?
What is the guidance from the Three-Tier architecture on these questions? From an operations standpoint, as in running your data warehouse like a business and not as a makework project for Humanities majors, these are critical questions. Three-Tier architectures don't provide more guidance about ensuring business logic stays consistent than a very hand-wavey point to the layer in between Raw and Analytical as where it should happen. It's the responsibility of the people who use the Operational layer. So in practice the actual handling of business logic is left to the individual practitioner. In DE-heavy warehouse teams, that means every pipeline is its own work of art, from a business logic standpoint. Or entities are persisted, but only as serialized objects you need a B.Sc. in Python to use. Warehouse teams with "Analytical Engineers" or data modelers might coordinate better, but typically not across star schemas. And BI people of course are all artists, so if they don't put business logic into their dashboards they're either very professional or not feeling well.
Failure in the Three-Tier model comes when Analytical outputs become increasingly sophisticated, but there's no reason to suppose business logic is consistent across those outputs. Reports that count customers or products end up with different numbers and no one knows why. Warehouse developers trace back through the Operations layer and discover multiple temporary tables, or multiple pipelines with different definitions of customer, or Analytical tables that kludge definitions, or its just that different orgs within the business classify them differently and no one was aware of that until now. Or all of the above. Plus so many total tables stuffed into the Operations and Analytical layers that end-users complain they can't figure out where their data is, and developers either add kludgey patches to existing tightly-coupled organic pipelines or build from scratch.
A couple of years ago another data architect and I were asked to come up with a way to prune the tables in the loosely-defined "data warehouse" for the central analytics team at a huge enterprise software company, as part of a consulting gig. They'd done everything right, including using a commercial data catalog to support an incredibly active analytical community. We restricted our scope to just ~5 core Snowflake dbs, each with a Three-Tier architecture organized by schema, and tens of thousands of tables and views across each layer. Nobody knew what was true and what wasn't. We were able to develop lineage of a sort using the assumption there were three nodes in each pipeline graph - raw, operations, analytics - and if we could identify the core reporting fields we could trace up or down the graph and put tables into layers. (And then we could get rid of the stuff that was "bad.") But aside from the heuristic-guided networks we were able to build, the only way to manage that Three-Tier data warehouse was to nuke the thing from orbit.
There is ultimately not enough guidance in the Three-Tier architecture to keep the warehouse from failing.
Medallion Architectures
And now for something completely different.
The first two patterns we've described here are engineering focused. The architecture tells data warehouse people how to organize their system to scale to handle more kinds of records, and more records in general, while staying consistent and continuing to provide useful data products. I started using a Three-Tier architecture all the time in ~2000 because it was just so much easier to scale. But I wasn't the only one, obviously, and people discovered the value of decoupling task spaces across the data warehouse, and the practice took a step forward.
But warehouses still fail with a Three-Tier architecture. The guidance just doesn't seem to prevent the eventual development of an organic mess. Over the years we (as practitioners) have tried various wherefors and whereases to make Three-Tier systems work long-term. We decided for example that the Operations layer needs to use a reference pipeline pattern, or that Analytical layers should be divided by end-user interests, or that all business logic needs to be applied in the Operations layer. But we still depended on the user-group focus of the architecture.
From the standpoint of business people the One-Tier or Three-Tier theories don't make much sense. Business people are concerned about quality, and in reports they want data quality. They want the good data. They want a data architecture that will produce "clean" data and thus prevent the scenario we run into often with both Zero- and Three-Tier architectures, where two people with expensive, well-designed reports about the same business scenario have completely different numbers. Sure, divide the warehouse into "layers" if you want, but each layer better be getting the people who paid for all this the good data. Not the data that isn't clean. The gold standard of data.
In 2021 Databricks formalized an approach they called the Medallion Architecture. The theory and the term had been in the wind for a few years already, at least five. But the Medallion pattern took hold of the business mind, as a clear set of principles that could be used to organize a data warehouse. The metaphor we use for "managing" data is in terms of "refinement of metals" or better yet "value to the business." Something like that.
This pattern is called the Medallion architecture because it distinguishes three grades of data, like the Olympics does with their medals. The lowest level, third place, is "Bronze" data, which ... well, I'll let Databricks describe it:
Bronze is "raw" data, copied from the source. We see a feature of Three-Tier architectures too, because we identify the users of this data - DEs, of course, and then data operations people (wtf?) and compliance and audit teams. You'll notice none of the intended users of the Bronze layer are the fun people at the Christmas party, and they certainly have no power at all in the corporate hierarchy.
Raw data in the Bronze layer isn't as valuable to the business as the next layer, which is "Silver." In the Silver layer we do "data cleaning and validation." Data analysts can use the data here for "a more refined dataset that still retains detailed information...", and data scientists can use the same "more refined" datasets to build models and do advanced analytics.
But the real value of the data warehouse is in the Gold layer, intended for Executives and other decision-makers. Gold is where knowledge becomes power. This layer is also where "dimensional modeling and aggregation" happens, another good piece of guidance. All that refinement in the Silver layer has produced Gold in the form of star schemas. The data here is "refined" and "cleaned" to the maxx!, tailored for business users who need only the best, the Gold standard in data quality. Data of such quality that the signal and intent is crystal clear.
You can see a practical (technical!) example of the progression here:
Medallion practitioners get detailed guidance on how to proceed, like this:
Gosh it's just so obvious, you know? Just filled with guidance. Refine your data, distinguish Bronze from Silver from Gold by how clean the records are. Clearly this is the winning data architecture, suitable for all purposes. And that is basically the position of most data warehouse people now, that the Medallion architecture is the winner. That the Medallion architecture confirms the star schema is really the only possible metamodel in the data warehouse is a plus, from a guidance standpoint. Those two precepts are articles of faith in data warehousing in 2026. Medallion is the best architecture, and the star schema is best and only data model for all reporting use cases, and so star schema = Gold and everything else is either Silver and for technical people who can talk to the business or, ick, Bronze, for the engineers.
I find it hard to even call the Medallion pattern an architecture, if I'm honest. Let's consider the value of this guidance.
First in the Bronze layer we just collect the extracts from Salesforce, our Kafka instance, and some raw Customer data sitting on Cloud storage. Do we dedupe the records? Do we timestamp them or key them or anything? How do we know we've got everything? Well, there's no guidance on deduplication or really doing anything in Bronze, so ... ok let's just stack everything in the Bronze layer. It's RAW after all, raw as it gets, and we're told it'll be valuable to the engineers and the data operations people if we ever find one of them.
Now let's implement the Silver layer, where we begin data cleanup and data validation. What the AF is "data cleaning"? How do we "clean records"? With those dental picks Archeologists use, or is it more of a Power Washing kind of thing? I suppose with really dirty data our Bronze layer will do a Power Wash, and with just mildly dirty data we can use the dental picks. Right? So can we get a Power Wash component in Informatica or Azure Data Factory? What would I even ask Claude? There is no guidance for Bronze beyond "stack it all" so it must be the case that we're managing duplicates in Silver.
The guidance for Silver is just "drop the NULLs" and "quarantine invalid records." But what if the NULLs are on purpose? Or maybe it's just that we remember to set the DEFAULT values on string fields in the Silver tables to 'N/A', which is just about the most basic and least difficult thing you could expect from a junior data analyst asked to build their first table.
Similarly with the "quarantine invalid records" advice: Do we quarantine them in Bronze, or in the Silver layer? If they're invalid why do we want to keep them in a quarantine? Why are they "invalid" and how do we know? If they break quarantine will they infect the whole Silver layer or abscond with the Gold? Maybe "invalid" covers "duplicate" or maybe it doesn't.
So far it seems the people pushing the Medallion architecture believe that you can get "dirty" data, or data that is "unclean" (?), but never duplicated. Which is often my biggest problem.
More guidance: Silver is where we "refine" datasets. DuckDuckGo points me to this definition of "refine":
Nothing even suggestive of a metaphor I can use with say my Salesforce records, which are uh "messy." Hmm ok maybe we'll have to come back to this layer later.
But surely the Gold layer is easy to understand. After all, it's gold!
The guidance from Databricks for Gold is even less specific than it is for Silver. They do tell us that Gold is for "dimensional modeling and aggregation," which makes sense I suppose, if the star schema is all you know how to model. Are records Gold automatically if they're in a star schema, or is there something else to it? (This reminds me not at all strangely of an old problem in Plato.) Because presumably if I had direct access to a star schema at a data source, and copied that whole thing into Bronze, couldn't I just copy it directly to Gold and bypass Silver entirely? Or maybe I'm supposed to copy the star schema from Bronze to Silver to Gold as-is, juuuuusst to make sure. I've done that, myself, because I'm a suspicious sort.
But still no mention of deduplication anywhere in this architecture, or having to reconcile different definitions of customer or campaign. Where do I do that, in Silver? Is a duplicated record clean or unclean or invalid or just in need of charm school, a little refinement?
Here's another question: When I join Gold tables to each other, are they automatically Gold or do they have to go back to Bronze for "cleaning, validation and possible quarantine if they're invalid"? How could any of that be necessary if the records are already Gold? One more: If I need to put a flash report for Sales on raw data in Bronze for executives during Black Friday, does that automatically elevate those records to Gold status? And then once the season is done those Gold tables just go back to being Bronze, of use only to the trolls sorry engineers and operations people? Or maybe Bronze can sometimes be useful in the whole knowledge = power thing.
Ok one more: Say we live in a culture where Silver is prized more than Gold; would the Medallion architecture make any more sense?
I could go on. The thinking behind the Medallion architecture is simple and straightforward. But it turns out that defining your architecture in terms of "quality" as represented by some metals we use to refer to vague cultural eras doesn't actually provide much guidance on how to build and run a warehouse. A little too simple, in other words. In fact it doesn't really help at all, and as a senior data warehousing consultant I spend a lot of my time helping client data architects make some coherent sense of the thing, especially if they use Databricks.
Of course we want to ensure our end-users get the highest-quality data. Puppies for everyone! That's why every Three-Tier architecture worries about the status of "raw" data. One reason Three-Tier architectures fail is because we become acutely aware of the fact that one person's "raw" is another person's "highly refined and very expensive," and neither label prevents it from being wrong, either malformed or semantically inconsistent. But we don't make semantic decisions in a Medallion architecture, we make judgments about how "clean" our "data" is.
Ultimately guidance in a Medallion architecture is just "clean" = "in a star schema," and Silver is thus just "all the tables we use to get to the star schema." Which could be anything, really.
I try to be patient with Medallion people. My primary consulting advice is "specify exactly what syntactic and semantic steps need to happen in each layer." Which is to say, my advice is to think of the Medallion architecture as a business-friendly explanation of why we need an Operations architecture. If your VP says "I don't understand why we have to do all this complex stuff" you can say "bronze! silver! gold!" as a kind of magical incantation and point them at the Databricks page.
But otherwise, if you have to implement a Medallion Architecture, you should really just do an Operations architecture, but take advantage of the Databricks marketing with the business.
Functional Architectures
Finally we come to Functional Architectures. This model popped into my head a few years ago, even though I didn't really have a name for it until recently. But it's proven really useful in strategy consulting, when you're trying to understand how someone's ecosystem works. It's also really helpful as an active data modeler, who sometimes gets tables of various sizes and shapes and is asked to turn them into a consistent data model. And finally, when we're thinking about how to scale integration across dozens of sources, I think the Functional Architecture is the best solution we've got. But it came to me by accident. I'd like to say it was all my idea. But it's so clearly a synthesis of my consultancy's data modeling practice I can't take full ownership. The story goes like this...
A few years ago, during the pandemic, I was on the bench for several months in a row and had some time on my hands. We'd just completed a series of warehouse projects designed to enable brand integrations and migrations. Sometimes the whole business from orders to delivery, sometimes just unintegrated Marketing zoos, and you could include too a project with government ministries with identical models and customers and privacy needs. The scale of these ecosystems was more than anything I'd seen before: Not one enterprise architecture, but multiple. One client had eight subsidiaries, with a full ecosystem of ERP, ecommerce, finance, product, merchandising and supply chain for each. Their data warehouse covered three of the subsidiaries, with something like 5k input tables, 20k pipelines and 50k Microstrategy reports, all semi-automated with unique bits of nearly invisible business logic scattered here and there. The flagship brand had three sources of truth for orders, all of which we eventually determined were inconsistent.
One problem I wanted to solve while I had the time was the onboarding and training of data modeler resources. I wanted some architectural runway for our data people, many of whom might be told they were a "data modeler" and expected to behave as such without much notice. Architectural runway for data modelers meant positions on PII, security, retention, governance, and all the other NFRs that a client might ask about. It also meant guidance on how to start: "OK, first ask your DE to create RAW, OPERATIONS and ANALYTICS schemas in Snowflake..." And then what?
In 2020 Raybeam settled on a Three-Tier architecture with very little effort to get consensus. We were all in agreement, both the software people and the data people, that Three-Tier was the right way to scale. We even described our Tiers in the same terms I've used above, with the confidence of people who've described their approach as "layers for different user groups or use-cases if we're technical..." on many successful projects.
Or projects that at least hadn't failed because the architecture was half-baked.
So far, great. At Raybeam at that point there were three people who functioned as data architects, in terms of people who could design a system that would scale, especially if the client was a bit vague. One of them was me. I knew I was a data architect. The other two people didn't give themselves that title but ran their teams just the same. The fourth data architect we had to consider was a Managing Partner, but he was amenable to good ideas so I didn't think we'd have a problem getting consensus. We'd already agreed on a Three-Tier architecture using both the working data architects and the people that had an interest, many of whom have since become data architects. That alone took years at some of the companies I've worked.
To build the architectural runway I needed some deeper guidance I could document. So I talked to the other two architects, who we'll alias as "Drew" and "JT" to keep their identities secret.
I started to explore the differences between how Drew used the Operational layer and how I did. Drew is a smart guy and at that point relatively early in his career, but he had some very clear operational reasons for structuring the layer the way he did, i.e. a mix of considerations about what he could automate, what took weekly human attention, how much it cost to scale. He saw his set of rules as more efficient and less risky than my own.
I discovered while talking to Drew that I had a somewhat incoherent notion of operational: Partly an "Operational Data Store" that for me consisted mostly an ER model of the business, and partly just plumbing. One key difference between Drew and me was our views on audience: I was pretty comfortable with data analysts and technical business users looking into my Operational layer, because in my case it had a bunch of ER domain models in addition to some plumbing, which then sourced the more analytically-focused star schemas I spun out into the Analytical layer. Drew was not a fan of using Operational layers that way, and thought everything that was useful should be pushed to the Analytical layer. That is, after all, what it's for, is the useful stuff. We did both get mutually hung up a bit on how to think of tables designed to support use in specific reporting tools. Where do you put them in an Analytical layer, when the content of the table is redundant with other tables in the layer, but the structure is probably inefficient for straight SQL analysis? And what do you do with plumbing in the Analytical layer, if you're not using dbt?
And if we say "no business logic in the Reporting layer" does that mean no business logic in the Analytical layer, or is there another layer?
I expected somewhat similar positions from JT, but no such luck. JT had used Three-Tier architectures as long as I had. He agreed with me on the utility for business users of access to tables in the Operational layer. We didn't agree on much else, including the need for persistent business ER domains (where he agreed largely with Drew those weren't necessary as such), or logic in the Analytical layer. And if I remember correctly he had no problem putting utility tables for reporting tools in the Analytical layer, any more than he had giving business users access to Operational tables that sat alongside other tables users might get seriously confused by.
There's an old saying in Philosophy: Two philosophers, three arguments. I started reconciling Drew, JT and myself based on our team adoption of a Three-Tier data architecture, in the hopes I could give a junior data modeler some guidance on how to make those first three months count. With the three of us we ended up, near as I could figure, with about six different architectures with <=66% approval each. I'm not generally afraid to tell people they have bad ideas - in the most California way possible of course! - but I could see holes in everyone's arguments, including my own. My own position appeared to have been based on a complete misreading of Inmon I'd carried around and propagated for maybe 15 years, except that to make it work in the real world I'd distorted the guidance to make sense.
Well that was embarrassing.
That was where we ended up: Multiple definitions of what happened in Operational layers. Very disappointing.
Except that while looking through some code I got from the two of them and some of our other data modelers, hoping for some empirical "ah ha!" moment, I got an honest-to-god empirical ah-ha! moment.
As I took a look across examples of what people were doing in their Three-Tier systems, or even the One-Tier warehouses, and the data pipelines built for all-in-one solutions that glued data together for one dashboard, it was clear we were all structuring our pipelines so they did one or more of seven different kinds of operations. When you stacked these operations you got a simple, scalable warehouse that separated the semantic and syntactic work into nice neat little piles. The result of each step, however, was of immediate value to the business. Maybe not everyone in the business, or right away, but each step had its own user base. Inside our old definition of Three-Tier Architectures, that meant each step must then have its own decoupled layer.
I came to call these steps functions, as in "the functions a data pipeline or data warehouse needs to perform to provide usable records to the business." Maximize the functions, it turns out, and you maximize the warehouse opportunities. Different orders of functions will change the truth, consistency and scalability of the outputs, as well as the cost of compute and the maintenance cost, because the world is not transitive.
Drew, JT and myself (and the Managing Partner) all explicitly saw the need for our SQL and Python code to perform some specific work on the records, something that cost money to do and required humans to think about before hand, between the point they got dropped into our space to the point they were handed off to someone to explain in a spreadsheet.
We were all four in agreement that the RAW layer, the bottom tier of the Operational architecture, should perform two functions. We'd semi-consciously even called out these two layers in the way we built our orchestration platforms:
Ingress: The Three-Tier RAW layer should contain whatever tables or files hold the latest set of records extracted from or provided by a source. Ingress tables or files should look exactly like the ones the source sent us and ideally they are that logical thing, unchanged.
Raw: The Three-Tier RAW layer should also contain tables (or files) with deduplicated copies of all the records received in Ingress files or tables, retaining the original format of the record and maybe adding metadata if that's useful. Each record in the Raw table must be a unique copy of all the records we got through Ingress.
With those two amendments to the Three-Tier architecture, it became clear to me that whether One-Tier, Three-Tier or Medallion, data warehouse people were trying to find a way to execute five, no six, no seven! functions across their systems.
Another discovery. If we had to do the whole architecture in a single pipeline or script for migration to a better system layer, we often built CTEs to organize a CUSTOMER table to manage natural keys. If we had three layers to work with, we might persist the CUSTOMER entity as a domain in a sub-layer inside the Operational layer the way I recommended, or in semi-temporary tables in another sub-layer inside the Operational layer called "Stage," the way Drew recommended. The difference in guidance between the two approaches is that one persists the entity objects in the Operational layer for reporting access, and the other uses entities behind the scenes, in a layer inaccessible to real people called "Stage." Wherever you do it, you need a layer that does that kind of work.
Why? The output of that Entity function is records you can use all over the warehouse. For one, understanding the natural key counts on your core business entities is logically necessary for data quality. We'd made "data quality" a core NFR in our consulting practice, too, as a consequence of a corporate value we called "No surprises." If you didn't know how many records were running through your system all the time, if you weren't in control of your logistics, then how could you make sure the CMO isn't embarrassed Monday morning? You need to know how many CUSTOMERs there are.
But both of those functions are common to our architectures. First, we need a layer to manage and count all the natural keys you might get for a given business "thing," so you don't surprise your sponsor with bad numbers. That's Dave's emphasis in the Operational layer part of the Three-Tier guidance guidance. You also need the "plumbing" function, a Stage layer where you can do all the transformations. And Stage is also a layer that produces records that are useful all over the warehouse: We can use it as well to transform and copy stuff into the Analytics layer or even out of the warehouse itself.
Our OPERATIONS guidance then added these two functions:
Stage: The OPERATIONS layer should contain tables and other objects required to transform records into their target form, but which are used exclusively for transformation purposes and support no business uses, except maybe as they contribute to data quality tests.
Entity: The OPERATIONS layer should contain tables that store records for every distinct natural key associated with the things the business is about, such as customers, products, campaigns, etc., i.e. the nouns and key relationships such as "order" or "shipment" between those nouns. These tables should be derived from Raw tables (i.e. deduped Ingress records) in the RAW layer.
("Is an ORDER an entity or a relationship between entities? Dave, I thought this was going to clear things up." Sure good question. The Entity function is where you manage natural keys for everything, including the abstract things like orders or oncology care plans that are themselves complex relationships between many other entities. Even relationships are "things" or entities in this model. If you can imagine ever getting the request "We need to know how many orders or oncology care plans are in our source systems to avoid surprising the sponsor" then you need an Entity function.)
Our ANALYTICS layer also needs to perform two functions. And here again, we did this separation of concerns in our physical platforms. We just didn't call the functions out:
Pivot: Represent in whole or part on each record an instance of a business process or some portion of that process, e.g. a single CAMPAIGN or ORDER, including the relevant entity instances, their individual attribute values, and their relationships, as recorded in Ingress tables, as a well-formed record, by denormalizing and filtering only Entity tables.
Report: Structure a Pivot table so that it can be consumed by a particular reporting platform, e.g. Tableau, PowerBI or Looker.
A final function was obvious:
Egress: The warehouse must be able to materialize records from Report or Pivot tables for use by other users outside the logical boundaries of the warehouse, to track the provenance of these Egress objects. This may be as a CSV or a serialized object of some sort.
That gives us seven functions a data warehouse needs to execute.
The first thing that struck me was that these functions mapped into the kinds of projects and products data people had been offering business people for years. The Pivot layer is typically star schemas, for example, but also business-operations tables that only show the latest status of a relationship, or giant denormalized CUSTOMER domains in an OBT. The Report layer is inevitable; sure you can write a generic SELECT * in any reporting tool, but they all perform better with their own formats.
The RAW layer itself, if it at least executes the Raw function, is just a "data lake" or "datalake" or "lakehouse" or whatever. RAW becomes even more useful if the Ingress function is consistently executed, for those Black Friday flash reports. Not a super hard datalake to make either. If we standardize, generalize and automate our Ingress and Raw processes we can deliver a "data lake" in a couple of weeks, and so we quickly did all three.
When people talk about the hard "data modeling" part of the warehouse, or the "Silver" layer, they're combining the Entity function that retains and masters natural keys with the behind-the scenes Stage function that does all the messy record remixing. While you may make those two functions "virtual" in the sense that they happen inside a pipeline or using temporary tables or CTEs, you add a ton of business value if you persist the Entity tables and confine all plumbing, of any sort, to Stage.
If you think of a Pivot table as just a denormalization and filtering of Entity tables, it becomes very easy to construct them. The key task is defining the Entity natural key types and the associated attributes that must be joined to represent the original instance of the business process. But after that any reasonably sophisticated dynamic SQL generator like dbt will be able to do the rest.
Not every reporting tool or BI architecture will require its own Reporting layer. Not every warehouse will need to persist the outputs of these functions either, as Entity or Pivot tables, for example.
But it is very helpful to functionally separate the work of the people building reports about business processes or entities, from the denormalized sources of that data. Tables in Pivot derive a lot of their value from direct consumption by data analysts, data scientists and report developers via their Reporting objects.
And here the guidance around the "No business logic in the Reporting layer!!11!!" intuition is straightforward:
We can transform records in the handoff between Entity and Pivot, where we denormalize and filter entirely within Stage, but we cannot transform records except to make them fit into the target format on the handoff to Report.
If we imagine each layer persisted, we see a clean and logical foundational epistemology, what I think of as a "proof architecture." The Pivot table is the source of truth for the Report table, and the Report table is the source of truth and a record of existence for the Egress object. Pivot is supported on the pillars of good (or at least intentional) natural key management in Entity. (You might prefer "derived from" instead of "supported on teh ...") Entity tables are derived from parts of records in Raw tables, which are themselves deduplicated records derived without transformation from copies of the latest source data in the Ingress layer.
One warehouse's Egress object is another ecosystem's Ingress table.
Semantic Decisions
I want to make one final point about the Functional architecture. We talk a lot about the value of the "semantic layer" in 2026, and even think about data quality tests of handoffs between layers in terms of "syntactic" and "semantic" property tests. There's a kind of intuitive sense you can divide these functions into "engineering" and "modeling" tasks, which is sort of true. Some functions appear to be more syntactic than semantic, Ingress and Raw in particular. But the Ingress function is simply a semantic decision that can be automated easily. The first half of the Functional warehouse is about nailing down the natural key management requirements at each transformation. It isn't that hard to derive Entity records with a "SELECT DISTINCT ..." on a small subset of the fields in e.g. campaign or Salesforce records. That identification of a natural key in Raw records for use in Entity is a complex semantic decision, and we may have a number of options. But the identification of a natural key in Ingress for use in Raw records is also a complex semantic decision; it's just that we've all already automated it, and know we'll need to adjust as needed, and what the ripple effects of that early semantic decision will have on the rest of the warehouse.
Each function is a semantic function, then, and should be treated as such.
No comments:
Post a Comment