Nearly every data warehouse needs a calendar dimension, and some need a time dimension too. Time at the Lakehouse provides a set of SQL files that builds both dimensions in Databricks, documented for humans and AI agents. Each dimension is a single set-based query, with no recursive CTEs, loops, or UDFs. That makes them easy to read, easy to change, and fast to run.
Background
I published the predecessor in early 2022 while at BlueGranite, as the calendar-dimension-spark repository and a companion post, Generate a Calendar Dimension in Spark. That version was built for Databricks as it was then: a PySpark notebook, the Hive metastore, and mounted storage.
Time at the Lakehouse is the maintained successor. It has been rewritten for current Databricks: Unity Catalog managed tables, Lakeflow Jobs, and Lakeflow Spark Declarative Pipelines.
What you get
| Simple and fast | One query per dimension: sequence generates the rows, and built-in functions derive the columns. No recursive CTEs, loops, or UDFs. |
| Pure SQL | No Python, Scala, or libraries to install. Runs on a serverless SQL warehouse. |
| Calendar and time | Both dimensions, kept separate so each stays small. |
| Rolling date range | Defaults to the last five years through today, and refreshes daily. |
| Unity Catalog native | Managed tables with cluster by auto, a table comment, and a comment on every column. |
| Ready for Genie | Every table and column description is written by hand, so Genie and other agents know which fiscal year, week numbering, and flag values each column holds. |
| Informational primary keys | Declared with RELY, so the optimizer and metric views can use them. |
| Two ways to deploy | A SQL file task in Lakeflow Jobs (or the SQL editor), or a materialized view in a Lakeflow Spark Declarative Pipeline. |
| Fiscal calendars | October–September and July–June fiscal years and months included. Change them to match your organization. |
The calendar dimension
The calendar dimension has one row per day. The whole thing is a single query. sequence builds an array of dates, explode turns it into rows, and built-in date functions derive the rest:
with dates as (
select explode(sequence(current_date() - interval 5 years, current_date(), interval 1 day)) as calendar_date
)
select
year(calendar_date) * 10000 + month(calendar_date) * 100 + day(calendar_date) as date_int,
calendar_date,
year(calendar_date) as calendar_year,
date_format(calendar_date, 'MMMM') as calendar_month,
month(calendar_date) as month_of_year,
date_format(calendar_date, 'EEEE') as calendar_day,
dayofweek(calendar_date) as day_of_week,
weekday(calendar_date) + 1 as day_of_week_start_monday,
case when weekday(calendar_date) < 5 then 'Y' else 'N' end as is_week_day,
dayofmonth(calendar_date) as day_of_month,
case when calendar_date = last_day(calendar_date) then 'Y' else 'N' end as is_last_day_of_month,
dayofyear(calendar_date) as day_of_year,
weekofyear(calendar_date) as week_of_year_iso,
quarter(calendar_date) as quarter_of_year,
case when month(calendar_date) >= 10 then year(calendar_date) + 1 else year(calendar_date) end as fiscal_year_oct_to_sep,
(month(calendar_date) + 2) % 12 + 1 as fiscal_month_oct_to_sep,
case when month(calendar_date) >= 7 then year(calendar_date) + 1 else year(calendar_date) end as fiscal_year_jul_to_jun,
(month(calendar_date) + 5) % 12 + 1 as fiscal_month_jul_to_jun
from dates;
Here are a few rows across a month end and the start of an October fiscal year:
| date_int | calendar_date | calendar_day | is_week_day | is_last_day_of_month | week_of_year_iso | quarter_of_year | fiscal_year_oct_to_sep | fiscal_month_oct_to_sep |
|---|---|---|---|---|---|---|---|---|
| 20260929 | 2026-09-29 | Tuesday | Y | N | 40 | 3 | 2026 | 12 |
| 20260930 | 2026-09-30 | Wednesday | Y | Y | 40 | 3 | 2026 | 12 |
| 20261001 | 2026-10-01 | Thursday | Y | N | 40 | 4 | 2027 | 1 |
| 20261003 | 2026-10-03 | Saturday | N | N | 40 | 4 | 2027 | 1 |
A few design choices worth explaining:
date_intas the key. An integer inyyyyMMddform is compact and fast to join on. It’s also readable, so20261001needs no lookup. Fact tables can store it directly.- Y/N flags instead of booleans. They read better as slicers and filters in BI tools. Switch to
booleanif you prefer. - Two day-of-week columns.
day_of_weekstarts on Sunday, which matches Spark’sdayofweek.day_of_week_start_mondayis there for the ISO convention. - Fiscal months by modular arithmetic.
(month + 2) % 12 + 1maps October to 1 and September to 12, with no lookup table or longcaseexpression. For a fiscal year starting in month s, the offset is12 - s.
The jobs version creates the table once, with types, not null constraints, and comments, then reloads it with insert overwrite. So the table definition, comments, and grants survive every refresh:
create table if not exists dim_calendar (
date_int int not null comment 'Integer representation of the date (yyyyMMdd)',
calendar_date date not null comment 'Calendar date',
-- ...
primary key (date_int) rely
)
comment 'Calendar dimension'
cluster by auto
tblproperties ('quality' = 'gold');
The pipeline version declares the same comments in the materialized view definition, so they’re reapplied on every refresh.
Context for Genie and agentic analytics
The table and column comments do more than document the tables. They give Genie, and any other agent that reads Unity Catalog, the context it needs to write correct SQL. Databricks’ Genie best practices say it directly: “Quality table and column descriptions in Unity Catalog are critical for Genie accuracy.”
A calendar dimension is where missing context causes wrong answers. Ask “What were sales in Q1 of fiscal 2027?” and an agent has several decisions to make:
- Which fiscal year? The comment on
fiscal_year_oct_to_sepsays the year runs from October to September. That means Q1 of fiscal 2027 is October through December 2026, orfiscal_month_oct_to_sep1–3. - Which week numbering?
week_of_year_isois labeled as the ISO week, not a Sunday-start week. - Which day numbering?
day_of_weekstarts on Sunday.day_of_week_start_mondaystarts on Monday. - What’s in the flags?
is_week_dayholdsYorN, and the comment defines a weekday as any day other than Saturday or Sunday.
Without the comments, an agent has to guess from column names. A wrong guess still returns a plausible number, and plausible wrong numbers are the hardest kind to catch.
Because the comments live in Unity Catalog rather than in a single Genie Agent, every Genie experience sees the same definitions. That includes Genie Agents, Genie One, and Genie Code, as well as your own agents that read table metadata. Since the calendar and time dimensions join to nearly every fact table, adding the comments here improves answers across your whole model.
The primary keys help with modeling too. If you build Unity Catalog metric views over a star schema, Databricks recommends primary and foreign key constraints with RELY on the underlying tables.
The time dimension
The time dimension has one row per minute of the day, which comes to 1,440 rows. It uses the same sequence and explode pattern over timestamps:
with times as (
select explode(sequence(to_timestamp('1900-01-01 00:00'), to_timestamp('1900-01-01 23:59'), interval 1 minute)) as time
)
select
cast(date_format(time, 'HHmm') as int) as id,
date_format(time, 'hh:mm a') as time,
date_format(time, 'hh') as hour,
date_format(time, 'HH:mm') as time_24,
date_format(time, 'HH') as hour_24,
date_format(time, 'a') as am_pm
from times;
| id | time | hour | time_24 | hour_24 | am_pm |
|---|---|---|---|---|---|
| 0 | 12:00 AM | 12 | 00:00 | 00 | AM |
| 1200 | 12:00 PM | 12 | 12:00 | 12 | PM |
| 1330 | 01:30 PM | 01 | 13:30 | 13 | PM |
| 2359 | 11:59 PM | 11 | 23:59 | 23 | PM |
Why keep time separate from the calendar? A combined date-and-minute dimension has 525,600 rows for each year. Over 20 years that is more than 10 million rows, most of which never join to anything. Two small dimensions answer the same questions, such as “orders by hour on weekdays in Q4”, at a fraction of the size. The time dimension never changes, so it only needs to run once.
Using the dimensions
Derive the keys from a timestamp when you load your fact tables:
select
cast(date_format(order_ts, 'yyyyMMdd') as int) as order_date_int,
cast(date_format(order_ts, 'HHmm') as int) as order_time_id,
...
from raw_orders;
Then join as usual:
select
c.fiscal_year_oct_to_sep,
c.fiscal_month_oct_to_sep,
t.hour_24,
count(*) as orders
from fact_orders o
join dim_calendar c on c.date_int = o.order_date_int
join dim_time t on t.id = o.order_time_id
where c.is_week_day = 'Y'
group by all;
Deploying it
Lakeflow Jobs. Copy the files in jobs/ into your project. Create a job with a SQL file task for each file, running on a serverless SQL warehouse, and set the catalog and schema parameters. Schedule dim_calendar daily. dim_time only needs to run once. You can also open either file in the SQL editor and run it interactively.
Lakeflow Spark Declarative Pipelines. Add the files in pipeline/transformations/ to a pipeline (serverless recommended) and set its default catalog and schema. Each file defines a materialized view. Schedule the pipeline to refresh daily.
Use whichever matches how the rest of your project runs. The columns are the same in both.
Customizing
The files are meant to be copied and changed. Common changes:
- Extend the range into the future for budgets, forecasts, or due dates:
sequence(current_date() - interval 5 years, current_date() + interval 2 years, interval 1 day). A fact row whose date falls outside the range won’t join, so cover every date your facts can hold. - Use a fixed start date if your history goes back further than five years:
sequence(date'2010-01-01', current_date(), interval 1 day). - Add an ISO year if you report by ISO week. Near January 1,
week_of_year_isocan belong to the previous or next year.extract(YEAROFWEEK from calendar_date)gives the year that matches. - Add holidays by joining to a holiday table you maintain. Holidays vary too much by country and company to hardcode.
- Swap fiscal calendars. Replace the two included fiscal year patterns with your own. In the jobs version, keep the
create tableandinsertcolumn lists in sync.
Get it
Everything is at github.com/cjkoester/time_at_the_lakehouse under the MIT license. If it saves you some time, a star helps other people find it. If something doesn’t fit your requirements, open an issue.
Questions or comments? Join the discussion on GitHub.