# Dataform for BigQuery: A basic end-to-end guide

> What Dataform is and how to build an end-to-end transformation pipeline in BigQuery, step by step, with a sample structure you can adapt.

Source: https://www.measurelab.co.uk/insights/blog/dataform-for-bigquery-basic-end-to-end-guide/  
Author: Prasanna Venkatesan  
Published: 2025-03-18  
Updated: 2026-10-13

Dataform is a powerful tool for managing your data workflows in a structured, version-controlled, and automated way. Whether you're a beginner or an experienced data engineer, Dataform simplifies SQL-based transformations while integrating seamlessly with Google BigQuery.

Although this blog offers a basic introduction to Dataform's functionality, users can achieve significantly more with Dataform. From advanced scheduling, parameterised queries, and dependency management to complex data modelling, the possibilities are vast.

We will be releasing more blogs diving deeper into various aspects of Dataform, covering best practices, optimisation techniques, and real-world use cases. This blog will also serve as a **live blog**, where we will continue adding new features, updates, and linking to detailed Dataform-related articles as they become available.

Make sure to **bookmark this blog** and stay tuned for upcoming posts that will help you leverage Dataform to its full potential!

Now, we'll take you through an end-to-end setup of Dataform, starting from creating a workspace to building target tables, while exploring all its key features along the way.

### **Setting up your Dataform workspace**

Before we dive into defining sources and building tables, let’s set up our Dataform workspace.

#### **1: Create a Google Cloud project**

1. Go to [Google Cloud Console](https://console.cloud.google.com/).
2. Create a new project or select an existing one.
3. Enable the BigQuery API.

#### **2: Enable Dataform in Google Cloud**

1. Navigate to [Google Cloud Dataform](https://console.cloud.google.com/dataform/).
2. Click **Create Repository** and give it a name.
3. Choose a location (typically the same as your BigQuery dataset).
4. Initialise the repository with a new workspace.

#### **3: Configure Dataform**

After setting up your Dataform workspace, it's important to configure the `workflow_settings.yaml` file. This file defines the default project, location, datasets, and Dataform core version for your workspace.

##### **Example workflow_settings.yaml configuration:**

```
defaultProject: dataform-64568

defaultLocation: EU

defaultDataset: dataform

defaultAssertionDataset: dataform_assertions

dataformCoreVersion: 3.0.0
```

This configuration lets you execute all transformations and assertions within the specified project and datasets, streamlining your workflow management.

#### Book your free Dataform consultation

### **Defining your data sources**

Now that your workspace is set up, let’s define the source dataset and tables. We’ll use a free BigQuery dataset for demonstration.

#### **1: Choose a public dataset**

Google provides a variety of public datasets. Let’s use `bigquery-public-data.london_bicycles.cycle_hire.`

#### **2: Define the source in Dataform**

- Delete all other folders and files except the `definitions/` folder and `workflow_settings.yaml` file.
- Create a new directory called source/ under `definitions/`.
- Define the source tables under the `definitions/source/` directory.

##### **1. Bike Hire Source Data - cycle_hire.sqlx**

```
config {

  type: "declaration",

  database: "bigquery-public-data",

  schema: "london_bicycles",

  name: "cycle_hire",

  description: "Cycle Hire Data"

}
```

#### **2. Bike stations source data - cycle_stations.sqlx**

```
config {

  type: "declaration",

  database: "bigquery-public-data",

  schema: "london_bicycles",

  name: "cycle_stations",

  description: "Cycle Stations Data"

}
```

### **Creating transformations**

Let’s create transformations that will process and refine our London bicycle hire dataset. We will build intermediate tables, apply multiple transformations, and finalise the results into target tables.

#### **Step 1: Create intermediate tables**

Intermediate tables help us clean and enrich the raw data before final aggregation. These should be placed under the `definitions/intermediate/` directory.

##### **1. Normalise Bike Ride Durations**

```
config {

  type: "table",

  schema: "intermediate",

  name: "normalized_cycle_rides"

}

-- In the above config, if schema is not mentioned the table will be created under default dataset mentioned in the workflow_settings.yml.

-- If the name is not mentioned then the table will be created as same as the file name. It is good practice to keep the tables names same as your file names.

SELECT

  rental_id,

  bike_id,

  start_station_id,

  end_station_id,

  TIMESTAMP_DIFF(end_date, start_date, SECOND) AS ride_duration_seconds,

  start_date,

  end_date

FROM ${ref("cycle_hire")}
```

#### **Step 2. Aggregate Daily Bike Usage**

```
config {

  type: "table",

  schema: "intermediate",

  name: "daily_cycle_usage"

}

SELECT

  DATE(start_time) AS ride_date,

  bike_id,

  COUNT(*) AS total_rides

FROM ${ref("normalized_cycle_rides")}

GROUP BY 1, 2
```

#### **Step 3: Define final output tables**

Final tables consolidate the transformed data for reporting and analysis. These should be placed under the `definitions/output/` directory.

##### **1. Summary Report**

```
config {

  type: "table",

  schema: "output",

  name: "summary_report"

}

SELECT

  d.ride_date,

  d.bike_id,

  d.total_rides,

  s.station_name AS start_station,

  e.station_name AS end_station

FROM ${ref("daily_cycle_usage")} d

LEFT JOIN ${ref("cycle_stations")} s ON d.start_station_id = s.station_id

LEFT JOIN ${ref("cycle_stations")} e ON d.end_station_id = e.station_id
```

##### **2. Peak Ride Hours**

```
config {

  type: "view",

  schema: "output",

  name: "peak_ride_hours"

}

SELECT

  EXTRACT(HOUR FROM start_date) AS hour_of_day,

  COUNT(*) AS ride_count

FROM ${ref("normalized_cycle_rides")}

GROUP BY 1

ORDER BY ride_count DESC
```

Instead of storing the data physically, we can create views for flexible and cost-efficient querying.

---

**Incremental tables** are another type of table that can be defined in Dataform. To learn more about these tables and their uses, please refer to this blog.

---

The "Not found: Dataset <project_id>: intermediate was not found in location" error message appears in the Dataform UI only when the underlying tables have not yet been created. The error will disappear once the pipeline has been executed and the tables have been created.

### **4. Partitioning and clustering in Dataform**

Both of these help optimise query performance and reduce costs in BigQuery by improving how data is stored and accessed.

##### **Partitioning**

Partitioning divides a table into segments based on a specific column (e.g., `ride_date`). Queries that filter by this column will scan only the relevant partitions, improving efficiency.

##### **Clustering**

Clustering organises data within partitions based on certain columns (e.g., `bike_id` and `start_station`). This speeds up queries that filter or group by these columns.

By implementing partitioning and clustering in our`summary_report` table, we can ensure efficient storage and faster query execution. This is particularly beneficial for queries that filter on a specific date, as it reduces the amount of data scanned in BigQuery, leading to cost savings. Let's implement partitioning and clustering in the `summary_report` table as shown below.

```
config {

  type: "table",

  schema: "output",

  name: "summary_report",

  bigquery: {

    partitionBy: "ride_date",

    clusterBy: ["bike_id", "start_station"]

  }

}

SELECT

  d.ride_date,

  d.bike_id,

  d.total_rides,

  s.station_id AS start_station,

  e.station_id AS end_station

FROM ${ref("daily_cycle_usage")} d

LEFT JOIN ${ref("station_usage")} s ON d.bike_id = s.station_id

LEFT JOIN ${ref("station_usage")} e ON d.bike_id = e.station_id
```

### **5. Compile and run transformations**

Now that we have defined our transformations and output tables, we need to execute them in Dataform to create the required tables and views in BigQuery.

##### **Executing Multiple Actions**

- Click **Start Execution** to validate your SQL code and check for errors.
- Navigate to the **Actions** section in Dataform.
- Click on **Multiple Actions…** to selectively run the required transformations.
- Choose the following actions to be executed: `bike_usage_summary`
- `popular_routes`

![](https://lh7-rt.googleusercontent.com/docsz/AD_4nXcSCbkfB2TNDwat-G6oe-YgCZfvgQtltTyG7ztDhLEgEbcVyv3u9IiT19NeHqDO0kTKtrAX3Cmadmo-49ebvi2ImZntk9P7RCojJPAqYgXvW_ZGaXgOg3JjcYwMCa2PRiL2COZKHQ?key=Oq4PB1enuG-h6zKdZ1uriHdD)

![](https://lh7-rt.googleusercontent.com/docsz/AD_4nXciGdNDOW-plPyEE0w0hfkUNVH7lLg9FfUfH4dpIHTtB3Z_SZV4qxZRBkwdPqWuPPrGk1VWTNmu-A3ZCKTAZbssKPrVk0zCUG7LPjelco5cNj4ySEAD8nJXVjrE23Fv7yy5CgJQ?key=Oq4PB1enuG-h6zKdZ1uriHdD)

By selecting multiple actions, we ensure that both tables are created efficiently while adhering to dependencies between intermediate and output tables.

![](https://lh7-rt.googleusercontent.com/docsz/AD_4nXe3HYlJQrIw_f7NMa4m1scK4B3Id67qzmMxGgYvE-PrOPeB83dn9lBhk9H_UxKQax85n2iX1AXbCCwNQOdfWHE-EcqazMWfa4cr3kfejJEPX8gF7yPqXuj2ILuOphDV3Vgb4DP2UA?key=Oq4PB1enuG-h6zKdZ1uriHdD)

### **6. Customising Dataform structure**

This blog uses a sample structure to simplify how Dataform works. You can adapt Dataform's structure to your project's specific needs after grasping the fundamentals. Depending on the complexity of data workflows, teams may choose to organise their Dataform repository differently, optimise queries for efficiency, or integrate additional features like parameterised datasets and advanced scheduling mechanisms. You can tailor Dataform's flexibility to meet various data engineering requirements.

If you’d rather have a Dataform pipeline built and handed over to your team, that’s work we do for marketing data. See [how we build BigQuery and Dataform platforms](https://www.measurelab.co.uk/solutions/intelligence-platforms/).