Skip to main content

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_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 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_IDFIRST_NAMELAST_NAMECOUNTRYCUSTOMER_STATUS
10001AshaSharmaINDIAactive
10002DanielLeeSINGAPOREactive
10003MayaPatelINDIAactive
10004RaviKumarINDIAactive
10006NoraTanSINGAPOREactive

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_IDORDER_IDPRODUCT_IDQUANTITYFINAL_AMOUNT
150001101176700.00
250002102181419.00
35000310317769.00
45000410415359.00
55000510514769.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_IDPRODUCT_NAMECATEGORY_IDUNIT_PRICEPRODUCT_STATUS
101Snowflake Fundamentals10499.00ACTIVE
102dbt Project Workshop10799.00ACTIVE
103Data Quality Lab20299.00ACTIVE

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_KEYYEAR_NUMBERMONTH_NUMBERDAY_NUMBERDAY_NAME
2025-02-01202521Sat
2025-02-02202522Sun
2025-02-03202523Mon
2025-02-04202524Tue
2025-02-05202525Wed

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_IDORDER_IDCUSTOMER_IDPAYMENT_METHODPAYMENT_STATUSAMOUNT
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_IDORDER_IDSELLER_IDCOURIER_PARTNERSHIPPING_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_IDORDER_ITEM_IDRETURN_DATERETURN_QUANTITYRETURN_REASONRETURN_STATUS
No rows in the current sample

Continue with 07. KPI and Mart Layer.