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."

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
$$
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