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:

  • Customer 360

  • 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 page, the Data Explorer, and the Queries page.

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 page.

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 versions is 10. A version must have been created within the last 14 days.

Caution

Changes to columns within a table that is enabled for table versioning changes the schema and will cause incompatible versions to be dropped. To avoid this, add columns to the end of the table.

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 within the table will cause incompatible versions to be dropped.

    Enforce static table schemas when using table versioning. This will help avoid schema changes that cause incompatible versions to be dropped.

  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 creation date

Use created_at to return a version at its time of creation.

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 created_at >= DATE_TRUNC('day', current_timestamp - interval '1' day)
    ORDER BY created_at ASC
    LIMIT 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
 ...
------------------- ------------------------ -------- --------------------------