Agent WranglersDEN-33Recommended
Rehearse restoring a dropped warehouse table
Write down how long the warehouse can bring back a dropped or overwritten table. Keep raw tables and snapshots recoverable for longer than that, and rehearse one restore every 6 months.
How to do it
Models can be rebuilt from raw data, as Land raw data unchanged and build tables in layers says. Raw tables and snapshots cannot, once the source has moved on. BigQuery's time travel reaches back 2 to 7 days, 7 by default, plus 7 days of fail-safe through support. Other warehouses have their own windows, and a self-hosted one needs backups as Prove restores work says. For a longer window, export raw tables and snapshots on a schedule to storage in another account. Give restores to a role that loaders and BI tools do not hold.
Examples
Shell example
# BigQuery: copy the table as it was at a moment in Unix milliseconds
bq cp example:raw.orders@1767225600000 example:raw.orders_restoredHow to check
On staging, drop a raw table and restore it from time travel or the export. Its row count matches the count before the drop. The rehearsal log shows one restore in the last 6 months.
Sources
Related rules
- DEN-2 Land raw data unchanged and build tables in layers in Data engineering
- DAT-28 Prove restores work in Data and databases
- DEN-38 Keep lake tables in an open table format and maintain them in Data engineering
- DEN-34 Keep the history of changing values with snapshots in Data engineering
For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/data-engineering/warehouse-and-models/warehouse-restore">Agent Wranglers data engineering playbook</a>. In code you write, add a comment next to it with the same link.