Courseiva

CCNA Design and implement data storage Questions

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

Practice these questionsDomain overviewAll questions
76
MCQmedium

A media company ingests high-definition video files into Azure Data Lake Storage Gen2. The files are uploaded once and then read by multiple analytics jobs for 48 hours, after which they are deleted. The company wants to optimize read performance and reduce latency for the analytics jobs. Which storage configuration should you recommend?

A.Store the files as page blobs in a premium page blob storage account.
B.Store the video files as append blobs in a general-purpose v2 storage account.
C.Store the files in a general-purpose v1 storage account with the Hot access tier.
D.Enable hierarchical namespace and store the files as block blobs in a premium block blob storage account.
AnswerD

A premium block blob storage account is designed for high transaction rates and low latency, making it ideal for analytics workloads that read data frequently. Enabling hierarchical namespace provides Data Lake Storage Gen2 capabilities, such as a directory structure that improves query performance. Storing files as block blobs is appropriate for large video files, and the premium tier ensures fast reads.

Why this answer

Premium block blob storage accounts provide low latency and high throughput, which are critical for analytics jobs reading large video files. Enabling hierarchical namespace (Data Lake Storage Gen2) adds a directory structure that improves query efficiency. Block blobs are the correct type for large files.

This combination optimizes read performance and reduces latency for the 48-hour analytics window.

Exam trap

The trap here is confusing premium page blob storage (for VM disks) with premium block blob storage (for high-performance analytics and media), leading to an incorrect storage account choice.

Practice this question →
77
Multi-Selecthard

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Select 3 answers
A.Align distribution keys on tables that are frequently joined together
B.Minimize data skew by choosing a distribution key with many unique values
C.Use round-robin distribution for large fact tables to distribute data evenly
D.Use replicated tables for large tables to avoid data movement
E.Consider the size of the table and the frequency of joins
AnswersA, B, E

Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance.

Why this answer

Option A is correct because co-locating the distribution key on tables that are frequently joined ensures matching rows land on the same distribution, letting joins execute locally and eliminating costly shuffle (data movement) operations. Option B is correct because a distribution key with many unique values spreads rows evenly across the 60 distributions, minimizing data skew that would otherwise create hot distributions and slow query performance. Option E is correct because table size and join frequency are the core drivers of the distribution decision: small tables may be replicated, while large frequently-joined tables should be hash-distributed on a shared key.

Option C is not correct because round-robin is generally recommended for staging or temporary tables, not large fact tables, which benefit from hash distribution on a join/filter column. Option D is not correct because replicated tables are intended for small dimension tables (roughly under 2 GB compressed); replicating large tables consumes excessive storage and rebuild time on every write.

Exam trap

The trap here is that candidates often confuse round-robin distribution as a good choice for large fact tables because it distributes data evenly, but they overlook the severe performance penalty from data movement during joins and aggregations.

Practice this question →
78
MCQeasy

A data engineer needs to store log data from multiple applications in Azure. The data is append-only, heavily compressed, and queried infrequently. Cost minimization is critical. Which storage solution is best?

A.Azure Table Storage
B.Azure Cosmos DB with analytical store
C.Azure Blob Storage with cool or archive access tier
D.Azure Data Lake Storage Gen2 with hot tier
AnswerC

Cool and Archive tiers suit append-only, infrequently queried log data because they trade higher access latency and per-operation costs for substantially lower storage pricing. Archive offers the cheapest per-GB rate, directly satisfying the cost-minimisation constraint while retaining compressed blobs.

Why this answer

Azure Blob Storage with cool or archive access tier is the best choice because the data is append-only, heavily compressed, and infrequently queried, making cost minimization the top priority. The cool tier offers low storage costs with higher access charges, while the archive tier provides the lowest storage cost for data that is rarely accessed and can tolerate hours of retrieval latency. This aligns perfectly with the append-only, infrequently queried nature of the log data.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage's access tiers with Data Lake Storage Gen2's tiers, assuming the hot tier is always the default for log data, but the question's emphasis on 'cost minimization' and 'infrequently queried' explicitly points to cool or archive tiers, not the hot tier.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for structured, semi-structured data with frequent point queries, not for large, append-only, compressed log blobs; it lacks the cost-optimized access tiers needed for infrequent access. Option B is wrong because Azure Cosmos DB with analytical store is a globally distributed, multi-model database optimized for low-latency transactional and analytical workloads, which is over-engineered and costly for append-only, infrequently queried log data; its analytical store is designed for near-real-time analytics, not cold storage. Option D is wrong because Azure Data Lake Storage Gen2 with hot tier is optimized for high-frequency access and big data analytics, with higher storage costs than cool or archive tiers, making it unsuitable for cost minimization when data is infrequently queried.

Practice this question →
79
MCQeasy

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

A.Use Azure Data Lake Storage Gen2 for both transactional and historical data
B.Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data
C.Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data
D.Use Azure SQL Database with Hyperscale tier for both transactional and historical data
AnswerC

Azure Cosmos DB delivers single-digit-millisecond reads for the transactional workload, satisfying the sub-10 ms latency constraint, while Azure Blob Storage's low-cost tiers hold the large historical dataset cheaply. Splitting hot and cold data this way minimises overall storage spend without compromising query performance.

Why this answer

Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Exam trap

The trap here is that candidates may assume a single service like Azure SQL Database or Data Lake Storage can handle both transactional and analytical workloads efficiently, overlooking the cost and performance trade-offs that make a hybrid approach optimal.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is optimized for big data analytics, not low-latency transactional queries, and cannot guarantee under 10ms response times. Option B is wrong because Azure Cache for Redis is an in-memory cache, not a durable transactional store, and Azure SQL Database for historical data incurs higher storage costs compared to Blob Storage. Option D is wrong because Azure SQL Database Hyperscale, while scalable, is more expensive for large historical data storage and does not minimize costs as effectively as Blob Storage.

Practice this question →
80
Multi-Selecteasy

Which TWO of the following Azure services can be used to orchestrate data pipelines that include data transformation?

Select 2 answers
A.Azure Synapse Pipelines
B.Azure Data Factory
C.Azure Logic Apps
D.Azure Databricks
E.Azure Functions
AnswersA, B

Synapse Pipelines provides orchestration with built-in data transformation activities, including Mapping Data Flows and notebook execution, within the Synapse workspace. It schedules and coordinates pipeline activities, satisfying the requirement to orchestrate pipelines that include transformation.

Why this answer

Azure Synapse Pipelines (A) is correct because it is the built-in pipeline orchestration engine in Azure Synapse Analytics, providing the same Data Factory-style activities (Copy, Data Flow, Stored Procedure, Notebook) that let you schedule and orchestrate data movement with transformation steps such as Mapping Data Flows. Azure Data Factory (B) is correct because it is Azure's dedicated cloud ETL/ELT orchestration service, where pipelines chain activities like Copy, Mapping Data Flow, Databricks notebook, and stored procedure activities to move and transform data. Azure Logic Apps (C) is a workflow/automation service for app and system integration, not a data pipeline orchestrator with transformation activities.

Azure Databricks (D) is an Apache Spark analytics/compute platform that performs transformations but is typically invoked as an activity within a pipeline rather than being the orchestrator itself. Azure Functions (E) is a serverless compute service for running event-driven code, not a data pipeline orchestration service.

Exam trap

The trap here is that candidates often confuse compute services (like Databricks or Functions) with orchestration services, mistakenly thinking they can replace Azure Data Factory or Synapse Pipelines for end-to-end pipeline management, when in fact they are typically used as activities within an orchestrated pipeline.

Practice this question →
81
MCQeasy

