ExamFinch.com My Study

Study guide

7.3 DataOps, CI/CD & Snowflake Git Integration

Practice this exam

SnowPro Advanced: Architect ARA-C01

Track your progress as you read

Finished sections get a check mark.

1 Introduction: The ARA-C01 Exam

About the SnowPro Advanced: Architect (ARA-C01) Exam

2 Chapter 1: Account Strategy & Organization Architecture

1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture

3 Chapter 2: Identity, Access Control & Storage Integrations

2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies

4 Chapter 3: Governance, Data Privacy, Network Security & Compliance

3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)

5 Chapter 4: Data Modeling in Snowflake

4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration

6 Chapter 5: Data Sharing & Collaboration Solutions

5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms

7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity

6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs

8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications

7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps

9 Chapter 8: Data Ingestion Architecture

8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture

10 Chapter 9: Continuous Pipelines & Stream Transformation

9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design

11 Chapter 10: Semi-Structured Data & Ecosystem Tooling

10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures

12 Chapter 11: Virtual Warehouse Architecture & Concurrency

11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service

13 Chapter 12: Query Profiling & Performance Diagnostics

12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse

14 Chapter 13: Storage Optimization, Cost Governance & Observability

13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer

7.3 DataOps, CI/CD & Snowflake Git Integration

Key Takeaways Explain This Topic AI

DataOps in Snowflake standardizes database change management (DCM) through declarative state tooling (Terraform, dbt) or versioned imperative migration frameworks (schemachange, Flyway, Liquibase).

Snowflake Git Integration introduces native first-class Git repository stages (CREATE GIT REPOSITORY), connecting Snowflake directly to GitHub, GitLab, and Bitbucket over secure HTTPS API integrations.

SQL migration scripts and Snowpark procedures can be executed directly from version control inside Snowflake via EXECUTE IMMEDIATE FROM @git_repo/branches/main/migrations/V1.0__init.sql without local file staging.

CI/CD pipelines should run as service users (TYPE = SERVICE) with non-password authentication such as key pairs or workload identity federation, restrictive network policies, and least-privilege deployment roles rather than ACCOUNTADMIN.

Blue/Green Zero-Copy Clone deployment patterns achieve zero-downtime releases and instantaneous rollbacks by cloning production, applying migrations to green, running automated tests, and executing an atomic ALTER DATABASE prod SWAP WITH prod_green;.

Last updated: September 2026

7.3 DataOps, CI/CD & Snowflake Git Integration

As enterprise data architectures expand in scale and complexity, manual DDL execution, ad-hoc spreadsheet tracking of schema migrations, and sharing privileged credentials become untenable. Uncontrolled schema modifications lead to environment drift, broken reporting pipelines, unrepeatable deployments, and severe governance violations.

DataOps applies modern software engineering disciplines—version control, continuous integration (CI), continuous delivery/deployment (CD), automated testing, and declarative infrastructure-as-code (IaC)—to the data lifecycle. Snowflake provides deep native support for DataOps through Git Integration , Key Pair Service Authentication , REST APIs , and metadata-driven Zero-Copy Clone Blue/Green Deployments .

For the SnowPro Advanced: Architect exam, you must understand how to architect end-to-end automated deployment pipelines, select the right database change management framework, secure headless CI/CD runners, and design zero-downtime schema release and rollback strategies.

DataOps Principles & Database Change Management (DCM)

Database Change Management (DCM) in Snowflake falls into two foundational architectural paradigms: Declarative Schema Management and Imperative / State-Machine Migration Scripts . ┌─────────────────────────────────────────────────────────────────────────────────┐

  • │ Database Change Management (DCM) Paradigms │
  • │ │
  • │ Declarative Paradigm (Desired State) Imperative Paradigm (Delta Steps)│
  • │ ┌───────────────────────────────────┐ ┌─────────────────────────────┐ │
  • │ │ Configuration: │ │ Migration Scripts: │ │
  • │ │ tables: │ │ • V1.1__create_customers.sql│ │
  • │ │ - name: customers │ │ • V1.2__add_status_col.sql │ │
  • │ │ columns: [id, name, status] │ │ • R__recreate_views.sql │ │
  • │ └─────────────────┬─────────────────┘ └──────────────┬──────────────┘ │
  • │ │ │ │
  • │ ▼ ▼ │
  • │ ┌───────────────────────────────────┐ ┌─────────────────────────────┐ │
  • │ │ Engine calculates delta & applies │ │ Engine executes new deltas; │ │
  • │ │ Tooling: Terraform, dbt │ │ tracks in CHANGE_HISTORY │ │
  • │ │ │ │ Tooling: schemachange, Flyway│ │
  • │ └───────────────────────────────────┘ └─────────────────────────────┘ │
  • └─────────────────────────────────────────────────────────────────────────────────┘
  • 1. Declarative Schema Management (State-Driven)

Core Concept : The engineer defines what the target architecture should look like in code (e.g., YAML, HCL, SQL models), not the step-by-step alter commands to get there.

Execution Engine : The tool inspects the current live Snowflake state, compares it with the declared target state in the Git repository, calculates the required delta, and applies the necessary DDL ( CREATE , ALTER , DROP ).

Primary Tools :

Terraform (Snowflake Provider) : Manages account-level and container-level infrastructure: virtual warehouses, resource monitors, users, roles, databases, schemas, and storage integrations.

dbt (data build tool) : Manages analytical transformation pipelines declaratively: tables, views, ephemeral models, and incremental updates driven by SELECT statements.

2. Imperative Versioned Migrations (Step-Driven)

Core Concept : Developers write discrete, ordered, immutable SQL migration scripts that represent the transition from version N N N to version N + 1 N+1 N + 1 .

Execution Engine : The tool tracks applied migrations in a dedicated metadata audit table (e.g., METADATA.CHANGE_HISTORY ). On every pipeline run, it scans the repository, identifies scripts whose version numbers exceed the highest applied version in the audit table, and executes them in strict sequence within a transaction.

Primary Tools : schemachange (lightweight Python-based DCM tool built specifically for Snowflake), Flyway , and Liquibase .

Standard Naming Conventions in Migration Frameworks

Migration frameworks rely on strict file naming conventions:

Versioned Scripts ( V<version>__<description>.sql ) : Executed exactly once in sequential order. Example: V1.1.0__create_orders_table.sql . If a versioned script is modified after execution, the pipeline fails with a checksum mismatch error to prevent drift.

Repeatable Scripts ( R__<description>.sql ) : Executed every time their contents or hash changes. Ideal for stateless objects such as views, secure views, user-defined functions (UDFs), and stored procedures. Example: R__customer_summary_view.sql .

Always Scripts ( A__<description>.sql ) : Executed on every deployment run regardless of modifications (e.g., auditing, permission reconciliations).

Snowflake Native Git Integration

Historically, deploying scripts stored in GitHub, GitLab, or Bitbucket into Snowflake required running external CI/CD agents (such as GitHub Actions runners or Jenkins slaves) that downloaded the code, installed Python drivers or SnowSQL, and streamed SQL commands over JDBC.

Snowflake simplifies this workflow through Native Git Integration , introducing first-class Git Repository Stages managed directly inside Snowflake. Snowflake Native Git Integration Architecture

  • ┌────────────────────────┐
  • │ Remote Git Repository │ (GitHub, GitLab, Bitbucket)
  • │ https://github.com/... │
  • └───────────┬────────────┘
  • │
  • │ HTTPS / Personal Access Token (PAT)
  • ▼
  • ┌────────────────────────────────────────────────────────────────────────┐
  • │ Snowflake Account │
  • │ │
  • │ [ SECRET ] [ API INTEGRATION ] │
  • │ TYPE = PASSWORD API_PROVIDER = git_https_api │
  • │ (Stores Git PAT Token) API_ALLOWED_PREFIXES = ('https://...') │
  • │ │ │ │
  • │ └───────────────┬───────────────┘ │
  • │ ▼ │
  • │ [ GIT REPOSITORY STAGE ] │
  • │ CREATE GIT REPOSITORY my_repo │
  • │ ORIGIN = 'https://github.com/acme/snowflake-dcm.git' │
  • │ │ │
  • │ ▼ │
  • │ ALTER GIT REPOSITORY my_repo FETCH; │
  • │ EXECUTE IMMEDIATE FROM @my_repo/branches/main/migrations/V1.sql; │
  • └────────────────────────────────────────────────────────────────────────┘
  • Step-by-Step Configuration Workflow

