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
In a nutshell
  • 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.

Commitcode in GittriggersCIvalidate, test on cloneapprovedCDdeploy scriptsruns onTest then Productionsame scripts

How it works

Migration

Migration has four parts, and a good plan treats them separately.

PartWhat to decide
Objects and codeConvert schemas, views, procedures and SQL dialect to Snowflake; SnowConvert AI helps convert source code
Historical dataExtract to files in cloud storage, stage them, load with COPY INTO, or read in place with external or Iceberg tables
Ongoing dataChange data capture, Snowpipe, Snowpipe Streaming or connectors keep Snowflake current
CutoverPhased 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.
Exam tip

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

CI/CD for Snowflake: code in a Git repository is tested and deployed by a pipeline, for example with Snowflake CLI, and promoted from development to test to production. Deployment means applying object definitions to an environment. Two script styles exist.

StyleHow it worksTrade-off
Imperative (versioned migrations)Numbered scripts run once and are recorded in a history table; tools such as schemachange work this wayClear order and audit trail; you write each ALTER
DeclarativeYou state the desired object and the tool computes the change; CREATE OR ALTER and infrastructure as code tools such as Terraform follow this ideaFewer hand written ALTERs; limited to supported objects and changes

A CI/CD pipeline for Snowflake normally does the following:

  1. A pull request starts CI: lint the SQL, deploy to a clone or a test database, run tests.
  2. A merge to the main branch starts CD: deploy to the next environment, then production after approval.
  3. The pipeline connects as a service user with a role limited to that environment, authenticating with key pair authentication rather than a stored password.
  4. 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.

Pull requestSQL scriptsCIClone or test databaserun testsmergeMain branchreviewedCDProductionservice user and role

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.

AreaExample commands
Run SQLsnow sql -q "select 1", snow sql -f deploy.sql
Connectionssnow connection add, snow connection test (stored in a config.toml file)
Stages and filessnow stage copy ./local @my_stage
Git repositoriessnow git fetch, snow git execute
Apps and functionssnow snowpark deploy, snow streamlit deploy, snow app run
Objectssnow 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.

Developer or CI runnersnow CLIconnection fromconfig.tomlaccount, user, keyrunsScripts and appsSQL, Snowpark, Streamlitdeploy toSnowflake environment

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 in config.toml; snowflake.yml defines projects.
  • Idempotent scripts make reruns safe; CREATE OR REPLACE on 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.