Dataform vs. transformation scripts: keeping warehouse changes reviewable

A SQL change can look harmless in a diff and still double a revenue total. Adding a join may change one row per order into one row per order line. A reviewer needs to see that consequence, the tables it affects, and the checks that would catch it. Whether the query lives in a Python string or a SQLX file doesn’t answer those questions by itself.

For a shared, SQL-first BigQuery warehouse, we usually prefer Dataform to a collection of scripts that separately manage dependencies, table creation, and validation. Its value is putting those choices alongside the transformation and compiling them into a workflow. Google’s Dataform overview describes that execution model.

Give each output a definition someone can inspect

A useful model file shows what one row represents, which inputs it depends on, how it is materialized, and which properties its output must satisfy. For an orders model, that might mean one row per source order, a key that includes the source system, a current-state treatment of cancellations, and a documented policy for late line changes.

Keep important joins, filters, and calculations close to that definition. A reviewer changing customer eligibility should be able to find the condition without tracing several generic helper functions and a scheduler branch. Shared helpers can remove mechanical repetition, but a helper that decides which customers exist in the model is carrying business logic and deserves to be reviewed as such.

Use ref for dependencies managed or declared in the project. It both resolves the table reference and adds an edge to the workflow. Writing a fully qualified table name into the SQL doesn’t create the same dependency automatically; use an explicit dependency when the query alone can’t express it. Google’s Dataform dependency guide covers both forms.

This makes the graph useful for sequencing and impact review. It doesn’t discover every spreadsheet, export, or external application that consumes a table, so keep important consumer ownership visible elsewhere too.

Review the output change, not just the edited SQL

Suppose a change adds shipment details to an order summary. The review should establish whether there can be several shipments per order, whether unshipped orders remain present, and whether a late shipment updates an existing order.

Show the evidence that matches the change. A new descriptive column may need a few representative rows and a schema check. A join or key change needs a comparison at the intended grain. An incremental change needs transition tests: insert, correction, late child, deletion, and retry, as applicable to the source. Compare a bounded incremental result with a full rebuild where practical.

Keep the evidence small enough to review. Explain unexpected row-count or aggregate differences rather than attach thousands of lines of output and call the comparison complete. A reviewer should know which differences are intentional and which remain unresolved.

Inspect what the project compiles into

SQLX contains configuration and can generate SQL through JavaScript. The final query and action graph can therefore differ materially from the visible SQL fragments. Review compiled behavior when a helper, variable, materialization setting, or incremental predicate changes.

Check the destination project and dataset, newly added dependencies, merge keys, and the historical range that will be read or rewritten. An incremental model with a narrow source filter can still perform an unexpectedly broad target operation. Google’s Dataform table configuration guide is the reference for the relevant materialization and incremental settings.

Compilation is an early check for project structure and generated behavior. It isn’t execution against real data, and it doesn’t establish that a declared unique key is unique. Attach the necessary assertions and make critical publication actions depend on them. See Dataform assertions that block downstream models for dependency configuration and a complete publication example.

Keep development isolation explicit

Dataform compilation overrides can direct development output into different projects, datasets, or table names. Use them to give a developer or CI run an isolated place to build, following Google’s Dataform compilation documentation.

Naming overrides aren’t a substitute for permissions. A hardcoded production destination or custom SQL operation still needs review, and a development identity shouldn’t have unnecessary production write access. Inspect compiled destinations before running code that can replace tables.

Use fixtures for specific logic and bounded source samples for realistic data behavior. Fixtures are good at demonstrating what should happen to a late shipment; representative source data helps reveal that the source has several kinds of “missing” shipment identifiers. Both can be useful without turning every small edit into a full warehouse rehearsal.

Let orchestration run the model, not redefine it

Scheduling belongs outside the transformation when it controls when to run, which release to use, or how to recover from an infrastructure failure. It shouldn’t quietly choose a different revenue definition for a backfill than for the nightly job.

Keep ordinary runs, corrections, and historical rebuilds on the same implementation. A backfill may change the range or materialization strategy, but the intended result for a given source state should remain consistent. Document exceptional source boundaries rather than hide them in a one-off production cleanup script.

The surrounding workflow still needs to load sources, avoid conflicting runs, inspect failures, and coordinate publication with external systems.

Keep scripts where they earn their place

An API extractor, a specialized non-SQL calculation, or a genuinely one-off migration can be clearer as ordinary code. A small scheduled query may not need a modeling framework.

When the existing system already makes dependencies, outputs, tests, and repair paths explicit, compare its maintenance cost with the cost of migrating.

We’d move shared BigQuery transformations into Dataform when the team is repeatedly rebuilding those capabilities around loose scripts, or when changes require too much explanation from the original author. Start with a useful group of related models, prove the development and recovery paths, and expand from there. Keep the code that does a different job outside it.