The Ultimate Guide to ARR book icon

The Ultimate
Guide to
ARR

The Ultimate Guide to ARR book icon

The Ultimate
Guide to
ARR

Table of Contents

Track and grow your ARR

Unlock CFO-grade clarity into your SaaS metrics with Equals.

The Date table

  • By
  • Headshot of Chris Burgner Chris Burgner

    Headshot of 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 + INTERVAL1 day)) AS month_ending
FROM TABLE (generator(rowcount => 365.25 * (2100 - 2020)))
ORDER BY 1
Example SQL to create the date table when building ARR.

And the corresponding output:

An example table with output for a date table when ARR reporting.
The Date table joined with the ARR tables lets you pull ARR for any date

Next topic

Padded Table