Access history and audit logging in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to query the access history for the last 7 days.

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE EVENT_TIMESTAMP >= CURRENT_DATE() - INTERVAL '[1]' DAY;

Drag options to blanks, or click blank then click option'

A1

B14

C7

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number without quotes in INTERVAL.

Using a wrong number of days like 30 or 1.

The query filters access history for the last 7 days using INTERVAL '7' DAY.

2fill in blank

medium

Complete the code to filter audit logs by a specific user.

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE USER_NAME = '[1]';

Drag options to blanks, or click blank then click option'

ASYSADMIN

BADMIN

CPUBLIC

DJOHN_DOE

Attempts:

3 left

💡 Hint

Common Mistakes

Using role names instead of usernames.

Using lowercase when username is uppercase.

Replace '{{BLANK_1}}' with the exact username to filter logs for that user.

3fill in blank

hard

Fix the error in the query to get audit logs for a specific object type.

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE OBJECT_TYPE = '[1]';

Drag options to blanks, or click blank then click option'

ATABLE

BTable

Ctable

Dtables

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase or plural forms for object types.

Using quotes incorrectly.

Object types in Snowflake audit logs are uppercase, so use 'TABLE'.

4fill in blank

hard

Fill both blanks to create a query that counts access events grouped by user and event type.

Snowflake

SELECT USER_NAME, [1], COUNT(*) AS EVENT_COUNT FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY GROUP BY USER_NAME, [2];

Drag options to blanks, or click blank then click option'

AEVENT_TYPE

BEVENT_TIMESTAMP

CUSER_NAME

DOBJECT_NAME

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by USER_NAME twice.

Grouping by a column not selected.

Grouping by EVENT_TYPE along with USER_NAME counts events per user per event type.

5fill in blank

hard

Fill all three blanks to create a query that filters access history for a specific database, object type, and orders by event time descending.

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE DATABASE_NAME = '[1]' AND OBJECT_TYPE = '[2]' ORDER BY [3] DESC;

Drag options to blanks, or click blank then click option'

AMY_DATABASE

BTABLE

CEVENT_TIMESTAMP

DSCHEMA_NAME

Attempts:

3 left

💡 Hint

Common Mistakes

Using schema name instead of database name.

Ordering by a non-timestamp column.

This query filters by database name and object type, then orders results by event timestamp descending.