Database table snapshotting is a practice used to store the state of a table at particular points in time, which can then be used for analyses like time-series reporting, for auditing purposes, and/or for general data backup and version control.
Each snapshot stores the state of all data in a table whenever the snapshot is run. In Mozart, when snapshotting is turned on for a table, a snapshot is taken every day at 03:05am PT. Those snapshots get added to the "snapshot" schema, in a table which is named after the table being snapshotted.
Each time a new snapshot is created, the results are appended to the corresponding table in the "snapshot" schema. Each row in that table has a "snapshot_ts" field, which is a timestamp of when that row was snapshotted. That field allows you to see how the table's data has changed over time.
To turn on snapshotting for a table
- On the header, click on Warehouse.
- Find the table you want to snapshot. This can be a table from one of your connected data sources, or a Transform.
- Turn on snapshotting by clicking the slider under the "Snapshot" column next to the desired table.
Once the snapshot runs the first time (at 03:05am PT), the results will be under the "snapshot" schema. The table created will be named based on the schema name and table name of the table being snapshotted: "[table_schema_name]__[table_name]"
You can now view and query against the snapshotted table like any other table in your Warehouse.
To turn off snapshotting for a table
- On the header, click on 'Warehouse'.
- Turn off snapshotting by clicking the slider under the "Snapshot" column next to the currently snapshotted table.
Note that if you had successfully snapshotted that table before, all prior results will remain the same table in the "snapshot" schema. You would need to delete the table in your Snowflake instance to fully remove it from your Warehouse. If you keep that table in your Warehouse and decide to turn snapshotting back on at a future date for the corresponding table, those results will be appended to the preexisting table in the "snapshot" schema.