Best practice for creating combined Azure Cognitive Search index from multiple SQL tables with change detection

Question

I have the following tables in a Azure SQL Database:

Tags (tagId int PK, tagLabel nvarchar(100))
Entities (entityID int PK, ....)
EntityTags (entityId int FK, tagID int FK)

I need fast retrieval of Entities from a fast growing db based on tagLabel searches (fully or partially specified) and I thought instead of querying the db, I would use Azure Cognitive Search service to retrieve the data from an index containing entityId as key and a comma separated list of tagLabels. as another field.

I could create a SQL view as source for the index, but I learnt that choosing this option I will not have change detection.

Is there a way to update a view based index regularly (every ~10 mins) without recreating it or any other best practice to create the index for this scenario?

Answer 1

Short answer, no.

What you can do is write your own 'data change detector' that will only push data to Azure Cognitive Search index when needed. Also, use MergeOrUpload operation:

https://learn.microsoft.com/en-us/dotnet/api/azure.search.documents.models.indexdocumentsaction.mergeorupload?view=azure-dotnet#azure-search-documents-models-indexdocumentsaction-mergeorupload-1(-0)