page140
123
For the next suite of tests where you
UPDATE
,
INSERT
, and then
DELETE
data, I indicate
the execution time differences for each operation, show the impact of deliberately using
an X-Small warehouse, and finally expose how retained storage for Time Travel can
affect your costs.
Most data warehouses are predicated upon periodic ingestion of data, and there are
some use cases where the volume, velocity, and variety of change cause issues. As you
may infer from the
v_table_storage_metrics
declaration, additional attributes provide
deeper insight into object storage.
You must consider object data maintenance from several perspectives:
•
Ingestion where you
INSERT
,
UPDATE
, and
DELETE
your data sets.
•
Internal processing where you manipulate your staged data.
•
Consumption where you
SELECT
, join, summarize, aggregate, and
filter your data sets.
•
The impact of object or database Time Travel setting.
•
How Fail-Safe retains micro-partitions and consumes storage.
•
Where cloned objects rely upon retained storage for deleted objects.
You will examine the impact of each perspective next.
Data Ingestion
Typically, you ingest data into a suite of staging table; let’s ignore external tables for the
purposes of this discussion.
Our data ingestion pattern should be optimized for single feeds or whole-schema
ingestion. You should not expect to run reports or analytics workloads against data
ingested into staging tables.
I must also address how DML operations affect micro-partition maintenance and
this leads to tuning your ingestion design.
Depending upon volume, frequency of ingest, and subsequent DML operations to
merge ingested data from your staging tables into your core schema, you may experience
a high degree of micro-partition churn. Your staging tables will likely be Flush and Fill
where each staging table is truncated and loaded. Pre-sorting your source data attributes
into optimal columnar format before load can optimize onward processing though any
benefits for smaller loads are likely to be minimal.
ChaPTer 4
MiCro-ParTiTionS