Dataflow Challenges Adding Columns and Values to a Lakehouse

[ItGoat]

Hello everyone,

I'm currently trying to copy tables from an SQL Server to a Lakehouse. In general, this works well using pipelines and dataflows.

BUT now there are external requirements, and I'm struggling to reconcile them.

I need to add three columns to the tables.

• First, a CreatedOn column where the date and time of the copying process should be inserted.
• CreatedBy, which should be a string containing the ETL process, i.e., the name of the pipeline.
• Lastly, a self-generated key using the statement "Binary.ToText(Text.ToBinary(Text.Combine({Number.ToText([parentIdBk])})))" (don't ask... that's what I've been given).

I can create variables with the pipeline copying process to include CreatedOn and CreatedBy. However, the variables only output a string, and CreatedOn should be stored in timestamp format. I couldn't insert the key using variables.

Then I thought it might be easier through the Dataflow. I can manually insert columns for CreatedOn and the key fairly easily. However, I have no idea if there is a function for the CreatedBy column - perhaps I'm not knowledgeable enough in that area and looked in the wrong place. In any case, my idea was to create the two columns manually in the Dataflow and append CreatedBy as a variable in the pipeline. Set variable and append variable also go through, but unfortunately, no additional column is created in the table.

Does anyone have an idea of how this can work?

[frithjof_v]

In Dataflow Gen 2 you can use 'Add custom column' to add columns with the needed info. For the timestamp you can use some DateTime function (e.g. DateTimeZone.UtcNow()). For the CreatedBy I think you can hardcode that? Or do you need it to be dynamic? (If so - why? ☺️)

In pipeline perhaps you can use 'Additional columns' under the Advanced setting. There are some functions you can use, or you can hardcode the values.

Or you could generate the extra columns as part of the SQL query if you are using a written SQL query.

Anyway I think you need to create the columns in the Delta table before you can start writing values to these columns. If you plan to use this table in SQL Analytics Endpoint or semantic model, you will need to create a new table in order to change the number of columns in your table.

[v-nikhilan-msft]

Hi @ItGoat 

Thanks for using Fabric Community.

Apologies for the issue that you are facing.

This might require a deeper investigation from our engineering team about your workspace and the logic behind it to properly understand what might be happening. 

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.
Thanks.

[v-nikhilan-msft]

Hi @ItGoat 
We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket. If yes please share the details here. Otherwise, will respond back with the more details and we will try to help.
Thanks

[ItGoat]

Hi @v-nikhilan-msft

I´m unsure if opening a ticket with the support team is necessary and welcomed by the company I´m working at.

Is it even possible to create a new column in a dataflow using variables? My last approach would be to copy data via the pipeline activity and then try to create new columns via Notebooks. But I´d rather not because coding is not my best skill ^^'

Thanks.

[v-nikhilan-msft]

Hi @ItGoat 
Unfortunately, we cannot create new columns in Dataflow Gen2 using variables.