AZ-305 Design data storage solutions Practice Question
A company runs a data analytics application that stores large volumes of structured data in a relational format. The data is write-intensive and the application needs to scale horizontally for high throughput. The solution must support SQL queries, including joins and ACID transactions. Which Azure database service should they choose?
⚠ Common exam trap
A common mix-up: candidates confuse Azure SQL Database Hyperscale's 'scale-out' read replicas with true horizontal write scaling, or they assume Cosmos DB's SQL API supports relational queries and transactions, when in fact it is a NoSQL store with limited consistency and no JOIN support.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Azure Database for PostgreSQL - Hyperscale (Citus)
Azure Database for PostgreSQL - Hyperscale (Citus) is correct because it provides horizontal scaling (sharding) across multiple nodes while preserving full SQL support, including JOINs and ACID transactions. Citus distributes data across worker nodes using a coordinator node, enabling write-intensive workloads to achieve high throughput through parallelized writes. This makes it ideal for large-volume, relational, write-heavy analytics applications that require relational integrity.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Azure Database for PostgreSQL - Hyperscale (Citus)
Why this is correct
Azure Database for PostgreSQL - Hyperscale (Citus) extends PostgreSQL by sharding tables across multiple worker nodes using a coordinator node, allowing the cluster to handle both large analytical scans and high-throughput OLTP queries without abandoning relational semantics. It supports full SQL including multi-table joins and ACID transactions across distributed tables, making it the only listed option that combines horizontal write scalability with strong consistency and relational query power. This fits a data analytics application that needs to ingest and query data at scale while preserving transactional integrity.
- ✗
Azure SQL Database Hyperscale
Why it's wrong here
Azure SQL Database Hyperscale is a service tier for a single logical database, not a distributed database cluster. It uses a distributed storage architecture to grow a database up to 100 TB and quickly provision compute replicas, but these replicas are read-only and cannot scale the primary write path across independent nodes. Since all transactions still commit on a single node, it does not offer the horizontal sharding or multi-node write parallelism that the Citus-based PostgreSQL service provides, leaving it unsuitable for workloads that need to distribute writes across many physical partitions.
- ✗
Azure Cosmos DB (SQL API)
Why it's wrong here
Azure Cosmos DB (SQL API) is a globally distributed NoSQL database with a schema-agnostic item model, and its SQL-like query syntax cannot perform arbitrary multi-table joins or enforce server-side referential integrity. ACID transactions in Cosmos DB are limited to a single logical partition—transactional batch with stored procedures or the .NET SDK’s transactional batch—while cross-partition operations are only eventually consistent. A data analytics application requiring complex relational queries and reliable multi-row transactions would hit fundamental limitations, making this option incorrect.
- ✗
Azure Synapse Analytics
Why it's wrong here
Azure Synapse Analytics is a massively parallel processing (MPP) data warehouse built for large-scale analytical queries over historically stored data, not for serving as the operational database for an application that performs frequent row-level writes. It separates compute from storage and optimizes for columnar scans and complex aggregations, but it does not provide the low-latency, ACID-compliant, single-row transaction processing expected of an OLTP system. While it could be part of an analytics pipeline, it is not a substitute for a horizontal, relational operational store like Citus.
Go deeper
Related to this question
About these practice questions
One of 795 original AZ-305 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This AZ-305 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the AZ-305 exam.