Dimensional Modeling and Schema Design Questions

Given orders_fact(order_id, product_business_key, order_date) and product_dim(product_key, product_business_key, effective_from, effective_to, current_flag) where product_dim is versioned with SCD Type 2, write a SQL query that returns each order with the product_key that was correct as of the order's order_date (not simply the current version).

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 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.

For a B2B SaaS analytics product, compare three ways to power the BI dashboards: a classic star schema, a single wide denormalized table, and a lakehouse (raw events plus views). Recommend one approach and justify it in terms of migration effort, query performance, and long-term maintainability.

Design a minimal star schema for a two-sided marketplace's ride analytics (for example a rideshare or delivery platform), supporting daily revenue, cancellations, driver or courier earnings, and pooled or shared trips. Specify the fact table's grain and core measures, and the dimensions you need (driver, rider, vehicle, city, and pricing).