DevOps with Snowflake¶

DCM Projects (recommended)¶

DCM Projects (Database Change Management Projects) provide a declarative, infrastructure-as-code approach to managing your Snowflake environment. Instead of writing imperative scripts that specify each step, you define the desired target state of your objects. Snowflake compares those definitions against the current state and determines the necessary changes.

A DCM project consists of:

• A manifest file (manifest.yml) that specifies deployment targets, owner roles, and templating configurations for each environment.

• Definition files (SQL files under sources/definitions/) that contain DEFINE statements for your Snowflake objects, GRANT statements for access control, and ATTACH statements for data quality expectations.

The following example shows a definition file that creates infrastructure for multiple teams using Jinja2 templating:

{% for team in teams %}

  DEFINE DATABASE {{team.name}}_DB;

  DEFINE WAREHOUSE {{team.name}}_WH
    WITH
      warehouse_size = '{{team.wh_size}}'
      auto_suspend = 300;

  DEFINE ROLE {{team.name}}_ADMIN;

  GRANT OWNERSHIP ON DATABASE {{team.name}}_DB TO ROLE {{team.name}}_ADMIN;
  GRANT OWNERSHIP ON WAREHOUSE {{team.name}}_WH TO ROLE {{team.name}}_ADMIN;

{% endfor %}

For complete documentation on DCM Projects, including how to set up your project files, manage multiple environments, and automate deployments, see Snowflake DCM Projects.

dbt Projects on Snowflake¶

dbt Projects on Snowflake let you deploy and run dbt Core projects as native Snowflake objects. You define SQL transformations in dbt models, deploy them as a versioned DBT PROJECT object, and execute them with Snowflake SQL or the Snowflake CLI. You can schedule runs with Snowflake tasks and integrate deployment into CI/CD pipelines.

For more information, see dbt Projects on Snowflake.

Alternative: CREATE OR ALTER with versioned scripts¶

For individual object changes outside of a DCM project, you can use the CREATE OR ALTER <object> command, which creates the object or alters it to match the definition specified by the command. By using this command from a versioned file in a remote repository, you can roll back changes to a previous version by executing a previous version of the file.

CREATE OR ALTER TABLE vacation_spots (
  city VARCHAR,
  airport VARCHAR,
  avg_temperature_air_f FLOAT,
  avg_relative_humidity_pct FLOAT,
  avg_cloud_cover_pct FLOAT,
  precipitation_probability_pct FLOAT
) data_retention_time_in_days = 1;

from snowflake.core import Root
from snowflake.core.table import PrimaryKey, Table, TableColumn

my_table = root.databases["my_db"].schemas["my_schema"].tables["vacation_spots"].fetch()
my_table.columns.append(TableColumn(name="city", datatype="varchar", nullable=False))
my_table.columns.append(TableColumn(name="airport", datatype="varchar", nullable=False))
my_table.columns.append(TableColumn(name="avg_temperature_air_f", datatype="float", nullable=False))
my_table.columns.append(TableColumn(name="avg_relative_humidity_pct", datatype="float", nullable=False))
my_table.columns.append(TableColumn(name="avg_cloud_cover_pct", datatype="float", nullable=False))
my_table.columns.append(TableColumn(name="precipitation_probability_pct", datatype="float", nullable=False))

my_table_res = root.databases["my_db"].schemas["my_schema"].tables["vacation_spots"]
my_table_res.create_or_alter(my_table)

Plan with DCM Projects¶

DCM Projects use a plan-then-deploy model. The PLAN command compares your definition files against the current state of your account and produces a list of proposed changes without modifying anything.

You can run a plan using the Snowflake CLI:

snow dcm plan --target PROD

Or using SQL:

EXECUTE DCM PROJECT my_db.my_schema.my_project
  PLAN
  USING CONFIGURATION PROD
FROM
  '@my_stage/my_project/';

Review the output to confirm the expected creates, alters, and drops before proceeding to deploy.

Connection profiles for environment targeting¶

With DCM Projects, you can define multiple deployment targets in your manifest.yml file. Each target maps to a specific Snowflake account (or database), project object, owner role, and templating configuration. The same definition files can deploy to all environments with environment-specific settings applied through configuration profiles.

targets:
  DEV:
    account_identifier: MYORG-MYACCOUNT_DEV
    project_name: MY_DB.MY_SCHEMA.MY_PROJECT_DEV
    project_owner: DEV_DEPLOYER
    templating_config: DEV

  PROD:
    account_identifier: MYORG-MYACCOUNT_PROD
    project_name: MY_DB.MY_SCHEMA.MY_PROJECT_PROD
    project_owner: PROD_DEPLOYER
    templating_config: PROD

templating:
  configurations:
    DEV:
      wh_size: "X-SMALL"
    PROD:
      wh_size: "LARGE"

For enterprise patterns such as multi-project setups and team collaboration, see Enterprise use cases for DCM Projects.

Advanced: Jinja parameterization for custom scripts¶

DCM Projects natively support Jinja2 templating in definition files. You can use template variables, loops, conditions, macros, and dictionaries to make your definitions reusable across environments. Variable values come from configuration profiles in the manifest.yml or from runtime overrides.

For details on DCM templating, see DCM Projects files and templates.

You can also parameterize standalone SQL scripts (outside of DCM Projects) using Jinja2 with EXECUTE IMMEDIATE FROM. The Snowflake CLI allows you to pass environment variables to Python scripts as well.

To change a deployment target, for example, you replace the name of the target database with a Jinja variable such as {{ environment }} in SQL scripts, or an environment variable in Python scripts. This technique is shown in the following SQL and Python code examples:

CREATE OR ALTER TASK {{ environment }}.my_schema.my_task
  WAREHOUSE = my_warehouse
  SCHEDULE = '60 minute'
  AS select pi();

import os
from snowflake.core import Root, CreateMode
from datetime import timedelta
from snowflake.core.task import Task

my_task = Task(
    name="my_task",
    warehouse="my_warehouse",
    definition="select pi()",
    schedule=timedelta(minutes=60)
)
root = Root(Session.builder.getOrCreate())
tasks = root.databases[os.environ["environment"]].schemas["my_schema"].tasks
tasks.create(my_task, mode=CreateMode.or_replace)