Snowflake Advanced · Interview Prep

CI/CD & Code Promotion

Difficulty: Medium-Hard · Commonly asked at Microsoft, Google, Amazon

Interview Question

"How do you implement a CI/CD pipeline for Snowflake schema changes? Walk me through the process of promoting code from dev to staging to production."

ℹ️

Companies Asking This: Microsoft (Principal Data Engineer), Google (Senior Cloud Engineer), Amazon (L6 Data Engineer), Meta (Data Platform Engineer)

CI/CD Architecture

Snowflake CLI (snowsql) & Code Management

# Install SnowSQL
pip install snowflake-cli

# Connect to Snowflake
snowsql -a my_account -u my_user -r my_role

# List databases
snowsql -q "SHOW DATABASES"

# Execute SQL file
snowsql -f scripts/create_tables.sql

# Execute with variables
snowsql -q "CREATE DATABASE {{database_name}}" -D database_name=my_db

Schema-as-Code Pattern

-- Example: create_orders.sql
CREATE TABLE IF NOT EXISTS orders (
    order_id VARCHAR(100) PRIMARY KEY,
    customer_id VARCHAR(100),
    order_date TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    amount NUMBER(12,2),
    status VARCHAR(20),
    _ingestion_time TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Example: create_orders_view.sql
CREATE OR REPLACE VIEW orders_view AS
SELECT 
    order_id,
    customer_id,
    order_date,
    amount,
    status
FROM orders
WHERE order_date >= DATEADD(year, -1, CURRENT_DATE());

Real-World Scenario: Microsoft

Question: "How do you handle schema migrations in a CI/CD pipeline? What happens when a migration fails in production?"

Solution: Migration Pipeline

-- 1. Create migration tracking table
CREATE TABLE schema_migrations (
    migration_id NUMBER AUTOINCREMENT,
    version VARCHAR(50) PRIMARY KEY,
    description VARCHAR(500),
    executed_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    executed_by VARCHAR(100),
    execution_time_ms NUMBER,
    status VARCHAR(20) DEFAULT 'SUCCESS',
    rollback_script VARCHAR(2000)
);

-- 2. Create migration script example
-- V001__create_orders_table.sql
CREATE TABLE IF NOT EXISTS orders (
    order_id VARCHAR(100) PRIMARY KEY,
    customer_id VARCHAR(100),
    order_date TIMESTAMP_NTZ,
    amount NUMBER(12,2),
    status VARCHAR(20)
);

-- Insert migration record
INSERT INTO schema_migrations (version, description, rollback_script)
VALUES (
    'V001',
    'Create orders table',
    'DROP TABLE IF EXISTS orders;'
);

-- 3. Migration runner procedure
CREATE OR REPLACE PROCEDURE run_migration(
    migration_version VARCHAR,
    migration_sql VARCHAR,
    rollback_sql VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
AS
<MathBlock formula="DECLARE
 start_time TIMESTAMP_NTZ;
 execution_time NUMBER;
 BEGIN
 -- Check if migration already applied
 IF EXISTS (
 SELECT 1 FROM schema_migrations 
 WHERE version = :migration_version AND status = 'SUCCESS'
 ) THEN
 RETURN 'Migration ' || :migration_version || ' already applied';
 END IF;
 start_time := CURRENT_TIMESTAMP();
 -- Execute migration
 EXECUTE IMMEDIATE :migration_sql;
 execution_time := TIMESTAMPDIFF('millisecond', start_time, CURRENT_TIMESTAMP());
 -- Record success
 INSERT INTO schema_migrations (version, description, execution_time_ms, rollback_script)
 VALUES (
 :migration_version,
 'Migration ' || :migration_version,
 :execution_time,
 :rollback_sql
 );
 RETURN 'Migration ' || :migration_version || ' applied successfully (' || 
 :execution_time || 'ms)';
 EXCEPTION
 WHEN OTHER THEN
 -- Record failure
 INSERT INTO schema_migrations (version, description, status, rollback_script)
 VALUES (
 :migration_version,
 'Migration ' || :migration_version || ' - FAILED',
 'FAILED',
 :rollback_sql
 );
 RETURN 'Migration ' || :migration_version || ' FAILED: ' || SQLERRM;
 END;
 $$;
 -- 4. Rollback procedure
 CREATE OR REPLACE PROCEDURE rollback_migration(migration_version VARCHAR)
 RETURNS VARCHAR
 LANGUAGE SQL
 AS" />
DECLARE
    rollback_sql VARCHAR;
BEGIN
    SELECT rollback_sql INTO rollback_sql
    FROM schema_migrations
    WHERE version = :migration_version AND status = 'SUCCESS';
    
    IF (rollback_sql IS NULL) THEN
        RETURN 'No rollback script found for migration ' || :migration_version;
    END IF;
    
    EXECUTE IMMEDIATE :rollback_sql;
    
    UPDATE schema_migrations
    SET status = 'ROLLED_BACK'
    WHERE version = :migration_version;
    
    RETURN 'Migration ' || :migration_version || ' rolled back successfully';
END;
$$;

GitHub Actions CI/CD Pipeline

# .github/workflows/snowflake-deploy.yml
name: Snowflake CI/CD

on:
  push:
    branches: [main, staging]
  pull_request:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Install SnowSQL
        run: |
          curl -O https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowsql-1.2.21-linux_x86_64.bash
          sudo bash snowsql-1.2.21-linux_x86_64.bash
          
      - name: Run Tests
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
          SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
        run: |
          snowsql -f scripts/test_queries.sql
          
  deploy-staging:
    needs: test
    if: github.ref == 'refs/heads/staging'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Deploy to Staging
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT_STAGING }}
        run: |
          for f in scripts/migrations/*.sql; do
            snowsql -f "$f"
          done
          
  deploy-prod:
    needs: deploy-staging
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Deploy to Production
        env:
          SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT_PROD }}
        run: |
          for f in scripts/migrations/*.sql; do
            snowsql -f "$f"
          done

Best Practices

Practice	Recommendation
Version control	Store all DDL in Git
Migrations	Use versioned migration scripts
Testing	Test migrations in dev before prod
Rollback	Always have rollback scripts
Monitoring	Track migration history
Automation	Use CI/CD for all deployments

⚠️

CI/CD Anti-Patterns:

Manual deployments — Error-prone and unrepeatable
No rollback plan — Must always have rollback scripts
Skipping dev/staging — Always test in lower environments first
No migration tracking — Always record what was applied
Hardcoded values — Use variables and secrets management

Topic: CI/CD & Code Promotion