Access history and audit logging in Snowflake - Mini Project: Build & Apply

Access History and Audit Logging in Snowflake

📖 Scenario: You are a data engineer working with Snowflake. Your manager wants you to track who accessed the data warehouse and what actions they performed. This helps keep the data safe and shows accountability.

🎯 Goal: You will create a query to view access history and audit logs in Snowflake. This will help you understand who accessed the system and what they did.

📋 What You'll Learn

Use the SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY view to get access records

Filter records by a specific user

Select important columns like USER_NAME, ACTION_NAME, and START_TIME

Order the results by START_TIME descending

💡 Why This Matters

🌍 Real World

Access history and audit logging help companies track who accessed their data and what actions were taken. This is important for security and compliance.

💼 Career

Data engineers and security analysts often write queries like this to monitor data warehouse usage and investigate suspicious activity.

1

Create a variable for the user name

Create a variable called user_name and set it to the string 'JOHN_DOE' to filter access history for this user.

Snowflake

# Create a variable called user_name and set it to 'JOHN_DOE'
# Your code here

Need a hint?

Use = to assign the string 'JOHN_DOE' to the variable user_name.

2

Write the base query to select access history

Write a SQL query string called query that selects USER_NAME, ACTION_NAME, and START_TIME from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY.

Snowflake

user_name = 'JOHN_DOE'
# Write a SQL query string called query to select USER_NAME, ACTION_NAME, START_TIME from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
# Your code here

Need a hint?

Use double quotes to create the SQL query string and select the three columns from the access history view.

3

Add a WHERE clause to filter by user and order results

Update the query string to add a WHERE USER_NAME = '{user_name}' clause using an f-string, and order the results by START_TIME in descending order.

Snowflake

user_name = 'JOHN_DOE'
query = "SELECT USER_NAME, ACTION_NAME, START_TIME FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY"
# Update query to filter by user_name and order by START_TIME DESC
# Your code here

Need a hint?

Use an f-string to insert the variable user_name inside the SQL query string.

4

Print the final query string

Write a print statement to display the query string.

Snowflake

user_name = 'JOHN_DOE'
query = f"SELECT USER_NAME, ACTION_NAME, START_TIME FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE USER_NAME = '{user_name}' ORDER BY START_TIME DESC"
# Print the query string
# Your code here

Need a hint?

Use print(query) to show the final SQL query string.