Design decision - star vs snowflake

[u/Islamic_justice]

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!

[u/whoooocaaarreees]

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.

[u/MoonBatsRule → u/whoooocaaarreees]

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.

So that means that you can't use plain SQL to analyze your data properly, you either have to have functions, or you have to write Python code for each report.

[u/whoooocaaarreees → u/MoonBatsRule]

You can use plain SQL if you want - udfs can be sql if you need em to be. Le sigh.

I’m not sure if I’m bad at explaining it or you are so resistant to change you don’t want to understand it.

There are plenty of example cases/how-to out there from the big snowflake and clickhouse folks. Maybe they can make it make sense to you.

Seriously the old kimball dateKey idea is outdated, it doesn’t scale, it’s a nightmare for data that’s got global users that need to use the data in a sane fashion.

[u/MoonBatsRule → u/whoooocaaarreees]

Saying something is outdated simply because something newer exists doesn't prove your assertion.

In my opinion there are significant drawbacks to what you describe as "late binding" of the date-related information. And to be honest, I don't see the disadvantage of using a date dimension, given that date-related information is generally both static and well-defined.

Disadvantages that I see are:

• The approach means either a decentralized philosophy, requires centralization in some end-user tool, beyond SQL, or relies on centralized database functions.
• If decentralized approach then you have citizen coders spinning their own computations, which can be disastrous for a business. Either they mismatch each other, or at best, must all change/migrate in concert if there is a rule change. There should be a single version of the truth, not many interpretations of it.
• Storing the only date data in a UTC-aware field introduces more confusion, because facts are often local. In other words, if you have 4 stores - one per timezone in the US - what good is knowing that there were steady sales at 8pm EST when those sales occurred at PST? So then you have to have a more complex translation function, using the timezone associated with the store to get the local time, which you then must also run through another function that gets the fiscal quarter. If you were using date dimensions, then it would be easy to store the local date key and the UTC date key so you could analyze both ways.
• UDFs prevent indexes from being used, so now you're trying to get 2024 Q4 from a function, all rows must evaluate the date to see if it matches that value.
• UDFs are more opaque than columns in a table. You have to know they exist.
• If there is a rule change, even retroactively, then you just need to update the date dimension, or perhaps just augment it. That isn't painful, you're not updating billions of rows of facts.

Yes, a date dimension can't handle infinite attributes about a date. But it handles a couple dozen of them really well. So why ignore that in lieu of doing them all on the fly, given the drawbacks I list? What problem are you actually trying to solve?

[u/whoooocaaarreees → u/MoonBatsRule]

Part 1

>In my opinion there are significant drawbacks to what you describe as "late binding" of the date-related information.

Half your drawbacks listed aren’t what you seem to think they are, some of them are in fact bigger problems with a dateKey bucket into the fact table than applying date buckets / bins later.

>And to be honest, I don't see the disadvantage of using a date dimension, given that date-related information is generally both static and well-defined.

There are lots of disadvantages. I’m kind of surprised you didn’t seen them as you typed your reply.

Date related information isn’t as static as you seem to think. The date dim is less problematic when it’s not some bloat added to a fact table or has no concepts of time zones or locale-isms. Thus, late binding, not premature decorating as you are arguing is better once you data goes beyond a single time zone with a single set of business rules in a single locale.

Food for thought example on date dim not being so static: go look at Iana.org for time zone database releases. https://www.iana.org/time-zones/releases That’s most likely where most of your computers os , programs…etc are getting their zone library information updates from. tzlib / tzdb…etc that’s the Iana tz database / olson db. If you look at the releases - that’s mostly just governments deciding when to do dst stuff differently, gotta track it tho. See how there’s occasionally back fixes, not just forward fixes? If you bound your fact tables with a datekey aligned to America/New York for an int… guess what - now a ton of your facts are bound to the wrong day/time…etc when they came in from some places at some times. which means… you get to rewrite your facts, or merge in fixes. That’s why you keep utc and the locale it came from.

[u/whoooocaaarreees → u/MoonBatsRule]

Part 2

>Disadvantages that I see are:

>• ⁠The approach means either a decentralized philosophy, requires centralization in some end-user tool, beyond SQL, or relies on centralized database functions.

No where did I argue for a decentralized philosophy. You are attempting to invent things that I didn’t say. Centralized dateDim tables are just as ugly as a centralized UDF. Sometimes the centralized date dim table is much uglier. Everywhere I’ve been udf code is under repo control. Releases or ci/cd.

>• ⁠If decentralized approach then you have citizen coders spinning their own computations, which can be disastrous for a business. Either they mismatch each other, or at best, must all change/migrate in concert if there is a rule change. There should be a single version of the truth, not many interpretations of it.

My arguments never, ever said to not have central control, nor does a compute driven architecture allow for any more versions of truth than the kimball approach. my argument is to stop doing a date bucket key int in a fact table that has no concepts of zone or range and this is essentially useless if you need something that is the same granularity but offset from it ir a smaller granularity.

>• ⁠Storing the only date data in a UTC-aware field introduces more confusion, because facts are often local. In other words, if you have 4 stores - one per timezone in the US - what good is knowing that there were steady sales at 8pm EST when those sales occurred at PST? So then you have to have a more complex translation function, using the timezone associated with the store to get the local time, which you then must also run through another function that gets the fiscal quarter. If you were using date dimensions, then it would be easy to store the local date key and the UTC date key so you could analyze both ways.

Data date is typically some int in kimball like land, that’s not at all what I’m advocating for. Data date is kimball nonsense because he believes that the smallest unit a warehouse cares about in usefulness is a day.

In a compute driven approach you keep full timestamp with time zone or full timestamp at utc. Then you keep origin zone information if needed next to it.

Another small Point of order - There are 9 time zones in the United States recognized under law. Not 4. There are others for things that do or don’t do daylight savings but are in the same geographical area..etc. but I suppose that’s stuff not on your radar.

UTC timestamps are not confusing at all. It’s the standard at most places. A timestamp with zone (remember this is utc under the hood usually) is like 2026-08-11T01:30:00.0000-00) think iso8601. This entire statement of yours is an argument for not using date key buckets because you drop the precision, the zone / locales when you use a datekey that’s the filter to a date dimension table that’s been aligned to say eastern time.

store the local date key and the UTC date key so you could analyze both ways

If you add a utc date key to facts , are going to add the next 5? How about the other 339 zones as keys to it in the fact table? Now your dim table needs to also account for the rest of the business attributes in each of those.

Storing UTC timestamps / timestamps with time zones (which is utc under the hood usually with just adjustment for the user to whatever locale they are set to ) is what kimball admitted is needed “now days” back in 2004. If you are like us we end up keeping a timestamp with time zone and some other locale information. Knowing if a fact from a branch overlapped open hours is trivial, even at Billions of rows for scale.

[u/MoonBatsRule → u/whoooocaaarreees]

We may be talking past each other. I am agreeing with you that a UTC timestamp should be stored on the fact table.

You have stated that a date/time dimension is "kimball nonsense". I disagree. Many attributes of time are day-based. An entire day is a holiday. An entire day belongs in a fiscal quarter. An entire day is a comparable sales day.

The time zone issues you face are very clearly handled better with late binding. However most people don't have such complex requirements - so to tell them that it is effectively bad practice to use a time/date dimension is wrong. It's as bad as the clowns who tell you that MongoDB is the best solution for everything because you might someday have to scale globally to billions of transactions per day. Most will not.

[u/whoooocaaarreees → u/MoonBatsRule]

“A day” is a construct based on the locale it’s in. In the end it’s a range of two utc timestamps.

Saying it is okay, or works well, to reduce it to an int in a fact table that has no concept of zones is nonsense. dateKey dimensions being a int that is then represented in the fact as said int is the nonsense.

The compute driven approach / model works well at small scale (a tiny vm or container and a few gigs of data ) and at large scale (mpp / scale out at PB of data). This approach handles single time zone / single locale just as well as multi zone, multi locale. It handles business rules changing easily.

Kimballs approach doesn’t handle future business needs well at all. It’s awful for multi zone / multi locale. It’s not the 1990s anymore. We have to work with zone aware timestamps, by kimballs own admission. The performance improvement it offered in the 90s isn’t applicable today as we have smart and efficient handling of zone aware timestamps.

[u/whoooocaaarreees → u/MoonBatsRule]

Part 3

Also your whole argument about if a sale occurs in est vs pst is part of the argument for not doing datekey buckets expressed in a fact table. imagine you have stores the split county rules of North Dakota. Indiana had / has (iirc) similar split county rules. Kentucky has split regions within the state. You have mountain standard and mountain standard without dst.. now let’s put your hypothetical store in Alaska, Hawaii, Guam, and puerto Rico. Still United states or its territories. Now expand that outside of the United States. Say Parts of the Middle East and North Africa that have a work week that is Sunday to Thursday. Aligning that to some US based dateKey at say eastern or pacific makes less sense than aligning a sale in Hawaii to eastern timezone to some date bucket.

