07. KPI and Mart Layer
KPI and Mart Layer
The KPI layer turns Gold facts into governed business measures. A KPI is a Key Performance Indicator, such as revenue, order count, or average order value. The Mart layer shapes those measures for a dashboard or a specific consumer.
Create customer_360
models/mart/customer_360.sql
{{ config(materialized='view') }}
SELECT
C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
C.COUNTRY,
C.CUSTOMER_STATUS,
COALESCE(SUM(F.FINAL_AMOUNT), 0) AS TOTAL_REVENUE,
COUNT(F.ORDER_ID) AS TOTAL_ORDERS
FROM {{ ref('dim_customers') }} AS C
LEFT JOIN {{ ref('fct_orders') }} AS F
ON C.CUSTOMER_ID = F.CUSTOMER_ID
AND F.ORDER_STATUS <> 'CANCELLED'
GROUP BY
C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
C.COUNTRY,
C.CUSTOMER_STATUS
Run and verify:
dbt run --select customer_360
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.CUSTOMER_360
ORDER BY CUSTOMER_ID
LIMIT 5;
| CUSTOMER_ID | FIRST_NAME | LAST_NAME | COUNTRY | TOTAL_REVENUE | TOTAL_ORDERS |
|---|---|---|---|---|---|
| 10001 | Asha | Sharma | INDIA | 76700.00 | 1 |
| 10002 | Daniel | Lee | SINGAPORE | 81419.00 | 1 |
| 10003 | Maya | Patel | INDIA | 7769.00 | 1 |
| 10004 | Ravi | Kumar | INDIA | 0.00 | 0 |
| 10006 | Nora | Tan | SINGAPORE | 4769.00 | 1 |
Create sales_performance
models/mart/sales_performance.sql
{{ config(materialized='view') }}
SELECT
ORDER_STATUS,
COUNT(*) AS TOTAL_ORDERS,
SUM(FINAL_AMOUNT) AS TOTAL_REVENUE,
AVG(FINAL_AMOUNT) AS AVERAGE_ORDER_VALUE
FROM {{ ref('fct_orders') }}
GROUP BY ORDER_STATUS
Run and verify:
dbt run --select sales_performance
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.SALES_PERFORMANCE
ORDER BY ORDER_STATUS;
| ORDER_STATUS | TOTAL_ORDERS | TOTAL_REVENUE | AVERAGE_ORDER_VALUE |
|---|---|---|---|
| CANCELLED | 1 | 5359.00 | 5359.00 |
| DELIVERED | 3 | 162888.00 | 54296.00 |
| SHIPPED | 1 | 7769.00 | 7769.00 |
Create product_performance
models/mart/product_performance.sql
{{ config(materialized='view') }}
SELECT
P.PRODUCT_ID,
P.PRODUCT_NAME,
SUM(F.QUANTITY) AS UNITS_SOLD,
SUM(F.FINAL_AMOUNT) AS TOTAL_REVENUE
FROM {{ ref('dim_products') }} AS P
INNER JOIN {{ ref('fct_order_items') }} AS F
ON P.PRODUCT_ID = F.PRODUCT_ID
GROUP BY
P.PRODUCT_ID,
P.PRODUCT_NAME
Run and verify:
dbt run --select product_performance
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.PRODUCT_PERFORMANCE
ORDER BY PRODUCT_ID
LIMIT 5;
| PRODUCT_ID | PRODUCT_NAME | UNITS_SOLD | TOTAL_REVENUE |
|---|---|---|---|
| 101 | Snowflake Fundamentals | 1 | 76700.00 |
| 102 | dbt Project Workshop | 1 | 81419.00 |
| 103 | Data Quality Lab | 1 | 7769.00 |
| 104 | Premium Support Package | 1 | 5359.00 |
| 105 | Data Governance Toolkit | 1 | 4769.00 |
Create seller_performance
models/mart/seller_performance.sql
{{ config(materialized='view') }}
SELECT
SELLER_ID,
COUNT(DISTINCT ORDER_ID) AS TOTAL_ORDERS,
SUM(FINAL_AMOUNT) AS TOTAL_REVENUE
FROM {{ ref('fct_deliveries') }} AS D
INNER JOIN {{ ref('fct_orders') }} AS O
ON D.ORDER_ID = O.ORDER_ID
GROUP BY SELLER_ID
Run and verify:
dbt run --select seller_performance
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.SELLER_PERFORMANCE
LIMIT 5;
| SELLER_ID | TOTAL_ORDERS | TOTAL_REVENUE |
|---|---|---|
| No rows in the current sample |
Create daily_ecommerce_kpi
models/mart/daily_ecommerce_kpi.sql
{{ config(materialized='view') }}
SELECT
ORDER_DATE AS SALES_DATE,
COUNT(DISTINCT ORDER_ID) AS TOTAL_ORDERS,
SUM(FINAL_AMOUNT) AS TOTAL_REVENUE,
SUM(FINAL_AMOUNT) / NULLIF(COUNT(DISTINCT ORDER_ID), 0)
AS AVERAGE_ORDER_VALUE
FROM {{ ref('fct_orders') }}
WHERE ORDER_STATUS <> 'CANCELLED'
GROUP BY ORDER_DATE
Run and verify:
dbt run --select daily_ecommerce_kpi
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.DAILY_ECOMMERCE_KPI
ORDER BY SALES_DATE;
| SALES_DATE | TOTAL_ORDERS | TOTAL_REVENUE | AVERAGE_ORDER_VALUE |
|---|---|---|---|
| 2025-02-01 | 1 | 76700.00 | 76700.00 |
| 2025-02-02 | 1 | 81419.00 | 81419.00 |
| 2025-02-03 | 1 | 7769.00 | 7769.00 |
| 2025-02-05 | 1 | 4769.00 | 4769.00 |
Full Pipeline Run
Run the complete project after reviewing each layer:
dbt run
Verify the final fact and Mart outputs:
SELECT *
FROM BEEMS_TECHNE_ECOMM.GOLD.FCT_ORDERS
ORDER BY ORDER_ID
LIMIT 5;
SELECT *
FROM BEEMS_TECHNE_ECOMM.MART.DAILY_ECOMMERCE_KPI
ORDER BY SALES_DATE;
The Gold layer owns reusable business meaning. The Mart layer presents that meaning in a shape that a dashboard can consume directly.