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_int as the key. An integer in yyyyMMdd form is compact and fast to join on. It’s also readable, so 20261001 needs 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 boolean if you prefer.
  • Two day-of-week columns. day_of_week starts on Sunday, which matches Spark’s dayofweek. day_of_week_start_monday is there for the ISO convention.
  • Fiscal months by modular arithmetic. (month + 2) % 12 + 1 maps October to 1 and September to 12, with no lookup table or long case expression. For a fiscal year starting in month s, the offset is 12 - 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_sep says the year runs from October to September. That means Q1 of fiscal 2027 is October through December 2026, or fiscal_month_oct_to_sep 1–3.
  • Which week numbering? week_of_year_iso is labeled as the ISO week, not a Sunday-start week.
  • Which day numbering? day_of_week starts on Sunday. day_of_week_start_monday starts on Monday.
  • What’s in the flags? is_week_day holds Y or N, 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_iso can 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 table and insert column 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.