update policy and streaming policy conflict

[gstafflannel]

My issue stems from the fact that an update policy cannot contain a join or lookup on a table with a streaming policy. I saw a post talking about a intermediary table . So I have tried to setup this work around, but continually get an error that the update policy cannot be implemented because the streaming policy on the b_stocks table.

Overview of tables and policies:

Table - load_stocks : has streaming policy inherited from database

Table - stock_sectors : dimension table with stock ticker & stock sector info

Table - b_stocks : Intermediary table that has update policy to load data from load_stocks table.

Table - s_stocks : receiving error when attempting to create update policy that references query to join or lookup (tried both methods) data between stock_sectors & b_stocks

Error Message:

Error during execution of a policy operation: Referencing additional tables from update policy is not allowed when the source table has a StreamingIngestion policy defined. Tip: discontinue using Streaming Ingestion on table 'b_Stocks': .alter table ['b_Stocks'] policy streamingingestion disable

Attempts to resolve:

• I have run the disable for the b_stocks table using the link referenced and still receive the error message.
• Deleting the policy from the table
• Deleting the streaming policy from the database and explicitly applying a streaming policy for the load_stocks table.

No matter the approach, the update policy errors if there is a join or lookup. Can you help me?

[v-venuppu]

Hi @gstafflannel ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rajendraongole1 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-kpoloju-msft]

Hi @gstafflannel,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[rajendraongole1 · Accepted solution]

@Hi @gstafflannel - Faced similar issue . 

Can you separate streaming ingestion and enrichment which is recommended.

Update policy validation is based on the source lineage, not just the target table.

Joins are allowed only when the source table of the update policy is not streaming-derived. By separating ingestion and enrichment, the join happens on a non-streaming table.

Eg.

load_stocks
Streaming ingestion enabled
Raw data only (no joins / lookups)
b_stocks_enriched
Newly created table
No streaming ingestion ever enabled
Update policy sourced only from load_stocks (single-table query)
s_stocks
Update policy joins b_stocks_enriched with stock_sectors

Hope this helps