Project Setup and Profiles

Project Structure, Profiles and Connecting to PostgreSQL and DuckDB

A dbt 37,942 project is a folder with a dbt_project.yml and conventional subfolders: models/, seeds/ (small CSV lookup tables), snapshots/ and tests/. BookNest's models follow the common layering: thin staging views, one per source table (stg_orders, stg_order_items, stg_customers, stg_books), and marts that analysts query (dim_book, dim_customer, dim_date, fct_sales).

Connections live in profiles, outside the SQL. dbt looks for profiles.yml in the current directory and then in ~/.dbt/. Each profile has targets; --target picks one, so the same models build in PostgreSQL 1,289 or DuckDB 61,228 :

profiles.yml: one project, two enginesYAML

booknest:
  target: pg
  outputs:
    pg:                                    # PostgreSQL 18 (container l2-pg)
      type: postgres
      host: "{{ env_var('BOOKNEST_PG_HOST', 'localhost') }}"
      port: "{{ env_var('BOOKNEST_PG_PORT', '32543') | as_number }}"
      user: postgres
      password: "{{ env_var('BOOKNEST_PG_PASSWORD', 'booknest') }}"
      dbname: booknest
      schema: analytics
      threads: 4
    duck:                                  # DuckDB file, reading the Section 4.6 database
      type: duckdb
      path: "{{ env_var('BOOKNEST_DUCK_PATH', 'booknest_dbt.duckdb') }}"
      attach:
        - path: "{{ env_var('BOOKNEST_DUCK_SRC', '/home/dev/v7-l2/ch04/booknest.duckdb') }}"
          alias: src
          read_only: true
      settings:
        TimeZone: UTC
      threads: 4

env_var() reads overrides from the environment, which is how Orchestration and Pipelines's Airflow 129 container will point the same profile at its own host and keep the password out of the file. The DuckDB target writes a new database file and attaches DuckDB: In-Process OLAP's booknest.duckdb read-only as src; settings fixes the session time zone, which would otherwise follow the host's. dbt debug checks it all: All checks passed!.

MENU