Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
frithjof_v
Resident Rockstar
Resident Rockstar

SQL Analytics Endpoint - Table constraints disappear after running Dataflow Gen2

Hi,

 

I created a Lakehouse with 2 tables:

 

Fact table: RandomNumbers

Dimensions table: Dim_Date

 

I created a relationship between the tables in the SQL Analytics Endpoint

 

frithjof_v_2-1713645285080.png

frithjof_v_3-1713645298521.png

 

When I query the [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] view, I can see that these relationships have been registered as constraints on the tables in the SQL Analytics Endpoint.

 

frithjof_v_1-1713685977271.png

 

When I connect to the RandomNumbers table of the SQL Analytics Endpoint from Power Query in Power BI Desktop, I can see that there is an expandable column so I can easily expand columns from Dim_Date into the RandomNumbers query.

 

frithjof_v_0-1713685765937.png

 

 

 

I have a Dataflow Gen2 which, when it runs, does a Replace with Dynamic schema of the Dim_Date table.

 

After running the Dataflow Gen2, these are my observations:

 

  • The relationship still appears in the model pane of the SQL Analytics Endpoint ✔️
  • The relationship still works in the Default Direct Lake semantic model ✔️
  • The constraints are not found anymore when I query the [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] view 
  • The expandable column is not found anymore in Power Query in Power BI Desktop. An import mode semantic model I built which uses this expandable column, get failed refreshes because the column can't be found anymore.  

 

My questions:

- Is this the expected behavior?

- Shouldn't the table constraints and the model relationships in the SQL Analytics Endpoint be automatically synchronized?

 

 

Thank you! 😀

1 ACCEPTED SOLUTION
frithjof_v
Resident Rockstar
Resident Rockstar

Oh, I see... My bad. It's not regular Replace I am using. I am actually using Replace with Dynamic schema. I had forgotten that. (I now changed the info to Replace with Dynamic schema also in my previous comments)

I guess the Dynamic schema setting is causing the table to get dropped and recreated.


I still find it inconsistent that the constraints and model relationships are affected differently by this operation. 

 

However, for my use, the model relationships are more important than the table constraints. So I like that the model relationships are not being dropped at least.

 

I just need to beware not to use the expand column option in Power Query for my import mode semantic models, when working with lakehouse tables which are subject to Dynamic schema.
As the expand column option seems to be depending on the table constraints in the SQL Analytics Endpoint, and the table constraints don't seem to get recreated when the table gets dropped and recreated.

 

I would prefer that the table constraints also keep working after running a Dataflow Gen2 with Dynamic schema, similar like the model relationships. 

 

frithjof_v_0-1713706166102.png

 

View solution in original post

5 REPLIES 5
frithjof_v
Resident Rockstar
Resident Rockstar

Oh, I see... My bad. It's not regular Replace I am using. I am actually using Replace with Dynamic schema. I had forgotten that. (I now changed the info to Replace with Dynamic schema also in my previous comments)

I guess the Dynamic schema setting is causing the table to get dropped and recreated.


I still find it inconsistent that the constraints and model relationships are affected differently by this operation. 

 

However, for my use, the model relationships are more important than the table constraints. So I like that the model relationships are not being dropped at least.

 

I just need to beware not to use the expand column option in Power Query for my import mode semantic models, when working with lakehouse tables which are subject to Dynamic schema.
As the expand column option seems to be depending on the table constraints in the SQL Analytics Endpoint, and the table constraints don't seem to get recreated when the table gets dropped and recreated.

 

I would prefer that the table constraints also keep working after running a Dataflow Gen2 with Dynamic schema, similar like the model relationships. 

 

frithjof_v_0-1713706166102.png

 

Hi @frithjof_v 
Apologies for the issue you have been facing. Thanks for the feedback. I have forwarded the feedback to the internal team.
Hope this helps. Please let me know if you have any further queries. Glad to help.

frithjof_v
Resident Rockstar
Resident Rockstar

I am suspecting that the reason is that the Dataflow Gen2 drops and recreates the Lakehouse table when using Replace mode with Dynamic schema.

 

So maybe that's why the constraints are removed (or rather: not re-applied) from the tables in the SQL Analytics Endpoint?

 

However, I think it's not consistent with the model relationships still being present and working afterwards.

 

 

These are the destination settings in my Dataflow Gen2:

 

 

frithjof_v_0-1713705197318.png

 

AndyDDC
Memorable Member
Memorable Member

Just a quick one before I dive a little deeper, this is a Warehouse (and not a Lakehouse)?

It's a Lakehouse

Helpful resources

Announcements
Sept Fabric Carousel

Fabric Monthly Update - September 2024

Check out the September 2024 Fabric update to learn about new features.

Expanding the Data Factory Forums

New forum boards available in Data Factory

Ask questions in Apache Airflow Job and Mirroring.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors