Forum Discussion

amaaiia's avatar
amaaiia
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

Use Dataflow Gen2 for multiple tables ingest dinamically

I have a Data Pipeline to ingest in my Lakehouse several tables located in an on-premise SQL Server, and the tables are going to be daily reloaded incrementally. They have a date field, and every day I'm going to delete X months of data of each table from Lakehouse. I have a Data Warehouse in Fabric with a table A that collects the list of tables that I have and want to reload, with fields table_name and months_to_reload.

 

Data ingestion has to be done with Dataflow Gen2 because my data is on-premise and it can only be done by DFGen2. My Data Pipeline has a ForEach that iterates over the records in table A. For each record, 2 steps:
1. Remove Lakehouse data for table_name from months_to_reload months ago.
2. Read from source (SQL Server on-premise) the data for table_name from months_to_reload months ago and write it to Lakehouse table_name.

 

My problem is that I can't pass variables to DataFlow Gen2 to tell it to read table table_name with a months_to_reload months filtering.

 

How can I set up a Data Pipeline with dynamic Dataflow Gen2, to which I can pass the name of the table I want to ingest and the date filters? If this cannot be done, I need to create a individual Data Pipeline for each of the tables I want to ingest, and I have 100. There must be a way to dinamically ingest tabla passing table_name as variable to Dataflow Gen2, the flow of data ingestion is always the same, and I can also pass date field names as variables stored in A table.

data engineering
Data Integration
power bi

21 Replies

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Hi amaaiia 

     

    Thanks for using Microsoft Fabric Community.
    At this time, we are reaching out to the internal team to get some help on this .
    We will update you once we hear back from them.

    Appreciate your patience.

     

    Thanks.

  • Element115's avatar
    Element115
    Icon for Memorable Member rankMemorable Member
    2 years ago

    amaaiia If I understand you correctly, how about having another table in the LH that contains 1 value only, namely the months_to_reload  value? 

    So for each delete-update cycle, in the DFg2, you read in this value in its own M query. Your other queries then can access the value. When the main query is done with its processing, you have another query that updates the months_to_reload value and publishes it back to the LH in replace mode.  

     

    Would that do the trick or am I missing something?

    • amaaiia's avatar
      amaaiia
      Icon for Skilled Sharer rankSkilled Sharer
      2 years ago

      Hi Element115 . yes, for months_to_realod it would be ok. In fact, I already have a warehouse table with the list of tables I need to ingest and with a column months_to_reload (table_name, months_to_reload). The problem I still have is that the tables I need to ingest I want them to be dynamic. For now, I have to define then in the dataflow one by one, I want a Foreach activity to iterate through my warehouse with tables list and ingest all the tables I have in the warehouse table.

      • Element115's avatar
        Element115
        Icon for Memorable Member rankMemorable Member
        2 years ago

        amaaiia How about this approach:

         

        CREATE OR ALTER VIEW vTABLE_NAMES 
AS 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
;

         

         

        If you run this SQL script from your SQL analytics endpoint, it will create a view in your LH. Then from you DF, all you have to do is create a query that uses this view as its source.  The view return a column of all the tables in the dbo schema of the LH as a table.  Now your M code can reference this table and Bob's your uncle.