Spring Cleaning Your Snowflake Account

The sun is shining (hopefully), the flowers are blooming, and your Snowflake account is full of tables named TEST_BACKUP_FINAL_V2.

It happens to the best of us. Data entropy is real. But a cluttered account costs money (storage) and creates security risks (over-privileged roles). Let’s do a deep clean.

1. Identifying Unused Tables#

Snowflake’s ACCESS_HISTORY view is your best friend here. We can find tables that haven’t been queried in 90 days.

SELECT
    obj.value:objectName::string as table_name,
    max(query_start_time) as last_accessed
FROM snowflake.account_usage.access_history,
LATERAL FLATTEN (base_objects_accessed) obj
GROUP BY 1
HAVING last_accessed < DATEADD(day, -90, CURRENT_DATE());

Action: Move these to an “Archive” database (or just drop them if you have Time Travel/Backups).

2. Pruning Orphan Roles#

Roles tend to accumulate. Someone asks for access, you create MARKETING_INTERN_ROLE, and then the intern leaves.

Check GRANTS_TO_ROLES to find roles that are assigned to no users.

SELECT name
FROM snowflake.account_usage.roles
WHERE deleted_on IS NULL
AND name NOT IN (
    SELECT role_name FROM snowflake.account_usage.grants_to_users WHERE deleted_on IS NULL
);

Action: Drop them.

3. Storage Audit: Failed Time Travel#

Are you paying for 90 days of Time Travel on your staging tables? Staging tables are transient by nature.

SELECT table_name, table_schema, retention_time
FROM information_schema.tables
WHERE retention_time > 1
AND table_schema = 'STAGING';

Action: Set retention to 0 or 1 day for transient data. ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS = 1.

4. Warehouse Sizing Review#

Check your warehouse metrics. Are they spilling to disk? (Up-size). Are they idling for 59 seconds every minute? (Down-size or increase suspend aggression).

Conclusion#

A clean Snowflake environment is a happy environment. Set up a quarterly task to run these audit scripts. Future you will thank you when your storage bill drops by 15%.