Configuring Snowflake Native Git Integration requires three coordinated security and integration objects:

Step 1: Create a Secret Object for Authentication

A Secret stores the Personal Access Token (PAT) or OAuth credentials required to authenticate with the remote Git provider over HTTPS: USE ROLE SECURITYADMIN;

  • CREATE OR REPLACE SECRET git_pat_secret
  • TYPE = PASSWORD
  • USERNAME = 'snowflake-deploy-bot'
  • PASSWORD = 'ghp_exampleTokenSecret9876543210'
  • COMMENT = 'Personal access token for GitHub CI/CD repository';
  • GRANT USAGE ON SECRET git_pat_secret TO ROLE deployment_role;
  • Step 2: Create an API Integration for Git HTTPS

An administrator creates an API Integration specifying the git_https_api provider and allowlisting the Git organization URL: USE ROLE ACCOUNTADMIN;

  • CREATE OR REPLACE API INTEGRATION git_api_integration
  • API_PROVIDER = git_https_api
  • API_ALLOWED_PREFIXES = ('https://github.com/acme-org/')
  • ALLOWED_AUTHENTICATION_SECRETS = (git_pat_secret) -- secrets this integration may use
  • ENABLED = TRUE;
  • GRANT USAGE ON INTEGRATION git_api_integration TO ROLE deployment_role;
  • Step 3: Create the Git Repository Object

The deployment engineer creates the Git repository object linking the origin URL, API integration, and authentication secret: USE ROLE deployment_role;

  • CREATE OR REPLACE GIT REPOSITORY dev_db.public.acme_dcm_repo
  • ORIGIN = 'https://github.com/acme-org/snowflake-dcm.git'
  • API_INTEGRATION = git_api_integration
  • GIT_CREDENTIALS = git_pat_secret;

Public repositories can be cloned with no authentication, and Snowflake also supports OAuth for interactive Workspaces users and private-link connectivity to self-hosted Git servers.

Git Operations and Execution Inside Snowflake

Once created, the Git repository acts as a special read-only stage. Snowflake provides native commands to synchronize and execute code directly from repository references: -- 1. Fetch latest commits, branches, and tags from remote repository

ALTER GIT REPOSITORY dev_db.public.acme_dcm_repo FETCH;

  • - 2. Inspect branches, tags, and files within the repository stage
  • SHOW GIT BRANCHES IN dev_db.public.acme_dcm_repo;
  • LIST @dev_db.public.acme_dcm_repo/branches/main;
  • LIST @dev_db.public.acme_dcm_repo/tags/v2.1.0/migrations/;
  • - 3. Execute a SQL script directly from a specific branch
  • EXECUTE IMMEDIATE FROM @dev_db.public.acme_dcm_repo/branches/main/migrations/V1.1__init_orders.sql;
  • - 4. Reference Python Snowpark code directly from a Git commit without file staging
  • CREATE OR REPLACE FUNCTION dev_db.public.calculate_tax(amount FLOAT)
  • RETURNS FLOAT
  • LANGUAGE PYTHON
  • RUNTIME_VERSION = '3.10'
  • IMPORTS = ('@dev_db.public.acme_dcm_repo/branches/main/src/python/tax_calc.py')
  • HANDLER = 'tax_calc.compute';

Automated CI/CD Pipelines & Security Architecture

Enterprise deployment pipelines must be autonomous, secure, and resilient. Automating Snowflake deployments via GitHub Actions, GitLab CI, Azure DevOps, or Jenkins introduces specific security requirements that frequently appear on the architect exam.

