Querying historical data (AT, BEFORE) in Snowflake - Mini Project: Build & Apply

Querying historical data (AT, BEFORE)

📖 Scenario: You work as a data analyst for a retail company. The company uses Snowflake to store sales data. You want to learn how to query the sales table to see what the data looked like at a specific point in time or before that time.

🎯 Goal: Build a Snowflake SQL query that retrieves sales data as it existed at a specific timestamp and before a specific timestamp using the AT and BEFORE clauses.

📋 What You'll Learn

Create a table called sales with columns sale_id (integer), product (string), and amount (number).

Insert sample sales data with at least 3 rows.

Write a query to select all sales data AT a specific timestamp.

Write a query to select all sales data BEFORE a specific timestamp.

💡 Why This Matters

🌍 Real World

Companies often need to see how their data looked at a past point in time to audit changes or recover lost data.

💼 Career

Knowing how to query historical data is important for data analysts and engineers working with Snowflake or other cloud data warehouses.

1

Create the sales table and insert sample data

Create a table called sales with columns sale_id as integer, product as string, and amount as number. Then insert these exact rows: (1, 'Shoes', 50), (2, 'Hat', 20), (3, 'Jacket', 100).

Snowflake

-- Create the sales table and insert sample data
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE to create the table and INSERT INTO to add rows.

2

Set a timestamp variable for querying historical data

Create a variable called query_time and set it to the timestamp '2024-01-01 10:00:00' to use for querying historical data.

Snowflake

CREATE OR REPLACE TABLE sales (
  sale_id INT,
  product STRING,
  amount NUMBER
);

INSERT INTO sales (sale_id, product, amount) VALUES
  (1, 'Shoes', 50),
  (2, 'Hat', 20),
  (3, 'Jacket', 100);

-- Create a variable called query_time with the timestamp '2024-01-01 10:00:00'
-- Your code here

Need a hint?

Use SET to create a session variable and TO_TIMESTAMP to convert the string to a timestamp.

3

Write a query to select sales data AT the timestamp

Write a query that selects all columns from sales AT the timestamp stored in query_time.

Snowflake

CREATE OR REPLACE TABLE sales (
  sale_id INT,
  product STRING,
  amount NUMBER
);

INSERT INTO sales (sale_id, product, amount) VALUES
  (1, 'Shoes', 50),
  (2, 'Hat', 20),
  (3, 'Jacket', 100);

SET query_time = TO_TIMESTAMP('2024-01-01 10:00:00');

-- Write a query to select all columns from sales AT the timestamp query_time
-- Your code here

Need a hint?

Use AT (TIMESTAMP => $query_time) after the table name to query data at that time.

4

Write a query to select sales data BEFORE the timestamp

Write a query that selects all columns from sales BEFORE the timestamp stored in query_time.

Snowflake

CREATE OR REPLACE TABLE sales (
  sale_id INT,
  product STRING,
  amount NUMBER
);

INSERT INTO sales (sale_id, product, amount) VALUES
  (1, 'Shoes', 50),
  (2, 'Hat', 20),
  (3, 'Jacket', 100);

SET query_time = TO_TIMESTAMP('2024-01-01 10:00:00');

SELECT * FROM sales AT (TIMESTAMP => $query_time);

-- Write a query to select all columns from sales BEFORE the timestamp query_time
-- Your code here

Need a hint?

Use BEFORE (TIMESTAMP => $query_time) after the table name to query data before that time.