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.
This exercise is part of the course
Snowflake Architecture
Exercise instructions
- 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.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Count subscriptions before the overnight batch update
SELECT COUNT(*) AS pre_update_count
FROM snowy_peak.subscriptions
AT (___ => ___);