You need to store semi-structured JSON data from a web application that requires low-latency reads and writes at a global scale. The data must be indexed automatically and support SQL-like queries. Which Azure data store should you use?

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

Azure Cosmos DB (NoSQL API) natively stores semi-structured JSON documents, automatically indexes every property without schema definitions, and offers single-digit-millisecond latency with global multi-region distribution. Its SQL-like query syntax over the NoSQL API satisfies the stem's requirements for automatic indexing, low-latency reads and writes at global scale, and SQL-like querying.

Why this answer

Azure Cosmos DB with the NoSQL API is the correct choice because it natively stores semi-structured JSON documents, provides automatic indexing of all properties, supports SQL-like queries via its query engine, and offers low-latency reads and writes at global scale through multi-region replication and configurable consistency levels. This combination directly matches the requirements for a globally distributed web application needing fast, queryable JSON storage.

Exam trap

The trap here is that candidates often confuse Azure Table Storage's key-value capabilities with Cosmos DB's document model, mistakenly thinking Table Storage supports SQL-like queries and automatic indexing, when in fact it only supports OData queries and requires explicit partition and row keys for efficient access.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational database that requires a fixed schema and is not optimized for semi-structured JSON data without manual schema management or JSON functions, nor does it provide automatic indexing of all JSON properties. Option B is wrong because Azure Blob Storage is an object store for unstructured data that does not support SQL-like queries or automatic indexing; it requires a separate compute layer (e.g., Azure Data Lake Analytics) for querying. Option D is wrong because Azure Table Storage is a NoSQL key-value store that does not support SQL-like queries, automatic indexing of all fields, or native JSON document storage; it uses OData queries and a flat schema.

Practice this question →
82
MCQeasy

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

A.Denies storage accounts that do not have any IP rules defined
B.Denies storage accounts that have firewall rules configured
C.Denies storage accounts that have public network access disabled
D.Denies storage accounts that allow public network access from all networks
AnswerD

The policy's `Deny` effect blocks creation or update of storage accounts whose network ACL default action permits access from all networks, satisfying the stem's network-security enforcement constraint. It evaluates the `networkAcls.defaultAction` property, rejecting any resource where that value equals `Allow` rather than `Deny`.

Why this answer

The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Exam trap

The trap here is that candidates confuse the 'defaultAction' property with the presence of IP rules or firewall settings, leading them to think the policy denies accounts with any firewall rules rather than those that allow all networks.

How to eliminate wrong answers

Option A is wrong because the policy does not evaluate the presence or absence of IP rules; it only checks the 'defaultAction' property. Option B is wrong because the policy denies accounts that allow all networks, not those with firewall rules configured; firewall rules are a separate mechanism. Option C is wrong because the policy denies accounts where public network access is enabled (defaultAction = 'Allow'), not disabled; disabling public access would set defaultAction to 'Deny', which the policy does not target.

Practice this question →
83
MCQhard

A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?

A.Change the distribution to round-robin
B.Replicate the table to all distributions
C.Create a columnstore index on OrderDate
D.Change the distribution to hash on OrderDate
AnswerD

Aligns distribution with filter column, minimizing data movement.

Why this answer

Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.

Exam trap

The trap here is that candidates often confuse indexing (columnstore) with distribution strategy, assuming a non-clustered index on the filter column is sufficient, when in fact the distribution key must match the most frequent filter predicate to avoid full distribution scans.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without any logical grouping, which forces full table scans and data shuffling for all queries, making performance worse for filtered queries. Option B is wrong because replicating a large fact table to all distributions would consume excessive storage and cause significant overhead during data loading, and is typically reserved for small dimension tables. Option C is wrong because a columnstore index on OrderDate improves compression and scan efficiency but does not address the distribution mismatch; queries would still need to scan all distributions, missing the benefit of distribution elimination.

Practice this question →
84
Multi-Selectmedium

Which of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2? (Choose two.)

Select 2 answers
A.Azure Storage Service Encryption (SSE) with Microsoft-managed keys
B.Azure Active Directory (Azure AD) authentication for storage accounts
C.Customer-managed keys stored in Azure Key Vault
D.Configure firewall rules to restrict IP access
AnswersA, C

Why this answer

Azure Storage Service Encryption (SSE) with Microsoft-managed keys encrypts data at rest automatically for Azure Data Lake Storage Gen2 using 256-bit AES encryption. This is enabled by default for all storage accounts, ensuring data written to disk is encrypted before being persisted, with no additional configuration required.

Exam trap

The trap here is confusing network security controls (like firewalls or Azure AD authentication) with data-at-rest encryption methods, leading candidates to select options that protect access rather than the stored data itself.

Why the other options are wrong

B

Azure AD authentication controls access, not encryption at rest.

D

Firewall rules control network access, not encryption at rest.

Practice this question →
85
MCQhard

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. The company also requires that the encryption keys are automatically rotated every 90 days. You need to configure the storage account to meet these requirements. What should you do?

A.Use Azure Disk Encryption with customer-managed keys and schedule a runbook to rotate the keys every 90 days.
B.Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a key rotation policy in Azure Key Vault.
C.Enable infrastructure encryption on the storage account and use Microsoft-managed keys for automatic rotation.
D.Configure the storage account to use customer-managed keys from Azure Key Vault and set a key rotation policy in Azure Key Vault.
AnswerD

Azure Storage supports customer-managed keys stored in Azure Key Vault. By configuring the storage account to use a key from Key Vault and setting a rotation policy on that key, you can automatically rotate the key every 90 days. The storage account will use the latest key version, ensuring encryption with rotated keys.

Why this answer

To use customer-managed keys with Azure Data Lake Storage Gen2, you configure the storage account to reference a key in Azure Key Vault. Azure Key Vault supports automatic key rotation policies, which can rotate the key every 90 days. The storage account automatically uses the new key version.

This meets both encryption at rest with CMK and automatic rotation requirements.

Exam trap

The trap here is confusing Azure Disk Encryption with storage account encryption, or assuming that Microsoft-managed keys can have custom rotation policies.

Practice this question →
86
Multi-Selectmedium

Which TWO actions should you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool when working with large fact tables?

Select 2 answers
A.Use replicated distribution for the fact table.
B.Use round-robin distribution to evenly distribute data.
C.Create statistics on columns used in WHERE clauses.
D.Use clustered index instead of columnstore index.
E.Implement table partitioning on a date column.
AnswersC, E

Creating statistics on filtered columns gives the dedicated SQL pool's query optimiser accurate cardinality estimates, enabling it to choose better join strategies and avoid full table scans. This directly satisfies the stem's large fact table constraint, where stale or missing statistics cause poor distribution-aware execution plans and inflated data movement.

Why this answer

Option C is correct because creating statistics on columns used in WHERE clauses gives the dedicated SQL pool's query optimizer accurate cardinality estimates, enabling better join orders and scan strategies for large fact tables. Option E is correct because partitioning a large fact table on a date column enables partition elimination, so queries filtering on that date range scan only relevant partitions instead of the entire table. Options A and B are incorrect: replicated distribution copies the full fact table to every compute node, which is meant for small dimension tables and would be prohibitively expensive for large fact tables, while round-robin distribution is a generic fallback that does not align data for joins and causes costly data movement.

Option D is incorrect because clustered columnstore indexes are the recommended default for large fact tables in dedicated SQL pools, delivering high compression and fast analytical scans, whereas a clustered (rowstore) index is generally inferior for this workload.

Exam trap

The trap here is that candidates often confuse distribution methods (replicated, round-robin, hash) with performance tuning for large fact tables, overlooking that statistics maintenance is a critical and separate optimization step that directly impacts query plan quality.

