05. Silver Layer
Silver Layer: Integrate Business Data
Silver models join Bronze models and apply business rules. The main grain in this demo is one row per order.
Create int_customer_orders
models/silver/int_customer_orders.sql
{{ config(materialized='table') }}
SELECT
O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
O.ORDER_STATUS,
SUM((OI.QUANTITY * OI.UNIT_PRICE) - OI.DISCOUNT_AMOUNT) AS FINAL_AMOUNT
FROM {{ ref('stg_orders') }} AS O
INNER JOIN {{ ref('stg_order_items') }} AS OI
ON O.ORDER_ID = OI.ORDER_ID
GROUP BY
O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
O.ORDER_STATUS
Run the model:
dbt run --select int_customer_orders
Verify in Snowflake:
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS,
FINAL_AMOUNT
FROM BEEMS_TECHNE_ECOMM.SILVER.INT_CUSTOMER_ORDERS
ORDER BY ORDER_ID
LIMIT 5;
| ORDER_ID | CUSTOMER_ID | ORDER_DATE | ORDER_STATUS | FINAL_AMOUNT |
|---|---|---|---|---|
| 50001 | 10001 | 2025-02-01 | DELIVERED | 76700.00 |
| 50002 | 10002 | 2025-02-02 | DELIVERED | 81419.00 |
| 50003 | 10003 | 2025-02-03 | SHIPPED | 7769.00 |
| 50004 | 10004 | 2025-02-04 | CANCELLED | 5359.00 |
| 50005 | 10006 | 2025-02-05 | DELIVERED | 4769.00 |
Create int_order_details
models/silver/int_order_details.sql
{{ config(materialized='table') }}
SELECT
O.ORDER_ID,
O.CUSTOMER_ID,
O.ORDER_DATE,
O.ORDER_STATUS,
OI.ORDER_ITEM_ID,
OI.PRODUCT_ID,
OI.QUANTITY,
OI.UNIT_PRICE,
OI.DISCOUNT_AMOUNT,
(OI.QUANTITY * OI.UNIT_PRICE) - OI.DISCOUNT_AMOUNT AS FINAL_AMOUNT
FROM {{ ref('stg_orders') }} AS O
INNER JOIN {{ ref('stg_order_items') }} AS OI
ON O.ORDER_ID = OI.ORDER_ID
Run and verify:
dbt run --select int_order_details
SELECT *
FROM BEEMS_TECHNE_ECOMM.SILVER.INT_ORDER_DETAILS
ORDER BY ORDER_ID
LIMIT 5;
| ORDER_ID | CUSTOMER_ID | ORDER_ITEM_ID | PRODUCT_ID | FINAL_AMOUNT |
|---|---|---|---|---|
| 50001 | 10001 | 1 | 101 | 76700.00 |
| 50002 | 10002 | 2 | 102 | 81419.00 |
| 50003 | 10003 | 3 | 103 | 7769.00 |
| 50004 | 10004 | 4 | 104 | 5359.00 |
| 50005 | 10006 | 5 | 105 | 4769.00 |
Create int_payment_details
models/silver/int_payment_details.sql
{{ config(materialized='table') }}
SELECT
P.PAYMENT_ID,
P.ORDER_ID,
O.CUSTOMER_ID,
P.PAYMENT_METHOD,
P.PAYMENT_STATUS,
P.AMOUNT,
P.PAYMENT_DATE
FROM {{ ref('stg_payments') }} AS P
INNER JOIN {{ ref('stg_orders') }} AS O
ON P.ORDER_ID = O.ORDER_ID
Run the model:
dbt run --select int_payment_details
Verify in Snowflake:
SELECT
PAYMENT_ID,
ORDER_ID,
CUSTOMER_ID,
PAYMENT_METHOD,
PAYMENT_STATUS,
AMOUNT
FROM BEEMS_TECHNE_ECOMM.SILVER.INT_PAYMENT_DETAILS
ORDER BY PAYMENT_ID
LIMIT 5;
| PAYMENT_ID | ORDER_ID | CUSTOMER_ID | PAYMENT_METHOD | PAYMENT_STATUS | AMOUNT |
|---|---|---|---|---|---|
| No rows in the current sample |
Continue with 06. Gold Layer.