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
In a nutshell
  • Local testing: local_testing set to True, 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.

Code changeunit testLocal testSnowpark emulatorpushDev clonezero copy of proddbt tests and DMF checksTest environmentpromoteProductionDMFs keep watching

How it works

Testing code

LayerToolWhat it checks
Unit test of Snowpark logicSnowpark local testing framework with pytestDataFrame code runs on an emulator, with no warehouse needed
Model testsdbt tests (not_null, unique, accepted_values, relationships, custom SQL)Results of dbt models
Stored procedure testsA test script that calls the procedure on a cloneBusiness logic with real shapes of data
Pipeline runTask history and EXECUTE TASK on a test copyScheduling 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.

Exam tip

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.CORE schema 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 SELECT that 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 NULL is 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_MODE to test staged files before loading.
Watch out

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

StrategyUse
Separate databases per environment in one account (DEV_DB, TEST_DB, PROD_DB)Simple, cheap, shared account settings
Separate accounts per environmentStrong isolation, separate network and security policies; connect with organization features
Zero copy clone of production data into dev or testRealistic data with no extra storage until it changes (CREATE DATABASE dev_db CLONE prod_db)
Role per environment and environment specific grantsDevelopers can write to dev, only the deploy role writes to prod
Separate warehouses with their own resource monitorsCap spending in non production work
Parameterized scriptsOne 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_testing set to True, 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.