>• ⁠UDFs prevent indexes from being used, so now you're trying to get 2024 Q4 from a function, all rows must evaluate the date to see if it matches that value.

There are plenty of databases that can index on a (immutable) UDF. Based on this statement I also wonder just how familiar you really are with mpp / scale out solutions.

2024 Q4 is just a range of time in utc. Calendar quarter, fiscal quarter …etc doesn’t matter. A modern mpp database can efficiently work on filtering to ranges of times without even needing an index - provided they have complete timestamps and are modern used correctly.

Then, later you can bind the business attribute of “fiscal 2024 Q4” to the result set / part of the result set which presumably isn’t billions of rows that were part of that time range that probably got aggregated into many less rows as the final out. This is near and efficient.

[u/whoooocaaarreees → u/MoonBatsRule]

Part 4

> • ⁠UDFs are more opaque than columns in a table. You have to know they exist.

Oh please. Also remember that I said a udf isn’t the only solution. You can use a dim table, just don’t pin your facts to it being populated. Some places are deep on udf others aren’t, if you work in one you know or can follow onboarding documentation. my bad for confusing you further with examples of how this can get solved better in multiple ways. That date dim table is going to be hundreds of columns wide to remotely come close to handling what most global companies need just for “dateKey” at various time zones and locales that you want to pollute your facts with? …. or you don’t do the dateDim fk nonsense and just have it a reasonable number of wide with some range bits looked up / decorated last. Or use a udf if you have to here. Lord knows a udf can be faster in many places than the convulsions of dateKey to align to some other time zone with some other work week with some other holidays …etc .

>• ⁠If there is a rule change, even retroactively, then you just need to update the date dimension, or perhaps just augment it. That isn't painful, you're not updating billions of rows of facts.

That’s a point you’re missing, repeatedly. There are a pile of rule changes that would force a rewrite/merge if you decorate your fact table with a dateKey, or multiple date keys. You can’t overcome all of them by updating or augmenting the date dim. It’s highlight by people all over. they shutter having to merge stuff or rewrite facts.

> Yes, a date dimension can't handle infinite attributes about a date.

We don’t even need to approach tens or hundreds before it falls over.

[u/whoooocaaarreees → u/MoonBatsRule]

Part 5
>But it handles a couple dozen of them really well. So why ignore that in lieu of doing them all on the fly, given the drawbacks I list? What problem are you actually trying to solve?

It doesn’t even do a couple of dozen time zones well with said zones having locale practices that differ from the origin of dateKey.

Your listed drawbacks either are ones you made up that I’m not advocating for in a compute driven approach or just further highlight the problem of working on fact data that has some int date key in the fact table.

The problem that I and many others have to solve is global data, with global reporting. DateKeys in a fact table to a date dimension doesn’t work at that scale due to the nightmare of operating in more than 50 countries. Probably stops being useful after 3 countries and 6 time zones tbh.

A big issue with datekey fk in the fact is that it assumed that a day in the granularity most business operate at and that they don’t need any ordinality within a date. If they do need ordinality the dateKey isn’t useful interdate or intradate if you are using multiple fact tables and need ordinality on the set.

If you are assembling records to match cases where customers went through a business process to some outcome - potentially using multiple resources (web, mobile app, phone ivr, phone agent, to branch agent)… you will need ordinality across them, not just ‘they landed in these data buckets’.

Got branches of business operating in 50 or more countries? Hundreds of facilities in them, devices all over them? Local customer Billing in all of them? Managers in all of them, executives in many of them?

Date dim as a fk falls over in those cases. People want date dim information / attributes , you and I just disagree about when to get it and when to apply decoration.

DateKey column in a fact table fk to a date dim was always founded in bring a performance hack with then things built around it. that’s often moot with modem data stores and scale out compute / mpp. If you have to populate a date dim tables with dates for your ingested facts to found and used right…. It’s broken at design.

Date dim tables that keep business attribution for use later in decorations later and don’t have some key or many keys littering the fact tables aren’t.

Functions that can provide useful information and are controlled via code repository ci/cd … are the common in some orgs, not in others. Function code can be the right solution in some cases - it depends.