Courseiva

CCNA Design and implement data storage Questions

75 of 121 questions · Page 1/2 · Design and implement data storage · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQeasy

You need to store structured data in Azure. The data will be accessed by multiple applications using T-SQL queries, and you require automatic indexing and serverless compute. Which Azure service should you use?

A.Azure Cosmos DB with Core (SQL) API.
B.Azure Data Lake Storage Gen2 with hierarchical namespace.
C.Azure Blob Storage with a flat namespace.
D.Azure SQL Database with serverless compute tier.
AnswerD

Azure SQL Database is a fully managed relational database service that supports T-SQL queries, automatic indexing, and a serverless compute tier that automatically scales compute based on workload. It provides built-in high availability and security. This service directly meets the requirements for structured data, T-SQL access, and serverless compute with automatic indexing.

Why this answer

Azure SQL Database is a relational database service that natively supports T-SQL, offers automatic indexing through features like automatic tuning, and provides a serverless compute tier that scales automatically. It is the appropriate choice for structured data accessed via T-SQL with serverless compute requirements. Other options lack T-SQL support or are not relational databases.

Exam trap

The trap here is assuming that any Azure storage or database service supports T-SQL; only relational services like Azure SQL Database do.

Practice this question →
2
MCQhard

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

A.Use hash-distributed tables for all tables and clustered columnstore indexes.
B.Use replicated tables for all fact tables and hash-distributed tables for dimension tables.
C.Use round-robin tables for all tables to simplify the migration.
D.Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.
AnswerD

Round-robin distribution suits staging tables by balancing loads evenly without a join key, while hash distribution on a high-cardinality fact column co-locates related rows, reducing data movement during joins and aggregations in the dedicated SQL pool.

Why this answer

It uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often assume hash-distributed tables are always the best choice for all tables, overlooking the fact that round-robin tables reduce data movement during migration and that hash distribution should be reserved for large fact tables to avoid skew and unnecessary shuffling.

How to eliminate wrong answers

Option A is wrong because using hash-distributed tables for all tables, including small dimension tables, can cause unnecessary data shuffling and skew, and clustered columnstore indexes are not optimal for tables with frequent updates due to high overhead in maintaining columnstore segments. Option B is wrong because replicated tables are designed for small dimension tables (typically < 2 GB), not for large fact tables with up to 500 million rows, as replicating such large tables would cause excessive storage and data movement. Option C is wrong because round-robin tables distribute data randomly across distributions, leading to poor query performance due to data movement during joins and aggregations, and are not suitable for production fact tables in a dedicated SQL pool.

Practice this question →
3
MCQmedium

A data engineering team is designing a storage solution for a retail company that receives point-of-sale (POS) transaction data from thousands of stores. The data arrives as JSON files in Azure Data Lake Storage Gen2. The team needs to query the data using Azure Synapse Analytics serverless SQL pool and optimize for performance and cost. The data is partitioned by year, month, and day. They want to minimize the amount of data scanned per query. What should they do?

A.Use Azure Data Explorer to ingest the JSON data and query it with KQL.
B.Convert the JSON files to Parquet format and use OPENROWSET with explicit file paths for each partition.
C.Keep the JSON files and use OPENROWSET with wildcards to read all files, then filter by date in the query.
D.Convert the JSON files to Parquet format and create an external table with partition columns (year, month, day) and use partition elimination in queries.
AnswerD

Converting to Parquet reduces storage size and improves query performance due to columnar storage and compression. Creating an external table with partition columns allows the serverless SQL pool to perform partition elimination, scanning only the relevant partitions based on query filters. This minimizes data scanned and reduces cost, meeting the requirements.

Why this answer

Converting JSON to Parquet and creating an external table with partition columns enables partition elimination in Synapse serverless SQL pool. This means queries that filter on year, month, or day will only scan the relevant partitions, significantly reducing the amount of data processed and lowering costs. Parquet's columnar format also improves performance by allowing column pruning.

Exam trap

The trap here is assuming that simply storing data in partitions is enough, but without using an external table with partition columns, the serverless SQL pool cannot eliminate partitions and will scan all data.

Practice this question →
4
MCQeasy

A financial services company needs to store transactional data in Azure Cosmos DB. The data is accessed by multiple applications using different partition keys. The company requires strong consistency for financial transactions and wants to minimize latency for reads and writes. Which consistency level should they choose?

A.Bounded staleness
B.Strong
C.Eventual
D.Session
AnswerB

Strong consistency guarantees linearizability, ensuring reads always return the most recent committed write. This is critical for financial transactions where data accuracy is paramount. While it may increase latency compared to weaker levels, it meets the requirement for strong consistency and is suitable for scenarios where correctness outweighs latency.

Why this answer

Strong consistency in Azure Cosmos DB ensures that all reads see the most recent committed write, which is essential for financial transactions. Although it may introduce higher latency compared to weaker consistency levels, it provides the required correctness. The scenario prioritizes strong consistency over minimal latency, making Strong the appropriate choice.

Exam trap

The trap here is assuming that lower latency always outweighs consistency, leading to the selection of a weaker consistency level that fails the financial accuracy requirement.

Practice this question →
5
MCQhard

You are designing a storage layer for a fraud detection system. The system writes millions of small JSON records per hour to Azure Data Lake Storage Gen2 and must support both batch analytics and interactive queries from Azure Databricks. You need to choose a storage format and layout that minimizes query latency for selective filters on customer ID while keeping storage costs predictable. What should you do?

A.Store the data as Delta Lake tables with Z-ORDER clustering on customer ID and optimize file sizes.
B.Store the data as Parquet files partitioned by customer ID and use partition pruning in Databricks.
C.Store the data as Avro files and use schema evolution to handle new fields.
D.Store the data as uncompressed JSON files and rely on Databricks caching to improve performance.
AnswerA

Delta Lake provides ACID transactions and metadata that Databricks can use for data skipping. Z-ORDER clustering colocates related customer ID values within files, so selective filters skip irrelevant data efficiently. Combined with OPTIMIZE to compact small files, this reduces latency and keeps storage costs predictable through compaction and retention policies, matching the scenario's needs.

Why this answer

Delta Lake with Z-ORDER clustering on customer ID enables data skipping by storing min/max statistics per file and colocating similar values. This directly reduces the data read for selective filters. OPTIMIZE compacts small files, which is critical when millions of small records arrive hourly, improving both batch and interactive query performance while keeping storage costs controlled.

Exam trap

The trap here is choosing Parquet with a high-cardinality partition column, which creates too many small partitions instead of using clustering within files.

Practice this question →
6
MCQmedium

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source has a fact table with 500 million rows and several dimension tables. You need to choose the best distribution strategy for the fact table to minimize data movement during joins. Which distribution type should you use?

A.Hash distribution on the foreign key column used in joins
B.No distribution (single distribution)
C.Replicated distribution
D.Round-robin distribution
AnswerA

Hash distribution on the join key colocates matching rows on the same compute node, so joins between the fact table and dimensions occur locally rather than shuffling 500 million rows across nodes. This directly satisfies the requirement to minimise data movement during joins.

Why this answer

Hash distribution on the foreign key column used in joins ensures that rows with the same join key are co-located on the same distribution node. This minimizes data movement because the join can be performed locally on each node without shuffling data across the compute nodes, which is critical for a 500-million-row fact table.

Exam trap

The trap here is that candidates often confuse replicated distribution as a general performance booster, but they fail to recognize that replicating a large fact table is impractical and that hash distribution on the join key is the correct strategy to minimize data movement for large fact tables.

How to eliminate wrong answers

Option B is wrong because single distribution (no distribution) places all data on one node, causing a bottleneck and eliminating the parallelism benefits of Azure Synapse Analytics, leading to poor performance for large fact tables. Option C is wrong because replicated distribution copies the entire table to each node, which is impractical for a 500-million-row fact table due to excessive storage and maintenance overhead; it is suitable only for smaller dimension tables. Option D is wrong because round-robin distribution distributes rows evenly without considering join keys, so joins require data to be shuffled across nodes, causing significant data movement and slower query performance.

Practice this question →
7
Multi-Selecthard

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Select 3 answers
A.Avoid using clustered columnstore indexes on large tables.
B.Avoid data skew by choosing a good distribution key.
C.Use round-robin distribution for all large fact tables.
D.Use replicated tables for small dimension tables (less than 1 GB).
E.Use hash distribution on a column with high cardinality for large fact tables.
AnswersB, D, E

A well-chosen hash distribution key spreads rows evenly across the sixty distributions, preventing one distribution from bearing disproportionate rows. Skew forces serialised processing, so even distribution directly supports the parallel-query design of dedicated SQL pools.

Why this answer

Option B is correct because choosing a good distribution key prevents data skew, which otherwise causes some distributions to hold disproportionate rows and forces the query engine to move data across nodes, degrading performance in a dedicated SQL pool. Option D is correct because replicated tables copy the full table to every compute node, eliminating data movement for joins; this is recommended for small dimension tables under roughly 1 GB (or up to 2 GB compressed). Option E is correct because hash distribution on a high-cardinality column spreads rows evenly across the 60 distributions, which is ideal for large fact tables and minimizes skew.

Option A is wrong because clustered columnstore indexes are the default and preferred storage for large tables in dedicated SQL pools, delivering high compression and fast analytical scans. Option C is wrong because round-robin distribution is a fallback for staging or tables with no clear join key, not a blanket best practice for all large fact tables, since it can cause costly data movement during joins.

Exam trap

The trap here is that candidates often assume clustered columnstore indexes are unsuitable for large tables due to memory constraints, but they are actually the default and recommended index type for fact tables in Synapse dedicated SQL pools.

Practice this question →
8
MCQmedium

A data engineer needs to store JSON documents that are frequently updated by multiple users concurrently. The solution must support optimistic concurrency control and have built-in indexing on all fields. Which Azure data store should be used?

A.Azure Cosmos DB (SQL API)
B.Azure Blob Storage
C.Azure SQL Database
D.Azure Table Storage
AnswerA

Azure Cosmos DB (SQL API) natively supports optimistic concurrency control through ETag-based conditional writes, satisfying the concurrent multi-user update requirement. Its automatic indexing policy indexes every property by default, meeting the built-in indexing constraint without manual configuration. JSON documents are stored natively, so no schema translation is needed.

Why this answer

Azure Cosmos DB (SQL API) is the correct choice because it natively supports optimistic concurrency control via ETags (HTTP entity tags) and provides automatic indexing of all fields without requiring manual index management. This makes it ideal for storing JSON documents that are frequently updated by multiple concurrent users, as it ensures conflict detection and resolution while maintaining high performance.

Exam trap

The trap here is that candidates often choose Azure SQL Database because they associate concurrency control with relational databases, overlooking that Cosmos DB is purpose-built for JSON documents with automatic indexing and native optimistic concurrency via ETags, which is more aligned with the requirements than a relational store.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it does not support optimistic concurrency control; it uses lease-based locking for blobs, which is not designed for fine-grained concurrent updates on JSON documents and lacks built-in indexing on all fields. Option C (Azure SQL Database) is wrong because while it supports optimistic concurrency via snapshot isolation or row versioning, it requires manual index creation and is not optimized for storing and querying JSON documents natively; it is a relational store, not a document store. Option D (Azure Table Storage) is wrong because it does not support optimistic concurrency control (it uses ETags but only for individual entities, not for complex JSON documents) and its indexing is limited to partition and row keys, not all fields.

Practice this question →
9
MCQeasy

You are designing a data storage solution for a marketing analytics platform. The platform collects clickstream data from websites and needs to store it for both real-time dashboards and historical analysis. The data is semi-structured (JSON) and arrives at a rate of 10,000 events per second. You need to choose an Azure storage solution that can handle the ingestion rate, support schema-on-read, and integrate with Azure Databricks for advanced analytics. The solution must also be cost-effective for long-term storage. What should you use?

A.Azure Table Storage
B.Azure Data Lake Storage Gen2
C.Azure Cosmos DB
D.Azure SQL Database
AnswerB

Data Lake Storage Gen2 ingests high-volume JSON streams, applies schema-on-read, integrates natively with Azure Databricks, and its hierarchical namespace plus lifecycle tiering keep long-term storage cost-effective, meeting the 10,000 events-per-second rate and historical analysis needs.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, providing high-throughput ingestion (up to 60 GB/s per account) to handle 10,000 events per second of semi-structured JSON data. It supports schema-on-read natively, allowing Azure Databricks to query the data directly using Spark without prior schema definition, and its tiered storage (hot, cool, archive) makes it cost-effective for long-term historical analysis.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its real-time capabilities, overlooking that the question emphasizes cost-effective long-term storage and schema-on-read for historical analysis, which ADLS Gen2 handles far more efficiently and cheaply than Cosmos DB's per-request-unit pricing model.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for structured data with a fixed schema, not for semi-structured JSON clickstream data, and it lacks the hierarchical namespace and high-throughput ingestion needed for 10,000 events per second. Option C is wrong because Azure Cosmos DB is optimized for low-latency real-time access with its multi-model API, but it is significantly more expensive for long-term storage of high-volume historical data and does not natively integrate with Azure Databricks for schema-on-read analytics as efficiently as ADLS Gen2. Option D is wrong because Azure SQL Database is a relational database requiring a predefined schema (schema-on-write), which conflicts with the schema-on-read requirement, and its ingestion rate and cost model are not designed for high-velocity semi-structured data at 10,000 events per second.

Practice this question →
10
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that some queries are slow due to high data movement. What should you do to minimize data movement for queries that join large fact tables?

A.Use round-robin distribution for all tables.
B.Partition both tables on the join keys.
C.Hash-distribute the fact tables on the join keys.
D.Use replicated tables for all large fact tables.
AnswerC

Hash-distributing both fact tables on their join keys colocates matching rows on the same distribution, so the join executes locally instead of shuffling large datasets between nodes. This directly reduces the high data movement causing slow queries in the dedicated SQL pool.

Why this answer

Hash-distributing the fact tables on the join keys ensures that rows with the same join key value are placed on the same distribution node. This eliminates the need to shuffle data across nodes during the join, minimizing data movement and improving query performance in Azure Synapse dedicated SQL pool.

Exam trap

The trap here is that candidates confuse partitioning with distribution, thinking that partitioning on join keys reduces data movement, when in fact only hash distribution on the join key ensures collocation across nodes.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering join keys, which does not reduce data movement for joins and can actually increase it. Option B is wrong because partitioning on join keys organizes data within a distribution but does not control data placement across distributions; data movement still occurs when joining across partitions. Option D is wrong because replicated tables are suitable for small dimension tables, not large fact tables, as replicating large tables would consume excessive storage and negate the benefits of scale-out.

Practice this question →
11
MCQmedium

You are designing a storage solution for a healthcare analytics platform. The platform ingests large volumes of structured patient records stored as Parquet files in Azure Data Lake Storage Gen2. Analysts query this data using Azure Synapse Analytics serverless SQL pools. To minimize query cost and improve performance, you need to choose an appropriate file organization and table type. What should you do?

A.Store data as CSV files partitioned by date, and query them using an external table in a serverless SQL pool.
B.Store data as JSON files and query them using OPENROWSET in a serverless SQL pool.
C.Store data as Parquet files in a dedicated SQL pool table with hash distribution on patient ID.
D.Store data as Parquet files partitioned by date, and query them using an external table in a serverless SQL pool.
AnswerD

Parquet is a columnar format that reduces I/O and cost in serverless SQL pools. Partitioning by date enables partition elimination, further reducing data scanned. External tables allow querying data in place without loading, which is ideal for ad-hoc analytics and cost control. This combination directly addresses the requirement to minimize cost and improve performance.

Why this answer

Storing data in Parquet format with date-based partitioning and querying via external tables in a serverless SQL pool minimizes data scanned and cost, while improving query performance. Parquet's columnar nature and compression reduce I/O, and partitioning enables partition elimination. This aligns with best practices for cost-effective analytics on large datasets in Azure Synapse Analytics.

Exam trap

The trap here is assuming that any file format works equally well with serverless SQL pools, ignoring the cost and performance benefits of columnar storage and partitioning.

Practice this question →
12
Multi-Selectmedium

You are designing a data storage solution for a media company that ingests video files from various sources into Azure Data Lake Storage Gen2. The files are uploaded continuously and must be processed by Azure Databricks. You need to ensure that the data is organized efficiently for query performance and that access is secure. Which two actions should you include in your design? (Choose two.)

Select 2 answers
A.Configure Azure Private Endpoints for the storage account to restrict access to the virtual network.
B.Use Azure Blob Storage with a flat namespace to simplify file management.
C.Store all video files in a single container without subfolders to reduce complexity.
D.Enable Azure Defender for Storage to protect against malicious file uploads.
E.Partition the data by upload date in a hierarchical folder structure (e.g., /year/month/day).
AnswersA, E

Private Endpoints ensure that access to the storage account is restricted to the virtual network, enhancing security by preventing public internet access. This meets the requirement for secure access and is a recommended practice when processing sensitive or large-scale data with services like Azure Databricks.

Why this answer

Partitioning data by upload date in a hierarchical folder structure enables efficient querying through partition pruning, which is essential for large-scale video processing in Databricks. Configuring Azure Private Endpoints ensures that access to the storage account is secure and restricted to the virtual network, meeting the security requirement. Together, these actions optimize performance and security.

Exam trap

The trap here is focusing on security features like Azure Defender that do not address data organization for query performance, while overlooking the need for hierarchical partitioning and private network access.

Practice this question →
13
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?

A.The table is not partitioned by OrderDate
B.Statistics on the join columns are outdated
C.The table should use round-robin distribution instead
D.The join columns are not aligned; data must be shuffled across distributions
AnswerD

Hash distribution places rows by CustomerID, so joining on OrderDate requires moving rows between distributions before the join completes. This shuffle, caused by misaligned join columns, is the most likely source of the slow query performance.

Why this answer

D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning on the join column solves the data movement issue, when in fact distribution alignment is the critical factor for collocated joins in a distributed MPP system.

How to eliminate wrong answers

Option A is wrong because partitioning by OrderDate would help with partition elimination for scans or maintenance, but it does not address the fundamental issue of data movement required when join columns are not aligned with the distribution key. Option B is wrong because outdated statistics can cause suboptimal query plans, but the primary performance bottleneck here is the physical data movement across distributions, not statistics. Option C is wrong because round-robin distribution distributes rows evenly without any key, which would still require full data movement for any join, making performance even worse than hash distribution on a non-join column.

Practice this question →
14
MCQhard

You are a data engineer for a multinational e-commerce company. The company uses Azure Synapse Analytics as its data warehouse. The current fact table, SalesFact, is distributed using hash distribution on the CustomerID column. It has 2 billion rows and is 2 TB in size. Recently, the business team has been running many queries that aggregate sales by product category and date, and these queries are experiencing high data movement and long execution times. The product dimension table (ProductDim) has 100,000 rows and is 100 MB. The date dimension table (DateDim) has 5,000 rows and is 5 MB. You need to redesign the storage to minimize data movement for these aggregation queries. You cannot change the fact table distribution key to ProductID because of other critical queries that rely on CustomerID. What should you do?

A.Create materialized views on the fact table that aggregate by product category and date
B.Replicate the ProductDim and DateDim tables to all compute nodes
C.Partition the fact table by date and keep the same distribution
D.Change the fact table distribution to round-robin and create non-clustered indexes on ProductID and DateID
AnswerB

Replicating the small ProductDim and DateDim tables places a full copy on every compute node, eliminating data movement during joins with the hash-distributed fact table. Aggregations by product category and date then execute locally, cutting shuffle overhead while CustomerID distribution stays unchanged.

Why this answer

Replicating small dimension tables (ProductDim at 100 MB and DateDim at 5 MB) to all compute nodes eliminates the need to shuffle these tables across nodes during joins. In Azure Synapse Analytics, replicated tables are copied to each distribution, so when the fact table (hash-distributed on CustomerID) joins with ProductDim and DateDim on ProductID and DateID, no data movement occurs for the dimension tables. This directly reduces the high data movement and long execution times for aggregation queries by product category and date.

Exam trap

The trap here is that candidates often choose materialized views (Option A) thinking they solve all aggregation performance issues, but they overlook that data movement from joins with non-replicated dimension tables remains the bottleneck, whereas table replication directly addresses the shuffle cost for small dimension tables.

How to eliminate wrong answers

Option A is wrong because materialized views in Azure Synapse Analytics pre-aggregate data but still require the underlying fact table's distribution; they do not eliminate data movement when joining with non-replicated dimension tables, and the queries would still suffer from shuffling ProductDim and DateDim. Option C is wrong because partitioning the fact table by date improves partition elimination for date-range filters but does not reduce data movement during joins; the hash distribution on CustomerID remains, so joins on ProductID and DateID still require redistributing the fact table or dimension tables. Option D is wrong because changing to round-robin distribution would distribute fact table rows randomly, causing even more data movement for all joins and aggregations, and non-clustered indexes do not address the fundamental distribution issue for large-scale aggregation queries.

Practice this question →
15
MCQeasy

A data engineer needs to store semi-structured JSON log files from a web application. Each log entry is about 1 KB. The logs are rarely queried (once a month) and must be retained for 7 years for compliance. The solution must minimize storage cost. Which storage option should be used?

A.Store the logs in Azure SQL Database as a table.
B.Store the logs in Azure Files share.
C.Store the logs in Azure Blob Storage with cool access tier.
D.Store the logs in Azure Cosmos DB with a JSON container.
AnswerC

Blob Storage cool tier is low-cost for infrequent access, suitable for logs.

Why this answer

Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB (D) because it natively supports JSON, but they overlook the extreme cost of storing and rarely querying 7 years of data in a globally distributed, high-throughput NoSQL database, which is optimized for frequent, low-latency access, not archival.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database designed for structured, transactional workloads with frequent queries, and it incurs high storage and compute costs for 7 years of 1 KB log entries, making it far more expensive than blob storage for rarely accessed data. Option B is wrong because Azure Files provides SMB/NFS file shares primarily for shared file access in VMs or on-premises apps, not for cost-effective, long-term archival of semi-structured logs, and it lacks the tiered pricing and lifecycle management of blob storage. Option D is wrong because Azure Cosmos DB is a NoSQL database optimized for low-latency, globally distributed, and frequently queried data; storing 7 years of rarely accessed logs in Cosmos DB would incur prohibitive costs due to its per-request unit (RU) pricing and storage charges, far exceeding blob storage costs.

Practice this question →
16
MCQmedium

You are a data engineer for a financial services company. The company uses Azure Data Lake Storage Gen2 as its data lake. You have a directory structure where each customer has a folder containing transaction files in CSV format. The security team requires that each customer's data be accessible only to that customer's users. You need to implement fine-grained access control using Azure Data Lake Storage Gen2's POSIX-like ACLs. However, you have thousands of customers, and managing ACLs individually is not feasible. What should you do?

A.Create a shared access signature (SAS) token for each customer and distribute it securely
B.Use POSIX ACLs on each customer folder, assigning permissions to individual user identities
C.Use row-level security in Azure Data Lake Storage Gen2
D.Create an Azure AD group for each customer, add users to the group, and assign ACLs to the group on the customer folder
AnswerD

Assigning POSIX ACLs to a Microsoft Entra ID group per customer folder satisfies the fine-grained, per-customer isolation requirement without editing thousands of individual user entries. Group membership changes propagate automatically, so onboarding or removing a customer's users needs no ACL rewrite, keeping administration feasible at scale.

Why this answer

Azure Data Lake Storage Gen2 supports POSIX-like ACLs that can be assigned to Azure AD security groups. By creating one Azure AD group per customer, adding the customer's users to that group, and then assigning the group the appropriate read/execute ACLs on the customer's folder, you achieve scalable, fine-grained access control without managing thousands of individual user ACLs. This approach aligns with the principle of least privilege and simplifies administration.

Exam trap

The trap here is that candidates often confuse row-level security (a SQL-based feature) with file-system access control in Azure Data Lake Storage Gen2, or they mistakenly believe that SAS tokens can provide granular directory-level isolation, when in fact SAS tokens operate at the container or storage account level and cannot enforce per-folder ACLs.

How to eliminate wrong answers

Option A is wrong because shared access signature (SAS) tokens provide delegated access at the storage account or container level, not at the directory or file level, and managing thousands of SAS tokens securely is operationally complex and does not integrate with Azure AD identity-based access control. Option B is wrong because assigning POSIX ACLs to individual user identities for thousands of customers is not feasible due to the Azure AD limit of 32 ACL entries per file or directory and the administrative overhead of managing individual user permissions at scale. Option C is wrong because row-level security is a feature of Azure SQL Database and Azure Synapse Analytics dedicated SQL pools, not of Azure Data Lake Storage Gen2, which uses POSIX ACLs and RBAC for access control.

Practice this question →
17
Multi-Selectmedium

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Select 3 answers
A.Enable TLS 1.0 for compatibility with legacy clients
B.Enable Azure Storage Service Encryption (SSE) for data at rest
C.Configure firewall rules to allow only trusted IPs
D.Use Azure RBAC and ACLs to control access to data
E.Require HTTPS for all data transfers
AnswersB, D, E

Azure Storage Service Encryption automatically encrypts data at rest using 256-bit AES, covering the stem's at-rest security requirement for Azure Data Lake Storage Gen2. It applies transparently to blobs and files with Microsoft-managed or customer-managed keys, requiring no application changes.

Why this answer

Option B is correct because Azure Storage Service Encryption (SSE) automatically encrypts data at rest in Azure Data Lake Storage Gen2 using 256-bit AES encryption, protecting stored files and blobs even if the underlying media is compromised. Option D is correct because Data Lake Storage Gen2 supports a hierarchical namespace where Azure RBAC controls management-plane and broad data access while POSIX-style ACLs provide fine-grained file and directory permissions, ensuring only authorized identities can read or modify data. Option E is correct because requiring secure transfer (HTTPS) enforces TLS encryption of data in transit between clients and the storage account, preventing eavesdropping or tampering on the network.

Option A is not correct because TLS 1.0 is deprecated and insecure; enabling it weakens transport security rather than strengthening it, and Azure recommends TLS 1.2 or higher. Option C is not correct because firewall IP restrictions are a network-perimeter control, not a mechanism for securing data at rest or in transit, and they do not encrypt or authorize data access by themselves.

Exam trap

The trap here is that candidates may confuse network security controls (firewalls) or legacy protocol compatibility (TLS 1.0) with actual data encryption mechanisms, leading them to select options that address access or connectivity rather than encryption of data at rest and in transit.

Practice this question →
18
MCQmedium

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

A.Avro
B.Parquet
C.CSV
D.JSON
AnswerB

Parquet stores data columnar with embedded schema and statistics, so Synapse serverless SQL reads only referenced columns and skips row groups via predicate pushdown. This satisfies the optimal query performance constraint for semi-structured JSON logs, whereas row-based formats force full scans and schema inference at runtime.

Why this answer

Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Exam trap

The trap here is that candidates often assume semi-structured data must stay in its native JSON format for simplicity, overlooking that columnar formats like Parquet can natively store nested JSON structures via repeated fields and maps, while providing massive performance gains in serverless SQL engines.

How to eliminate wrong answers

Option A is wrong because Avro is a row-oriented format that, while efficient for write-heavy and schema-evolving scenarios, does not support column pruning or predicate pushdown as effectively as Parquet, leading to higher I/O and slower query performance in Synapse Serverless SQL. Option C is wrong because CSV is a plain-text, row-oriented format with no built-in compression or indexing, forcing full table scans and increasing data transfer costs, which degrades query performance. Option D is wrong because storing logs as raw JSON files results in verbose, uncompressed data that lacks schema enforcement and columnar optimization, causing Synapse Serverless SQL to parse entire files without the benefits of predicate pushdown or efficient compression.

Practice this question →
19
MCQeasy

Your company uses Azure Cosmos DB for NoSQL to store user profiles. The application frequently reads profiles by user ID (the partition key). Occasionally, the application needs to query by email address, which is not part of the partition key. What should you do to optimize the occasional queries by email?

A.Create a secondary (composite) index on the email field.
B.Change the partition key to the email field.
C.Denormalize the data by storing a copy of the email in the partition key.
D.Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.
AnswerA

A secondary index allows efficient queries on non-partition key fields.

Why this answer

Creating a secondary index on the email field allows Azure Cosmos DB for NoSQL to efficiently serve queries filtering by email without scanning all partitions. Since email is not the partition key, a secondary index (specifically a composite index if needed for multi-field queries, or a single-field index) enables index-based lookup across all physical partitions, optimizing the occasional query without redesigning the data model.

Exam trap

The trap here is that candidates often assume a secondary index is unnecessary or that changing the partition key is the only way to optimize non-key queries, but Azure Cosmos DB supports secondary indexes for non-partition key fields, and altering the partition key would disrupt the primary access pattern.

How to eliminate wrong answers

Option B is wrong because changing the partition key to email would break the primary access pattern (reads by user ID), causing cross-partition queries for the frequent user ID lookups and likely exceeding request unit (RU) costs. Option C is wrong because denormalizing by storing a copy of the email in the partition key does not change the partition key itself; the partition key remains user ID, so queries by email would still require a cross-partition scan unless a secondary index is used. Option D is wrong because using the change feed to maintain a separate container keyed by email introduces operational complexity and eventual consistency, and is overkill for occasional queries; a secondary index is simpler and directly supported.

Practice this question →
20
MCQhard

You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?

A.The credential used to access ADLS Gen2 does not have sufficient permissions.
B.The serverless SQL pool does not support reading Parquet files.
C.The external table definition is missing the SCHEMA_NAME parameter.
D.The DATA_COMPRESSION setting is incompatible with Parquet files.
AnswerA

Insufficient permissions (e.g., missing Storage Blob Data Reader role) cause zero rows.

Why this answer

The most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.

Exam trap

The trap here is that candidates assume a missing or misconfigured schema parameter (like SCHEMA_NAME) would cause zero rows, but in reality, permission issues are the primary cause of empty results when the data path is correct and the file format is supported.

How to eliminate wrong answers

Option B is wrong because Azure Synapse serverless SQL pool fully supports reading Parquet files, including partitioned data like /year=2024/. Option C is wrong because the SCHEMA_NAME parameter is optional in CREATE EXTERNAL TABLE and is used for schema binding, not for data access or row retrieval. Option D is wrong because DATA_COMPRESSION is not applicable to Parquet files; Parquet has its own internal compression (e.g., Snappy, gzip) and the setting is ignored or causes an error, not silent zero rows.

Practice this question →
21
MCQhard

A financial services company needs to store transaction data for audit purposes. The data must be immutable and cannot be modified or deleted for 7 years. Which Azure storage feature should be used?

A.Azure Blob Storage immutability policy (time-based retention).
B.Azure Blob Storage soft delete.
C.Azure Blob Storage versioning.
D.Azure Files share snapshots.
AnswerA

A time-based retention policy on Azure Blob Storage enforces write-once, read-many (WORM) semantics, blocking modification or deletion of transaction blobs until the retention interval lapses. Setting it to seven years directly satisfies the audit requirement for immutability, unlike standard blob storage where objects remain freely overwritable.

Why this answer

Azure Blob Storage immutability policy with time-based retention ensures that blobs cannot be modified or deleted for a specified retention period (e.g., 7 years). This meets the audit requirement for immutable storage by locking the data at the storage level, preventing any writes or deletes until the retention interval expires. The policy is enforced at the container level and applies to all blobs within, making it the correct choice for regulatory compliance.

Exam trap

The trap here is that candidates confuse soft delete or versioning with immutability, not realizing that only a locked time-based retention policy provides the strict write-once, read-many guarantee required for audit data that cannot be modified or deleted for a fixed duration.

How to eliminate wrong answers

Option B is wrong because soft delete only protects against accidental deletion by retaining deleted blobs for a configurable period, but it does not prevent modification or provide true immutability; data can still be overwritten. Option C is wrong because versioning preserves previous versions of a blob when overwritten or deleted, but it does not block writes or deletes—new versions can be created, and the current version can be modified, violating immutability. Option D is wrong because Azure Files share snapshots are point-in-time read-only copies of a file share, but they do not enforce a write-once-read-many (WORM) state on the live share; the original files can still be modified or deleted.

Practice this question →
22
MCQmedium

A financial services firm stores trade records in an Azure Data Lake Storage Gen2 account. Regulatory requirements mandate that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that the key be rotated every 90 days without re-uploading any data. The storage account currently uses Microsoft-managed keys. What should you do to meet these requirements with the least administrative effort?

A.Configure the storage account to use customer-managed keys stored in Azure Key Vault, and create a new key version in Key Vault every 90 days.
B.Deploy Azure Disk Encryption on the storage account and schedule a Key Vault key rotation task using Azure Automation.
C.Create a new storage account with customer-managed keys and migrate all trade records using Azure Data Factory, then delete the original account.
D.Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a lifecycle management policy to re-encrypt blobs every 90 days.
AnswerA

Configuring customer-managed keys for the storage account links encryption at rest to a key in Azure Key Vault. Creating a new key version every 90 days rotates the key without requiring any data re-upload, because Azure Storage automatically uses the latest key version. This directly satisfies both the CMK mandate and the rotation requirement with minimal ongoing effort.

Why this answer

Customer-managed keys in Azure Key Vault allow the storage account to use an encryption key controlled by the organization. Rotating the key is achieved by creating a new key version in Key Vault; Azure Storage automatically picks up the latest version, so no data re-upload or re-encryption job is needed. This satisfies both the CMK mandate and the 90-day rotation requirement with minimal administrative effort.

Exam trap

The trap here is assuming that key rotation requires re-encrypting or migrating data, when Azure Storage automatically uses the latest key version once customer-managed keys are configured.

Practice this question →
23
MCQhard

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

A.Azure Cosmos DB with multi-region writes and eventual consistency
B.Azure Cosmos DB with single-region writes and strong consistency
C.Azure Data Lake Storage Gen2 with hierarchical namespace
D.Azure Blob Storage with hot tier and append blobs
AnswerA

Cosmos DB with multi-region writes and eventual consistency accepts writes at any region without cross-region coordination, giving the lowest write latency and high throughput. This satisfies the write-heavy, globally distributed ingestion requirement, while still serving dashboard reads.

Why this answer

Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Exam trap

The trap here is that candidates often assume strong consistency is required for real-time dashboards, but eventual consistency is sufficient for write-heavy IoT scenarios where occasional stale reads are acceptable, and multi-region writes drastically reduce latency compared to single-region writes.

Why the other options are wrong

B

Strong consistency increases write latency and single-region limits throughput.

C

Data Lake Storage is optimized for analytics, not low-latency writes.

D

Blob storage has higher write latency and append blobs are not ideal for high-throughput ingestion.

Practice this question →
24
MCQmedium

A company is designing a data storage solution for streaming IoT telemetry data. The data is JSON-formatted, arrives at up to 10,000 events per second, and must be stored for at least 30 days for real-time dashboards and ad-hoc querying. The solution must minimize operational overhead and query latency. Which Azure service should they use?

A.Azure Blob Storage with Azure Data Lake Storage Gen2
B.Azure Data Explorer (ADX)
C.Azure Cosmos DB with analytical store
D.Azure SQL Database with elastic query
AnswerB

Azure Data Explorer ingests high-volume JSON telemetry at millions of events per second and stores it in a columnar engine optimised for low-latency analytical queries, meeting the 10,000 events per second, 30-day retention and real-time dashboard requirements with minimal operational overhead.

Why this answer

Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and time-series data, ingesting up to 10,000 events per second with low latency. Its columnar storage and indexing enable sub-second queries on JSON data for real-time dashboards, while the 30-day retention is natively configurable via caching and soft-delete policies. This minimizes operational overhead by eliminating the need for manual partitioning or index tuning.

Exam trap

The trap here is that candidates confuse Azure Data Explorer with Azure Data Lake Storage, assuming that a data lake can serve real-time dashboards, but ADLS Gen2 lacks the indexing and query engine needed for sub-second latency on streaming data.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage with ADLS Gen2 is optimized for large-scale batch analytics and data lakes, not for real-time, sub-second queries on streaming data; querying JSON blobs directly incurs high latency and requires additional compute (e.g., Azure Synapse or Databricks). Option C is wrong because Azure Cosmos DB with analytical store is designed for transactional workloads with real-time analytics on operational data, but its ingestion throughput for 10,000 events/second of pure telemetry would be costly and over-provisioned, and the analytical store is better suited for hybrid transactional/analytical processing (HTAP) rather than pure streaming telemetry. Option D is wrong because Azure SQL Database with elastic query is a relational OLTP system not optimized for high-velocity JSON ingestion or time-series queries; it would require extensive schema design, indexing, and sharding to handle 10,000 events/second, and query latency would be higher due to row-based storage.

Practice this question →
25
MCQeasy

You need to store JSON files from an external partner in Azure Blob Storage. The files contain sensitive financial data. Which access method provides the highest security while allowing the partner to upload files?

A.Share the storage account access key with the partner
B.Configure a firewall to allow only the partner's IP address
C.Generate a shared access signature (SAS) with write-only permission and expiry time
D.Enable anonymous public access to a container
AnswerC

A Shared Access Signature (SAS) with write-only permission and an expiry time allows the partner to upload files without exposing the account key. It limits access to exactly what is needed (write only) and automatically revokes after the expiry, providing the highest security for sensitive financial data.

Why this answer

A Shared Access Signature (SAS) with write-only permission and an expiry time provides delegated, time-limited access to Azure Blob Storage without exposing the storage account key. This ensures the partner can upload JSON files but cannot read, modify, or list existing blobs, and access automatically revokes after the expiry, meeting the highest security requirement for sensitive financial data.

Exam trap

The trap here is that candidates often confuse IP-based firewalls (Option B) as a security method, but firewalls do not authenticate users or control data-plane permissions, and they can be bypassed if the partner's IP changes or if the partner uses a shared network.

How to eliminate wrong answers

Option A is wrong because sharing the storage account access key grants full administrative control (read, write, delete, list) over all blobs and containers, violating the principle of least privilege and exposing the key to potential compromise. Option B is wrong because a firewall restricting to the partner's IP address does not authenticate the partner's identity or control permissions; it only limits network-level access, and the partner's IP may change, leading to access failures or security gaps. Option D is wrong because enabling anonymous public access allows anyone on the internet to read and list blobs without any authentication, completely exposing sensitive financial data.

Practice this question →
26
Multi-Selecteasy

You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?

Select 2 answers
A.Store data in columnar format (e.g., Parquet)
B.Create indexes on all columns
C.Enable row-level security
D.Partition the data by date
E.Enable geo-redundant storage
AnswersA, D

Parquet stores data column-wise, so time-range queries read only the timestamp and relevant value columns rather than entire rows, cutting I/O dramatically. Its built-in compression and encoding shrink the append-only telemetry at rest, lowering storage costs while sustaining the high-velocity ingestion the scenario demands.

Why this answer

Option A is correct because storing append-only IoT telemetry in a columnar format such as Parquet enables column pruning and compression, so time-range queries scan only the needed timestamp/value columns and consume far less I/O and storage, reducing cost. Option D is correct because partitioning the data by date (e.g., a partition per day or month) lets the query engine perform partition pruning, skipping entire partitions outside the requested time range, which dramatically improves time-range query performance and lowers scanned data volume. Option B is not appropriate because indexing every column on high-velocity append-only data adds heavy write and storage overhead while providing little benefit for range scans on time-series data.

Option C does not belong because row-level security is an access-control feature, not a query-performance or cost optimization. Option E is not relevant because geo-redundant storage improves durability and disaster recovery across regions but does not optimize time-range query performance and typically increases cost.

Exam trap

The trap here is that candidates often confuse indexing (B) with partitioning, but for append-only analytical workloads, indexes add write overhead and cost without benefit, while date partitioning directly enables partition elimination for time-range queries.

Practice this question →
27
MCQhard

You are tuning a dedicated SQL pool in Azure Synapse Analytics. A query that joins two large tables (fact_sales and dim_product) is slow. The fact_sales table is hash-distributed on product_id, and dim_product is replicated. You notice that the query plan shows a shuffle move. What is the most likely cause?

A.The fact_sales table uses clustered columnstore index.
B.The dim_product table is replicated, causing a broadcast join.
C.Statistics are out of date on both tables.
D.The join condition does not include the distribution key for fact_sales.
AnswerD

Hash distribution on product_id means rows with the same product_id reside on one distribution. If the join predicate omits product_id, the engine cannot co-locate fact and dimension rows, forcing a shuffle move to redistribute data before the join completes.

Why this answer

When the join condition does not include the distribution key (product_id) of the hash-distributed fact_sales table, the SQL engine cannot perform a collocated join. Instead, it must shuffle data across distributions to satisfy the join, which introduces expensive data movement. The shuffle move in the query plan directly indicates this redistribution.

Exam trap

The trap here is that candidates often confuse a shuffle move with a broadcast join or blame indexing, but the root cause is the mismatch between the join key and the distribution key, which forces data movement regardless of other optimizations.

How to eliminate wrong answers

Option A is wrong because a clustered columnstore index is optimized for large fact tables and typically improves query performance; it would not cause a shuffle move. Option B is wrong because a replicated table (dim_product) is designed to avoid shuffles by having a copy on each distribution, enabling a broadcast join without data movement. Option C is wrong because out-of-date statistics can lead to suboptimal plans but do not directly force a shuffle move; the shuffle is a structural requirement based on the join key not matching the distribution key.

Practice this question →
28
MCQeasy

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be organized for efficient querying by a Synapse Analytics serverless SQL pool. The data is currently stored as many small CSV files in a flat directory. You need to improve query performance and reduce the cost of scanning unnecessary data. What should you do?

A.Combine all CSV files into one large CSV file.
B.Create an external table in the serverless SQL pool that points to the CSV files.
C.Move the data to Azure Blob Storage and enable hierarchical namespace.
D.Convert the data to Parquet format and partition it by admission date.
AnswerD

Parquet is a columnar format that enables efficient compression and column pruning, reducing I/O and cost. Partitioning by admission date allows the serverless SQL pool to skip irrelevant partitions, further minimizing data scanned. This combination significantly improves query performance and reduces cost for date-filtered queries.

Why this answer

Converting to Parquet, a columnar format, enables column pruning and better compression, reducing the amount of data scanned. Partitioning by admission date allows the serverless SQL pool to prune partitions based on query filters. Together, these changes optimize query performance and lower cost.

Exam trap

The trap here is focusing on file count or storage service rather than the data format and partitioning, which are the primary drivers of query performance and cost in serverless SQL pools.

Practice this question →
29
MCQeasy

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

A.Azure Table Storage
B.Azure Cosmos DB
C.Azure Data Lake Storage Gen2
D.Azure SQL Database
AnswerB

Azure Cosmos DB stores schema-agnostic JSON natively and indexes every property automatically, so evolving schemas need no migrations. Its multi-region replication delivers the required global distribution, while single-digit-millisecond reads and writes satisfy the low-latency constraint. The SQL API queries semi-structured documents directly, unlike fixed-schema relational services.

Why this answer

Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's key-value model with document storage, overlooking that it lacks native JSON support and global low-latency query capabilities, while Azure Cosmos DB is specifically designed for these requirements.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store that does not natively support JSON documents or complex nested structures; it requires manual serialization and lacks global distribution with low-latency guarantees. Option C is wrong because Azure Data Lake Storage Gen2 is optimized for large-scale batch analytics and data lakes, not for low-latency transactional queries on semi-structured data. Option D is wrong because Azure SQL Database requires a fixed relational schema and does not handle dynamic schema changes without manual migrations, nor does it offer the same turnkey global distribution as Cosmos DB.

Practice this question →
30
MCQeasy

You are tasked with designing a data storage solution for a social media analytics company. They need to store user profile data (JSON) and social media posts (text and images). The data is used for machine learning models that require fast random access to individual user profiles and the ability to run analytical queries over posts. The solution must provide low-latency reads for user profiles (milliseconds) and support for large-scale analytics on posts. Which combination of Azure data services should you recommend?

A.Azure Cosmos DB for user profiles and Azure Data Lake Storage Gen2 for posts
B.Azure Cosmos DB for both user profiles and posts
C.Azure SQL Database for both user profiles and posts
D.Azure Table Storage for user profiles and Azure Blob Storage for posts
AnswerA

Cosmos DB delivers single-digit-millisecond point reads for JSON profiles through its partitioned, indexed document store, meeting the low-latency requirement. Data Lake Storage Gen2 provides hierarchical namespace and massively parallel analytical engines for large-scale post analytics, satisfying the second half of the workload.

Why this answer

Azure Cosmos DB provides low-latency (millisecond) reads for user profiles via its indexing and partitioning capabilities, ideal for fast random access. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with Blob Storage, enabling large-scale analytical queries on posts using tools like Azure Synapse Analytics or Spark, while efficiently storing text and images.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for both workloads (Option B) because they assume its multi-model support handles analytics, but they overlook that Cosmos DB is a transactional database not designed for large-scale analytical queries, while ADLS Gen2 is purpose-built for data lakes and analytics.

How to eliminate wrong answers

Option B is wrong because using Azure Cosmos DB for both profiles and posts would be cost-prohibitive for large-scale analytics on posts, as Cosmos DB is optimized for transactional workloads, not petabyte-scale analytical queries, and lacks native support for hierarchical file storage. Option C is wrong because Azure SQL Database is a relational store that struggles with semi-structured JSON profiles and large binary images, and it cannot scale to handle massive analytical workloads on posts without significant performance degradation and cost. Option D is wrong because Azure Table Storage is a NoSQL key-value store that does not support complex queries or indexing for fast random access to JSON profiles, and Azure Blob Storage lacks a hierarchical namespace and native analytical integration, making large-scale analytics inefficient.

Practice this question →
31
MCQeasy

Your company is migrating an on-premises SQL Server database to Azure SQL Database. The database includes a large fact table with hourly updates. You need to minimize downtime during migration. Which Azure service should you use to replicate data continuously?

A.Use Azure SQL Managed Instance as a target
B.Use Azure Data Factory with a copy activity
C.Use Azure Database Migration Service with online migration mode
D.Use Azure Synapse Link for SQL Server
AnswerC

Azure Database Migration Service online migration mode replicates ongoing changes continuously while the source stays live, so cutover downtime is minimised. This directly satisfies the stem's requirement for continuous replication of an hourly-updated fact table.

Why this answer

Azure Database Migration Service (DMS) with online migration mode uses continuous change data capture (CDC) to replicate ongoing changes from the source SQL Server to Azure SQL Database with minimal downtime. This is the only option that supports near-zero downtime migration by synchronizing data continuously until the final cutover, which is critical for a large fact table with hourly updates.

Exam trap

The trap here is that candidates confuse Azure Data Factory's copy activity (a batch tool) with continuous replication, or assume Azure SQL Managed Instance inherently supports migration, when in fact DMS online mode is the specific service designed for minimal-downtime migrations.

How to eliminate wrong answers

Option A is wrong because Azure SQL Managed Instance is a target platform, not a migration service; it does not provide continuous data replication from an on-premises source. Option B is wrong because Azure Data Factory with a copy activity is a batch-oriented ETL tool that performs periodic snapshots, not continuous real-time replication, and would require downtime for the final data sync. Option D is wrong because Azure Synapse Link for SQL Server is designed for real-time analytics on operational data in Azure Synapse, not for migrating databases to Azure SQL Database with continuous replication.

Practice this question →
32
MCQeasy

A company is designing a data storage solution for IoT device telemetry data. The data is append-only, needs to be stored cost-effectively for long-term analytics, and must support querying by device ID and timestamp. Which Azure storage solution should they use?

A.Azure Data Lake Storage Gen2
B.Azure Cosmos DB
C.Azure SQL Database
D.Azure Blob Storage with hot access tier
AnswerA

Azure Data Lake Storage Gen2 suits append-only telemetry because its hierarchical namespace organises data into directories partitioned by device ID and timestamp, enabling efficient partition pruning during analytics queries. It also layers the cost-effective Hot, Cool and Archive tiers over Blob storage, satisfying the long-term retention requirement without sacrificing query performance.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines the cost-effective, append-only blob storage of Azure Blob Storage with a hierarchical namespace that enables directory-level operations and POSIX-like access control. This makes it ideal for storing large volumes of IoT telemetry data at low cost while supporting efficient querying by device ID and timestamp through partition pruning in tools like Azure Synapse Analytics or Apache Spark.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with ADLS Gen2, assuming that Blob Storage alone supports hierarchical namespace and efficient querying, when in fact ADLS Gen2 is required for the hierarchical namespace and POSIX-like directory structure that enables partition pruning and cost-effective analytics on append-only data.

How to eliminate wrong answers

Option B (Azure Cosmos DB) is wrong because it is a NoSQL database optimized for low-latency, transactional workloads with flexible schemas, not for cost-effective long-term storage of append-only telemetry data; its RU-based pricing model becomes prohibitively expensive for high-volume, append-only IoT data. Option C (Azure SQL Database) is wrong because it is a relational database designed for OLTP workloads with strong consistency and indexing, but its per-core licensing and storage costs are too high for storing petabytes of append-only telemetry data, and it does not natively support the hierarchical namespace needed for efficient partition pruning by device ID and timestamp. Option D (Azure Blob Storage with hot access tier) is wrong because while it provides cost-effective storage, the hot access tier incurs higher storage costs than the cool or archive tiers, and without the hierarchical namespace of ADLS Gen2, querying by device ID and timestamp requires full scans or external indexing, making it less efficient for analytics workloads.

Practice this question →
33
MCQmedium

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

A.Use Blob Storage event triggers to invoke Azure Functions
B.Use Azure Synapse Pipelines with a schedule and full load each time
C.Use Azure Data Factory with watermark columns in the source
D.Store processed file names in a Delta table and compare with source folder listing
AnswerD

Recording processed filenames in a Delta table gives transactional, queryable state that survives failures, and comparing it against the source listing identifies new files. This supports incremental processing while allowing late-arriving files within the 2-hour window to be picked up.

Why this answer

Storing processed file names in a Delta table allows the pipeline to track which files have already been ingested, supporting incremental processing and handling late-arriving data up to 2 hours. By comparing the current source folder listing against the Delta table, the pipeline can identify only new or late-arriving files, avoiding reprocessing and ensuring exactly-once semantics. This approach integrates seamlessly with Azure Databricks and Delta Lake's ACID transactions, providing reliable state management for batch pipelines.

Exam trap

The trap here is that candidates often choose Azure Data Factory with watermark columns (Option C) because it is a common incremental load pattern, but they overlook that watermark columns apply to row-based sources with change tracking, not to file-based sources where the challenge is tracking which files have been processed.

How to eliminate wrong answers

Option A is wrong because Blob Storage event triggers invoke Azure Functions in near-real-time, which is suitable for event-driven or streaming patterns, not for a batch pipeline that needs to track processed files incrementally with a 2-hour late-arrival window. Option B is wrong because using Azure Synapse Pipelines with a schedule and full load each time ignores incremental processing requirements and would reprocess all data, leading to inefficiency and inability to handle late-arriving data without overwriting. Option C is wrong because Azure Data Factory with watermark columns in the source is designed for incremental loads based on a timestamp or numeric column, but the scenario involves tracking files in a folder structure, not rows in a table, and watermark columns cannot track individual file names or handle late-arriving files that appear after the watermark value has advanced.

Practice this question →
34
Multi-Selectmedium

You are designing a data storage solution in Azure Synapse Analytics dedicated SQL pool. The solution must support efficient loading of large volumes of data from external sources and provide high query performance for reporting. You need to choose two table distribution types that are most appropriate for large fact tables and dimension tables respectively. (Choose two.)

Select 2 answers
A.External table
B.Replicated table
C.Hash-distributed table
D.Temporary table
E.Round-robin table
AnswersB, C

Replicated tables store a full copy of the table on every compute node. They are best for small dimension tables because they eliminate the need for data movement during joins with large fact tables. This reduces query latency and improves performance for star schema queries.

Why this answer

For a dedicated SQL pool, large fact tables should be hash-distributed on a column frequently used in joins to minimize data movement. Small dimension tables should be replicated so that joins with fact tables avoid data movement. Round-robin is for staging, and external and temporary tables are not distribution types.

This combination optimizes query performance for star schema workloads.

Exam trap

The trap here is assuming that round-robin distribution is always best for even data spread, or that external tables can replace internal tables for performance-critical fact tables.

Practice this question →
35
MCQeasy

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

A.Azure Disk Encryption
B.Azure Storage Firewall
C.Azure Storage Service Encryption (SSE) with customer-managed keys
D.Azure Information Protection
AnswerC

Azure Storage Service Encryption with customer-managed keys wraps the account's data encryption key with a key held in Azure Key Vault, so blob data is encrypted at rest under organisational control. This meets the customer-managed key constraint for the CSV files.

Why this answer

Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Exam trap

The trap here is confusing Azure Disk Encryption (which encrypts VM disks) with Azure Storage Service Encryption (which encrypts blob data), leading candidates to select Option A when the requirement is for blob-level encryption with customer-managed keys.

How to eliminate wrong answers

Option A is wrong because Azure Disk Encryption uses BitLocker or DM-Crypt to encrypt OS and data disks of virtual machines, not the data stored in Azure Blob Storage. Option B is wrong because Azure Storage Firewall controls network access to the storage account via IP rules and virtual network rules, it does not provide encryption at rest. Option D is wrong because Azure Information Protection is a classification and labeling solution for documents and emails, not an encryption mechanism for data at rest in Azure Blob Storage.

Practice this question →
36
MCQmedium

You are designing a storage solution for a financial analytics platform that ingests CSV files into Azure Data Lake Storage Gen2. Analysts run complex T-SQL queries against the data using Azure Synapse Analytics serverless SQL pool. You need to minimize query cost and improve performance. What should you do?

A.Store the CSV files in a single folder and create a view that selects all columns.
B.Use Azure Blob Storage instead of Azure Data Lake Storage Gen2.
C.Convert the CSV files to Parquet format and partition the data by date.
D.Enable Azure Synapse Link for Azure Cosmos DB.
AnswerC

Parquet is a columnar format that enables column pruning and predicate pushdown, reducing the amount of data scanned by serverless SQL queries. Partitioning by date further limits the data read when queries filter on date, directly lowering cost and improving performance. This is the recommended approach for analytical workloads in Azure Synapse serverless SQL pools.

Why this answer

Serverless SQL pool charges based on data processed. Converting CSV to Parquet, a columnar format, allows queries to read only needed columns and push down filters, reducing data scanned. Partitioning by date further minimizes data read when queries filter on date.

Together, these changes significantly lower cost and improve performance for analytical queries over file data in Data Lake Storage Gen2.

Exam trap

The trap here is assuming that simply changing the storage service or adding a view will optimize query cost, when the real gains come from using a columnar format and partitioning the data.

Practice this question →
37
MCQmedium

A media company uses Azure Data Lake Storage Gen2 to store video files and metadata. They need to ensure that when a user is deleted from Azure Active Directory, their access to the data lake is immediately revoked. They also want to minimize administrative overhead. What should they do?

A.Assign POSIX ACLs directly to each user.
B.Generate a shared access signature (SAS) token for each user.
C.Use Azure Active Directory security groups and assign ACLs to the groups.
D.Store the storage account key in Azure Key Vault and grant users access to the key.
AnswerC

Assigning ACLs to Azure Active Directory security groups means that access is managed through group membership. When a user is deleted from Azure Active Directory, they are automatically removed from all groups, and their access is revoked immediately. This minimizes administrative overhead because permissions are managed at the group level.

