7
0
Recently my colleague Aman and I were discussing a challenge that we were facing in our project: How can we track DDL (Data Definition Language) changes made to tables in Snowflake?. We needed a way to monitor schema modifications, not just to capture what changed but also when those changes occurred.
Our goal was to create a solution that would allow us to log changes such as adding or dropping columns, along with the exact dates these changes were made. Essentially, we wanted an audit trail that could show, for example, if Table A was altered on the 1st day, and then modified again on the 2nd day, detailing each operation (column addition or deletion) along the way. This would give us visibility into the structural evolution of our tables over time and help with both auditing and ensuring data consistency.
Snowflake doesn’t offer an out-of-the-box solution for logging DDL changes in this way. This led us to brainstorm how we could develop a custom solution to monitor and log DDL modifications to ensure data integrity and support auditing needs.
To address this, we implemented a DDL auditing solution using a Snowflake stored procedure and schema snapshots.
The key objectives were:
- Track schema changes dynamically whenever a table is modified.
- Log changes (addition, modification, or deletion of columns) into a dedicated audit table.
- Automatically update schema snapshots to maintain a consistent reference for future checks.
Step-by-Step Breakdown of the Solution
Step-by-Step Breakdown of the Solution
- Creating the Schema Snapshot and DDL Change Log Tables
We began by creating two key tables:
- TABLE_schema_snapshot: This stores the current state of table schemas, which acts as a baseline for detecting changes.
- ddl_change_log: This table logs any DDL changes made to the table structures, recording details such as column changes, data types, and timestamps.
- Stored Procedure to Monitor Schema Changes
The core of our solution is the monitor_ddl_changes procedure. This procedure compares the current schema of a given table against the snapshot stored in TABLE_schema_snapshot and logs any changes in ddl_change_log.
Here’s how the stored procedure works:
- Step 1: Fetch the current schema (columns and data types) of the table from the information_schema.columns.
- Step 2: Compare the current schema with the previously captured schema stored in TABLE_schema_snapshot.
- Step 3: If new columns are added, dropped, or modified, log the changes into ddl_change_log.
- Step 4: Update the schema snapshot in TABLE_schema_snapshot to reflect the latest state.
Stored Procedure:
Execute the Stored procedure:
Once the monitor_ddl_changes procedure executes, it detects the DDL changes (if any) and logs them into the ddl_change_log table. This table will now contain records for any column additions, deletions, or modifications made to the specified table, along with the operation type, column name, timestamp of the change, and user details.
Conclusion
Conclusion
This solution provides a comprehensive way to track DDL changes made to tables in Snowflake, ensuring you can monitor changes over time and maintain an audit trail. This can be particularly valuable in environments with multiple users and frequent schema modifications, helping ensure that:
- Changes are documented and can be traced back to the user who made them,
- Modifications to the schema don’t go unnoticed and impact downstream applications.
As an Account Admin, this custom solution can be implement in any Snowflake project to keep a close eye on structure changes within tables, ensuring better control, governance, and transparency.
