page148
131
•
Where consumed data is periodically re-created, choose
transient tables.
•
For large tables, implement optimal cluster keys to match the most
common data access paths; see the next chapter for details.
Let’s first identify the Time Travel settings for your TPC database:
SELECT retention_time
FROM snowflake.account_usage.databases
WHERE database_name = 'TPC'
AND deleted IS NULL;
Our query should return 90 indicating the Time Travel retention period is 90 days for
the TPC database.
Now create a table called
lineitem_baseline_tt_test
for immediate
DROP
; the Data
Retention Period
data_retention_time_in_days
is set to 1.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xl );
The next statement
CREATE TABLE AS SELECT
(CTAS) will take a few minutes to run.
CREATE OR REPLACE TABLE tpc.tpc_owner.lineitem_baseline_tt_test
data_retention_time_in_days = 1
AS
SELECT *
FROM tpc.tpc_owner.lineitem_baseline;
Immediately drop your new table,
lineitem_baseline_tt_test
.
DROP TABLE tpc.tpc_owner.lineitem_baseline_tt_test;
Then reset your warehouse to X-SMALL.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Then check the effect on storage by executing the following query; you may
experience latency:
SELECT active_GB,
time_travel_GB,
failsafe_GB,
ChaPTer 4
MiCro-ParTiTionS