version: 2

sources:
  - name: crm
    description: "Customer system of record (Postgres)"
    database: postgres
    schema: crm
    tables:
      - name: customers
      - name: leads

  - name: sales
    description: "Order history (Snowflake)"
    database: snowflake
    schema: sales
    tables:
      - name: orders
      - name: order_items

  # Stripe — registered in Datattach. No credentials here.
  - name: stripe
    schema: stripe
    tables:            # trim to the objects you need
      - name: payments
      - name: customers

  # Where the ingest model lands it — a schema you own.
  - name: stripe_landed
    database: snowflake
    schema: stripe_landed
    tables:
      - name: payments

name: acme_analytics
version: "1.0"
profile: datattach

models:
  acme_analytics:
    staging:
      postgres:
        +database: postgres    # this folder builds in Postgres
        +materialized: view
      snowflake:
        +database: snowflake   # this folder builds in Snowflake
        +materialized: view
    marts:
      +database: snowflake     # marts land in the warehouse
      +materialized: table
    ingest:
      destination_snowflake:
        +materialized: ingest  # from the Datattach dbt package
        +database: snowflake   # a catalog you own
        +schema: stripe_landed

-- Extracts from the Stripe API and lands rows in
-- snowflake.stripe_landed (set by folder config).

{{ config(alias='payments', cursor='created') }}

SELECT * FROM {{ source('stripe', 'payments') }}

-- Source: Postgres (crm.customers)

SELECT
    id      AS customer_id,
    name,
    region,
    email
FROM {{ source('crm', 'customers') }}

-- Source: Snowflake (sales.orders)

SELECT
    id          AS order_id,
    customer_id,
    total,
    ordered_at
FROM {{ source('sales', 'orders') }}

-- Landed by the ingest model; the landed table is a source.
-- depends_on: {{ ref('stripe_landed_payments') }}

SELECT
    id              AS payment_id,
    order_id,
    amount / 100.0  AS amount,
    status,
    created_at
FROM {{ source('stripe_landed', 'payments') }}
WHERE status = 'succeeded'

-- One model, two databases.
-- customers lives in Postgres; orders live in Snowflake.
-- Datattach federates the join — no pipeline moved this data.

WITH customers AS (
    SELECT * FROM {{ ref('stg_postgres_customers') }}
),

orders AS (
    SELECT * FROM {{ ref('stg_snowflake_orders') }}
)

SELECT
    c.customer_id,
    c.name,
    c.region,
    COUNT(o.order_id)  AS orders,
    SUM(o.total)       AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY 1, 2, 3

-- Orders ⋈ Stripe payments — both live in Snowflake now,
-- so this whole model runs natively there.

WITH orders AS (
    SELECT * FROM {{ ref('stg_snowflake_orders') }}
),

payments AS (
    SELECT * FROM {{ ref('stg_stripe_payments') }}
)

SELECT
    DATE_TRUNC('month', p.created_at)  AS month,
    SUM(p.amount)                     AS mrr
FROM payments p
JOIN orders o ON o.order_id = p.order_id
GROUP BY 1
ORDER BY 1

version: 2

models:
  - name: customer_lifetime_value
    description: "Revenue per customer across CRM + order history"
    columns:
      - name: customer_id
        tests: [unique, not_null]
      - name: lifetime_value
        tests: [not_null]

  - name: finance_mrr
    columns:
      - name: month
        tests: [unique]

# One profile. One connection. Every engine behind it.
datattach:
  target: prod
  outputs:
    prod:
      type: trino
      host: trino.datattach.com
      port: 443
      user: "{{ env_var('DATATTACH_USER') }}"
      password: "{{ env_var('DATATTACH_TOKEN') }}"
      catalog: postgres   # default — models can build anywhere
      schema: analytics