dbt_project/
├── dbt_project.yml              # Project configuration
├── packages.yml                 # Package dependencies
├── profiles.yml                 # Connection profiles (local only, not committed)
├── models/
│   ├── staging/                 # 1:1 with source tables
│   │   ├── stripe/
│   │   │   ├── _stripe__models.yml
│   │   │   ├── _stripe__sources.yml
│   │   │   ├── stg_stripe__payments.sql
│   │   │   └── stg_stripe__customers.sql
│   │   └── shopify/
│   │       ├── _shopify__models.yml
│   │       ├── _shopify__sources.yml
│   │       └── stg_shopify__orders.sql
│   ├── intermediate/            # Business logic joins and transformations
│   │   ├── finance/
│   │   │   ├── _int_finance__models.yml
│   │   │   └── int_payments_pivoted.sql
│   │   └── marketing/
│   │       └── int_web_sessions_sessionized.sql
│   └── marts/                   # Business-facing tables
│       ├── finance/
│       │   ├── _finance__models.yml
│       │   ├── fct_orders.sql
│       │   └── dim_customers.sql
│       └── marketing/
│           ├── _marketing__models.yml
│           └── fct_web_sessions.sql
├── macros/                      # Reusable Jinja macros
├── tests/                       # Singular and generic tests
│   └── generic/                 # Custom generic tests
├── seeds/                       # CSV reference data
├── snapshots/                   # SCD Type 2 snapshots
└── analyses/                    # Ad-hoc analytical queries

name: 'my_project'
version: '1.0.0'
config-version: 2

profile: 'my_project'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets: ["target", "dbt_packages"]

models:
  my_project:
    staging:
      +materialized: view           # Lightweight, always fresh
    intermediate:
      +materialized: ephemeral      # No warehouse cost, inlined as CTEs
    marts:
      +materialized: table          # Fast reads for BI tools

Raw Data (sources)
    ↓
Staging (stg_) ── view ── clean, rename, cast, 1:1
    ↓
Intermediate (int_) ── ephemeral ── join, pivot, business logic
    ↓
Marts (fct_, dim_) ── table/incremental ── business entities
    ↓
BI Tools / Consumers

-- stg_stripe__payments.sql
with source as (
    select * from {{ source('stripe', 'payments') }}
),

renamed as (
    select
        -- primary key
        id as payment_id,

        -- foreign keys
        order_id,
        customer_id,

        -- properties
        lower(payment_method) as payment_method,
        status as payment_status,

        -- numerics
        amount / 100.0 as amount_dollars,

        -- timestamps
        created_at,
        updated_at

    from source
)

select * from renamed

-- int_payments_pivoted.sql
with payments as (
    select * from {{ ref('stg_stripe__payments') }}
),

pivoted as (
    select
        order_id,
        sum(case when payment_method = 'credit_card' then amount_dollars else 0 end) as credit_card_amount,
        sum(case when payment_method = 'bank_transfer' then amount_dollars else 0 end) as bank_transfer_amount,
        sum(amount_dollars) as total_amount
    from payments
    where payment_status = 'completed'
    group by order_id
)

select * from pivoted

-- fct_orders.sql
with orders as (
    select * from {{ ref('stg_shopify__orders') }}
),

payments as (
    select * from {{ ref('int_payments_pivoted') }}
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.order_status,
        payments.credit_card_amount,
        payments.bank_transfer_amount,
        payments.total_amount
    from orders
    left join payments on orders.order_id = payments.order_id
)

select * from final

with source as (
    select * from {{ source('shopify', 'orders') }}
),

renamed as (
    select
        id as order_id,
        user_id as customer_id,
        lower(status) as order_status,
        created_at as order_date,
        updated_at
    from source
),

final as (
    select
        order_id,
        customer_id,
        order_status,
        order_date,
        updated_at
    from renamed
    where order_status is not null
)

select * from final

# _stripe__sources.yml
version: 2

sources:
  - name: stripe
    description: "Stripe payment data loaded by Fivetran"
    database: raw                     # Snowflake: database name
    schema: stripe                    # Snowflake: schema name
    loader: fivetran
    loaded_at_field: _fivetran_synced # For freshness checks
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: payments
        description: "One record per payment attempt"
        columns:
          - name: id
            description: "Primary key"
            data_tests:
              - unique
              - not_null
          - name: order_id
            description: "Foreign key to orders"
            data_tests:
              - not_null
              - relationships:
                  to: source('shopify', 'orders')
                  field: id

          /\
         /  \        Singular Tests (specific business rules)
        /____\
       /      \      Generic Tests (reusable patterns)
      /________\
     /          \    Schema Tests (YAML-configured)
    /____________\
   /              \  Source Freshness (automated)
  /________________\

# _finance__models.yml
version: 2

models:
  - name: fct_orders
    description: "One record per order with payment details"
    columns:
      - name: order_id
        description: "Primary key"
        data_tests:
          - unique
          - not_null
      - name: order_status
        data_tests:
          - accepted_values:
              values: ['completed', 'pending', 'cancelled', 'refunded']
      - name: total_amount
        data_tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0

source('stripe', 'payments')  →  stg_stripe__payments
                                       ↓
                                 int_payments_pivoted
                                       ↓
                                   fct_orders

-- WRONG: Hardcoded table reference
select * from raw.stripe.payments

-- WRONG: source() outside staging
-- (in a marts model)
select * from {{ source('stripe', 'payments') }}

-- WRONG: Skipping layers
-- (in a marts model referencing staging directly)
select * from {{ ref('stg_stripe__payments') }}

-- CORRECT: Follow the DAG
select * from {{ ref('int_payments_pivoted') }}