Date and time-of-day should always be split into two separate dimension tables. Combining them into a single date-time dimension results in a massive row explosion that ruins dimension performance, whereas separating them keeps dimension tables small, fast, and reusable.
Why combining date and time explodes rows
A date dimension covering twenty years contains roughly 7,300 rows. If you attempt to combine date and time down to the second, the dimension balloons to over 630 million rows:
Separate dimensions: dim_date: 7,305 rows (20 years x 365.25 days) dim_time_of_day: 86,400 rows (24 hours x 60 mins x 60 secs) Combined dimension: dim_timestamp: 631,152,000 rows (unusable dimension size)
A review of the storage impact:
- A 630-million-row dimension loses all caching benefits.
- Table scans consume massive memory during join operations.
- Simple date filtering becomes slow and resource-intensive.
Role-playing date dimensions
The separate dim_date table carries rich calendar logic including fiscal quarters, trading holidays, day-of-week indicators, and seasonal markers. Fact tables reference dim_date multiple times using role-playing foreign keys: order_date_key, ship_date_key, and delivery_date_key.
Handling high-precision timestamps
For time-of-day analytics like peak ordering hours or call center staffing, use dim_time_of_day with 1,440 minute-level or 86,400 second-level rows containing hour, minute, AM/PM, and shift indicators. In the fact table, store date_key, time_key, and keep the raw UTC event_timestamp for millisecond-level precision and technical event sequencing.