You have a Fabric workspace that contains two lakehouses named Lakehouse1 and Lakehouse2. Lakehouse1 contains staging data in a Delta table named Orderlines. Lakehouse2 contains a Type 2 Slowly…

``sql SELECT orderLineID order_line_id ,OrderDate order_date ,c.customer_key ,c.customer_id ,Quantity order_quantity ,UnitPrice unit_price ,TaxRate tax_rate FROM Lakehouse1.orderlines o INNER JOIN lakehouse2.dim_customer c ON o.customerid = c.customer_id AND c.is_current = 1…

Design and implement data modeling

Question

You have a Fabric workspace that contains two lakehouses named Lakehouse1 and Lakehouse2. Lakehouse1 contains staging data in a Delta table named Orderlines. Lakehouse2 contains a Type 2 Slowly changing dimension (SCD) dimension table named Dim_Customer.
You need to build a query that will combine data from Orderlines and Dim_Customer to create a new fact table named Fact_Orders. The new table must meet the following requirements:
Enable the analysis of customer orders based on historical attributes.
Enable the analysis of customer orders based on the current attributes.

How should you complete the statement? To answer, select the appropriate options in the answer area.
SELECT
orderLineID order_line_id
,OrderDate order_date
,c.customer_key
,c.customer_id
,Quantity order_quantity
,UnitPrice unit_price
,TaxRate tax_rate
FROM
Lakehouse1.orderlines o
INNER JOIN
lakehouse2.dim_customer c
ON o.customerid = c.customer_id
AND
-- DROPDOWN 1 selections here
AND
-- DROPDOWN 2 selections here

Exhibit

Explanation

SELECT
    orderLineID order_line_id
    ,OrderDate order_date
    ,c.customer_key
    ,c.customer_id
    ,Quantity order_quantity
    ,UnitPrice unit_price
    ,TaxRate tax_rate
FROM
    Lakehouse1.orderlines o
INNER JOIN
    lakehouse2.dim_customer c
ON o.customerid = c.customer_id
AND
    c.is_current = 1
    AND o.OrderDate < c.valid_to_datetime
    AND o.OrderDate >= c.valid_from_datetime
AND
    c.is_current = 1
    AND o.OrderDate < c.valid_to_datetime
    AND o.OrderDate >= c.valid_from_datetime

Topics

#Slowly Changing Dimensions#Fact Tables#Data Modeling#SQL Joins

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice