Azure ADX ingest data across databases on the same cluster

Question

I want to find a way to ingest data from one database to another (same cluster), based on when the source table gets updated. We have an update query that is able to see across databases , however when I try to update the update policy with:

.alter table DataBase2.ChildTable policy update
'''
[
    {
        "IsEnabled": true,
        "Source": "DataBase1.SourceTable",
        "Query": "QueryFunction()"
    }
]
'''

This produces the following error:

 Error during execution of a policy operation: Source table does not exist for policy: 'IsEnabled = 'True', Source = 'DataBase1.SourceTable', Query = 'QueryFunction()', IsTransactional = 'False', PropagateIngestionProperties = 'False''

Anyone got any ideas?

-Thanks!

Answer 1

So we found a solution:

Create a source table that is ingestion with a 0 time retention policy The command:

.alter table SourceTableName policy retention 
'''
{
  "SoftDeletePeriod": "0", "Recoverability":"Disabled"
}
'''

This will make the table never commit any rows.

Next, for each of your update policies, you need to structure them as follows:

.alter table Childtable policy update @'[{"Source": "SourceTableName", "Query": "UpdateQuery()", "IsEnabled": "True", "IsTransactional": "True"}]' 

This will make the child tables pull the rows as and when they are ingested, but the rows will NEVER be committed to the source table. Think of it as a stepping stone. I recommend having another DB that also ingests from the same source as a backup, allowing you to do .appends as needed (if ingestion ever fails) From here, you can add RLS to the child tables as wanted!

Retention reference

RLS Reference