Practice this question →
87
MCQhard

A retail analytics team stores Parquet files in Azure Data Lake Storage Gen2 partitioned by year, month, and day. Queries in Azure Synapse serverless SQL pools filter on a transaction date column, but performance is poor because the engine scans all files in the folder hierarchy. You need to reduce the amount of data scanned without changing the file layout. What should you implement?

A.Use the filepath() function in the OPENROWSET query to filter on the year, month, and day segments of the folder path.
B.Enable result set caching on the serverless database and set the cache retention period to seven days.
C.Create an external data source and external table over the folder and query the external table instead of using OPENROWSET directly.
D.Create a partitioned table in a dedicated SQL pool and load the Parquet files into it using PolyBase.
AnswerA

The filepath() function exposes virtual columns derived from the folder path, so filtering on those columns lets the serverless engine eliminate entire folders and read only matching files. This reduces scanned data without moving or rewriting the Parquet files.

Why this answer

Files stored in a year/month/day hierarchy can be pruned by exposing the path segments as virtual columns through the filepath() function and filtering on them. The serverless engine then skips folders that do not match, cutting the amount of data scanned. External tables without partition elimination, dedicated pool loading, or result set caching do not address the scan volume for arbitrary filtered queries.

Exam trap

The trap here is believing that simply querying through an external table automatically prunes partitions, when elimination requires explicit path-based predicates such as filepath().

Practice this question →
88
Matchingmedium

Match each Azure service to its primary purpose in a data engineering pipeline.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Scalable data lake for analytics workloads

Unified analytics platform with SQL and Spark

Cloud-based ETL and data integration service

Real-time stream processing service

Apache Spark-based analytics platform

Why these pairings

The correct matches are: Azure Data Lake Storage Gen2 as a scalable data lake, Azure Synapse Analytics as a unified data warehousing platform, Azure Data Factory for data integration and orchestration, and Azure Databricks for Spark-based analytics. Common confusions include swapping storage and integration services, or mistaking data lakes for data warehousing.

Practice this question →
89
MCQmedium

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

A.Azure Cosmos DB
B.Azure SQL Database
C.Azure Synapse Analytics
D.Azure Table Storage
AnswerB

Azure SQL Database provides ACID transactions with strong consistency, supports frequent updates, and handles complex multi-table joins through its relational engine. This satisfies the transactional integrity and query complexity requirements, which analytical or key-value stores would not meet.

Why this answer

Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its low-latency and global distribution capabilities, overlooking that it does not provide native relational joins or the strong consistency required for transactional workloads, which Azure SQL Database is specifically designed for.

Why the other options are wrong

A

Cosmos DB is NoSQL and while it can be configured for strong consistency, it does not natively support complex joins across multiple tables as efficiently as a relational database.

C

Synapse is a data warehouse for analytics, not designed for transactional workloads with frequent updates.

D

Table Storage is a NoSQL key-value store with limited query capabilities and no support for complex joins.

Practice this question →
90
Multi-Selecthard

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Select 2 answers
A.Using INSERT INTO VALUES
B.Using BCP utility
C.Using the COPY statement
D.Using SQL Server Integration Services (SSIS)
E.Using PolyBase to load from Azure Blob Storage
AnswersC, E

The COPY statement is a modern, high-performance bulk load method, but it is not the only valid option; the question requires exactly two answers.

Why this answer

For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Exam trap

Do not confuse supported utilities with recommended bulk loading methods. BCP and SSIS can connect to Synapse SQL Pool, but for high-volume bulk loads, COPY and PolyBase are the recommended approaches.

Practice this question →
91
MCQhard

You are designing a data lake architecture for a healthcare company. The solution must support fine-grained access control at the file level, encryption at rest and in transit, and integration with Microsoft Purview for data lineage. Which storage solution should you recommend?

A.Azure NetApp Files.
B.Azure Files.
C.Azure Data Lake Storage Gen2 (ADLS Gen2).
D.Azure Blob Storage.
AnswerC

ADLS Gen2 combines a hierarchical namespace with POSIX ACLs for file-level access control, plus encryption at rest and TLS in transit. Its native integration with Microsoft Purview supplies the required data lineage, meeting all three healthcare compliance constraints in the scenario.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with POSIX-like access control lists (ACLs) for fine-grained file-level permissions, supports encryption at rest (Azure Storage Service Encryption) and in transit (TLS 1.2+), and natively integrates with Microsoft Purview for automated data lineage and cataloging. This makes it ideal for healthcare scenarios requiring strict compliance and auditability.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with ADLS Gen2, assuming blob storage's container-level permissions are sufficient for file-level control, but the hierarchical namespace and POSIX ACLs are exclusive to ADLS Gen2 and required for the fine-grained access described.

How to eliminate wrong answers

Option A is wrong because Azure NetApp Files provides NFS/SMB file shares with ACLs but lacks native integration with Microsoft Purview for data lineage and is not optimized for large-scale analytics workloads like data lakes. Option B is wrong because Azure Files offers SMB file shares with ACLs but does not support the hierarchical namespace or POSIX ACLs needed for fine-grained file-level control in a data lake, and its Purview integration is limited compared to ADLS Gen2. Option D is wrong because Azure Blob Storage provides encryption and Purview integration but lacks a hierarchical namespace and POSIX ACLs, making it impossible to enforce fine-grained access control at the individual file level.

Practice this question →
92
MCQhard

A data engineering team uses Azure Data Factory to load data from Azure SQL Database to Azure Data Lake Storage Gen2. They notice that the pipeline runs fail intermittently due to transient errors. They need to implement a retry policy with exponential backoff. What is the most efficient way to achieve this?

A.Use a 'Validation' activity before the copy to check source availability
B.Create a custom .NET activity to handle retries
C.Add a 'Until' loop with a wait activity in the pipeline
D.Configure the 'Retry' property on the copy activity with a count and exponential backoff interval
AnswerD

The copy activity's retry property automatically reattempts failed runs, and its exponential backoff interval progressively lengthens the delay between attempts, absorbing transient faults without custom pipeline logic. This is the native, most efficient mechanism for the stated requirement.

Why this answer

Azure Data Factory natively supports configuring a 'Retry' property on activities, including Copy activities, with an exponential backoff interval. This built-in mechanism automatically retries the activity upon transient failures without requiring custom logic, making it the most efficient and maintainable approach for handling intermittent errors.

Exam trap

The trap here is that candidates may overcomplicate the solution by choosing a custom loop or validation activity, overlooking that Azure Data Factory's native 'Retry' property with exponential backoff is the simplest and most efficient built-in mechanism for handling transient errors.

How to eliminate wrong answers

Option A is wrong because a 'Validation' activity only checks source availability before the copy starts; it does not retry the copy operation itself if a transient error occurs during data transfer. Option B is wrong because creating a custom .NET activity introduces unnecessary complexity, development overhead, and maintenance burden when Azure Data Factory already provides a native retry feature. Option C is wrong because an 'Until' loop with a wait activity requires manual implementation of retry logic and exponential backoff, which is less efficient and more error-prone than using the built-in 'Retry' property.

Practice this question →
93
MCQmedium

You are designing a data lake architecture using Azure Data Lake Storage Gen2. The data will be ingested from multiple sources with varying schemas. You need to organize the data in a way that supports both batch and streaming analytics while maintaining data lineage. Which folder structure convention should you use?

A.Organize by ingestion date only, with subfolders for each source.
B.Organize by source system, then by date.
C.Use a medallion architecture with three layers: bronze (raw), silver (cleaned), gold (aggregated).
D.Organize by file format (CSV, Parquet, JSON) and date.
AnswerC