Why this answer

Using Azure Active Directory security groups to manage access to Data Lake Storage Gen2 ensures that when a user is deleted from Azure Active Directory, their group memberships are removed, and their access is revoked immediately. This approach centralizes permission management, reducing administrative overhead. Direct ACLs, SAS tokens, and storage account keys do not provide automatic revocation upon user deletion.

Exam trap

The trap here is assuming that SAS tokens or direct ACLs can provide immediate revocation when a user is deleted, when only Azure Active Directory group-based access does so automatically.

Practice this question →
38
Multi-Selecthard

Which TWO strategies can be used to optimize storage costs for historical data in Azure Data Lake Storage Gen2?

Select 2 answers
A.Enable soft delete to recover data
B.Use geo-redundant storage (GRS) for durability
C.Implement lifecycle management policies to move data to archive tier
D.Store data in compressed columnar format like Parquet
E.Encrypt data with Azure Storage Service Encryption
AnswersC, D

Lifecycle management policies automatically transition blobs from hot to cool then archive tier based on age, cutting per-GB cost for historical data. This directly satisfies the storage cost optimisation requirement without manual intervention or data deletion.

Why this answer

Option C is correct because Azure Storage lifecycle management policies can automatically transition blobs from hot/cool tiers to the archive tier after a defined age, and the archive tier has the lowest storage cost per GB for rarely accessed historical data. Option D is correct because storing historical data in a compressed columnar format such as Parquet reduces the total bytes stored and scanned, and columnar compression typically achieves much higher compression ratios than row-based formats, directly lowering storage and query costs in ADLS Gen2. Option A is not a cost-optimization strategy; soft delete retains deleted data and can actually increase storage charges during the retention period.

Option B is not cost-optimizing either, since GRS replicates data to a secondary region and costs more than LRS. Option E is also not a cost strategy; Azure Storage Service Encryption is enabled by default and does not reduce storage cost.

Exam trap

The trap here is that candidates confuse data protection features (soft delete, encryption, replication) with cost optimization strategies, but only tiering and compression directly reduce the amount or cost of stored data.

Practice this question →
39
Multi-Selecthard

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Select 3 answers
A.Use materialized views for complex aggregations
B.Use the largest resource class for all queries
C.Create clustered columnstore indexes
D.Use hash distribution on columns used in JOINs
E.Use round-robin distribution for large fact tables
AnswersA, C, D

Materialized views persist precomputed results of complex aggregations, so repeated queries read stored data instead of rescanning the large fact table. This directly reduces compute and elapsed time for the aggregation-heavy workloads the dedicated SQL pool must optimise.

Why this answer

Option A is correct because materialized views in a dedicated SQL pool precompute and persist the results of complex aggregations (such as SUM, COUNT, AVG over large fact tables), letting the optimizer rewrite queries to read the smaller materialized result instead of rescanning and re-aggregating the base tables, which dramatically reduces I/O and CPU. Option C is correct because clustered columnstore indexes are the default and recommended storage format for dedicated SQL pools, providing high compression and batch-mode vectorized execution that speeds up large-scale analytical scans and aggregations on fact tables. Option D is correct because hash distribution on columns frequently used in JOINs co-locates matching rows on the same distribution, enabling collocated joins that avoid costly data movement (shuffle) across the 60 distributions.

Option B is not a best practice because assigning the largest resource class to every query consumes excessive memory and concurrency slots, reducing overall workload throughput; resource classes should be sized to each query's needs. Option E is not a best practice because round-robin distribution is suited to staging or temporary tables, whereas large fact tables benefit from hash distribution on a join/filter column to minimize data movement during queries.

Exam trap

The trap here is that candidates often confuse resource class with performance optimization, assuming larger resource classes always speed up queries, when in fact they reduce concurrency and can cause resource contention, making them a poor general-purpose best practice.

Practice this question →
40
Multi-Selecteasy

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Select 2 answers
A.Hierarchical namespace
B.Immutable storage
C.Soft delete for blobs
D.Lifecycle management policies
E.POSIX-compliant access control lists
AnswersA, E

A hierarchical namespace arranges objects into true directories, so operations like atomic directory renames and POSIX-style access control lists become possible. Flat Blob Storage merely simulates folders through naming conventions, meaning renames require copying every blob individually — the specific capability the stem demands.

Why this answer

Azure Data Lake Storage Gen2 is built on top of Blob Storage but adds a hierarchical namespace (option A), which organizes objects into true directories and enables atomic directory operations and efficient rename/delete semantics that flat Blob Storage cannot provide. It also supports POSIX-compliant access control lists (option E), allowing fine-grained, file- and directory-level permissions (read/write/execute) that map to Hadoop and POSIX-style security models, which plain Blob Storage does not offer. The other options are not unique to ADLS Gen2: immutable storage (option B) is a Blob Storage feature (time-based retention and legal holds), soft delete for blobs (option C) is a Blob Storage data-protection feature, and lifecycle management policies (option D) are available for Blob Storage to transition or expire data across hot/cool/archive tiers.

Exam trap

The trap here is that candidates often assume features like soft delete or lifecycle management are exclusive to ADLS Gen2, when in fact they are shared with Blob Storage, while the hierarchical namespace and POSIX ACLs are the true differentiators.

Practice this question →
41
MCQhard

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

A.Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.
B.Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.
C.Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.
D.Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.
AnswerB

CDC captures only changed rows, and ADF can run frequently to meet latency target.

Why this answer

Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Exam trap

The trap here is that candidates often confuse Change Tracking (which only tracks that a row changed, not the actual changes) with Change Data Capture (which captures the before-and-after values), leading them to choose Option A without realizing the missing push mechanism and the need for additional services to achieve near-real-time replication.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Change Tracking does not natively push changes to Event Hubs; it requires custom logic or additional services (e.g., Azure Functions) to bridge the gap, adding complexity and potential latency that may not guarantee sub-5-minute replication. Option C is wrong because PolyBase in Synapse is designed for batch querying of external data sources, not for near-real-time incremental replication; querying the source SQL database every 5 minutes would perform full table scans on 50 million rows, causing high source database load and failing to meet latency requirements. Option D is wrong because scheduling a full copy of the entire 50-million-row table every 5 minutes is extremely inefficient, consumes excessive bandwidth and Synapse storage resources, and would likely exceed the latency window due to the time required for a full data transfer.

Practice this question →
42
MCQmedium

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

A.Hot tier
B.Archive tier
C.Cool tier
D.Premium tier
AnswerC

Cool tier suits append-only data accessed infrequently, offering lower storage cost than hot while retaining millisecond access and a 30-day minimum retention. It satisfies the requirement to minimise storage costs while still permitting retrieval well within the 24-hour compliance window.

Why this answer

The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Exam trap

The trap here is that candidates often choose the Archive tier because it has the lowest storage cost, overlooking the rehydration latency and the fact that retrieval within 24 hours is not guaranteed with standard priority rehydration, especially under heavy demand.

How to eliminate wrong answers

Option A is wrong because the Hot tier is designed for frequently accessed data and has higher storage costs, which would unnecessarily increase expenses for infrequently accessed compliance data. Option B is wrong because the Archive tier has the lowest storage cost but requires a rehydration process that can take up to 15 hours (and often longer), which may not guarantee retrieval within 24 hours and incurs significant read and data retrieval costs. Option D is wrong because the Premium tier is for high-performance, low-latency access (e.g., for transactional or real-time workloads) and is the most expensive option, making it unsuitable for cost-minimized, infrequently accessed historical data.

Practice this question →
43
MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The fact table is partitioned by date and distributed by product ID. Queries often join the fact table with a small dimension table on product ID. You notice that these joins cause significant data movement. You need to minimize data movement for these joins. What should you do?

A.Change the distribution of the fact table to round-robin.
B.Replicate the dimension table and distribute the fact table by product ID.
C.Partition the dimension table by product ID and use hash distribution on the fact table.
D.Create a materialized view that pre-joins the fact and dimension tables.
AnswerB

Replicating the dimension table creates a full copy on every compute node. When the fact table is distributed by product ID, each node can join its local fact rows with the replicated dimension without shuffling data. This eliminates data movement for the join. Replication is ideal for small dimension tables (typically under 2 GB compressed) and is a recommended pattern in dedicated SQL pool.

Why this answer

Replicating a small dimension table and hash-distributing the large fact table on the join key eliminates data movement because each compute node has a local copy of the dimension and only its subset of fact rows. This is a standard technique in Azure Synapse Analytics dedicated SQL pool to optimize join performance. Other options either increase data movement or do not address the distribution mismatch.

Exam trap

The trap here is confusing partitioning with distribution; partitioning does not co-locate rows across compute nodes, so it cannot eliminate join data movement.

Practice this question →
44
MCQmedium

You are designing a storage solution for a financial analytics platform. The data consists of large Parquet files stored in Azure Data Lake Storage Gen2. Analysts run complex queries that scan entire partitions, but only a subset of columns is needed for each query. You need to minimize the amount of data read from storage and improve query performance. What should you do?

A.Enable Azure Data Lake Storage Gen2 hierarchical namespace and organize files into folders by date.
B.Use Parquet format with column pruning and predicate pushdown enabled in the query engine.
C.Store the data in a row-based format such as Avro to enable faster full-row retrieval.
D.Convert the data to JSON and compress it with GZip to reduce storage size.
AnswerB

Parquet is a columnar format that allows query engines to read only the columns referenced in a query, drastically reducing I/O. Predicate pushdown further filters data at the storage layer. In Azure Synapse Analytics and Databricks, Parquet supports these optimizations natively. This directly addresses the need to minimize data read and improve performance for column-subset queries.

Why this answer

Parquet is a columnar storage format that enables column pruning, so only the columns needed by a query are read from storage. This significantly reduces I/O and improves performance for analytical queries that access a subset of columns. Predicate pushdown further optimizes by filtering data at the source.

Other formats like Avro or JSON are row-based and cannot provide these benefits.

Exam trap

The trap here is assuming that any compression or file organization automatically reduces data read during queries, when only columnar formats with column pruning achieve that.

Practice this question →
45
Multi-Selecthard

You are designing a storage layer in Azure Data Lake Storage Gen2 for a data engineering pipeline. You need to store Parquet files that will be queried by Azure Synapse Analytics serverless SQL pools and Azure Databricks. You must optimize for query performance and minimize data scanned. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Partition the data by a commonly filtered column such as TransactionDate.
B.Store the data in CSV format to ensure compatibility with all query engines.
C.Partition the data by a high-cardinality column such as TransactionId.
D.Enable hierarchical namespace and store all files in a single directory.
E.Store the data in Parquet format with a columnar layout.
AnswersA, E

Partitioning by a frequently filtered column allows queries with predicates on that column to skip entire partitions, reducing the amount of data scanned. This is especially effective when the column has a moderate number of distinct values, such as dates. It aligns with the goal of minimizing data scanned and improving performance in both Synapse and Databricks.

Why this answer

Using Parquet with a columnar layout enables column pruning and compression, reducing the data scanned. Partitioning by a commonly filtered column such as TransactionDate allows partition elimination, further reducing the amount of data read. Together, these actions optimize query performance for both Synapse serverless SQL pools and Databricks.

Exam trap

The trap here is assuming that any partitioning improves performance, when high-cardinality partitioning creates many small files and degrades it.

Practice this question →
46
MCQhard

You are designing a data storage solution in Azure Synapse Analytics. You need to load data incrementally from an Azure Data Lake Storage Gen2 source into a dedicated SQL pool. The source files are appended daily with new data, and you must ensure that only new records are loaded without duplicating existing records. The target table has a column named LoadDate that records when each row was inserted. Which approach should you use?

A.Use the COPY statement to load all source files directly into the target table, relying on the primary key to reject duplicates.
B.Use Azure Data Factory to copy all source files to the target table with a daily schedule, and use a tumbling window trigger to avoid duplicates.
C.Use PolyBase to load all source files into a staging table, then use a MERGE statement to insert only rows that do not exist in the target.
D.Use CREATE TABLE AS SELECT (CTAS) to create a new table from the source each day, replacing the target table.
AnswerC

PolyBase efficiently loads data from ADLS Gen2 into a staging table. A MERGE statement can then compare the staging data with the target using a key and insert only new rows, preventing duplicates. This approach supports incremental loads and leverages the LoadDate column if needed for filtering, meeting the requirement without reloading historical data.

Why this answer

Loading into a staging table with PolyBase and then using MERGE to insert only new rows is a standard pattern for incremental loads in a dedicated SQL pool. It avoids reloading historical data and prevents duplicates by comparing keys. The LoadDate column can be used to filter or track inserted rows, ensuring that only new records are added to the target.

Exam trap

The trap here is assuming that a primary key or a scheduled copy automatically prevents duplicates, when deduplication requires an explicit comparison such as MERGE.

Practice this question →
47
MCQmedium

You are designing a storage solution in Azure Synapse Analytics for a financial services company. The company ingests trade data into a dedicated SQL pool. The data is partitioned by trade date and queried primarily by date ranges. To improve query performance and reduce data movement, you need to choose an appropriate distribution type for the fact table. The table is large (over 2 billion rows) and frequently joined with a smaller dimension table on a non-distributed key. Which distribution type should you use?

A.Hash-distributed on the join key column
B.Hash-distributed on the trade date column
C.Round-robin distribution
D.Replicated distribution
AnswerA

Hash-distributing on the join key column co-locates rows with the same key on the same distribution, enabling collocated joins that eliminate data movement. This is optimal for large fact tables frequently joined on that key. It also distributes data evenly if the key has high cardinality, improving query performance for joins and aggregations.

Why this answer

For a large fact table frequently joined on a specific key, hash-distributing on that join key ensures that matching rows from both tables reside on the same distribution, enabling collocated joins and avoiding costly data movement. This distribution strategy is recommended when the join key has high cardinality and is used in most queries.

Exam trap

The trap here is assuming that hash-distributing on the most frequently filtered column (like date) is best, but the primary goal is to minimize data movement during joins, not to optimize for filters alone.

