CareerByteCode
ByteBlogby CareerByteCode

dbt with BigQuery: Clean Data Like an Analytics Engineer

What you build: a dbt project on BigQuery that cleans messy raw CSVs into a tested mart table; the tests really catch a duplicate order and an unknown status, then pass 11 of 11 after the fix.

dbt with BigQuery: Clean Data Like an Analytics Engineer

✍️ Translate this blog into Hindi, Tamil or Telugu

Raw data is always a little wrong: the same order arrives twice, Delivered, delivered and DELIVERED mean the same thing, a city has a leading space, an amount is missing. dbt (data build tool) is how analytics engineers turn that mess into clean, tested tables, using only SQL files kept in Git. In this lab you build a small dbt project on BigQuery: load raw CSV files, write staging models that clean them, a mart table the business reads, and tests that catch bad data. The tests really fail on our raw data, you look at why, fix it, and build again until everything passes.

Who it is for: data analysts, SQL learners and aspiring data engineers. Time: about 1 hour. Cost: free. BigQuery's free tier covers this lab many times over (the whole lab reads a few KB).

Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output is the real one we got.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: dbt in plain English

dbt in one picture
Raw tables are cleaned by staging models, joined into a mart, and every model is checked by tests
  • Model: one .sql file with one SELECT. dbt turns it into a view or a table in BigQuery with the file's name. You never write CREATE TABLE.
  • **ref('name'): how one model uses another. dbt reads these links, works out the order to build things in, and draws the lineage** (who depends on whom).
  • Seed: a small CSV file in the project that dbt loads as a table. Good for small reference data, and here for our raw data.
  • Staging model: cleans one raw table: names, types, case, spaces, duplicates. Mart model: the final table the business reads.
  • Test: a rule about the data, written in YAML: unique, not_null, accepted_values, relationships. A test is really a query that looks for bad rows; zero rows = PASS.
  • Materialization: how a model is saved. view (always fresh, no storage) or table (fast to read).
  • Profile: how dbt connects to the warehouse, in ~/.dbt/profiles.yml. Here: BigQuery with your gcloud sign-in (no key file).

Real-life example: a restaurant kitchen. Seeds are the deliveries, staging is washing and cutting, the mart is the plated dish, tests are the chef tasting before it leaves the kitchen, and ref() is the recipe order.

Before you start

  1. A Google Cloud account (the free trial works) and a project.
  2. Open Cloud Shell (the >_ icon at the top right of the console). It has Python, gcloud and bq. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com
  1. Make a folder ~/lab193 with the dbt project in a subfolder shop (the files are shown in Step 3).

Part B: the lab

Step 1: install dbt

Why: dbt is a Python tool. dbt-bigquery brings dbt itself plus the BigQuery adapter. A virtual environment (.venv) keeps it apart from other Python tools.

# Install dbt with the BigQuery adapter in a Python virtual environment
python3 -m venv .venv && source .venv/bin/activate
pip install -q "dbt-bigquery==1.*" 2>&1 | grep -v "^\[notice\]" | tail -2
dbt --version 2>&1 | grep -E "installed|bigquery"
  - installed: 1.13.0-b1
  - bigquery: 1.12.4 - Up to date!

Step 2: connect dbt to BigQuery

Why: the profile tells dbt the project, the dataset to build in (dbt_lab, dbt creates it) and the location (US). method: oauth uses your Cloud Shell sign-in, so there is no service account key file to manage. dbt debug tests the connection.

# Tell dbt how to reach BigQuery: method oauth = your gcloud sign-in (no key file), dataset dbt_lab in the US
mkdir -p ~/.dbt && cat > ~/.dbt/profiles.yml <<YML
shop:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: $PROJECT_ID
      dataset: dbt_lab
      location: US
      threads: 4
YML
sed "s/$PROJECT_ID/your-project-id/" ~/.dbt/profiles.yml
source .venv/bin/activate && cd shop && dbt debug 2>&1 | grep -E "Connection test|All checks|ERROR"
shop:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: your-project-id
      dataset: dbt_lab
      location: US
      threads: 4
