Skip to main content

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:

CustomerStandard status
10001ACTIVE
10002ACTIVE
10003ACTIVE
10004INACTIVE
10005UNKNOWN

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.