Google Cloud Dataform tutorial for building tested SQL pipelines in BigQuery

Google Cloud Dataform: What It Is, When to Use It, and How to Use It

by

It starts with one scheduled query in BigQuery. Then the sales report needs a cleaned orders table, so you add another one. Six months later you have twenty queries, nobody remembers which must run first, and one small change quietly breaks the Monday dashboard.

Google Cloud Dataform brings order to that mess. You keep writing SQL, and Dataform works out the run order, tests your data, tracks your changes and runs everything on a schedule.

Last reviewed: November 2026

In this guide:

What Is Google Cloud Dataform?

Google Cloud Dataform is a service for building, testing and scheduling SQL data transformations in BigQuery. Your raw data is already in BigQuery, and Dataform helps you turn it into clean tables that people can trust.

Google Cloud Dataform tutorial for building tested SQL pipelines in BigQuery

Think of a restaurant kitchen. Raw vegetables come in, and they must be washed before they are chopped, and chopped before they are cooked. A taster checks the dish before it reaches the customer. Google Cloud Dataform is the head chef who knows the order of every step and stops a bad dish from leaving the kitchen.

These are the main terms you need:

TermSimple meaning
RepositoryThe home of your project and its files
WorkspaceYour personal area for making changes
SQLX fileA SQL file with a small settings block on top
DeclarationTells Dataform about a table that already exists
AssertionA data quality test
Incremental tableA table that adds new data instead of rebuilding everything

What Is SQLX?

If you know SQL, you already know most of this. SQLX is normal SQL with a config block on top.

config {
  type: "table"
}

SELECT city, SUM(amount) AS total_sales
FROM ${ref("stg_orders")}
GROUP BY city

The magic is in ${ref("stg_orders")}. Instead of typing a full table name, you point to another step. Dataform reads these links, draws a dependency graph, and runs each step in the correct order.

Google Cloud Dataform vs Dataflow

The names are almost the same, so people mix them up all the time. They do very different jobs.

PointDataformDataflow
Main jobTransforms data already in BigQueryProcesses data as it moves between systems
You writeSQLXApache Beam code, or use a template
Runs onBigQueryWorker virtual machines
Live streaming dataNot its main purposeYes
Best forData analysts and SQL developersData engineers building pipelines

They can also work together. Dataflow brings raw data into BigQuery, and Dataform turns it into reporting tables.

Why Use Google Cloud Dataform?

  • Correct run order: Google Cloud Dataform works out the order from your ref links, so you do not have to.
  • Built-in data tests: Check for duplicates, empty values and impossible numbers.
  • Version control: Connect your repository to Git and see who changed what.
  • Clear documentation: Table descriptions in your code can appear in BigQuery.
  • Lower query costs: Incremental tables can process only new data.
  • No servers: Your SQL runs in BigQuery, and you do not manage any machines.

When to Use Google Cloud Dataform

Google Cloud Dataform is a good choice when:

  • Your data is already in BigQuery.
  • You have many SQL steps that depend on each other.
  • Several people change the same transformations.
  • Wrong numbers on a dashboard would be a serious problem.

You may not need it when:

  • You only have one or two simple queries. A BigQuery scheduled query is enough.
  • You need to process live data outside BigQuery. Look at Dataflow.
  • You work across many data warehouses. dbt may suit you better, because it supports many platforms.

How to Set Up Google Cloud Dataform

We will build a small Google Cloud Dataform sales pipeline for the same sample online shop used in our Google Cloud Run tutorial. Use a test project, and replace PROJECT_ID and PROJECT_NUMBER with your own values.

Step 1: Create sample data. Open BigQuery and run this SQL. Notice the extra space in " Delhi" and the cancelled order. We will fix both.

CREATE SCHEMA IF NOT EXISTS shop_raw OPTIONS (location = "asia-south1");

CREATE OR REPLACE TABLE shop_raw.orders (
  order_id STRING,
  city STRING,
  amount INT64,
  status STRING,
  order_time TIMESTAMP
);

INSERT INTO shop_raw.orders VALUES
  ("A1001", " Delhi", 1200, "PAID", TIMESTAMP "2026-11-01 10:15:00"),
  ("A1002", "Mumbai", 850, "PAID", TIMESTAMP "2026-11-01 11:30:00"),
  ("A1003", "Delhi", 300, "CANCELLED", TIMESTAMP "2026-11-01 12:45:00"),
  ("A1004", "Delhi", 700, "PAID", TIMESTAMP "2026-11-01 15:20:00"),
  ("A1005", "Pune", 1500, "PAID", TIMESTAMP "2026-11-02 09:10:00"),
  ("A1006", "Mumbai", 650, "PAID", TIMESTAMP "2026-11-02 10:05:00");

Step 2: Turn on the APIs and create a service account. Dataform uses this account to run your SQL.

gcloud services enable dataform.googleapis.com bigquery.googleapis.com

gcloud iam service-accounts create dataform-runner \
  --display-name="Runs Dataform workflows"

for role in roles/bigquery.dataEditor roles/bigquery.dataViewer roles/bigquery.jobUser; do
  gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="serviceAccount:dataform-runner@PROJECT_ID.iam.gserviceaccount.com" \
    --role="$role"
done

Step 3: Allow Dataform’s own service agent to use that account.

for role in roles/iam.serviceAccountUser roles/iam.serviceAccountTokenCreator; do
  gcloud iam service-accounts add-iam-policy-binding \
    dataform-runner@PROJECT_ID.iam.gserviceaccount.com \
    --member="serviceAccount:service-PROJECT_NUMBER@gcp-sa-dataform.iam.gserviceaccount.com" \
    --role="$role"
done

If this says the service agent does not exist, create the repository in the next step, then run this command again. Our Google Cloud IAM guide explains roles and service accounts in more detail.

Step 4: Create the Google Cloud Dataform repository and workspace.

  1. Open the BigQuery Dataform page and click Create repository.
  2. Enter shop-pipeline as the Repository ID and select asia-south1 as the Region.
  3. Select dataform-runner as the Service account and click Create.
  4. Click Go to repositories and open shop-pipeline.
  5. Click Create development workspace, enter my-workspace, and click Create.
  6. Open the workspace and click Initialize workspace.

Step 5: Open workflow_settings.yaml and change it to this. These settings decide where your tables and test results go.

defaultProject: PROJECT_ID
defaultLocation: asia-south1
defaultDataset: shop_reports
defaultAssertionDataset: shop_checks
dataformCoreVersion: "3.0.0"

If your workspace already shows a newer dataformCoreVersion, keep that value.

To add a file, click the More menu next to definitions/, choose Create file, enter the path, and click Create file.

Real-Time Google Cloud Dataform Examples

Example 1: Build a Daily Sales Report

Problem: The raw orders table has extra spaces and cancelled orders. The owner wants clean sales by city for each day.

Step 1: Tell Dataform about the raw table. Create definitions/sources/raw_orders.sqlx:

config {
  type: "declaration",
  schema: "shop_raw",
  name: "orders"
}

Step 2: Clean the orders. Create definitions/staging/stg_orders.sqlx:

config {
  type: "view",
  description: "Clean orders with paid status only",
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "city", "amount"],
    rowConditions: ["amount > 0"]
  }
}

SELECT
  order_id,
  TRIM(city) AS city,
  amount,
  DATE(order_time) AS order_date
FROM ${ref("orders")}
WHERE status = "PAID"

Step 3: Build the report. Create definitions/marts/daily_city_sales.sqlx:

config {
  type: "table",
  dependOnDependencyAssertions: true,
  description: "Paid sales by city for each day"
}

SELECT
  order_date,
  city,
  COUNT(*) AS total_orders,
  SUM(amount) AS total_sales
FROM ${ref("stg_orders")}
GROUP BY order_date, city

Step 4: Click Start execution, choose All actions, and click Start execution again. If Google asks for permission, click Allow.

You never told Dataform the order, but it reads the links and runs this path: orders, stg_orders, the tests, then daily_city_sales.

Step 5: Check the result in BigQuery.

SELECT * FROM shop_reports.daily_city_sales ORDER BY order_date, city;
order_datecitytotal_orderstotal_sales
2026-11-01Delhi21900
2026-11-01Mumbai1850
2026-11-02Mumbai1650
2026-11-02Pune11500

Delhi is one clean row with 1900. The extra space is gone, and the cancelled order of 300 is not counted.

Example 2: Catch Bad Data Before It Reaches the Dashboard

Problem: A bug in the billing system sends a negative amount. Without a check, the sales report would be wrong, and nobody would notice until a manager asks why.

Add a bad row in BigQuery:

INSERT INTO shop_raw.orders VALUES
  ("A1007", "Pune", -50, "PAID", TIMESTAMP "2026-11-02 12:00:00");

Now run the workflow again. The amount > 0 test in Example 1 fails, and you will see the failure in Workflow Execution Logs.

This is where dependOnDependencyAssertions: true matters. It makes the sales table wait for those tests. Without it, a failed test does not stop the next step, so bad data could still reach your report.

You can also write your own test. Any query that returns rows counts as a failure. Create definitions/checks/no_future_orders.sqlx:

config {
  type: "assertion"
}

SELECT *
FROM ${ref("stg_orders")}
WHERE order_date > CURRENT_DATE()

To fix the example, delete the bad row and run the workflow again.

DELETE FROM shop_raw.orders WHERE order_id = "A1007";

Example 3: Save Money with an Incremental Table

Problem: The orders table grows every day. Rebuilding years of history each night wastes time and BigQuery money.

Solution: Process only recent data. Create definitions/marts/orders_incremental.sqlx:

config {
  type: "incremental",
  uniqueKey: ["order_id"],
  description: "Adds new and changed paid orders"
}

SELECT
  order_id,
  city,
  amount,
  order_date
FROM ${ref("stg_orders")}
${when(incremental(),
  `WHERE order_date >= (SELECT MAX(order_date) FROM ${self()})`)}

Here is what happens:

  • First run: Dataform builds the full table.
  • Later runs: The WHERE part is added, so only recent orders are read.
  • ${self()}: This points to the table itself, so the query can find its latest date.
  • uniqueKey: If the same order arrives again, Dataform merges it instead of adding a duplicate.
  • Full refresh: When you need to rebuild everything, a full refresh ignores the WHERE part.

How to Schedule Google Cloud Dataform

Scheduling has two parts. A release configuration prepares your code, and a workflow configuration runs it. For real production work, connect your repository to Git first, and commit your files.

  1. Open your repository, click Releases & scheduling, then click Create production release.
  2. Enter production as the Release ID, and keep the Git commitish as main.
  3. Under Repeats, choose Custom, enter 0 1 * * * as the schedule, choose India Standard Time, and click Create.
  4. In the Workflow configurations section, click Create.
  5. Enter production as the Configuration ID, and select the production release.
  6. Enter 0 2 * * * as the custom schedule, choose the same time zone, select All actions, and click Create.

Your code compiles at 1:00 AM, and the pipeline runs at 2:00 AM. Google recommends a gap of at least one hour, so the run uses your latest code.

If a Dataform run is one step in a bigger process, our Google Cloud Workflows guide shows how to connect several services together.

Google Cloud Dataform Pricing

Here is the good news: Google Cloud Dataform itself is free. You still pay for the services it uses:

  • BigQuery: Every table or test runs a query, and those queries are charged by BigQuery.
  • Cloud Logging: Dataform uses it to record workflow runs.
  • Other tools: Services such as Cloud Scheduler or Workflows are billed separately if you use them.

So the real cost depends on how much data your SQL reads. See the official pricing page for details.

Google Cloud Dataform Mistakes to Avoid

  • Typing table names by hand: Use ref, or Dataform cannot see the dependency.
  • Assuming a failed test stops everything: It does not by default. Add the assertion dependency as shown in Example 1.
  • Using different locations: Your datasets and defaultLocation must match.
  • Rebuilding huge tables every run: Use incremental tables for large, growing data.
  • Using a personal login for schedules: Use a service account, so runs do not depend on one person.
  • Skipping descriptions: Future you will not remember what tmp_orders_2 means.

Conclusion

Google Cloud Dataform lets you treat SQL like proper software. It keeps the run order correct, tests your data, tracks changes and runs on a schedule, all while your work stays in plain SQL.

Start small. Move one messy report into a repository, add a uniqueKey test, and run it. You will probably find a data problem you did not know you had. For more details, read the official Google Cloud Dataform documentation.

FAQs

Q1: Is Google Cloud Dataform free?
Yes, the service is free. You pay for the BigQuery queries it runs and other services you use with it.

Q2: What is the difference between Google Cloud Dataform and Dataflow?
Dataform transforms data inside BigQuery using SQL. Dataflow processes batch and streaming data using Apache Beam workers.

Q3: Do I need to know JavaScript for Google Cloud Dataform?
No. SQL is enough for most work. JavaScript is optional and helps you reuse code.

Q4: Can Google Cloud Dataform load data into BigQuery?
Its main job is the transformation step after data is already in BigQuery. Use other tools to bring the raw data in.

Q5: What is the difference between Google Cloud Dataform and dbt?
Both help you manage SQL transformations. Dataform is built into BigQuery, while dbt works across more data platforms.

Q6: Does a failed assertion stop the pipeline?
Not by default. Add the assertions as dependencies if you want the next step to wait for them.

Leave a Comment