Building a SQL-Generating Status Macro
Learning objectives
You will package a business rule into a reusable macro, normalize messy source values, and use the same rule for more than one model.
1. The business problem
Different source applications may represent the same state differently:
| Source values | Standard value |
|---|---|
ACTIVE, A, ENABLED | ACTIVE |
INACTIVE, I, DISABLED | INACTIVE |
CANCEL, CANCELLED, CANCELED | CANCELLED |
DELIVERED, COMPLETE, COMPLETED, SUCCESS | COMPLETED |
PROCESSING, IN_PROGRESS, PENDING | PENDING |
FAILED, FAILURE, REJECTED | FAILED |
NULL | UNKNOWN |
The analytical layer should work with a controlled vocabulary while the raw layer preserves the original value.
2. Create the macro
Create macros/standardize_status.sql:
{% macro standardize_status(status_expression) %}
CASE
WHEN {{ status_expression }} IS NULL THEN 'UNKNOWN'
WHEN UPPER(TRIM({{ status_expression }})) IN ('ACTIVE', 'A', 'ENABLED')
THEN 'ACTIVE'
WHEN UPPER(TRIM({{ status_expression }})) IN ('INACTIVE', 'I', 'DISABLED')
THEN 'INACTIVE'
WHEN UPPER(TRIM({{ status_expression }})) IN ('CANCEL', 'CANCELLED', 'CANCELED')
THEN 'CANCELLED'
WHEN UPPER(TRIM({{ status_expression }})) IN ('DELIVERED', 'COMPLETE', 'COMPLETED', 'SUCCESS')
THEN 'COMPLETED'
WHEN UPPER(TRIM({{ status_expression }})) IN ('PROCESSING', 'IN_PROGRESS', 'PENDING')
THEN 'PENDING'
WHEN UPPER(TRIM({{ status_expression }})) IN ('FAILED', 'FAILURE', 'REJECTED')
THEN 'FAILED'
ELSE UPPER(TRIM({{ status_expression }}))
END
{% endmacro %}
The parameter is called status_expression, not status_column, because callers may pass a column, a qualified column, or another SQL expression:
{{ standardize_status('customer_status') }}
{{ standardize_status('orders.order_status') }}
3. Why TRIM() and UPPER() matter
The values active, ACTIVE, and Active should not create three business states. TRIM() removes surrounding whitespace and UPPER() makes comparisons case-insensitive.
The ELSE branch keeps an unexpected value visible instead of silently converting it to UNKNOWN. This is useful during source investigation. A team may later choose a stricter rule that maps all unrecognized values to UNKNOWN.
4. See the generated SQL
A model containing:
SELECT
customer_id,
{{ standardize_status('customer_status') }} AS customer_status
FROM {{ source('raw', 'customers') }}
will compile into ordinary SQL containing a CASE expression. Snowflake never receives the Jinja itself.
Practice
Add a mapping for ON_HOLD and PAUSED to PENDING. Then explain why the change should be made in this macro rather than copied into each staging model.
Summary
- SQL-generating macros return reusable SQL expressions.
- A macro can accept a column name or any safe SQL expression.
- Normalization makes source-system values comparable.
- The raw value remains untouched; standardization belongs in a downstream model.
The next lesson uses this macro with real Snowflake tables and dbt models.