How does Azure Data Explorer in Synapse Analytics work as regards Cosmos db

Question

Hi,

I am trying different ways to get Cosmos db data into Synapse Analytics in real-time (near real-time is good enough). I have seen mentioned that it is possible to use Azure Data Explorer. Does Data Explorer use the Analytical Store to get the latest data from Cosmos? If not, what is the cost impact using Data Explorer? Does it increase the amount of RUs? Also, it is possible to get the data from Data Explorer into a dedicated SQL pool?

All help would be greatly appreciated.

Regards,

Chris

Answer 1

Hi @Christopher Wilkinson
Welcome to Microsoft Q&A platform and thanks for posting your query here.

Thank you for your question! Let me clarify how Azure Data Explorer (ADX) in Synapse Analytics works with Cosmos DB and address your concerns:

Does Azure Data Explorer use the Analytical Store for Cosmos DB data?

Yes, Azure Data Explorer can integrate with Cosmos DB and typically leverages the Analytical Store for this purpose. The Analytical Store is optimized for real-time and near real-time analytics by providing a columnar format of your data. This approach reduces the load on the Cosmos DB transactional store and avoids consuming extra Request Units (RUs).

If the Analytical Store is enabled in your Cosmos DB container, ADX will use it to access the latest data efficiently.

Cost Impact and RU Consumption

• Using the Analytical Store: Queries made via ADX on the Analytical Store do not impact the RU consumption of your transactional store. This is cost-effective and ensures your operational workload isn't affected.
• Without the Analytical Store: If the Analytical Store is not enabled and ADX queries data directly from the transactional store, this will increase RU consumption and may lead to higher costs.

To minimize costs and maximize efficiency, it’s recommended to enable the Analytical Store for Cosmos DB containers you intend to analyze.

Can you move data from Azure Data Explorer to a Dedicated SQL Pool?

Yes, you can export data from Azure Data Explorer to a Dedicated SQL Pool in Synapse Analytics. Common approaches include:

• Using Azure Data Factory to create pipelines that transfer data.
• Leveraging ADX's export capabilities (e.g., KQL queries) to periodically write data to the SQL pool.

For your scenario:

1. Enable the Analytical Store in Cosmos DB for near real-time analytics.
2. Use Azure Data Explorer to query data efficiently without impacting RU usage.
3. If required, export data from ADX to a Dedicated SQL Pool for further processing or reporting.

Ref: https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-cosmos-db-connection?utm_source=chatgpt.com&tabs=portal

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

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.