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. For example:

  • Customer 360

  • Unified Coalesced

  • Unified Customer

  • Merged Customers

  • Unified Itemized Transactions

  • Unified Transactions

The customer 360 database also contains passthrough tables that bring domain tables to the customer 360 database. For example:

  • Domain_Table_A

  • Domain_Table_B

  • Domain_Table_C

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

For each table with versioning enabled a table is created with the same name with _versioned appended. This table has the version history for the table. A table named amperity_table_versions is created that contains a record of all versioned tables.

For example:

  • Customer360_versioned

  • Unified_Coalesced_versioned

  • Unified_Customer_versioned

  • Merged_Customers_versioned

  • Unified_Scores_versioned

  • Domain_Table_A_versioned

  • Domain_Table_B_versioned

  • Domain_Table_C_versioned

  • amperity_table_versions

Available versions

A version is created for a table configured for table versioning each time the customer 360 database is run, regardless of database run type (express, normal, or full) and whether or not updates to data in the table occurred. The amperity_version column is appended to each versioned table.

Important

The maximum number of versions is 10. A version must have been created within the last 14 days. The frequency at which the customer 360 database is run will affect the total number of versions. For example:

  • If the customer 360 database runs once per day, over a 14 days there will be a rolling count of 10 versions.

  • If the customer 360 database runs every other day, the maximum number of versions will be 7.

  • If the customer 360 database runs twice per day, the maximum number of versions will be 10, but all created within the last 7 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 or enforce static table schemas.

To enable table versioning

  1. From the Customer 360 page open a database.

  2. From the Database Table editor, select a table.

  3. In the Settings pane for the selected table, under Advanced Settings, expand Version history.

  4. 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 to ensure versions are not dropped from the table’s version history.

  5. Click Save.

Static schemas

To prevent table schemas from being updated automatically you can enforce a static schema for the database table. When enabled, a user cannot save, activate, or run a database table when there is inconsistency between the database table’s current schema and its upstream dependency.

To enforce a static table schema

  1. From the Customer 360 page open a database.

  2. From the Database Table editor, select a table.

  3. In the Settings pane for the selected table, under Advanced Settings, expand Table Schema.

  4. Select Enforce static schema.

    Note

    A table’s schema cannot be changed when this option is enabled.

  5. Click Save.

Return versions by datetime

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

1SELECT *
2FROM table_a_versioned AS a
3JOIN amperity_table_versions AS v
4ON v.version = a.amperity_version
5WHERE v.datetime = 20250402

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:

 1WITH Customer360_yesterday AS (
 2  SELECT *
 3  FROM Customer360_versioned
 4  WHERE amperity_version = (
 5    SELECT DISTINCT version
 6    FROM amperity_table_versions
 7    WHERE created_at >= DATE_TRUNC('day', current_timestamp - interval '1' day)
 8    ORDER BY created_at ASC
 9    LIMIT 1
10  )
11)
12
13SELECT *
14FROM Customer360 AS ct
15LEFT JOIN Customer360_yesterday AS cy
16ON ct.amperity_id = cy.amperity_id
17AND ct.addressline1 <> cy.addressline1
18WHERE cy.amperity_id IS NOT NULL

Use a delta to send only updated records

Build a delta that identifies which records are new or updated since the previous database update, and then send only new or updated records to downstream workflows. Use this approach with large tables that have frequent updates of data.

  1. Track the table version used to create each delta.

    Open the Queries page and create a query called “Latest_Version” with the following SQL:

    SELECT DISTINCT version
FROM amperity_table_versions
where offset = 0

    Tip

    Version identifiers are unique to a database. If deltas are required for multiple databases, use a latest version query for each database.

  2. Use an orchestration to send the results of the “Latest_Version” query to a storage location, such as an Amazon S3 bucket. Automate this workflow to run at the same time as orchestrations that send deltas.

    Tip

    Allow this workflow to overwrite the previous file each time it runs. Only the current version of this file is required.

  3. Create a feed named “Latest_Version” and use a courier to pull the file from the storage location. Configure the courier to truncate and load this file daily, and then include the courier in the daily scheduled workflow.

    Every day this courier will load the version that was used in the previous day’s workflow to today’s workflow.

  4. Add the “Latest_Version” table as a passthrough table to the same database from which the “Latest_Version” query is configured.

  5. Use a query to identify new and updated records since the previous database run.

    Open the Queries page and create a query with the following SQL:

    SELECT *
FROM [table_name]

except

SELECT tv.*
FROM [table_name]_versioned tv
JOIN Latest_Version lv
ON tv.amperity_version = lv.version

    The results of this query will be only the new and updated records.

  6. Use an orchestration to send results of this query–a list of new and updated records–to a downstream workflow.

Deltas for tables that only append records

For tables that append updated records and never update older records you can create incremental exports without using table versions.

For example, the Campaign_Results table appends information about campaigns using the current date as the delivery date. Information about campaigns for the previous day exist. Use the following SQL to send an incremental export of campaign activity:

SELECT *
FROM Campaign_Recipients
WHERE delivery_date = current_date - INTERVAL '1' DAY

This approach has some risks.

  • A workflow that is delayed may cause this query to run on a different day than intended.

  • A campaign that runs between midnight and this query will not be included in the query results until the following day.

  • Timezones can affect the timing of orchestrations.

Use offsets to send smaller deltas

Use an offset value to limit the number of records sent to a downstream workflow that allows upserts. For example, using 5 as the offset will return a list of records that is smaller that the full table.

For example:

SELECT *
FROM TABLE

EXCEPT

SELECT a.*
FROM TABLE_versioned a
JOIN (SELECT DISTINCT version
  FROM amperity_table_versions
  WHERE v.offset = 5) AS v
ON v.version = a.amperity_version

Note

This approach is less reliable than using a delta because it is more likely to contain multiple record updates even if it is unlikely to miss sending any incremental records.

The downstream workflow must allow upserts.

What about using “offset 1”?

An offset of 1 is an unreliable version indicator.

Deltas that identify new or updated data between table versions must be done against data that was sent to downstream workflows.

It is possible for more than one version to exist for the previous day, but only one version represents the data sent to downstream workflows.

For example, a manual database run creates new table versions but does not run any orchestrations and does not send data to a downstream workflow. If the manual run occurred after the automated run, the offset that represents data sent to downstream workflows is “offset 2”.

The created_at can have the same behavior.

View all versioned tables

The amperity_table_versions table stores a list of 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-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Unified_Coalesced   qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Unified_Customer    qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Merged_Customers    qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Unified_Scores      qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Table_A             qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Table_B             qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 Table_C             qdv-20250402-1234-abcd   0        2025-04-02T12:58:54.000Z
 ...
 Customer360         qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Unified_Coalesced   qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Unified_Customer    qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Merged_Customers    qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Unified_Scores      qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Table_A             qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Table_B             qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 Table_C             qdv-20250402-1234-abcd   1        2025-04-01T08:18:23.000Z
 ...
 Customer360         qdv-20200402-1234-abcd   2        2025-03-31T10:34:33.000Z
 Unified_Coalesced   qdv-20200402-1234-abcd   2        2025-03-31T10:34:33.000Z
 ...
------------------- ------------------------ -------- --------------------------