04. Bronze Layer
Bronze Layer: Standardize Raw Data
Bronze models are close to one-to-one copies of RAW tables. They rename columns, standardize values, and cast data types. They do not contain complex business metrics.
Create stg_customers
models/bronze/customers/stg_customers.sql
{{ config(materialized='view') }}
SELECT
CUSTOMER_ID,
TRIM(FIRST_NAME) AS FIRST_NAME,
TRIM(LAST_NAME) AS LAST_NAME,
LOWER(TRIM(EMAIL)) AS EMAIL,
UPPER(TRIM(COUNTRY)) AS COUNTRY,
CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
UPDATED_AT::TIMESTAMP_NTZ AS UPDATED_AT,
LOWER(TRIM(CUSTOMER_STATUS)) AS CUSTOMER_STATUS
FROM {{ source('ecommerce_raw', 'customers') }}
Run the model:
dbt run --select stg_customers
Verify in Snowflake:
SELECT
CUSTOMER_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
COUNTRY,
CUSTOMER_STATUS
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_CUSTOMERS
ORDER BY CUSTOMER_ID
LIMIT 5;
Expected output:
| CUSTOMER_ID | FIRST_NAME | LAST_NAME | COUNTRY | CUSTOMER_STATUS | |
|---|---|---|---|---|---|
| 10001 | Asha | Sharma | asha@example.com | INDIA | active |
| 10002 | Daniel | Lee | daniel@example.com | SINGAPORE | active |
| 10003 | Maya | Patel | maya@example.com | INDIA | active |
| 10004 | Ravi | Kumar | ravi@example.com | INDIA | active |
| 10006 | Nora | Tan | nora@example.com | SINGAPORE | active |
Create stg_orders
models/bronze/orders/stg_orders.sql
{{ config(materialized='view') }}
SELECT
ORDER_ID,
CUSTOMER_ID,
PROMOTION_ID,
ORDER_DATE::DATE AS ORDER_DATE,
UPPER(TRIM(ORDER_STATUS)) AS ORDER_STATUS,
UPPER(TRIM(CURRENCY)) AS CURRENCY,
SHIPPING_ADDRESS_ID,
UPDATED_AT::TIMESTAMP_NTZ AS UPDATED_AT
FROM {{ source('ecommerce_raw', 'orders') }}
Run and verify:
dbt run --select stg_orders
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS,
CURRENCY
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_ORDERS
ORDER BY ORDER_ID
LIMIT 5;
| ORDER_ID | CUSTOMER_ID | ORDER_DATE | ORDER_STATUS | CURRENCY |
|---|---|---|---|---|
| 50001 | 10001 | 2025-02-01 | DELIVERED | INR |
| 50002 | 10002 | 2025-02-02 | DELIVERED | SGD |
| 50003 | 10003 | 2025-02-03 | SHIPPED | INR |
| 50004 | 10004 | 2025-02-04 | CANCELLED | INR |
| 50005 | 10006 | 2025-02-05 | DELIVERED | SGD |
Create stg_order_items
models/bronze/orders/stg_order_items.sql
{{ config(materialized='view') }}
SELECT
ORDER_ITEM_ID,
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
COALESCE(DISCOUNT_AMOUNT, 0) AS DISCOUNT_AMOUNT
FROM {{ source('ecommerce_raw', 'order_items') }}
Run and verify:
dbt run --select stg_order_items
SELECT
ORDER_ITEM_ID,
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
DISCOUNT_AMOUNT
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_ORDER_ITEMS
ORDER BY ORDER_ITEM_ID
LIMIT 5;
| ORDER_ITEM_ID | ORDER_ID | PRODUCT_ID | QUANTITY | UNIT_PRICE | DISCOUNT_AMOUNT |
|---|---|---|---|---|---|
| 1 | 50001 | 101 | 1 | 76700.00 | 0.00 |
| 2 | 50002 | 102 | 1 | 81419.00 | 0.00 |
| 3 | 50003 | 103 | 1 | 7769.00 | 0.00 |
| 4 | 50004 | 104 | 1 | 5359.00 | 0.00 |
| 5 | 50005 | 105 | 1 | 4769.00 | 0.00 |
Create stg_customer_addresses
models/bronze/customers/stg_customer_addresses.sql
{{ config(materialized='view') }}
SELECT
ADDRESS_ID,
CUSTOMER_ID,
UPPER(TRIM(ADDRESS_TYPE)) AS ADDRESS_TYPE,
TRIM(CITY) AS CITY,
UPPER(TRIM(STATE)) AS STATE,
TRIM(POSTAL_CODE) AS POSTAL_CODE,
IS_CURRENT,
UPDATED_AT::TIMESTAMP_NTZ AS UPDATED_AT
FROM {{ source('ecommerce_raw', 'customer_addresses') }}
Run the model:
dbt run --select stg_customer_addresses
Verify in Snowflake:
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_CUSTOMER_ADDRESSES
LIMIT 5;
| ADDRESS_ID | CUSTOMER_ID | ADDRESS_TYPE | CITY | STATE | POSTAL_CODE | IS_CURRENT |
|---|---|---|---|---|---|---|
| No rows |
Create stg_products
models/bronze/products/stg_products.sql
{{ config(materialized='view') }}
SELECT
PRODUCT_ID,
CATEGORY_ID,
TRIM(PRODUCT_NAME) AS PRODUCT_NAME,
UNIT_PRICE::NUMBER(12, 2) AS UNIT_PRICE,
UPPER(TRIM(PRODUCT_STATUS)) AS PRODUCT_STATUS,
UPDATED_AT::TIMESTAMP_NTZ AS UPDATED_AT
FROM {{ source('ecommerce_raw', 'products') }}
Run and verify:
dbt run --select stg_products
SELECT
PRODUCT_ID,
CATEGORY_ID,
PRODUCT_NAME,
UNIT_PRICE,
PRODUCT_STATUS
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_PRODUCTS
ORDER BY PRODUCT_ID
LIMIT 5;
| PRODUCT_ID | CATEGORY_ID | PRODUCT_NAME | UNIT_PRICE | PRODUCT_STATUS |
|---|---|---|---|---|
| 101 | 10 | Snowflake Fundamentals | 499.00 | ACTIVE |
| 102 | 10 | dbt Project Workshop | 799.00 | ACTIVE |
| 103 | 20 | Data Quality Lab | 299.00 | ACTIVE |
Create stg_categories
models/bronze/products/stg_categories.sql
{{ config(materialized='view') }}
SELECT
CATEGORY_ID,
TRIM(CATEGORY_NAME) AS CATEGORY_NAME,
PARENT_CATEGORY_ID
FROM {{ source('ecommerce_raw', 'categories') }}
Run and verify:
dbt run --select stg_categories
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_CATEGORIES
ORDER BY CATEGORY_ID
LIMIT 5;
| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID |
|---|---|---|
| 10 | Data Engineering | |
| 20 | Cloud Training |
Create stg_payments
models/bronze/payments/stg_payments.sql
{{ config(materialized='view') }}
SELECT
PAYMENT_ID,
ORDER_ID,
LOWER(TRIM(PAYMENT_METHOD)) AS PAYMENT_METHOD,
UPPER(TRIM(PAYMENT_STATUS)) AS PAYMENT_STATUS,
AMOUNT::NUMBER(12, 2) AS AMOUNT,
PAYMENT_DATE::TIMESTAMP_NTZ AS PAYMENT_DATE
FROM {{ source('ecommerce_raw', 'payments') }}
Run and verify:
dbt run --select stg_payments
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_PAYMENTS
ORDER BY PAYMENT_ID
LIMIT 5;
| PAYMENT_ID | ORDER_ID | PAYMENT_METHOD | PAYMENT_STATUS | AMOUNT |
|---|---|---|---|---|
| No rows |
Create stg_shipments
models/bronze/orders/stg_shipments.sql
{{ config(materialized='view') }}
SELECT
SHIPMENT_ID,
ORDER_ID,
SELLER_ID,
UPPER(TRIM(COURIER_PARTNER)) AS COURIER_PARTNER,
AWB_NUMBER,
SHIPMENT_DATE::DATE AS SHIPMENT_DATE,
EXPECTED_DELIVERY_DATE::DATE AS EXPECTED_DELIVERY_DATE,
UPPER(TRIM(SHIPPING_STATUS)) AS SHIPPING_STATUS,
ORIGIN_PINCODE,
DESTINATION_PINCODE,
CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
UPDATED_AT::TIMESTAMP_NTZ AS UPDATED_AT
FROM {{ source('ecommerce_raw', 'shipments') }}
Run and verify:
dbt run --select stg_shipments
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_SHIPMENTS
LIMIT 5;
| SHIPMENT_ID | ORDER_ID | SELLER_ID | COURIER_PARTNER | SHIPPING_STATUS |
|---|---|---|---|---|
| No rows |
Create stg_returns
models/bronze/orders/stg_returns.sql
{{ config(materialized='view') }}
SELECT
RETURN_ID,
ORDER_ITEM_ID,
RETURN_DATE::DATE AS RETURN_DATE,
RETURN_QUANTITY,
LOWER(TRIM(RETURN_REASON)) AS RETURN_REASON,
UPPER(TRIM(RETURN_STATUS)) AS RETURN_STATUS
FROM {{ source('ecommerce_raw', 'returns') }}
Run and verify:
dbt run --select stg_returns
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_RETURNS
LIMIT 5;
| RETURN_ID | ORDER_ITEM_ID | RETURN_DATE | RETURN_QUANTITY | RETURN_REASON | RETURN_STATUS |
|---|---|---|---|---|---|
| No rows |
Create stg_promotions
models/bronze/orders/stg_promotions.sql
{{ config(materialized='view') }}
SELECT
PROMOTION_ID,
UPPER(TRIM(PROMOTION_CODE)) AS PROMOTION_CODE,
DISCOUNT_PERCENT::NUMBER(5, 2) AS DISCOUNT_PERCENT,
STARTS_ON::DATE AS STARTS_ON,
ENDS_ON::DATE AS ENDS_ON,
UPPER(TRIM(PROMOTION_STATUS)) AS PROMOTION_STATUS
FROM {{ source('ecommerce_raw', 'promotions') }}
Run and verify:
dbt run --select stg_promotions
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_PROMOTIONS
LIMIT 5;
| PROMOTION_ID | PROMOTION_CODE | DISCOUNT_PERCENT | STARTS_ON | ENDS_ON | PROMOTION_STATUS |
|---|---|---|---|---|---|
| No rows |
Create stg_inventory_snapshots
models/bronze/products/stg_inventory_snapshots.sql
{{ config(materialized='view') }}
SELECT
SNAPSHOT_DATE::DATE AS SNAPSHOT_DATE,
PRODUCT_ID,
UPPER(TRIM(WAREHOUSE_CODE)) AS WAREHOUSE_CODE,
QUANTITY_ON_HAND,
REORDER_LEVEL
FROM {{ source('ecommerce_raw', 'inventory_snapshots') }}
Run and verify:
dbt run --select stg_inventory_snapshots
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_INVENTORY_SNAPSHOTS
LIMIT 5;
| SNAPSHOT_DATE | PRODUCT_ID | WAREHOUSE_CODE | QUANTITY_ON_HAND | REORDER_LEVEL |
|---|---|---|---|---|
| No rows |
Create stg_product_reviews
models/bronze/products/stg_product_reviews.sql
{{ config(materialized='view') }}
SELECT
REVIEW_ID,
PRODUCT_ID,
CUSTOMER_ID,
RATING,
TRIM(REVIEW_TEXT) AS REVIEW_TEXT,
REVIEWED_AT::TIMESTAMP_NTZ AS REVIEWED_AT
FROM {{ source('ecommerce_raw', 'product_reviews') }}
Run and verify:
dbt run --select stg_product_reviews
SELECT *
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_PRODUCT_REVIEWS
LIMIT 5;
| REVIEW_ID | PRODUCT_ID | CUSTOMER_ID | RATING | REVIEW_TEXT | REVIEWED_AT |
|---|---|---|---|---|---|
| No rows |
Continue with 05. Silver Layer.