page146
129
The simple walk-through of the life cycle of data supports the earlier assertion of
both
INSERT
and
DELETE
operations being faster than
UPDATE
operations. Your mileage
may vary according to the volume, velocity, and variety of change experienced within
your application; note the retained data indicated by
time_travel_GB
can far exceed
initial expectations.
Unlike data ingestion and data consumption, for data processing, you have limited
options when considering how to reduce storage costs.
•
Set Time Travel to the minimum required period for each object.
•
Use temporary tables and transient tables where possible.
•
Use clones noting that new micro-partitions are created when data
changes within the cloned object.
Snowflake recommends the use of temporary tables to hold intermediate result sets
to reduce query complexity.
Data Consumption
In the previous section, I discussed data processing where you ingest data from your
staging tables and ingest into your core application components. You also began to see
the impact of data retention in support of Time Travel.
The same considerations apply to outbound data consumption where you will
observe storage is retained due to your data retention period, particularly when moving
data from third normal form to Data Vault 2.0 and then into star schemas with each data
model retaining a copy of all data. The important point to note is the physical cost of
storing multiple copies of data within your various models along with the hidden cost of
storage to support both Time Travel setting and Fail-Safe retention period.
Data consumption can lead to increased storage costs where you may need to
denormalize data. The same storage considerations apply when you create these objects
as you consume additional storage to support faster and more user-friendly data access
paths. You do not get anything for free, and system implementation is usually a trade-off.
There is always a price to pay either in terms of performance or storage.
ChaPTer 4
MiCro-ParTiTionS