Models

MODEL (
  materialized table,
  tags [marts],
);

SELECT
  o.order_id,
  o.customer_id,
  w.waffle_name,
  w.price_cents * o.quantity AS line_total_cents
FROM __ref("stg_orders") o
LEFT JOIN __ref("waffle_types") w ON o.waffle_type_id = w.waffle_type_id

MODEL (
  materialized view,
  tags [staging],
);

SELECT id AS order_id, customer_id, status
FROM __source("raw_orders")

MODEL (
  materialized table,
  tags [marts],
);

SELECT customer_id, COUNT(*) AS total_orders
FROM __ref("stg_orders")
GROUP BY customer_id

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  cursor_inputs (
    fact_orders ordered_at,
  ),
  incremental_mode microbatch,
  batch_size 1d,
  tags [marts],
);

SELECT
  DATE_TRUNC('hour', o.ordered_at) AS activity_hour,
  COUNT(*) AS orders_placed,
  SUM(o.quantity) AS waffles_ordered
FROM __ref("fact_orders") o
GROUP BY DATE_TRUNC('hour', o.ordered_at)

MODEL (
  materialized partition_tracked,
  tags [marts],
  placeholders (
    partition_start '\'2026-04-01\'',
    partition_end '\'2026-04-05\'',
  ),
  config (
    tracking_table main.partition_state,
    partition_column order_date,
    date_range_start 2026-04-01,
    date_range_end 2026-04-05,
  ),
);

SELECT
  CAST(o.ordered_at AS DATE) AS order_date,
  COUNT(DISTINCT o.order_id) AS order_count
FROM __ref("stg_orders") o
WHERE CAST(o.ordered_at AS DATE) >= CAST(@@partition_start AS DATE)
  AND CAST(o.ordered_at AS DATE) < CAST(@@partition_end AS DATE)
GROUP BY CAST(o.ordered_at AS DATE)

SELECT *
FROM __ref("stg_orders") o
LEFT JOIN __ref("stg_payments") p ON o.order_id = p.order_id
LEFT JOIN __ref("waffle_types") w ON o.waffle_type_id = w.waffle_type_id

models:
  - name: fact_orders
    description: Order fact table with waffle and payment details.
    type_enforcement: true
    columns:
      - name: order_id
        type: INTEGER
        audits:
          - not_null
          - unique
      - name: customer_id
        type: INTEGER
        audits:
          - not_null
    audits:
      - expression_is_true:
          name: revenue is non-negative
          expression: "total_revenue_cents >= 0"

columns:
  - name: activity_hour
    audits:
      - not_null:
          run_scope: delta_and_final
audits:
  - expression_is_true:
      name: orders placed is non-negative
      expression: "orders_placed >= 0"
      run_scope: delta_and_final

MODEL (
  materialized table,
  post_hook ['@grant_target(${CTX:target.qualified})'],
);