Technical sync tables

DinMo maintains internal sync tables for warehouse-backed activations to track state across runs and calculate deltas. These tables help DinMo compare the current source data with the last checkpoint, prepare the operations to run, and preserve a bounded history of previous syncs.

For a functional overview of activation runs, see Overview.

Naming pattern

For each sync, DinMo creates technical tables in dinmo_delta_storage schema using the following pattern:

• _{sync_id}_checkpoint

• _{sync_id}_history

• _{sync_id}_staging

• _{sync_id}_data_diff_insert

• _{sync_id}_data_diff_delete

• _{sync_id}_data_diff_update

• _{sync_id}_data_diff_error

• _{sync_id}_data_diff_nothing

Persisted and temporary tables

Only the checkpoint and history tables are persisted after a sync run completes.

The staging table and all data-diff tables are working tables for the current run. They are deleted and recreated for each sync execution.

Data-diff tables

The data-diff tables are rebuilt at the beginning of each sync run. They represent the difference between the current source table and the checkpoint table from the previous run.

Together, these tables classify the records involved in the current sync into insert, delete, update, unchanged, and error states.

Staging table

The staging table contains the primary keys involved in the current sync run across all operation types:

• inserted

• deleted

• updated

• unchanged

• error

During the sync, DinMo deletes and recreates this table, then appends the records processed in the current run. It is used as a working table to track the current sync before state is committed to the checkpoint table.

During the checkpoint commit step, DinMo also adds the primary keys that were seen in previous runs but are not present in the current sync. At that stage, the staging data becomes the basis for the history table.

Checkpoint table

The checkpoint table is persisted across sync runs. It contains the primary keys seen since the beginning of the activation sync and is used to compute the data-diff tables at the start of each new run.

DinMo only updates the checkpoint table at the end of the current sync run.

At the end of the run:

• primary keys whose latest operation is insert or update remain in the checkpoint table

• primary keys whose latest operation is delete are removed from the checkpoint table

• if a delete operation fails on the destination side, the primary key stays in the checkpoint table so DinMo can retry the delete on the next sync run

When building the new checkpoint state, DinMo keeps the latest operation for each primary key from the staging table.

History table

The history table stores the operations recorded across sync runs.

DinMo keeps:

• at least one row for every primary key

• for primary keys with more than one operation, only the last 90 days of history and the most recent 1,000,000 operations

and checkpointed_at >= (now - 90days)
and history_row_num <= 1000000

All staging data from the current sync is appended to the history table. DinMo preserves the latest distinct operations for each primary key, ordered by checkpointed_at, which is the timestamp of the sync run.