RevPipeline360
Fabric Lakehouse Build

Workspace & Assets

Gold Schema — Dimensions

• gold_dim_date — date_key, calendar_date, year, month, quarter, week, fiscal_year, fiscal_month
• gold_dim_customers — master_customer_id (PK), customer_name, industry, region, active_flag
• gold_dim_products — master_sku (PK), product_name, product_family, category, uom
• gold_dim_users — salesrep_id (PK), rep_name, manager, territory, region
• gold_dim_campaigns — campaign_id (PK), campaign_name, channel, start_date/end_date + date_keys

Gold Schema — Facts

• gold_fact_opportunity — opportunity_id, customer_id, salesrep_id, stage, is_closed, is_won, amount, close_date_key, created_date_key
• gold_fact_opportunity_line — line items joined to opportunity and products (product_sku, quantity, unit_price, discount_pct, line_amount)
• gold_fact_leads — lead_id, lead_source, campaign_id, created_date_key, converted_to_opportunity_id
• gold_fact_campaigns — campaign activity fact bridging campaigns dim to lead funnel
• gold_fact_invoice_header — invoice_id, opportunity_id, customer_id, invoice_date_key, due_date_key, status
• gold_fact_invoice_line — line-level invoice detail with product and customer enrichment
• gold_fact_gl_postings — posting_date_key, customer_id, account_code, account_type, amount
• gold_fact_budget_monthly — month_date_key, region, budget_revenue, budget_cogs, budget_gross_profit, budget_gm_pct
• gold_fact_headcount_monthly — month_date_key, region, headcount, hires, attrition, attrition_rate

Relationship Design

• Active paths: gold_dim_date → fact tables via primary date key per fact (e.g., close_date_key for opportunity, invoice_date_key for invoice line)
• Inactive relationships: created_date_key, due_date_key, campaign start/end date keys — activated via USERELATIONSHIP in DAX where needed
• Attribution pattern: campaign → lead → opportunity linked via TREATAS on converted_to_opportunity_id to avoid ambiguous active paths
• Pipeline hardening: retries + timeouts on notebook activities to handle Spark/Livy session throttling (HTTP 430)

Key DAX Measures

Medallion ELT Pattern

All transforms run inside Fabric via PySpark notebooks using spark.sql() with CREATE OR REPLACE TABLE statements. This ensures idempotent rebuilds — re-running any notebook fully refreshes the layer without manual cleanup. Silver focuses exclusively on data quality (types, nulls, trimming); Gold focuses exclusively on business logic (joins, date key enrichment, derived metrics).

Campaign Date Key Enhancement

The gold_dim_campaigns table was enhanced with both start_date_key and end_date_key by joining to gold_dim_date twice during Gold build. This enables fiscal year overlap slicing for the Active Campaigns (FY) measure without requiring a bridge table.

Ambiguous Relationship Resolution

Several fact tables (opportunity, invoice header) have multiple date foreign keys pointing to gold_dim_date. Only one relationship per fact table is set active (the primary analytical date). Alternate date roles are activated in DAX measures using USERELATIONSHIP. For campaign-to-opportunity attribution where no direct foreign key exists, TREATAS is used to project the filtered opportunity set from lead conversion data.

Pipeline Governance

The PL_RevPipeline360_Refresh pipeline uses On Success chaining between Silver and Gold notebook activities. Retry policies and timeout settings were added after intermittent Spark/Livy session throttling (HTTP 430) was observed during early runs on shared capacity. Interactive notebook sessions should not run in parallel with pipeline execution.