06. Gold Layer
Gold Layer: Publish Analytics Models
Gold models are designed for analytics. Dimensions describe entities; facts record measurable business events. The main fact in this demo is FCT_ORDERS, with one row per order.
Create fct_orders
models/gold/facts/fct_orders.sql
{{ config(materialized='table') }}
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
UPPER(ORDER_STATUS) AS ORDER_STATUS,
FINAL_AMOUNT
FROM {{ ref('int_customer_orders') }}
Run the model:
dbt run --select fct_orders
Verify in Snowflake:
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS,
FINAL_AMOUNT
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_ORDERS
ORDER BY ORDER_ID
LIMIT 5;
Expected output:
| 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 dim_customers
models/gold/dimensions/dim_customers.sql
{{ config(materialized='table') }}
SELECT
CUSTOMER_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
COUNTRY,
CUSTOMER_STATUS
FROM {{ ref('stg_customers') }}
Run and verify:
dbt run --select dim_customers
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.DIM_CUSTOMERS
ORDER BY CUSTOMER_ID
LIMIT 5;
| CUSTOMER_ID | FIRST_NAME | LAST_NAME | COUNTRY | CUSTOMER_STATUS |
|---|---|---|---|---|
| 10001 | Asha | Sharma | INDIA | active |
| 10002 | Daniel | Lee | SINGAPORE | active |
| 10003 | Maya | Patel | INDIA | active |
| 10004 | Ravi | Kumar | INDIA | active |
| 10006 | Nora | Tan | SINGAPORE | active |
Create fct_order_items
models/gold/facts/fct_order_items.sql
{{ config(materialized='table') }}
SELECT
ORDER_ITEM_ID,
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
DISCOUNT_AMOUNT,
FINAL_AMOUNT
FROM {{ ref('int_order_details') }}
Run and verify:
dbt run --select fct_order_items
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_ORDER_ITEMS
ORDER BY ORDER_ITEM_ID
LIMIT 5;
| ORDER_ITEM_ID | ORDER_ID | PRODUCT_ID | QUANTITY | FINAL_AMOUNT |
|---|---|---|---|---|
| 1 | 50001 | 101 | 1 | 76700.00 |
| 2 | 50002 | 102 | 1 | 81419.00 |
| 3 | 50003 | 103 | 1 | 7769.00 |
| 4 | 50004 | 104 | 1 | 5359.00 |
| 5 | 50005 | 105 | 1 | 4769.00 |
Create dim_products
models/gold/dimensions/dim_products.sql
{{ config(materialized='table') }}
SELECT
P.PRODUCT_ID,
P.PRODUCT_NAME,
P.CATEGORY_ID,
P.UNIT_PRICE,
P.PRODUCT_STATUS
FROM {{ ref('stg_products') }} AS P
Run and verify:
dbt run --select dim_products
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.DIM_PRODUCTS
ORDER BY PRODUCT_ID
LIMIT 5;
| PRODUCT_ID | PRODUCT_NAME | CATEGORY_ID | UNIT_PRICE | PRODUCT_STATUS |
|---|---|---|---|---|
| 101 | Snowflake Fundamentals | 10 | 499.00 | ACTIVE |
| 102 | dbt Project Workshop | 10 | 799.00 | ACTIVE |
| 103 | Data Quality Lab | 20 | 299.00 | ACTIVE |
Create dim_sellers
models/gold/dimensions/dim_sellers.sql
{{ config(materialized='table') }}
SELECT DISTINCT
SELLER_ID
FROM {{ ref('stg_shipments') }}
WHERE SELLER_ID IS NOT NULL
Run and verify:
dbt run --select dim_sellers
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.DIM_SELLERS
LIMIT 5;
| SELLER_ID |
|---|
| No rows in the current sample |
Create dim_date
models/gold/dimensions/dim_date.sql
{{ config(materialized='table') }}
SELECT DISTINCT
ORDER_DATE AS DATE_KEY,
YEAR(ORDER_DATE) AS YEAR_NUMBER,
MONTH(ORDER_DATE) AS MONTH_NUMBER,
DAY(ORDER_DATE) AS DAY_NUMBER,
DAYNAME(ORDER_DATE) AS DAY_NAME
FROM {{ ref('fct_orders') }}
Run and verify:
dbt run --select dim_date
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.DIM_DATE
ORDER BY DATE_KEY
LIMIT 5;
| DATE_KEY | YEAR_NUMBER | MONTH_NUMBER | DAY_NUMBER | DAY_NAME |
|---|---|---|---|---|
| 2025-02-01 | 2025 | 2 | 1 | Sat |
| 2025-02-02 | 2025 | 2 | 2 | Sun |
| 2025-02-03 | 2025 | 2 | 3 | Mon |
| 2025-02-04 | 2025 | 2 | 4 | Tue |
| 2025-02-05 | 2025 | 2 | 5 | Wed |
Create fct_payments
models/gold/facts/fct_payments.sql
{{ config(materialized='table') }}
SELECT
PAYMENT_ID,
ORDER_ID,
CUSTOMER_ID,
PAYMENT_METHOD,
PAYMENT_STATUS,
AMOUNT,
PAYMENT_DATE
FROM {{ ref('int_payment_details') }}
Run and verify:
dbt run --select fct_payments
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_PAYMENTS
ORDER BY PAYMENT_ID
LIMIT 5;
| PAYMENT_ID | ORDER_ID | CUSTOMER_ID | PAYMENT_METHOD | PAYMENT_STATUS | AMOUNT |
|---|---|---|---|---|---|
| No rows in the current sample |
Create fct_deliveries
models/gold/facts/fct_deliveries.sql
{{ config(materialized='table') }}
SELECT
SHIPMENT_ID,
ORDER_ID,
SELLER_ID,
COURIER_PARTNER,
SHIPMENT_DATE,
EXPECTED_DELIVERY_DATE,
SHIPPING_STATUS
FROM {{ ref('stg_shipments') }}
Run and verify:
dbt run --select fct_deliveries
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_DELIVERIES
LIMIT 5;
| SHIPMENT_ID | ORDER_ID | SELLER_ID | COURIER_PARTNER | SHIPPING_STATUS |
|---|---|---|---|---|
| No rows in the current sample |
Create fct_returns
models/gold/facts/fct_returns.sql
{{ config(materialized='table') }}
SELECT
RETURN_ID,
ORDER_ITEM_ID,
RETURN_DATE,
RETURN_QUANTITY,
RETURN_REASON,
RETURN_STATUS
FROM {{ ref('stg_returns') }}
Run and verify:
dbt run --select fct_returns
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_RETURNS
LIMIT 5;
| RETURN_ID | ORDER_ITEM_ID | RETURN_DATE | RETURN_QUANTITY | RETURN_REASON | RETURN_STATUS |
|---|---|---|---|---|---|
| No rows in the current sample |
Continue with 07. KPI and Mart Layer.