Skip to content

Instantly share code, notes, and snippets.

@ralfbecher

ralfbecher/puppini_bridge_uss_1.sql

Created September 8, 2025 10:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ralfbecher/4ac9b1c3ede2674ce5ff07379755b1ed to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ralfbecher/4ac9b1c3ede2674ce5ff07379755b1ed to your computer and use it in GitHub Desktop.
Download ZIP
LLM generated virtual USS SQL View
Raw
puppini_bridge_uss_1.sql
-- =====================================================================
-- VIRTUAL PUPPINI BRIDGE - UNIFIED STAR SCHEMA (USS)
-- Schema: uss_1
-- Purpose: Create a unified fact table combining all business processes
-- (sales, purchases, shipments, returns) with conformed dimensions
-- Author: Ralfo Becher, OrionBelt
-- Date: 2025
-- =====================================================================
CREATE OR REPLACE VIEW uss_1.v_unified_star_schema AS
WITH
-- =================================================================
-- UNIFIED FACT TABLE using UNION ALL approach
-- Each fact grain is preserved with its natural key and measures
-- =================================================================
unified_facts AS (
-- ================== SALES FACTS ==================
SELECT
-- Unified Keys
'SALES' AS fact_type,
salesid AS transaction_id,
salesid AS sales_id,
NULL::TEXT AS purchase_id,
NULL::TEXT AS shipment_id,
NULL::TEXT AS return_id,
-- Date Keys (Conformed)
salesdate AS transaction_date,
EXTRACT(YEAR FROM salesdate) AS year,
EXTRACT(QUARTER FROM salesdate) AS quarter,
EXTRACT(MONTH FROM salesdate) AS month,
EXTRACT(WEEK FROM salesdate) AS week,
EXTRACT(DOW FROM salesdate) AS day_of_week,
-- Dimensional Keys
salesclient AS client_id,
product AS product_id,
salesempid AS employee_id,
saleschanid AS channel_id,
NULL::TEXT AS supplier_id,
salespaymenttype AS payment_type,
NULL::TEXT AS shipment_type,
-- Measures
salesquantity AS quantity,
salesamount AS revenue_amount,
0::NUMERIC AS cost_amount,
0::NUMERIC AS purchase_amount,
0::NUMERIC AS shipment_amount,
0::NUMERIC AS return_amount,
salesquantity AS units_sold,
0::NUMERIC AS units_purchased,
0::NUMERIC AS units_shipped,
0::NUMERIC AS units_returned,
-- Audit & Notes
notes AS transaction_notes,
'SALES_SYSTEM' AS source_system
FROM uss_1.sales
UNION ALL
-- ================== PURCHASE FACTS ==================
SELECT
-- Unified Keys
'PURCHASE' AS fact_type,
purchaseid AS transaction_id,
NULL::TEXT AS sales_id,
purchaseid AS purchase_id,
NULL::TEXT AS shipment_id,
NULL::TEXT AS return_id,
-- Date Keys (Conformed)
purchasedate AS transaction_date,
EXTRACT(YEAR FROM purchasedate) AS year,
EXTRACT(QUARTER FROM purchasedate) AS quarter,
EXTRACT(MONTH FROM purchasedate) AS month,
EXTRACT(WEEK FROM purchasedate) AS week,
EXTRACT(DOW FROM purchasedate) AS day_of_week,
-- Dimensional Keys
NULL::TEXT AS client_id,
purchaseproduct AS product_id,
purchaseempid AS employee_id,
purchasechanid AS channel_id,
purchasesupplier AS supplier_id,
NULL::TEXT AS payment_type,
NULL::TEXT AS shipment_type,
-- Measures
purchasequantity AS quantity,
0::NUMERIC AS revenue_amount,
purchaseamount AS cost_amount,
purchaseamount AS purchase_amount,
0::NUMERIC AS shipment_amount,
0::NUMERIC AS return_amount,
0::NUMERIC AS units_sold,
purchasequantity AS units_purchased,
0::NUMERIC AS units_shipped,
0::NUMERIC AS units_returned,
-- Audit & Notes
notes AS transaction_notes,
'PURCHASE_SYSTEM' AS source_system
FROM uss_1.purchases
UNION ALL
-- ================== SHIPMENT FACTS ==================
SELECT
-- Unified Keys
'SHIPMENT' AS fact_type,
shipmentid AS transaction_id,
shipmentsalesid AS sales_id,
NULL::TEXT AS purchase_id,
shipmentid AS shipment_id,
NULL::TEXT AS return_id,
-- Date Keys (Conformed)
shipmentdate AS transaction_date,
EXTRACT(YEAR FROM shipmentdate) AS year,
EXTRACT(QUARTER FROM shipmentdate) AS quarter,
EXTRACT(MONTH FROM shipmentdate) AS month,
EXTRACT(WEEK FROM shipmentdate) AS week,
EXTRACT(DOW FROM shipmentdate) AS day_of_week,
-- Dimensional Keys
shipmentclient AS client_id,
shipmentproduct AS product_id,
shipmentempid AS employee_id,
NULL::TEXT AS channel_id,
NULL::TEXT AS supplier_id,
NULL::TEXT AS payment_type,
shipmenttype AS shipment_type,
-- Measures
shipmentquantity AS quantity,
0::NUMERIC AS revenue_amount,
0::NUMERIC AS cost_amount,
0::NUMERIC AS purchase_amount,
shipmentamount AS shipment_amount,
0::NUMERIC AS return_amount,
0::NUMERIC AS units_sold,
0::NUMERIC AS units_purchased,
shipmentquantity AS units_shipped,
0::NUMERIC AS units_returned,
-- Audit & Notes
notes AS transaction_notes,
'SHIPMENT_SYSTEM' AS source_system
FROM uss_1.shipments
UNION ALL
-- ================== RETURN FACTS ==================
SELECT
-- Unified Keys
'RETURN' AS fact_type,
returnid AS transaction_id,
returnsalesid AS sales_id,
NULL::TEXT AS purchase_id,
NULL::TEXT AS shipment_id,
returnid AS return_id,
-- Date Keys (Conformed)
returndate AS transaction_date,
EXTRACT(YEAR FROM returndate) AS year,
EXTRACT(QUARTER FROM returndate) AS quarter,
EXTRACT(MONTH FROM returndate) AS month,
EXTRACT(WEEK FROM returndate) AS week,
EXTRACT(DOW FROM returndate) AS day_of_week,
-- Dimensional Keys
s.salesclient AS client_id,
s.product AS product_id,
returnempid AS employee_id,
s.saleschanid AS channel_id,
NULL::TEXT AS supplier_id,
s.salespaymenttype AS payment_type,
NULL::TEXT AS shipment_type,
-- Measures
returnquantity AS quantity,
-returnamount AS revenue_amount, -- Negative for returns
0::NUMERIC AS cost_amount,
0::NUMERIC AS purchase_amount,
0::NUMERIC AS shipment_amount,
returnamount AS return_amount,
-returnquantity AS units_sold, -- Negative for returns
0::NUMERIC AS units_purchased,
0::NUMERIC AS units_shipped,
returnquantity AS units_returned,
-- Audit & Notes
r.notes AS transaction_notes,
'RETURN_SYSTEM' AS source_system
FROM uss_1.returns r
LEFT JOIN uss_1.sales s ON r.returnsalesid = s.salesid
)
-- =================================================================
-- MAIN SELECT with Dimension Enrichment
-- Join unified facts with all dimension tables for complete context
-- =================================================================
SELECT
-- ============ FACT IDENTIFIERS ============
uf.fact_type,
uf.transaction_id,
uf.sales_id,
uf.purchase_id,
uf.shipment_id,
uf.return_id,
-- ============ TIME DIMENSIONS ============
uf.transaction_date,
uf.year,
uf.quarter,
uf.month,
uf.week,
uf.day_of_week,
cal.ym AS year_month,
cal.weekday,
cal.publicholiday AS is_public_holiday,
-- ============ CUSTOMER DIMENSIONS ============
uf.client_id,
cl.clientname AS client_name,
cl.clientgender AS client_gender,
cl.clientemail AS client_email,
cl.clientcountryid AS client_country_id,
-- ============ PRODUCT DIMENSIONS ============
uf.product_id,
p.productname AS product_name,
p.productcat AS product_category,
p.unitcost AS product_unit_cost,
p.unitprice AS product_unit_price,
p.curr AS product_currency,
p.unitsinstock AS product_units_in_stock,
-- ============ EMPLOYEE DIMENSIONS ============
uf.employee_id,
e.employeename AS employee_name,
e.department AS employee_department,
-- ============ CHANNEL DIMENSIONS ============
uf.channel_id,
ch.channelname AS channel_name,
-- ============ SUPPLIER DIMENSIONS ============
uf.supplier_id,
COALESCE(s.suppliername, p.productsuppl) AS supplier_name,
s.suppliercountryid AS supplier_country_id,
-- ============ GEOGRAPHIC DIMENSIONS ============
co.countryname AS client_country_name,
co.region AS client_region_id,
r.regionname AS client_region_name,
-- ============ TRANSACTION ATTRIBUTES ============
uf.payment_type,
uf.shipment_type,
-- ============ MEASURES (ADDITIVE) ============
uf.quantity,
uf.revenue_amount,
uf.cost_amount,
uf.purchase_amount,
uf.shipment_amount,
uf.return_amount,
uf.units_sold,
uf.units_purchased,
uf.units_shipped,
uf.units_returned,
-- ============ CALCULATED MEASURES ============
-- Profit Calculation
CASE
WHEN uf.fact_type = 'SALES' THEN uf.revenue_amount - (p.unitcost * uf.quantity)
WHEN uf.fact_type = 'RETURN' THEN -uf.return_amount + (p.unitcost * uf.quantity)
ELSE 0
END AS gross_profit,
-- Margin Calculation
CASE
WHEN uf.fact_type = 'SALES' AND uf.revenue_amount > 0
THEN ((uf.revenue_amount - (p.unitcost * uf.quantity)) / uf.revenue_amount) * 100
ELSE 0
END AS gross_margin_pct,
-- Net Revenue (Sales - Returns)
CASE
WHEN uf.fact_type IN ('SALES', 'RETURN') THEN uf.revenue_amount
ELSE 0
END AS net_revenue,
-- ============ AUDIT FIELDS ============
uf.transaction_notes,
uf.source_system,
CURRENT_TIMESTAMP AS view_refresh_timestamp
FROM unified_facts uf
-- Calendar Dimension
LEFT JOIN uss_1.calendar cal
ON uf.transaction_date = cal.date
-- Client Dimension
LEFT JOIN uss_1.clients cl
ON uf.client_id = cl.clientid
-- Product Dimension
LEFT JOIN uss_1.products p
ON uf.product_id = p.productid
-- Employee Dimension
LEFT JOIN uss_1.employees e
ON uf.employee_id = e.employeeid
-- Channel Dimension
LEFT JOIN uss_1.channels ch
ON uf.channel_id = ch.channelid
-- Supplier Dimension
LEFT JOIN uss_1.suppliers s
ON uf.supplier_id = s.supplierid OR p.productsuppl = s.supplierid
-- Geographic Dimensions
LEFT JOIN uss_1.countries co
ON cl.clientcountryid = co.countryid
LEFT JOIN uss_1.regions r
ON co.region = r.regionid
;
-- =====================================================================
-- GRANT PERMISSIONS (Adjust as needed for your environment)
-- =====================================================================
-- GRANT SELECT ON uss_1.v_unified_star_schema TO readonly_role;
-- GRANT SELECT ON uss_1.v_unified_star_schema TO analytics_role;
-- =====================================================================
-- USAGE EXAMPLES
-- =====================================================================
/*
-- Example 1: Total Sales by Product Category and Region
SELECT
product_category,
client_region_name,
SUM(revenue_amount) AS total_revenue,
SUM(units_sold) AS total_units_sold,
AVG(gross_margin_pct) AS avg_margin
FROM uss_1.v_unified_star_schema
WHERE fact_type = 'SALES'
AND year = 2024
GROUP BY product_category, client_region_name
ORDER BY total_revenue DESC;
-- Example 2: Cross-Functional Analysis (Sales vs Returns vs Shipments)
SELECT
year_month,
SUM(CASE WHEN fact_type = 'SALES' THEN revenue_amount ELSE 0 END) AS sales_revenue,
SUM(CASE WHEN fact_type = 'RETURN' THEN return_amount ELSE 0 END) AS return_amount,
SUM(CASE WHEN fact_type = 'SHIPMENT' THEN shipment_amount ELSE 0 END) AS shipment_value,
SUM(CASE WHEN fact_type = 'PURCHASE' THEN purchase_amount ELSE 0 END) AS purchase_cost
FROM uss_1.v_unified_star_schema
WHERE year = 2024
GROUP BY year_month
ORDER BY year_month;
-- Example 3: Employee Performance Across All Activities
SELECT
employee_name,
employee_department,
fact_type,
COUNT(DISTINCT transaction_id) AS num_transactions,
SUM(quantity) AS total_quantity,
SUM(revenue_amount) AS total_revenue
FROM uss_1.v_unified_star_schema
WHERE transaction_date >= '2024-01-01'
GROUP BY employee_name, employee_department, fact_type
ORDER BY employee_name, fact_type;
-- Example 4: Net Revenue Analysis (Sales minus Returns)
SELECT
product_name,
SUM(net_revenue) AS net_revenue,
SUM(units_sold) AS net_units_sold,
SUM(gross_profit) AS total_gross_profit
FROM uss_1.v_unified_star_schema
WHERE fact_type IN ('SALES', 'RETURN')
GROUP BY product_name
HAVING SUM(net_revenue) > 0
ORDER BY net_revenue DESC;
-- Example 5: Supply Chain Analysis
SELECT
supplier_name,
product_category,
SUM(CASE WHEN fact_type = 'PURCHASE' THEN units_purchased ELSE 0 END) AS units_purchased,
SUM(CASE WHEN fact_type = 'SALES' THEN units_sold ELSE 0 END) AS units_sold,
SUM(CASE WHEN fact_type = 'SHIPMENT' THEN units_shipped ELSE 0 END) AS units_shipped,
SUM(CASE WHEN fact_type = 'RETURN' THEN units_returned ELSE 0 END) AS units_returned
FROM uss_1.v_unified_star_schema
GROUP BY supplier_name, product_category
ORDER BY supplier_name, product_category;
*/
-- =====================================================================
-- PERFORMANCE OPTIMIZATION NOTES
-- =====================================================================
/*
For optimal performance, consider:
1. Creating indexes on frequently filtered columns:
CREATE INDEX idx_uss_fact_type ON unified_facts(fact_type);
CREATE INDEX idx_uss_transaction_date ON unified_facts(transaction_date);
2. Materializing this view if query performance is critical:
CREATE MATERIALIZED VIEW uss_1.mv_unified_star_schema AS
[VIEW DEFINITION];
3. Partitioning the underlying fact tables by date for large datasets
4. Using columnar storage or compression for analytical workloads
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment