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.

The time granularity of the time spine in Honeydew is by day as a default.

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