DCM (Database Change Management) Skill

When to Use

Use this skill when a user wants to:

• •Create a new DCM project from scratch
• •Modify an existing DCM project (with or without local source code)
• •Define or modify Snowflake infrastructure (databases, schemas, tables, views, dynamic tables, tasks, warehouses, roles, grants)
• •Set up data quality expectations and data metric functions
• •Understand dependencies or lineage between objects in a DCM project
• •Deploy changes to Snowflake infrastructure

Prerequisites

• •Active Snowflake connection (-c <connection> required for all DCM commands)
• •Appropriate Snowflake privileges for the operations being performed
• •For new projects: CREATE DATABASE or CREATE SCHEMA privileges as needed
• •For deployments: privileges to create/alter/drop the objects defined in the project

⚠️ MANDATORY INITIALIZATION

Before any DCM workflow, you MUST:

Step 1: Load Core References ✋ BLOCKING

Load the following reference documents to understand DCM concepts:

1. •Load: reference/syntax.md - DCM definition syntax (DEFINE vs CREATE, grants, data quality)
2. •Load: reference/project_structure.md - Project structure and manifest.yml configuration
3. •Load: reference/cli_reference.md - All DCM CLI commands and workflows

DO NOT PROCEED until you have loaded and understood these references.

Step 2: Gather Required Information ✋ BLOCKING

For ALL DCM operations, you MUST collect:

1. •

   Target DCM Project Identifier (fully qualified: DATABASE.SCHEMA.PROJECT_NAME)

   • •This is the Snowflake object where the project is registered
   • •Required for all snow dcm commands (except list that should be used to aid the user in selecting a project)
   • •⚠️ CRITICAL: A DCM project CANNOT define its parent database or schema. If the project identifier is MY_DB.MY_SCHEMA.MY_PROJECT, you cannot use DEFINE DATABASE MY_DB or DEFINE SCHEMA MY_DB.MY_SCHEMA - these containers must already exist. You can only define objects inside the project's schema.
2. •

   Snowflake Connection (--connection or -c)

   • •The named connection to use for all operations
   • •Ask user if not provided (or use default connection if not specified)
3. •

   Configuration Name (if the project uses configurations)

   • •Check manifest.yml for available configurations (DEV, PROD, etc.)
   • •Required for analyze, plan, preview, and deploy commands when configurations exist

DO NOT PROCEED until you have confirmed these details with the user.

Intent Detection

When a user makes a request, detect their intent and follow the appropriate workflow:

CREATE Intent - User wants to create a new DCM project

Trigger phrases: "create project", "new project", "set up DCM", "start from scratch", "build infrastructure"

→ Load: create-project/SKILL.md

MODIFY_LOCAL Intent - User wants to modify an existing project with local source code

Trigger phrases: "modify", "update", "change", "add table", "edit definitions" (when source files are available locally)

→ Load: modify-project/SKILL.md

DOWNLOAD_AND_MODIFY Intent - User wants to work with an existing deployed project (no local code)

Trigger phrases: "download project", "get sources", "work with existing project", "modify deployed project"

→ Load: modify-project/SKILL.md (includes download workflow)

ANALYZE Intent - User wants to understand dependencies or check for errors

Trigger phrases: "analyze", "check dependencies", "lineage", "what depends on", "validate"

→ Follow: Analyze Project Workflow

IMPORT_EXISTING Intent - User wants to import/adopt existing Snowflake objects into DCM

Trigger phrases: "import existing", "adopt objects", "bring into DCM", "convert to DCM", "add existing table"

→ Follow: Adopting Existing Objects Workflow

ROLE_GRANT_GUIDELINES Intent - User needs guidance on roles/grants in DCM

Trigger phrases: "dcm role", "dcm grant", "roles in dcm", "grants in dcm project", "dcm permission model", "dcm warehouse grant error", "define roles in dcm"

→ Load: dcm-roles-and-grants/SKILL.md

DEPLOY Intent - User wants to deploy changes

Trigger phrases: "deploy", "apply changes", "push to Snowflake"

→ Load: deploy-project/SKILL.md

Core Workflows

Workflow 1: Create New Project