The medallion architecture separates raw ingestion (bronze), validated and cleaned data (silver), and aggregated business-ready data (gold), accommodating varying source schemas while supporting both batch and streaming consumption. Each layer preserves lineage as data progressively transforms.

Why this answer

The medallion architecture (bronze, silver, gold) is the recommended pattern for Azure Data Lake Storage Gen2 when handling multiple sources with varying schemas. It supports both batch and streaming by storing raw data in bronze, applying incremental transformations in silver, and serving aggregated views in gold, while maintaining data lineage through clear layer boundaries and audit columns.

Exam trap

The trap here is that candidates often choose Option B (source then date) because it seems logical for organization, but they overlook the requirement to support both batch and streaming analytics while maintaining data lineage, which the medallion architecture explicitly addresses through layered transformations.

How to eliminate wrong answers

Option A is wrong because organizing by ingestion date only, with subfolders for each source, lacks schema evolution support and makes it difficult to trace data lineage across transformations. Option B is wrong because organizing by source system then by date does not provide a standardized processing pipeline for both batch and streaming, and it fails to separate raw, cleaned, and aggregated states. Option D is wrong because organizing by file format and date ignores the need for schema management and lineage tracking, and it does not facilitate incremental processing or data quality checks across layers.

Practice this question →
94
Matchingmedium

Match each Azure security feature to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Role-based access control for Azure resources

Cloud-based identity and access management service

Manage cryptographic keys and secrets

Private connectivity to Azure services over VNet

Why these pairings

The correct matches are: Azure AD for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Azure AD with Managed Identity and Key Vault with RBAC.

Practice this question →
95
Multi-Selectmedium

A company is designing a data storage solution for a global application that requires low-latency reads and writes for user session data. The solution must support automatic failover across multiple Azure regions. Which TWO Azure services meet these requirements?

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

Supports geo-replication and automatic failover.

Why this answer

Azure Cache for Redis is correct because it provides an in-memory data store with sub-millisecond latency for both reads and writes, making it ideal for user session data. It supports automatic failover across Azure regions through geo-replication, where data from a primary cache is asynchronously replicated to a secondary cache in a paired region, ensuring high availability and disaster recovery.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's multi-region writes with the specific requirement for low-latency session data, but Cosmos DB, while supporting automatic failover, has higher latency than an in-memory cache like Redis for frequent, small reads and writes typical of session state.

Practice this question →
96
Multi-Selecteasy

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Select 2 answers
A.SQL Server Integration Services (SSIS) package
B.Azure Data Factory with incremental copy
C.PolyBase from external table
AnswersA, B

SQL Server Integration Services is a valid method because it is a full-featured ETL tool that can connect to on-premises SQL Server and load data directly into Azure Synapse Analytics using connectors like the Synapse destination adapter.

Why this answer

Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion.

PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Exam trap

The trap is that candidates may view SSIS as outdated or legacy, but it remains a fully supported and effective method for loading data into Azure Synapse Analytics. Meanwhile, PolyBase is often mistakenly thought of as a loading method, but it is primarily a query engine for external data.

Practice this question →
97
MCQeasy

A logistics company uses Azure Blob Storage to store shipping manifests as block blobs. The manifests are accessed frequently for the first 30 days, then rarely accessed for the next 60 days, and after 90 days they must be retained for seven years for compliance but are almost never accessed. You need to minimize storage costs while ensuring the data remains available for compliance audits. What should you do?

A.Create a lifecycle management policy that deletes blobs after 90 days and stores them in Azure Backup for seven years.
B.Move blobs to the Archive tier after 30 days and keep them there for seven years.
C.Enable soft delete and versioning, and leave all blobs in the Hot tier for seven years.
D.Move blobs to the Cool access tier after 30 days, then to the Archive tier after 90 days.
AnswerD

The Cool tier is designed for data that is infrequently accessed but still requires rapid retrieval, which matches the 30-90 day period. The Archive tier is for data that is rarely accessed and can tolerate hours of retrieval latency, making it ideal for long-term compliance retention beyond 90 days. This tiering strategy minimizes storage costs while keeping data available, though Archive retrieval may take hours.

Why this answer

Azure Blob Storage access tiers optimize costs based on access frequency. The Cool tier is cost-effective for data accessed infrequently but still requiring quick retrieval, suitable for the 30-90 day period. The Archive tier offers the lowest storage cost for data that can tolerate hours of retrieval latency, ideal for long-term compliance retention.

Transitioning blobs through these tiers minimizes costs while ensuring availability for audits.

Exam trap

The trap here is moving data to Archive too early, which incurs early deletion penalties and makes data inaccessible for the period when it is still occasionally needed.

Practice this question →
98
MCQhard

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

A.Create external tables with statistics on relevant columns.
B.Create clustered columnstore indexes on the external tables.
C.Convert the Parquet files to CSV format for faster reads.
D.Partition the data into many small files.
AnswerA

Creating external tables with statistics on relevant columns lets the serverless SQL pool use metadata to build better execution plans, pruning row groups and reducing data scanned. This directly addresses the slow Parquet query constraint, since statistics on join and filter columns cut the bytes read per query.

Why this answer

In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Exam trap

The trap here is that candidates confuse external table capabilities with dedicated SQL pool features, assuming that indexes like columnstore can be applied to external tables, or that file format changes (CSV) or file count adjustments are the primary performance levers, when in fact statistics are the critical missing piece for serverless SQL pool optimization.

How to eliminate wrong answers

Option B is wrong because clustered columnstore indexes are not supported on external tables in serverless SQL pool; they are only applicable to tables in dedicated SQL pools. Option C is wrong because CSV format is slower than Parquet for analytical queries due to lack of compression, columnar storage, and predicate pushdown; converting to CSV would degrade performance. Option D is wrong because partitioning data into many small files increases metadata overhead and file open operations, which slows down queries in serverless SQL pool; optimal performance is achieved with a moderate number of reasonably sized files.

Practice this question →
99
MCQhard

You are designing a data storage solution for a global retail company that uses Azure Synapse Analytics dedicated SQL pool. The fact table is partitioned by date and contains 10 years of sales data. You need to implement a rolling window that keeps only the most recent 3 years of data while loading new daily data with minimal impact on concurrent queries. What should you do?

A.Use PolyBase to load new data directly into the main table and truncate the oldest partition.
B.Use partition switching to load new data into a staging table, switch it into the main table, and switch out the oldest partition to a staging table for deletion.
C.Use CREATE TABLE AS SELECT (CTAS) to rebuild the entire fact table with only the most recent 3 years of data each day.
D.Use DELETE statements to remove data older than 3 years and INSERT statements to add new daily data.
AnswerB

Partition switching is a metadata operation that moves a partition from one table to another almost instantaneously. By loading new data into a staging table and then switching it in, you avoid expensive data movement and minimize impact on concurrent queries. Switching out the oldest partition to a staging table and then dropping it efficiently removes old data. This approach is the recommended method for rolling window scenarios in dedicated SQL pools.

Why this answer

Partition switching is the most efficient way to implement a rolling window in a dedicated SQL pool. It allows new data to be loaded into a staging table and then switched into the main table as a metadata operation, and the oldest partition can be switched out and dropped. This minimizes data movement and lock contention, preserving performance for concurrent queries.

Exam trap

The trap here is assuming that DELETE or CTAS are acceptable for daily rolling windows; they cause heavy data movement and locking, whereas partition switching is a metadata-only operation.

Practice this question →
100
MCQmedium

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

