In the Build Data Pipelines with Snowflake DCM Projects guide, you learned how to split infrastructure across multiple DCM Projects and build a medallion architecture with Dynamic Tables defined as code.

In this guide, you'll focus on dynamic table lifecycle management — making changes and enhancements to your pipeline while maintaining control over the backfill process.

Dynamic tables are the backbone of declarative data pipelines in Snowflake — you define the what, and Snowflake handles the when and how. But managing dynamic tables at scale introduces real challenges: How do you version-control their definitions? How do you promote changes across environments? And when you need to evolve a schema, how do you avoid an expensive full recomputation of historical data?

This guide answers all three questions by combining a few powerful Snowflake features:

You'll start by deploying a food truck analytics pipeline using DCM Projects, then evolve a dynamic table's schema by adding an AI-classified column — and see firsthand how frozen regions prevent a full rewrite of historical data and stop the AI function from re-running on rows that will never change.

In this step, you'll create a Snowsight Workspace linked to the sample DCM Project repository on GitHub.

1. Navigate to Projects > Workspaces in Snowsight.
2. Click Create (+) and select Git repository.
3. Enter the repository URL: https://github.com/snowflake-labs/snowflake-dcm-projects
4. Select an API Integration for GitHub (create one if needed).
5. Select Public repository.

Once the workspace is created, you'll see the repository files in the file explorer. Navigate to Quickstarts/dcm-projects-for-dynamic-tables to find two directories:

Open scripts/01_pre_deploy.sql in a Snowsight worksheet — you'll use it in the next step.

Before deploying the pipeline, you need to create a dedicated role for managing DCM Projects and a DCM Project object.

Open scripts/01_pre_deploy.sql in a Snowsight worksheet and run each section in order. This script lives outside the DCM project directory, so it won't be picked up by Plan or Deploy. The script does the following:

The DCM_DEVELOPER role needs privileges to create infrastructure objects through DCM deployments:

To define and test data quality expectations, grant the following:

The DCM Project object dcm_dt_project_dev is now created in dcm_demo.projects. This is the object referenced in the manifest's DCM_DEV target.

Now that the infrastructure is in place, take a moment to explore the DCM Project structure before deploying. Navigate into the DCM_Projects_DT_Lifecycle/ directory — this is the actual DCM Project that the Plan & Deploy operations read. A DCM Project consists of a manifest file and one or more definition files organized in a sources/ directory.

Open DCM_Projects_DT_Lifecycle/manifest.yml in the file explorer. The manifest is the configuration file for your DCM Project. It defines:

Here's the manifest for this project:

Notice how the DEV configuration uses env_suffix: "_DEV" while PROD uses env_suffix: "". This allows the same definition files to create DCM_DEMO_3_DEV in development and DCM_DEMO_3 in production. The teams list is also different per environment — DEV has a single team, while PROD has six.

The sources/definitions/ directory contains SQL files that define your Snowflake infrastructure. Each file uses DEFINE statements and Jinja templating variables (like {{env_suffix}}):

For example, here's how raw.sql defines the database and a table:

The {{env_suffix}} variable is replaced at deployment time based on the target configuration — _DEV for development, empty string for production.

And here's how analytics.sql defines a dynamic table that joins across several raw tables to create enriched order details:

The sources/macros/ directory contains reusable Jinja macros. Open grants_macro.sql to see a macro that creates a standard set of roles for each team:

This macro is called in jinja_demo.sql inside a {% for %} loop that iterates over the teams list from the manifest configuration. For each team, it creates a schema, a set of roles, a products table, and optionally a sandbox schema — all driven by the manifest's templating values.

Before deploying changes, always run a Plan first. A Plan is a dry-run that shows you exactly what changes DCM will make without actually executing them.

