Query Historical Data

You can enable table versioning, after which you can query historical versions of any data table in any database. Access to historical data enables change data capture scenarios for the customer 360 database–“What changed yesterday?”–and provides additional ways to validate Stitch results and to measure the quality of transactional data.

How it works

The customer 360 database is a collection of tables:

  • Customer360

  • Unified_Coalesced

  • Unified_Customer

  • Merged_Customers

  • Unified_Itemized_Transactions

  • Unified_Transactions

  • etc.

It also may contain passthrough tables that bring data from domain tables to the customer 360 database:

  • Table_A

  • Table_B

  • Table_C

  • etc.

These tables are visible from within the Customer 360 tab, the Data Explorer, and the Queries tab.

A series of versioned tables is also provided, along with a table named amperity_table_versions that is a record of all tables. A versioned table can be accessed using a SQL query authored from the Queries tab.

All versioned tables are appended with _versioned. For example:

  • Customer360_versioned

  • Unified_Coalesced_versioned

  • Unified_Customer_versioned

  • Merged_Customers_versioned

  • Unified_Scores_versioned

  • Table_A_versioned

  • Table_B_versioned

  • Table_C_versioned

  • amperity_table_versions

  • etc.

Available versions

A version is created for all tables every time the customer 360 database is run, regardless of database run type (express, normal, or full). The amperity_version column is added to all versioned tables. The typical frequency at which the customer 360 database is run is “once per day”, but this is not always the case.

Important

The maximum number of days for which versions are retained is 14. For example, a table that is run two times per day will have 28 versions (two for each day), whereas a table that is run one time per day will have 14 versions (one for each day).

To enable table versioning

  1. From the Database Table editor, under Advanced Settings, expand Version History.

  2. Select Enable table version history.

    Note

    Version history allows queries to be made against older versions of this table. Adding or removing columns will break previous versions. Only enable table versioning for tables that are expected to have a static schema.

  3. Click Save.

Return versions by datetime

Use datetime to return a version that is on a specific date. For example:

SELECT *
FROM table_a_versioned AS a
JOIN amperity_table_versions AS v
ON v.version = a.amperity_version
WHERE v.datetime = 20200402

Return versions by offset

Use offset to return a version that is a position farther back in the version history equal to the value applied to the offset. An offset of 1 is the previous version, and 2 is the version before that, etc.

The following query returns the most recent version:

SELECT *
FROM table_a_versioned AS a
JOIN amperity_table_versions AS v
ON v.version = a.amperity_version
WHERE v.offset = 1

The following query returns the version 10 positions back in the version history:

SELECT *
FROM table_a_versioned AS a
JOIN amperity_table_versions AS v
ON v.version = a.amperity_version
WHERE v.offset = 10

The following query returns all records from the Customer360 table with addresses that changed between yesterday and today:

WITH Customer360_yesterday AS (
  SELECT *
  FROM Customer360_versioned
  WHERE
    amperity_version = (
      SELECT DISTINCT version
      FROM amperity_table_versions
      WHERE offset = 1
    )
)

SELECT *
FROM Customer360 AS ct
LEFT JOIN Customer360_yesterday AS cy
ON ct.amperity_id = cy.amperity_id
AND ct.addressline1 <> cy.addressline1
WHERE cy.amperity_id IS NOT NULL

View all versioned tables

The amperity_table_versions table stores a list of available versioned tables, their offset values, and the date and time at which they were created.

Use the following query to return the list of available versioned tables:

SELECT * FROM amperity_table_versions

The list of available versioned tables is similar to:

------------------- ------------------------ -------- --------------------------
 table_name          version                  offset   created_at
------------------- ------------------------ -------- --------------------------
 Customer360         qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Unified_Coalesced   qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Unified_Customer   qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Merged_Customers    qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Unified_Scores      qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Table_A             qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Table_B             qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 Table_C             qdv-20200402-1234-abcd   0        2021-04-02T12:58:54.000Z
 ...
 Customer360         qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Unified_Coalesced   qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Unified_Customer   qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Merged_Customers    qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Unified_Scores      qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Table_A             qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Table_B             qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 Table_C             qdv-20200402-1234-abcd   1        2021-04-01T08:18:23.000Z
 ...
 Customer360         qdv-20200402-1234-abcd   2        2021-03-31T10:34:33.000Z
 Unified_Coalesced   qdv-20200402-1234-abcd   2        2021-03-31T10:34:33.000Z
 ...
------------------- ------------------------ -------- --------------------------