Writing Macros

my_project/
├── macros/
│   ├── currency.py
│   └── test_helpers.py
├── models/
└── sqlbuild_project.toml

# macros/currency.py
def cents_to_dollars(expression: str) -> str:
    """Convert a cents expression to dollars."""
    return f"ROUND(CAST({expression} AS DOUBLE) / 100, 2)"

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

SELECT
  order_id,
  @cents_to_dollars("amount_cents") AS amount_dollars
FROM __ref("stg_orders")

ROUND(CAST(amount_cents AS DOUBLE) / 100, 2)

@mock_orders(count=5, status="completed")

# macros/orders.py
_DEFAULT_STATUS = "completed"


def _status_filter(status: str) -> str:
    return f"status = '{status}'"


def completed_orders() -> str:
    return _status_filter(_DEFAULT_STATUS)

# macros/test_helpers.py
def mock_orders(count: int = 1) -> str:
    rows = [
        f"SELECT {i} AS id, {i * 100} AS customer_id, 'completed' AS status"
        for i in range(1, count + 1)
    ]
    return " UNION ALL ".join(rows)

TEST();

WITH
__source__raw__orders AS (
  @mock_orders(3)
),
__expected__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 'completed' AS status
  UNION ALL
  SELECT 2 AS order_id, 200 AS customer_id, 'completed' AS status
  UNION ALL
  SELECT 3 AS order_id, 300 AS customer_id, 'completed' AS status
)
SELECT 1

# macros/permissions.py
def grant_target(target: str) -> str:
    return f"GRANT SELECT ON {target} TO analyst_role"

MODEL (
  post_hooks [inline_sql('@grant_target(@@CTX:destination.qualified)')],
);

SELECT 1 AS id