1. You should see the DCM control panel in the first tab in the bottom panel. Select the project DCM_Projects_DT_Lifecycle.
2. The DCM_DEV target should already be selected (it's the default in the manifest).
3. Click on the target profile to verify it uses DCM_DT_PROJECT_DEV and the DEV templating configuration.

Click the play button to the right of Plan and wait for the definitions to render, compile, and dry-run.

Since none of the defined objects exist yet, the plan will show only CREATE statements. You should see planned operations for:

In the file explorer, notice that a new out folder was created above sources. This contains the rendered Jinja output for all definition files.

Open the jinja_demo.sql file from the plan output side-by-side with the original jinja_demo.sql in sources/definitions/ to see how the Jinja templating was resolved — loops expanded, conditionals evaluated, and variables replaced with their DEV configuration values.

If the plan result looks correct and all planned changes match your expectations, deploy:

1. Instead of Plan, set the operation to Deploy.
2. Optionally, add a Deployment alias (e.g., "Initial pipeline deployment") — think of it as a commit message that appears in the deployment history of your project.
3. DCM will create all objects and attach grants and expectations using the owner role of the project object.

Once the deployment completes successfully, refresh the Database Explorer on the left side of Snowsight. You should see the DCM_DEMO_3_DEV database and all of the created objects inside it.

The deployment created the table structures, but they're empty. Now you'll insert sample data to populate the raw tables and bring the dynamic tables and views to life.

Open scripts/02_post_deploy.sql in a Snowsight worksheet and run each section in order.

The script inserts data into the raw tables: trucks, menu items, customers, inventory, order headers, and order details. Run all the INSERT statements.

Because all dynamic tables use INITIALIZE = ON_SCHEDULE, they were created without data. Use EXECUTE DCM PROJECT ... REFRESH ALL to kick off the first refresh of every DT the project manages in one statement:

DCM triggers refreshes in dependency order — upstream tables refresh before downstream ones.

Run the final query in the script to see the enriched order details:

You should see rows with columns like order_id, order_ts, quantity, menu_item_name, line_item_revenue, line_item_profit, customer_city, and truck_brand_name. Take note of this schema — in the next step, you'll evolve it.

This is where the guide diverges from the basics. You have a running pipeline — but now the analytics team wants every order tagged with a diet classification (Healthy or Indulgent) so they can analyze ordering trends. Rather than maintain a lookup table by hand, you'll let a Cortex AI function classify each menu item directly inside the dynamic table.

There's a catch: AI inference isn't free. Running an LLM over every historical order on every refresh would be slow and expensive. You only want to classify rows that are still changing — not years of frozen history. This is exactly what a frozen region is for.

The FROZEN WHERE clause on a dynamic table declares a condition under which rows are considered frozen. Snowflake skips these rows during refreshes, which means:

For more details, see the Snowflake documentation on frozen regions.

Open sources/definitions/analytics.sql in your workspace and update the enriched_order_details dynamic table definition. You're making two changes:

1. Add a DIET_CLASSIFICATION column powered by AI_CLASSIFY.
2. Add a FROZEN WHERE clause that freezes rows older than 1 day.

Replace the existing enriched_order_details definition with:

Here's what changed and why:

The frozen region is the key. Most of the sample data has order timestamps from October 2023 — well over a day ago — so those rows are frozen. But orders 1024 and 1025, inserted with CURRENT_TIMESTAMP() in the previous step, fall in the 1-day active region. Because DCM applies the change with CREATE OR ALTER (not a full replace), Snowflake will:

1. Preserve the frozen rows as-is — they are skipped entirely, so DIET_CLASSIFICATION stays NULL for them and no AI inference runs on them.
2. Classify only the active rows (orders 1024 and 1025) using AI_CLASSIFY — DIET_CLASSIFICATION will have a calculated value.

This means you'll see both behaviors side-by-side after a single refresh — no need to insert additional data.

Now push your definition change through the DCM plan-deploy cycle.

1. In the DCM control panel in the bottom panel, click the play button next to Plan.
2. This time, the plan output will look different from the initial deployment. Instead of all CREATEs, you'll see an ALTER operation for enriched_order_details — DCM applies the change with CREATE OR ALTER, updating the definition in place rather than rebuilding the table. This is what preserves the frozen rows.

Review the rendered output in the out folder to confirm the FROZEN WHERE clause and the new DIET_CLASSIFICATION column appear correctly.

1. Instead of Plan, set the operation to Deploy.
2. Add a deployment alias like "Add AI diet classification with frozen region".
3. DCM will alter the dynamic table in place with the new column and frozen region.

After deployment, open scripts/03_verify_evolution.sql in a Snowsight worksheet and run each section in order.

First, refresh the dynamic table:

Then query the table and include the METADATA$IS_FROZEN virtual column:

You should see results like this:

The two most recent orders (1024 and 1025) have:

All historical orders (October 2023) have:

This is a frozen region in action: a schema change was deployed, and Snowflake only reprocessed the rows that needed it — keeping your AI inference cost confined to the active region.

When you're done, open scripts/04_cleanup.sql in a Snowsight worksheet and run it to tear down all objects:

In this guide, you learned how to:

The combination of DCM Projects, frozen regions, and Cortex AI functions gives you a production-grade workflow: version-controlled pipeline definitions, environment-aware deployments, AI-enriched columns that refresh automatically, and efficient schema evolution that respects the cost of reprocessing large datasets.

Continue the series: