Snowflake Architecture · Topic 8 of 18
Development Lifecycle - Migration, Deployment, CI/CD and Snowflake CLI
Plan migrations to Snowflake and automate deployment with CI/CD pipelines, versioned or declarative scripts and the Snowflake CLI, with exam traps.
- 5 min
- Includes a diagram
- Migration: convert code (SnowConvert AI), load history with stage and COPY, keep current with CDC or Snowpipe, validate, then cut over, preferably phased.
- Versioned migration scripts (schemachange style) versus declarative (
CREATE OR ALTER, Terraform). - CI tests on clones; CD promotes the same scripts through environments with a service user.
What it is
The development lifecycle is the path of a change from idea to production: design, build, test, deploy, operate. The architect makes that path repeatable so the same objects exist in every environment and nobody edits production by hand.
Two situations dominate the exam. Migration moves an existing system (another warehouse, a data lake, an on premises database) into Snowflake. Deployment moves your own changes between Snowflake environments, usually automated by CI/CD with the Snowflake CLI.
How it works
Migration
Migration has four parts, and a good plan treats them separately.
| Part | What to decide |
|---|---|
| Objects and code | Convert schemas, views, procedures and SQL dialect to Snowflake; SnowConvert AI helps convert source code |
| Historical data | Extract to files in cloud storage, stage them, load with COPY INTO, or read in place with external or Iceberg tables |
| Ongoing data | Change data capture, Snowpipe, Snowpipe Streaming or connectors keep Snowflake current |
| Cutover | Phased by workload, or parallel run of old and new, then switch consumers |
Practical rules:
- Validate with row counts, aggregates and sample comparisons between source and Snowflake before cutover.
- Run source and target in parallel for a period when the risk is high, then retire the old system.
- Migrate by subject area or workload, not everything at once, so each step can be tested and rolled back.
- Redesign where it pays: a migration is a chance to replace load scripts with ELT and to drop indexes and partitioning logic that Snowflake does not need.
Snowflake has no indexes to migrate. Lift and shift of index and partition strategies is wasted effort; clustering keys come later and only where measured.
Deployment and CI/CD
Deployment means applying object definitions to an environment. Two script styles exist.
| Style | How it works | Trade-off |
|---|---|---|
| Imperative (versioned migrations) | Numbered scripts run once and are recorded in a history table; tools such as schemachange work this way | Clear order and audit trail; you write each ALTER |
| Declarative | You state the desired object and the tool computes the change; CREATE OR ALTER and infrastructure as code tools such as Terraform follow this idea | Fewer hand written ALTERs; limited to supported objects and changes |
A CI/CD pipeline for Snowflake normally does the following:
- A pull request starts CI: lint the SQL, deploy to a clone or a test database, run tests.
- A merge to the main branch starts CD: deploy to the next environment, then production after approval.
- The pipeline connects as a service user with a role limited to that environment, authenticating with key pair authentication rather than a stored password.
- Secrets live in the CI system's secret store, never in the repository.
Keep scripts idempotent (CREATE ... IF NOT EXISTS, CREATE OR REPLACE for stateless objects, CREATE OR ALTER where supported) so a rerun is safe. Be careful with CREATE OR REPLACE on tables, because it drops existing data.
Snowflake CLI
The Snowflake CLI (the snow command) is the open source command line client for developer workloads, and the preferred client for new work. SnowSQL is the legacy client; new features go only to the Snowflake CLI.
| Area | Example commands |
|---|---|
| Run SQL | snow sql -q "select 1", snow sql -f deploy.sql |
| Connections | snow connection add, snow connection test (stored in a config.toml file) |
| Stages and files | snow stage copy ./local @my_stage |
| Git repositories | snow git fetch, snow git execute |
| Apps and functions | snow snowpark deploy, snow streamlit deploy, snow app run |
| Objects | snow object list, snow object describe |
Application style projects use a project definition file (snowflake.yml) that describes what to deploy, so the same command works on a laptop and in CI. For plain SQL, snow sql -f or EXECUTE IMMEDIATE FROM a file runs the scripts in order.
Common mistakes
Hand editing production. Changes outside the pipeline cause drift between environments. Make every change a script.
Using CREATE OR REPLACE on tables in a deploy. It drops the data. Use CREATE OR ALTER where supported or versioned ALTER scripts.
Storing passwords in the pipeline. Use key pair authentication and the CI secret store.
Lifting indexes and partitions during migration. Snowflake does not use them; design for micro-partitions and clustering where needed.
Cutting over without validation. Compare row counts and aggregates first.
What to remember for the exam
- Migration: convert code (SnowConvert AI), load history with stage and COPY, keep current with CDC or Snowpipe, validate, then cut over, preferably phased.
- Versioned migration scripts (schemachange style) versus declarative (
CREATE OR ALTER, Terraform). - CI tests on clones; CD promotes the same scripts through environments with a service user.
- Snowflake CLI is
snow; SnowSQL is legacy; connections live inconfig.toml;snowflake.ymldefines projects. - Idempotent scripts make reruns safe;
CREATE OR REPLACEon a table loses its data.
Check yourself
Which client is preferred for new deployment automation, SnowSQL or Snowflake CLI?
Snowflake CLI (
snow). SnowSQL is legacy and receives no new features.Why test a deployment on a clone?
A zero copy clone gives production like data and structure cheaply, so scripts are proven before they reach production.
What is the risk of CREATE OR REPLACE TABLE in a deployment script?
It recreates the table and drops its existing data. Use CREATE OR ALTER or an ALTER script instead.
Topic 8 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.