Introduction

A time spine entity is a list of dates, that has relations to date columns of other entities.

The main use cases for a time spine are:

  • Filling in missing data - for example when counting new users by day, a day without users should be counted as 0. But without a time spine, if counting on the new users table on a missing date, then it will just not appear.

  • Providing a common time for different metrics - most metrics are based on time. When metrics are on different entities how to count them to the same day? Connect those entities to a time spine, and count them to the date in the spine.

Building a time spine

The time spine is an entity. It can be a table in the database or on a custom SQL query, such as:

-- Snowflake SQL for a per-day time spine spanning 2020-2030
SELECT
    DATEADD(DAY, row_number() over (order by seq), '2020-01-01'::date)::DATE AS DATE,
    DAYOFMONTH(DATE) as DAY_OF_MONTH,
    TO_VARCHAR(DATE, 'dy') as DAY_OF_WEEK,
    DAYOFWEEK(DATE) as DAY_OF_WEEK_INDEX,
    DAYOFYEAR(DATE) as DAY_OF_YEAR,
    DATE_TRUNC('month', DATE) as MONTH,
    MONTHNAME(DATE) as MONTH_NAME,
    MONTH(DATE) as MONTH_NUM,
    DATE_TRUNC('quarter', DATE) as QUARTER,
    'Q' || QUARTER(DATE) as QUARTER_OF_YEAR,
    DATE_TRUNC('week', DATE) as WEEK,
    WEEKOFYEAR(DATE) as WEEK_OF_YEAR,
    YEAR(DATE) as YEAR,
FROM (SELECT SEQ4() as seq FROM TABLE(GENERATOR(ROWCOUNT=>365*10)))

The Honeydew convention for a per-day time spine is to call it date.

  1. Time entity should be marked as such through the is_time_spine metadata tag in its yaml.
  2. Multiple time spines are not supported.

Always prefer creating time spines as tables and not as custom SQL entities.

Snowflake does not cache queries that use table generators. Using a time spine as custom SQL without materialization will disable caching for queries that involves it.

The time spine should have the granularity column as key - in a per-day spine, the date is the key. Make sure to set the timegrain attribute for the granularity column, to enforce that granularity.