Querying historical data with Time Travel
Snowy Peak's overnight batch job loaded 2 cancelled accounts into the subscriptions table. The data team wants to compare the current row count with what it was just before the batch ran. Snowflake's Time Travel makes this possible without any external backup.
The snowy_peak.subscriptions table has columns subscription_id, user_email, plan, status, start_date, and monthly_fee. The session variable $snapshot_ts holds the timestamp captured just before the batch update ran.
Este ejercicio forma parte del curso
Snowflake Architecture
Instrucciones del ejercicio
- Use
AT (TIMESTAMP => ...)to querysnowy_peak.subscriptionsas it was before the batch update. Use the session variable$snapshot_tsas the timestamp. - Count the rows as
pre_update_countto confirm how many subscriptions existed before the 2 cancelled accounts were added.
Ejercicio interactivo práctico
Prueba este ejercicio y completa el código de muestra.
-- Count subscriptions before the overnight batch update
SELECT COUNT(*) AS pre_update_count
FROM snowy_peak.subscriptions
AT (___ => ___);