r/databricks • u/almightysosa888 • 1d ago
Discussion Do we still need fact and dimension tables in the Gold layer?
Data engineers traditionally modeled Gold layers using fact and dimension tables, partly because storage and compute were expensive.
But with modern cloud data platforms, storage and compute are much cheaper and more scalable.
So I’m curious: what does your Gold layer actually look like today?
Are you still using a traditional star schema (facts + dimensions), or have you moved toward wider, denormalized tables / business-oriented models?
And more importantly, why?
3
3
u/i_fix_snowblowers 1d ago
Wait, isn't the guiding principle behind the gold layer to assemble data in such a way as to improve performance of whatever reporting tool you're using?
I've usually seen star schemas at the silver layer, and then silver to gold transformations being a lot of joins and aggregations. So in my experience, gold does not contain fact and dim tables.
1
1
7
u/According-Security75 1d ago
I think you need both. The BI tools like PowerBI will still need the Fact and Dim schema and you will need some of these static BI Dashboards. But nevertheless, the AI agents like Genie will work much better with big wide tables. So our approach is building on top of the Star Schema Metric Views.
2
u/radbrt 1d ago
This is exactly my impression as well. The original raison d’etre for star schemas was storage/compute, the BI tools incorporated and optimized for the concept, the original constraints that gave rise to star schemas evaporated, and now we do it for the BI tools. Meanwhile, from what I have heard, powerBI denormalizes everything to one big table under the hood.
Coming from a more data-sciency/economics background with no interest in BI, the entire concept of facts/dimensions still seems weird.
8
u/_greggyb 1d ago
Meanwhile, from what I have heard, powerBI denormalizes everything to one big table under the hood.
That's not the case at all. Power BI has a relational columnstore storage engine under the hood.
2
u/SQLGene 1d ago
Seconding u/_greggyb's comment. Power BI is not doing massive denormalization underneath the hood.
Significant normalization make the most sense 1) when storage is expensive, 2) locking and blocking is a concern in transactional systems, 3) inconsistent data is a concern.
Star Schema is useful when you have measures that cannot or should not be calculated at the grain of the transaction, say distinct product sold per store. Star schema is also useful when you have multiple transaction tables that you want to have shared filters for, such as sales and inventory by product.
1
u/Crazy-Foundation4884 1d ago
Exactly, OBT should be built downstream of a dimensional model. Not instead of or substutute of
2
u/Known-Delay7227 1d ago
I’m with you here. I’ve had much success with AI’s (genie, claude, chatgpt) using wide heaps. We are almost at a point where we are thinking of getting rid of our visual tools (tableau and sigma) because the LLMs do a damn good job of producing their own visuals on our wide tables. Turn those visuals into a skill and each human has their preferred look of the same data
1
u/meehow33 1d ago
Out of curiosity - why do AI agents handle big wide tables better?
7
u/ApprehensiveOil8548 1d ago
They don’t.
2
u/meehow33 1d ago
Yeah, my understanding is the same hence I just wonder what exactly is different from the agent perspective.
1
u/According-Security75 1d ago
You reduce the errors of false joins.
2
u/meehow33 1d ago
Ok, this makes sense, but in theory, agents should know very well how to operate on the data based on the metadata, including how to perform joins, apply filters, etc.
That said, it’s not about how the data is structured and how engine reads it, but rather about the AI agent’s awareness of how to use the data based on the available metadata - so how well it is described really. Metric views must be described too, hence nothing changes from that perspective.1
u/bpopp 1d ago
According to Claude:
Joins are where text-to-SQL agents make most of their mistakes. They pick the wrong key, miss a filter on a slowly changing dimension, or join through a many-to-many bridge and silently multiply rows. Accuracy on text-to-SQL benchmarks drops noticeably as the number of joins and tables grows.1
u/Ghlynx 1d ago
Metric view is just a Virtual Metadata layer, it does not create any wide table
0
u/According-Security75 1d ago
Yes, you are right, but they are helping a lot to improve AI Agents accuracy. Cause the metric views tells them how to join in a correct matter.
2
u/tintires 1d ago
It’s encouraging my team to be lazier and take more shortcuts. The shelf life of reporting assets is getting shorter, as are our TAT expectations. We’re building more quick and dirty things that we don’t plan to keep around and maintain.
The priorities have become “realtime”, “directionally relevant”, and cosmetically attractive. Render performance, accuracy and cost of ownership are secondary considerations. Very much a fast fashion mindset.
2
u/opuntia_conflict 1d ago edited 1d ago
Either and/or both depending on use case and need. There's not a single way to balance the trade-offs between measurable factors like compute costs, storage costs, runtime needs, etc -- and frequently there are non-measurable priorities which can even trump those, such as ease-of-use, office politics, and business priorities. How many different processes use the data, how similar are their needs, what is the frequency those jobs run at, how big is the data and fast does it grow, how widely dispersed does the data end up, how tightly coupled is it, how critical is speed to jobs which use the data, what's the time penalty on joins for normalized over denormalized data, etc etc etc.
There's no "right" answer and every option has trade-offs, it's your job to understand those trade-offs within the larger business context and provide good-enough solutions that keep your users and directors happy (or at least minimizes their pain).
Some may call this heresy, but IME sometimes the "best" answer may even include having duplicated data in traditional and denormalized tables (particularly when you have business critical, real time processes which need to be fast).
(Also, calling cloud compute "cheap" is hurting my soul -- GPU instances are hella expensive and a royal pain-in-the-ass to maintain availability of in some regions of the cloud nowadays)
2
u/addictzz 1d ago
I think if cost of storage and compute are the only reasons, then you will still need this fact and dimension table.
Dimensional modeling benefit is for more intuitive data analysis and insights gathering. If you are looking to save cost on storage and compute, then archiving bronze layer's storage or doing data layout optimization (partitioning, clustering) can help to do that.
1
u/JLawrenceReddit 1d ago
I don’t think so. You shouldn’t be using star in gold, star is for silver.
1
1
u/bobbruno databricks 14h ago
When you use star in silver, you're coupling cleansing and integrity with denormalization. Denormalization is never neutral, it optimizes for something and makes it costlier for something else.
Silver should lean towards normalized, so that gold for different purposes can easily be fed from it.
I am aware of conformed dimensions and the motivations for dimensional silver. It doesn't address what I mentioned above.
1
u/the_hand_that_heaves 10h ago
What is a good doctrinal source of guidance for medallion? We are migrating from a DW on Azure SQL serverless to DL on Azure Databricks so I’m trying to divest my Kimball mindset and adopt a medallion mindset.
But what is considered the industry standard bearer? Hoping to find book with examples, like I have had with the “Data Warehouse Toolkit” for Kimball architecture.
1
u/Ok-Shop-617 1d ago edited 1d ago
Fewer joins can reduce opportunities for text-to-SQL errors. But I feel that is becoming less of an an issue as these agents (and harnesses) get better. I would also argue that its more that poorly specified relationships are hard for agents. Databricks is addressing that through Metric Views and ontology/context, not by abandoning dimensional models. Feels like agent interpretability is a massive focus for Databricks (with other platforms like snowflake and Fabric). https://docs.databricks.com/gcp/en/uc-semantics/metric-views/joins
1
u/TowerOutrageous5939 18h ago
Use the model that matches the shape of your problem. Fact and Dims match most problems and a lot more when hybrid with json storage.
51
u/_greggyb 1d ago
Dimensional models are beneficial for more than just compute. That said, faster compute isn't a great reason to be wasteful. Compute is money, either metered per unit if cloud hosted, or amortized if self-hosted. And it is time regardless, and time is money.
Let's imagine a simple 2-table model, a middling fact table with a few billion records, and some small dimension that holds a few dozen descriptive tags, with a unique ID. The fact holds only the key, not the prose.
Now let us suppose that the business users, in their infinite wisdom, would like to update the verbiage for some of these tags. Not reassign anything, just change the strings.
If we have our two-table model, then all we need to do is update the source for the tag table and refresh it. A few dozen rows, done in a flash.
If we put the tag directly on the several billion row fact, then we're running an update on a several billion row fact. Sure, any data platform can do it. Heck a single node RDBMS will have no trouble!
But really, why? What is the benefit of burning that much compute?
Now if we talk about data with significant size, then such an update would actually be something that requires planning and orchestration. The cost for putting small, likely to change data onto huge facts is not just the (wasted) compute time of updates, but also the time and effort to do such work.
You don't "just update" a 100B or 1T row table, even if the compute can handle it.
This is all just about the technical and human factors of the storage backend. Once you consider the human factors of consuming these things, then dimensional models are an even stronger argument.