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:

Account-level privileges cannot be granted by a project on itself, so they all happen here, before the first plan. The script grants DCM_DEVELOPER a superset: every guide in this series shares this role, so one run of any guide's pre-deploy script prepares you for all of them, and each grant carries a comment saying what capability it buys. The ones this guide relies on:

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 shows 37 entities: 36 CREATE and 1 ALTER. 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. One ALTER DYNAMIC TABLE statement can refresh several dynamic tables at once. The script lists the three leaf tables:

Their shared upstream, ENRICHED_ORDER_DETAILS, refreshes with them, and all four land at a single data timestamp, so the leaf tables are consistent with each other. You don't list the upstream yourself.

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. It purges everything the project manages and drops the now-empty project object:

PURGE drops every object the project created — database, tables, the four dynamic tables, views, the task, functions, warehouse, roles, and all their data. It is irreversible, and it leaves the project object behind, which is why the DROP follows.

The dcm_demo database, its projects schema and the dcm_developer role are shared across every guide in this series, so the script leaves them in place, commented out. The dcm_demo.projects schema holds the project objects for every DCM guide in this series, so only drop it once you have finished with all of them. Check what it contains first:

The inherited-grants account parameter enabled during setup also stays on. It is account-wide and the other guides in this series need it; the script carries the commented-out line to turn it off:

If PURGE ever fails, the script tells you to suspend the dynamic tables and the task first, so nothing keeps consuming credits while you investigate. The known cause is a data metric function attachment, which this guide has; dropping the table the DMF is attached to removes the attachment, after which PURGE succeeds.

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: