topic Re: Data Flow Gen2: multiple query transformations fail in Data Engineering

Data Flow Gen2: multiple query transformations fail

elenat — Mon, 28 Aug 2023 18:12:34 GMT

I have a very simple dataflow gen2 reading from lakehouse files and creating Lakehouse tables out of them after some transformation.

First query (query1 in the picture) is navigating to the right location in the Lakehouse. Second query (query2) is extracting data from one file and making required transformations to save data as table (also named query2).

In the same flow you can also see a third query (query3) which is doing exactly what query 1 and 2 were doing but in a single query. In fact I created query3 by copy/pasting query1+query2 code in a single query. So query1 + query 2 == query3

When in the web UI, everything works: query2 preview result shown in picture. Not shown in picture but query3 result is also showing the data correctly in the preview, like query2.

When the flow is triggered and processed to generate the tables, the result of query1 + query2 produce a table with only one row, and content is only available in the first column. Query 3 instead works as expected and produces a table that matches the preview shown in the web ui.

Inspecting the content of the produced table, I realized that what query2 is producing is the content of the source file encoded in base64 and pushes it to the table:

if you are curious you can check for yourself (note that this is a truncated result, I only copy/pasted what is shown in the UI) TW92aWVBY3RvcklELE1vdmllSUQsQWN0b3JJRA0KNDREQTQzQTItMzIxMS00MDkwLUIyMUItMDAwREUzN0ZGM0UzLEM2MURFMUFELTQwNTEtNERBOS04Q0ZGLUM1NDVGOUI0OTYwRiw4NDk5RDI1Qy1GREZ

which, decoded from base64, gives you:

"MovieActorID,MovieID,ActorID
44DA43A2-3211-4090-B21B-000DE37FF3E3,C61DE1AD-4051-4DA9-8CFF-C545F9B4960F,8499D25C-FDF"

which is the (truncated) content of my file.

As I said query3 instead produces a table containing the content of the file as expected (3 columns, multiple rows)

Re: Data Flow Gen2: multiple query transformations fail

puneetvijwani — Tue, 29 Aug 2023 08:14:24 GMT

@elenat

1. Double-check the code for query1 and query2, ensure they are doing what you want , especially when run in sequence.

2 check if no type casting or encoding options are set differently for query1 + query2 compared to query3.

3. If query2 depends on some output of query1, ensure that this dependency is handled correctly.
4. Try running query1 and query2 individually in the flow to see if they each produce the expected output

Re: Data Flow Gen2: multiple query transformations fail

elenat — Fri, 01 Sep 2023 15:53:28 GMT

Hello @puneetvijwani and thanks for replying, but I am not sure I understand your suggestions.

As mentioned in my post, the code behind query 1 and query 2 combined is identical to the code of query3. And query 3 produces the expected result. But when the code from query 3 is split into 2 queries that are executed sequentially, then it does not work anymore. This definitely seems like a bug

Re: Data Flow Gen2: multiple query transformations fail

puneetvijwani — Fri, 01 Sep 2023 18:10:01 GMT

@elenat can you share the Query 1 and Query 2 and if possible the sample file from landing area
Would like to reproduce at my end to confirm if its behaving the same at my end as well

Re: Data Flow Gen2: multiple query transformations fail

elenat — Tue, 05 Sep 2023 12:27:12 GMT

@puneetvijwani here you go:

query3 definition:

let

Source = Lakehouse.Contents(null){[workspaceId = "WORKSPACEID"]}[Data]{[lakehouseId = "LAKEHOUSEID"]}[Data],

#"Navigation 1" = Source{[Id = "Files", ItemKind = "Folder"]}[Data],

#"Navigation 2" = #"Navigation 1"{[Name = "bronze"]}[Content],

#"Navigation 3" = #"Navigation 2"{[Name = "fourthcoffee"]}[Content],

#"Removed columns" = Table.RemoveColumns(#"Navigation 3", {"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),

Navigation = #"Removed columns"{[Name = "MovieActors.csv"]}[Content],

#"Imported CSV" = Csv.Document(Navigation, [Delimiter = ",", Columns = 3, QuoteStyle = QuoteStyle.None])

#"Imported CSV"

query1 definition:

let

Source = Lakehouse.Contents(null){[workspaceId = "WORKSPACEID"]}[Data]{[lakehouseId = "LAKEHOUSEID"]}[Data],

#"Navigation 1" = Source{[Id = "Files", ItemKind = "Folder"]}[Data],

#"Navigation 2" = #"Navigation 1"{[Name = "bronze"]}[Content],

#"Navigation 3" = #"Navigation 2"{[Name = "fourthcoffee"]}[Content],

#"Removed columns" = Table.RemoveColumns(#"Navigation 3", {"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})

#"Removed columns"

query2 definition:

let

Navigation = query1{[Name = "MovieActors.csv"]}[Content],

#"Imported CSV" = Csv.Document(Navigation, [Delimiter = ",", Columns = 3, QuoteStyle = QuoteStyle.None])

#"Imported CSV"

As per the data, I cannot seem to be able to attach a file to this message, but I think you should be able to reproduce it easily if you create any CSV file with 3 columns. In my case you see in my first message the name of the columns and the columns just contain guids. My file currently contains roughly 2000 rows