Azure Synapse Link for Cosmos DB

Azure Synapse Link for Cosmos DB is a cloud-native HTAP (Hybrid Transactional/Analytical Processing) feature that seamlessly integrates Azure Cosmos DB (operational database) with Azure Synapse Analytics (analytics service). It allows you to run near-real-time analytics over your Cosmos DB data without affecting the transactional workload. The key innovation is that analytics queries are run directly against the Cosmos DB data store using the analytical store, not the transactional store. This eliminates the need for complex ETL pipelines and reduces data latency from hours to minutes.

Traditional architectures separate transactional and analytical systems. Data is extracted from the operational database (e.g., Cosmos DB), transformed, and loaded into a data warehouse (e.g., Azure Synapse dedicated SQL pool) in batch processes. This approach has several drawbacks: - Latency: Data can be hours or even days old in the analytics system. - Complexity: ETL pipelines require maintenance and can break. - Cost: Duplicate storage and compute resources are needed. - Impact: Running analytics directly on the transactional store can degrade OLTP performance.

Synapse Link solves these problems by providing a fully managed, real-time analytical store that is automatically synchronized with the transactional store. The analytical store is columnar-optimized for fast scan queries, while the transactional store remains row-oriented for low-latency reads and writes.

Azure Synapse Link leverages two key components: 1. Cosmos DB Analytical Store: A columnar representation of the data in Cosmos DB, automatically maintained by the system. When you enable Synapse Link on a Cosmos DB container, the analytical store is created and kept in sync with the transactional store using the Change Feed. 2. Azure Synapse Apache Spark Runtime: A serverless Spark pool in Azure Synapse that can read from the analytical store using a special connector.

The synchronization process works as follows:

To enable Synapse Link on an existing Cosmos DB account: 1. In the Azure portal, navigate to your Cosmos DB account. 2. Under "Features", enable "Azure Synapse Link". 3. Create a new container with analytical store enabled (or update an existing container using the EnableAnalyticalStorage property).

Using Azure CLI:

# Enable Synapse Link at account level
az cosmosdb update --name mycosmosdb --resource-group myrg --enable-analytical-storage true

# Create a container with analytical store enabled
az cosmosdb sql container create --account-name mycosmosdb --resource-group myrg --database-name mydb --name mycontainer --partition-key-path /id --analytical-storage-ttl -1

In Azure Synapse, you can then create a linked service to Cosmos DB and load data into a Spark DataFrame:

# Read from Cosmos DB analytical store using Spark
cosmos_df = spark.read.format("cosmos.olap").option("spark.synapse.linkedService", "CosmosDbLinkedService").option("spark.cosmos.container", "mycontainer").load()

- Latency: Typically under 2 minutes from write to analytical store availability. For updates, the entire document is replaced in the analytical store (no partial updates). - Storage Costs: Analytical storage costs are separate from transactional storage. They are billed per GB/month, typically lower than transactional storage. - Query Performance: Analytical queries on the columnar store are much faster than on the transactional store for aggregations and scans, but they are not as low-latency as transactional point reads. - Limitations: - Analytical store does not support spatial queries or user-defined functions (UDFs). - Only SQL and MongoDB APIs are supported (not Cassandra, Gremlin, or Table). - Analytical store is read-only from Synapse; writes must go to the transactional store.

A large online retailer uses Cosmos DB to store customer orders, inventory, and user sessions. The marketing team needs a real-time dashboard showing current sales by product category, region, and channel. Previously, they ran hourly ETL jobs to a dedicated SQL pool, causing a 60-minute delay. With Synapse Link, they enable analytical store on the orders container. In Azure Synapse, they create a Spark notebook that reads from the analytical store every minute, aggregates sales data, and writes results to a Power BI dataset. The dashboard now updates within 2 minutes of an order being placed. The key consideration is that the analytical store does not consume RUs, so the transactional workload remains unaffected. However, they had to ensure the analytical TTL was set to -1 to retain historical data. A misconfiguration could cause data to expire prematurely, leading to incomplete reports.

A manufacturing company collects sensor data from thousands of IoT devices into Cosmos DB using the MongoDB API. They need to run real-time anomaly detection on temperature and vibration metrics. Previously, they streamed data into Azure Stream Analytics, which added complexity and cost. With Synapse Link, they enable analytical store on the telemetry container. In Synapse, they use a serverless SQL pool to query the analytical store with T-SQL, joining with reference data stored in another container. They built a Power BI report that alerts when average temperature exceeds thresholds. The analytical store's columnar format makes aggregate queries (AVG, MAX, MIN) extremely fast. They discovered that the analytical store does not support MongoDB-specific queries like $lookup or $unwind, so they had to flatten the data model. Also, they set an analytical TTL of 7 days to limit storage costs, as old telemetry is moved to cold storage.