Models

MODEL (
  materialized table,
  tags [marts],
  description "Order fact table with waffle and payment details.",
  columns (
    order_id (audits [not_null]),
  ),
);

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 __seed("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 snapshot,
  unique_key [customer_id],
  snapshot_strategy timestamp,
  updated_at updated_at,
);

SELECT
  customer_id,
  name,
  plan,
  status,
  updated_at
FROM __source("customers")

MODEL (
  materialized partition_tracked,
  tags [marts],
  placeholders (
    partition_start "'2026-04-01'",
    partition_end "'2026-04-05'",
  ),
  config (
    tracking_table partition_state,
    partition_column order_date,
    date_range_start 2026-04-01,
    date_range_end 2026-04-05,
  ),
  description "Partition-tracked daily order summary using custom materialization.",
  columns (
    order_date (audits [not_null]),
  ),
  audits [
    expression_is_true (
      name "waffles ordered is positive",
      expression "waffles_ordered > 0",
    ),
  ],
);

SELECT
  CAST(o.ordered_at AS DATE) AS order_date,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(o.quantity) AS waffles_ordered,
  COUNT(DISTINCT o.customer_id) AS unique_customers
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
  o.order_id,
  o.customer_id,
  w.waffle_name,
  w.price_cents * o.quantity AS line_total_cents,
  __udf("udf__is_completed_order")(o.status) AS is_completed
FROM __ref("stg_orders") o
LEFT JOIN __seed("waffle_types") w ON o.waffle_type_id = w.waffle_type_id

MODEL (
  materialized view,
  tags [staging],
  description "Cleaned order records.",
  columns (
    order_id (audits [not_null, unique]),
    customer_id (audits [not_null]),
    status (
      audits [
        accepted_values (values ["placed", "preparing", "ready", "completed", "cancelled"]),
      ],
    ),
  ),
);

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

columns (
  order_id (audits [not_null, unique]),
  status (
    audits [
      accepted_values (values ["placed", "preparing", "completed", "cancelled"]),
    ],
  ),
),

MODEL (
  materialized table,
  audits [
    expression_is_true (
      name "revenue is non-negative",
      expression "total_revenue_cents >= 0",
    ),
  ],
);

MODEL (
  materialized table,
  columns (
    order_id (type INTEGER, audits [not_null]),
    amount_cents (type INTEGER),
  ),
);

MODEL (
  materialized table,
  contract enforced,
  columns (
    order_id (type INTEGER, audits [not_null]),
    customer_id (type INTEGER, audits [not_null]),
    amount_cents (type INTEGER),
    status (type VARCHAR),
  ),
);

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  columns (
    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_hooks [sql('GRANT SELECT ON @@CTX:destination.qualified TO analyst_role')],
);

# hooks/permissions.py
from sqlbuild.hooks import hook

@hook
def grant_analyst(ctx):
    ctx.execute_sql(f"GRANT SELECT ON {ctx.destination.qualified} TO analyst_role")

MODEL (
  materialized table,
  post_hooks [python("grant_analyst")],
);

MODEL (
  materialized table,
  post_hooks [python("grant_analyst", role: "reader_role")],
);

MODEL (
  materialized table,
  pre_hooks [sql('SET search_path TO analytics')],
  post_hooks [
    python("grant_analyst"),
    sql('ANALYZE @@CTX:destination.qualified'),
  ],
);

from sqlbuild.hooks import hook

@hook
def grant_analyst(ctx):
    """Grant analyst role on the destination table."""
    ctx.execute_sql(f"GRANT SELECT ON {ctx.destination.qualified} TO analyst_role")

@hook(name="custom_name", description="Custom hook with explicit name")
def my_hook(ctx, role="analyst_role"):
    ctx.execute_sql(f"GRANT SELECT ON {ctx.destination.qualified} TO {role}")