page273
361
Transient or Permanent Tables?
I am assuming that inbound data feeds are repeatable and therefore suggest ingestion
of raw or staging tables should use transient tables with Time Travel set to 0 as transient
tables do not utilize Fail-Safe. You might also consider using transient tables for
frequently refreshed data generated for point-in-time reporting.
Permanent tables should be reserved for persistent storage where Time Travel is
required. Note that the seven-day Fail-Safe period follows. Typically, permanent tables
are used for storing both curated and historized data.
Parallelizing data loads as shown in Chapter 8 can significantly reduce micro-
partition churn for permanent tables.
Warehouse Considerations
Correctly sizing and scaling a warehouse is dependent upon a full understanding of
workload under “steady state” conditions. Previously in Chapter 8 we asked these
questions:
•
Is queueing, spills to disk, or OOMs evident?
•
Is the warehouse overloaded, queueing, or blocking?
•
Is the data feed overrunning its schedule leading to feeds backing up?
•
Are costs increasing over time?
Later in this chapter I summarize queries to answer each question noting that there
are several ways to answer these questions.
As discussed in Chapter 6, I prefer fixed-size warehouses where the warehouse
declaration remains constant.
i do not advocate dynamically resizing warehouses. This is a poor approach to
performance tuning.
A known, fixed suite of declared warehouses is preferable to dynamically managed
warehouses. Dynamically resizing warehouses disables the warehouse cache.
ChapTer 10
OpTimizing perfOrmanCe