Dimensional Modeling and Schema Design Questions

Two source systems share the same natural business key for customers but assign different customer IDs, and some customers overlap between the two. You must merge both sources into a single customer dimension with clean surrogate keys and no collisions. Propose a strategy to resolve conflicts, deduplicate, and maintain backward compatibility with fact tables that already reference the old surrogate keys, and write a SQL approach to deduplicate a staging table by keeping the most recently updated record per business key.

What are conformed dimensions, and why do they matter once you have multiple fact tables or data marts (for example, sales, returns, and shipments)? Describe a concrete plan to implement conformed customer and product dimensions so that revenue, support, and marketing dashboards all report consistent attributes and totals, and how you would detect and prevent divergence across teams over time.

Two fact tables exist: orders_fact at order-line grain and refunds_fact at refund-event grain, both referencing the same product and customer dimensions. Analysts need a cross-fact net-revenue report without double counting. Describe the schema and query pattern you would use to correctly combine two facts at different grains into one net-revenue number.

Design a resilient process to handle late-arriving facts (facts that arrive out of order relative to the periods they belong to, and can update previously-computed daily aggregates). Describe how you would detect that a correction is needed, re-run only the affected aggregates rather than the whole table, keep ingestion idempotent, and prioritize reprocessing to control recompute cost.

What is a date (calendar) dimension, and why is it worth building as a dedicated dimension rather than deriving date parts on the fly at query time? List the attributes you would include, and explain how a BI tool uses them for built-in time-intelligence features.