User wants NEW project
    ↓
1. Gather project details:
   - Target identifier (DATABASE.SCHEMA.PROJECT_NAME)
   - Connection name
   - What infrastructure to create (databases, tables, views, tasks, etc.)
    ↓
2. Verify database/schema exist (or prompt user to create them)
    ↓
2.5. ⚠️ MANDATORY: Analyze Roles/Grants (if any mentioned)
   - If user mentions roles, grants, permissions, or warehouse access:
     → **Load** [dcm-roles-and-grants/SKILL.md](dcm-roles-and-grants/SKILL.md)
   - Categorize grants by DCM support level
   - Identify warehouse grants (need account role workaround)
   - Present analysis and get user approval BEFORE writing definitions
    ↓
3. Create DCM project in Snowflake:
   snow dcm create <identifier> -c <connection>
    ↓
4. Create local project structure:
   - manifest.yml
   - definitions/ folder
   - Definition files (.sql)
    ↓
5. Clarify with user:
   - Object names
   - Column definitions
   - Relationships and dependencies
   - Configuration variables (if multi-environment)
    ↓
6. Write definition files with DEFINE statements
    ↓
7. Run analyze to validate:
   snow dcm analyze <identifier> -c <connection> --output-path ./out/analyze
    ↓
8. Fix any errors found during analysis
    ↓
9. Ask user if ready to proceed to plan/deploy

Key Steps:

1. •

   Confirm project identifier: Always use fully qualified names (DATABASE.SCHEMA.PROJECT_NAME)

2. •

   Check prerequisites: Verify the target database and schema exist:

   sql

   SHOW DATABASES LIKE '<database>';
   SHOW SCHEMAS IN DATABASE <database> LIKE '<schema>';
   

3. •

   Create the DCM project:

   bash

   snow dcm create <DATABASE.SCHEMA.PROJECT_NAME> -c <connection>
   

4. •

   Create project structure using the recommended layout:

   code

   project/
   ├── manifest.yml
   └── definitions/
       ├── infrastructure.sql
       ├── tables.sql
       ├── analytics.sql
       └── access.sql
   

5. •

   Create manifest.yml — use ONLY the fields shown below (see reference/project_structure.md for full schema):

   yaml

   manifest_version: 1
   include_definitions:
     - definitions/.*
   type: DCM_PROJECT
   
   # Optional: configurations for multi-environment
   configurations:
     DEV:
       db: "DEV"
       wh_size: "X-SMALL"
     PROD:
       db: "PROD"
       wh_size: "LARGE"
   

   Note: Deployment target is specified via CLI (snow dcm plan DB.SCHEMA.PROJECT --target dev), not in the manifest.

Workflow 2: Modify Existing Project

User has LOCAL source code
    ↓
1. Identify the project:
   - Read manifest.yml to understand structure
   - Identify target DCM project identifier
   - Identify configuration to use (if any)
    ↓
2. Understand current state:
   - Read existing definition files
   - Run analyze to see current objects and dependencies
    ↓
3. Clarify changes with user:
   - What to add/modify/remove?
   - Confirm object names, columns, properties
    ↓
4. Make changes to definition files
    ↓
5. Run analyze to validate:
   snow dcm analyze <identifier> -c <connection> --configuration <config> --output-path ./out/analyze
    ↓
6. Fix any errors
    ↓
7. Run plan to preview changes:
   snow dcm plan <identifier> -c <connection> --configuration <config> --output-path ./out/plan
    ↓
8. Present plan summary to user (CREATE/ALTER/DROP counts)
    ↓
9. Offer preview of specific objects (if applicable)
    ↓
10. Proceed to deploy ONLY with explicit user confirmation

Workflow 3: Download and Modify Existing Project

User wants to work with DEPLOYED project (no local code)
    ↓
1. List ALL available projects (use --database "" to see all):
   snow dcm list -c <connection> --database ""
    ↓
2. Help user select project or use provided name
    ↓
3. Describe project:
   snow dcm describe <identifier> -c <connection>
    ↓
4. Download sources using script:
   bash <skill-dir>/scripts/download_project.sh <project_name> \
     --connection <connection> \
     --target <local_folder>
    ↓
5. Proceed with Workflow 2 (Modify Existing Project)

Workflow 4: Analyze Project

User wants to understand dependencies or validate
    ↓
1. Run analyze:
   snow dcm analyze <identifier> -c <connection> \
     --configuration <config> \
     --output-path ./out/analyze
    ↓
2. ⚠️ CRITICAL: Read and parse out/analyze/analyze_output.json
   - This step is MANDATORY, not optional
   - Check for errors at file and definition levels
   - Extract dependency information
   - Extract column-level lineage
    ↓
3. If errors exist:
   - Report them to the user
   - Fix issues in definition files
   - Rerun analyze
    ↓
4. Present findings to user:
   - List of objects defined
   - Dependencies between objects
   - Any errors or warnings
   - Column lineage (if requested)

Workflow 5: Adopting Existing Objects

User wants to import existing Snowflake objects into DCM
    ↓
1. Identify the objects to adopt:
   - Ask user which objects to import
   - Get fully qualified names
    ↓
2. Get current DDL for each object:
   SELECT GET_DDL('TABLE', 'DB.SCHEMA.TABLE');
   SELECT GET_DDL('VIEW', 'DB.SCHEMA.VIEW');
   SELECT GET_DDL('STAGE', 'DB.SCHEMA.STAGE');
    ↓
2.5. ⚠️ MANDATORY: Categorize Objects by DCM Support
   - **Stages**: Check for URL parameter
     ✅ No URL (internal) → Use DEFINE STAGE
     ⚠️ Has URL (external) → Use ATTACH POST_HOOK
   - **Grants**: Load dcm-roles-and-grants/SKILL.md
     ✅ Supported → include in definitions
     ⚠️ Workaround needed → warehouse grants need account role
     ❌ Unsupported → document in post_deployment_grants.sql
   - **Other Objects**: Tables, Views, Warehouses → DEFINE
   - **Unsupported Objects**: Streams, Alerts, Integrations → POST_HOOK
   - Present categorized analysis to user
   - ⚠️ CHECKPOINT: Get explicit approval before proceeding
    ↓
3. Convert supported objects (CREATE to DEFINE):
   - Replace CREATE keyword with DEFINE for supported objects
   - Internal stages: CREATE STAGE → DEFINE STAGE
   - Preserve all properties exactly
   - Keep grants separate (handle per step 2.5 analysis)
   - External stages/streams/alerts go to POST_HOOK (not DEFINE)
    ↓
4. Add definitions to project files:
   - DEFINE statements → appropriate .sql files
   - POST_HOOK objects → in ATTACH POST_HOOK blocks
   - Unsupported grants → post_deployment_grants.sql
    ↓
5. Run analyze and READ out/analyze/analyze_output.json:
   - Verify objects appear in definitions
    ↓
6. Run plan and READ out/plan/plan_metadata.json:
   - ⚠️ VERIFY: Plan should show ZERO changes for adopted objects
   - If plan shows CREATE/ALTER, the definition doesn't match
   - Adjust definition to match existing object exactly
    ↓
7. Repeat until plan shows no changes for adopted objects

Key Point: Successful adoption = plan shows NO operations for the adopted objects. They should appear in analyze but result in zero changes in plan.

Workflow 6: Deploy Changes

⚠️ CRITICAL: NEVER DEPLOY WITHOUT PLAN AND USER CONFIRMATION

User wants to deploy
    ↓
1. MUST have run analyze successfully first
    ↓
2. Check if plan output already exists:
   - If out/plan/plan_metadata.json exists and is current:
     → READ the existing file instead of rerunning
   - Only rerun plan if explicitly requested or definitions changed
    ↓
3. ⚠️ CRITICAL: Read and parse out/plan/plan_metadata.json
   - Check status: SUCCESS or PLAN_FAILED
   - If PLAN_FAILED → Report error, do NOT proceed
   - Parse operations array to understand all changes
    ↓
4. Present plan summary:
   📊 Plan Summary:
   ✅ CREATE: X objects (list types)
   ⚠️  ALTER: Y objects (highlight data-affecting changes)
   🚨 DROP: Z objects (EMPHASIZE destructive operations)
    ↓
5. Ask user: "Would you like to preview any specific objects?"
   - If yes, use: snow dcm preview <identifier> -c <connection> --object <fqn> --limit 10
    ↓
6. WAIT FOR EXPLICIT USER CONFIRMATION
   ⚠️ You are about to deploy changes to Snowflake.
   This will affect database: <database>
   Using connection: <connection>

   Are you sure you want to proceed? (yes/no)
    ↓
7. Only if user confirms, deploy:
   snow dcm deploy <identifier> -c <connection> \
     --configuration <config> \
     --alias "<descriptive-alias>"
    ↓
8. Check if project has tests (from analyze output)
   - If tests exist, ask: "Would you like to run data quality tests?"
   - If yes:
     snow dcm refresh <identifier> -c <connection>  # Refresh dynamic tables first
     snow dcm test <identifier> -c <connection> --output-path ./out/test

Workflow Decision Tree

Start Session
    ↓
MANDATORY: Load reference documents (syntax.md, project_structure.md, cli_reference.md)
    ↓
Gather: Project identifier, Connection, Configuration
    ↓
Detect User Intent
    ↓
    ├─→ CREATE → Load create-project/SKILL.md
    │   (Triggers: "create project", "new project", "set up DCM")
    │   ⚠️ If roles/grants/permissions mentioned:
    │      → ALSO load dcm-roles-and-grants/SKILL.md
    │
    ├─→ MODIFY_LOCAL → Load modify-project/SKILL.md
    │   (Triggers: "modify", "update", "add table" with local files)
    │
    ├─→ DOWNLOAD_AND_MODIFY → Load modify-project/SKILL.md (includes download)
    │   (Triggers: "download project", "get sources", "work with existing")
    │
    ├─→ IMPORT_EXISTING → Follow Adopting Existing Objects workflow
    │   (Triggers: "import existing", "adopt", "bring into DCM", "convert DDL")
    │   → Get DDL → ⚠️ Analyze grants first → Convert to DEFINE
    │   → ALWAYS load dcm-roles-and-grants/SKILL.md for grant analysis
    │   → Verify plan shows zero changes for adopted objects
    │
    ├─→ ROLE_GRANT_GUIDELINES → Load dcm-roles-and-grants/SKILL.md
    │   (Triggers: "dcm role", "dcm grant", "roles in dcm", "dcm permission model")
    │   → Recommended patterns for roles and grants in DCM
    │
    ├─→ ANALYZE → Run analyze workflow
    │   (Triggers: "analyze", "check dependencies", "lineage")
    │   ⚠️ MUST read out/analyze/analyze_output.json after running
    │
    └─→ DEPLOY → Load deploy-project/SKILL.md
        (Triggers: "deploy", "apply changes")
            ↓
        ALWAYS: analyze → plan → READ OUTPUT FILES → user confirmation → deploy
            ↓
        If tests exist: offer to run tests

Sub-Skills

Note: The IMPORT_EXISTING workflow (adopting existing objects) is documented in modify-project/SKILL.md and in Workflow 5: Adopting Existing Objects.

Note: For role and grant guidance (recommended patterns, handling warehouse constraints, unsupported grant types), load the dcm-roles-and-grants skill.

Rules

Running Scripts

When running scripts from this skill:

1. •

   Use bash to run the download script:

   bash

   bash <skill-dir>/scripts/download_project.sh <project_name> \
     --connection <connection> \
     --target <target_folder>
   

2. •

   Do not cd into the skill directory - run from the user's working directory.

DCM Command Patterns

All DCM commands follow this pattern:

snow dcm <command> <identifier> -c <connection> [options]

Common options:

• •--configuration <name>: Use specific configuration from manifest.yml
• •--output-path <path>: Save command output to local directory
• •--format json: Get machine-readable output (for list commands)

Definition Syntax Rules

1. •

   Use DEFINE, not CREATE for named objects:

   sql

   DEFINE TABLE MY_DB.MY_SCHEMA.MY_TABLE (
       id NUMBER,
       name VARCHAR
   );
   

2. •

   Always use fully qualified names:

   sql

   DEFINE TABLE database.schema.table_name (...);
   

