Forum Discussion

pmscorca's avatar
pmscorca
Kudo Kingpin
6 months ago
Solved

Using MLV to write data from a lakehouse into a warehouse

Hi, for a Fabric solution I've a lakehouse to save data read from some sources and a warehouse having a staging area and a dwh area. I think to use some MLVs to perform some transformation on raw d...
data engineering
lakehouse
  • smeetsh's avatar
    smeetsh
    6 months ago

    I stand corrected, you can use a shortcut, but you dont have to, you can query the MLV directly and insert it in a staging table in the warehouse, and from there ETL to your gold layer, using a pipeline

    Some sample code:

    TRUNCATE TABLE stg.factSales_stg;

     

    INSERT INTO stg.factSales_stg
    (
        sale_id, sale_date, customer_id, product_id,
        quantity, amount, region_name, last_update_ts
    )
    SELECT
        sale_id, sale_date, customer_id, product_id,
        quantity, amount, region_name, SYSUTCDATETIME()
    FROM lh_sales.sales.v_fact_sales_mlv
     
    MLV to staging table, staging table ETL to gold layer is the considered best practice.
smeetsh's avatar
smeetsh
Continued Contributor
6 months ago

I have not realy used them, but as far as I understood, you create the MLV in your lake house (using a notebook or UI), you can than use that MLV to create a shortcut in the warehouse, where you could that shortcut to use the MLV to do a merge into your gold table in warehouse

A code example:

-- Target: an existing Warehouse table
-- Source: the MLV exposed in Warehouse via a shortcut schema

 

MERGE INTO dbo.factSales AS target
USING shortcut_gold.fact_sales_mlv AS source   -- this is your MLV via shortcut
    ON target.sale_id = source.sale_id
WHEN MATCHED THEN
    UPDATE SET
        target.sale_date   = source.sale_date,
        target.customer_id = source.customer_id,
        target.product_id  = source.product_id,
        target.quantity    = source.quantity,
        target.amount      = source.amount,
        target.region_name = source.region_name,
        target.last_update_ts = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (sale_id, sale_date, customer_id, product_id, quantity, amount, region_name, last_update_ts)
    VALUES (source.sale_id, source.sale_date, source.customer_id, source.product_id,
            source.quantity, source.amount, source.region_name, SYSUTCDATETIME());
  • pmscorca's avatar
    pmscorca
    Kudo Kingpin
    6 months ago

    Hi, do you say that the best pratice to put MLV data into a warehouse is to use shortcuts without implementing any ETLs, obtaining optimal performances?

    • smeetsh's avatar
      smeetsh
      Continued Contributor
      6 months ago

      I stand corrected, you can use a shortcut, but you dont have to, you can query the MLV directly and insert it in a staging table in the warehouse, and from there ETL to your gold layer, using a pipeline

      Some sample code:

      TRUNCATE TABLE stg.factSales_stg;

       

      INSERT INTO stg.factSales_stg
      (
          sale_id, sale_date, customer_id, product_id,
          quantity, amount, region_name, last_update_ts
      )
      SELECT
          sale_id, sale_date, customer_id, product_id,
          quantity, amount, region_name, SYSUTCDATETIME()
      FROM lh_sales.sales.v_fact_sales_mlv
       
      MLV to staging table, staging table ETL to gold layer is the considered best practice.