page128
111
CREATE OR REPLACE VIEW v_stage_avg_storage
AS
SELECT usage_date,
average_stage_bytes / 1024 / 1024 AS avg_stage_MB,
average_stage_bytes / 1024 / 1024 / 1024 AS avg_stage_GB
FROM snowflake.account_usage.stage_storage_usage_history
ORDER BY usage_date DESC;
As with all Account Usage Store views, latency applies, in this case of up to 120
minutes. You can find more information at
https://docs.snowflake.com/en/sql-
reference/account-usage/stage_storage_usage_history
.
External storage consumption can be tracked using CSP tooling and will be charged
separately than the Snowflake storage charges.
Micro-partition Overview
Now I will discuss “how” storage is managed, always keeping in mind the manner in which
the Snowflake optimizer processes your SQL statements to deliver highly performant queries.
What Are Micro-partitions?
Micro-partitions are the fundamental units of storage that comprise physical tables
and are immutable. Snowflake does not add, change, or remove data from an existing
micro-partition. Every data change is recorded by creating new micro-partitions, and the
old micro-partitions age out according to the Time Travel setting and Fail-Safe, both of
which are explained in my first book, Building the Snowflake Data Cloud.
Unlike some legacy RDBMSs, micro-partitions do not require the periodic gathering
of statistics. Snowflake guarantees statistics are always maintained for every object. To
illustrate this point, consider how the results for your next SQL statement are generated.
SELECT count(1)
FROM lineitem_baseline;
This query should return 5,999,989,709 rows, though the interesting information is
“how” the row count was derived. To reveal the information source, click the query ID to
access the query profile. Figure 4-2 shows the row count was derived from metadata, and
the profile shows “Other” indicating that the stored statistics were referenced.
ChaPTer 4
MiCro-ParTiTionS