DCM Test Branches

Warning

While DCM Test Branches is in preview, use it only in non-production environments.

Introduction

If you already manage your account infrastructure with DCM projects, you can now write test scripts for your projects to validate that the business logic of your pipelines works correctly. A test script exercises the transformation procedures, functions, and views that your project defines, using test cases such as the following:

  • Select from output tables and views to validate the shape and structure of the data.
  • Insert test data, then run procedures or functions and validate their output.

Because a test can modify the tables it reads from, you aren’t limited to running your transformations over the full dataset. If a dataset is too large, and transforming it would take too long or cost too much, you can truncate the tables and insert a small number of rows for the test run.

Run TEST between PLAN and DEPLOY

Running EXECUTE DCM PROJECT ... TEST is completely separate from running PLAN or DEPLOY, and neither of those commands runs your tests. We recommend the following flow after you make changes to your definitions:

  1. Run PLAN to validate the syntax and the changes made to your object definitions.
  2. Run TEST to validate the data processing logic and its output.
  3. When both PLAN and TEST succeed, run DEPLOY.

Tests run on isolated clones before you deploy

DCM Test Branches differs from running test queries against your account in two ways.

Tests run against cloned objects on an isolated branch: Tests are executed against a cloned set of objects on a fully isolated branch, which leaves the actual objects in your account untouched. You can therefore run potentially destructive statements in a test, because they’re only applied to the cloned objects. Other users aren’t affected by your tests, aren’t aware of them, and can continue to work with the original objects while your tests run.

Tests run before deployment: Because tests run on the branch, you can run them before you deploy your changes. If a test fails, you can still modify your definitions based on the test results before anything is deployed to your account.

When to use DCM Test Branches

Use DCM Test Branches in the following situations:

  • Inner-loop development: Validate definition changes while you iterate on them during your development cycle, before you commit or deploy anything.
  • Integration and regression testing in CI/CD: Run your tests as a step in your CI/CD pipeline to ensure that changes to the project don’t break existing logic and contracts.

How a test run works

A test run requires that the DCM project has at least one previous successful deployment.

When you run EXECUTE DCM PROJECT ... TEST, Snowflake runs the following steps automatically for each test file:

  1. Creates a test branch.
  2. Clones all managed objects that were modified through the definition changes.
  3. Deploys the definition changes to the cloned objects, so that they represent the newly defined state on the branch.
  4. Executes the test file against the objects on the isolated branch.
  5. Returns the test results.
  6. Drops the branch and all cloned objects.

No objects, whether managed by the project or external to it, are impacted by this process. Each test run is a complete, fully managed workflow: you don’t have to create a branch, clone objects, or deploy your changes manually. You also can’t see the test branch or interact with it manually. You write your test scripts, run the test, and evaluate the test results.

Each test file runs on its own branch, and all test files run in parallel. Each file runs from top to bottom. Because each file has its own branch, test files are isolated from each other and don’t interfere with each other. For example, rows that one test file inserts into a table aren’t visible to any other test file.

Run DCM Tests

By default, EXECUTE DCM PROJECT ... TEST runs all SQL files in the sources/tests/ folder of your project. Optionally, you can pass a list of individual test files to run.

A test file passes when all of its statements run without an error. When a statement raises an error, the test file fails, and the test results include the error message.

Note

Requires Snowflake CLI version 3.29.0 or later.

Use snow dcm test with --all-scripts to run all test files, or with --script to run individual test files. Without either option, snow dcm test checks the data quality expectations of the already-deployed objects instead of running your test files.

--all-scripts

Runs all test files in sources/tests/. You can’t combine this option with --script.

--script script_name

Runs the specified test file. Specify the path of the test file under sources/tests/, without the .sql extension. Names are case-sensitive. Repeat the option to run multiple test files.

--expectations

Also checks the data quality expectations of the already-deployed objects in the same run.

--save-output

Saves the test results to out/test/test_result.json.

  • Run all test files for the default target:

    snow dcm test --all-scripts
    
  • Run all test files for a specific target and save the results:

    snow dcm test --all-scripts --target DEV --save-output
    
  • Run two specific test files:

    snow dcm test --script 1_test_existing_contract --script 2_test_category_total
    

The CLI reports the result of each test file, including the error message of each failed file, followed by a summary:

✓ PASS      1_test_existing_contract
✗ FAIL      2_test_category_total
  └─ <error message>
1 passed, 1 failed out of 2 total.

When at least one test file fails, the command exits with exit code 1, so you can use it to block the deployment step in a CI/CD pipeline.

Required access privileges

Only the project owner role can run EXECUTE DCM PROJECT ... TEST. The project owner role holds ownership of all objects that the DCM project manages, so the role that runs a test also holds ownership of all branched objects.

Warning

A test run only applies its statements to the cloned objects on the branch. However, there’s no additional access restriction on the original objects: the project owner role keeps full ownership of them. Statements from a test file that you run manually outside a test run therefore apply to the original objects. For more information, see Write a test file.

Write a test file

Put your test files in the sources/tests/ folder of your DCM project. Test files in any other path can’t be executed.

my_dcm_project/
├── manifest.yml
└── sources/
    ├── definitions/
    │   └── definitions.sql
    └── tests/
        ├── 1_test_existing_contract.sql
        ├── 2_test_category_total.sql
        └── 3_test_with_sample_data.sql

Each test file follows a procedure-like structure:

  1. (Optional) A DECLARE section, where you declare any variables and exceptions that the test uses.
  2. A block that starts with BEGIN BRANCH and ends with END BRANCH, which contains the statements of the test.
declare
	<variable_declarations>
begin branch
	<statements>
end branch;

The BEGIN BRANCH and END BRANCH syntax helps prevent accidental manual execution of a test file outside of a branch, because Snowflake only accepts this syntax in the context of a test branch.

Warning

The BEGIN BRANCH syntax only protects the test file as a whole. If you copy individual statements from a test file and run them manually, they run against the actual objects in your account. Take care not to manually run destructive statements, such as TRUNCATE or UPDATE, that are meant for a test branch.

To make a test fail when the data doesn’t meet a condition, declare an exception in the DECLARE section and raise it in the BEGIN BRANCH block.

Test files support the same Jinja templating as definition files. You can reference the variables defined in the templating section of the manifest file, such as {{db}}, in your test files. For more information, see Object definition templating.

Supported object types and statements

Your DCM project can define any object type, but you can currently only test the following object types on a test branch:

  • Tables
  • Views
  • Procedures with SQL or Python handlers
  • Functions with SQL or Python handlers

Support for more object types like Dynamic Tables and Data Metric Functions is planned.

A test file can execute the following statements:

  • SELECT statements
  • TRUNCATE TABLE statements
  • INSERT statements
  • UPDATE statements
  • CALL statements for procedures, and SELECT statements that call functions

Examples

The following examples use a DCM project that manages a table TEST_ON_BRANCH.PUBLIC.SALES and a view TEST_ON_BRANCH.PUBLIC.V_SALES. The deployed version of the view calculates a NET_AMOUNT column directly in its query.

The pending definition change, which hasn’t been deployed yet, extracts the net amount calculation into a reusable function and adds a CATEGORY_TOTAL_NET_AMOUNT column to the view:

-- sources/definitions/definitions.sql
define table TEST_ON_BRANCH.PUBLIC.SALES (
	SALE_ID NUMBER,
	CATEGORY VARCHAR,
	GROSS_AMOUNT NUMBER(12,2),
	DISCOUNT_AMOUNT NUMBER(12,2),
	STATUS VARCHAR
)
comment = 'Small sales dataset for the DCM TEST data branch demo'
;


define function TEST_ON_BRANCH.PUBLIC.CALCULATE_NET_AMOUNT(
	GROSS_AMOUNT NUMBER,
	DISCOUNT_AMOUNT NUMBER
)
returns NUMBER(12,2)
comment = 'Calculates a non-negative net amount after discounts'
as
$$
	round(
		greatest(
			coalesce(GROSS_AMOUNT, 0) - coalesce(DISCOUNT_AMOUNT, 0),
			0
		),
		2
	)
$$;


