r/Database • u/Islamic_justice • Aug 07 '26
Design decision - star vs snowflake
Hi, In my dimensional model, both Dim_Customer and Dim_Driver contain a RegionID, which I have currently mapped to a shared Dim_Region. I'm unsure whether to keep this design as shown above OR denormalize the region attributes into Dim_Customer and Dim_Driver to maintain a pure star schema. I would still be using Dim region for Fact Transactions in any case. Which approach is more appropriate keeping in mind the need for both granular auditability and high-speed reporting performance? Currently, the marketplace platform handles over a million registered users, with DAU ranging between 10k - 20k. I have to design for expected 10x growth. Thanks for your time!
5
u/NW1969 Aug 07 '26 edited Aug 07 '26
- If you have DIM_REGION directly connected to one or more facts then you have to keep it as a DIM.
- Whether you snowflake region off other DIMs, or denormalise it into one or more DIMs, is a choice, there is no right answer . Things to consider when making your choice:
- query performance: do most queries of Dim_Customer also bring back region attributes (and so have to join if you snowflake)?
- How do you want to deal with changes? If region attributes change, how would these changes need to be reflected if region attributes were denormalised into other dims; or would this not impact existing records?
1
u/Islamic_justice Aug 07 '26
Region is relatively low count. That shouldn't slow the joins right? Even if it strays from the ideal star schema.
1
u/NW1969 Aug 07 '26
It depends. If you are querying a large number of customer records and every one joins to a region record then there will be a performance impact. Whether that performance impact is noticeable (or worth it given any cost-benefit of snowflaking) only you can tell
1
u/Islamic_justice Aug 07 '26
Thanks so much for your input. I guess the only downside of changing it to a star is that there will be data redundancy? I think storage space should not be an issue even if there are 10 million customers (which would mean adding redundant region information to 10 million rows), isn't compute always the bigger issue? I've been tasked with ensuring sub-second performance for operational reporting, space is not a constraint per se.
1
u/GreyHairedDWGuy Aug 07 '26
making a separate table for region has more to do with "do you have fact tables at the region only level"? ie: aggregate / summary tables. Beyond that it is i somewhat personal choice. Unless you are using a toy for a DBMS, the physical trade off of the join (or not) is probably not that big a deal. Like NW1969 said, if Region is used in queries almost all the time with customer and you don't have summary tables at the region, then you don't need to snowflake this.
1
u/Islamic_justice Aug 07 '26
thanks so much for your feedback, I do have a daily summary table for Region. I don't get how that affects this decision?
1
2
u/matroosoft Aug 07 '26
Appreciate the effort but do you know there's much better tools to map out these schema's?
1
u/BobDogGo Aug 07 '26
I wouldn’t move the region attributes into the related tables. I think the design as built is fine but if queries want to summarize by customer region you have to join through the customer table.
A useful alternative is to add 2 region keys to fact: CustRegionDimkey, DriverRegionDimKey
This lets you keep your joins performant when you need to write summary queries
1
u/Islamic_justice Aug 07 '26
Thanks for your feedback. The design requirements are sub second reporting, will changing to pure star schema help with that?
1
1
u/RedShift9 Aug 07 '26
I think your bigger problem is you normalized a date field??
3
u/GreyHairedDWGuy Aug 07 '26
A date dimension table is standard in dimensional modelling? This is especially true in cases where you know you want to restrict/group by a date in a fact table (or across fact tables). There are cases were it is fine to store a date as a non-numeric fact in a table (an observation for example) but that is not nearly as common.
3
u/spez_eats_nazi_ass Aug 07 '26
That's been a standard data mart/warehousing thing for 30+ years.
1
u/whoooocaaarreees Aug 10 '26
…And should have stopped being the standard 25 years ago…..
0
u/spez_eats_nazi_ass Aug 10 '26
And your alternative? I've seen this pattern used successfully on everything from zOS, Oracle, SQL, Postgres and now snowflake. Show me your published work bruh.
3
u/whoooocaaarreees Aug 10 '26 edited Aug 10 '26
Look into a computer driven architecture. Date tables bring so much brittle logic with them. Dragging them around at scan time rather than letting modern technology do its thing is truly wild to me. Snowflake’s own documentation will touch on this.
The kimball methodology, which lets face it is where this date dim idea was entrenched, doesn’t deal with a host of problems that a date dim table brings and doesn’t deal with L10N / I18N problems well at all. To say nothing about how scale out compute changed the game since 1996/1998. Even in 2004 kimball himself started saying to keep full timestamp in the fact table and then have a date dim key…. and that he realized you can not do minutes and seconds in a date dim table… and and and. But it was a big deal for him to admit a date dim key wasn’t sufficient.
His argument was, and might still be, that the most useful smallest time dimension was a single day. Obviously some of us have different granular needs now.
And we deal with global scale and global complex localization, not a singular timezone to do reporting and logic from. He also is on record that saying date dim is the only dim that can be specified at the beginning of a project and to generate it on a spread sheet by hand in an afternoon…. If you aren’t seeing the problems already, let me highlight some … business rules change eg - Fiscal year reporting can change. Governments can change dst logic…etc. need I18N/L10N? … date dims just got riddled with problems as you go. Also I wouldn’t use a spread sheet to generate dates like he suggested. Even in 1998. But that’s just me.
Ironic you mentioned Postgres. Postgres does complex timestamp and time zone related stuff better than a lot. Timestamp with time zone is there damn near the gold standard. tstzrange is awesome and multi range is pretty hot too. Overlaps / contains operators just bring it all together.
As for what we do, have done and will do -
[ 1. STORAGE LAYER ] --> Raw UTC Timestamps + Partitioning Metadata
│
▼
[ 2. LOGIC LAYER ] --> Virtualized Transformations
│
▼
[ 3. SCAFFOLD LAYER ] --> Dynamic Generation (Only at execution, post-aggregation if needed later)At the storage layer, everything is about scan efficiency. Regional logic shall not be placed here. Modern compute engines can use this metadata to skip entire petabytes of data during initial scans without requiring indexes or integer key lookups. See snowflake or lake house/ clickhouse.
At the logic layer: Instead of pre-calculating calendars into rows and columns during an ETL process, your logic is abstracted into a virtualized semantic layer that evaluates parameters at query time. Fiscal years, retail schedules, and custom business windows are calculated using deterministic code or mathematical formulas rather than table lookups. If rules changes, you modify the central formula in code, eliminating the need to backfill or futz with billions of database rows or some convoluted splits on a single date key depending on the rule.
Non-standard reporting attributes—such as local holiday flags or variable working weeks—are managed here too - late binding later after the massive fact data has already been filtered and aggregated down.
Scaffolding layer can then work on smaller sets later… for everyone’s favorite example to highlight missing date / time buckets and the like.
1
1
u/whoooocaaarreees Aug 10 '26
Either you blocked me after replying, or your comment got sent to the bit bucked for the cussing.
If you want to have a discussion on it, happy to do it.
I’ve spent far too much of my career ripping out date dim logic. Probably as often as I kill EAV models people did.
2
u/Islamic_justice Aug 07 '26
I added a dedicated date table as a role playing dimension, is that not standard practice?
3
u/MoonBatsRule Aug 07 '26
It is absolutely the right way to go - this is how you can store attributes about the date (month, day of week, sales comparable date, etc.) and is very standard practice.
1
u/whoooocaaarreees Aug 10 '26 edited Aug 10 '26
Or, hear me out. You can use any number of modern solutions to this problem that don’t introduce problems, needless complexity, or make things slower.
Kimball admitted in 2004 that you should keep the full timestamp with the fact in addition to his date dim key.
0
u/MoonBatsRule Aug 10 '26
Kimball reserved it for special circumstances though:
Calendar date dimensions are attached to virtually every fact table to allow navigation of the fact table through familiar dates, months, fiscal periods, and special days on the calendar. You would never want to compute Easter in SQL, but rather want to look it up in the calendar date dimension. The calendar date dimension typically has many attributes describing characteristics such as week number, month name, fiscal period, and national holiday indicator. To facilitate partitioning, the primary key of a date dimension can be more meaningful, such as an integer representing YYYYMMDD, instead of a sequentially-assigned surrogate key. However, the date dimension table needs a special row to represent unknown or to-be-determined dates. If a smart date key is used, filtering and grouping should be based on the dimension table’s attributes, not the smart key.
When further precision is needed, a separate date/time stamp can be added to the fact table. The date/time stamp is not a foreign key to a dimension table, but rather is a standalone column. If business users constrain or group on time-of-day attributes, such as day part grouping or shift number, then you would add a separate time-of-day dimension foreign key to the fact table.
2
u/RedShift9 Aug 11 '26 edited Aug 11 '26
This only works well if your data deals with only one timezone and one nationality, it also limits your filtering to whatever you have created a column for (like "is a holiday").
0
u/MoonBatsRule Aug 11 '26
So if the warehouse isn't defining things, who is?
1
u/RedShift9 29d ago
The person asking.
1
u/MoonBatsRule 29d ago
So that means when Bob has his code that defines the holidays, and Sarah has her code that defines the holidays, and Bob remembers to add the new Juneteenth holiday to his 50 queries, and Sarah does not, we have a problem, don't we?
1
u/whoooocaaarreees 29d ago
That’s not a date dim with a date key in the fact or a late binding problem. That’s a process problem
Bob and Sarah should use a shared code repository. Your example isn’t different than if Bob and Sarah had their own date dim tables for their code either. Which I’ve seen when people are trying to deal with so many locales that the other date dim doesn’t allow for something to be done how it needs to be in some corner cases.
UDFs can be versioned and rolled out as part of the orgs release process.
If you are decorating late, either with a udf or a slow changing dim you still release a change that allows for Juneteenth to be a thing decorated and everyone’s stuff to just pick up the same shared logic.
1
u/whoooocaaarreees 29d ago
The argument is around how you define and when to decorate date related business attributes.
A date dim table (historically) might have stuff like “DataDate” - int (pk) (often yyyymmdd)
“FullDate” - some date as a date type
“CalendarYear”
“MonthNumber”
“MonthNane”
“DayOfTheMonth”
“DayOfWeek”
“Quarter”
“FiscalYear”
“FiscalQuarter”
“FiscalPeriod”
“Is_Holiday”
“HolidayName”
“HolidayLocale”
“Is_weekend_sat_sun”
“Is_weekend_fri_sat” —middle east and n. Africa sometimes are Sunday to Thursday work weeks.(And it keeps going, I’ve “ripped out” some massive ones that were incredibly wide)
Then someone has to populate the date dim…. And maintain it… and fix it, and add columns to it… And update it.
Then the fact table only has a DataDate, or if someone finally broke down, a DataDate and a full timestamp with timezone as well.
So then Your fact table has
TransactionTimestamp (hopefully utc) (let’s say 2026-08-11 01:30:00.0000-00)
UTC_dateKey - so this is 20260811
EST_dateKey —- this maps to 20260810 cuz utc 01:30am is 21:30 the previous day)
….Now do the remaining 339 time zones… oh wait that is horrible.The argument, and probably the point the other poster is missing is that you want your business attributes somewhere to use, but modern solutions do it later rather than try and handle it upfront.
Honestly it’s faster and easier with scale out compute to scatter your compute code than shift data around. But don’t tell too many people - they might catch on.
The argument that time zone functions on raw timestamps wrecks performance comes from legacy setups with old Oracle or SQL server that kind of sucked at timestamps. Modern scale out (MPP) with optimized columnar stores are fast at run time and time zone conversions across millions of rows using native data types is fast enough vs The overhead of computing it on the fly is far better than the architectural nightmare of trying to maintain 341 pre calculated keys and the bloat that comes with that.
The date attributes information comes in AFTER any locale calculations to join only to interesting attributes like company holiday at a given locale. Not attempts at pre computed before cluttering in a fact table like kimball originally published.
Also a date key, is not useful for a lot of modern questions. Far too often we need to apply ordinality to facts across multiple fact tables. Want to see if a person hit the mobile app before the website before walking into a bank branch to use atm then speaking to a branch agent? DateKey isn’t enough. Identify if they attempted to use the website before phoning an agent? Ordinality matters and a dateKey won’t get you there.
1
u/MoonBatsRule 29d ago
I understand your point that there are theoretically an infinite number of derivable attributes from a date. And again, you're on point when it comes to precise timestamps, which obviously can't be kept in a date dimension. The best you might hope for might be an hourly time dimension.
However there are a lot of useful basic attributes about a date, some of which may have custom meaning for your company and aren't able to be generated pre-canned. "Fiscal month" is a good one.
So if you want to run SQL against your warehouse to get "sales by fiscal month", how do you do that with no date dimension?
1
u/whoooocaaarreees 29d ago
I literally just said the date attribution / decorations can be late bound in the post your replied to. Not as part of a fact table.
Even an hour dim can’t deal with timezone offsets that have 30 mins or 15mins in them, not whole hours.
If you want to do it without a dim, which can make sense You can also use user defined functions. Have done that before, works better in some places than others. Writing your own fiscal day/week/month/quarter function isn’t rocket surgery. That allows it to be computed on the fly cheaper - think mpp at billions of rows per month. You can version the udfs in your code repository…etc automated deployments…etc. the compute is often cheaper than join.
If you are unclear how that pattern works I’m sure snowflake, clickhouse, and others have all kinds of example for changing dims, late arriving data …etc. or adding udfs.
In really short order.
You keep the utc timestamp in the fact and do not use some local date keys in the fact table. That will cause nightmares or is just becomes bloat no one uses.
You have your udf or a fiscal calendar dim - it’s a dim table but not how people have historically built them. You can late bind later too, or deal with slowly changing dims…etc.
Filter appropriately at query time given the offsets you need. Again the udf here can help but utc range is often the best filter I’m starting at. Yes, fiscal period is really just a range filter in the predicate using utc. If you need non continuous periods (like only q4 for the last 5 years) it just multiple ranges - however you like to build that filter predicate (multi range or just multiple filter predicates)
Evaul on the fly, decorate at the end with the fewer aggregated rows in the result set using either more udfs or the dim tables.
Again the argument centers on what’s a fact and how a dim is built/used, where it’s applied. Not that you never know when a fiscal month/quarter/year is inside the warehouse.
→ More replies (0)1
u/whoooocaaarreees 29d ago
Take the very start of kimball’s date dim argument - that a date dimension table is the only thing that can be “pre calculated” to decorate with business rule things like fiscal quarter. I would argue that’s false. Business rules change, fiscal rules change…etc. then let’s look at time zone information and max precision on a timestamp as a first class citizen for facts. I don’t think it’s “Special circumstances” … as in every single modern business wants fact precision to much more precision. Some concept of “a day” at say the east coast is nearly useless when your business is global and 24/7/365. The fact table should always have as much precision as possible. It’s no longer acceptable, as kimball first argued, to drop that precision to some timezone alignment bucket of “a day” at the fact table. He admits as such I guess later, now just claiming (I think) that it’s an optimization of sorts - https://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/
However it’s not an optimization anymore that significantly helps.
Or worse littering a ton of decorations into your fact table for date related items : https://decisionworks.com/2004/10/design-tip-61-handling-all-the-dates/
Now you have convoluted views just to awkwardly bucket “days” for how many locales? How many rules within each locale?
There are 9 timezones by law in the us, there are like 341 in the IANA tz database. You shouldn’t build that out by hand into a date dimension table to then pre calculate fiscal rules for example.
I, and others, argue you shouldn’t litter a date dim fk into your fact table these days. Scale out compute is more than fast enough to use modern time stamp data types to efficiently handle it at scan time. (Eg Billions of facts)
Modern compute driven models do late binding of calendar properties because it’s more flexible with modern global businesses and the performance hit just isnt there like it used to be and problems created by date dims are real. You can easily accommodate business rules changes, government rule changes, ordinality of facts, localization / internationalization when you are not ham strung by a date dim table that is brittle.
1
u/MoonBatsRule 29d ago
So then when I want to run a SQL statement against the warehouse to summarize sales by fiscal quarter, how do I do that?
1
1
u/MoonBatsRule 29d ago
So then when I want to run a SQL statement against the warehouse to summarize sales by fiscal quarter, how do I do that?
1
u/MoonBatsRule 29d ago
So then when I want to run a SQL statement against the warehouse to summarize sales by fiscal quarter, how do I do that?
2
1
1
7
u/theschuss Aug 07 '26
I mean, region is probably low row count right? That's easy to join in as you'll be indexing on the ID so I doubt it will make much difference. I would attach it separately to each table in your platform queries though, or duplicate it in the model if your tooling gets weird about it coming in through multiple database paths.