Skip to main content

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_IDFIRST_NAMELAST_NAMEEMAILCOUNTRYCUSTOMER_STATUS
10001AshaSharmaasha@example.comINDIAactive
10002DanielLeedaniel@example.comSINGAPOREactive
10003MayaPatelmaya@example.comINDIAactive
10004RaviKumarravi@example.comINDIAactive
10006NoraTannora@example.comSINGAPOREactive

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_IDCUSTOMER_IDORDER_DATEORDER_STATUSCURRENCY
50001100012025-02-01DELIVEREDINR
50002100022025-02-02DELIVEREDSGD
50003100032025-02-03SHIPPEDINR
50004100042025-02-04CANCELLEDINR
50005100062025-02-05DELIVEREDSGD

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_IDORDER_IDPRODUCT_IDQUANTITYUNIT_PRICEDISCOUNT_AMOUNT
150001101176700.000.00
250002102181419.000.00
35000310317769.000.00
45000410415359.000.00
55000510514769.000.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_IDCUSTOMER_IDADDRESS_TYPECITYSTATEPOSTAL_CODEIS_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_IDCATEGORY_IDPRODUCT_NAMEUNIT_PRICEPRODUCT_STATUS
10110Snowflake Fundamentals499.00ACTIVE
10210dbt Project Workshop799.00ACTIVE
10320Data Quality Lab299.00ACTIVE

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_IDCATEGORY_NAMEPARENT_CATEGORY_ID
10Data Engineering
20Cloud 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_IDORDER_IDPAYMENT_METHODPAYMENT_STATUSAMOUNT
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_IDORDER_IDSELLER_IDCOURIER_PARTNERSHIPPING_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_IDORDER_ITEM_IDRETURN_DATERETURN_QUANTITYRETURN_REASONRETURN_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_IDPROMOTION_CODEDISCOUNT_PERCENTSTARTS_ONENDS_ONPROMOTION_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_DATEPRODUCT_IDWAREHOUSE_CODEQUANTITY_ON_HANDREORDER_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_IDPRODUCT_IDCUSTOMER_IDRATINGREVIEW_TEXTREVIEWED_AT
No rows

Continue with 05. Silver Layer.