21:35:21    Connection test: [OK connection ok]
21:35:21  All checks passed!

Step 3: the project files

Why: a dbt project is just folders of text files. dbt_project.yml names the project and says staging models are views and marts are tables.

# The project: raw CSV files (seeds), staging models that clean them, a mart the business reads, and tests
cd shop && find . -type f -not -path "./target/*" -not -path "./logs/*" | sort
cat seeds/raw_orders.csv
./dbt_project.yml
./models/marts/customer_revenue.sql
./models/staging/schema.yml
./models/staging/stg_customers.sql
./models/staging/stg_orders.sql
./seeds/raw_customers.csv
./seeds/raw_orders.csv
order_id,customer_id,order_date,amount,status
1001,1,2026-10-01,1200,Delivered
1002,1,2026-10-03,450,delivered
1003,2,2026-10-03,3000,DELIVERED
1004,3,2026-10-04,700,returned
1005,2,2026-10-05,250,Cancelled
1006,4,2026-10-06,1800,delivered
1006,4,2026-10-06,1800,delivered
1007,1,2026-10-08,999,delivered
1008,5,2026-10-09,600,shipped
1009,3,2026-10-10,,delivered

Look at the raw orders closely: order 1006 appears twice, the status is written four different ways, order 1007 has delivered with a trailing space, order 1009 has no amount, and order 1008 is shipped. The customer cities have the same kind of problems (chennai, Chennai, KOCHI). The models:

-- stg_orders: one clean row per order. Fixes the raw data's case and spaces, and turns a missing amount into 0.
select
  order_id,
  customer_id,
  cast(order_date as date)               as order_date,
  coalesce(cast(amount as numeric), 0)   as amount,
  lower(trim(status))                    as status
from {{ ref('raw_orders') }}
-- stg_customers: city names in one style (trimmed, first letter capital)
select
  customer_id,
  name,
  initcap(trim(city))           as city,
  cast(signup_date as date)     as signup_date
from {{ ref('raw_customers') }}
-- customer_revenue: the table the business reads. Delivered revenue and order count per customer and city.
select
  c.customer_id,
  c.name,
  c.city,
  count(o.order_id)                                         as orders,
  sum(case when o.status = 'delivered' then o.amount end)  as delivered_revenue
from {{ ref('stg_customers') }} c
left join {{ ref('stg_orders') }} o using (customer_id)
group by 1, 2, 3

And the tests, in models/staging/schema.yml:

version: 2
models:
  - name: stg_orders
    description: "One row per order, cleaned"
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ["delivered", "returned", "cancelled"]
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

Step 4: load the raw CSV files (seeds)

# Load the raw CSV files into BigQuery as tables
source .venv/bin/activate && cd shop && dbt seed 2>&1 | grep -E "OK loaded|Completed|Done\."
21:35:58  2 of 2 OK loaded seed file dbt_lab.raw_orders .......... [INSERT 10 in 4.55s]
21:35:58  1 of 2 OK loaded seed file dbt_lab.raw_customers .......... [INSERT 5 in 5.30s]
21:35:58  Completed successfully
21:35:58  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=2

Step 5: build the models

Why: dbt run reads every ref(), builds stg_customers and stg_orders first (views), then customer_revenue (a table) that depends on both. bq ls shows what landed in BigQuery.