define view TEST_ON_BRANCH.PUBLIC.V_SALES
comment = 'Sales with reusable net amount logic and category totals'
as
select
	SALE_ID,
	CATEGORY,
	GROSS_AMOUNT,
	DISCOUNT_AMOUNT,
	STATUS,
	TEST_ON_BRANCH.PUBLIC.CALCULATE_NET_AMOUNT(
		GROSS_AMOUNT,
		DISCOUNT_AMOUNT
	) as NET_AMOUNT,
	sum(
		TEST_ON_BRANCH.PUBLIC.CALCULATE_NET_AMOUNT(
			GROSS_AMOUNT,
			DISCOUNT_AMOUNT
		)
	) over (
		partition by CATEGORY
	) as CATEGORY_TOTAL_NET_AMOUNT
from
	TEST_ON_BRANCH.PUBLIC.SALES
;

Each of the following tests runs on its own branch, against the pending function and view.

Check that a refactoring preserves an existing contract

This regression test compares the view with the base table. It fails if the view doesn’t preserve the row count of the table or the number of rows for each SALE_ID, or if the view produces null net amounts. If this test passes, extracting the calculation into the function didn’t change the grain of the view:

-- sources/tests/1_test_existing_contract.sql
declare
	V_VIEW_ROW_COUNT NUMBER;
	V_TABLE_ROW_COUNT NUMBER;
	V_GRAIN_MISMATCH_COUNT NUMBER;
	V_NULL_COUNT NUMBER;
	ROW_COUNT_MISMATCH exception (-20001, 'The view must preserve the base table row count');
	GRAIN_MISMATCH exception (-20002, 'The view must preserve the base table grain');
	NULL_AMOUNT_FOUND exception (-20003, 'The view must not produce null net amounts');
begin branch
	select
		count(*),
		count_if(NET_AMOUNT is null)
	into
		:V_VIEW_ROW_COUNT,
		:V_NULL_COUNT
	from
		TEST_ON_BRANCH.PUBLIC.V_SALES
	;

	select
		count(*)
	into
		:V_TABLE_ROW_COUNT
	from
		TEST_ON_BRANCH.PUBLIC.SALES
	;

	if (V_VIEW_ROW_COUNT <> V_TABLE_ROW_COUNT) then
		raise ROW_COUNT_MISMATCH;
	end if;

	select
		count(*)
	into
		:V_GRAIN_MISMATCH_COUNT
	from
		(
			select
				SALE_ID,
				count(*) as ROW_COUNT,
				1 as PRESENT
			from
				TEST_ON_BRANCH.PUBLIC.V_SALES
			group by
				SALE_ID
		) as VIEW_GRAIN
	full outer join
		(
			select
				SALE_ID,
				count(*) as ROW_COUNT,
				1 as PRESENT
			from
				TEST_ON_BRANCH.PUBLIC.SALES
			group by
				SALE_ID
		) as TABLE_GRAIN
		on VIEW_GRAIN.SALE_ID is not distinct from TABLE_GRAIN.SALE_ID
	where
		VIEW_GRAIN.PRESENT is null
		or TABLE_GRAIN.PRESENT is null
		or VIEW_GRAIN.ROW_COUNT <> TABLE_GRAIN.ROW_COUNT
	;

	if (V_GRAIN_MISMATCH_COUNT <> 0) then
		raise GRAIN_MISMATCH;
	end if;

	if (V_NULL_COUNT <> 0) then
		raise NULL_AMOUNT_FOUND;
	end if;
end branch;

Validate a new column against an independent aggregation

This test compares the category totals from the pending view with an independent aggregation and net amount calculation over the SALES table. It fails if any category is missing on either side or if any total doesn’t match:

-- sources/tests/2_test_category_total.sql
declare
	V_MISMATCH_COUNT NUMBER;
	CATEGORY_TOTAL_MISMATCH exception (-20006, 'Category totals in the view must match independently aggregated base data');
