Calendar Tables (date spine)

Modeling
1

You might have heard of a “calendar table” or “date spine”. It’s a technique that starts with a contiguous time series and can be useful in a few scenarios:

  • Unifying multiple timestamps on the same common x-axis
  • Sparse Data (though you can fill in missing rows in Omni)
  • Creating rows between a start date and an end date allowing for various measures and operations

image
image3014×1522 434 KB

in this example, we are joining opportunities twice to a calendar table, on opened and closed dates, to allow for open pipeline rate analysis, an example of the first useful scenario for calendar tables

Snowflake

Using a recursive CTE pattern

Create a view:

# Reference this view as calendar
sql: |
  WITH RECURSIVE rec_cte AS (
      /* start date */
      SELECT {{ filters.calendar.start_date.range_start }} as DATE
      UNION ALL
      SELECT DATEADD('day',1,DATE) as DATE
      FROM rec_cte
      /* end date (inclusive) */
      WHERE DATE < {{ filters.calendar.end_date.range_end }}
  )
  SELECT DATE FROM rec_cte

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch,
        time_of_day
      ]

# optional if you'd like your users to choose the range. Rational to hardcode a wide range instead
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

In the topic file (this is the same for all dialects):

This shows how two disparate time series could be compared / unified on the calendar table axis. For a between two effective dates pattern, adjust the on_sql parameter to join on all dates between the start and end, this will yield a row for each day in the effective range.

# this is the calendar topic
# optional if you allowed the users to select a range, best to fill it with defaults:
default_filters:
  calendar.end_date:
    is: 2029-12-31
  calendar.start_date:
    is: 2020-01-01

joins:
  opened_opportunities: {}
  closed_opportunities: {}

relationships:
#join the same table more than once, but upon different timestamps, giving different meanings to its measures
  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: opened_opportunities
    join_type: always_left
    # joining on opened date
    on_sql: ${calendar.date[date]} = ${opportunity.opened_date[date]}
    relationship_type: one_to_many

  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: closed_opportunities
    join_type: always_left
    # join on closed date
    on_sql: ${calendar.date[date]} = ${opportunity.closed_date[date]}
    relationship_type: one_to_many

Alternatively, to join between effective dates would look like this in your topic:

# this is the calendar_between topic
base_view: calendar

default_filters:
  calendar.end_date:
    is: 2029-12-31
  calendar.start_date:
    is: 2020-01-01

joins:
  open_opportunities: {}

relationships:
  - join_from_view: calendar
    join_to_view: opportunity
    join_to_view_as: open_opportunities
    join_type: always_left
    on_sql: |
      ${calendar.date[date]} >= ${opportunity.opened_date[date]}
      AND ${calendar.date[date]} <= ${opportunity.closed_date[date]}
    relationship_type: one_to_many

This allows us a positive row for each day that an opportunity was open, and answers the question “how many opps were open on X day”

image
image1284×574 69.4 KB

And with a count distinct we can easily measure that:
image
image2314×1158 190 KB

BigQuery

Using a generate / unnest pattern

# Reference this view as calendar
sql: |
  SELECT
    date
  FROM
    UNNEST(GENERATE_DATE_ARRAY({{ filters.calendar.start_date.range_start }}, {{ filters.calendar.end_date.range_end }})) AS date

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

MySQL

Using a recursive CTE pattern

# Reference this view as calendar
sql: | 
  WITH RECURSIVE date_range AS (
      SELECT {{ filters.calendar.start_date.range_start }} AS date
      UNION ALL
      SELECT DATE_ADD(date, INTERVAL 1 DAY)
      FROM date_range
      WHERE date < {{ filters.calendar.end_date.range_end }}
  )
  SELECT date from date_range

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

Postgres

Using a generate series function

# Reference this view as calendar
sql: |
    SELECT
        generate_series(
            {{ filters.calendar.start_date.range_start }},
            {{ filters.calendar.end_date.range_end }},
            INTERVAL '1 day'
        )::DATE AS date

dimensions:
  is_ytd:
    sql: |
      EXTRACT(dayofyear FROM ${calendar.date}) <= EXTRACT(dayofyear FROM CURRENT_DATE)
    group_label: Date

  date:
    sql: '"DATE"'
    timeframes:
      [
        year,
        quarter,
        month,
        week,
        date,
        quarter_of_year,
        week_of_year,
        day_of_week_name,
        day_of_week_num,
        month_name,
        month_num,
        day_of_month,
        day_of_year,
        day_of_quarter,
        fiscal_year,
        fiscal_quarter,
        epoch
      ]

#optional if you'd like your users to be able to select the timeframe. Hardcoding a wide range by changing the above
# variable references to dates is a reasonable design decision
filters:
  start_date:
    type: timestamp
    description: Date starting the contiguous range

  end_date:
    type: timestamp
    description: Date ending the contiguous range

What if my dialect isn’t listed?

Connor wrote an excellent article which includes calendar table generation code for more databases, you can find here link.
Also AI is excellent at generating these simple calendar table queries, ask ChatGPT, Claude or your favorite foundational model to create a calendar table, but ask it not to use temp tables or stored procedures and you’ll end up with a sql statement like the above, which you can place the optional filter ranges within.