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 10 years from 2016 to 2026
SELECT DATEADD(DAY, SEQ4(), '2016-01-01')::DATE AS DATE
FROM TABLE(GENERATOR(ROWCOUNT=>365*10))
WHERE DATE <= CURRENT_DATE()

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

Choose the time granularity of the time spine to your business domain - a day, a month, a second. Larger time spines (such as per second) are best built as tables in the data warehouse.

The time spine should have the granularity column as key - in a per-day spine, the date is the key.

After building the time spine entity, connect then date column in a relationship to every entity that has a time in it.

For example, every events fact table that you have can be connected based on event timestamp.

Many time spines are both a conformed dimension (shared across different facts) and have role playing (may have more than one join path from the same fact).

Metrics based on different times aligned to same time spine

Different metrics on the same event table can be counted based on different times.

For example, the visit count metrics can be counted based on visit time, while the session count can be counted based on session start time.

To achieve a question like how many visits and users were in a specific time selection can use a time spine entity with role playing - connect the time spine to the events table with two paths.

Assuming all events (session starts and visits) are coming from the same fact table.

To facilitate that make a time spine entity and add to it two relationship roles:

  • visit_time role, joining events fact table based on visit_time column.
  • session_time role, joining events fact table based on session_start_time column.

Then define the two metrics:

  • Count new visits: events.count USING (visit_time) FILTER (WHERE event_type='visit'). This metric filters the base count metric for visits only and then applies a visit_time role to know that if that metric is put on a time spine, it should join based on the visit time stamp.

  • Count new sessions: events.count USING (session_time) FILTER (WHERE event_type='session'). This metric filters the base count metric for sessions only and then applies a session_time role to know that if that metric is put on a time spine, it should join based on the session time stamp.

The following dynamic dataset would count visits and sessions per day:

Attributes -

  • time_spine.date - days Metrics -
  • events.visit_count - the metric for visits will make events facts join time spine on visit time column
  • events.session_count - the metric for visits will make events facts join time spine on session time column

Change (MoM / YoY) metrics using a time spine

In some cases would want to define a metric counting on this day, and a metric counting on the previous day.

There are few ways to do that:

  1. Build a dynamic dataset that counts a metric per day, and then on the result of the dynamic dataset apply a window function. The con of that approach is that it does not allow to build reusable metrics for growth or change, and expose them to users.

  2. Use a time spine role for to define a time offset.

The latter option is achieved with a time spine:

If you have metric defined, and want the same metric defined with an offset, then:

  1. Build the offset date as a calculated attribute. For example, dateadd('day', -1, visit_time) is yesterday time. This is defined once and reused for all metrics on the same time offset.
  2. Connect the offset attribute to the time spine with a named role (visit_time_last_day).
  3. The count metric for yesterday can be defined as events.count USING (visit_time_last_day), and then used to construct derived growth metrics (ratio between today change and yesterday total) or change metrics (the difference).