DateKey

Datekey is a numeric surrogate key used to identify a specific date in a date dimension within data warehousing and business intelligence systems. It serves as the primary key for the date dimension and is used to join fact tables to date-related attributes, enabling efficient time-based analysis. The most common representation is an eight-digit integer in the form YYYYMMDD, such as 20240315 for March 15, 2024. Some implementations use a sequential day number since a base date or other encodings; the key itself is not meant to be read as a calendar date in isolation but as a stable identifier.

In the date dimension table, the DateKey is paired with attributes that describe the calendar date, including the actual date value, year, quarter, month, day, day of week, and flags such as holidays. This enables powerful hierarchical analytics and easy aggregation by year, quarter, month, or day.

In practice, the DateKey is generated during ETL by transforming a source date into the key format and loading it into the date dimension. Fact tables store DateKey fields that reference the corresponding row in the date dimension, decoupling fact data from calendar logic and supporting stable, slow-changing dimensions.

Advantages of using a DateKey include faster joins, improved query performance, consistent time-based hierarchies, and easier data maintenance. Variants exist, with some teams calling it DayKey or DateId, and with different base-date encodings. Pitfalls include the need to convert the key back to a real date when necessary and ensuring numeric formats remain consistent across systems. The concept is central to dimensional modeling and widely used in star schemas.