Serverless SQL Pool

← Back to DP-420 Terminology Reference

Category: Integration & Analytics

Summary: On-demand T-SQL query service in Azure Synapse to query Cosmos DB analytical store via OPENROWSET.

Overview

A serverless SQL pool is an on-demand, serverless data-processing service in Azure Synapse Analytics that enables data engineers and analysts to run Transact-SQL (T-SQL) queries directly over data in external stores without provisioning or managing reserved cluster capacity. In the DP-420 exam and Azure Cosmos DB scenarios, serverless SQL pools are used to query the Cosmos DB analytical store (enabled via Azure Synapse Link) in near real time using the OPENROWSET function with provider 'CosmosDB'. This enables hybrid transactional/analytical processing (HTAP) and BI reporting (e.g. creating SQL views for Power BI) without consuming transactional Request Units (RUs) or running heavy ETL jobs.

Example 1 (T-SQL): Query Cosmos DB analytical store using OPENROWSET with automatic schema inference

SELECT TOP 100 *
FROM OPENROWSET(
    'CosmosDB',
    'Account=mycosmosaccount;Database=ecommerce;Key=AccountKeyOrSecret',
    Orders
) AS [orders];

This query reads directly from the Orders container's analytical store without consuming operational RUs from the transactional store.

Example 2 (T-SQL): Query with explicit schema and credential using WITH clause

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM OPENROWSET(
    'CosmosDB',
    'Connection=https://mycosmosaccount.documents.azure.com;Database=ecommerce',
    Orders
)
WITH (
    OrderId VARCHAR(50) '$.id',
    CustomerId VARCHAR(50) '$.customerId',
    OrderDate DATETIME2 '$.orderDate',
    TotalAmount DECIMAL(18,2) '$.total'
) AS [orders];

Using the WITH clause explicitly defines column types and JSON property paths, optimizing query performance, reducing data processing charges, and enabling clean creation of relational views for Power BI reporting.

More Info & Links

• Microsoft Learn: Query Azure Cosmos DB data using a serverless SQL pool in Azure Synapse Link
• Microsoft Learn: How to use OPENROWSET in serverless SQL pool
• Microsoft Learn: Azure Synapse Link for Azure Cosmos DB

DP-420 Style Practice Questions

Q1. How does a serverless SQL pool access Azure Cosmos DB data in Azure Synapse Link?

• A. By executing T-SQL queries using the OPENROWSET function targeting the container's analytical store
• B. By sending SQL API queries through the Cosmos DB SDK Gateway connection mode
• C. By establishing a direct TCP socket connection to Cosmos DB physical partition replicas
• D. By triggering a Cosmos DB stored procedure to export data to CSV files

Q2. What is the RU impact on the Cosmos DB transactional store when running queries from a serverless SQL pool over the analytical store?

• A. Zero RUs consumed from the transactional store, because the analytical store is fully isolated from transactional throughput
• B. Equal RU cost to running the query via the Cosmos DB API for NoSQL
• C. It consumes half the RUs of a standard cross-partition transactional query
• D. It consumes RUs only if the query scans more than 20 GB of data

Q3. Why is specifying an explicit schema in the WITH clause of OPENROWSET recommended for serverless SQL pools querying Cosmos DB?

• A. It defines precise column types and paths, improving query performance and avoiding default wide types like VARCHAR(8000)
• B. It is mandatory because automatic schema inference is not supported for Cosmos DB
• C. It encrypts the results using Customer-Managed Keys (CMK) automatically
• D. It migrates the analytical store container directly into Delta format

Answer Key

Answers are listed at the very end so you can attempt the questions first.

• Q1 (Serverless SQL Pool): A. By executing T-SQL queries using the OPENROWSET function targeting the container's analytical store — Serverless SQL pools use the OPENROWSET function with provider 'CosmosDB' to query the analytical store directly.
• Q2 (Serverless SQL Pool): A. Zero RUs consumed from the transactional store, because the analytical store is fully isolated from transactional throughput — The analytical store has independent column-store storage and synchronization; querying it via serverless SQL pools has no RU impact on the transactional store.
• Q3 (Serverless SQL Pool): A. It defines precise column types and paths, improving query performance and avoiding default wide types like VARCHAR(8000) — The WITH clause provides explicit typing and JSON property mapping, which optimizes query execution and prevents inefficient fallback types like VARCHAR(8000).

---

DP-420: Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB — term detail page