A.Transition to cool tier after 30 days; delete after 7 years.
B.Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).
C.Transition to archive tier after 30 days; delete after 7 years.
D.Transition to cool tier after 30 days; transition to cool tier again after 365 days.
AnswerB

Lifecycle management rules apply tier transitions and deletion by last-modified age, so cool at 30 days, archive at 365 days and deletion at 2555 days exactly match the stated retention and cost requirements for the rarely accessed log data.

Why this answer

It aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Exam trap

The trap here is that candidates may confuse the required tiering order (cool then archive) with direct archiving after 30 days (Option C) or fail to include a deletion rule (Option D), missing the 7-year compliance requirement.

How to eliminate wrong answers

Option A is wrong because it deletes the data after 7 years but does not include a transition to the archive tier after 1 year, which is required by the compliance policy to archive data after 365 days. Option C is wrong because it transitions to archive tier after only 30 days, which violates the requirement to keep data in a lower-cost tier (cool) for the first year before archiving. Option D is wrong because it transitions to cool tier again after 365 days, which does not archive the data as required, and it lacks a deletion rule for the 7-year retention period.

Practice this question →
101
Multi-Selecthard

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Select 3 answers
A.Store data in Parquet format
B.Partition files by date to enable partition elimination
C.Compress files using snappy or gzip
D.Use many small files (under 64 MB) to increase parallelism
E.Store data in nested folder structures for better organization
AnswersA, B, C

Why this answer

Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Exam trap

The trap here is that candidates often confuse file size optimization with parallelism, assuming smaller files increase parallelism, but in serverless SQL, too many small files cause excessive metadata requests and reduce throughput, while larger files enable better batch processing.

Why the other options are wrong

D

Small files cause overhead; larger files (128 MB+) are recommended.

E

Deeply nested folders increase file listing time, impacting performance.

Practice this question →
102
MCQeasy

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

A.Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.
B.Azure Data Factory mapping data flows.
C.Azure Cosmos DB Core (SQL) API.
D.Azure SQL Database with JSON columns.
AnswerA

Synapse serverless SQL pool queries JSON files in ADLS Gen2 using T-SQL with OPENROWSET, applying schema-on-read so no ingestion or schema definition is required. This satisfies both the T-SQL query requirement and the schema-on-read constraint for semi-structured logs.

Why this answer

Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Exam trap

The trap here is that candidates often confuse schema-on-read with schema-on-write, picking Azure SQL Database or Cosmos DB because they support JSON, but those require predefined schemas or containers, failing the schema-on-read requirement.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory mapping data flows are designed for ETL/ELT transformations, not for direct T-SQL querying of data at rest. Option C is wrong because Azure Cosmos DB Core (SQL) API stores data as JSON but does not support schema-on-read; it requires a defined container schema and uses its own SQL dialect, not standard T-SQL. Option D is wrong because Azure SQL Database with JSON columns requires a predefined table schema and does not support schema-on-read for external files; it stores JSON in relational columns, not as files.

Practice this question →
103
MCQeasy

A healthcare organization needs to store electronic health records (EHR) in a format that supports schema flexibility and complex nested data. The solution must allow fast queries by patient ID and enable analytics with Azure Synapse. Which data store should you choose?

A.Azure Table Storage
B.Azure Data Lake Storage Gen2 with files in JSON format
C.Azure Cosmos DB with analytical store enabled
D.Azure SQL Database with JSON columns
AnswerC

Azure Cosmos DB with analytical store enabled satisfies the schema-flexibility and nested-data requirements through its schema-agnostic JSON document model, while partitioning on patient ID delivers fast point reads. The analytical store provides columnar, Synapse-linked querying without impacting transactional throughput, meeting the analytics constraint.

Why this answer

Azure Cosmos DB with analytical store enabled is the correct choice because it provides schema flexibility for complex nested EHR data, supports fast point reads by patient ID via its indexed partition key, and the analytical store enables efficient analytics with Azure Synapse through the Synapse Link feature, which automatically synchronizes operational data into a columnar format optimized for large-scale queries.

Exam trap

The trap here is that candidates often choose Azure SQL Database with JSON columns (Option D) because they assume relational databases can handle JSON, but they overlook the requirement for schema flexibility and native analytical store integration, which Cosmos DB with analytical store uniquely provides for hybrid transactional/analytical processing (HTAP) workloads.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store that does not support complex nested data structures or schema flexibility for hierarchical EHR records, and it lacks native integration with Azure Synapse for analytics. Option B is wrong because while Azure Data Lake Storage Gen2 with JSON files can store nested data, it does not provide fast point queries by patient ID without additional indexing or processing, and it requires separate ETL for analytics rather than real-time analytical store access. Option D is wrong because Azure SQL Database with JSON columns imposes a fixed relational schema and does not offer the same level of schema flexibility as a NoSQL document store; JSON columns also complicate indexing and nested query performance, and it lacks a built-in analytical store for seamless Synapse integration.

Practice this question →
104
MCQhard

A company is migrating its on-premises SQL Server data warehouse to Azure Synapse Analytics. They have a fact table with 2 billion rows and 30 columns. The table is frequently joined on CustomerID and filtered on OrderDate. What is the recommended table design?

A.Hash-distribute on CustomerID and partition on OrderDate
B.Replicate the table to all nodes
C.Round-robin distribution with partitions on OrderDate
D.Hash-distribute on OrderDate and partition on CustomerID
AnswerA

Hash distribution on CustomerID co-locates joined rows, eliminating data movement during joins, while partitioning on OrderDate enables partition elimination for date filters. Together they satisfy both the join and filter patterns on the 2-billion-row table.

Why this answer

Hash-distributing the fact table on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution node, which makes joins on CustomerID efficient by avoiding data movement. Partitioning on OrderDate enables partition elimination when filtering by date, reducing the amount of data scanned. This combination optimizes both the join and filter operations for a large fact table in Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse the roles of distribution and partitioning, thinking that partitioning on the join column or distributing on the filter column will improve performance, when in fact distribution should align with join keys and partitioning with filter keys.

How to eliminate wrong answers

Option B is wrong because replicating a 2-billion-row fact table to all nodes would consume excessive storage and cause significant overhead during data loading and maintenance, and it is intended for small dimension tables, not large fact tables. Option C is wrong because round-robin distribution distributes rows evenly but without any logical grouping, so joins on CustomerID would require shuffling all data across nodes, leading to poor performance. Option D is wrong because hash-distributing on OrderDate would scatter rows with the same CustomerID across nodes, making joins on CustomerID highly inefficient, and partitioning on CustomerID is not supported (partition columns must be date/time types in Synapse) and would not help with date-based filtering.

Practice this question →
105
Multi-Selectmedium

Which TWO of the following are supported storage options for use as a source in Azure Synapse Pipeline Copy Activity?

Select 2 answers
A.Azure Data Lake Storage Gen2
B.Azure Analysis Services
C.Azure Cognitive Search
D.Azure Purview
E.Azure Blob Storage
AnswersA, E

ADLS Gen2 is a supported source.

Why this answer

Azure Data Lake Storage Gen2 is a supported source for Azure Synapse Pipeline Copy Activity because it combines a hierarchical file system with Azure Blob Storage APIs, enabling efficient data ingestion. The Copy Activity can read data from ADLS Gen2 using the AzureBlobFS linked service, which supports both file and folder-level reads for structured and unstructured data.

Exam trap

The trap here is that candidates confuse Azure services that manage or process data (like Analysis Services, Cognitive Search, or Purview) with actual storage services that can serve as a source for the Copy Activity, leading them to select non-storage options.

Practice this question →
106
MCQmedium

You are designing a storage layer for an Azure Synapse Analytics dedicated SQL pool that ingests 4 TB of CSV files daily into a fact table. The files are landed in Azure Data Lake Storage Gen2 by an external ETL process. You need to load the data with the highest possible throughput while minimizing the load window. What should you do?

A.Use the COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.
B.Use the COPY statement with a shared access signature (SAS) token and set MAXDOP to a value equal to the number of files.
C.Use PolyBase with an external table and enable REJECT_TYPE VALUE with a threshold of 0.
D.Use Azure Data Factory with a copy activity that has a single pipeline and a single parallel copy thread.
AnswerA

The COPY statement is the recommended high-throughput ingestion method for dedicated SQL pools and supports SAS authentication to ADLS Gen2. Splitting large source files into multiple evenly sized files allows the COPY statement to parallelize reads across distributions, dramatically reducing the load window. This combination is the documented best practice for maximizing ingestion throughput in Synapse dedicated SQL pools.

Why this answer

The COPY statement is the preferred high-throughput ingestion mechanism for dedicated SQL pools, especially when reading from ADLS Gen2 with SAS authentication. Splitting the source files into evenly sized chunks enables parallel reads across distributions, which is essential for loading 4 TB within a tight window. Combining COPY with proper file sizing directly addresses the throughput and load-window requirements.

Exam trap

The trap here is assuming that any supported ingestion method (PolyBase, Data Factory, COPY) will automatically deliver maximum throughput without considering file layout and parallelism.

Practice this question →
107
MCQeasy

A data engineer needs to store semi-structured JSON logs from IoT devices. The data will be queried using SQL and must support high-throughput writes. Which Azure data store is most appropriate?

A.Azure Blob Storage with JSON blobs
B.Azure Cosmos DB with Core (SQL) API
C.Azure Data Lake Storage Gen2 with JSON files and PolyBase
D.Azure SQL Database with JSON columns
AnswerB

Cosmos DB's Core (SQL) API stores schema-agnostic JSON documents and exposes them to SQL-like queries, while its partitioned, multi-region write model delivers the high-throughput ingestion the IoT logs demand. This combination satisfies both the semi-structured format and write-throughput constraints in the stem.

Why this answer

Azure Cosmos DB with Core (SQL) API is the most appropriate choice because it natively stores semi-structured JSON documents, supports high-throughput writes with single-digit millisecond latency, and allows querying the JSON data directly using SQL syntax. Its schema-agnostic design and automatic indexing make it ideal for IoT workloads where device telemetry arrives at high velocity and must be immediately queryable.

Exam trap

The trap here is that candidates often choose Azure Blob Storage or Data Lake Storage because they associate JSON files with cheap storage, but they overlook the requirement for high-throughput writes and native SQL querying, which Cosmos DB uniquely satisfies among the options.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage with JSON blobs does not provide native SQL querying capabilities; querying would require additional services like Azure Synapse or external tools, and it is optimized for large, infrequent access rather than high-throughput writes. Option C is wrong because Azure Data Lake Storage Gen2 with JSON files and PolyBase is designed for analytical batch processing and large-scale data lakes, not for real-time high-throughput writes; PolyBase is used for querying external data in Synapse, not for direct ingestion at IoT scale. Option D is wrong because Azure SQL Database with JSON columns imposes a fixed relational schema and transactional overhead that cannot match the write throughput and schema flexibility of Cosmos DB; it is optimized for ACID transactions and structured data, not for high-velocity semi-structured ingestion.

Practice this question →
108
MCQeasy

You are designing a data storage solution for IoT sensor data. The data is written thousands of times per second and requires low-latency reads for real-time dashboards. Which Azure storage solution should you use?

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

Cosmos DB provides single-digit-millisecond reads and writes with horizontal partitioning, absorbing thousands of writes per second across partitions. Its low-latency, globally distributed model suits real-time IoT dashboards, whereas Blob or Table storage cannot match that ingest rate and latency.

Why this answer

Azure Cosmos DB is the correct choice because it provides single-digit millisecond read and write latency at any scale, with automatic indexing and multi-region distribution. Its support for multiple APIs (SQL, MongoDB, Cassandra, etc.) and configurable consistency levels makes it ideal for IoT sensor data requiring high-throughput writes and low-latency reads for real-time dashboards.

Exam trap

The trap here is that candidates often choose Azure Blob Storage or Data Lake Storage Gen2 because they associate IoT data with 'storage' rather than 'real-time querying,' overlooking the critical requirement for low-latency reads and high-frequency writes that only a NoSQL database like Cosmos DB can satisfy.

Why the other options are wrong

A

Blob Storage is optimized for large, unstructured data with higher latency, not real-time ingestion.

C

SQL Database can handle writes but may struggle with the scale and low-latency requirements of IoT sensor data.

D

Designed for big data analytics, not real-time ingestion and query.

Practice this question →
109
Multi-Selectmedium

You are designing a data storage solution for a media company that stores video files in Azure Blob Storage. The company wants to optimize storage costs by automatically moving older files to cooler tiers. The files are accessed frequently for the first 30 days, then infrequently for the next 60 days, and rarely after that. You need to configure a lifecycle management policy. Which two actions should you include in the policy? (Choose two.)

Select 2 answers
A.Move blobs to Cool tier after 30 days since last modification.
B.Move blobs to Cool tier after 90 days since last modification.
C.Move blobs to Archive tier after 30 days since last modification.
D.Delete blobs after 180 days since last modification.
E.Move blobs to Archive tier after 90 days since last modification.
AnswersA, E

Moving blobs to Cool tier after 30 days aligns with the access pattern: frequently accessed for the first 30 days, then infrequently. Cool tier is optimized for infrequent access and lower storage cost. This action reduces cost while maintaining availability. It is a valid lifecycle management action.

Why this answer

The access pattern indicates that data is frequently accessed for 30 days, infrequently for the next 60 days (days 31-90), and rarely after 90 days. Therefore, moving to Cool tier after 30 days and to Archive tier after 90 days optimizes storage costs while matching access needs. Deletion is not specified.

Exam trap

The trap here is misaligning tier transitions with the access pattern, such as moving to Archive too early or Cool too late.

Practice this question →
110
MCQhard

You are designing a storage solution for a financial services company. The solution must store large volumes of semi-structured JSON data in Azure Data Lake Storage Gen2. The data is accessed by Azure Databricks for batch processing and by Azure Synapse Analytics for interactive queries. The data must be organized for efficient partition elimination and must support atomic operations. You need to choose the appropriate file format and partitioning strategy. What should you do?

A.Store the data as CSV files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
B.Store the data as Parquet files without partitioning, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
C.Store the data as Avro files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
D.Store the data as Parquet files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.
AnswerD

Parquet is a columnar format ideal for analytical workloads, offering efficient compression and predicate pushdown. Partitioning by date enables partition elimination, reducing the amount of data scanned. Registering the folder as an external table in both services allows them to query the same data without duplication. This meets the requirements for efficient querying and atomic operations (Parquet files are immutable, and writes can be atomic at file level).

Why this answer

Parquet is the optimal format for analytical workloads due to its columnar storage, compression, and predicate pushdown capabilities. Partitioning by date enables partition elimination, which is critical for efficient querying. Registering the folder as an external table in both Azure Databricks and Azure Synapse Analytics allows both services to access the same data seamlessly.

Other formats like CSV and Avro are not as efficient for these requirements.

Exam trap

The trap here is assuming that any file format with partitioning will meet the performance requirements, but columnar formats like Parquet are essential for efficient analytical queries.

