In this post we are going to cover the date dimension, and answer a question that comes up in almost every data warehouse interview: why build a whole table of dates when every database already has date functions?
A date dimension is a table with one row for every calendar day. Each row has a key like 20260105 and a set of attributes that are worked out once, in advance: the day name, weekday or weekend, month, quarter, year, the company's fiscal year and any holiday. Fact tables store the date key, so a report can slice sales by any of those attributes with a simple join.
We will build a date dimension for 2026 with SQL, add a company holiday, and then use it to answer two report questions without writing a single date function in the report.
Question 20 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A date dimension has one row per calendar day, with a key like 20260105 and pre-calculated attributes: day name, month, quarter, year, fiscal periods, weekend and holiday flags. Facts join to it, so reports can slice by any calendar attribute without date functions, and everyone uses the same company calendar.
Why not just use date functions?
Date functions can tell you the month or the day of the week, but they cannot tell you anything about your company's calendar. The fiscal year, the retail weeks, the holidays and what counts as a weekend are business rules, and they have to be written down somewhere.
- Company calendars - fiscal years, retail weeks and holidays are not in any date function.
- One definition - every report uses the same weekend, quarter and fiscal year.
- Simple for users - pick a column instead of writing a date expression.
- Hides dialect differences - date logic is written once, in the load, not in every report.
Let's create some sample data
We need two tables: an empty dim_date that we will fill with SQL, and a small fact_sales table with six sales in January 2026. Each sale points to its day through date_key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- smart key: YYYYMMDD
full_date DATE,
day_name VARCHAR(10),
day_type VARCHAR(10), -- Weekday / Weekend
month_name VARCHAR(10),
quarter INT,
year INT,
fiscal_year VARCHAR(6), -- TB Shop's fiscal year starts on April 1
holiday VARCHAR(30)
);
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, date_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
(1, 20260101, 40.00), (2, 20260102, 35.00), (3, 20260103, 60.00),
(4, 20260105, 25.00), (5, 20260110, 80.00), (6, 20260112, 30.00);Let's look at the sales we just created.
SELECT * FROM fact_sales ORDER BY 1;| sale_id | date_key | amount |
|---|---|---|
| 1 | 20260101 | 40.00 |
| 2 | 20260102 | 35.00 |
| 3 | 20260103 | 60.00 |
| 4 | 20260105 | 25.00 |
| 5 | 20260110 | 80.00 |
| 6 | 20260112 | 30.00 |
Six sales, each with a date key in the YYYYMMDD form. The keys only mean something once the date dimension exists, so let's build it.
Step 1: Build one row per day for 2026
Now let's try generating every day of 2026 and calculating each attribute once. A recursive CTE produces the dates, one per row, and the SELECT turns each date into its key, day name, weekday or weekend flag, month, quarter, year and fiscal year. TB Shop's fiscal year starts on April 1, so April 2026 already belongs to FY2027.
WITH RECURSIVE days (d) AS (
SELECT CAST('2026-01-01' AS DATE)
UNION ALL
SELECT CAST(d + INTERVAL '1' DAY AS DATE)
FROM days WHERE d < CAST('2026-12-31' AS DATE)
)
INSERT INTO dim_date
SELECT CAST(TO_CHAR(d, 'YYYYMMDD') AS INT), d,
TO_CHAR(d, 'Dy'),
CASE WHEN EXTRACT(ISODOW FROM d) IN (6, 7)
THEN 'Weekend' ELSE 'Weekday' END,
TO_CHAR(d, 'Mon'),
EXTRACT(QUARTER FROM d), EXTRACT(YEAR FROM d),
CASE WHEN EXTRACT(MONTH FROM d) >= 4
THEN 'FY' || (EXTRACT(YEAR FROM d) + 1)
ELSE 'FY' || EXTRACT(YEAR FROM d) END,
NULL
FROM days;Now let's check the result.
SELECT date_key, day_name, day_type, month_name,
quarter, fiscal_year, holiday
FROM dim_date
WHERE date_key <= 20260107
ORDER BY date_key;| date_key | day_name | day_type | month_name | quarter | fiscal_year | holiday |
|---|---|---|---|---|---|---|
| 20260101 | Thu | Weekday | Jan | 1 | FY2026 | NULL |
| 20260102 | Fri | Weekday | Jan | 1 | FY2026 | NULL |
| 20260103 | Sat | Weekend | Jan | 1 | FY2026 | NULL |
| 20260104 | Sun | Weekend | Jan | 1 | FY2026 | NULL |
| 20260105 | Mon | Weekday | Jan | 1 | FY2026 | NULL |
| 20260106 | Tue | Weekday | Jan | 1 | FY2026 | NULL |
| 20260107 | Wed | Weekday | Jan | 1 | FY2026 | NULL |
The first week of January shows the pattern: January 3 and 4 are a Saturday and a Sunday, so they are marked Weekend, and every row in January is in FY2026. The full table has 365 rows for the year, and ten years would still be only about 3,650 rows, which is tiny for a warehouse.
The date functions in this step are PostgreSQL ones. Every database has its own, which is exactly why this logic belongs in one load instead of in every report.
Step 2: Add company holidays
Holidays are a company rule too. We mark New Year's Day on its row, so every report that asks about holidays gets the same answer.
UPDATE dim_date SET holiday = 'New Year''s Day'
WHERE date_key = 20260101;Now let's check the result.
SELECT COUNT(*) AS days_in_2026 FROM dim_date;| days_in_2026 |
|---|
| 365 |
The update touched one row: the holiday now lives on the row for 20260101. The count confirms the table still has exactly one row for each of the 365 days, so nothing was added or lost.
Step 3: Sales by weekday vs weekend
Now let's use the dimension in a report. We want total sales for weekdays and for weekends. There is no date function in this query: we join the fact to the dimension on date_key and group by day_type.
SELECT d.day_type, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY d.day_type
ORDER BY d.day_type;| day_type | sales |
|---|---|
| Weekday | 130.00 |
| Weekend | 140.00 |
Weekdays brought in 130.00 and weekends 140.00. The weekend rule was decided once, when the dimension was loaded, so every report that groups by day_type will agree with this one.
Step 4: Fiscal year without any date math
The fiscal year is the best example of why the table exists. Working it out with date functions means remembering the April rule in every query. With the dimension it is just another column.
SELECT d.fiscal_year, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY d.fiscal_year;| fiscal_year | sales |
|---|---|
| FY2026 | 270.00 |
All six January sales add up to 270.00 and fall in FY2026, because the 2026 fiscal year runs until the end of March.
Follow-up questions interviewers ask
Why a smart key like 20260105?
Readable, sorts by date, and useful for partitioning facts; it is still a fixed key for a fixed date.
Date and time in one dimension?
No: keep a separate time-of-day dimension, otherwise the table explodes.
How do you handle unknown dates?
A special row (for example key 0 or -1) so facts never carry NULL date keys.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #20 - The date dimension
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q20; SET search_path TO dwh_q20;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- smart key: YYYYMMDD
full_date DATE,
day_name VARCHAR(10),
day_type VARCHAR(10), -- Weekday / Weekend
month_name VARCHAR(10),
quarter INT,
year INT,
fiscal_year VARCHAR(6), -- TB Shop's fiscal year starts on April 1
holiday VARCHAR(30)
);
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, date_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES
(1, 20260101, 40.00), (2, 20260102, 35.00), (3, 20260103, 60.00),
(4, 20260105, 25.00), (5, 20260110, 80.00), (6, 20260112, 30.00);
-- Step 1: Build one row per day for 2026 (PostgreSQL date functions)
WITH RECURSIVE days (d) AS (
SELECT CAST('2026-01-01' AS DATE)
UNION ALL
SELECT CAST(d + INTERVAL '1' DAY AS DATE)
FROM days WHERE d < CAST('2026-12-31' AS DATE)
)
INSERT INTO dim_date
SELECT CAST(TO_CHAR(d, 'YYYYMMDD') AS INT), d,
TO_CHAR(d, 'Dy'),
CASE WHEN EXTRACT(ISODOW FROM d) IN (6, 7)
THEN 'Weekend' ELSE 'Weekday' END,
TO_CHAR(d, 'Mon'),
EXTRACT(QUARTER FROM d), EXTRACT(YEAR FROM d),
CASE WHEN EXTRACT(MONTH FROM d) >= 4
THEN 'FY' || (EXTRACT(YEAR FROM d) + 1)
ELSE 'FY' || EXTRACT(YEAR FROM d) END,
NULL
FROM days;
-- check the result
SELECT date_key, day_name, day_type, month_name,
quarter, fiscal_year, holiday
FROM dim_date
WHERE date_key <= 20260107
ORDER BY date_key;
-- Step 2: Add company holidays
UPDATE dim_date SET holiday = 'New Year''s Day'
WHERE date_key = 20260101;
-- check the result
SELECT COUNT(*) AS days_in_2026 FROM dim_date;
-- Step 3: Slice sales by any calendar attribute: weekday vs weekend
SELECT d.day_type, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY d.day_type
ORDER BY d.day_type;
-- Step 4: Fiscal year without any date math
SELECT d.fiscal_year, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY d.fiscal_year;
Key points
- One row per day with pre-calculated calendar attributes
- Holds company rules: fiscal years, holidays, weekends
- One definition for every report; no date functions in queries
- Smart key YYYYMMDD, an unknown-date row, separate time dimension
- One date table plays many roles (order, ship, delivery)
Question 21 — Transaction, snapshot and accumulating facts What are the types of fact tables?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.