How To Easily Create A SQL Date Dimension Table
Use Generate Sequence To Programmatically Create A Date Dimension Table
If you’d like to support my writing, consider buying a copy of my E-Book, JetPack SQL. It’s a comprehensive, 70+ page PDF for absolute beginners. Click to learn more: https://stan.store/datawithjon/p/elevate-your-sql-skills-elevate-your-career
A date dimension table includes dimensions like day_of_week and day_of_month for each date. Business reports frequently need these utility columns for storytelling. For example, an analysis might need to compare weekend metrics against weekday metrics. In this situation, a day_of_week column is necessary.
Date dimension tables are usually very wide and include every dimension that may be valuable. This can easily equal 20 or more columns.
When a data pull requires dimensions of this nature, the data engineer or data analyst has two options.
- They can build formulas off of the date field already in the data.
- They can join a date dimension to the data using the date field.
Option 2, of course, is much more efficient than having to write date formulas for 20+ dimensions for every data pull request.