Practice this question →
111
MCQmedium

You manage an Azure Synapse Analytics dedicated SQL pool that stores a 4 TB fact table named FactSales. The table is currently distributed using ROUND_ROBIN and has a clustered columnstore index. Most analytical queries join FactSales to a much smaller dimension table DimProduct on ProductKey and then filter by DateKey. You need to redesign the physical storage to minimize data movement during these joins and improve query performance. What should you do?

A.Keep ROUND_ROBIN distribution and add a nonclustered index on ProductKey in FactSales.
B.Change the distribution of FactSales to HASH(DateKey) and create a partition on ProductKey.
C.Change the distribution of FactSales to HASH(ProductKey) and ensure DimProduct is replicated.
D.Recreate FactSales as a replicated table and create a hash distribution on DimProduct using ProductKey.
AnswerC

Hash distributing the large fact table on the frequently joined column ProductKey colocates rows with the same key on the same compute node. Replicating the small dimension table makes its rows available on every node. This combination eliminates data movement during joins on ProductKey, which is the primary performance bottleneck described in the scenario.

Why this answer

For large fact tables in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during joins. Replicating the smaller dimension table ensures its rows are present on every compute node, so the join can be performed locally. This design directly addresses the scenario's need to reduce data movement and improve query performance.

Exam trap

The trap here is assuming that any hash distribution improves performance, when the distribution key must match the join column to actually eliminate data movement.

Practice this question →
112
MCQeasy

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

A.Azure Private Endpoint
B.Shared access signature (SAS)
C.Azure Defender for Storage
D.Firewall and virtual network service endpoints
AnswerD

Enabling the storage account firewall with virtual network service endpoints restricts traffic to specified subnets, blocking all public internet access. This directly satisfies the requirement to deny direct internet access while permitting the designated virtual network.

Why this answer

Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Exam trap

The trap here is that candidates often confuse Azure Private Endpoint with a complete internet-blocking solution, but Private Endpoint alone does not disable the public endpoint; you must also configure the firewall to deny all public traffic.

How to eliminate wrong answers

Option A is wrong because Azure Private Endpoint uses a private IP address from your virtual network to connect to the storage account, but it does not inherently block all internet access; it provides a private connection but still requires additional firewall rules to fully prevent internet access. Option B is wrong because Shared access signature (SAS) provides delegated access to storage resources via tokens that can be used over the internet, and it does not restrict network-level access from the internet. Option C is wrong because Azure Defender for Storage is a security monitoring and threat detection service, not a network access control mechanism; it does not block or restrict network traffic.

Practice this question →
113
MCQmedium

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

A.Partition by device ID first, then by date.
B.Partition by device ID only, with a separate directory for each device.
C.Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).
D.Partition by device type only, with a directory for each type.
AnswerC

Partitioning by date first lets the analytics engine prune to the last 30 days, then the device-type subfolder prunes further within each date. This hierarchical layout matches the query predicate exactly, minimising files scanned and reducing both I/O cost and query latency in ADLS Gen2.

Why this answer

Partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Exam trap

The trap here is that candidates often assume partitioning by the most granular attribute (device ID) first will provide the best performance, but they overlook that query patterns typically filter by time range, making date the most effective first-level partition for cost and performance optimization.

How to eliminate wrong answers

Option A is wrong because partitioning by device ID first, then by date, would require scanning all device ID partitions even when querying only recent data, leading to high I/O and cost. Option B is wrong because partitioning only by device ID with a separate directory per device does not support efficient date-range pruning; queries for the last 30 days would need to scan every device directory, which is prohibitively expensive for millions of devices. Option D is wrong because partitioning only by device type would force scanning all date directories for every query, even when the query is limited to a specific time range, resulting in unnecessary data reads and higher costs.

Practice this question →
114
MCQeasy

A company is planning to migrate an on-premises data warehouse to Azure Synapse Analytics dedicated SQL pool. The data warehouse contains a large fact table with billions of rows and several dimension tables. The company wants to optimize query performance and minimize data movement during joins. They need to choose an appropriate distribution type for the fact table. The fact table is frequently joined with dimension tables on a column that has high cardinality and is evenly distributed. What distribution type should they use?

A.Replicated distribution
B.Hash distribution on the join column
C.Round-robin distribution
D.Hash distribution on a column with low cardinality
AnswerB

Hash distribution on the join column ensures that rows with the same join key are stored in the same distribution, eliminating data movement during joins. For a high-cardinality, evenly distributed column, this provides balanced data distribution and optimal query performance. This is the recommended approach for large fact tables in Azure Synapse Analytics dedicated SQL pool.

Why this answer

Hash distribution on the join column aligns the data layout with the most common join operation, ensuring that matching rows are colocated and eliminating the need for data movement. Since the column has high cardinality and even distribution, it also provides balanced data across distributions, which is critical for query performance in a dedicated SQL pool.

Exam trap

The trap here is assuming that round-robin distribution is always best for large tables, but it causes data movement during joins; hash distribution on the join column is preferred when the column is high-cardinality and evenly distributed.

Practice this question →
115
MCQeasy

You are designing a data storage solution for real-time streaming data from IoT devices. The data must be stored in its original format for immediate processing and later transformed for analytics. Which Azure service should you use for raw data ingestion?

A.Azure Data Lake Storage Gen2
B.Azure Event Hubs
C.Azure Stream Analytics
D.Azure Data Factory
AnswerB

Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for later transformation. This makes it the correct choice for raw data ingestion.

Why this answer

Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for up to 7 days (or longer with Event Hubs Capture) for later transformation and analytics. This makes it the correct choice for raw data ingestion before any transformation occurs.

Exam trap

The trap here is that candidates confuse data ingestion (Event Hubs) with data storage (Data Lake Storage) or data processing (Stream Analytics), assuming a single service must handle both raw capture and transformation, when in fact the question explicitly asks for raw data ingestion only.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is a hierarchical file store for analytics workloads, not a real-time ingestion endpoint; it cannot natively accept streaming events at high velocity without an intermediary like Event Hubs or IoT Hub. Option C is wrong because Azure Stream Analytics is a stream processing engine that consumes data from sources like Event Hubs and performs real-time transformations, but it does not store raw data itself—it outputs results to sinks. Option D is wrong because Azure Data Factory is a cloud-based ETL and orchestration service for batch and scheduled data movement, not designed for real-time, high-throughput streaming ingestion from IoT devices.

Practice this question →
116
MCQmedium

You are designing a storage solution for a global application that requires low-latency reads and writes of JSON documents. The data model includes nested properties, and you need to query these properties efficiently. You also need to ensure the data is available in multiple regions with automatic failover. Which Azure service should you use?

A.Azure SQL Database with active geo-replication.
B.Azure Blob Storage with RA-GRS replication.
C.Azure Table Storage with a partition key and row key.
D.Azure Cosmos DB with Core (SQL) API and multi-region writes enabled.
AnswerD

Azure Cosmos DB is a globally distributed, multi-model database that natively supports JSON documents and allows querying nested properties using SQL-like syntax. With multi-region writes enabled, it provides low-latency reads and writes across multiple regions and automatic failover. It is designed for global distribution and elastic scalability, making it ideal for this scenario.

Why this answer

Azure Cosmos DB with Core (SQL) API is designed for JSON documents, supports querying nested properties, and offers multi-region writes with automatic failover, ensuring low latency globally. Other services either lack native JSON support, do not support multi-region writes, or are not optimized for document queries.

Exam trap

The trap here is assuming that relational databases with geo-replication provide multi-region writes; most only allow writes to a single primary region.

Practice this question →
117
MCQmedium

