Skip to content
by SQLStack
Docs

Getting Started

Quickstart

Get a SQLBuild project running locally with DuckDB in under a minute.

This guide walks you through creating and running a complete transformation project - views, tables, incremental models, audits, tests, and a custom materialization - all running locally against DuckDB. No external data setup or warehouse credentials required.

Prerequisites

Section titled “Prerequisites”
  • Python 3.12–3.14
  • SQLBuild installed: uv pip install sqlbuild or pip install sqlbuild

Confirm the installed release with sqb --version.

1. Create the playground

Section titled “1. Create the playground”
sqb playground waffle-shop
cd waffle-shop

This creates a self-contained waffle shop project with everything you need to explore SQLBuild.

The playground creates the shared sqlbuild_project.toml; that is sufficient for this quickstart. In a team project, you can additionally create a gitignored sqlbuild_local.toml to select your usual target and override your personal development schema or credentials without changing the committed project config. See Project Configuration.

2. Plan

Section titled “2. Plan”

Preview what SQLBuild will do:

sqb plan
Plan ready  15 selected, 2 sources to load


Sources to load (2)
├── raw__customers                     table
└── raw__orders                        delete_insert (cursor: ordered_at)


Changed functions (2)
├── table_fn__customer_orders          table function
└── udf__is_completed_order            sql udf


First run (12)
├── stg_customers                      view
├── stg_payments                       view
├── stg_orders                         view
├── daily_order_partitioned            partition_tracked (custom)
├── daily_revenue                      table
├── dim_customers                      table
├── fact_orders                        table
├── customer_status_snapshot           merge (timestamp)
├── hourly_order_activity              delete_insert (timestamp, microbatch)
├── daily_activity_rollup              delete_insert (timestamp, microbatch)
├── hourly_activity_with_daily_context delete_insert (timestamp, microbatch)
└── order_status_index                 delete_insert (integer)


Seeds (1)
└── waffle_types  (first_run)


✓ Plan complete  15 selected, 2 sources to load

raw__customers and raw__orders are loaded by a Python loader (python/loaders/waffle_sources.py) at the start of the build; raw__payments is an inline expression in sources/raw.yml. Incremental models also list their cursor and window under their row; they are left out here.

3. Build

Section titled “3. Build”

Execute the full build:

sqb --no-color build

SQLBuild materializes all models in DAG order. SQL unit tests run before their target models. Error-severity audits run against a staging table before promotion - if any fail, the production table is untouched:

Execution  sqb build  (concurrency: 1)


   1/17  source    raw__customers                                        OK     0.05s  rows=5
   2/17  source    raw__orders                                           OK     0.03s  rows=10


   3/17  seed      waffle_types                                          OK     0.03s
   4/17  view      stg_customers                                         OK     0.02s
           audit     not_null (customer_id)                              PASS
           audit     unique (customer_id)                                PASS
           audit     not_null (email)                                    PASS
   7/17  view      stg_orders                                            OK     0.04s
           test      test_daily_revenue_chain                            PASS
             expect  expected stg_orders                                 PASS
             expect  expected stg_payments                               PASS
             expect  expected daily_revenue                              PASS
             expect  assertion daily_revenue_is_non_negative             PASS
           test      test_stg_orders                                     PASS
             expect  expected stg_orders                                 PASS
             expect  assertion order_ids_are_not_null                    PASS
           audit     not_null (order_id)                                 PASS
           audit     unique (order_id)                                   PASS
           audit     not_null (customer_id)                              PASS
           audit     relationships (waffle_type_id)                      PASS
           audit     accepted_values (status)                            PASS
   8/17  custom    daily_order_partitioned                               OK     0.21s
           audit     waffles_ordered_is_positive                         PASS  4/4
           audit     not_null (order_date)                               PASS  4/4
   9/17  table     daily_revenue                                         OK     0.07s
           test      test_daily_revenue_chain                            PASS
             expect  expected stg_orders                                 PASS
             expect  expected stg_payments                               PASS
             expect  expected daily_revenue                              PASS
             expect  assertion daily_revenue_is_non_negative             PASS
           audit     revenue_is_non_negative                             PASS
  11/17  table     fact_orders                                           OK     0.05s
           test      test_fact_orders                                    PASS
             expect  expected fact_orders                                PASS
             expect  assertion line_totals_are_non_negative              PASS
           audit     not_null (order_id)                                 PASS
  12/17  table     customer_status_snapshot  (merge)                     OK     0.05s
           audit     not_null (customer_id)                              PASS
           audit     unique (customer_id)                                PASS
  13/17  table     hourly_order_activity  (delete_insert)                OK     0.24s
         5 batches (1d)    range 2026-03-31T09:00:00 → 2026-04-04T14:00:00    8 rows
           audit (d) orders_placed_is_non_negative                       PASS  5/5
           audit (d) not_null (activity_hour)                            PASS  5/5
           audit (f) orders_placed_is_non_negative                       PASS
           audit (f) not_null (activity_hour)                            PASS
  17/17  table     hourly_activity_with_daily_context  (delete_insert)   OK     0.63s
         13 batches (6h)    range 2026-03-31T18:00:00 → 2026-04-03T23:00:00    0 rows
           audit (d) day_orders_cover_hourly_orders                      PASS  13/13
           audit (d) not_null (activity_hour)                            PASS  13/13
           audit (f) day_orders_cover_hourly_orders                      PASS
           audit (f) not_null (activity_hour)                            PASS


✓ Completed successfully  PASS=99  WARN=0  FAIL=0  INSUFFICIENT=0  SKIP=0  TOTAL=99  (2.13s)

Notice audit (d) and audit (f) on incremental models - these are delta-phase and final-phase audits. Delta audits validate each batch before DML is applied; final audits run against the target after promotion.

4. Verify

Section titled “4. Verify”

Run the plan again to see the steady-state:

sqb plan
Plan ready  15 selected, 2 sources to load


Sources to load (2)
├── raw__customers                     table
└── raw__orders                        delete_insert (cursor: ordered_at)


Models (12)
├── stg_customers                      view
├── stg_payments                       view
├── stg_orders                         view
├── daily_order_partitioned            partition_tracked (custom)
├── daily_revenue                      table
├── dim_customers                      table
├── fact_orders                        table
├── customer_status_snapshot           merge (timestamp)
├── hourly_order_activity              delete_insert (timestamp, microbatch)
├── daily_activity_rollup              delete_insert (timestamp, microbatch)
├── hourly_activity_with_daily_context delete_insert (timestamp, microbatch)
└── order_status_index                 delete_insert (integer)


Functions (2)
├── table_fn__customer_orders          table function
└── udf__is_completed_order            sql udf


Seeds (1)
└── waffle_types  (current)


✓ Plan complete  15 selected, 2 sources to load

The models and functions are no longer listed as first run, and the seed is current. The two loader-managed sources load on every build.

5. Common operations

Section titled “5. Common operations”
# Rebuild a specific model
sqb build daily_revenue


# Rebuild models under a path
sqb build path:models/marts


# Full refresh of everything
sqb build --full-refresh


# Run tests only
sqb test


# Run audits only
sqb audit


# Inspect lineage
sqb lineage fact_orders --direction both


# Rename a model or a column, updating every reference (see Refactoring)
sqb rename fact_orders order_facts
sqb rename stg_customers.email email_address


# Compile and check contracts
sqb compile

What you just built

Section titled “What you just built”

Model DAG

Section titled “Model DAG”
raw__customers ──> stg_customers ──> dim_customers
raw__orders ────> stg_orders ────> fact_orders ──> customer_status_snapshot
raw__payments ──> stg_payments ──>  │              order_status_index
                                   │              hourly_order_activity ──> daily_activity_rollup
                                   │                                       hourly_activity_with_daily_context
                                   └──> daily_revenue
                                   └──> daily_order_partitioned

Materialization types

Section titled “Materialization types”

The project demonstrates several materialization strategies:

  • Views (stg_customers, stg_orders, stg_payments) - lightweight staging layer
  • Tables (fact_orders, dim_customers, daily_revenue) - full table rebuilds
  • Merge incremental (customer_status_snapshot) - upsert with timestamp cursor
  • Delete/insert incremental (hourly_order_activity, daily_activity_rollup, hourly_activity_with_daily_context) - microbatch processing with configurable batch sizes
  • Integer cursor incremental (order_status_index) - non-timestamp incremental
  • Custom materialization (daily_order_partitioned) - Python-defined partition-tracked strategy

See Incremental for details on cursor-based strategies and microbatch processing.

Project structure

Section titled “Project structure”
waffle-shop/
  sqlbuild_project.toml         # project config
  sources/
    raw.yml                     # source declarations (loader-managed and expression-backed)
  python/
    loaders/
      waffle_sources.py         # Python loaders for raw__customers and raw__orders
  seeds/
    waffle_types.csv            # seed data
    lookups.yml                 # seed declarations
  functions/
    sql/
      udf__is_completed_order.sql    # scalar SQL UDF
      table_fn__customer_orders.sql  # table function
  models/
    staging/
      stg_customers.sql         # view (audits declared in MODEL() header)
      stg_orders.sql            # view
      stg_payments.sql          # view
    marts/
      fact_orders.sql           # table
      dim_customers.sql         # table
      daily_revenue.sql         # table
      daily_order_partitioned.sql # custom materialization
      hourly_order_activity.sql # microbatch incremental
      hourly_activity_with_daily_context.sql # microbatch incremental
      daily_activity_rollup.sql # microbatch incremental
      _sqlbuild/
        _audits/
          generic/
            expression_is_true.sql # custom generic audit
        _macros/                # visible only to models directly in marts/
          currency.py           # cents_to_dollars macro
          datetime.py           # adapter-portable timestamp_trunc
    intermediate/
      customer_status_snapshot.sql  # merge incremental
      order_status_index.sql       # integer cursor incremental
  tests/
    unit/
      test_stg_orders.sql              # model unit test
      test_fact_orders.sql             # multi-model test
      test_daily_revenue_chain.sql     # multi-model test
      test_line_total_cents_macro.sql  # macro test
    scenarios/
      daily_revenue_minimal.sql   # E2E scenario test
      daily_revenue_multi_order.sql # E2E scenario test
  macros/
    currency.py                 # project-wide line_total_cents macro
  materializations/
    partition_tracked.py        # custom materialization

Next steps

Section titled “Next steps”
  • Models - understand MODEL() headers and materialization types
  • Rules - add compile-time SQL, architecture, and model-shape checks
  • Functions - SQL UDFs, Python UDFs, and table functions
  • Incremental - learn about cursor-based incremental strategies
  • Audits - configure data quality checks
  • Testing - write SQL unit tests and multi-model tests with macro support
  • Column Lineage - trace individual columns through your pipeline
  • CLI Reference - full command reference
Part of SQLStack