Forum Discussion

gstafflannel's avatar
gstafflannel
New Member
10 months ago
Solved

update policy and streaming policy conflict

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?

 

eventhouse
  • rajendraongole1's avatar
    rajendraongole1
    9 months ago

    @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

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    9 months ago

    @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

  • v-venuppu's avatar
    v-venuppu
    Icon for Community Support rankCommunity Support
    9 months ago

    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's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity Support
      9 months ago

      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.