FinScience · June 2, 2026 06:16
diff --git a/gistfile1.txt b/gistfile1.txt
 # How to Build a CI/CD Pipeline for Snowflake Semantic Views and Cortex Agents with Azure DevOps

 *Automate the deployment of your AI-powered data layer — from Git push to production — with validation, approval gates, and zero manual Snowsight edits.*

 ---

 ## The Problem

 If you're building Snowflake Semantic Views (the metadata layer that powers Cortex Analyst) or Cortex Agents (AI assistants that answer natural language questions over your data), you're probably editing them manually in Snowsight.

 This works fine until:

 - Multiple people need to edit the same semantic view
 - A change breaks something and no one remembers what it looked like before
 - You need to promote changes from dev to production with confidence
 - An auditor asks "who changed this, when, and why?"

 The solution? Treat your semantic view YAML and agent specifications as **code**. Store them in Git. Deploy them through automated pipelines. Validate them before they reach users.

 This tutorial walks you through building a complete CI/CD pipeline that:

 1. **Validates** semantic view YAML on every pull request (dry-run, no object created)
 2. **Deploys** to a DEV environment automatically on merge
 3. **Waits for approval** before promoting to production
 4. **Deploys to PROD** after human sign-off

 I built and ran this pipeline end-to-end. Every command, script, and configuration in this article is verified working.

 ---

 ## What You'll Build

 ```
 Developer edits YAML → Push to Azure Repos → Pipeline triggers
    → Validate (dry-run) → Deploy DEV (auto) → Approve → Deploy PROD
 ```

 **Stack:**
 Snowflake (Semantic Views + Cortex Agents) · Azure DevOps (Repos + Pipelines) · Python (deployment scripts) · RSA key pair authentication (base64-encoded for CI/CD)

 ---

 ## Prerequisites

 - A Snowflake account
 - An Azure DevOps organization (free at https://dev.azure.com)
 - OpenSSL (pre-installed on Mac/Linux)
 - Git

 ---

 ## Step 1: Create the Snowflake Infrastructure

 First, set up a demo database with two schemas to simulate environments:

 ```sql
 CREATE DATABASE IF NOT EXISTS CICD_DEMO_DB;
 CREATE SCHEMA IF NOT EXISTS CICD_DEMO_DB.DEV;
 CREATE SCHEMA IF NOT EXISTS CICD_DEMO_DB.PROD;
 ```

 Create a sample table in both:

 ```sql
 CREATE OR REPLACE TABLE CICD_DEMO_DB.DEV.AR_INVOICES (
  INVOICE_ID VARCHAR(20),
  INVOICE_DATE DATE,
  CUSTOMER_NAME VARCHAR(100),
  BUSINESS_UNIT VARCHAR(50),
  INVOICE_AMOUNT_USD NUMBER(18,2),
  PAYMENT_STATUS VARCHAR(20),
  DAYS_OUTSTANDING NUMBER(10,0)
 );

 INSERT INTO CICD_DEMO_DB.DEV.AR_INVOICES VALUES
 ('INV-001', '2026-01-15', 'Acme Corp', 'New Equipment', 125000.00, 'PAID', 0),
 ('INV-002', '2026-02-01', 'BuildCo Ltd', 'Service', 45000.00, 'PAID', 0),
 ('INV-003', '2026-02-20', 'Metro Properties', 'Modernization', 320000.00, 'OVERDUE', 95);

 -- Mirror to PROD
 CREATE OR REPLACE TABLE CICD_DEMO_DB.PROD.AR_INVOICES AS
 SELECT * FROM CICD_DEMO_DB.DEV.AR_INVOICES;
 ```

 ---

 ## Step 2: Create the Service User and Role

 The pipeline needs a dedicated service account with minimal privileges:

 ```sql
 -- Create role
 CREATE ROLE IF NOT EXISTS CICD_DEPLOY_ROLE;

 -- Grant access
 GRANT USAGE ON DATABASE CICD_DEMO_DB TO ROLE CICD_DEPLOY_ROLE;
 GRANT USAGE ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
 GRANT USAGE ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
 GRANT CREATE SEMANTIC VIEW ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
 GRANT CREATE SEMANTIC VIEW ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
 GRANT CREATE AGENT ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
 GRANT CREATE AGENT ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
 GRANT SELECT ON ALL TABLES IN SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
 GRANT SELECT ON ALL TABLES IN SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
 GRANT SELECT ON FUTURE TABLES IN SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
 GRANT SELECT ON FUTURE TABLES IN SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
 GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE CICD_DEPLOY_ROLE;

 -- Create service user (no password — key pair only)
 CREATE USER IF NOT EXISTS CICD_DEPLOYER
  TYPE = SERVICE
  DEFAULT_ROLE = CICD_DEPLOY_ROLE
  DEFAULT_WAREHOUSE = COMPUTE_WH;

 GRANT ROLE CICD_DEPLOY_ROLE TO USER CICD_DEPLOYER;
 ```

 **Important:** If semantic views or agents already exist (created by another role), you must transfer ownership. Without this, `CREATE OR REPLACE` will fail with: *"Object already exists, but current role has no privileges on it."*

 ```sql
 GRANT OWNERSHIP ON SEMANTIC VIEW CICD_DEMO_DB.DEV.MY_VIEW 
  TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
 GRANT OWNERSHIP ON AGENT CICD_DEMO_DB.DEV.MY_AGENT 
  TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
 ```

 ---

 ## Step 3: Set Up Key Pair Authentication

 ```bash
 # Generate unencrypted private key
 openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

 # Extract public key
 openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
 ```

 Assign the public key to the service user:

 ```bash
 # Get the key body (no headers)
 grep -v "BEGIN\|END" rsa_key.pub | tr -d '\n'
 ```

 ```sql
 ALTER USER CICD_DEPLOYER SET RSA_PUBLIC_KEY = '<paste key body here>';
 ```

 ### The Base64 Trick (Critical for Azure DevOps)

 Azure DevOps secret variables **strip newlines** from multi-line values. If you paste a PEM key directly, it becomes corrupted. The fix:

 ```bash
 # Base64-encode the key (produces a single-line string)
 cat rsa_key.p8 | base64 | tr -d '\n'
 ```

 Save this string — you'll paste it into Azure DevOps. Your deployment script will detect and decode it automatically.

 ---

 ## Step 4: Create the Repository

 Here's the folder structure:

 ```
 snowflake-cicd-demo/
 ├── semantic-views/
 │   └── ar_invoice_analysis.yaml
 ├── agents/
 │   └── ar_demo_agent.yaml
 ├── scripts/
 │   ├── deploy.py
 │   └── validate.py
 ├── azure-pipelines.yml
 └── requirements.txt
 ```

 > **[IMAGE: repo_structure.png]** — Azure Repos file browser showing the folder tree

 ### Semantic View YAML (`semantic-views/ar_invoice_analysis.yaml`)

 ```yaml
 name: AR_INVOICE_ANALYSIS
 description: Accounts Receivable invoice analysis
 tables:
  - name: INVOICES
    base_table:
      database: ${DATABASE}
      schema: ${SCHEMA}
      table: AR_INVOICES
    primary_key:
      columns:
        - INVOICE_ID
    dimensions:
      - name: INVOICE_DATE
        expr: INVOICE_DATE
        data_type: DATE
      - name: CUSTOMER_NAME
        expr: CUSTOMER_NAME
        data_type: VARCHAR(100)
      - name: PAYMENT_STATUS
        expr: PAYMENT_STATUS
        data_type: VARCHAR(20)
    facts:
      - name: INVOICE_AMOUNT
        expr: INVOICE_AMOUNT_USD
        data_type: NUMBER(18,2)
    metrics:
      - name: TOTAL_REVENUE
        description: Sum of all invoice amounts
        expr: SUM(invoices.invoice_amount)
      - name: INVOICE_COUNT
        description: Total number of invoices
        expr: COUNT(invoices.invoice_id)
 relationships: []
 ```

 The `${DATABASE}` and `${SCHEMA}` placeholders are replaced at deploy time — the same file works for DEV and PROD.

 ### Agent Specification (`agents/ar_demo_agent.yaml`)

 ```yaml
 models:
  orchestration: claude-4-sonnet

 orchestration:
  budget:
    seconds: 30
    tokens: 16000

 instructions:
  response: "You are an AR analyst. Provide concise answers about invoices and revenue."
  orchestration: "Use the Analyst tool for all invoice and payment questions."

 tools:
  - tool_spec:
      type: "cortex_analyst_text_to_sql"
      name: "ARAnalyst"
      description: "Converts natural language to SQL for AR analysis"

 tool_resources:
  ARAnalyst:
    semantic_view: "${DATABASE}.${SCHEMA}.AR_INVOICE_ANALYSIS"
 ```

 ### Deployment Script (`scripts/deploy.py`)

 ```python
 import os
 import sys
 import glob
 import base64
 from cryptography.hazmat.primitives import serialization
 from cryptography.hazmat.backends import default_backend
 import snowflake.connector


 def get_private_key():
    private_key_raw = os.environ['SNOWFLAKE_PRIVATE_KEY_RAW']
    # Auto-detect: base64-encoded or raw PEM
    if not private_key_raw.startswith('-----'):
        private_key_raw = base64.b64decode(private_key_raw).decode('utf-8')
    p_key = serialization.load_pem_private_key(
        private_key_raw.encode(), password=None, backend=default_backend()
    )
    return p_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption()
    )


 def get_connection():
    return snowflake.connector.connect(
        account=os.environ['SNOWFLAKE_ACCOUNT'],
        user=os.environ['SNOWFLAKE_USER'],
        private_key=get_private_key(),
        warehouse=os.environ['SF_WAREHOUSE'],
        role=os.environ['SF_ROLE']
    )


 def deploy_semantic_views(conn, database, schema):
    for yaml_file in sorted(glob.glob('semantic-views/*.yaml')):
        print(f"Deploying: {yaml_file}")
        with open(yaml_file, 'r') as f:
            yaml_content = f.read()
        yaml_content = yaml_content.replace('${DATABASE}', database)
        yaml_content = yaml_content.replace('${SCHEMA}', schema)

        sql = f"""CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
            '{database}.{schema}', $${yaml_content}$$);"""
        cursor = conn.cursor()
        cursor.execute(sql)
        print(f"  Result: {cursor.fetchone()[0]}")
        cursor.close()


 def deploy_agents(conn, database, schema, build_id='local'):
    for yaml_file in sorted(glob.glob('agents/*.yaml')):
        agent_name = os.path.basename(yaml_file).replace('.yaml', '').upper()
        print(f"Deploying agent: {agent_name}")
        with open(yaml_file, 'r') as f:
            spec = f.read()
        spec = spec.replace('${DATABASE}', database)
        spec = spec.replace('${SCHEMA}', schema)

        sql = f"""CREATE OR REPLACE AGENT {database}.{schema}.{agent_name}
            COMMENT = 'Deployed via CI/CD - Build {build_id}'
            FROM SPECIFICATION $${spec}$$;"""
        cursor = conn.cursor()
        cursor.execute(sql)
        print(f"  Result: {cursor.fetchone()[0]}")
        cursor.close()


 def main():
    database = os.environ['SF_DATABASE']
    schema = os.environ['SF_SCHEMA']
    build_id = os.environ.get('BUILD_BUILDID', 'local')
    conn = get_connection()
    print(f"Connected. Deploying to {database}.{schema}")
    deploy_semantic_views(conn, database, schema)
    deploy_agents(conn, database, schema, build_id)
    conn.close()
    print("Deployment complete.")


 if __name__ == '__main__':
    main()
 ```

 ### Pipeline YAML (`azure-pipelines.yml`)

 ```yaml
 trigger:
  branches:
    include:
      - main
  paths:
    include:
      - semantic-views/*
      - agents/*

 pool:
  vmImage: ubuntu-latest

 variables:
  - group: snowflake-credentials

 stages:
  - stage: Validate
    displayName: 'Validate YAML'
    variables:
      - group: snowflake-dev
    jobs:
      - job: ValidateJob
        steps:
          - task: UsePythonVersion@0
            inputs:
              versionSpec: '3.11'
          - script: pip install snowflake-connector-python cryptography
            displayName: 'Install Dependencies'
          - script: python scripts/validate.py
            displayName: 'Validate Semantic Views'
            env:
              SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
              SNOWFLAKE_USER: $(SNOWFLAKE_USER)
              SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
              SF_DATABASE: $(SF_DATABASE)
              SF_SCHEMA: $(SF_SCHEMA)
              SF_WAREHOUSE: $(SF_WAREHOUSE)
              SF_ROLE: $(SF_ROLE)

  - stage: DeployDev
    displayName: 'Deploy to DEV'
    dependsOn: Validate
    condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))
    variables:
      - group: snowflake-dev
    jobs:
      - deployment: DeployDevJob
        environment: dev
        strategy:
          runOnce:
            deploy:
              steps:
                - checkout: self
                - task: UsePythonVersion@0
                  inputs:
                    versionSpec: '3.11'
                - script: pip install snowflake-connector-python cryptography
                  displayName: 'Install Dependencies'
                - script: python scripts/deploy.py
                  displayName: 'Deploy to DEV'
                  env:
                    SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
                    SNOWFLAKE_USER: $(SNOWFLAKE_USER)
                    SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
                    SF_DATABASE: $(SF_DATABASE)
                    SF_SCHEMA: $(SF_SCHEMA)
                    SF_WAREHOUSE: $(SF_WAREHOUSE)
                    SF_ROLE: $(SF_ROLE)
                    BUILD_BUILDID: $(Build.BuildId)

  - stage: DeployProd
    displayName: 'Deploy to PROD'
    dependsOn: DeployDev
    condition: succeeded()
    variables:
      - group: snowflake-prod
    jobs:
      - deployment: DeployProdJob
        environment: prod
        strategy:
          runOnce:
            deploy:
              steps:
                - checkout: self
                - task: UsePythonVersion@0
                  inputs:
                    versionSpec: '3.11'
                - script: pip install snowflake-connector-python cryptography
                  displayName: 'Install Dependencies'
                - script: python scripts/deploy.py
                  displayName: 'Deploy to PROD'
                  env:
                    SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
                    SNOWFLAKE_USER: $(SNOWFLAKE_USER)
                    SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
                    SF_DATABASE: $(SF_DATABASE)
                    SF_SCHEMA: $(SF_SCHEMA)
                    SF_WAREHOUSE: $(SF_WAREHOUSE)
                    SF_ROLE: $(SF_ROLE)
                    BUILD_BUILDID: $(Build.BuildId)
 ```

 ---

 ## Step 5: Configure Azure DevOps

 ### Variable Groups

 Create three variable groups in **Pipelines → Library**:

 **snowflake-credentials** (shared across stages):
 `SNOWFLAKE_ACCOUNT`, `SNOWFLAKE_USER`, `SNOWFLAKE_PRIVATE_KEY_RAW` (base64-encoded, marked as secret)

 **snowflake-dev** (environment-specific):
 `SF_DATABASE=CICD_DEMO_DB`, `SF_SCHEMA=DEV`, `SF_WAREHOUSE=COMPUTE_WH`, `SF_ROLE=CICD_DEPLOY_ROLE`

 **snowflake-prod** (environment-specific):
 `SF_DATABASE=CICD_DEMO_DB`, `SF_SCHEMA=PROD`, `SF_WAREHOUSE=COMPUTE_WH`, `SF_ROLE=CICD_DEPLOY_ROLE`

 > **[IMAGE: variable_groups.png]** — Three variable groups in Pipelines Library

 ### Environments with Approval

 1. Go to **Pipelines → Environments**
 2. Create `dev` (no approval) and `prod` (add yourself as approver)

 > **[IMAGE: prod_approval_config.png]** — Approval check on the prod environment

 When the pipeline reaches `prod`, it pauses and waits for you to click **Approve**.

 ---

 ## Step 6: Push and Run

 ```bash
 git init && git add -A && git commit -m "Initial commit"
 git push -u "https://<user>:<PAT>@dev.azure.com/<org>/<project>/_git/<repo>" main
 ```

 Create the pipeline: **Pipelines → New → Azure Repos Git → Existing YAML file → `/azure-pipelines.yml`**

 On first run, click **Permit** when asked to authorize variable groups.

 ### Expected Result

 ```
 ✓ Validate YAML          → "All semantic views validated successfully"
 ✓ Deploy to DEV          → "Semantic view was successfully created" + "Agent deployed"
 ⏸ Deploy to PROD         → Waiting for approval...
 ✓ Deploy to PROD         → Same output after you click Approve
 ```

 > **[IMAGE: pipeline_stages.png]** — All three stages green: Validate → Deploy DEV → Deploy PROD

 Verify in Snowflake:

 ```sql
 SHOW SEMANTIC VIEWS IN SCHEMA CICD_DEMO_DB.PROD;
 SHOW AGENTS IN SCHEMA CICD_DEMO_DB.PROD;
 ```

 Both should show `OWNER = CICD_DEPLOY_ROLE` and the agent's comment contains the build ID.

 > **[IMAGE: snowflake_verification.png]** — SHOW AGENTS result confirming deployment with build ID in comment

 ---

 ## Gotchas & Lessons Learned

 These are the issues I hit during implementation. Each took 15–30 minutes to debug. Save yourself the trouble:

 ### 1. `ValueError: Unable to load PEM file. MalformedFraming`

 **Cause:** Azure DevOps strips newlines from secret variables. Your PEM key becomes one long broken line.

 **Fix:** Base64-encode the key before storing. Decode at runtime:

 ```python
 if not private_key_raw.startswith('-----'):
    private_key_raw = base64.b64decode(private_key_raw).decode('utf-8')
 ```

 ### 2. `Incoming request with IP/Token X.X.X.X is not allowed to access Snowflake`

 **Cause:** Your Snowflake account has a network policy. Azure DevOps hosted agents use **rotating IPs** — a different IP every single pipeline run.

 **Options:**

 **For production:** Use a self-hosted agent with a fixed IP.
 **For demos:** Temporarily add broad Azure CIDRs to your network policy.
 **Long-term:** Use Workload Identity Federation (OIDC) — eliminates stored secrets entirely.

 I saw IPs from `20.204.x.x`, `40.81.x.x`, and `4.240.x.x` across just 4 pipeline runs. Individual IP allowlisting is not viable with hosted agents.

 ### 3. `Object already exists, but current role has no privileges on it`

 **Cause:** The semantic view or agent was created by ACCOUNTADMIN. Your CI/CD role can't `CREATE OR REPLACE` objects it doesn't own.

 **Fix:** Transfer ownership before running the pipeline:

 ```sql
 GRANT OWNERSHIP ON AGENT my_db.my_schema.MY_AGENT 
  TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
 ```

 ### 4. Why Python instead of `snow sql` CLI?

 I initially tried shell scripts with `snow sql -q "CALL ...$YAML_CONTENT..."`. It breaks immediately with real YAML (quotes, dollar signs, special characters). Python with the `snowflake-connector-python` handles multi-line YAML content cleanly via `$$` dollar-quoting — no escaping needed.

 ---

 ## What's Next

 This pipeline covers the basics: validate → deploy → approve → promote. For production use, consider adding:

 - **Evaluation gates** — Run verified queries after deployment, block promotion if accuracy drops below a threshold
 - **Three environments** (DEV → QA → PROD) — Separate integration testing from production
 - **Workload Identity Federation (OIDC)** — Eliminates stored secrets entirely; Azure DevOps gets a short-lived token that Snowflake validates directly
 - **Rollback automation** — On failure, automatically re-deploy the previous Git tag

 ---

 ## Summary

 - **Store definitions** → YAML files in Azure Repos
 - **Validate on PR** → `SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(..., TRUE)` (dry-run mode)
 - **Deploy** → Python script with `snowflake-connector-python`
 - **Authenticate** → RSA key pair, base64-encoded for Azure DevOps
 - **Promote to PROD** → Azure DevOps environment with approval gate
 - **Handle network policy** → Self-hosted agent (production) or broad CIDR (demo)

 The full working code is available as a starter repo. Clone it, replace the account details, and you have a working pipeline in under an hour.

 ---

 *If you found this useful, follow me for more Snowflake + AI engineering content. Questions? Drop a comment — I've already hit every error so you don't have to.*

 ---

 **Tags:** `Snowflake` · `Azure DevOps` · `CI/CD` · `Data Engineering` · `Cortex AI`
	# How to Build a CI/CD Pipeline for Snowflake Semantic Views and Cortex Agents with Azure DevOps

	Automate the deployment of your AI-powered data layer — from Git push to production — with validation, approval gates, and zero manual Snowsight edits.

	---

	## The Problem

	If you're building Snowflake Semantic Views (the metadata layer that powers Cortex Analyst) or Cortex Agents (AI assistants that answer natural language questions over your data), you're probably editing them manually in Snowsight.

	This works fine until:

	- Multiple people need to edit the same semantic view
	- A change breaks something and no one remembers what it looked like before
	- You need to promote changes from dev to production with confidence
	- An auditor asks "who changed this, when, and why?"

	The solution? Treat your semantic view YAML and agent specifications as code. Store them in Git. Deploy them through automated pipelines. Validate them before they reach users.

	This tutorial walks you through building a complete CI/CD pipeline that:

	1. Validates semantic view YAML on every pull request (dry-run, no object created)
	2. Deploys to a DEV environment automatically on merge
	3. Waits for approval before promoting to production
	4. Deploys to PROD after human sign-off

	I built and ran this pipeline end-to-end. Every command, script, and configuration in this article is verified working.

	---

	## What You'll Build

	```
	Developer edits YAML → Push to Azure Repos → Pipeline triggers
	→ Validate (dry-run) → Deploy DEV (auto) → Approve → Deploy PROD
	```

	Stack:
	Snowflake (Semantic Views + Cortex Agents) · Azure DevOps (Repos + Pipelines) · Python (deployment scripts) · RSA key pair authentication (base64-encoded for CI/CD)

	---

	## Prerequisites

	- A Snowflake account
	- An Azure DevOps organization (free at https://dev.azure.com)
	- OpenSSL (pre-installed on Mac/Linux)
	- Git

	---

	## Step 1: Create the Snowflake Infrastructure

	First, set up a demo database with two schemas to simulate environments:

	```sql
	CREATE DATABASE IF NOT EXISTS CICD_DEMO_DB;
	CREATE SCHEMA IF NOT EXISTS CICD_DEMO_DB.DEV;
	CREATE SCHEMA IF NOT EXISTS CICD_DEMO_DB.PROD;
	```

	Create a sample table in both:

	```sql
	CREATE OR REPLACE TABLE CICD_DEMO_DB.DEV.AR_INVOICES (
	INVOICE_ID VARCHAR(20),
	INVOICE_DATE DATE,
	CUSTOMER_NAME VARCHAR(100),
	BUSINESS_UNIT VARCHAR(50),
	INVOICE_AMOUNT_USD NUMBER(18,2),
	PAYMENT_STATUS VARCHAR(20),
	DAYS_OUTSTANDING NUMBER(10,0)
	);

	INSERT INTO CICD_DEMO_DB.DEV.AR_INVOICES VALUES
	('INV-001', '2026-01-15', 'Acme Corp', 'New Equipment', 125000.00, 'PAID', 0),
	('INV-002', '2026-02-01', 'BuildCo Ltd', 'Service', 45000.00, 'PAID', 0),
	('INV-003', '2026-02-20', 'Metro Properties', 'Modernization', 320000.00, 'OVERDUE', 95);

	-- Mirror to PROD
	CREATE OR REPLACE TABLE CICD_DEMO_DB.PROD.AR_INVOICES AS
	SELECT * FROM CICD_DEMO_DB.DEV.AR_INVOICES;
	```

	---

	## Step 2: Create the Service User and Role

	The pipeline needs a dedicated service account with minimal privileges:

	```sql
	-- Create role
	CREATE ROLE IF NOT EXISTS CICD_DEPLOY_ROLE;

	-- Grant access
	GRANT USAGE ON DATABASE CICD_DEMO_DB TO ROLE CICD_DEPLOY_ROLE;
	GRANT USAGE ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
	GRANT USAGE ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
	GRANT CREATE SEMANTIC VIEW ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
	GRANT CREATE SEMANTIC VIEW ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
	GRANT CREATE AGENT ON SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
	GRANT CREATE AGENT ON SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
	GRANT SELECT ON ALL TABLES IN SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
	GRANT SELECT ON ALL TABLES IN SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
	GRANT SELECT ON FUTURE TABLES IN SCHEMA CICD_DEMO_DB.DEV TO ROLE CICD_DEPLOY_ROLE;
	GRANT SELECT ON FUTURE TABLES IN SCHEMA CICD_DEMO_DB.PROD TO ROLE CICD_DEPLOY_ROLE;
	GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE CICD_DEPLOY_ROLE;

	-- Create service user (no password — key pair only)
	CREATE USER IF NOT EXISTS CICD_DEPLOYER
	TYPE = SERVICE
	DEFAULT_ROLE = CICD_DEPLOY_ROLE
	DEFAULT_WAREHOUSE = COMPUTE_WH;

	GRANT ROLE CICD_DEPLOY_ROLE TO USER CICD_DEPLOYER;
	```

	Important: If semantic views or agents already exist (created by another role), you must transfer ownership. Without this, `CREATE OR REPLACE` will fail with: "Object already exists, but current role has no privileges on it."

	```sql
	GRANT OWNERSHIP ON SEMANTIC VIEW CICD_DEMO_DB.DEV.MY_VIEW
	TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
	GRANT OWNERSHIP ON AGENT CICD_DEMO_DB.DEV.MY_AGENT
	TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
	```

	---

	## Step 3: Set Up Key Pair Authentication

	```bash
	# Generate unencrypted private key
	openssl genrsa 2048 \| openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

	# Extract public key
	openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
	```

	Assign the public key to the service user:

	```bash
	# Get the key body (no headers)
	grep -v "BEGIN\\|END" rsa_key.pub \| tr -d '\n'
	```

	```sql
	ALTER USER CICD_DEPLOYER SET RSA_PUBLIC_KEY = '<paste key body here>';
	```

	### The Base64 Trick (Critical for Azure DevOps)

	Azure DevOps secret variables strip newlines from multi-line values. If you paste a PEM key directly, it becomes corrupted. The fix:

	```bash
	# Base64-encode the key (produces a single-line string)
	cat rsa_key.p8 \| base64 \| tr -d '\n'
	```

	Save this string — you'll paste it into Azure DevOps. Your deployment script will detect and decode it automatically.

	---

	## Step 4: Create the Repository

	Here's the folder structure:

	```
	snowflake-cicd-demo/
	├── semantic-views/
	│ └── ar_invoice_analysis.yaml
	├── agents/
	│ └── ar_demo_agent.yaml
	├── scripts/
	│ ├── deploy.py
	│ └── validate.py
	├── azure-pipelines.yml
	└── requirements.txt
	```

	> [IMAGE: repo_structure.png] — Azure Repos file browser showing the folder tree

	### Semantic View YAML (`semantic-views/ar_invoice_analysis.yaml`)

	```yaml
	name: AR_INVOICE_ANALYSIS
	description: Accounts Receivable invoice analysis
	tables:
	- name: INVOICES
	base_table:
	database: ${DATABASE}
	schema: ${SCHEMA}
	table: AR_INVOICES
	primary_key:
	columns:
	- INVOICE_ID
	dimensions:
	- name: INVOICE_DATE
	expr: INVOICE_DATE
	data_type: DATE
	- name: CUSTOMER_NAME
	expr: CUSTOMER_NAME
	data_type: VARCHAR(100)
	- name: PAYMENT_STATUS
	expr: PAYMENT_STATUS
	data_type: VARCHAR(20)
	facts:
	- name: INVOICE_AMOUNT
	expr: INVOICE_AMOUNT_USD
	data_type: NUMBER(18,2)
	metrics:
	- name: TOTAL_REVENUE
	description: Sum of all invoice amounts
	expr: SUM(invoices.invoice_amount)
	- name: INVOICE_COUNT
	description: Total number of invoices
	expr: COUNT(invoices.invoice_id)
	relationships: []
	```

	The `${DATABASE}` and `${SCHEMA}` placeholders are replaced at deploy time — the same file works for DEV and PROD.

	### Agent Specification (`agents/ar_demo_agent.yaml`)

	```yaml
	models:
	orchestration: claude-4-sonnet

	orchestration:
	budget:
	seconds: 30
	tokens: 16000

	instructions:
	response: "You are an AR analyst. Provide concise answers about invoices and revenue."
	orchestration: "Use the Analyst tool for all invoice and payment questions."

	tools:
	- tool_spec:
	type: "cortex_analyst_text_to_sql"
	name: "ARAnalyst"
	description: "Converts natural language to SQL for AR analysis"

	tool_resources:
	ARAnalyst:
	semantic_view: "${DATABASE}.${SCHEMA}.AR_INVOICE_ANALYSIS"
	```

	### Deployment Script (`scripts/deploy.py`)

	```python
	import os
	import sys
	import glob
	import base64
	from cryptography.hazmat.primitives import serialization
	from cryptography.hazmat.backends import default_backend
	import snowflake.connector


	def get_private_key():
	private_key_raw = os.environ['SNOWFLAKE_PRIVATE_KEY_RAW']
	# Auto-detect: base64-encoded or raw PEM
	if not private_key_raw.startswith('-----'):
	private_key_raw = base64.b64decode(private_key_raw).decode('utf-8')
	p_key = serialization.load_pem_private_key(
	private_key_raw.encode(), password=None, backend=default_backend()
	)
	return p_key.private_bytes(
	encoding=serialization.Encoding.DER,
	format=serialization.PrivateFormat.PKCS8,
	encryption_algorithm=serialization.NoEncryption()
	)


	def get_connection():
	return snowflake.connector.connect(
	account=os.environ['SNOWFLAKE_ACCOUNT'],
	user=os.environ['SNOWFLAKE_USER'],
	private_key=get_private_key(),
	warehouse=os.environ['SF_WAREHOUSE'],
	role=os.environ['SF_ROLE']
	)


	def deploy_semantic_views(conn, database, schema):
	for yaml_file in sorted(glob.glob('semantic-views/*.yaml')):
	print(f"Deploying: {yaml_file}")
	with open(yaml_file, 'r') as f:
	yaml_content = f.read()
	yaml_content = yaml_content.replace('${DATABASE}', database)
	yaml_content = yaml_content.replace('${SCHEMA}', schema)

	sql = f"""CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
	'{database}.{schema}', $${yaml_content}$$);"""
	cursor = conn.cursor()
	cursor.execute(sql)
	print(f" Result: {cursor.fetchone()[0]}")
	cursor.close()


	def deploy_agents(conn, database, schema, build_id='local'):
	for yaml_file in sorted(glob.glob('agents/*.yaml')):
	agent_name = os.path.basename(yaml_file).replace('.yaml', '').upper()
	print(f"Deploying agent: {agent_name}")
	with open(yaml_file, 'r') as f:
	spec = f.read()
	spec = spec.replace('${DATABASE}', database)
	spec = spec.replace('${SCHEMA}', schema)

	sql = f"""CREATE OR REPLACE AGENT {database}.{schema}.{agent_name}
	COMMENT = 'Deployed via CI/CD - Build {build_id}'
	FROM SPECIFICATION $${spec}$$;"""
	cursor = conn.cursor()
	cursor.execute(sql)
	print(f" Result: {cursor.fetchone()[0]}")
	cursor.close()


	def main():
	database = os.environ['SF_DATABASE']
	schema = os.environ['SF_SCHEMA']
	build_id = os.environ.get('BUILD_BUILDID', 'local')
	conn = get_connection()
	print(f"Connected. Deploying to {database}.{schema}")
	deploy_semantic_views(conn, database, schema)
	deploy_agents(conn, database, schema, build_id)
	conn.close()
	print("Deployment complete.")


	if __name__ == '__main__':
	main()
	```

	### Pipeline YAML (`azure-pipelines.yml`)

	```yaml
	trigger:
	branches:
	include:
	- main
	paths:
	include:
	- semantic-views/*
	- agents/*

	pool:
	vmImage: ubuntu-latest

	variables:
	- group: snowflake-credentials

	stages:
	- stage: Validate
	displayName: 'Validate YAML'
	variables:
	- group: snowflake-dev
	jobs:
	- job: ValidateJob
	steps:
	- task: UsePythonVersion@0
	inputs:
	versionSpec: '3.11'
	- script: pip install snowflake-connector-python cryptography
	displayName: 'Install Dependencies'
	- script: python scripts/validate.py
	displayName: 'Validate Semantic Views'
	env:
	SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
	SNOWFLAKE_USER: $(SNOWFLAKE_USER)
	SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
	SF_DATABASE: $(SF_DATABASE)
	SF_SCHEMA: $(SF_SCHEMA)
	SF_WAREHOUSE: $(SF_WAREHOUSE)
	SF_ROLE: $(SF_ROLE)

	- stage: DeployDev
	displayName: 'Deploy to DEV'
	dependsOn: Validate
	condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))
	variables:
	- group: snowflake-dev
	jobs:
	- deployment: DeployDevJob
	environment: dev
	strategy:
	runOnce:
	deploy:
	steps:
	- checkout: self
	- task: UsePythonVersion@0
	inputs:
	versionSpec: '3.11'
	- script: pip install snowflake-connector-python cryptography
	displayName: 'Install Dependencies'
	- script: python scripts/deploy.py
	displayName: 'Deploy to DEV'
	env:
	SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
	SNOWFLAKE_USER: $(SNOWFLAKE_USER)
	SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
	SF_DATABASE: $(SF_DATABASE)
	SF_SCHEMA: $(SF_SCHEMA)
	SF_WAREHOUSE: $(SF_WAREHOUSE)
	SF_ROLE: $(SF_ROLE)
	BUILD_BUILDID: $(Build.BuildId)

	- stage: DeployProd
	displayName: 'Deploy to PROD'
	dependsOn: DeployDev
	condition: succeeded()
	variables:
	- group: snowflake-prod
	jobs:
	- deployment: DeployProdJob
	environment: prod
	strategy:
	runOnce:
	deploy:
	steps:
	- checkout: self
	- task: UsePythonVersion@0
	inputs:
	versionSpec: '3.11'
	- script: pip install snowflake-connector-python cryptography
	displayName: 'Install Dependencies'
	- script: python scripts/deploy.py
	displayName: 'Deploy to PROD'
	env:
	SNOWFLAKE_ACCOUNT: $(SNOWFLAKE_ACCOUNT)
	SNOWFLAKE_USER: $(SNOWFLAKE_USER)
	SNOWFLAKE_PRIVATE_KEY_RAW: $(SNOWFLAKE_PRIVATE_KEY_RAW)
	SF_DATABASE: $(SF_DATABASE)
	SF_SCHEMA: $(SF_SCHEMA)
	SF_WAREHOUSE: $(SF_WAREHOUSE)
	SF_ROLE: $(SF_ROLE)
	BUILD_BUILDID: $(Build.BuildId)
	```

	---

	## Step 5: Configure Azure DevOps

	### Variable Groups

	Create three variable groups in Pipelines → Library:

	snowflake-credentials (shared across stages):
	`SNOWFLAKE_ACCOUNT`, `SNOWFLAKE_USER`, `SNOWFLAKE_PRIVATE_KEY_RAW` (base64-encoded, marked as secret)

	snowflake-dev (environment-specific):
	`SF_DATABASE=CICD_DEMO_DB`, `SF_SCHEMA=DEV`, `SF_WAREHOUSE=COMPUTE_WH`, `SF_ROLE=CICD_DEPLOY_ROLE`

	snowflake-prod (environment-specific):
	`SF_DATABASE=CICD_DEMO_DB`, `SF_SCHEMA=PROD`, `SF_WAREHOUSE=COMPUTE_WH`, `SF_ROLE=CICD_DEPLOY_ROLE`

	> [IMAGE: variable_groups.png] — Three variable groups in Pipelines Library

	### Environments with Approval

	1. Go to Pipelines → Environments
	2. Create `dev` (no approval) and `prod` (add yourself as approver)

	> [IMAGE: prod_approval_config.png] — Approval check on the prod environment

	When the pipeline reaches `prod`, it pauses and waits for you to click Approve.

	---

	## Step 6: Push and Run

	```bash
	git init && git add -A && git commit -m "Initial commit"
	git push -u "https://<user>:<PAT>@dev.azure.com/<org>/<project>/_git/<repo>" main
	```

	Create the pipeline: Pipelines → New → Azure Repos Git → Existing YAML file → `/azure-pipelines.yml`

	On first run, click Permit when asked to authorize variable groups.

	### Expected Result

	```
	✓ Validate YAML → "All semantic views validated successfully"
	✓ Deploy to DEV → "Semantic view was successfully created" + "Agent deployed"
	⏸ Deploy to PROD → Waiting for approval...
	✓ Deploy to PROD → Same output after you click Approve
	```

	> [IMAGE: pipeline_stages.png] — All three stages green: Validate → Deploy DEV → Deploy PROD

	Verify in Snowflake:

	```sql
	SHOW SEMANTIC VIEWS IN SCHEMA CICD_DEMO_DB.PROD;
	SHOW AGENTS IN SCHEMA CICD_DEMO_DB.PROD;
	```

	Both should show `OWNER = CICD_DEPLOY_ROLE` and the agent's comment contains the build ID.

	> [IMAGE: snowflake_verification.png] — SHOW AGENTS result confirming deployment with build ID in comment

	---

	## Gotchas & Lessons Learned

	These are the issues I hit during implementation. Each took 15–30 minutes to debug. Save yourself the trouble:

	### 1. `ValueError: Unable to load PEM file. MalformedFraming`

	Cause: Azure DevOps strips newlines from secret variables. Your PEM key becomes one long broken line.

	Fix: Base64-encode the key before storing. Decode at runtime:

	```python
	if not private_key_raw.startswith('-----'):
	private_key_raw = base64.b64decode(private_key_raw).decode('utf-8')
	```

	### 2. `Incoming request with IP/Token X.X.X.X is not allowed to access Snowflake`

	Cause: Your Snowflake account has a network policy. Azure DevOps hosted agents use rotating IPs — a different IP every single pipeline run.

	Options:

	For production: Use a self-hosted agent with a fixed IP.
	For demos: Temporarily add broad Azure CIDRs to your network policy.
	Long-term: Use Workload Identity Federation (OIDC) — eliminates stored secrets entirely.

	I saw IPs from `20.204.x.x`, `40.81.x.x`, and `4.240.x.x` across just 4 pipeline runs. Individual IP allowlisting is not viable with hosted agents.

	### 3. `Object already exists, but current role has no privileges on it`

	Cause: The semantic view or agent was created by ACCOUNTADMIN. Your CI/CD role can't `CREATE OR REPLACE` objects it doesn't own.

	Fix: Transfer ownership before running the pipeline:

	```sql
	GRANT OWNERSHIP ON AGENT my_db.my_schema.MY_AGENT
	TO ROLE CICD_DEPLOY_ROLE COPY CURRENT GRANTS;
	```

	### 4. Why Python instead of `snow sql` CLI?

	I initially tried shell scripts with `snow sql -q "CALL ...$YAML_CONTENT..."`. It breaks immediately with real YAML (quotes, dollar signs, special characters). Python with the `snowflake-connector-python` handles multi-line YAML content cleanly via `$$` dollar-quoting — no escaping needed.

	---

	## What's Next

	This pipeline covers the basics: validate → deploy → approve → promote. For production use, consider adding:

	- Evaluation gates — Run verified queries after deployment, block promotion if accuracy drops below a threshold
	- Three environments (DEV → QA → PROD) — Separate integration testing from production
	- Workload Identity Federation (OIDC) — Eliminates stored secrets entirely; Azure DevOps gets a short-lived token that Snowflake validates directly
	- Rollback automation — On failure, automatically re-deploy the previous Git tag

	---

	## Summary

	- Store definitions → YAML files in Azure Repos
	- Validate on PR → `SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(..., TRUE)` (dry-run mode)
	- Deploy → Python script with `snowflake-connector-python`
	- Authenticate → RSA key pair, base64-encoded for Azure DevOps
	- Promote to PROD → Azure DevOps environment with approval gate
	- Handle network policy → Self-hosted agent (production) or broad CIDR (demo)

	The full working code is available as a starter repo. Clone it, replace the account details, and you have a working pipeline in under an hour.

	---

	If you found this useful, follow me for more Snowflake + AI engineering content. Questions? Drop a comment — I've already hit every error so you don't have to.

	---

	Tags: `Snowflake` · `Azure DevOps` · `CI/CD` · `Data Engineering` · `Cortex AI`
No results found