Practice this question →
48
MCQeasy

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

A.Querying the external table automatically imports data into a round-robin distribution.
B.The table cannot be queried until the data is imported into the dedicated SQL pool.
C.You can query the external table using standard T-SQL SELECT statements.
D.You must first create a PolyBase external table before querying.
AnswerC

Dedicated SQL pools expose external tables through the PolyBase engine, so standard T-SQL SELECT statements work against them exactly as with internal tables. The external table definition supplies the schema and the LOCATION pointing to the data in Azure Data Lake Storage, satisfying the requirement to query without loading.

Why this answer

An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Exam trap

The trap here is that candidates often assume external tables require an explicit import step before querying, but in reality, PolyBase allows direct querying of external data without any data movement into the dedicated SQL pool.

How to eliminate wrong answers

Option A is wrong because querying an external table does not automatically import data into a round-robin distribution; external tables remain external and data is not stored in the pool unless you explicitly use CREATE TABLE AS SELECT (CTAS) to import it. Option B is wrong because the external table can be queried immediately after creation without importing data; the data stays in external storage and is accessed on-the-fly by PolyBase. Option D is wrong because the T-SQL statements in the exhibit already create a PolyBase external table (using CREATE EXTERNAL TABLE with an external data source and file format), so no additional PolyBase external table creation is needed before querying.

Practice this question →
49
MCQeasy

You are designing a data storage solution for a company that uses Azure Data Lake Storage Gen2. The company needs to store data in a hierarchical namespace and requires fine-grained access control at the folder and file level. The solution must also support POSIX-style permissions. Which feature should you enable?

A.Enable Azure Defender for Storage.
B.Enable static website hosting on the storage account.
C.Enable blob soft delete on the storage account.
D.Enable hierarchical namespace on the storage account.
AnswerD

Hierarchical namespace is the feature that enables Azure Data Lake Storage Gen2 capabilities, including a true directory hierarchy, POSIX-style permissions, and fine-grained access control lists (ACLs) at the folder and file level. Without it, the storage account behaves like Blob Storage, which lacks these features. Enabling it is the correct action to meet the requirements.

Why this answer

Azure Data Lake Storage Gen2 is built on Blob Storage and adds a hierarchical namespace, which enables directory operations, POSIX-style permissions, and fine-grained ACLs. Enabling hierarchical namespace on the storage account is the required step to gain these capabilities. Other options are unrelated features that do not provide the needed functionality.

Exam trap

The trap here is assuming that any storage account feature can enable hierarchical namespace, but only the hierarchical namespace setting provides ADLS Gen2 capabilities.

Practice this question →
50
Multi-Selectmedium

You are designing a storage solution for a healthcare analytics platform. The data lands in Azure Data Lake Storage Gen2, and analysts must query it through Azure Synapse Analytics serverless SQL pools. Security policy requires that analysts see only the columns relevant to their role, and that access be governed by Microsoft Entra ID identities rather than shared keys. Which two actions should you include in the design? (Choose two.)

Select 2 answers
A.Grant analysts the Storage Blob Data Reader role on the ADLS Gen2 container through Microsoft Entra ID.
B.Enable anonymous public read access on the ADLS Gen2 container so that serverless queries do not require credentials.
C.Create a database scoped credential in the serverless database that stores the storage account access key.
D.Define a SQL view or stored procedure in the serverless database that projects only the columns each role is permitted to see, and grant SELECT on that object.
E.Configure a shared access signature with read permissions and distribute it to each analyst for use in their queries.
AnswersA, D

Synapse serverless SQL pools authenticate to ADLS Gen2 using the caller's Microsoft Entra identity, so granting Storage Blob Data Reader on the container lets analysts read the underlying files. This satisfies the requirement to avoid shared keys while providing least-privilege read access at the storage layer.

Why this answer

Serverless SQL pools in Synapse authenticate to ADLS Gen2 through the caller's Microsoft Entra identity, so Storage Blob Data Reader on the container provides the necessary file access without shared keys. Column-level restriction is then achieved by exposing only approved columns through a view or stored procedure and granting SELECT on that object. Together these actions satisfy both the identity governance and least-privilege column visibility requirements.

Exam trap

The trap here is assuming that storage-layer permissions alone can restrict which columns an analyst sees, when column projection requires a logical SQL object layered over the files.

Practice this question →
51
MCQeasy

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

A.Increase the provisioned RU/s on the container to handle both transactional and analytical queries.
B.Change the partition key to /date to optimize for analytical queries.
C.Create a materialized view using the change feed and store aggregated data in a separate container.
D.Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.
AnswerD

Enabling the analytical store (Synapse Link) replicates NoSQL data into a column-oriented store, separate from the transactional row store. Azure Synapse serverless SQL then queries this columnar copy, so cross-partition aggregations by date avoid consuming RUs on the transactional workload — satisfying the isolation constraint.

Why this answer

Enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Exam trap

The trap here is that candidates may think increasing RU/s or changing the partition key is a simpler fix, but the DP-203 exam specifically tests the understanding that analytical workloads must be isolated from transactional workloads using a dedicated analytical store like Synapse Link.

How to eliminate wrong answers

Option A is wrong because simply increasing provisioned RU/s does not separate the analytical workload from the transactional workload; both would still compete for the same throughput, and analytical queries would continue to consume high RUs, potentially throttling transactions. Option B is wrong because changing the partition key to /date would destroy the existing container's partitioning strategy, causing hot partitions for high-volume dates and severely degrading transactional performance for customer-based lookups; partition keys cannot be changed after creation without data migration. Option C is wrong because creating a materialized view using the change feed requires custom code to maintain the view, adds operational complexity, and still consumes RUs on the source container for change feed processing, whereas the analytical store provides a fully managed, zero-ETL solution.

Practice this question →
52
MCQmedium

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

A.Azure Table Storage.
B.Azure SQL Database with in-memory OLTP.
C.Azure Cosmos DB with analytical store.
D.Azure Data Explorer (ADX).
AnswerD

Azure Data Explorer ingests telemetry at high throughput and indexes it for sub-second queries, satisfying both the 10,000 events per second and low-latency constraints. Its columnar store and Kusto engine suit time-series IoT analytics, unlike row-store or batch-oriented alternatives.

Why this answer

Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and log analytics, ingesting 10,000+ events per second with sub-second query latency via its columnar storage and distributed query engine. It supports real-time analytics on streaming IoT data without requiring pre-defined schemas or indexing, making it the optimal choice for this scenario.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's analytical store with a real-time analytics solution, but it is designed for hybrid transactional/analytical processing (HTAP) on operational data, not for high-velocity streaming telemetry analytics where ADX excels.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for simple lookups and batch operations, not for real-time analytics with sub-second query latency on high-velocity streaming data. Option B is wrong because Azure SQL Database with in-memory OLTP is optimized for transactional workloads (OLTP) with high concurrency, not for analytical queries on high-volume telemetry; it cannot sustain 10,000 events per second with sub-second analytical latency. Option C is wrong because Azure Cosmos DB with analytical store is designed for globally distributed operational data with eventual consistency for analytics, but its analytical store uses a separate columnar format optimized for read-heavy workloads, not real-time sub-second queries on streaming IoT telemetry; it introduces higher latency for ingestion and query compared to ADX.

Practice this question →
53
MCQmedium

You are designing a data storage solution for a retail company that expects high volumes of small, time-series sensor data from thousands of IoT devices. The data must be stored cost-effectively and queried by time range with low latency. Which Azure data store should you recommend?

A.Azure Cosmos DB with analytical store
B.Azure SQL Database with columnstore indexes
C.Azure Blob Storage with Azure Data Lake Storage Gen2
D.Azure Data Explorer (ADX)
AnswerD

Azure Data Explorer is purpose-built for time-series telemetry, using columnar storage and time-based partitioning to return range queries over billions of small IoT records with low latency. It ingests high-volume streaming data cost-effectively, matching the retail scenario's throughput and query pattern.

Why this answer

Azure Data Explorer (ADX) is optimized for high-volume, time-series data ingestion and low-latency queries over time ranges. It uses a columnar storage engine and automatic indexing, making it cost-effective for sensor data from thousands of IoT devices.

Exam trap

The trap here is that candidates often choose Azure Blob Storage with Data Lake Storage Gen2 because it is cheap for storage, but they overlook that it lacks native query capabilities for low-latency time-series queries, requiring additional compute layers.

How to eliminate wrong answers

Option A is wrong because Azure Cosmos DB with analytical store is designed for globally distributed, multi-model data with operational and analytical workloads, not specifically for high-throughput time-series ingestion and time-range queries at low cost. Option B is wrong because Azure SQL Database with columnstore indexes is a relational database optimized for transactional workloads and analytical queries on structured data, but it cannot efficiently handle the high ingestion rates and time-series query patterns of thousands of IoT devices without significant cost and performance overhead. Option C is wrong because Azure Blob Storage with Azure Data Lake Storage Gen2 is a hierarchical file storage for big data analytics, not a query engine; it requires additional compute services like Azure Synapse or Spark to query time-series data, adding latency and complexity.

Practice this question →
54
Multi-Selectmedium

You are designing a data storage solution in Azure Synapse Analytics. You need to store large fact tables that are frequently joined with dimension tables on a common column. The solution must minimize data movement during query execution and support high-concurrency queries. Which two actions should you take? (Choose two.)

Select 2 answers
A.Use hash distribution on the join column for the fact table.
B.Use round-robin distribution for the fact table.
C.Use replicated tables for dimension tables.
D.Use a columnstore index on the fact table.
E.Use hash distribution on a column with high cardinality for the fact table.
AnswersA, C

Hash distribution on the join column colocates rows with the same key on the same distribution, minimizing data movement during joins. This is especially effective for large fact tables joined with dimension tables on that key. It improves query performance by reducing shuffling across distributions, which is critical for high-concurrency workloads.

Why this answer

Hash distributing the fact table on the join column and replicating the dimension tables minimizes data movement by colocating matching rows and eliminating shuffling of dimension data. These actions directly address the need for efficient joins and high concurrency in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is focusing solely on indexing or distribution method without considering the join column alignment, which is key to minimizing data movement.

Practice this question →
55
MCQmedium

Refer to the exhibit. An ARM template deploys an Azure Synapse Analytics workspace. What is the purpose of the 'managedVirtualNetwork' property set to 'default'?

A.It configures the workspace to use a user-assigned managed identity
B.It disables public network access to the workspace
C.It creates the workspace in a private endpoint configuration
D.It enables the workspace to use a managed virtual network for network isolation
AnswerD

Setting 'managedVirtualNetwork' to 'default' provisions a managed virtual network for the workspace, isolating Synapse compute from other Azure services and the public internet. This satisfies the stem's network isolation requirement, ensuring integration runtimes and Spark pools communicate privately within Microsoft-managed infrastructure rather than traversing public endpoints.

Why this answer

Setting 'managedVirtualNetwork' to 'default' in an ARM template for Azure Synapse Analytics enables a managed virtual network that provides network isolation for the workspace. This allows the workspace to use private endpoints and managed private endpoints for secure data integration without exposing traffic to the public internet. It is a foundational setting for implementing a secure, network-isolated Synapse environment.

Exam trap

The trap here is that candidates confuse 'managedVirtualNetwork' with directly creating private endpoints or disabling public access, when in fact it is the prerequisite that enables the workspace to use a managed virtual network for network isolation, with private endpoints and public network access controls being separate configurations.

How to eliminate wrong answers

Option A is wrong because the 'managedVirtualNetwork' property controls network isolation, not identity configuration; user-assigned managed identities are configured via the 'identity' property in the ARM template. Option B is wrong because disabling public network access is a separate setting (e.g., 'publicNetworkAccess' property), not the purpose of 'managedVirtualNetwork'. Option C is wrong because setting 'managedVirtualNetwork' to 'default' does not directly create the workspace in a private endpoint configuration; it enables the managed virtual network, and private endpoints must be explicitly created within that network for specific resources.

Practice this question →
56
MCQmedium

You are designing a data storage solution for a healthcare analytics platform. The solution must store patient records in Azure SQL Database and allow point-in-time restore for any time within the last 35 days. The data must be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. You need to configure the Azure SQL Database to meet these requirements. What should you do?

A.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and configure long-term retention (LTR) for 35 days.
B.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault and configure the retention policy for automated backups to 35 days.
C.Enable Transparent Data Encryption (TDE) with a service-managed key and configure the retention policy for automated backups to 35 days.
D.Enable Always Encrypted with a column master key stored in Azure Key Vault and configure the retention policy for automated backups to 35 days.
AnswerB

TDE with a customer-managed key in Azure Key Vault satisfies the encryption at rest requirement. Azure SQL Database automatically retains backups for 7 days by default, but the retention policy can be extended up to 35 days for point-in-time restore. This configuration meets both the encryption and the 35-day point-in-time restore requirements.

Why this answer

The requirement is for encryption at rest with customer-managed keys and point-in-time restore for 35 days. TDE with a customer-managed key in Azure Key Vault provides the required encryption. Configuring the backup retention policy to 35 days enables point-in-time restore within that window.

Other options either use the wrong encryption method or confuse long-term retention with point-in-time restore.

Exam trap

The trap here is confusing long-term retention with point-in-time restore retention, leading to selection of LTR when the requirement is for point-in-time restore.

Practice this question →
57
MCQhard

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

A.Store all data in Azure SQL Database with partitioning and drop older partitions
B.Use Azure Data Lake Storage Gen2 with a single storage tier
C.Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups
D.Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers
AnswerD

Lifecycle management rules automatically demote blobs from Hot to Cool after 30 days, then to Archive, satisfying the 10-year retention at lowest cost. Hot tier keeps recent data queryable with low latency, while Archive stores rarely accessed records offline, meeting the regulatory constraint.

Why this answer

Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Exam trap

The trap here is that candidates may choose Option C thinking TTL in Cosmos DB can handle retention, but TTL deletes data automatically, which violates regulatory retention requirements, not just cost optimization.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with partitioning and dropping older partitions permanently deletes data, violating the 10-year regulatory retention requirement. Option B is wrong because Azure Data Lake Storage Gen2 with a single storage tier (e.g., Hot) does not provide automatic cost optimization for rarely accessed data over 10 years, leading to higher costs. Option C is wrong because Azure Cosmos DB with TTL automatically deletes expired data, which cannot be used for long-term retention, and Azure Blob Storage for backups does not replace a tiering strategy for the primary data store.

Practice this question →
58
MCQeasy

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

A.Azure SQL Database
B.Azure Event Hubs
C.Azure Data Lake Storage Gen2
D.Azure Blob Storage
AnswerB

Azure Event Hubs provides a partitioned, low-latency streaming endpoint that ingests high-volume IoT telemetry for immediate downstream querying, while retaining events for replay into long-term cost-effective storage. This satisfies both the real-time analytics and long-term retention requirements.

Why this answer

Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage or Data Lake Storage as ingestion endpoints because they are cost-effective for storage, but they lack the real-time streaming capabilities and event-ordering guarantees that Event Hubs provides for immediate querying.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database designed for OLTP workloads, not for high-volume streaming ingestion; it lacks native support for event streaming protocols like AMQP or Kafka and would create bottlenecks and high costs for real-time IoT data. Option C is wrong because Azure Data Lake Storage Gen2 is a hierarchical file storage optimized for big data analytics and batch processing, not a real-time ingestion endpoint; it cannot natively accept streaming events or provide sub-second querying without an intermediary ingestion service. Option D is wrong because Azure Blob Storage is an object storage service for unstructured data, not designed for real-time event ingestion; it does not support streaming protocols or provide the low-latency, ordered event delivery required for immediate querying.

Practice this question →
59
Multi-Selectmedium

You are designing a data storage solution in Azure Synapse Analytics. You need to store large volumes of semi-structured data in a dedicated SQL pool. The data will be used for analytical queries that often filter on a date column and join on a customer ID column. You want to optimize query performance. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Use round-robin distribution for all tables to ensure even data spread.
B.Use hash distribution on the customer ID column for large tables that are frequently joined.
C.Use replicated distribution for all dimension tables regardless of size.
D.Partition large fact tables on the date column to enable partition elimination.
E.Store the semi-structured data as JSON in a single column and use JSON functions for queries.
AnswersB, D

Hash distribution on a frequently joined column co-locates rows with the same key on the same distribution, minimizing data movement during joins. For large tables, this significantly improves query performance. It is a recommended practice in dedicated SQL pool to distribute large fact tables on their common join key, such as customer ID, to reduce shuffling.

Why this answer

Hash-distributing large tables on the customer ID column co-locates rows for joins, reducing data movement. Partitioning large fact tables on the date column enables partition elimination for date filters. Together, these actions optimize the most common query patterns.

Other options either increase data movement or are not suitable for large-scale analytical workloads.

Exam trap

The trap here is assuming that any distribution or partitioning strategy improves performance; only those aligned with query patterns (join keys and filter columns) provide real benefits.

Practice this question →
60
MCQeasy

Which Azure storage solution is best suited for storing large volumes of unstructured data, such as log files and media files, and supports both hierarchical namespace and POSIX-like access control lists?

A.Azure Blob Storage
B.Azure Data Lake Storage Gen2
C.Azure Files
D.Azure SQL Database
AnswerB

Azure Data Lake Storage Gen2 satisfies the unstructured-data requirement by layering a hierarchical namespace over Blob Storage, enabling directory-level operations and POSIX-like ACLs. This directly meets the stem's constraints: massive log and media volumes, hierarchical namespace, and fine-grained POSIX access control that flat Blob Storage cannot provide.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with POSIX-like access control lists (ACLs) on top of Azure Blob Storage. This makes it ideal for storing large volumes of unstructured data (e.g., log files, media files) while supporting fine-grained, POSIX-compliant permissions and directory-level operations that are essential for big data analytics workloads.

Exam trap

The trap here is that candidates often choose Azure Blob Storage because it is the underlying storage for ADLS Gen2, but they overlook the key differentiators—hierarchical namespace and POSIX ACLs—that are exclusive to ADLS Gen2 and not available in standard Blob Storage.

Why the other options are wrong

A

Blob Storage supports unstructured data but does not provide a hierarchical namespace or POSIX ACLs by default.

C

Azure Files is for SMB file shares, not optimized for large-scale unstructured data.

D

Azure SQL Database is a relational database for structured data, not for unstructured data.

Practice this question →
61
Multi-Selectmedium

Which TWO Azure services can be used to implement a polyglot persistence architecture for an e-commerce application that requires both a relational database for orders and a document database for product catalogs?

Select 2 answers
A.Azure Cache for Redis
B.Azure SQL Database
C.Azure Cosmos DB
D.Azure Table Storage
E.Azure Data Lake Storage Gen2
AnswersB, C

Azure SQL Database supplies the relational engine for order processing, enforcing ACID transactions and referential integrity across order and line-item tables. It satisfies the stem's relational requirement, pairing with Azure Cosmos DB's document model to deliver polyglot persistence.

Why this answer

Azure SQL Database (B) is correct because it is a fully managed relational database engine based on SQL Server, providing ACID transactions, T-SQL, and relational schema enforcement needed for the orders workload. Azure Cosmos DB (C) is correct because it is a globally distributed, multi-model NoSQL service that natively supports the document (SQL/Core) API, making it suitable for storing product catalogs as JSON documents. Together they satisfy polyglot persistence by letting each workload use the data store best suited to it.

Azure Cache for Redis (A) is an in-memory key-value cache, not a durable relational or document database. Azure Table Storage (D) is a key-attribute NoSQL store but is not a document database in the sense required here. Azure Data Lake Storage Gen2 (E) is object storage optimized for analytics, not transactional relational or document workloads.

Exam trap

The trap here is that candidates often confuse Azure Table Storage with a document database, but Table Storage is a key-value store without native JSON support or rich querying, whereas Cosmos DB provides a true document database with SQL API and indexing.

Practice this question →
62
MCQeasy

A logistics company needs to store delivery tracking data that is updated frequently by multiple services. The solution must support transactions across multiple documents and provide real-time analytics. Which Azure service should you recommend?

A.Azure Table Storage
B.Azure Cosmos DB with SQL API
C.Azure Data Lake Storage Gen2
D.Azure Service Bus
AnswerB

Azure Cosmos DB with SQL API satisfies both constraints: multi-document transactions via server-side stored procedures and the transactional batch feature within a single logical partition, plus real-time analytics through the integrated change feed. Its schema-agnostic document model also suits frequently updated tracking records written concurrently by multiple services.

Why this answer

Azure Cosmos DB with SQL API is the correct choice because it provides multi-document transaction support (ACID within a logical partition) and real-time analytics via its change feed and integrated analytical store. This meets the requirement for frequent updates from multiple services while enabling low-latency reads for analytics, unlike other Azure storage options that lack transactional guarantees across documents or real-time query capabilities.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's single-entity transactions with multi-document support, or mistakenly think Azure Data Lake Storage Gen2 can handle transactional updates, when it is designed for append-heavy, analytical workloads.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage does not support multi-document transactions; it only offers single-entity transactions and lacks the ability to perform ACID operations across multiple documents. Option C is wrong because Azure Data Lake Storage Gen2 is optimized for large-scale batch analytics and data lake workloads, not for transactional updates or real-time analytics on frequently updated data. Option D is wrong because Azure Service Bus is a message broker for decoupling services and asynchronous communication, not a data store for transactional or analytical workloads.

Practice this question →
63
MCQmedium

You are designing a data lake on Azure Data Lake Storage Gen2. The data includes customer PII that must be encrypted at rest using customer-managed keys. Which feature should you enable?

A.Enable double encryption for Azure Storage
B.Configure Azure Storage encryption with customer-managed keys in Azure Key Vault
C.Enable infrastructure encryption
D.Use Azure Customer Lockbox for access control
AnswerB

Customer-managed keys in Azure Key Vault give the organisation control over the encryption key protecting PII at rest, satisfying the stated requirement. Microsoft-managed keys cannot meet that constraint because the customer never owns or rotates the key.

Why this answer

Azure Storage encryption with customer-managed keys (CMK) in Azure Key Vault allows you to control the encryption keys used to encrypt data at rest in Azure Data Lake Storage Gen2. This meets the requirement for encrypting PII with customer-managed keys, as CMK provides an extra layer of security by letting you manage key rotation, revocation, and access policies.

Exam trap

The trap here is that candidates confuse 'double encryption' or 'infrastructure encryption' with customer-managed key control, but only CMK gives you direct ownership of the encryption keys for data at rest.

How to eliminate wrong answers

Option A is wrong because double encryption for Azure Storage encrypts data twice (once at the service level and once at the infrastructure level) but does not inherently use customer-managed keys; it can use Microsoft-managed keys. Option C is wrong because infrastructure encryption uses platform-managed keys to encrypt the storage infrastructure, not customer-managed keys for the data itself. Option D is wrong because Azure Customer Lockbox provides access control for Microsoft support engineers to access your data, not encryption at rest with customer-managed keys.

Practice this question →
64
MCQeasy

You need to design a data storage solution for a batch processing pipeline that processes petabytes of data daily. The data is stored in Parquet format and must be accessible by both Azure Databricks and Azure Synapse Analytics. Which storage solution should you recommend?

A.Azure Data Lake Storage Gen2
B.Azure Files
C.Azure SQL Database
D.Azure Blob Storage
AnswerA

Azure Data Lake Storage Gen2 provides a hierarchical namespace over Blob Storage, which is the specific capability enabling efficient directory-level operations across petabytes. Its POSIX-like ACLs and native ABFS driver satisfy the stem's requirement that both Azure Databricks and Azure Synapse Analytics access the same Parquet data without copying or conversion.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, providing native POSIX-like access control and high throughput for petabyte-scale batch processing. Both Azure Databricks and Azure Synapse Analytics have optimized connectors for ADLS Gen2 that leverage the hierarchical namespace for efficient partition pruning and file listing, which is critical for Parquet-based analytics at this scale.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage (flat namespace) with ADLS Gen2 (hierarchical namespace), assuming both are equivalent for big data analytics, but the hierarchical namespace is a critical differentiator for performance at petabyte scale in batch pipelines.

How to eliminate wrong answers

Option B (Azure Files) is wrong because it is designed for SMB-based file sharing with low-latency access for small-to-medium workloads, not for petabyte-scale batch analytics, and lacks the throughput and native integration with Spark and Synapse pipelines required for Parquet data. Option C (Azure SQL Database) is wrong because it is a relational OLTP store optimized for transactional queries and small row-based operations, not for storing and processing petabytes of Parquet files in a batch pipeline. Option D (Azure Blob Storage) is wrong because while it can store Parquet files, it lacks a hierarchical namespace, which forces Azure Databricks and Synapse to use slower flat namespace listing operations (e.g., ListBlobs) that degrade performance at petabyte scale compared to ADLS Gen2's directory-aware operations.

Practice this question →
65
Multi-Selectmedium

Which TWO factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for migrating a legacy application? (Choose two.)

Select 2 answers
A.Support for active geo-replication
B.Authentication using Microsoft Entra ID
C.Need for SQL Server Agent jobs
D.Requirement for cross-database queries
E.Integration with Azure VNet
AnswersC, D

SQL Server Agent is available in Azure SQL Managed Instance but not in Azure SQL Database, so scheduled job requirements force the managed instance choice. This directly satisfies the stem's migration constraint: a legacy application depending on Agent jobs cannot run on Azure SQL Database without rewriting that scheduling layer.

Why this answer

Option C (Need for SQL Server Agent jobs) is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance, whereas Azure SQL Database does not include SQL Server Agent and instead requires alternatives such as Elastic Database Jobs or Azure Automation for scheduled task execution. Option D (Requirement for cross-database queries) is correct because Azure SQL Managed Instance supports three-part and cross-database queries within the same instance, while Azure SQL Database generally restricts cross-database queries unless you use elastic query features, making this a key differentiator for legacy applications. Option A is not a deciding factor because active geo-replication is available in both Azure SQL Database and Azure SQL Managed Instance.

Option B is not a deciding factor because Microsoft Entra ID authentication is supported by both services. Option E is not a deciding factor because both Azure SQL Database (via Private Link or VNet service endpoints) and Azure SQL Managed Instance can integrate with an Azure VNet, though the mechanisms differ.

Exam trap

The trap here is that candidates often assume VNet integration (Option E) is exclusive to Managed Instance, but Azure SQL Database also supports VNet integration via private endpoints, making it a non-differentiating factor for this migration decision.

Practice this question →
66
MCQmedium

You are designing a storage solution for a financial services company that uses Azure SQL Database. The database contains a table with sensitive customer data, including credit card numbers. Regulatory requirements mandate that the credit card numbers must be encrypted at rest and in use, and only authorized applications should be able to decrypt them. You need to implement a solution that allows encryption keys to be managed in Azure Key Vault. What should you use?

A.Row-level security (RLS) with a security policy on the customer table.
B.Always Encrypted with column master keys stored in Azure Key Vault.
C.Dynamic data masking on the credit card number column.
D.Transparent Data Encryption (TDE) with service-managed keys.
AnswerB

Always Encrypted protects data at rest and in use by encrypting sensitive columns on the client side, so the database engine never sees plaintext. Column master keys can be stored in Azure Key Vault, and only client applications with access to those keys can decrypt the data. This meets the requirements for encryption in use and authorized application access.

Why this answer

Always Encrypted encrypts sensitive columns on the client side, ensuring data is protected both at rest and in use. By storing column master keys in Azure Key Vault, key management is centralized and access is restricted to authorized applications. This satisfies the requirements for encryption in use and authorized decryption, which other options do not provide.

Exam trap

The trap here is confusing encryption at rest with encryption in use, leading to the selection of TDE or masking when the requirement is for client-side encryption.

Practice this question →
67
MCQhard

