Skip to main content

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_IDCUSTOMER_IDORDER_DATEORDER_STATUSFINAL_AMOUNT
50001100012025-02-01DELIVERED76700.00
50002100022025-02-02DELIVERED81419.00
50003100032025-02-03SHIPPED7769.00
50004100042025-02-04CANCELLED5359.00
50005100062025-02-05DELIVERED4769.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_IDCUSTOMER_IDORDER_ITEM_IDPRODUCT_IDFINAL_AMOUNT
5000110001110176700.00
5000210002210281419.00
500031000331037769.00
500041000441045359.00
500051000651054769.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_IDORDER_IDCUSTOMER_IDPAYMENT_METHODPAYMENT_STATUSAMOUNT
No rows in the current sample

Continue with 06. Gold Layer.