page147
130
Where data sets are periodically rebuilt and history is not required, you might
consider optimizing your data consumption costs by doing the following:
•
Set Time Travel to 0 for periodically rebuilt tables where persistence
is not required; note that Fail-Safe is retained for seven days.
•
Use transient tables for periodically rebuilt tables with Time Travel
set to 0 as transient tables do not utilize Fail-Safe.
Both of these approaches imply periodically rebuilt tables can be rebuilt from source
within acceptable timeframes and with minimal business impact.
Time Travel
In previous sections many references have been made to Time Travel, and you have seen
the impact of
UPDATE
,
INSERT
, and
DELETE
operations on data retention too.
Many Snowflake applications set Time Travel at the database level to 90 days
ensuring that all objects created within the database inherit the default setting. With
your new understanding of the storage implications for high Time Travel retention
settings, you must adopt a more nuanced approach.
Not all applications are equal; your requirements will differ accordingly, and the key
takeaway from the next suite of suggestions is to balance your efforts. Storage is relatively
cheap these days.
To assist tuning your storage design, I list some options for your consideration; again,
remember not to “boil the ocean.” Focus on the cheapest and quickest options to return
the maximum benefit for the minimum amount of expended effort.
•
Where ingested data can easily be reloaded, choose either temporary
or transient tables.
•
Where processed data is subject to high-frequency, low-volume DML
activity, set Time Travel as low as acceptable.
•
Build intermediate data sets into temporary tables before loading
into core tables.
•
Parallelize high-frequency, low-volume data loads to reduce micro-
partition churn.
•
Adopt an insert-only design pattern such as Data Vault 2.0.
ChaPTer 4
MiCro-ParTiTionS