Study guide
9.1 SQL Pipelines with Dataform & Scheduled Queries
Google Cloud Associate Data Practitioner Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
About the Associate Data Practitioner Exam
2 Chapter 1: Data Processing Paradigms & Ingestion Strategies
1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation
3 Chapter 2: Data Formats & Storage Solution Selection
2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services
4 Chapter 3: Loading Data & Modern Ingestion Architectures
3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data
5 Chapter 4: BigQuery SQL Analytics & Query Foundations
4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses
6 Chapter 5: BigQuery Performance Optimization & Cost Governance
5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls
7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation
6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards
8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI
7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections
9 Chapter 8: Pipeline Engines & Transformation Services
8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers
10 Chapter 9: Workflow Orchestration, Automation & Monitoring
9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring
11 Chapter 10: Access Control, Governance & Data Sharing
10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)
12 Chapter 11: Lifecycle Management, Reliability & Compliance
11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance
9.1 SQL Pipelines with Dataform & Scheduled Queries
Key Takeaways Explain This Topic AI
BigQuery Scheduled Queries provide lightweight, cron-based automation for single SQL statements, but lack native dependency resolution, test assertions, and branching.
Dataform manages the transformation layer ("T" in ELT) natively inside BigQuery, compiling SQLX models into Directed Acyclic Graphs (DAGs) through dynamic ${ref()} functions.
Dataform built-in assertions (uniqueKey, nonNull, rowConditions) and custom SQL assertions enforce automated data quality gates, preventing corrupted data from propagating to downstream marts.
Dataform environments isolate development workspaces, Git repositories, compilation targets, and release configurations across development, staging, and production Google Cloud projects.
Last updated: October 2026
SQL Pipelines with Dataform & Scheduled Queries
Core Focus: Modern data platforms prioritize in-warehouse transformation (ELT) to maximize performance and reduce operational overhead. Within Google Cloud, BigQuery Scheduled Queries and Dataform represent the two native mechanisms for automating SQL workflows. Mastering their operational mechanics, dependency management, and testing frameworks is essential for the Google Cloud Associate Data Practitioner examination.
In modern cloud data architectures, extracting and loading raw data directly into the analytical warehouse allows organizations to leverage distributed compute engines for downstream transformation. Rather than maintaining external computing clusters to parse, join, and aggregate datasets before persistence, in-warehouse transformations execute directly inside Google BigQuery. Automating these SQL operations reliably requires choosing between lightweight scheduled tasks and comprehensive, version-controlled transformation frameworks.
In-Warehouse Transformation Automation: Why Keep Processing in BigQuery?
In an Extract-Load-Transform (ELT) paradigm, raw data lands directly in BigQuery staging datasets via streaming ingestion, batch file loads from Cloud Storage, or database replication services. Performing the subsequent cleaning, deduplication, and dimensional modeling inside BigQuery provides significant architectural benefits:
Zero Data Egress and Network Overhead: Keeping transformations inside BigQuery eliminates the need to serialize, transfer, and deserialize massive datasets across external virtual machines or external compute clusters like Apache Spark.
Massively Parallel Slot Execution: Transformations leverage BigQuery Dremel engine, which dynamically allocates thousands of worker slots to execute distributed SQL operations across Colossus storage blocks connected via the petabit Jupiter network.
Declarative Maintenance: SQL-native pipelines allow analytics engineers and data practitioners to author transformations using standard ANSI SQL, avoiding the operational complexity of managing containerized runtimes or third-party orchestrators for purely relational operations.
However, in-warehouse transformations cannot execute in a vacuum. Production pipelines require scheduling, automated execution order, failure recovery, data validation gates, and environment segregation.
BigQuery Scheduled Queries
BigQuery Scheduled Queries provide a serverless, managed scheduling mechanism built directly into the BigQuery console and API, powered by the BigQuery Data Transfer Service infrastructure. +-------------------------------------------------------------------------+
- | BigQuery Scheduled Query |
- | |
- | [ Cron Schedule ] ---> [ SQL DDL / DML / Query ] |
- | | |
- | v |
- | [ Destination Table Disposition ] |
- | - WRITE_TRUNCATE (Overwrite) |
- | - WRITE_APPEND (Append) |
- | | |
- | +---> [ Pub/Sub Notification ] |
- | +---> [ Email Notification ] |
- +-------------------------------------------------------------------------+
- Operational Mechanics and Capabilities
Schedule Syntax: Scheduled queries use the Data Transfer Service schedule format, in UTC, such as every 24 hours , every day 02:00 , or every mon,wed 09:00 (not Unix cron strings). The minimum interval between runs is 5 minutes.
Runtime Parameters: Scheduled Queries support dynamic runtime parameters, including @run_time and @run_date . These parameters allow queries to filter ingested partitions dynamically, processing only the specific sliding window corresponding to the execution time: -- Incremental partition load using Scheduled Query runtime parameters
- INSERT INTO `analytics_production.daily_active_users` (activity_date, user_id, session_count)
- SELECT
- DATE(@run_time) AS activity_date,
- user_id,
- COUNT(DISTINCT session_id) AS session_count
- FROM `raw_events.clickstream_partitioned`
- WHERE _PARTITIONDATE = DATE(@run_time)
- GROUP BY 1, 2;
Destination Table Write Preferences: A scheduled SELECT that writes to a destination table offers two write preferences:
WRITE_TRUNCATE ( Overwrite table ): Replaces the destination table (or the targeted partition) with the new results.
WRITE_APPEND ( Append to table ): Appends the results to the existing rows.
DDL and DML scheduled queries (for example, a MERGE ) do not use a write preference; the statement itself defines the change.
Service Account Delegation: By default, scheduled queries execute under the identity of the user who configured them. In enterprise environments, this creates fragile pipelines vulnerable to failure when employees change roles or leave. Best practice mandates transferring scheduled query ownership to a dedicated Google Cloud Service Account endowed with minimal necessary IAM permissions ( roles/bigquery.jobUser and roles/bigquery.dataEditor ).
Notification Channels: Built-in integration allows scheduled queries to dispatch completion status to Cloud Pub/Sub topics or send failure notifications via email.
Limitations of Scheduled Queries
While convenient for isolated summary tables, BigQuery Scheduled Queries exhibit severe architectural limitations for enterprise pipelines:
No Native Dependency Resolution: Scheduled queries cannot be chained natively. If Query B depends on the output of Query A, practitioners must resort to estimating execution times and scheduling Query B at an arbitrary offset (e.g., Query A at 02:00, Query B at 02:30). If Query A experiences resource contention or data volume spikes, Query B runs against incomplete or stale data.
Absence of Data Quality Gates: Scheduled queries execute the SQL statement and commit results directly. There is no native mechanism to test data validity (such as null checks or uniqueness assertions) before downstream consumers query the table.
No Branching or Dynamic Logic: Workflows requiring conditional execution, dynamic parameter passing, or rollback mechanisms cannot be authored using scheduled queries.
Limited Version Control: Queries configured manually in the console bypass standard software development lifecycle (SDLC) practices, lacking automated code review, branch merging, and staging promotion.
Creating and Managing Scheduled Queries
You create a scheduled query from the BigQuery console (write the query, then choose Schedule ) or from the bq tool: bq query \
- -use_legacy_sql=false \
- -destination_table=reporting.daily_kpis \
- -display_name='Daily KPI refresh' \
- -schedule='every day 02:00' \
- -replace=true \
- -service_account_name=sq-runner@analytics-prod.iam.gserviceaccount.com \
- 'SELECT DATE(order_ts) AS order_date, SUM(total) AS revenue
- FROM sales.orders
- WHERE DATE(order_ts) = DATE_SUB(@run_date, INTERVAL 1 DAY)
- GROUP BY order_date'
Behind the scenes a scheduled query is a Data Transfer Service transfer configuration , so you manage it like one:
List: bq ls --transfer_config --transfer_location=US shows every scheduled query in that location.
Inspect runs: The console's Scheduled queries page shows each run, its status, and its error message.
Pause or edit: Disable the schedule during maintenance, or change the query, schedule, or service account.
Backfill: Schedule a backfill to rerun the query for past dates; each run gets its own @run_date , so parameterized queries rebuild exactly the missing days.
Notify: Send run notifications to a Pub/Sub topic or email the owner on failure.
Other Ways to Schedule SQL: Cloud Scheduler and Cloud Composer
The exam guide lists three schedulers. Pick by what else the job has to do:
Scheduler Schedule format What it triggers Choose it when
BigQuery scheduled queries every day 02:00 style, UTC, 5-minute minimum One SQL statement or script in BigQuery A single summary table or MERGE refreshes on a timetable
Cloud Scheduler Unix cron ( 0 2 * * * ) with any time zone you choose An HTTP(S) endpoint, a Pub/Sub topic, or an App Engine target, with retries You need to start something outside BigQuery on a clock: a Workflows execution, a Cloud Run job, a Dataflow template launch, or a Dataform workflow invocation
Cloud Composer (Airflow) DAG schedule (cron or presets) A whole DAG of tasks across services The SQL is one step in a multi-system pipeline with dependencies, sensors, and retries
A typical Cloud Scheduler pattern publishes a message to Pub/Sub at 02:00 in the company's time zone, and that message starts a Cloud Run function or a Workflows execution that runs the BigQuery job and the steps around it.
Dataform: Native In-Warehouse Data Modeling
Dataform is Google Cloud native, fully managed service designed specifically to manage the "T" in ELT pipelines. Built directly into BigQuery, Dataform enables data teams to develop, test, version control, and orchestrate SQL transformation workflows at scale. +-----------------------------------------------------------------------------------------+
- | Dataform Architecture |
- | |
- | [ Git Repo (GitHub/GitLab) ] <---> [ Dev Workspaces ] |
- | | |
- | (SQLX Compilation) |
- | | |
- | v |
- | [ Directed Acyclic Graph (DAG) ] |
- | | |
- | +---------------------------+---------------------------+ |
- | | | |
- | v v |
- | [ stg_customers.sqlx ] [ stg_orders.sqlx ] |
- | | | |
- | +---------------------------+---------------------------+ |
- | | |
- | v |
- | [ Assertion Quality Gates ] |
- | - nonNull, uniqueKey, custom |
- | | (Passes) |
- | v |
- | [ fct_daily_revenue.sqlx ] |
- | | |
- | v |
- | [ Release & Workflow Invocation ] |
- +-----------------------------------------------------------------------------------------+
- SQLX Syntax and Modeling
Dataform extends standard SQL with SQLX , a declarative language combining ANSI SQL, JavaScript logic, and configuration blocks.
Each SQLX file defines a single table, view, or assertion. A typical SQLX file includes:
A config Block: Declares the materialization type ( table , view , or incremental ), target schema/dataset, description, column documentation, tags, and built-in assertions.
A SQL Query: Specifies the transformation logic, referencing upstream dependencies dynamically. -- File: definitions/staging/stg_orders.sqlx
- config {
- type: "table",
- schema: "staging",
- description: "Cleaned orders dataset with deduplicated records",
- columns: {
- order_id: "Primary key identifying unique orders",
- customer_id: "Foreign key referencing staging.stg_customers",
- order_total: "Total monetary transaction value"
- },
- assertions: {
- uniqueKey: ["order_id"],
- nonNull: ["order_id", "customer_id"]
- }
- }
- SELECT
- order_id,
- customer_id,
- order_status,
- ROUND(CAST(order_total AS NUMERIC), 2) AS order_total,
- TIMESTAMP(order_timestamp) AS order_timestamp
- FROM
- ${ref("raw_orders")}
- WHERE
- order_status IS NOT NULL
- QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_timestamp DESC) = 1
- Dependency Graphs (DAGs) via ref()
The most transformative feature of Dataform is the ${ref('table_name')} function. Instead of hardcoding static dataset and project paths (such as typing FROM my_prod_project.analytics.raw_orders ), developers use ${ref('raw_orders')} .
When Dataform compiles a project:
It resolves ${ref('...')} statements to determine exact upstream dependencies.
It automatically compiles a Directed Acyclic Graph (DAG) establishing the required topological execution order.
It allows independent models to run concurrently across multiple BigQuery jobs while ensuring dependent models wait until upstream tables finish successfully.
It seamlessly adjusts target project and dataset prefixes depending on whether the code is running in a local developer sandbox or a production compilation environment.
Incremental Modeling
For high-volume tables containing millions or billions of rows, regenerating the entire table on every run is cost-prohibitive. Dataform supports native incremental tables using the when(incremental(), ...) block: config {
- type: "incremental",
- schema: "analytics",
- uniqueKey: ["event_id"]
- }
- SELECT
- event_id,
- event_timestamp,
- user_id,
- event_name
- FROM
- ${ref("stg_events")}
- ${when(incremental(), `
- WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM ${self()})
- `)}
During compilation, Dataform automatically manages the DDL/DML, executing a full table creation on initial deployment and generating an optimized MERGE or INSERT statement on subsequent incremental executions.
Data Quality Assertions as Quality Gates
Dataform incorporates automated data quality testing directly into the compilation and execution pipeline:
Built-in Assertions: Declared directly inside the config block of a table (e.g., uniqueKey , nonNull , rowConditions: ["order_total >= 0"] ).
Custom SQL Assertions: Standalone .sqlx files written as queries that return rows violating business constraints. If the query returns zero rows, the assertion passes; if it returns one or more rows, the assertion fails: -- File: definitions/assertions/assert_no_negative_revenue.sqlx
config { type: "assertion" }
- SELECT
- order_id,
- order_total
- FROM
- ${ref("stg_orders")}
- WHERE
- order_total < 0
Execution Quality Gates: If an assertion fails, Dataform can halt execution of all downstream dependent models. This ensures reporting data marts and executive dashboards are never corrupted by malformed upstream transactions.
Workspace Lifecycle and Enterprise Release Management
Dataform enforces rigorous software engineering practices across data modeling:
Development Workspaces: Isolated web-based or command-line developer environments where practitioners can edit code, compile graphs, and execute runs against isolated developer datasets without impacting production.
Git Integration: Connects to GitHub, GitLab, Bitbucket, and Azure DevOps repositories. Code changes are reviewed through pull requests and merged into designated release branches.
Compilation Environments: Configurations defining project-level parameters, execution dataset overrides, and variable substitutions for different environments (e.g., Development, Staging, Production).
Release Configurations & Workflow Invocations: Automated release triggers compile the Git repository at set schedules (e.g., every 6 hours or on Git merge) and invoke workflow executions across target BigQuery environments.
Decision Matrix: Choosing the Right SQL Automation Tool
The table below contrasts BigQuery Scheduled Queries, Dataform, and cross-service orchestrators (such as Cloud Composer):
Evaluation Criteria BigQuery Scheduled Queries Dataform Cloud Composer (Managed Airflow)
Core Purpose Simple, periodic execution of standalone SQL queries. Comprehensive in-warehouse ELT data modeling and DAG pipelines. Enterprise cross-service workflow orchestration across heterogeneous systems.
Dependency Management None (manual schedule time offsets). Native, automatic DAG compilation via ${ref()} references. Full programmatic DAG orchestration via Python bitshift operators.
Quality Testing None. Native built-in assertions, custom SQL tests, and downstream blocking. Python sensors, custom assertions, and operator pre/post-execution hooks.
Scope of Services BigQuery only. BigQuery only. Google Cloud (GCS, Dataflow, Dataproc, BigQuery, Vertex AI) and multi-cloud systems.
Version Control Manual or third-party API automation. Native Git branching, PRs, and multi-environment compilation. Git-driven DAG deployment to Cloud Storage buckets.
Compute Footprint & Cost Serverless; charges only for BigQuery slot/bytes scanned. Serverless; charges only for underlying BigQuery query execution. Continuous cluster cost (GKE, Cloud SQL, Airflow web server) plus autoscaling workers.
Ideal Exam Fit Standalone reporting table updated nightly without dependencies. Multi-tier analytical data models (staging, core, mart) inside BigQuery. Pipelines spanning Cloud SQL, Cloud Storage, Dataflow, BigQuery, and Looker.
Common Exam Traps & Real-World Scenarios
Exam Tip: Pay close attention to questions describing a multi-step data warehouse transformation. If the pipeline involves multiple dependent BigQuery tables, version control, and data testing, Dataform is the designated Google Cloud solution. Never choose BigQuery Scheduled Queries for multi-table pipelines requiring dependency management.
Trap 1: Scheduling Queries with Artificial Time Offsets
The Trap: Recommending three separate BigQuery Scheduled Queries scheduled at 01:00, 01:30, and 02:00 to populate staging, intermediate, and reporting tables.
The Reality: This is an anti-pattern. If the 01:00 query is delayed due to high data volume or cluster quotas, the 01:30 query executes against unrefreshed data without erroring. Dataform resolves this completely by establishing strict parent-child DAG relationships.
Trap 2: Hardcoding Dataset Identifiers in Dataform Models
The Trap: Writing SQL queries inside Dataform using explicit project and dataset paths like SELECT * FROM prod_project.analytics.orders .
The Reality: Hardcoding paths defeats Dataform dependency compiler. Dataform cannot infer the execution DAG, cannot enforce quality gates, and prevents models from running safely in development or testing environments. The ${ref('orders')} function must always be used.
Trap 3: Deploying Cloud Composer for BigQuery-Only Transformations
The Trap: Provisioning an Apache Airflow environment in Cloud Composer purely to sequence a series of SQL views and tables inside BigQuery.
The Reality: While Cloud Composer can execute BigQuery SQL via operators, deploying a full Kubernetes-backed Airflow cluster solely for SQL transformations incurs substantial financial cost and operational maintenance. Dataform provides serverless, Git-integrated SQL orchestration natively inside BigQuery at zero additional orchestration cost.