How to build a data warehouse on BigQuery with dbt, step by step.

1. Prepare an isolated BigQuery workspace

This walkthrough uses Python 3.11 and dbt Core 1.9 with the BigQuery adapter. It is a learning project, not a production ingestion service. You need a Google Cloud project with billing enabled, the BigQuery API enabled and the Google Cloud CLI installed. Queries, loads and storage may incur charges; the example does not promise a free deployment.

Create an empty dataset named warehouse_demo in the EU multi-region. Ask your project administrator for BigQuery Job User on the project and BigQuery Data Editor on that dataset. Use the same location in the profile. Replace YOUR_PROJECT_ID below with your own project ID. Authenticate locally using Application Default Credentials; keep credentials outside Git.

mkdir warehouse_demo
cd warehouse_demo
python3.11 -m venv .venv
. .venv/bin/activate
python -m pip install 'dbt-core==1.9.6' 'dbt-bigquery==1.9.2'
gcloud auth application-default login
mkdir -p seeds models/staging models/marts tests
printf '.venv/\ntarget/\nlogs/\nprofiles.yml\n' > .gitignore

name: warehouse_demo
version: '1.0.0'
config-version: 2
profile: warehouse_demo
model-paths: [models]
seed-paths: [seeds]
test-paths: [tests]
models:
  warehouse_demo:
    staging:
      +materialized: view
    marts:
      +materialized: table

warehouse_demo:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: YOUR_PROJECT_ID
      dataset: warehouse_demo
      location: EU
      threads: 1
      job_execution_timeout_seconds: 300

2. Ingest a tiny, repeatable fixture

Save the CSV below as seeds/raw_orders.csv. Here dbt seed performs the load into BigQuery. Seeds are appropriate for this tiny demonstration; for live application data, use a managed connector or ingestion job and declare the loaded tables as dbt sources instead.

All amounts use one illustrative currency. There are two completed orders worth 125.50 on 2026-10-01, plus a cancelled order on 2026-10-02. Cancelled orders remain visible in staging but do not enter the completed-order fact.

order_id,customer_id,order_date,amount,status
101,1,2026-10-01,85.00,completed
102,2,2026-10-01,40.50,completed
103,1,2026-10-02,20.00,cancelled

3. Separate staging, facts and reporting marts

Create these three SQL files at the paths shown. Staging sets names and types without hiding cancelled records. The fact has one row per completed order. The daily mart is the reporting interface; ref() tells dbt the dependency order. Everything lands in the isolated dataset for this example.

select
    order_id,
    customer_id,
    cast(order_date as date) as order_date,
    cast(amount as numeric) as amount,
    status
from {{ ref('raw_orders') }}

select order_id, customer_id, order_date, amount
from {{ ref('stg_orders') }}
where status = 'completed'

select
    order_date,
    count(*) as completed_orders,
    sum(amount) as completed_order_value
from {{ ref('fct_orders') }}
group by order_date

4. Protect the grain and reconcile the result

Save the YAML and singular SQL test below. This uses the top-level generic-test arguments supported by the pinned dbt 1.9 version. The key tests protect row grain, while the singular test checks the exact daily result of this fixture. Remove or rewrite the fixture-specific assertion before using real data.

Run debug first to diagnose credentials, project permissions and dataset location. Then seed and build. Expect daily_orders to contain exactly one row: 2026-10-01, 2, 125.50. A passing fixture does not establish production completeness or freshness.

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: customer_id
        tests: [not_null]
      - name: order_date
        tests: [not_null]
      - name: amount
        tests: [not_null]
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: [completed, cancelled]
  - name: fct_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
  - name: daily_orders
    columns:
      - name: order_date
        tests: [not_null, unique]

select 1 as mismatch
where (select count(*) from {{ ref('daily_orders') }}) != 1
   or (select count(*) from {{ ref('daily_orders') }}
       where order_date = cast('2026-10-01' as date)
         and completed_orders = 2
         and completed_order_value = 125.50) != 1

dbt debug --profiles-dir .
dbt seed --full-refresh --profiles-dir .
dbt build --profiles-dir .
dbt show --select daily_orders --profiles-dir .

5. Trigger a failure, then restore the pass

Change order 102 to order_id 101 in the CSV, then run dbt seed --full-refresh --profiles-dir . followed by dbt build --profiles-dir .. Expect the staging unique test to fail and dependent models to be skipped. Restore 102 and repeat both commands. Running tests alone does not reload the CSV.

Inspect target/run_results.json and target/compiled/ for the failing node and SQL. If a table cannot be found, check the project, dataset and location before changing SQL. If access is denied, check the authenticated identity and dataset permissions.

6. Turn the reference build into an owned pipeline

For production, separate raw ingestion from transformation, use dedicated development and production datasets, and run scheduled jobs with a service identity rather than a personal login. Define freshness expectations, retry behaviour, an alert owner and a recovery procedure before depending on the mart.

Start with full table builds while the data is small. Add partitioning or incremental processing only after measuring scan volume and defining how late updates and backfills are handled. Budget alerts help detect spend; they do not cap it. When finished experimenting, remove only the warehouse_demo dataset you created, including its tables, through the BigQuery console.

• Choose a warehouse: BigQuery vs Snowflake vs Redshift costs
• Find where warehouse spend actually goes
• Audit an existing stack before rebuilding
• Build a production data platform
• BigQuery dataset and job locations