You need to assign permissions to a service principal so that it can write data to a specific container in Azure Data Lake Storage Gen2, but not delete blobs. The above JSON shows the built-in role 'Storage Blob Data Contributor'. The role includes delete permission in DataActions. What should you do?

A.Create a custom role that includes read and write DataActions but excludes the delete DataAction, then assign that custom role.
B.Assign the Storage Blob Data Contributor role and create a deny assignment that denies delete.
C.Assign the Storage Blob Data Contributor role and use ACLs to deny delete on the container.
D.Assign the Storage Blob Data Contributor role and remove the delete permission at the role assignment scope.
AnswerA

Storage Blob Data Contributor includes delete in its DataActions, so it cannot meet the no-delete constraint. A custom role defining only read and write DataActions, excluding delete, grants precisely the required container access without over-permissioning the service principal.

Why this answer

Azure RBAC roles are all-or-nothing at the permission level; you cannot selectively remove a single DataAction from a built-in role at assignment time. The only way to grant write access without delete is to create a custom role that explicitly includes the required read and write DataActions (e.g., Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write) and omits the delete DataAction (Microsoft.Storage/storageAccounts/blobServices/containers/blobs/delete). This custom role is then assigned to the service principal at the container scope, ensuring it can write data but never delete blobs.

Exam trap

The trap here is that candidates mistakenly believe you can modify a built-in role's permissions at assignment time (Option D) or that ACLs can override RBAC permissions (Option C), when in reality Azure requires a custom role for such granular control.

How to eliminate wrong answers

Option B is wrong because deny assignments cannot be used to selectively block a specific DataAction within a role assignment; deny assignments are designed to block all assignments of a role at a higher scope, not to carve out individual permissions. Option C is wrong because ACLs in Azure Data Lake Storage Gen2 are applied to the data plane for user/group identities, but they cannot override an RBAC role assignment that explicitly grants delete permission; RBAC takes precedence over ACLs when both are present. Option D is wrong because built-in roles like Storage Blob Data Contributor have fixed DataActions that cannot be modified at the role assignment scope; you cannot 'remove' a permission from a built-in role during assignment.

Practice this question →
68
MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The sales table is partitioned by month and has a clustered columnstore index. Over time, the performance of queries filtering on a specific month has degraded. The data engineer suspects high rowgroup elimination. Which action should be taken to improve performance?

A.Change the table distribution to hash-distributed on the partition key.
B.Reorganize or rebuild the columnstore index on the table.
C.Drop and recreate the affected partitions.
D.Update statistics on the partitioned column.
AnswerB

Reorganizing or rebuilding consolidates small rowgroups, improving partition elimination.

Why this answer

Reorganizing or rebuilding the columnstore index compresses fragmented rowgroups and merges small rowgroups into optimal sizes (typically 102,400 rows per rowgroup). This directly addresses the degraded rowgroup elimination: when rowgroups are too small or fragmented, the engine cannot efficiently skip entire rowgroups during partition-level scans, causing more I/O and slower performance.

Exam trap

The trap here is that candidates confuse rowgroup elimination (a columnstore physical storage concept) with partition elimination (a table design concept), and incorrectly choose partition-related actions like dropping partitions or updating statistics instead of addressing the columnstore index fragmentation directly.

How to eliminate wrong answers

Option A is wrong because changing the distribution to hash-distributed on the partition key does not fix rowgroup fragmentation; distribution affects data movement across distributions, not the internal rowgroup structure of columnstore indexes. Option C is wrong because dropping and recreating affected partitions is an overly aggressive operation that drops data and requires reloading; it does not target the root cause of fragmented rowgroups within the columnstore index. Option D is wrong because updating statistics on the partitioned column improves cardinality estimates for the query optimizer but does not repair the physical rowgroup layout that causes poor rowgroup elimination.

Practice this question →
69
MCQeasy

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

A.Azure Cosmos DB
B.Azure SQL Database
C.Azure Synapse Analytics
D.Azure Data Lake Storage Gen2
AnswerB

Azure SQL Database is a fully managed, serverless-capable relational engine supporting transactional (OLTP) workloads, with automatic scaling, patching and backups. It satisfies the serverless relational requirement, unlike Synapse or Cosmos DB, which target analytical or non-relational workloads respectively.

Why this answer

Azure SQL Database is a fully managed, serverless relational database service designed for transactional workloads. It provides built-in high availability, automatic scaling, and pay-per-use billing, making it ideal for OLTP scenarios without the need to manage underlying infrastructure.

Exam trap

The trap here is that candidates confuse 'fully managed serverless relational database' with Azure Cosmos DB (which is serverless but not relational) or Azure Synapse Analytics (which is relational but designed for analytics, not transactions), leading them to overlook the specific OLTP focus of Azure SQL Database.

Why the other options are wrong

A

Cosmos DB is a NoSQL database, not relational.

C

Synapse Analytics is for large-scale analytics, not transactional workloads.

D

Data Lake Storage is for big data analytics, not relational OLTP.

Practice this question →
70
MCQeasy

You need to store log files from multiple applications in a central location for long-term retention and occasional analysis. The data is rarely accessed after 30 days. Which storage solution should you use to minimize cost?

A.Azure Files
B.Azure Data Lake Storage Gen2
C.Azure Cosmos DB
D.Azure Blob Storage (cool or archive tier)
AnswerD

Azure Blob Storage cool or archive tiers satisfy the long-term retention and infrequent access constraints by storing data at lower cost than hot tier, while archive offers the cheapest per-GB rate for data rarely read. Lifecycle policies can automatically transition blobs after 30 days, minimising spend for occasional analysis.

Why this answer

Azure Blob Storage with cool or archive tier is the most cost-effective solution for storing log files that are rarely accessed after 30 days. The cool tier offers low storage costs with higher access costs, while the archive tier provides the lowest storage cost but requires rehydration for access, making both ideal for long-term retention and occasional analysis.

Exam trap

The trap here is that candidates may choose Azure Data Lake Storage Gen2 for its analytics capabilities, overlooking that blob storage tiers are specifically designed for cost-efficient long-term retention of infrequently accessed data.

How to eliminate wrong answers

Option A is wrong because Azure Files provides fully managed file shares using SMB protocol, which is designed for shared access and active workloads, not for cost-optimized long-term archival storage. Option B is wrong because Azure Data Lake Storage Gen2 is optimized for big data analytics with hierarchical namespace and high-throughput access, incurring higher storage costs than blob storage tiers for infrequently accessed data. Option C is wrong because Azure Cosmos DB is a NoSQL database with low-latency access and global distribution, designed for transactional workloads, not for cost-efficient long-term retention of log files.

Practice this question →
71
MCQmedium

You are designing a data storage solution for a global e-commerce company. The company needs to store clickstream data from millions of users with high write throughput and low-latency reads for real-time analytics. The data is semi-structured and includes nested JSON objects. Which Azure data store should you recommend?

A.Azure Cosmos DB
B.Azure Table Storage
C.Azure SQL Database
D.Azure Blob Storage
AnswerA

Azure Cosmos DB satisfies the high write throughput, low-latency read, and semi-structured nested JSON requirements through its schema-agnostic document model and partition-based horizontal scale. Its multi-region replication supports the global footprint, while the SQL API queries nested objects natively without transformation, unlike columnar or relational stores.

Why this answer

Azure Cosmos DB is the correct choice because it provides a multi-model, globally distributed database service with guaranteed single-digit-millisecond read and write latencies at the 99th percentile, making it ideal for high-throughput clickstream ingestion and real-time analytics. Its native support for semi-structured data and nested JSON objects via the SQL API (or MongoDB API) allows direct storage and querying of complex event payloads without schema flattening. Additionally, Cosmos DB offers automatic indexing and tunable consistency levels to balance performance and data freshness for global e-commerce scenarios.

Exam trap

The trap here is that candidates often choose Azure Table Storage because it is a NoSQL store, but they overlook its lack of native JSON support and sub-10ms latency guarantees, confusing its simple key-value model with the richer document capabilities of Cosmos DB.

How to eliminate wrong answers

Option B (Azure Table Storage) is wrong because it is a NoSQL key-value store that does not natively support nested JSON objects; it requires flattening complex structures into flat key-value pairs, which adds overhead and complicates real-time analytics on clickstream data. Option C (Azure SQL Database) is wrong because it is a relational database that enforces a fixed schema, making it poorly suited for semi-structured, schema-on-read clickstream data with varying nested JSON fields; it also cannot match Cosmos DB's sub-10ms write throughput at scale. Option D (Azure Blob Storage) is wrong because it is an object store designed for large, unstructured binary data (e.g., images, logs) and does not provide low-latency, indexed query capabilities for real-time analytics on individual clickstream events; it lacks native support for querying nested JSON without additional compute layers like Azure Data Lake or Synapse.

Practice this question →
72
MCQmedium

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

A.Use Azure SQL Database with clustered columnstore index on date and device ID.
B.Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.
C.Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.
D.Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.
AnswerB

ADLS Gen2 hierarchical namespace supports true directory semantics, so /YYYY/MM/DD/DeviceID/ paths let partition pruning skip irrelevant folders during queries. Date-first ordering suits time-range filters, while DeviceID narrows per-device scans, and timestamped filenames preserve ingestion order within each partition.

Why this answer

ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Exam trap

The trap here is that candidates confuse storage services (ADLS Gen2) with database or NoSQL solutions (SQL Database, Table Storage, Cosmos DB), failing to recognize that the question explicitly requires a data lake with a hierarchical namespace, which only ADLS Gen2 provides.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database with a clustered columnstore index is a relational store, not a data lake solution, and it does not support a hierarchical namespace or folder-based partitioning as required. Option C is wrong because Azure Table Storage is a NoSQL key-value store that lacks a hierarchical namespace and folder organization; its PartitionKey/RowKey model does not provide the folder-based partitioning by date and device ID needed for ADLS Gen2. Option D is wrong because Azure Cosmos DB is a globally distributed NoSQL database, not a data lake storage service, and its partition key on (date, device ID) does not create a hierarchical folder structure in ADLS Gen2.

Practice this question →
73
MCQeasy

A logistics company needs to store shipment tracking events in Azure Cosmos DB. Events are written continuously throughout the day, and the most common query pattern retrieves all events for a specific shipment ID ordered by timestamp. The workload is write-heavy and must scale horizontally across partitions. Which partition key should you choose?

A.Use the shipment ID as the partition key.
B.Use the event timestamp as the partition key.
C.Use the event type as the partition key.
D.Use a synthetic key formed by concatenating the event timestamp and a random GUID.
AnswerA

Shipment ID distributes writes across partitions while co-locating all events for a given shipment in one logical partition. The common query for a shipment's events ordered by timestamp becomes a single-partition query, which is efficient and cheap in request units.

Why this answer

The partition key should align with the dominant query pattern while distributing writes. Using shipment ID places all events for a shipment in the same logical partition, so the common query executes as a single-partition operation, and the high cardinality of shipment IDs spreads write traffic across physical partitions. Timestamp, event type, or random synthetic keys either cause cross-partition fan-out or create hot partitions.

Exam trap

The trap here is choosing a high-cardinality key purely for write distribution without checking whether it matches the most frequent query filter.

Practice this question →
74
MCQhard

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

A.Hash distribution on the most joined dimension key with clustered columnstore index
B.Round-robin distribution with clustered columnstore index
C.Replicated distribution with clustered columnstore index
D.Hash distribution on a dimension key with heap index
AnswerA

Hash distribution on the most joined dimension key co-locates matching rows on the same compute node, eliminating costly data movement during star schema joins. A clustered columnstore index compresses columnar fact data and delivers the high scan throughput that aggregation-heavy queries demand, directly minimising query latency.

Why this answer

Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Exam trap

The trap here is that candidates often choose round-robin distribution thinking it balances load evenly, but they overlook the severe join performance penalty caused by data movement across distributions in star schema queries.

How to eliminate wrong answers

Option B is wrong because round-robin distribution distributes rows evenly without considering join keys, causing excessive data shuffling across distributions during joins, which increases query latency. Option C is wrong because replicated distribution copies the entire table to each distribution node, which is impractical for large fact tables due to storage overhead and data movement during updates. Option D is wrong because a heap index lacks ordering and compression, leading to full table scans and poor query performance for star schema queries.

Practice this question →
75
MCQeasy

You need to store historical sales data for 10 years with infrequent queries. The storage cost must be minimized while retaining the ability to query using Azure Synapse serverless SQL pool. Which storage tier should you use?

A.Azure Storage Archive tier.
B.Azure Storage Premium tier.
C.Azure Storage Hot tier.
D.Azure Storage Cool tier.
AnswerD

The cool tier offers lower storage cost than hot while remaining fully queryable by Synapse serverless SQL pool, unlike archive which requires rehydration. This satisfies the minimise-cost constraint alongside infrequent query access over ten years.

Why this answer

The Cool tier is the correct choice because it provides low-cost storage for data that is infrequently accessed (e.g., historical sales data spanning 10 years) while still supporting immediate read access via Azure Synapse serverless SQL pool. Unlike the Archive tier, Cool tier data is online and can be queried without the need for time-consuming rehydration, making it suitable for infrequent but on-demand analytical queries.

Exam trap

The trap here is that candidates often confuse 'infrequent queries' with 'no queries' and incorrectly choose the Archive tier, forgetting that Azure Synapse serverless SQL pool cannot directly query archived data without a time-consuming rehydration process.

How to eliminate wrong answers

Option A is wrong because the Archive tier is designed for long-term backup and rarely accessed data, requiring a rehydration step (which can take up to 15 hours) before data can be queried by Azure Synapse serverless SQL pool, making it unsuitable for even infrequent queries. Option B is wrong because the Premium tier is optimized for low-latency, high-transaction workloads and is significantly more expensive, which contradicts the requirement to minimize storage cost. Option C is wrong because the Hot tier is intended for frequently accessed data and has higher storage costs than the Cool tier, so it does not meet the cost-minimization goal for infrequently queried historical data.

Practice this question →
Page 1 of 2 · 121 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Design and implement data storage questions.

Start 20-question session