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 data, not more complex ones, as a good practice.

Well, in order to put MLV data on the warehouse staging area is it better implementing a pipeline or a stored procedure in the warehouse, in terms of best practices and performances?

Thanks

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.

5 Replies

  • 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.
  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    Hi pmscorca,

    Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to smeetsh for sharing valuable insights.

     

    Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

     

    Thank you for being part of the Microsoft Fabric Community.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    5 months ago

    Hello pmscorca,

    We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

    Thank you.