Forum Discussion
Dataflow gen2 using variables from Variable Libraries Failing
I have a dataflow gen2 in a fabric workspace. I'm updating our workspace so it can be used with deployment pipelines. For this I'm parameterizing navigation queries and destination M code to read lakehouse and workspace ids from a variable library. I pull the variables into the dataflow using a seperate query per variable and I use the function Variable.ValueOrDefault().
Using this method I pull the lakehouse and the workspace id. I use these ids to both pull in tables from the lakehouse into the query, in addition to using them in the destination query for the final output of the gen2.
In the preview the gen2 works fine and I am able to save it. When I run it I have encountered two different error messages:
Unable to create a table on the Lakehouse SQL catalog due to metadata refresh failure, for Lakehouse Id: a underlying error Unable to create a table.
I took over ownership of the lakehouse like the message reccomends but that hasn't solved the issue.
I have also encountered this error:
There was a problem refreshing the dataflow: "Source query: "sl_odoo_gl_transactions", Message: Expression.Error: The key didn't match any rows in the table.."
In the above query sl_odoo_gl_transactions is the name of both the query of the final output table and the name of the table it is writing to in the lakehouse.
Thank you for any assistance with this matter.
Hello TLibson,
I will check the variable queries first. For those queries, are staging and output disabled?One thing worth checking is staging on the variable queries. Microsoft suggests turning this setting off when you’re using Variable.ValueOrDefault().
Another choice is to compare the Workspace and Lakehouse IDs in your variables with the ones returned by Lakehouse.Contents(). If they are not matching, then that mismatch might be the reason you are getting the key error.
Check the GUID casing too.
If those match, I will then look at the destination query and table navigation.
Microsoft docs: Variable references in a Dataflow
2 Replies
I turned off staging on my variable queries and that cleared up the errors with loading the variables.
I realized I was also having some mismatch issues with fixed schema tables. I took over ownership on some previously written dataflows and I believe the data destination was configured to an existing table instead of a new table with a dynamic schema. I just needed to delete the table in the lakehouse, and then set the data destination to create a new table.
I would say that as of now my issue is fixed.
Thank you for your help
Hello TLibson,
I will check the variable queries first. For those queries, are staging and output disabled?One thing worth checking is staging on the variable queries. Microsoft suggests turning this setting off when you’re using Variable.ValueOrDefault().
Another choice is to compare the Workspace and Lakehouse IDs in your variables with the ones returned by Lakehouse.Contents(). If they are not matching, then that mismatch might be the reason you are getting the key error.
Check the GUID casing too.
If those match, I will then look at the destination query and table navigation.
Microsoft docs: Variable references in a Dataflow
