|

Full Guide to BigQuery Dataform

1. What is Dataform

Dataform is a Google Cloud tool that helps teams manage data transformations inside BigQuery. It is useful when you have many SQL scripts and want to organize them in one project. With Dataform you can:

  • keep all logic in one repository,
  • see clear dependencies between tables,
  • add tests to check data quality,
  • run workflows automatically with schedules,
  • write documentation inside the same code.

The main idea: Dataform makes your data pipeline clean, safe, and easy to understand.

2. Basic Definitions

  • Repository: a place where all project code is stored. Usually connected to GitHub or GitLab. It allows version control and teamwork.
  • Workspace: a safe environment for development. You can test changes here before they go to production.
  • SQLX: extended SQL language. It adds special blocks like config, ref, source, assertion. These blocks describe tables, dependencies, and tests.
  • Dependency tree: a graph that shows which tables depend on others. It helps you understand execution order.
  • Compilation: process that converts SQLX into normal SQL for BigQuery. It checks code and builds correct queries.

3. Main Tools

  • Dataform Core: the main system. It lets you describe tables, tests, and documentation in SQLX.
  • Dataform CLI: command line tool. You use it to compile, run tests, and deploy. It is good for CI/CD pipelines.
  • Dataform API: allows integration with other services like Airflow or Workflows.
  • BigQuery Studio Integration: lets you use Dataform directly inside BigQuery interface. This is simple for analysts.

4. When to Use Dataform

You should use Dataform when:

  • you have many tables and want clear dependencies,
  • you need automatic checks for data quality,
  • you want to run workflows on schedule,
  • your team works together and needs version control,
  • you want documentation inside the project.

5. Step‑by‑Step Setup

Step 1. Create Repository

Create a new project in Dataform and connect it to GitHub/GitLab.
Why: repository keeps all code safe, allows rollback, and supports teamwork.

Step 2. Create Workspace

Make a workspace called dev. Inside create folders:

  • definitions for tables and views,
  • tests for quality checks,
  • includes for shared functions.
    Why: workspace separates development from production. Folders keep code organized.

Step 3. Define Sources

Use source to describe raw tables.
Why: clear source definition makes project transparent. If source changes, you only update one place.

Step 4. Write Transformations

Create SQLX files with business logic. Use ref() to connect tables.
Why: transformations turn raw data into useful analytics tables. Dependencies are automatic.

Step 5. Add Tests

Write assertion files to check data quality.
Why: tests protect against errors like null values or duplicate keys. They increase trust in data.

Step 6. Compile

Run compilation.
Why: Dataform checks code, builds correct SQL, and shows dependency graph.

Step 7. Run Workflow

Run manually or set schedule.
Why: automation keeps data fresh and reduces manual work.

Step 8. Documentation

Add descriptions for tables and columns.
Why: documentation helps new team members understand data quickly. It prevents confusion.

6. Practical Example (with detailed comments)

Source

-- Create a view with cleaned orders
config {
  type: "view",              -- we create a view, not a table
  schema: "staging",         -- schema for intermediate data
  name: "orders_clean"       -- name of the view
}

select
  order_id,                  -- unique id of the order
  customer_id,               -- id of the customer
  date(order_time) as order_date, -- convert timestamp to date
  amount                     -- order amount
from
  source("raw", "orders")    -- take data from raw source table
where
  amount > 0                 -- filter only positive amounts

Explanation: This view cleans raw orders. It removes negative or zero amounts. It prepares data for next steps.

Aggregated Table

-- Create a table with daily sales
config {
  type: "table",             -- we create a physical table
  schema: "analytics",       -- schema for analytics data
  name: "daily_sales"        -- name of the table
}

select
  order_date,                -- date of the order
  sum(amount) as total_sales, -- total sales per day
  count(distinct customer_id) as unique_customers -- number of unique buyers
from
  ref("orders_clean")        -- reference to cleaned orders view
group by
  order_date                 -- group by date

Explanation: This table aggregates sales by day. It shows total sales and number of unique customers. It uses ref() to connect with the cleaned orders view.

Test

-- Test: check for null values in daily sales
config {
  type: "assertion",         -- type = test
  schema: "tests",           -- schema for tests
  name: "daily_sales_not_null" -- name of the test
}

select
  order_date,
  total_sales
from
  ref("daily_sales")         -- check the daily sales table
where
  order_date is null         -- order_date must not be null
  or total_sales is null     -- total_sales must not be null

Explanation: This test checks that important columns are not empty. If null values appear, the test fails. This protects data quality.

Documentation

-- Add descriptions for table and columns
config {
  type: "table",
  schema: "analytics",
  name: "daily_sales",
  description: "Daily aggregated sales"
}

columns {
  order_date: "Order date"
  total_sales: "Total sales per day"
  unique_customers: "Number of unique customers"
}

Explanation: Documentation is inside the code. It describes table purpose and columns. This helps new analysts understand data faster.

7. Useful Tips (with reasons)

  • Use Git
    Reason: version control protects code, allows rollback, and supports teamwork with pull requests.
  • Organize Structure
    Reason: clear separation between staging, analytics, and tests makes project easy to understand. Raw data stays separate from analytics.
  • Document Everything
    Reason: descriptions help new analysts and prevent confusion. Without documentation, project becomes a black box.
  • Automate Runs
    Reason: scheduled runs keep data fresh and reduce manual work. Reports are always up to date.
  • Manage Security
    Reason: correct IAM roles protect data. Analysts can read but not change raw sources.
  • Visualize Dependencies
    Reason: dependency graph shows execution order and possible bottlenecks. It helps debugging.
  • Write Tests
    Reason: tests quickly show problems in data. They increase trust and save time.

Similar Posts