1. Headless Authentication: Key Pair Authentication

Interactive username/password logins and Multi-Factor Authentication (MFA) cannot be used in automated pipelines. Service accounts must authenticate using Asymmetric Key Pair Authentication (RSA 2048-bit or 4096-bit keys): # Generate an encrypted 2048-bit RSA private key

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -out rsa_key.p8

  • # Generate the corresponding public key in PEM format
  • openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

In Snowflake, an administrator associates the public key with the dedicated deployment service user: USE ROLE USERADMIN;

  • CREATE USER svc_github_actions_deployer
  • DEFAULT_ROLE = deployment_role
  • DEFAULT_WAREHOUSE = deploy_wh
  • TYPE = SERVICE
  • COMMENT = 'Service user for automated GitHub Actions CI/CD pipeline';
  • - Assign the public key payload (excluding headers and newlines)
  • ALTER USER svc_github_actions_deployer
  • SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA0t...';
  • 2. Network Policy Restrictions for CI/CD Runners

To adhere to zero-trust principles, service accounts must be restricted by a dedicated Network Policy . The network policy allowlists only the egress IP CIDR ranges of the CI/CD runners (e.g., self-hosted GitHub Actions runners or corporate VPC NAT gateways): USE ROLE SECURITYADMIN;

  • CREATE OR REPLACE NETWORK POLICY cicd_runner_allowlist
  • ALLOWED_IP_LIST = ('52.89.214.0/24', '34.212.85.0/24');
  • - Apply network policy specifically to the service user (overriding account policy)
  • ALTER USER svc_github_actions_deployer
  • SET NETWORK_POLICY = cicd_runner_allowlist;
  • 3. Snowflake CLI in Pipelines

The Snowflake CLI ( snow ) is Snowflake's open-source, developer-focused command-line tool and the recommended way to script deployments from CI/CD runners (SnowSQL remains available for SQL scripting). Typical pipeline steps:

snow connection test — verify the runner's connection (for example, a key-pair or workload-identity service user).

snow sql -f migrations/V3.0__schema_evolution.sql — run SQL scripts.

snow git fetch <repository> and snow git execute @<repository>/branches/main/migrations/ — use a Git repository object from the pipeline.

snow snowpark deploy , snow streamlit deploy , snow app run — deploy Snowpark objects, Streamlit apps, and Native Apps from project definition files.

4. Least Privilege Deployment Roles

Exam Trap: Never Use ACCOUNTADMIN in CI/CD Pipelines! A pervasive architectural antipattern is configuring automated deployment pipelines with ACCOUNTADMIN or SECURITYADMIN . If the pipeline runner or secret store is compromised, attackers gain full dominion over the entire Snowflake account.

Instead, architects create a dedicated functional role (e.g., CICD_DEPLOYER_ROLE ) with strictly scoped privileges:

USAGE on the deployment virtual warehouse ( deploy_wh ).

USAGE and CREATE SCHEMA on targeted non-production or production databases.

OWNERSHIP on specific application schemas or objects being managed by the DCM repository.

Explicitly omit account-level management privileges ( CREATE USER , CREATE ROLE , MANAGE GRANTS ).

Blue/Green Zero-Copy Clone Deployment Pattern

A signature capability of Snowflake is executing zero-downtime, instantaneous-rollback database deployments by combining Zero-Copy Cloning with the atomic Metadata Swap command ( ALTER DATABASE ... SWAP WITH ... ).

The Blue/Green Deployment Challenge