# Build the models: dbt works out the order from ref() and creates the views and the table in BigQuery
source .venv/bin/activate && cd shop && dbt run 2>&1 | grep -E "OK created|Completed|Done\."
bq ls --format=pretty dbt_lab
21:36:16  1 of 3 OK created sql view model dbt_lab.stg_customers .......... [CREATE VIEW (0 processed) in 1.28s]
21:36:16  2 of 3 OK created sql view model dbt_lab.stg_orders .......... [CREATE VIEW (0 processed) in 1.46s]
21:36:19  3 of 3 OK created sql table model dbt_lab.customer_revenue .......... [CREATE TABLE (5.0 rows, 480.0 Bytes processed) in 3.13s]
21:36:19  Completed successfully
21:36:19  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=3
+------------------+-------+--------+-------------------+------------------+
|     tableId      | Type  | Labels | Time Partitioning | Clustered Fields |
+------------------+-------+--------+-------------------+------------------+
| customer_revenue | TABLE |        |                   |                  |
| raw_customers    | TABLE |        |                   |                  |
| raw_orders       | TABLE |        |                   |                  |
| stg_customers    | VIEW  |        |                   |                  |
| stg_orders       | VIEW  |        |                   |                  |
+------------------+-------+--------+-------------------+------------------+
dbt seed and run
dbt debug connects, seeds load 10 and 5 rows, three models are built: two views and one table

Break it: run the tests

Everything built without an error, so is the data right? dbt test runs every test in schema.yml.

# Break it: run the tests. The raw data has a duplicate order and a status the business does not know
source .venv/bin/activate && cd shop && dbt test 2>&1 | grep -E "PASS|FAIL|ERROR|Done\." | sed -E 's/ in [0-9.]+s\]/]/'
21:36:39  1 of 6 FAIL 1 accepted_values_stg_orders_status__delivered__returned__cancelled  [FAIL 1]
21:36:39  2 of 6 PASS not_null_stg_customers_customer_id .......... [PASS]
21:36:39  3 of 6 PASS not_null_stg_orders_order_id .......... [PASS]
21:36:39  4 of 6 PASS relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [PASS]
21:36:40  5 of 6 PASS unique_stg_customers_customer_id .......... [PASS]
21:36:41  6 of 6 FAIL 1 unique_stg_orders_order_id .......... [FAIL 1]
21:36:41  [ERROR]: in test accepted_values_stg_orders_status__delivered__returned__cancelled (models/staging/schema.yml)
21:36:41  [ERROR]: in test unique_stg_orders_order_id (models/staging/schema.yml)
21:36:41  Done. PASS=4 WARN=0 ERROR=2 SKIP=0 NO-OP=0 REUSED=0 TOTAL=6

Two of six tests fail, each with FAIL 1 (one bad row or value):

  • unique_stg_orders_order_id: an order id appears more than once.
  • accepted_values_stg_orders_status: a status that is not in the list we agreed with the business.

This is the point of tests: the models built fine, and without tests these problems would have gone straight into the business's numbers (order 1006 would have been counted twice).

Look at what the tests found

# Look at what the failing tests found
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT order_id, COUNT(*) AS copies FROM dbt_lab.stg_orders GROUP BY order_id HAVING COUNT(*) > 1"
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT status, COUNT(*) AS n FROM dbt_lab.stg_orders GROUP BY status ORDER BY n DESC"
+----------+--------+
| order_id | copies |
+----------+--------+
|     1006 |      2 |
+----------+--------+
+-----------+---+
|  status   | n |
+-----------+---+
| delivered | 7 |
| cancelled | 1 |
| returned  | 1 |
| shipped   | 1 |
+-----------+---+

Order 1006 has 2 copies. Our cleaning already fixed the case and spaces (Delivered, DELIVERED and delivered all became delivered), so the only unknown status is shipped. Now two decisions:

  1. The duplicate is a data problem: keep one row per order.
  2. shipped is a real status the business confirms it uses: the test was wrong, so we add it to the list. (Not every failing test means the data is wrong; sometimes the rule is.)

Fix it: one row per order, and an updated test

Why: QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ...) = 1 keeps the first row of each order. dbt build then runs seeds, models and tests together in the right order, and stops a model's children if its tests fail.

# Fix it: keep one row per order in the staging model, add 'shipped' (a real status the business confirmed) to the test, then build and test again
cd shop && cat > models/staging/stg_orders.sql <<'SQL'
-- stg_orders: one clean row per order. Fixes case and spaces, turns a missing amount into 0,
-- and keeps ONE row per order_id (the raw feed sometimes sends an order twice).
select
  order_id,
  customer_id,
  cast(order_date as date)               as order_date,
  coalesce(cast(amount as numeric), 0)   as amount,
  lower(trim(status))                    as status
from {{ ref('raw_orders') }}
qualify row_number() over (partition by order_id order by order_date) = 1
SQL
sed -i 's/values: \["delivered", "returned", "cancelled"\]/values: ["delivered", "returned", "cancelled", "shipped"]/' models/staging/schema.yml && grep -A1 accepted_values models/staging/schema.yml | tail -1
source ../.venv/bin/activate && dbt build 2>&1 | grep -E "PASS|FAIL|ERROR|OK created|Done\." | sed -E 's/ in [0-9.]+s\]/]/'
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →
              values: ["delivered", "returned", "cancelled", "shipped"]
21:37:18  3 of 11 OK created sql view model dbt_lab.stg_customers .......... [CREATE VIEW (0 processed)]
21:37:19  4 of 11 OK created sql view model dbt_lab.stg_orders .......... [CREATE VIEW (0 processed)]
21:37:20  6 of 11 PASS unique_stg_customers_customer_id .......... [PASS]
21:37:20  5 of 11 PASS not_null_stg_customers_customer_id .......... [PASS]
21:37:20  7 of 11 PASS accepted_values_stg_orders_status__delivered__returned__cancelled__shipped  [PASS]
21:37:20  8 of 11 PASS not_null_stg_orders_order_id .......... [PASS]
21:37:21  10 of 11 PASS unique_stg_orders_order_id .......... [PASS]
21:37:21  9 of 11 PASS relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [PASS]
21:37:24  11 of 11 OK created sql table model dbt_lab.customer_revenue .......... [CREATE TABLE (5.0 rows, 560.0 Bytes processed)]
21:37:24  Done. PASS=11 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=11
Tests fail, then pass
2 of 6 tests fail (a duplicate order and an unknown status); after the fix dbt build passes 11 of 11

PASS=11 ERROR=0: 2 seeds, 3 models and 6 tests, all green.

Step 6: the clean table the business reads

# The clean table the business reads
bq query --quiet --use_legacy_sql=false --format=pretty "SELECT * FROM dbt_lab.customer_revenue ORDER BY delivered_revenue DESC"
+-------------+--------------+---------+--------+-------------------+
| customer_id |     name     |  city   | orders | delivered_revenue |
+-------------+--------------+---------+--------+-------------------+
|           2 | Bright Books | Pune    |      2 |              3000 |
|           1 | Asha Traders | Chennai |      3 |              2649 |
|           4 | Delta Foods  | Kochi   |      1 |              1800 |
|           3 | City Mart    | Chennai |      2 |                 0 |
|           5 | Echo Labs    | Pune    |      1 |              NULL |
+-------------+--------------+---------+--------+-------------------+

Every city is now written one way (Chennai, Pune, Kochi), order 1006 is counted once (Delta Foods 1,800, not 3,600), and the numbers are easy to explain: City Mart's only delivered order (1009) had no amount, so it counts as 0; Echo Labs' only order is still shipped, so it has no delivered revenue yet (NULL).

Step 7: lineage

Why: dbt knows the whole dependency tree from ref(). This is how it builds things in the right order, and how you answer "if this raw table breaks, which reports are affected?".

# Lineage: dbt knows which model depends on which (from ref()), and builds them in that order
source .venv/bin/activate && cd shop && dbt ls --resource-type model --output json --output-keys "name depends_on" 2>/dev/null | python3 -c "
import json, sys
for l in sys.stdin:
    if l.startswith('{'):
        d = json.loads(l); print(d['name'], '<-', ', '.join(x.split('.')[-1] for x in d['depends_on']['nodes']))"
customer_revenue <- stg_customers, stg_orders
stg_customers <- raw_customers
stg_orders <- raw_orders
Result and lineage
The mart table with clean cities and one row per order, and the lineage from raw seeds to the mart

dbt docs generate and dbt docs serve turn the same information into a browsable website with a lineage graph.

POC: prove it works

  • dbt connected to BigQuery with the Cloud Shell sign-in (no key file) and built 2 views and 1 table from 2 raw tables.
  • Tests caught a duplicate order and an unexpected status that the build itself did not notice.
  • One fix in SQL (one row per order) and one in the test (shipped is valid) turned ERROR=2 into PASS=11.
  • The mart counts order 1006 once and has one spelling per city.

Quiz task: check what you learned

  1. Add a test that amount in stg_orders is never NULL. Would it pass? *(Answer: add tests: [not_null] under a column amount; it passes because coalesce(..., 0) replaced the NULL.)*
  2. Why use ref('stg_orders') and not dbt_lab.stg_orders? (Answer: ref builds in the right order, follows the target dataset, and draws the lineage.)
  3. Make customer_revenue a view instead of a table. *(Answer: set +materialized: view for marts in dbt_project.yml, or add {{ config(materialized='view') }} at the top of the model.)*
  4. A test fails. Is the data always wrong? *(Answer: no; sometimes the rule is out of date, like shipped here. Decide with the data owner.)*
  5. Which single command builds and tests everything in order? *(Answer: dbt build.)*

Clean up (delete everything)

Why: delete the dataset dbt created (with every table and view in it) and the local files, including the profile.

# Clean up: delete the dataset dbt made, and the local files
bq rm -r -f -d dbt_lab && echo "dataset dbt_lab deleted"
rm -rf .venv shop/target shop/logs ~/.dbt
echo "datasets left: $(bq ls --format=json 2>/dev/null | python3 -c 'import json,sys; print(len(json.load(sys.stdin) or []))' 2>/dev/null || echo 0)"
dataset dbt_lab deleted
datasets left: 0
Clean up
The dbt_lab dataset is deleted: 0 datasets left

FAQ

dbt Core or dbt Cloud? This lab used dbt Core: free, open source, run from the command line. dbt Cloud is a paid service with a web IDE and scheduler. The project files are the same.

How do I run dbt every night? In Google Cloud: a Cloud Run job or a Cloud Build trigger that runs dbt build on a Cloud Scheduler timer, signed in as a service account with BigQuery Data Editor and Job User roles.

Views or tables? Staging as views (always fresh, no storage), marts as tables (fast for dashboards). For very large tables use incremental models, which only add new rows.

Where do the test failures go? dbt prints them. Add --store-failures to save the bad rows in a table you can query.

Is this the same as Dataform? Dataform is Google Cloud's own SQL workflow tool built into BigQuery, with similar ideas (SQLX files, ref, assertions). dbt works with many warehouses and has a bigger community.

What about secrets for production? Use a service account with only the BigQuery roles it needs, and sign in with Workload Identity or the runtime's own account; avoid key files.

Troubleshooting

You seeWhyFix
Connection test: [ERROR] in dbt debugWrong project, no BigQuery permission, or not signed inCheck profiles.yml, gcloud auth list, your BigQuery roles
Could not find profile named 'shop'The profile name differs from profile: in dbt_project.ymlUse the same name in both files
Compilation Error ... depends on a node named 'x' which was not foundA ref() names a model that does not existCheck the file name and the spelling in ref()
FAIL 1 unique_...Duplicate keys in the modelFind them with GROUP BY ... HAVING COUNT(*) > 1, dedupe in staging
FAIL ... accepted_values_...A value outside the listFix the data, or update the list if the value is valid
Dataset ... was not found in location USThe dataset is in another locationSet location in the profile to the dataset's location

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →BigQuery ML: Train a Model with Just SQL⏱ 12 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →