How To Easily Create A SQL Date Dimension Table

Use Generate Sequence To Programmatically Create A Date Dimension Table

Photo by Nick Hillier on Unsplash

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.

1. They can build formulas off of the date field already in the data.
2. 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.