Data Transformation · Topic 12 of 14
Testing, Validation and Environment Management
Test and validate Snowflake pipelines with dbt tests, data metric functions and Snowpark local testing, and manage dev, test and prod with clones.
- 4 min
- Local testing:
local_testingset toTrue, same DataFrame code, subset of features,Session.sql()must be mocked. - DMFs: system ones in
SNOWFLAKE.CORE,DATA_METRIC_SCHEDULE, expectations, Enterprise Edition, serverless billing for scheduled runs. - Environments: separate databases or accounts, zero copy clones, dedicated roles and warehouses, parameterized scripts.
What it is
Testing and validation proves a transformation is correct before release (does the code work?) and while it runs (is the data still good?). Environment management is how you keep development, test and production separate yet alike, so a change that passes in test behaves the same in production.
How it works
Testing code
| Layer | Tool | What it checks |
|---|---|---|
| Unit test of Snowpark logic | Snowpark local testing framework with pytest | DataFrame code runs on an emulator, with no warehouse needed |
| Model tests | dbt tests (not_null, unique, accepted_values, relationships, custom SQL) | Results of dbt models |
| Stored procedure tests | A test script that calls the procedure on a clone | Business logic with real shapes of data |
| Pipeline run | Task history and EXECUTE TASK on a test copy | Scheduling and dependencies |
The local testing framework is enabled by creating the session with the local_testing configuration set to True. The same DataFrame code can then run locally or against Snowflake. Know its limits: it implements only a subset of functions, does not support raw SQL through Session.sql() (mock it), and does not support UDTFs, UDAFs or built in table functions.
When the question is "test Snowpark code quickly in CI without consuming credits", the answer is the local testing framework.
Validating data
Data metric functions (DMFs) measure the quality of a table or view and are part of Snowflake's data quality monitoring (Enterprise Edition).
- System DMFs in the
SNOWFLAKE.COREschema cover common needs such as null counts, duplicate counts, unique counts, row counts and freshness. You can also write custom DMFs in SQL. - A DMF is attached to a column or table and runs on a schedule set on the table with
DATA_METRIC_SCHEDULE. The default schedule is hourly. - An expectation compares a DMF result with a rule, so a bad value becomes a reported violation. A DMF alone only returns a number.
- Anomaly detection flags volume and freshness values outside the predicted range.
- Scheduled DMF runs use serverless compute and are billed as serverless credits. A plain
SELECTthat calls a DMF is not billed as DMF serverless usage.
ALTER TABLE orders SET DATA_METRIC_SCHEDULE = '60 MINUTE';
ALTER TABLE orders ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_id);
Other validation inside the pipeline:
- Constraints (
NOT NULLis enforced; primary and foreign keys are informational on standard tables). - Reconciliation queries comparing source and target row counts or sums after a load.
- Task graph assertions: a final task or alert that fails or notifies when a check fails.
COPY INTO ... VALIDATION_MODEto test staged files before loading.
Primary key and unique constraints are not enforced on standard tables. Duplicates need a check such as the duplicate count DMF or a dbt unique test.
Environment management
| Strategy | Use |
|---|---|
Separate databases per environment in one account (DEV_DB, TEST_DB, PROD_DB) | Simple, cheap, shared account settings |
| Separate accounts per environment | Strong isolation, separate network and security policies; connect with organization features |
| Zero copy clone of production data into dev or test | Realistic data with no extra storage until it changes (CREATE DATABASE dev_db CLONE prod_db) |
| Role per environment and environment specific grants | Developers can write to dev, only the deploy role writes to prod |
| Separate warehouses with their own resource monitors | Cap spending in non production work |
| Parameterized scripts | One script, an environment variable for database and warehouse names |
Points that matter:
- A clone is writable and independent: changes in the clone do not touch the source. Masked or sampled data is wise when production holds sensitive values; masking policies carry over with the clone.
- Time Travel on the source lets you clone a past state:
CLONE ... AT (TIMESTAMP => ...). - Grant production access to a deployment role only, and let the pipeline, not people, run changes.
- Use the same object definitions everywhere; differences belong in parameters, not in copied code.
Common mistakes
Testing only in production. Use a clone and run checks before promotion.
Assuming primary keys are enforced. They are metadata; test uniqueness explicitly.
Using a DMF as a rule by itself. Add an expectation, or alert on the result.
Forgetting costs of non production. Separate warehouses and resource monitors keep dev and test from draining credits.
What to remember for the exam
- Local testing:
local_testingset toTrue, same DataFrame code, subset of features,Session.sql()must be mocked. - DMFs: system ones in
SNOWFLAKE.CORE,DATA_METRIC_SCHEDULE, expectations, Enterprise Edition, serverless billing for scheduled runs. - Environments: separate databases or accounts, zero copy clones, dedicated roles and warehouses, parameterized scripts.
Check yourself
How can Snowpark code be unit tested without a warehouse?
Use the Snowpark local testing framework by creating the session with local_testing set to True.
What turns a data metric function result into a pass or fail check?
An expectation, which compares the DMF value with a rule.
How do you give the test environment production like data cheaply?
Create a zero copy clone of the production database, optionally from a past point with Time Travel.
Topic 12 of 14 in Data Transformation
Test yourself on Data Transformation
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.