begin branch
	select
		count(*)
	into
		:V_MISMATCH_COUNT
	from
		(
			select distinct
				CATEGORY,
				CATEGORY_TOTAL_NET_AMOUNT,
				1 as PRESENT
			from
				TEST_ON_BRANCH.PUBLIC.V_SALES
		) as VIEW_TOTALS
	full outer join
		(
			select
				CATEGORY,
				sum(
					round(
						greatest(
							coalesce(GROSS_AMOUNT, 0) - coalesce(DISCOUNT_AMOUNT, 0),
							0
						),
						2
					)
				) as EXPECTED_TOTAL,
				1 as PRESENT
			from
				TEST_ON_BRANCH.PUBLIC.SALES
			group by
				CATEGORY
		) as BASE_TOTALS
		on VIEW_TOTALS.CATEGORY is not distinct from BASE_TOTALS.CATEGORY
	where
		VIEW_TOTALS.PRESENT is null
		or BASE_TOTALS.PRESENT is null
		or VIEW_TOTALS.CATEGORY_TOTAL_NET_AMOUNT is distinct from BASE_TOTALS.EXPECTED_TOTAL
	;

	if (V_MISMATCH_COUNT <> 0) then
		raise CATEGORY_TOTAL_MISMATCH;
	end if;
end branch;

Replace the table data with a small set of sample rows

To avoid running the transformation over the full table, this test truncates the SALES table, inserts three rows with known values, and validates the expected NET_AMOUNT and CATEGORY_TOTAL_NET_AMOUNT of every row. Because SALES is managed by the DCM project, the TRUNCATE and INSERT statements only affect the clone on the branch of this test. The deployed table and the branches of the other tests keep their original data:

-- sources/tests/3_test_with_sample_data.sql
declare
	V_ROW_COUNT NUMBER;
	V_MATCH_COUNT NUMBER;
	ROW_COUNT_MISMATCH exception (-20007, 'The fixture must produce three view rows');
	FIXTURE_VALUE_MISMATCH exception (-20008, 'The view must return the expected values for all fixtures');
begin branch
	truncate table TEST_ON_BRANCH.PUBLIC.SALES;

	insert into TEST_ON_BRANCH.PUBLIC.SALES (
		SALE_ID,
		CATEGORY,
		GROSS_AMOUNT,
		DISCOUNT_AMOUNT,
		STATUS
	)
	select
		COLUMN1,
		COLUMN2,
		COLUMN3,
		COLUMN4,
		COLUMN5
	from
		values
			(101, 'SOFTWARE', 100.00, 10.00, 'COMPLETED'),
			(102, 'SOFTWARE', 20.00, 30.00, 'REFUNDED'),
			(103, 'SERVICES', 200.00, 25.00, 'COMPLETED')
	;

	select
		count(*),
		count_if(
			(SALE_ID = 101 and NET_AMOUNT = 90.00 and CATEGORY_TOTAL_NET_AMOUNT = 90.00)
			or (SALE_ID = 102 and NET_AMOUNT = 0.00 and CATEGORY_TOTAL_NET_AMOUNT = 90.00)
			or (SALE_ID = 103 and NET_AMOUNT = 175.00 and CATEGORY_TOTAL_NET_AMOUNT = 175.00)
		)
	into
		:V_ROW_COUNT,
		:V_MATCH_COUNT
	from
		TEST_ON_BRANCH.PUBLIC.V_SALES
	;

	if (V_ROW_COUNT <> 3) then
		raise ROW_COUNT_MISMATCH;
	end if;

	if (V_MATCH_COUNT <> 3) then
		raise FIXTURE_VALUE_MISMATCH;
	end if;
end branch;

Limitations

DCM Test Branches has the following limitations during the preview:

  • Procedures and functions that declare integrations can’t run on a branch: A procedure or function that declares an integration, such as an external access integration, can’t run in a test branch, because Snowflake can’t control what the external call does.
  • Only managed objects can be modified: You can only test objects that are already managed by the DCM project, or that are being added to the project definitions. A test can reference objects of any type outside of the project, but these unmanaged objects are read-only. A test can’t modify or write to unmanaged tables or other unmanaged objects in any way.
  • The warehouse can’t be specified: You can’t specify the warehouse that a test run uses. Test runs always use the default warehouse of the DCM project owner.
  • Environment variables and secrets aren’t supported yet: Test branches don’t yet work with a project that declares environment variables (env_vars) or secrets (env_secrets) in manifest.yml.
  • Runtime variable overwrites aren’t supported: The test command doesn’t accept overwrites of templating variable values for the run, such as USING (<expr>), the --variable option, or Override Variables in Workspaces.