Skip to main content

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 valuesStandard value
ACTIVE, A, ENABLEDACTIVE
INACTIVE, I, DISABLEDINACTIVE
CANCEL, CANCELLED, CANCELEDCANCELLED
DELIVERED, COMPLETE, COMPLETED, SUCCESSCOMPLETED
PROCESSING, IN_PROGRESS, PENDINGPENDING
FAILED, FAILURE, REJECTEDFAILED
NULLUNKNOWN

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.