> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Implementing data snapshots

> Build point-in-time snapshots from change-history data so you can report the latest status of an entity as of any date.

## Use case

For a dataset that contains a sequence of changes to a property over time, we
want to be able to get the most recent state of said property at any given date.
In this recipe, we'll learn how to calculate snapshots of statuses at any given
date for a cube with `Product Id`, `Status`, and `Changed At` dimensions.

<Info>
  We can consider the status property to be a
  [slowly changing dimension](https://en.wikipedia.org/wiki/Slowly_changing_dimension)
  (SCD) of type 2. Modeling data with slowly changing dimensions is an essential
  part of the data engineering skillset.
</Info>

## Data modeling

Let's explore the `statuses` cube that contains data like this:

| order\_id | status     | changed\_at         |
| --------: | ---------- | ------------------- |
|         1 | shipped    | 2019-01-19 00:00:00 |
|         1 | processing | 2019-03-14 00:00:00 |
|         1 | completed  | 2019-01-25 00:00:00 |
|         2 | processing | 2019-08-21 00:00:00 |
|         2 | completed  | 2019-04-13 00:00:00 |
|         2 | shipped    | 2019-03-18 00:00:00 |

We can see that statuses change occasionally. How do we count orders that
remained in the `shipped` status at a particular date?

First, we need to generate a range with all dates of interest, from the earliest
to the latest. Second, we need to join the dates with the statuses and leave
only the most recent statuses to date.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: status_snapshots
      extends: statuses
      sql: |
        -- Create a range from the earlist date to the latest date
        WITH range AS (
          SELECT date
          FROM GENERATE_SERIES(
            (SELECT MIN(changed_at) FROM {statuses.sql()} AS statuses),
            (SELECT MAX(changed_at) FROM {statuses.sql()} AS statuses),
            INTERVAL '1 DAY'
          ) AS date
        )

        -- Calculate snapshots for every date in the range
        SELECT range.date, statuses.*
        FROM range
        LEFT JOIN {statuses.sql()} AS statuses
          ON range.date >= statuses.changed_at
          AND statuses.changed_at = (
            SELECT MAX(changed_at)
            FROM {statuses.sql()} AS sub_statuses
            WHERE sub_statuses.order_id = statuses.order_id
          )
      dimensions:
        - name: date
          sql: date
          type: time
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`status_snapshots`, {
    extends: statuses,

    sql: `
      -- Create a range from the earlist date to the latest date
      WITH range AS (
        SELECT date
        FROM GENERATE_SERIES(
          (SELECT MIN(changed_at) FROM ${statuses.sql()} AS statuses),
          (SELECT MAX(changed_at) FROM ${statuses.sql()} AS statuses),
          INTERVAL '1 DAY'
        ) AS date
      )

      -- Calculate snapshots for every date in the range
      SELECT range.date, statuses.*
      FROM range
      LEFT JOIN ${statuses.sql()} AS statuses
        ON range.date >= statuses.changed_at
        AND statuses.changed_at = (
          SELECT MAX(changed_at)
          FROM ${statuses.sql()} AS sub_statuses
          WHERE sub_statuses.order_id = statuses.order_id
        )
    `,

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    }
  })
  ```
</CodeGroup>

<Info>
  To generate a range of dates, here we use the
  [`GENERATE_SERIES` function](https://www.postgresql.org/docs/9.1/functions-srf.html)
  which is Postgres-specific. Other databases have similar functions, e.g.,
  [`GENERATE_DATE_ARRAY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_date_array)
  in BigQuery.
</Info>

Please note that it makes sense to make the `status_snapshots` cube
[extend](/reference/data-modeling/cube#extends) the original `statuses`
cube in order to reuse the dimension definitions. We only need to add a new
dimension that indicates the `date` of a snapshot. We're also referencing the
definition of the `statuses` cube with the
[`sql()` property](/reference/data-modeling/cube#sql).

## Result

To count orders that remained in the `shipped` status at a particular date,
filter on both `status_snapshots.date` and `status_snapshots.status`. Running
the same query for two different dates shows how the snapshot changes over time:

| date       | status  | count |
| ---------- | ------- | ----: |
| 2019-04-01 | shipped |    16 |
| 2019-05-01 | shipped |    25 |