In traditional relational databases, applying major DDL changes (such as column restructuring, data backfills, and table constraints) requires placing the database in maintenance mode, taking locks that block incoming queries, or risking catastrophic failure halfway through a multi-table migration script.

  • Snowflake Blue/Green Architectural Workflow 1. Initial State: Production Active
  • [ prod_dw ] (Live Production Traffic: Reads & Writes)
  • 2. Instantaneous Zero-Copy Clone (Seed Green from Blue)
  • CREATE DATABASE prod_dw_green CLONE prod_dw;
  • [ prod_dw ] (Live Traffic) [ prod_dw_green ] (Isolated Deployment Target)
  • 3. Execute Schema Migrations & Data Transformations
  • Run schemachange / Flyway / dbt migrations directly against prod_dw_green
  • [ prod_dw ] (Live Traffic Unaffected) [ prod_dw_green ] (Migrated & Backfilled)
  • 4. Execute Automated Validation & Smoke Tests
  • Run integration test suite against prod_dw_green using isolated test warehouse
  • Ensure 100% test pass rate with zero disruption to production queries
  • 5. Atomic Cutover via Metadata Swap (Milliseconds Execution)
  • ALTER DATABASE prod_dw SWAP WITH prod_dw_green;
  • [ prod_dw ] ◄── (Now points to migrated green state; traffic seamlessly continues)
  • [ prod_dw_green ] ◄── (Now holds pre-migration historical snapshot)

6. Decision Fork:

  • ├── SUCCESS: Soak verification passes -> DROP DATABASE prod_dw_green;
  • └── REGRESSION: Bug detected -> ALTER DATABASE prod_dw SWAP WITH prod_dw_green; (Instant Rollback!)
  • Detailed Implementation Code -- Step 1: Switch to Deployment Role
  • USE ROLE deployment_role;
  • USE WAREHOUSE deploy_wh;
  • - Step 2: Clone production database to create isolated Green staging environment
  • CREATE OR REPLACE DATABASE prod_dw_green CLONE prod_dw;
  • - Step 3: Apply versioned migration scripts against Green database
  • - (Executed via CI/CD runner or EXECUTE IMMEDIATE FROM Git repository)
  • USE DATABASE prod_dw_green;
  • EXECUTE IMMEDIATE FROM @acme_dcm_repo/branches/release-3.0/migrations/V3.0__schema_evolution.sql;
  • - Step 4: Run automated integration tests against Green
  • CALL prod_dw_green.test_suite.run_regression_tests();
  • - Step 5: Execute atomic cutover (completes in milliseconds)
  • - Swaps all metadata pointers: prod_dw becomes the migrated state,

-- and prod_dw_green becomes the pre-migration rollback safety snapshot.

ALTER DATABASE prod_dw SWAP WITH prod_dw_green;

  • - ============================================================================
  • - EMERGENCY ROLLBACK RUNBOOK (Executed if telemetry reveals fatal production bugs)
  • - ============================================================================
  • - Instantaneous reversal: swaps pre-migration state back into production namespace
  • ALTER DATABASE prod_dw SWAP WITH prod_dw_green;
  • - ============================================================================
  • - POST-DEPLOYMENT CLEANUP (Executed after 24-48 hour soak verification period)
  • - ============================================================================
  • DROP DATABASE prod_dw_green;

Critical caveat — writes during the migration window: the green database is a snapshot taken at clone time. Any rows loaded into prod_dw after the clone and before the swap are not in green and effectively disappear from production after the swap (they end up in the old database). Pause ingestion and tasks during the window, or replay the changes into green before swapping. SWAP WITH also swaps grants and other metadata between the two databases, so verify grants after cutover.

Architectural Benefits of Blue/Green Cloning in Snowflake

Minimal Downtime : The SWAP WITH operation is an atomic metadata swap, so readers see either the old or the new state. Ingestion must still be paused (or changes replayed) for the migration window.

Production-Fidelity Testing : Because Green is cloned directly from live Blue, migration scripts and data transformations execute against real production data volumes and data skews, eliminating testing surprises.

Near-Zero Storage Overhead : Before migrations begin, Green consumes 0 GB of additional storage. During migration, only the modified or newly created micro-partitions consume additional storage.

Instantaneous Rollback : If unexpected runtime errors emerge 15 minutes post-deployment, executing ALTER DATABASE prod_dw SWAP WITH prod_dw_green; instantly restores the exact pre-migration state without restoring backups or replaying logs.

Loading diagram... Automated CI/CD Pipeline and Blue/Green Zero-Copy Clone Deployment Architecture