3. •

   Grants use standard SQL syntax (imperative, not DEFINE):

   sql

   GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO ROLE MY_ROLE;
   

4. •

   Data quality expectations use ATTACH syntax:

   sql

   ATTACH DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
       TO TABLE MY_DB.RAW.MY_TABLE
       ON (column_name)
       EXPECTATION NO_NULLS (value = 0);
   

⚠️ CRITICAL: Reading Output Files

After running analyze or plan, you MUST read and parse the output JSON files:

• •out/analyze/analyze_output.json - after analyze
• •out/plan/plan_metadata.json - after plan

This is MANDATORY, not optional. The agent must:

1. •Read the JSON file
2. •Parse the content
3. •Check for errors or issues
4. •Report findings to the user
5. •Fix any issues before proceeding

If plan output already exists and user asks for a summary, read the existing file instead of rerunning unless explicitly requested.

Safety Rules

1. •NEVER deploy without running plan first
2. •NEVER deploy without explicit user confirmation
3. •ALWAYS highlight DROP and data-affecting ALTER operations
4. •ALWAYS suggest using --alias for deployments to track deployment history
5. •ALWAYS read and parse output JSON files after analyze/plan commands

When Creating Definitions

1. •

   Clarify requirements before writing code:

   • •Ask about object names
   • •Confirm column names and types
   • •Verify relationships between objects
   • •Understand configuration needs (multi-environment?)
2. •

   Propose structure and get confirmation:

   • •Present proposed definitions to user
   • •Wait for approval before writing files
3. •

   Use appropriate file organization:

   • •infrastructure.sql: Databases, schemas, warehouses, internal stages
   • •tables.sql or raw.sql: Table definitions
   • •analytics.sql: Dynamic tables, transformations
   • •serve.sql: Views for consumption
   • •access.sql: Roles, grants, permissions
   • •expectations.sql: Data quality rules

Common Use Cases

Creating a Data Pipeline

1. •Define source tables with CHANGE_TRACKING = TRUE
2. •Define dynamic tables for transformations (or tasks for procedural ETL)
3. •Define views for consumption
4. •Define tasks for scheduled operations and orchestration
5. •Define roles and grants for access control
6. •Optionally add data quality expectations

Adopting Existing Objects into DCM

When a user wants to "import" or "adopt" existing Snowflake objects:

1. •Get current DDL: SELECT GET_DDL('TABLE', 'fully.qualified.name')
2. •Categorize the object:
   • •✅ Internal stages (no URL) → Convert to DEFINE STAGE
   • •⚠️ External stages (with URL parameter) → Keep in ATTACH POST_HOOK
   • •✅ Tables, Views, Warehouses → Convert to DEFINE
   • •⚠️ Streams, Alerts, Integrations → Use ATTACH POST_HOOK
3. •Convert CREATE to DEFINE (for supported objects): Replace the keyword only
4. •Add to DCM project definitions: Place in appropriate .sql file
5. •Run analyze: Verify object appears in definitions
6. •Run plan and READ the output:
   • •⚠️ Plan should show ZERO changes for adopted objects
   • •If plan shows CREATE/ALTER, definition doesn't match exactly
   • •Adjust definition until plan shows no changes

Success criteria: Adopted objects appear in analyze but result in zero operations in plan.

Multi-Environment Setup

1. •Define configurations in manifest.yml (DEV, PROD)
2. •Use Jinja variables in definitions: {{env}}, {{wh_size}}
3. •Use --configuration flag with all commands

Inspecting dbt Pipelines

When user asks to create DCM from dbt models:

1. •Read dbt model files to understand transformations
2. •Create corresponding dynamic table definitions
3. •Preserve the DAG structure in DCM

Error Handling

When commands fail, check:

1. •Connection issues: Verify connection name is correct
2. •Permission errors: Ensure user has required privileges
3. •Analysis errors: Review errors in analyze output JSON
4. •Plan failures: Check the error field in plan output

For debugging, suggest: snow dcm <command> --debug

Related Documentation

• •DCM Syntax Reference
• •Project Structure Guide
• •CLI Command Reference