Raw data from your ingestion tool is not analysis-ready. Column names are whatever the source API called them. Booleans come in as integers. There are three different tables that all mean “customer”. The transformation layer is where you turn that mess into something your business can use.

dbt (data build tool) is the standard here. You write SQL, dbt turns it into models, and those models are versioned, tested, documented, and repeatable.

What dbt actually does

dbt runs SQL SELECT statements against your warehouse and materialises the results as tables or views. That is the core of it.

What it adds on top:

  • Dependencies — models can reference other models with {{ ref('model_name') }}, and dbt builds the DAG for you
  • Tests — assert that a column is not null, unique, or only contains expected values
  • Documentation — auto-generated data catalogue from model descriptions you write in YAML
  • Incremental models — only process new rows rather than rebuilding entire tables on every run

dbt Core vs dbt Cloud

dbt Core is the open-source CLI. Free. You run it from your terminal, a CI job, or a cron command. No UI, no scheduler built in.

dbt Cloud is the managed product — a web IDE, a built-in scheduler, a docs UI, Slim CI for faster runs. Starts at $100/developer/month on the Team plan.

For most startups: start with dbt Core. The free tier is fully capable for a single analyst or small team. Add dbt Cloud when you need the scheduler or when multiple people are authoring models and want a shared environment.

Model structure that actually scales

The layer structure that works for most teams:

models/
  staging/        ← one model per source table; rename, cast, minimal cleaning
    salesforce/
      stg_salesforce__accounts.sql
      stg_salesforce__opportunities.sql
    stripe/
      stg_stripe__charges.sql
  marts/          ← business logic; joins, aggregations, metrics
    sales/
      fct_opportunities.sql
      dim_accounts.sql
    finance/
      fct_revenue.sql

Staging models are thin. They rename columns to your convention, cast types, and add nothing else. One staging model per source table.

Mart models contain business logic. They join staging models together, apply business rules, and produce the facts and dimensions your BI tool will query.

Keep business logic in marts, not staging. Staging models are disposable — if Fivetran changes the schema, you fix the staging model and nothing else changes.

Essential tests to add from day one

 1# models/staging/salesforce/stg_salesforce__accounts.yml
 2version: 2
 3models:
 4  - name: stg_salesforce__accounts
 5    columns:
 6      - name: account_id
 7        tests:
 8          - unique
 9          - not_null
10      - name: created_at
11        tests:
12          - not_null

A not_null + unique test on every primary key. That is the minimum. dbt will fail the run if the test breaks, which means you catch data quality issues before they reach your dashboards.

Before you move on

Before adding orchestration (Step 4):

  • Staging models created for each source table your ingestion tool loads
  • At least one mart model that joins staging models for a key business question
  • Primary key tests (unique, not_null) on every model
  • dbt run and dbt test both pass cleanly
  • dbt docs generate && dbt docs serve shows your lineage graph

If dbt run takes more than 10 minutes, check for missing incremental models on large tables. Full refreshes on millions of rows run every hour will become a problem quickly.