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.

✍️ 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

- Model: one
.sqlfile with oneSELECT. dbt turns it into a view or a table in BigQuery with the file's name. You never writeCREATE 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) ortable(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
- A Google Cloud account (the free trial works) and a project.
- Open Cloud Shell (the
>_icon at the top right of the console). It has Python,gcloudandbq. Set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable bigquery.googleapis.com- Make a folder
~/lab193with the dbt project in a subfoldershop(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,,deliveredLook 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, 3And 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=2Step 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_lab21: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 | | | |
+------------------+-------+--------+-------------------+------------------+
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=6Two 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:
- The duplicate is a data problem: keep one row per order.
shippedis 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
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
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 (
shippedis valid) turnedERROR=2intoPASS=11. - The mart counts order 1006 once and has one spelling per city.
Quiz task: check what you learned
- Add a test that
amountinstg_ordersis never NULL. Would it pass? *(Answer: addtests: [not_null]under a columnamount; it passes becausecoalesce(..., 0)replaced the NULL.)* - Why use
ref('stg_orders')and notdbt_lab.stg_orders? (Answer: ref builds in the right order, follows the target dataset, and draws the lineage.) - Make
customer_revenuea view instead of a table. *(Answer: set+materialized: viewfor marts indbt_project.yml, or add{{ config(materialized='view') }}at the top of the model.)* - A test fails. Is the data always wrong? *(Answer: no; sometimes the rule is out of date, like
shippedhere. Decide with the data owner.)* - 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
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 see | Why | Fix |
|---|---|---|
Connection test: [ERROR] in dbt debug | Wrong project, no BigQuery permission, or not signed in | Check profiles.yml, gcloud auth list, your BigQuery roles |
Could not find profile named 'shop' | The profile name differs from profile: in dbt_project.yml | Use the same name in both files |
Compilation Error ... depends on a node named 'x' which was not found | A ref() names a model that does not exist | Check the file name and the spelling in ref() |
FAIL 1 unique_... | Duplicate keys in the model | Find them with GROUP BY ... HAVING COUNT(*) > 1, dedupe in staging |
FAIL ... accepted_values_... | A value outside the list | Fix the data, or update the list if the value is valid |
Dataset ... was not found in location US | The dataset is in another location | Set location in the profile to the dataset's location |
What next
- SQL skills behind every model: BigQuery for Beginners: Query Billions of Rows for Free.
- Clean data for a model: BigQuery ML: Train a Model with Just SQL.
- Run your project from a pipeline: Cloud Build Hands-On: Pipelines Without Managing Servers.
- More hands-on posts: ByteBlog. Practice in ByteLabs and see the courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet 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.
Related blogs
CareerByteCode TeamBigQuery ML: Train a Model with Just SQL
What you build: a churn prediction model written in SQL with BigQuery ML, scored and used for predictions, in your own Google Cloud account. Every step tested.
#bigquery
CareerByteCode TeamData Engineering Project for Your Resume on Google Cloud
What you build: a daily batch pipeline on Cloud Storage and BigQuery with layers, data quality checks, quarantine and idempotent re-runs. Every step tested.
#bigquery
CareerByteCode TeamRedis Caching: Make Your API 10x Faster
What you build: an API on a slow 1-million-row database with and without a Redis cache on a free VM, load-tested (about 20x more requests per second), plus stale data and a full cache broken and fixed
#bigquery
CareerByteCode TeamSpanner Free Trial: Global SQL Database Hands-On
What you build: a free trial Cloud Spanner instance with interleaved tables, real duplicate and orphan errors, a unique index, a Python app saving an order and points in one transaction, and a stale r
💬 Comments (0)
Top · NewestSign in to comment