Capture table history with snapshots

This guide shows you how to capture the history of ticking tables.

An append-only table only adds rows at the bottom and never deletes or modifies them, so the table itself is its history. Other ticking tables can also modify or remove rows, or insert rows in the middle, so the table alone doesn't preserve earlier values. By using snapshot_when, you can capture the history of a table, even if it is not append-only. To keep only the latest state of a table each time another table ticks, instead of its full history, see Reduce the update frequency of ticking tables.

Syntax

snapshot_when with history=True produces an in-memory table that records the history of a source table. Two tables control it:

• The source table is the table you call snapshot_when on. snapshot_when copies this table into the result.
• The trigger table is the table whose rows cause snapshots. For each row the trigger table adds, snapshot_when appends a full copy of the source table to the result. When you create the result, snapshot_when also appends one copy for each row the trigger table already has. If the source table is empty when the trigger table adds a row, that trigger row adds nothing to the result.

The trigger table is often a time table, which adds rows at a regular, user-defined interval. A time table adds its new rows once per update graph cycle, which targets one second by default. While cycles keep pace with the time table's period, it adds one row per interval, so the result gets one snapshot per interval; if a cycle runs long, the next cycle adds every row that came due in the meantime. A time table created without a start time starts empty and adds exactly one row in the first update graph cycle after you create it. Because the time table has no rows when you create the result, it adds no initial snapshot.

Stamp columns are columns from the trigger table that snapshot_when adds to every row of a snapshot, so each row shows which trigger event captured it.

• Use stamp_cols to choose them.
• If you omit stamp_cols or pass an empty list, every column of the trigger table is a stamp column.

Requirements and limitations

With history=True, the trigger table must be append-only. A time table meets this requirement unless you pass blink_table=True to time_table, which makes it a blink table. A blink table keeps only the rows added in the current update graph cycle. If the trigger table modifies or removes rows, or adds a row anywhere but after its last row, the snapshot raises an assertion error and the result table stops updating.

You can't combine history=True with the initial or incremental parameters.

The result records the source table only at trigger events. If the source table changes more than once between two trigger rows, the result keeps only the state at the next trigger.

Capture the history of a ticking table

The example uses two input tables:

• source starts from a time table with a 0.2-second period. A time table adds its new rows once per update graph cycle, as described in Syntax. So the time table adds roughly five rows about once per second, and source updates at the same rate.
• trigger is a time table with a 2-second period. Each row it adds makes snapshot_when append a snapshot of source to result.

Both tables have a Timestamp column. The example doesn't pass stamp_cols, so every column of trigger becomes a stamp column. It renames the Timestamp column of trigger to TriggerTimestamp to avoid the stamp-column name conflict described in Requirements and limitations.

Because of last_by, source has at most two rows: the latest row for X = A and the latest row for X = B.

Usually both time tables get their first row in the same update graph cycle. Each time table adds only one row in its first cycle, as described in Syntax, so at that point source has only the X = A row. The first snapshot usually copies that one row. Every later snapshot appends a copy of both rows. Each copied row carries the TriggerTimestamp of the trigger row that captured it.

Existing rows in result never change, so result keeps a record of source as it was at each trigger event.

As described in Requirements and limitations, result doesn't record changes to source between two trigger events. In this example, source updates about once per second, but trigger adds a row only every two seconds, so snapshot_when appends a snapshot only every two seconds.

Related documentation

• Create a time table
• Reduce the update frequency of ticking tables
• Table types
• last_by
• rename_columns
• snapshot
• snapshot_when
• time_table
• update