The Date table

• By
• Chris Burgner

  Chris Burgner

  12+ years of experience, most recently as a Director of Finance at Intercom.

  

The Clean ARR table returns a single record for each invoice or subscription object, depending on which you use to generate your build. However, the end goal of the ARR Build is to pull ARR across the entire customer base at any point in time, not just when an invoice or a subscription was created. In order to make this possible, the table needs to expand from a single object record to a record for every day that object was active.

This process is called padding and requires creating a Date table in SQL that the Clean ARR table can join to. Most data warehouses have functions that allow this. Adding a few other dimensions to the date table, such as month_ending or month_beginning fields, is helpful in making reporting easier.

Here’s an example of how we build these in Snowflake:

SELECT
  dateadd(
    day,
    ‘-’ || row_number() over (order by null),
    dateadd(day, ‘+1’, ‘2100-12-31’)
  )::DATE as date
  ,DATE_TRUNC(‘week’,date)::DATE AS week
  ,DATE_TRUNC(‘month’,date)::DATE AS month
  ,CASE WHEN date = DATE_TRUNC(‘week’,date)::DATE THEN TRUE ELSE FALSE END AS week_beginning
  ,CASE WHEN date = (DATE_TRUNC(‘week’,date)::DATE + 6) THEN TRUE ELSE FALSE END AS week_ending
  ,CASE WHEN date = DATE_TRUNC(‘month’,date)::DATE THEN TRUE ELSE FALSE END AS month_beginning
  ,EXTRACT(MONTH FROM date) <> EXTRACT(MONTH FROM (date + INTERVAL ‘1 day’)) AS month_ending
FROM TABLE (generator(rowcount => 365.25 * (2100 - 2020)))
ORDER BY 1

And the corresponding output: