I'm building a proof of concept for deploying Snowflake objects such as tables, streams, tasks, and stages across DEV, QA, and PROD using GitHub Actions. I'm using schemachange for versioned deployments, SQLFluff for linting, and Python scripts to create database backups and handle rollback if a deployment fails. I'm currently testing in DEV, but I'm unsure how to verify that the objects created from the schemachange migrations and SQL DDL files actually match the intended definitions and work correctly. What post-deployment validation should I add, and what other practices would improve the pipeline?
3 Answers
Separate deployment validation into two checks. First, query schemachange’s change-history table to confirm that the expected migration scripts ran. That only proves execution, though. For correctness, add a post-deployment step that queries Snowflake metadata and fails the pipeline when the actual state differs from the expected state. You can use SHOW TABLES, SHOW STREAMS, SHOW TASKS, and SHOW STAGES for existence and basic properties, INFORMATION_SCHEMA.COLUMNS for table columns and data types, and GET_DDL to retrieve the deployed definition and compare it with the expected DDL. Keep in mind that schemachange’s verify command checks connectivity and configuration, not whether the resulting objects are correct.
For the broader pipeline, make dependencies explicit because migration tools generally follow version order rather than automatically resolving object dependencies. Use repeatable scripts for views or procedures when CREATE OR REPLACE is safe, but avoid replacing data-bearing tables casually. Snowflake zero-copy database or schema clones can provide a simpler pre-deployment restore point than maintaining custom snapshot scripts, while Time Travel can help recover recent changes. For production, add an approval gate using the CI system’s environment protection rules. A sensible flow is lint, deploy to a temporary or lower environment, run metadata and smoke tests, then require approval before production.
A manifest file can make this much easier to manage. Use YAML or JSON to describe each expected object, including its type, name, columns, and important properties. After deployment, have a validation script compare the manifest with Snowflake metadata and return a nonzero exit code for any mismatch. Also validate behavior and state, not just existence: tasks are created suspended by default, streams can become stale, and stages may exist but have incorrect or unusable configuration.
GET_DDL is especially useful because it catches differences that a simple existence check misses. I’d store the expected definitions or assertions alongside the migration files so the validation stays versioned with the deployment.