my_project/
  dbt_project.yml
  models/
    staging/
      stripe/
        _stripe__models.yml    # source + model definitions
        _stripe__sources.yml   # source freshness config
        stg_stripe__payments.sql
        stg_stripe__customers.sql
      shopify/
        _shopify__models.yml
        _shopify__sources.yml
        stg_shopify__orders.sql
    intermediate/
      int_orders__pivoted_payments.sql
    marts/
      finance/
        _finance__models.yml
        fct_orders.sql
        dim_customers.sql
      marketing/
        _marketing__models.yml
        fct_ad_spend.sql
  tests/
    singular/
      assert_order_total_positive.sql
  macros/
    cents_to_dollars.sql

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

renamed as (
    select
        id as payment_id,
        order_id,
        cast(amount as integer) as amount_cents,
        cast(created as timestamp) as created_at,
        status,
        lower(currency) as currency
    from source
)

select * from renamed

-- models/marts/finance/fct_orders.sql
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns'
    )
}}

with orders as (
    select * from {{ ref('stg_shopify__orders') }}
),

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

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        payments.total_amount_cents,
        payments.payment_method,
        payments.total_amount_cents / 100.0 as total_amount_dollars
    from orders
    left join payments on orders.order_id = payments.order_id
    {% if is_incremental() %}
    where orders.updated_at > (select max(updated_at) from {{ this }})
    {% endif %}
)

select * from final

# models/marts/finance/_finance__models.yml
semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: order_date
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    measures:
      - name: order_count
        agg: count
        expr: order_id
      - name: total_revenue_cents
        agg: sum
        expr: total_amount_cents
      - name: average_order_value_cents
        agg: average
        expr: total_amount_cents

metrics:
  - name: revenue
    type: derived
    label: "Total Revenue"
    description: "Sum of all order payments in dollars"
    type_params:
      expr: total_revenue_cents / 100
      metrics:
        - name: total_revenue_cents
  - name: order_count
    type: simple
    label: "Order Count"
    type_params:
      measure: order_count

# models/marts/finance/_finance__models.yml
models:
  - name: fct_orders
    description: "One row per order. Grain: order_id."
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: varchar
        description: "Primary key - unique order identifier"
        tests:
          - unique
          - not_null
      - name: customer_id
        description: "FK to dim_customers"
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: total_amount_cents
        data_type: integer
        description: "Total order value in cents"
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0

-- tests/singular/assert_order_total_positive.sql
-- Returns rows that violate the rule (should return 0 rows to pass)
select order_id, total_amount_cents
from {{ ref('fct_orders') }}
where total_amount_cents < 0

# models/staging/stripe/_stripe__sources.yml
sources:
  - name: stripe
    database: raw
    schema: stripe
    loaded_at_field: _loaded_at
    freshness:
      warn_after: { count: 12, period: hour }
      error_after: { count: 24, period: hour }
    tables:
      - name: payments
        description: "Raw Stripe payment events"
        columns:
          - name: id
            tests:
              - unique
              - not_null

-- models/marts/finance/dim_customers.sql
with customers as (
    select * from {{ ref('stg_shopify__customers') }}
),

orders as (
    select * from {{ ref('fct_orders') }}
),

customer_metrics as (
    select
        customer_id,
        count(*) as lifetime_order_count,
        sum(total_amount_cents) as lifetime_value_cents,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date
    from orders
    group by customer_id
),

final as (
    select
        customers.customer_id,
        customers.full_name,
        customers.email,
        customers.created_at as customer_since,
        coalesce(customer_metrics.lifetime_order_count, 0)
            as lifetime_order_count,
        coalesce(customer_metrics.lifetime_value_cents, 0)
            as lifetime_value_cents,
        customer_metrics.first_order_date,
        customer_metrics.most_recent_order_date,
        case
            when customer_metrics.lifetime_order_count >= 5
                then 'high_value'
            when customer_metrics.lifetime_order_count >= 2
                then 'returning'
            else 'new'
        end as customer_segment
    from customers
    left join customer_metrics
        on customers.customer_id = customer_metrics.customer_id
)

select * from final