How to create a date dimension in a Data Warehouse

Almost every BI report filters or groups by time — by year, by month, by quarter. Having a date dimension (a calendar table) in your Data Warehouse is the cleanest and fastest way to do it, instead of computing dates over and over in every query. Below you'll see, step by step, how to create a date dimension in SQL and populate it with a simple, reusable script.

Prerequisites

• A SQL Server or Azure SQL database (the concept applies to any SQL-based Data Warehouse).
• Permissions to create schemas and tables (CREATE TABLE).
• Basic SQL knowledge (CREATE, INSERT, SELECT).

Step 1: Understand what a date dimension is

A date dimension has one row per day and several descriptive columns: year, month, month name, quarter, day of week and a weekend flag. The table key is usually an integer in YYYYMMDD format (for example, 20260704), called a surrogate key. It's small, sortable and easy to join to your fact tables.

Step 2: Create the table

We start by creating a dim schema (optional, but it keeps things tidy) and the table that will hold the calendar. Give each column the smallest type that fits — a simple detail that saves space in a table with thousands of rows.

CREATE SCHEMA dim;
GO

CREATE TABLE dim.Data (
    DataKey     int         NOT NULL PRIMARY KEY,
    Data        date        NOT NULL,
    Ano         smallint    NOT NULL,
    Trimestre   tinyint     NOT NULL,
    Mes         tinyint     NOT NULL,
    Dia         tinyint     NOT NULL,
    NomeMes     varchar(20) NOT NULL,
    DiaSemana   tinyint     NOT NULL,
    NomeDia     varchar(20) NOT NULL,
    FimDeSemana bit         NOT NULL
);

Step 3: Generate the dates with a recursive CTE

To populate the table we need one row per day between two dates. A recursive CTE generates that sequence without any helper tables. Set the range (for example, 2015 to 2035) and let SQL count the days one by one.

DECLARE @Inicio date = '2015-01-01';
DECLARE @Fim    date = '2035-12-31';

WITH Calendario AS (
    SELECT @Inicio AS Data
    UNION ALL
    SELECT DATEADD(DAY, 1, Data)
    FROM Calendario
    WHERE Data < @Fim
)
SELECT Data FROM Calendario
OPTION (MAXRECURSION 0);

Common error: by default, SQL Server stops a recursive CTE at 100 levels and returns the "maximum recursion" error. The OPTION (MAXRECURSION 0) clause removes that limit.

Step 4: Insert the data with calculated columns

Now we combine the CTE with date functions to fill every column. Note two best practices: the DataKey key is built with style 112 (AAAAMMDD format), and the day of week is calculated with a formula that does not depend on the server's language settings (@@DATEFIRST), always returning Monday = 1.

SET LANGUAGE Portuguese;

DECLARE @Inicio date = '2015-01-01';
DECLARE @Fim    date = '2035-12-31';

WITH Calendario AS (
    SELECT @Inicio AS Data
    UNION ALL
    SELECT DATEADD(DAY, 1, Data)
    FROM Calendario
    WHERE Data < @Fim
)
INSERT INTO dim.Data
    (DataKey, Data, Ano, Trimestre, Mes, Dia, NomeMes, DiaSemana, NomeDia, FimDeSemana)
SELECT
    CAST(CONVERT(char(8), Data, 112) AS int)              AS DataKey,
    Data,
    YEAR(Data)                                            AS Ano,
    DATEPART(QUARTER, Data)                               AS Trimestre,
    MONTH(Data)                                           AS Mes,
    DAY(Data)                                             AS Dia,
    DATENAME(MONTH, Data)                                 AS NomeMes,
    ((@@DATEFIRST + DATEPART(WEEKDAY, Data) - 2) % 7) + 1 AS DiaSemana,
    DATENAME(WEEKDAY, Data)                               AS NomeDia,
    CASE WHEN ((@@DATEFIRST + DATEPART(WEEKDAY, Data) - 2) % 7) + 1 >= 6
         THEN 1 ELSE 0 END                                AS FimDeSemana
FROM Calendario
OPTION (MAXRECURSION 0);

The SET LANGUAGE Portuguese; statement makes DATENAME return the names in Portuguese ("janeiro", "segunda-feira"). If you prefer another language, just change that one line.

Verify the result

Confirm the load worked with two quick queries: one to see the first rows and another to count the days and detect duplicates.

SELECT TOP 10 * FROM dim.Data ORDER BY DataKey;

SELECT COUNT(*)              AS TotalDias,
       COUNT(DISTINCT DataKey) AS ChavesUnicas,
       MIN(Data)               AS Inicio,
       MAX(Data)               AS Fim
FROM dim.Data;

If TotalDias equals ChavesUnicas, there are no duplicates. For the 2015–2035 range you should get 7,670 rows.

Conclusion

With a date dimension in place, your time measures (year-to-date, year-over-year comparisons, quarter filters) become much simpler and faster. The next step is to join the DataKey column to your fact tables and, if you use Power BI, mark this table as a "date table" to enable time intelligence. Which other attributes make sense for your business — holidays, fiscal week, peak season?