Skip to main content

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_IDFIRST_NAMELAST_NAMECOUNTRYTOTAL_REVENUETOTAL_ORDERS
10001AshaSharmaINDIA76700.001
10002DanielLeeSINGAPORE81419.001
10003MayaPatelINDIA7769.001
10004RaviKumarINDIA0.000
10006NoraTanSINGAPORE4769.001

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_STATUSTOTAL_ORDERSTOTAL_REVENUEAVERAGE_ORDER_VALUE
CANCELLED15359.005359.00
DELIVERED3162888.0054296.00
SHIPPED17769.007769.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_IDPRODUCT_NAMEUNITS_SOLDTOTAL_REVENUE
101Snowflake Fundamentals176700.00
102dbt Project Workshop181419.00
103Data Quality Lab17769.00
104Premium Support Package15359.00
105Data Governance Toolkit14769.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_IDTOTAL_ORDERSTOTAL_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_DATETOTAL_ORDERSTOTAL_REVENUEAVERAGE_ORDER_VALUE
2025-02-01176700.0076700.00
2025-02-02181419.0081419.00
2025-02-0317769.007769.00
2025-02-0514769.004769.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.