Your company stores sensitive customer data in Azure SQL Database. You need to encrypt the data at rest and ensure that only your application can decrypt it, even from database administrators. What should you implement?

A.Transparent Data Encryption (TDE)
B.Always Encrypted
C.Dynamic Data Masking
D.Azure Storage Service Encryption
AnswerB

Always Encrypted keeps column encryption keys on the client, so data is encrypted at rest and decrypted only by the application holding the keys. Database administrators see ciphertext, satisfying the requirement that even privileged administrators cannot read sensitive customer data.

Why this answer

Always Encrypted is correct because it ensures that sensitive data is encrypted at rest and in use, and the encryption keys are stored client-side, so only the application can decrypt the data. Database administrators (DBAs) cannot access the plaintext data because they lack the column encryption keys, even though they have full administrative access to the database.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with client-side encryption, assuming TDE protects against DBA access, but TDE only protects data at rest from storage theft, not from authorized database users.

Why the other options are wrong

A

TDE encrypts data at rest but the database engine holds the keys, allowing DBAs to decrypt.

C

Only masks data from unauthorized users; data is still stored in plaintext.

D

This applies to Azure Storage, not SQL Database.

Practice this question →
118
MCQmedium

You are designing a data storage solution for a financial analytics platform. The platform ingests CSV files into Azure Data Lake Storage Gen2 and processes them with Azure Synapse Analytics serverless SQL pools. Queries frequently filter on a transaction date column and a region column, but the files are currently organized in a flat folder structure. You need to minimize the amount of data scanned by serverless SQL queries while keeping the files queryable using standard T-SQL OPENROWSET. What should you do?

A.Move the files into an Azure Blob Storage container and query them using the Blob Storage REST API.
B.Partition the data by transaction date and region using a Hive-style folder hierarchy, then query with OPENROWSET and a wildcard path.
C.Convert all CSV files to Parquet and place them in a single folder without subfolders.
D.Create an external table in the serverless SQL pool over the entire folder path and add a clustered columnstore index.
AnswerB

A Hive-style hierarchy such as /year=2024/month=03/region=us/ enables serverless SQL pools to perform partition elimination when the query filters on those columns and uses FILEPATH or the appropriate wildcard path. This reduces the bytes scanned, lowering cost and latency, and keeps the data queryable through standard T-SQL OPENROWSET without any additional service.

Why this answer

Organizing files into a Hive-style folder hierarchy by transaction date and region allows the serverless SQL pool to perform partition elimination. When the query filters on those virtual columns, only the relevant folders are read, reducing scanned bytes and cost. The data remains fully queryable with standard T-SQL OPENROWSET, satisfying both the performance and compatibility requirements.

Exam trap

The trap here is assuming that converting to Parquet alone solves the scanning problem, when folder-based partitioning is what enables partition elimination in serverless SQL pools.

Practice this question →
119
MCQhard

Refer to the exhibit. A Bicep file is used to deploy an Azure Synapse Analytics workspace. What is the purpose of the 'purviewConfiguration' property?

A.It links the workspace to a Microsoft Purview account for data lineage and cataloging
B.It configures automated backups of the Synapse workspace
C.It enables monitoring of data movement by Azure Monitor
D.It connects the workspace to a data catalog for pipeline sources
AnswerA

The purviewConfiguration property binds the Synapse workspace to a Microsoft Purview account, enabling automatic lineage capture and metadata cataloging for workspace artefacts. This satisfies the requirement to govern and trace data assets across the workspace.

Why this answer

The 'purviewConfiguration' property in a Bicep file for Azure Synapse Analytics links the workspace to a Microsoft Purview account. This integration enables automated data lineage tracking, cataloging, and discovery across the Synapse environment, allowing users to search for and govern data assets directly from Purview. Without this property, the Synapse workspace operates independently of Purview's unified data governance capabilities.

Exam trap

The trap here is that candidates confuse the Purview integration with general cataloging or monitoring features, assuming it only applies to pipeline sources rather than understanding it provides full data lineage and cataloging across the entire Synapse workspace.

How to eliminate wrong answers

Option B is wrong because automated backups of a Synapse workspace are configured via the 'sqlPoolBackup' or workspace-level backup policies, not through the 'purviewConfiguration' property, which is solely for Purview integration. Option C is wrong because enabling monitoring of data movement by Azure Monitor is done through diagnostic settings and workspace-level monitoring configurations, not by linking to Purview. Option D is wrong because connecting the workspace to a data catalog for pipeline sources is a general description of Purview's role, but the specific purpose of 'purviewConfiguration' is to link to a Microsoft Purview account for full data lineage and cataloging, not just for pipeline sources.

Practice this question →
120
Multi-Selecthard

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Select 3 answers
A.Partition switching can be used to quickly load data into a table.
B.Partitions are automatically aligned with distributions.
C.Each partition is stored as a separate set of rowgroups in a columnstore index.
D.Partitioning is only supported on tables with clustered rowstore indexes.
E.Excessive partitioning can lead to fragmentation and poor query performance.
AnswersA, C, E

Partition switching uses ALTER TABLE ... SWITCH to move a staging table's partition into the target table's matching partition as a metadata operation, avoiding row-by-row insertion. This satisfies the requirement for fast data loading by replacing expensive DML with near-instant metadata swaps.

Why this answer

Option A is correct because partition switching (ALTER TABLE ... SWITCH PARTITION) is a metadata-only operation that instantly moves a fully prepared staging table's partition into the target table, making it a fast way to load data. Option C is correct because in a clustered columnstore index each partition is stored as its own set of rowgroups, so partition boundaries also define rowgroup boundaries.

Option E is correct because creating too many partitions (especially small ones) increases metadata overhead, causes rowgroup fragmentation, and degrades query performance due to reduced segment elimination efficiency. Option B is not correct because partitions and distributions are independent constructs; partitioning does not automatically align with the 60 distributions, and alignment must be managed explicitly. Option D is not correct because partitioning is supported on clustered columnstore, clustered rowstore, and heap tables in dedicated SQL pools, not only on clustered rowstore indexes.

Exam trap

The trap here is that candidates often confuse partitions with distributions, thinking they are automatically aligned, or assume partitioning is only for rowstore indexes, when in fact columnstore indexes are the recommended and most common storage type for partitioning in dedicated SQL pool.

Practice this question →
121
MCQeasy

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

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

Event Hubs ingests millions of telemetry events per second with low latency, buffering the stream for downstream consumers. This satisfies the real-time dashboard requirement while retaining data for long-term analytics, unlike batch-oriented stores such as Blob Storage or Azure SQL Database.

Why this answer

Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Exam trap

The trap here is that candidates confuse the ingestion layer with the storage layer, choosing Azure Blob Storage or Data Lake Storage because they think 'store data' means persistent storage, but the question specifically asks for the ingestion layer where real-time, low-latency streaming is required, which Event Hubs uniquely provides.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database is a relational OLTP store optimized for structured queries and ACID transactions, not for high-velocity, schema-less JSON ingestion at millions of events per second, and it would introduce latency and cost bottlenecks. Option B is wrong because Azure Blob Storage is an object store designed for batch and large-file storage, not for real-time, per-second event ingestion; it lacks native streaming ingestion, pub-sub semantics, and sub-second latency for dashboards. Option D is wrong because Azure Data Lake Storage is a hierarchical file system optimized for analytics on large datasets, not for real-time event ingestion; it is typically used as a destination for data after it has been processed or captured from a streaming source like Event Hubs.

Practice this question →
← PreviousPage 2 of 2 · 121 questions total

Ready to test yourself?

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

Start 20-question session