page286
374
Time Travel Setting
Incorrectly setting Time Travel to retain data for longer than required incurs additional
storage costs. In Chapter 4, I discussed various considerations that may facilitate a
reduction in your Time Travel setting for each object.
•
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.
•
Where consumed data is periodically recreated, choose
transient tables.
•
For large tables, implement optimal clustering keys to match the
most common data access paths.
Use Clones
When testing, you will often require either a baseline data set or a known configuration
to reset at a known point in time. I will discuss cloned objects in detail in Chapter 4.
The overuse of clones is guaranteed to increase storage costs and, under certain
circumstances, may contribute to metadata queries running slowly.
Remove all redundant cloned objects at the earliest possible opportunity.
Warehouse AUTO_SUSPEND
The minimum runtime for a warehouse is 60 seconds. You must ensure your warehouses
cease execution after 60 seconds; this occurs only where there is no load on the
warehouse.
ChapTer 10
OpTimizing perfOrmanCe