History snapshots with Polytomic

Polytomic's Bulk Syncs allow you to generate a history table from any of your source systems. This history mode is universal: you can generate history tables from any source tables in the systems Polytomic can sync from. For example: PostgreSQL and MongoDB CDC streams, SaaS applications like Salesforce, NetSuite, and others, spreadsheets, CSV/JSON/Parquet source files, and all other source integrations supported by Polytomic.

A history table (sometimes called a snapshot table) is an append-only table where each change to a source row is captured rather than only the current state of the source row.

The typical use case for this is to be able to run a historical or cohort analysis on a table's rows, where all changes to all rows are needed.

Activating a history table

You can activate a history table from any existing table that you are syncing. To do so, go to the Polytomic Bulk Sync configuration and click on the ... button to the right of the table whose change history you want to start capturing. From that submenu, turn on the 'History mode' switch:

Once turned on, all row changes to that table will start being recorded in a new table named <source_table_name>_history. In the example above, the history table will be named users_history after the source table name of users.

Note that you cannot sync a history table without also syncing its source table.

History table format

Polytomic's history tables follow the Slowly Changing Dimension (SCD) Type 2 format. Specifically, they contain all columns of the source table with the addition of these:

Column name	Type	Definition
`_polytomic_id`	string	Row identity; same value as the source table's `_polytomic_id`
`_polytomic_operation`	string	The type of row update: `insert`, `update`, or `delete`
`_polytomic_start`	timestamp	When Polytomic observed this row change
`_polytomic_end`	timestamp	When this row change was superseded by another one. This value is `NULL` while the change remains current.
`_polytomic_active`	boolean	`TRUE` while a row change remains current; `FALSE` otherwise
`_polytomic_row_hash`	string	Polytomic system value; not intended for querying

Note that start and end intervals are half-open: a row version is in effect at time T for _polytomic_start <= T < _polytomic_end, while a row version that is not active anymore will have its _polytomic_end equal to its successor's _polytomic_start.

At most one version per row will be active (i.e. where _polytomic_active is TRUE). A row marked as deleted will have no active versions: all its versions will have _polytomic_active set to FALSE.