9.1 SQL Pipelines with Dataform & Scheduled Queries

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:

1. 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.
2. 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.
3. 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:

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:

1. A config Block: Declares the materialization type (table, view, or incremental), target schema/dataset, description, column documentation, tags, and built-in assertions.
2. 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:

1. It resolves ${ref('...')} statements to determine exact upstream dependencies.
2. It automatically compiles a Directed Acyclic Graph (DAG) establishing the required topological execution order.
3. It allows independent models to run concurrently across multiple BigQuery jobs while ensuring dependent models wait until upstream tables finish successfully.
4. 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):

---

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.