Hands-on: Standardize Snowflake Status Data
This exercise takes the status macro from a code example to a working dbt transformation.
What you will build
BEEMS_TECHNE_ECOMM.RAW.CUSTOMERS
|
| stg_customers.sql
| standardize_status()
v
<dbt target schema>.STG_CUSTOMERS
You will preserve inconsistent values in RAW and create standardized values in staging.
1. Prepare the Snowflake context
Use a development database and schema that you are allowed to modify. The examples below use the training names:
USE DATABASE BEEMS_TECHNE_ECOMM;
USE SCHEMA RAW;
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();
Do not run CREATE OR REPLACE statements in a shared production schema.
2. Create a small raw table
CREATE OR REPLACE TABLE RAW.CUSTOMERS (
CUSTOMER_ID NUMBER(38, 0),
FIRST_NAME VARCHAR(100),
EMAIL VARCHAR(255),
CUSTOMER_STATUS VARCHAR(50),
UPDATED_AT TIMESTAMP_NTZ
);
Load deliberately inconsistent values:
INSERT INTO RAW.CUSTOMERS
(CUSTOMER_ID, FIRST_NAME, EMAIL, CUSTOMER_STATUS, UPDATED_AT)
VALUES
(10001, 'Asha', 'asha@example.com', 'A', '2025-01-05 09:00:00'),
(10002, 'Daniel', 'daniel@example.com', ' active ', '2025-01-06 10:30:00'),
(10003, 'Maya', 'maya@example.com', 'ENABLED', '2025-01-07 11:15:00'),
(10004, 'Ravi', 'ravi@example.com', 'I', '2025-01-08 12:00:00'),
(10005, 'Nora', 'nora@example.com', NULL, '2025-01-09 13:30:00');
Inspect the input before transforming it:
SELECT CUSTOMER_ID, CUSTOMER_STATUS
FROM RAW.CUSTOMERS
ORDER BY CUSTOMER_ID;
3. Create the staging model
Ensure macros/standardize_status.sql exists from the previous lesson. Then create models/staging/stg_customers.sql:
{{ config(materialized='view') }}
SELECT
CUSTOMER_ID,
FIRST_NAME,
EMAIL,
UPDATED_AT,
{{ standardize_status('CUSTOMER_STATUS') }} AS CUSTOMER_STATUS
FROM {{ source('raw', 'customers') }}
If your project does not yet define this source, use a temporary relation reference that matches your project, or add a source declaration such as:
sources:
- name: raw
database: BEEMS_TECHNE_ECOMM
schema: RAW
tables:
- name: customers
Use the project’s existing source conventions when they differ from this training example.
4. Compile before running
dbt compile --select stg_customers
Open the compiled model under target/compiled/ and confirm that the standardize_status() call has become a CASE expression and that source('raw', 'customers') has become a fully qualified relation.
5. Build and validate
dbt run --select stg_customers
Then query the target relation in Snowflake. Replace the placeholder with the schema configured by your dbt profile:
SELECT CUSTOMER_ID, CUSTOMER_STATUS
FROM BEEMS_TECHNE_ECOMM.<YOUR_DBT_TARGET_SCHEMA>.STG_CUSTOMERS
ORDER BY CUSTOMER_ID;
Expected result:
| Customer | Standard status |
|---|---|
| 10001 | ACTIVE |
| 10002 | ACTIVE |
| 10003 | ACTIVE |
| 10004 | INACTIVE |
| 10005 | UNKNOWN |
6. Extend the exercise
Create models/staging/stg_orders.sql and apply the same macro to ORDER_STATUS. Add source rows for DELIVERED, CANCEL, and PROCESSING, then confirm they become COMPLETED, CANCELLED, and PENDING.
Reflection
Why is it safer to leave RAW.CUSTOMERS.CUSTOMER_STATUS unchanged? In a layered architecture, raw data records what arrived, while staging records a reproducible transformation of that data.
Summary
- Build and inspect raw data before writing the transformation.
- Compile to verify the macro and source resolve correctly.
- Run the model only after the generated SQL looks right.
- Validate the final values in Snowflake, not only the dbt command output.