Study guide
13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation
Google Cloud Certified Professional Data Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Security, IAM, and Data Protection
1.1 Cloud IAM, Service Accounts, and Resource Hierarchy in GCP Data Systems 1.2 Data Encryption, Key Management (CMEK), and Cloud KMS 1.3 Sensitive Data Protection: Cloud DLP, De-identification, and Masking
2 Chapter 2: Reliability and Compliance Architecture
2.1 High Availability, Regional vs Multi-Regional Deployments, and Disaster Recovery 2.2 Data Residency, Sovereignty, and Regulatory Compliance (HIPAA, GDPR) 2.3 Modern Data Architecture Patterns: Data Mesh, Lakehouse, and Modern Data Stack
3 Chapter 3: Cross-Cloud and Federated Governance
3.1 BigLake Architecture: Unified Multicloud Storage and Fine-Grained Access Control 3.2 BigQuery Omni: Multicloud Analytics Across AWS S3, Azure ADLS, and Google Cloud 3.3 Analytics Hub and Federated Computational Governance
4 Chapter 4: Data Ingestion and Pipeline Planning
4.1 Data Pipeline Planning: Sources, Sinks, and Network Architecture (VPC, PSC, Interconnect) 4.2 Batch vs Streaming Processing Trade-Offs and Architecture Selection 4.3 Change Data Capture (CDC) with Datastream for Continuous Database Ingestion
5 Chapter 5: Streaming Ingestion with Cloud Pub/Sub
5.1 Cloud Pub/Sub Core Architecture: Topics, Subscriptions, and Message Lifecycle 5.2 Pub/Sub Message Ordering, Exactly-Once Delivery, and Deduplication 5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics
6 Chapter 6: Pipeline Processing with Apache Beam and Cloud Dataflow
6.1 Apache Beam Programming Model: Pipelines, PCollections, and Core PTransforms 6.2 Cloud Dataflow Architecture: Autoscaling, Worker Allocation, and Dataflow Prime 6.3 Windowing Strategies in Streaming Dataflow: Fixed, Sliding, and Session Windows
7 Chapter 7: Advanced Dataflow: Robustness and Exactly-Once Semantics
7.1 Watermarks, Triggers, Accumulating vs Discarding Panes, and Late Data Handling 7.2 Side Inputs, Broadcast Joins, and Stateful Stream Processing 7.3 Pipeline Robustness: Dead-Letter Queues, Error Handling, and Pipeline Lifecycle Management
8 Chapter 8: Managed Data Processing: Cloud Dataproc and Cloud Data Fusion
8.1 Cloud Dataproc: Architecture, Ephemeral vs Persistent Clusters, and Spark Tuning 8.2 Dataproc Serverless and Autoscaling Policies for Batch Processing 8.3 Cloud Data Fusion: Visual ETL/ELT Pipelines, CDAP Plugins, and Data Lineage
9 Chapter 9: Storage Selection and Cloud Storage Architecture
9.1 GCP Storage Selection Decision Matrix: Relational, NoSQL, Object, and Analytical Stores 9.2 Cloud Storage Architecture: Storage Classes, Lifecycle Rules, and Object Versioning 9.3 Cloud Storage Security: Bucket Lock (WORM), Access Control, and Data Migration Tools
10 Chapter 10: Relational Cloud Databases: Cloud Spanner and Cloud SQL
10.1 Cloud Spanner Architecture: TrueTime, External Consistency, and Global Scalability 10.2 Cloud Spanner Schema Design: Primary Keys, Interleaved Tables, and Query Tuning 10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery
11 Chapter 11: Scalable NoSQL Databases: Cloud Bigtable and Firestore
11.1 Cloud Bigtable Architecture: Storage Model, Tablet Splitting, and Cluster Management 11.2 Cloud Bigtable Row Key Design: Lexicographical Ordering, Salt Keys, and Anti-Patterns 11.3 Cloud Firestore: Document Model, Real-Time Sync, and ACID Collections
12 Chapter 12: Enterprise Data Warehousing with BigQuery
12.1 BigQuery Architecture: Capacitor Columnar Format, Jupiter Network, and Borg Slots 12.2 BigQuery Partitioning: Ingestion-Time, Date/Timestamp, and Integer Range Strategies 12.3 BigQuery Clustering: Cardinality, Multi-Column Ordering, and Performance Optimization 12.4 Data Warehouse Modeling: Normalization, Star Schemas, and Slowly Changing Dimensions
13 Chapter 13: Advanced Analytical Queries and Transformations in BigQuery
13.1 Advanced SQL in BigQuery: Window Analytic Functions, ARRAY/STRUCT, and PIVOT/UNPIVOT 13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation 13.3 BI Engine In-Memory Acceleration and Enterprise Looker/LookML Semantic Modeling
14 Chapter 14: Machine Learning and Feature Engineering with BigQuery ML
14.1 BigQuery ML Architecture: In-Database Training, Model Topologies, and SQL Analytics 14.2 Feature Engineering: ML.TRANSFORM, Preprocessing Pipelines, and Vertex AI Feature Store 14.3 MLOps, Model Governance, Continuous Monitoring, and Deployment Lifecycles
15 Chapter 15: Operations, Orchestration, and Data Governance
15.1 Workflow Orchestration with Cloud Composer: Managed Airflow, DAG Design, and Dependencies 15.2 Unified Data Governance with Dataplex: Lakes, Zones, Data Quality, and Lineage 15.3 Pipeline Observability, FinOps, and Cost Optimization Across GCP Data Services
13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation
Key Takeaways Explain This Topic AI
SQLX files blend declarative configuration metadata (config blocks) with standard GoogleSQL, utilizing the ${ref('table_name')} function to dynamically infer and construct topological dependency graphs across tables and views.
Incremental table materialization (type: 'incremental') processes only newly arrived or mutated records using when(incremental(), ...) predicates, slashing BigQuery compute slot consumption and scan costs compared to full table re-creations.
Built-in data quality assertions (nonNull, rowConditions, uniqueKey) and custom SQL assertion files validate data integrity directly in BigQuery, blocking defective data from flowing downstream into production dashboards.
Native Git integration with Cloud Source Repositories, GitHub, and GitLab enables CI/CD version control, developer workspace isolation, and release configurations for dynamic multi-environment deployments across dev, staging, and prod projects.
Generated SQL from Gemini in BigQuery or data canvas is a draft, not a pipeline: commit it to a SQLX model with ${ref()} references and assertions so a wrong join grain fails the run, because regenerating a transformation from a prompt on every execution is non-deterministic.
Last updated: September 2026
13.2 Modern SQL Orchestration with Dataform: DAG Modeling, Testing, and Documentation
Exam Focus: The Google Cloud Professional Data Engineer exam expects deep architectural and operational mastery of Dataform for ELT data modeling. You must know how Dataform compiles declarative SQLX files into executable Directed Acyclic Graphs (DAGs), how the ${ref()} function resolves dependencies and enables multi-project deployment, how to configure incremental tables with uniqueKey and when(incremental(), ...) clauses, how to implement automated data quality assertions , how to structure reusable JavaScript libraries in includes/ , and how to manage production release configurations and CI/CD pipelines.
In legacy data architectures, transformation logic was executed by external compute engines (Extract-Transform-Load, or ETL) using tools like Apache Spark, Talend, or Informatica. Raw data was extracted from databases, transformed on intermediate compute clusters, and reloaded into data warehouses. With the advent of petabyte-scale serverless cloud warehouses like BigQuery, modern engineering has universally transitioned to the Extract-Load-Transform (ELT) paradigm. Data is ingested into BigQuery in raw form and transformed in-place using BigQuery's massive, distributed Dremel compute engine.
However, managing raw SQL transformations at enterprise scale quickly degrades into chaos without proper tooling: scripts suffer from hardcoded project and dataset paths, lack version control, have no automated testing, and depend on brittle procedural schedulers. Dataform solves this operational bottleneck by providing an enterprise-grade, serverless data modeling and orchestration framework native to Google Cloud.
1. The ELT Paradigm and Dataform Architecture
Dataform enables data engineers to develop, version-control, test, and schedule complex SQL transformation pipelines in BigQuery using software engineering best practices. +─────────────────────────────────────────────────────────────────────────────────+
- | DATAFORM ARCHITECTURE IN GCP |
- +─────────────────────────────────────────────────────────────────────────────────+
- | |
- | DEVELOPMENT ENVIRONMENT (Git-Backed) |
- | +---------------------------------------------------------------------------+ |
- | | Dataform Repository (Connected to GitHub / GitLab / Cloud Source Repos) | |
- | | - Developer Workspaces (Isolated Git Branches) | |
- | | - Declarative SQLX Files (definitions/*.sqlx) | |
- | | - Reusable JavaScript Modules (includes/*.js) | |
- | +---------------------------------------------------------------------------+ |
- | │ |
- | ▼ |
- | COMPILATION ENGINE |
- | +---------------------------------------------------------------------------+ |
- | | Dataform Compiler | |
- | | - Parses SQLX and evaluates JavaScript macros | |
- | | - Resolves ${ref()} calls into Topological Dependency DAG | |
- | | - Generates Execution Graph (Tables, Views, Incremental, Assertions) | |
- | | - Applies Environment Overrides (Dev / Staging / Prod Project IDs) | |
- | +---------------------------------------------------------------------------+ |
- | │ |
- | ▼ |
- | EXECUTION ENGINE (Serverless BigQuery Runtime) |
- | +---------------------------------------------------------------------------+ |
- | | BigQuery (Regional Dremel Slots) | |
- | | - Executes compiled DDL / DML in topological order | |
- | | - Runs automated assertions (fails DAG if data quality is violated) | |
- | | - Manages atomic table swaps, merges, and partition updates | |
- | +---------------------------------------------------------------------------+ |
- +─────────────────────────────────────────────────────────────────────────────────+
- Core Structural Concepts
Dataform Repository: The centralized project container in Google Cloud that links directly to a remote Git repository (GitHub, GitLab, Bitbucket, or Cloud Source Repositories).
Developer Workspaces: Isolated working branches where individual data engineers author, test, and validate SQLX code against personal sandbox BigQuery datasets without affecting production pipelines.
Compilation Results: A compiled representation of your entire project. Dataform executes the JavaScript and SQLX templates to build a static execution graph detailing all target tables, views, assertions, and dependencies.
Workflow Configurations & Execution: Schedulers that execute compiled DAGs on demand or on a cron schedule using a dedicated Google Cloud service account.
2. Declarative SQLX Modeling and Dependency Resolution
Dataform introduces SQLX , an extension of standard SQL that unifies declarative configuration metadata, executable SQL queries, and dynamic documentation in a single file.
The Anatomy of a SQLX File
Every .sqlx file located in the definitions/ directory contains two primary blocks:
config {} Block: A declarative JSON-like block defining metadata, materialization strategy, target schema, tags, and documentation.
SQL Query Body: The standard GoogleSQL transformation statement. -- definitions/staging/stg_customer_orders.sqlx
- config {
- type: "view",
- schema: "staging",
- description: "Cleaned customer orders with standardized status codes",
- columns: {
- order_id: "Primary surrogate key for the order",
- order_timestamp: "UTC timestamp when order was placed",
- order_status: "Normalized order status: PENDING, COMPLETED, CANCELLED"
- },
- tags: ["daily_pipeline", "staging"]
- }
- SELECT
- order_id,
- TIMESTAMP(order_date) AS order_timestamp,
- UPPER(TRIM(status)) AS order_status,
- ROUND(order_amount, 2) AS order_amount
- FROM ${ref("raw_orders")}
- WHERE order_date IS NOT NULL
- Dynamic Dependency Resolution via ${ref()}
In enterprise environments, hardcoding table paths (e.g., FROM my-company-prod.raw_data.orders ) is a critical anti-pattern. If code containing hardcoded paths is executed in a development branch, it either fails due to IAM restrictions or accidentally reads and overwrites production datasets.
Dataform eliminates hardcoding via the ${ref("table_name")} or ${ref("schema", "table_name")} function:
Topological DAG Construction: Dataform inspects all ${ref()} statements across the entire repository to determine the exact upstream and downstream dependencies. You do not need to manually configure task schedules or orchestration order; Dataform automatically builds the directed acyclic graph.
Environment Portability: During compilation, Dataform dynamically substitutes ${ref("raw_orders")} with the appropriate project ID and dataset name configured for that specific execution environment (e.g., resolving to dev_sandbox_john.raw_orders in development and prod_analytics.raw_orders in production).
Referencing Unmanaged Tables via declare()
When your pipeline reads from raw tables that are ingested by external tools (such as Datastream, Fivetran, or Pub/Sub) and not authored within Dataform, you declare them using a .sqlx declaration file: -- definitions/sources/raw_orders.sqlx
- config {
- type: "declaration",
- database: "my-gcp-project",
- schema: "raw_ecom",
- name: "raw_orders",
- description: "Raw ingested order events streamed from Cloud Pub/Sub"
- }
- 3. Materialization Strategies and Incremental Processing
Dataform supports four primary materialization types configured in the type parameter of the config {} block:
Materialization Type BigQuery Output Object Execution Behavior & Lifecycle Cost & Latency Profile
view Logical BigQuery View Re-creates or updates the view DDL ( CREATE OR REPLACE VIEW ). Zero data storage. Incurs zero storage cost; query costs are billed to the consumer whenever the view is queried.
table Standard Physical Table Drops and completely re-creates the table on every run ( CREATE OR REPLACE TABLE AS SELECT ... ). Optimal for dimensions and moderate-sized datasets (< 10 GB) where full recalculation is fast.
incremental Partitioned Physical Table Re-creates the table on the first execution; on subsequent runs, scans and inserts/merges only new or updated rows . Essential for multi-terabyte or petabyte fact tables; cuts slot usage and scan bytes by 95%+.
assertion Data Quality Test Query Compiles into a query that returns invalid rows. Fails execution if row count > 0. Lightweight validation query.
Incremental Table Mechanics
On massive datasets, rebuilding multi-terabyte tables daily is prohibitively expensive and violates batch SLAs. Dataform's incremental materialization allows queries to process strictly delta changes. -- definitions/marts/fct_daily_transactions.sqlx
- config {
- type: "incremental",
- schema: "analytics_marts",
- uniqueKey: ["transaction_id"],
- bigquery: {
- partitionBy: "DATE(transaction_timestamp)",
- clusterBy: ["customer_id", "merchant_id"]
- },
- description: "Incremental daily transaction ledger partitioned by date"
- }
- SELECT
- transaction_id,
- customer_id,
- merchant_id,
- transaction_timestamp,
- amount,
- currency
- FROM ${ref("stg_transactions")}
- - The incremental filter applies ONLY during subsequent incremental runs
- ${when(incremental(), `
- WHERE transaction_timestamp > (
- SELECT MAX(transaction_timestamp)
- FROM ${self()}
- )
- `)}
- Critical Incremental Components
when(incremental(), ...) : A template helper that emits the enclosed SQL filter only when the table already exists in BigQuery and is undergoing an incremental run. During the initial build or a full refresh, this block is omitted.
${self()} : Evaluates to the target table's own project, dataset, and table name in the current environment, allowing the query to inspect the maximum existing watermark.
uniqueKey : Specifies one or more primary key columns. If uniqueKey is defined, Dataform executes a BigQuery MERGE statement under the hood, updating existing matching records and inserting new ones. If uniqueKey is omitted, Dataform executes a fast INSERT , which can introduce duplicate rows if source data is not strictly append-only.
Handling Full Refreshes: Running an execution with the --full-refresh flag forces Dataform to drop the incremental table and rebuild it from scratch. You can protect mission-critical historical tables from accidental truncation by adding protected: true to the config block.
4. Automated Data Quality Assurance: Assertions
Data pipelines in production must enforce data quality contracts before downstream consumers (such as Looker dashboards or Vertex AI models) consume the data. In Dataform, assertions are automated data validation tests. +─────────────────────────────────────────────────────────────────────────────────+
- | DATAFORM ASSERTION VALIDATION FLOW |
- +─────────────────────────────────────────────────────────────────────────────────+
- | |
- | [ Staging Tables Compiled ] ──> [ Execute Built-In Assertions ] |
- | - uniqueKey: ["order_id"] |
- | - nonNull: ["order_id", "order_timestamp"] |
- | - rowConditions: ["amount >= 0"] |
- | │ |
- | ┌────────────────────────┴────────────────────────┐ |
- | ▼ ▼ |
- | [ Assertions Pass ] [ Assertion FAILS ] |
- | (0 Violating Records) (Violations > 0) |
- | │ │ |
- | ▼ ▼ |
- | [ Execute Downstream Mart DAG ] [ HALT Execution Run ] |
- | - Materializes fct_orders - Prevents dirty data leak |
- | - Updates Looker Reporting Views - Alerts on-call engineer |
- +─────────────────────────────────────────────────────────────────────────────────+
- Built-in Assertions in the config {} Block
- You can declare standard data hygiene rules directly inside any table or view's configuration: config {
- type: "table",
- schema: "analytics",
- assertions: {
- uniqueKey: ["customer_id"],
- nonNull: ["customer_id", "signup_date", "email"],
- rowConditions: [
- "signup_date <= CURRENT_DATE()",
- "lifetime_spend >= 0.0"
- ]
- }
- }
When compiled, Dataform automatically generates hidden assertion tasks that query the materialized table. If any condition evaluates to FALSE (or if duplicates/nulls are detected), the assertion produces output rows, causing Dataform to fail the assertion job and halt downstream execution tasks .
Custom Assertion Files
For complex validations that span multiple tables (such as referential integrity checks or statistical anomaly boundaries), you can create dedicated .sqlx files with type: "assertion" : -- definitions/assertions/assert_order_customer_fk.sqlx
- config {
- type: "assertion",
- description: "Ensure every order references an existing registered customer"
- }
- - An assertion query must return VIOLATING rows (0 rows = PASS)
- SELECT
- o.order_id,
- o.customer_id
- FROM ${ref("fct_orders")} AS o
- LEFT JOIN ${ref("dim_customers")} AS c
- ON o.customer_id = c.customer_id
- WHERE c.customer_id IS NULL
- 5. Code Reusability with JavaScript Modules ( includes/ )
In large enterprise projects with hundreds of tables, identical SQL snippets—such as custom currency conversions, date truncations, regex scrubbers, or repetitive aggregation logic—are frequently repeated. Dataform provides an includes/ directory where you can define reusable JavaScript functions, constants, and macros.
Defining Reusable Helpers in includes/
Any .js file placed in the includes/ folder is globally accessible across all SQLX files: // includes/analytics_helpers.js
- // Standardized currency conversion helper
- function convert_to_usd(amount_col, currency_col) {
- return `
- CASE
- WHEN ${currency_col} = 'USD' THEN ${amount_col}
- WHEN ${currency_col} = 'EUR' THEN ${amount_col} * 1.08
- WHEN ${currency_col} = 'GBP' THEN ${amount_col} * 1.27
- ELSE ${amount_col}
- END
- `;
- }
- // Reusable date grouping macro
- const standard_fiscal_quarter = (date_col) => `
- CONCAT('FY', EXTRACT(YEAR FROM ${date_col}), '-Q', EXTRACT(QUARTER FROM ${date_col}))
- `;
- module.exports = {
- convert_to_usd,
- standard_fiscal_quarter
- };
- Invoking JavaScript Helpers in SQLX
You invoke exported JavaScript macros directly inside SQLX template literals: -- definitions/marts/fct_sales_summary.sqlx
- config {
- type: "table",
- schema: "finance"
- }
- SELECT
- sale_id,
- transaction_date,
- ${analytics_helpers.standard_fiscal_quarter("transaction_date")} AS fiscal_quarter,
- original_amount,
- currency_code,
- ${analytics_helpers.convert_to_usd("original_amount", "currency_code")} AS amount_usd
- FROM ${ref("raw_sales")}
During compilation, Dataform evaluates the JavaScript expressions and inlines the generated SQL strings before transmitting the query to BigQuery.
6. Enterprise Workspaces, CI/CD, and Multi-Environment Governance
Enterprise data engineering teams require strict isolation between development sandboxes, continuous integration (CI) testing, staging environments, and production analytical warehouses. +─────────────────────────────────────────────────────────────────────────────────+
- | DATAFORM MULTI-ENVIRONMENT CI/CD PIPELINE |
- +─────────────────────────────────────────────────────────────────────────────────+
- | |
- | DEVELOPER WORKSPACE |
- | - Feature Branch (git checkout -b feature/new-marts) |
- | - Isolated BigQuery Dataset: dev_sandbox_alice |
- | - Tests SQLX changes locally against development schema |
- | │ |
- | ▼ Git Push & Pull Request |
- | CONTINUOUS INTEGRATION (CI) TRIGGER |
- | - Cloud Build / GitHub Actions invokes Dataform CLI compilation |
- | - Compiles DAG with test variables; runs assertions on staging dataset |
- | │ |
- | ▼ Merge to 'main' branch |
- | PRODUCTION RELEASE & WORKFLOW CONFIGURATION |
- | - Release Configuration: Compiles daily from 'main' branch |
- | - Project Override: my-company-prod |
- | - Dataset Override: analytics_production |
- | - Workflow Schedule: Executes daily at 02:00 UTC using Service Account |
- +─────────────────────────────────────────────────────────────────────────────────+
- Release Configurations and Compilation Overrides
Dataform decouples code definitions from environment deployment settings through Release Configurations :
Git Commit / Branch Pinning: A release configuration specifies which Git branch (e.g., main ), tag, or commit hash to compile.
Project and Dataset Overrides: You configure rules that rewrite database and dataset targets during compilation without altering a single line of SQLX code:
In development: Database is set to analytics-dev and schema prefix is set to dev_ . An ${ref("orders")} call resolves to analytics-dev.dev_staging.orders .
In production: Database is set to analytics-prod with zero prefix. The same ${ref("orders")} call automatically resolves to analytics-prod.staging.orders .
Compilation Variables: Custom variables passed into JavaScript logic to toggle feature flags or date ranges per environment.
Workflow Configurations and Execution Scheduling
A Workflow Configuration defines the execution schedule and operational scope for a compiled release:
Execution Frequency: Configured using standard 5-field cron expressions.
Selective Execution via Tags: Rather than executing the entire repository, you can target specific sub-DAGs using tags (e.g., executing only models tagged with hourly_pipeline every 60 minutes, while running daily_finance at midnight).
Service Account Delegation: Executes under a dedicated Google Cloud Service Account granted strict BigQuery IAM permissions ( roles/bigquery.dataEditor , roles/bigquery.jobUser ), guaranteeing auditability and least-privilege compliance.
7. Prompting LLMs for Query Generation and Data Preparation
Blueprint topic 1.2 lists "prompting LLMs for query generation" alongside Dataform, Dataflow and Cloud Data Fusion as a way of preparing and cleaning data. The exam is not testing prompt craft; it is testing whether you know where generated SQL is allowed to land and what still has to validate it .
The assisted-authoring surfaces
Surface What it does Where the output goes
Gemini in BigQuery — SQL generation Natural-language prompt in the editor produces a query against the tables in scope, using table and column metadata for grounding The SQL editor, as a draft a human runs
SQL completion and explanation Completes partially typed SQL; explains an inherited 400-line query in plain language The editor
Data canvas Graph-based exploration where each node is a generated query, so you can branch and compare analyses Saved queries and charts
Data preparation suggestions Inspects sampled data and proposes cleansing, standardization and join-key transformations A prep pipeline you review, not a silent mutation
AI.GENERATE_TEXT / remote Vertex models Calls an LLM as a SQL function over rows — classification, summarization, extraction A table or view, inside your pipeline
The first four assist a human authoring step. Only the last runs inside production data flow, and that distinction is exactly what scenario questions turn on.
Why generated SQL still goes through Dataform
A model that writes a plausible query has no idea that orders.amount is gross of refunds, that dim_customer is a Type 2 dimension needing an is_current filter, or that the fact table demands a partition filter. Generated SQL is a draft , and the governed path for a draft is the same path as hand-written SQL:
Commit the query into a SQLX model in the Dataform repository, replacing literal table names with ${ref(...)} so lineage is real.
Attach assertions — nonNull , uniqueKey , rowConditions — so a wrong join grain or a silent fan-out fails the run instead of publishing bad numbers.
Let the release configuration compile it into dev , then promote through the environment ladder after the assertions pass.
Review the diff in version control like any other change.
Exam Trap: An option that schedules a natural-language prompt to regenerate a production transformation on every run is always wrong. Generation is non-deterministic — the same prompt can produce a differently-shaped query tomorrow. Production pipelines must run committed, reviewed, asserted SQL; the model's role ends when the SQL enters version control.
Governance note: Prompts and the metadata sent for grounding are subject to the same data-residency rules as the underlying tables, so a scenario with an EU-only processing mandate must use a region where the assistive feature is available rather than routing prompts to a US endpoint.
8. Architectural Anti-Patterns and Exam Traps
Production Scenario Architectural Anti-Pattern Correct Google Cloud Architecture
Hardcoded Environment Paths ; A data engineer writes SQLX models with explicit table targets: 'FROM my-prod-project.raw_data.events'. Hardcoding projects and datasets prevents environment portability, risking accidental corruption of production tables during developer testing. Always wrap table references in the ${ref("table_name")} function. Use Dataform release configurations to manage project and dataset redirection across dev, test, and prod.
Full Refresh Fact Table Rebuilds ; A daily pipeline drops and re-creates a 30 TB clickstream table using type: "table" , taking 45 minutes and burning massive slot-hours. Performing full re-computations on append-heavy or time-series fact tables. Configure type: "incremental" with bigquery.partitionBy and define a watermark filter inside ${when(incremental(), ...)} . This restricts daily runs to scanning strictly the preceding 24 hours of data.
Missing Incremental Primary Keys ; An engineer implements an incremental table using when(incremental(), ...) but omits the uniqueKey declaration in the config block. Ingesting upstream retries or reprocessing partitions causes duplicate rows to append repeatedly to the table. Declare uniqueKey: ["transaction_id"] in the config block. Dataform will generate a BigQuery MERGE statement that automatically updates existing keys and inserts new ones.
Silent Downstream Data Corruption ; A pipeline loads null-corrupted customer records into production tables. Downstream Looker dashboards display broken aggregations before the engineering team notices. Allowing pipelines to complete without verifying data quality assertions. Implement built-in assertions ( nonNull , uniqueKey , rowConditions ) or custom assertion files. Dataform halts DAG execution immediately upon assertion failure, preventing dirty data from propagating to production.