Cosmos DB transactional store count mismatch with analytical store

Question

Hi,

In my cosmos container analytical store enabled with TTL off, also TTL is off for my cosmos container. I created Synapse linked service but while running queries I am getting different counts.

Cosmos container has less records where as my analytical store showing more than 2 times of cosmos container record count.

I believe there is no way we can delete records from analytical store to refresh and reload data from cosmos container. What are other scenarios which may lead to this count mismatch issue?

Thanks

Answer 1

@Vikas Tiwari - Thanks for the question and using MS Q&A platform.

There are several scenarios that can lead to a count mismatch between the transactional store and the analytical store in Cosmos DB. Here are a few possible reasons:

Data ingestion lag: There may be a delay between when data is ingested into the transactional store and when it is available in the analytical store. This can result in a count mismatch if you are querying the analytical store before all the data has been ingested.

Query consistency level: The consistency level of your query can affect the count results. If you are using a strong consistency level, the count results should be consistent between the transactional and analytical stores. However, if you are using a weaker consistency level, there may be some lag between the two stores, resulting in a count mismatch.

Query filters: If you are using filters in your query, make sure that they are consistent between the transactional and analytical stores. If the filters are different, you may get different count results.

Data updates: If data is updated or deleted in the transactional store after it has been ingested into the analytical store, this can result in a count mismatch.

Data partitioning: If your data is partitioned across multiple partitions, it is possible that the count results may be inconsistent between the transactional and analytical stores.

To troubleshoot the count mismatch issue, you can try the following steps:

1. Check the consistency level of your query and make sure it is consistent between the transactional and analytical stores.
2. Check the filters in your query and make sure they are consistent between the transactional and analytical stores.
3. Check the data ingestion lag and make sure that all the data has been ingested into the analytical store before running your query.
4. Check for any data updates or deletions in the transactional store that may have affected the count results.
5. Check the data partitioning and make sure that the count results are consistent across all partitions.

If none of these steps resolve the count mismatch issue, you may need to open a support ticket for further assistance.

Hope this helps. Do let us know if you any further queries.