I85PREP
⌘ K
DP-203 study materials
DP-203 study resources

DP-203 practice questions

One question at a time. Build your DP-203 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Design and implement data storage

In Azure Synapse Analytics, which storage format is recommended for dedicated SQL pool staging tables to maximize load throughput?

  1. Hash-distributed clustered index tables
  2. Clustered columnstore tables
  3. Replicated tables with a clustered index
  4. Round-robin heap tables Correct answer.

Explanation

Staging tables should be heaps with round-robin distribution because they avoid the cost of building indexes and moving rows during the load. Data is then inserted into the final table with a single well-designed statement. Clustered columnstore staging adds overhead during ingestion.

Question 2Design and implement data storage

You need to design a dedicated SQL pool table that stores 2 TB of sales facts queried mostly by date range with aggregations. Which physical design best suits this workload?

  1. Heap with round-robin distribution
  2. Clustered columnstore with hash distribution on a high-cardinality join column Correct answer.
  3. Clustered index with hash distribution on the date column
  4. Clustered columnstore with round-robin distribution

Explanation

Large fact tables benefit from clustered columnstore because it compresses data and scans only needed columns for aggregations. Hash distribution on a frequently joined high-cardinality column colocates rows to reduce shuffle during joins.

Question 3Design and implement data storage

A dedicated SQL pool table is hash-distributed on CustomerKey, but one customer accounts for 40 percent of all rows. What is the primary performance consequence?

  1. Data skew causes one distribution to hold most rows, so queries run at the speed of the slowest distribution Correct answer.
  2. Columnstore compression ratio improves because rows are grouped together
  3. Queries fail with a distribution mismatch error at execution time
  4. The table automatically rebalances by splitting the distribution

Explanation

Hash distribution assigns rows to distributions by hash value. A dominant key value places most rows on one distribution, so parallel queries finish only when the largest distribution finishes, creating a bottleneck. Choosing a higher-cardinality, evenly distributed column mitigates this.

Question 4Design and implement data storage

Which Azure Data Factory activity copies data without staging it through an intermediate store when both source and sink support the same protocol?

  1. A Lookup activity followed by a Stored Procedure activity
  2. A ForEach activity wrapping a Delete activity
  3. A Copy activity with a binary copy and no staging linked service Correct answer.
  4. A Mapping Data Flow with a derived column

Explanation

When the source and sink connectors support the same protocol and credentials, the Copy activity can move data directly without an intermediate staging store. This reduces latency and storage cost. Mapping data flows and orchestration activities are not direct-copy mechanisms.

Question 5Design and implement data storage

You must copy data from an on-premises SQL Server into Azure Data Lake Storage Gen2 every night. Which integration runtime configuration is required?

  1. An Azure-SSIS integration runtime joined to a virtual network
  2. A self-hosted integration runtime installed on a machine that can reach the SQL Server Correct answer.
  3. A global Azure integration runtime in the same region as the storage account
  4. An Azure integration runtime with a managed virtual network

Explanation

On-premises data sources require a self-hosted integration runtime because the Azure-hosted runtimes cannot reach private corporate networks directly. The self-hosted runtime runs on a machine inside the network and makes outbound connections to Azure Data Factory.

Question 6Design and implement data storage

In Azure Data Lake Storage Gen2, which capability provides hierarchical directory semantics and atomic directory rename operations?

  1. Blob inventory reports
  2. A shared access signature scoped to a container
  3. A hierarchical namespace enabled on the storage account Correct answer.
  4. Blob soft delete with versioning

Explanation

A hierarchical namespace organizes objects into real directories, enabling atomic directory renames and efficient directory-level operations. Analytics engines rely on this behavior for fast job commits. Flat blob namespaces emulate directories only with prefixes.

Question 7Design and implement data storage

Which file layout typically gives the best query performance for a large Delta Lake table read by Spark SQL aggregations?

  1. Gzip-compressed Avro files without partitioning
  2. Many small JSON files in a single directory
  3. One very large CSV file per day
  4. Parquet files of 100-500 MB with partitioning on low-cardinality filter columns Correct answer.

Explanation

Columnar Parquet files of moderate size maximize parallelism and predicate pushdown while avoiding the small-file problem. Partitioning on low-cardinality columns lets queries prune irrelevant directories.

Question 8Design and implement data storage

What is the main benefit of partitioning a Delta table by a date column when most queries filter on that date?

  1. Row-level concurrency conflicts are eliminated
  2. Delta automatically creates indexes on the partition column
  3. Queries can skip reading files that do not match the filter value Correct answer.
  4. The table no longer requires checkpoint files

Explanation

Partitioning physically separates rows into directories by value, so a filter on the partition column lets the engine prune entire directories. This reduces I/O and speeds up selective queries. Partitioning does not create indexes or change transaction logging.

Question 9Design and implement data storage

A team wants automatic compaction of small files and automatic data layout optimization in a Delta table. Which feature should they enable?

  1. Z-ordering executed manually after every write
  2. Time travel with a short log retention period
  3. Change data feed with a retention of zero days
  4. Optimized writes with auto compaction and optimized layout Correct answer.

Explanation

Optimized writes coalesce small writes into larger files, and auto compaction merges files after writes complete. Optimized layout (liquid clustering) maintains data layout automatically without manual rewriting.

Question 10Design and implement data storage

Which statement correctly describes how Delta Lake provides ACID transactions on top of Parquet files?

  1. Writers rely on blob snapshots taken by the storage service
  2. Each Parquet file contains embedded row locks managed by the storage account
  3. A JSON transaction log records committed actions, and readers use it to see a consistent snapshot Correct answer.
  4. Readers acquire exclusive leases on all files before querying

Explanation

Delta Lake maintains an ordered transaction log in the _delta_log directory that records adds, removes, and metadata as atomic commits. Readers reconstruct a consistent snapshot from the log, which provides serializable isolation.

Question 11Design and implement data storage

Users report that a query on a Delta table returns different row counts within the same minute. Which cause is most likely?

  1. The storage account has hierarchical namespace disabled
  2. Concurrent write transactions are committing new versions while readers query different snapshots Correct answer.
  3. The Parquet compression codec is inconsistent across files
  4. The table lacks a Z-order index on the filter column

Explanation

Delta readers resolve the latest committed snapshot at query start, so concurrent commits can make successive queries see different versions. This is expected isolation behavior rather than corruption. Pinning a version with time travel gives reproducible results.

Question 12Design and implement data storage

Which Azure Synapse Analytics component stores relational data in a provisioned, massively parallel processing engine with dedicated compute?

  1. Apache Spark pool
  2. Serverless SQL pool
  3. Data Explorer pool
  4. Dedicated SQL pool Correct answer.

Explanation

A dedicated SQL pool provisions its own compute nodes and stores data in a relational MPP engine, billed by data warehouse units. Serverless pools are on-demand and query data in place without provisioned storage.

Question 13Design and implement data storage

Which workload is best served by a serverless SQL pool in Azure Synapse Analytics?

  1. Running nightly SSIS packages with transactional consistency
  2. Ad hoc querying of Parquet files in a data lake using OPENROWSET Correct answer.
  3. Storing 50 TB of dimension tables with clustered columnstore indexes
  4. Serving millisecond-latency point lookups for a web application

Explanation

Serverless SQL pools excel at querying files in the lake on demand without loading data into provisioned storage. OPENROWSET lets users define the file schema inline. They are not intended for persistent relational storage or low-latency transactional serving.

Question 14Design and implement data storage

In Azure Databricks, which cluster type is appropriate for a scheduled production job that must run reliably without manual startup?

  1. A high-concurrency cluster with table access control
  2. A single-node cluster with autoscaling enabled
  3. An all-purpose interactive cluster shared by analysts
  4. A job cluster created for the run and terminated afterward Correct answer.

Explanation

Job clusters are created for a specific run and terminated when it completes, which is cost-effective and reliable for automated workloads. All-purpose clusters are meant for interactive exploration and remain running.

Question 15Design and implement data storage

You need to store semi-structured JSON documents in Azure Cosmos DB and query nested properties without scanning the whole document. Which design achieves this?

  1. Store each nested property as a separate container
  2. Embed the nested properties and add an index policy that includes those property paths Correct answer.
  3. Disable indexing and rely on full document scans
  4. Store the JSON as a base64 string in a single property

Explanation

Cosmos DB indexes document paths according to the indexing policy, so including nested paths allows efficient filtered queries. Embedding related data keeps reads to a single request. Disabling indexing forces scans.

Question 16Design and implement data storage

Which Cosmos DB partitioning choice best supports a workload that reads a single tenant's data with high throughput?

  1. Partition key set to the tenant identifier Correct answer.
  2. No partition key, using a single logical partition
  3. Partition key set to the document creation timestamp
  4. Partition key set to a random GUID per document

Explanation

Using the tenant identifier as the partition key colocates a tenant's items in one logical partition, enabling efficient single-partition queries. It also distributes load across tenants. Random GUIDs scatter related items and timestamps cause hot partitions.

Question 17Design and implement data storage

A container in Azure Cosmos DB has a provisioned throughput of 4000 RU/s shared across partitions. What happens when a request exceeds the available throughput?

  1. The request is rejected permanently and the document is lost
  2. The request is throttled and returns status code 429 with a retry-after hint Correct answer.
  3. The request is silently queued until throughput becomes available
  4. The container automatically increases its maximum RU/s without limit

Explanation

Cosmos DB enforces throughput limits per partition and returns HTTP 429 when the budget is exhausted. The response includes a retry-after value, and SDKs retry automatically. Data is not lost because the write did not commit.

Question 18Design and implement data storage

Which Azure Storage feature should be used to prevent accidental deletion of blobs for a defined retention period while still allowing overwrites?

  1. A read-only shared access signature
  2. Blob soft delete with a retention policy Correct answer.
  3. Immutable blob storage with a legal hold
  4. Blob versioning with a lifecycle rule

Explanation

Blob soft delete retains deleted or overwritten blob versions for a configured period so they can be restored. It protects against accidental deletion without blocking normal writes. Legal hold prevents modification entirely.

Question 19Design and implement data storage

Which access mechanism grants time-limited, scoped access to a single blob without sharing account keys?

  1. A stored access policy on the container without a signature
  2. The storage account primary access key
  3. A service shared access signature scoped to the blob with an expiry Correct answer.
  4. Azure role-based access control at the subscription scope

Explanation

A service SAS encodes permissions, resource scope, and expiry in a signed token, so a client can access one blob for a limited time without account keys. This follows least privilege.

Question 20Design and implement data storage

In Azure Synapse, a query joining a large fact table to a small dimension table repeatedly moves the dimension rows. Which technique reduces this movement?

  1. Convert the fact table to a heap with round-robin distribution
  2. Hash-distribute the dimension on a column with few distinct values
  3. Replicate the small dimension table so every compute node holds a full copy Correct answer.
  4. Increase the result set cache expiration

Explanation

Replicated tables keep a full copy on every compute node, so joins with large distributed tables avoid shuffling the small table. This suits dimension tables that are small and infrequently updated.

Question 21Design and implement data storage

Which Azure Data Factory mapping data flow transformation should be used to keep only rows matching a condition before a join?

  1. Aggregate transformation
  2. Surrogate key transformation
  3. Filter transformation Correct answer.
  4. Union transformation

Explanation

The Filter transformation evaluates a boolean expression and passes only matching rows downstream, reducing data volume before expensive operations such as joins.

Question 22Design and implement data storage

A pipeline must land files into a folder named with the pipeline run date, such as /raw/2024/06/01/. Which feature supplies the dynamic path?

  1. A mapping data flow sink with fixed partitioning
  2. A hard-coded folder path in the dataset definition
  3. Pipeline parameters combined with the utcnow() expression in a dynamic dataset property Correct answer.
  4. A trigger that renames the storage container

Explanation

Dynamic content expressions such as utcnow() formatted with date functions produce run-specific paths at execution time. Parameters pass values into dataset properties so a single dataset serves many runs.

Question 23Design and implement data storage

You are designing storage for a streaming pipeline that must support upserts and time travel on a data lake. Which combination is appropriate?

  1. JSON files partitioned by ingestion hour without metadata
  2. Delta Lake tables on ADLS Gen2 with MERGE writes Correct answer.
  3. CSV files overwritten each micro-batch
  4. Parquet files appended without a transaction log

Explanation

Delta Lake adds a transaction log to Parquet, enabling MERGE for upserts, ACID commits, and time travel. Streaming writers can use exactly-once checkpointing against these tables.

Question 24Design and implement data storage

What does Z-ordering improve in a Delta Lake table?

  1. It compresses each file with a stronger codec
  2. It colocates related rows in the same files so data-skipping statistics prune more files Correct answer.
  3. It sorts rows globally across all files and removes the need for partitioning
  4. It replicates files across regions for disaster recovery

Explanation

Z-ordering arranges multidimensional data so rows with similar values on the specified columns land in the same files. File statistics then let queries skip more files. It is a layout optimization, not a global sort.

Question 25Design and implement data storage

A dedicated SQL pool table is distributed by hash on a column that many queries join on, but some queries join on a different column and show heavy shuffle. Which design change addresses this most directly?

  1. Change the distribution column to a low-cardinality status column
  2. Increase the number of distributions by scaling to a higher service level
  3. Convert the table to a replicated table
  4. Create a materialized view or a second table distributed on the other join column Correct answer.

Explanation

Joins on columns that differ from the distribution key require data movement. Distributing a copy of the table on the other join column, or precomputing results in a materialized view, colocates rows for that join.

Question 26Design and implement data storage

Which Azure Data Lake Storage Gen2 feature should be used to organize files into a logical folder hierarchy that supports atomic directory renames?

  1. Hierarchical namespace Correct answer.
  2. Blob snapshots
  3. Blob flat namespace
  4. Blob versioning

Explanation

Enabling a hierarchical namespace on a storage account turns it into Data Lake Storage Gen2, which provides true directory semantics and atomic directory rename operations. A flat blob namespace does not offer atomic directory operations. Snapshots and versioning are data protection features, not directory organization features.

Question 27Design and implement data storage

You are designing a Delta Lake table that will be queried primarily by date and region. Which physical layout strategy best supports these filters?

  1. Partition by a randomly generated ID column
  2. Do not partition and rely on file statistics only
  3. Partition by region and use Z-ORDER on date
  4. Partition by date and use Z-ORDER on region Correct answer.

Explanation

Partitioning by the most selective and commonly filtered column (date) prunes files efficiently, while Z-ORDERing on region co-locates related region values within each partition to improve data skipping. Partitioning by region first may create too many small partitions if region cardinality is high. Random partitioning provides no pruning benefit.

Question 28Design and implement data storage

Which Azure Synapse Analytics pool type is best suited for exploratory queries over files in a data lake without provisioning dedicated compute?

  1. Dedicated SQL pool
  2. SQL on-demand pool
  3. Spark pool
  4. Serverless SQL pool Correct answer.

Explanation

A serverless SQL pool in Azure Synapse Analytics queries files in a data lake on demand without provisioning dedicated compute resources. Dedicated SQL pools require provisioned capacity. Spark pools are for big data processing, not T-SQL exploration. SQL on-demand is the former name for serverless SQL pools.

Question 29Design and implement data storage

In Azure Databricks, which cluster configuration is most appropriate for a production job that must run reliably on a schedule without manual intervention?

  1. All-purpose cluster with autoscaling
  2. High-concurrency cluster with autoscaling
  3. Single-node cluster
  4. Job cluster created for the run Correct answer.

Explanation

Job clusters are created automatically for a scheduled job run and terminated when the job completes, providing isolation and reliability for production workloads. All-purpose clusters are interactive and may be modified by users. High-concurrency clusters are for shared interactive workloads. Single-node clusters lack distributed processing.

Question 30Design and implement data storage

You need to store JSON documents in Azure Cosmos DB and query nested properties efficiently. Which indexing approach should you use?

  1. Exclude all paths from indexing
  2. Index only the root path
  3. Use the default indexing policy Correct answer.
  4. Disable indexing and rely on scans

Explanation

The default indexing policy in Azure Cosmos DB automatically indexes all properties, including nested ones, so queries on nested JSON properties can use the index. Excluding paths or indexing only the root prevents efficient nested queries. Disabling indexing forces full scans and is inefficient.

Question 31Design and implement data storage

A Cosmos DB container is partitioned by tenantId and serves a workload that reads a single tenant's data with high throughput. Which partition key choice is best?

  1. The tenantId Correct answer.
  2. A synthetic key combining tenantId and a random suffix
  3. A randomly generated GUID
  4. The document creation timestamp

Explanation

Using tenantId as the partition key co-locates all data for a tenant in one logical partition, enabling efficient single-partition queries for that tenant. A synthetic key or random GUID scatters a tenant's data across partitions, requiring cross-partition queries. A timestamp key may create hot partitions.

Question 32Design and implement data storage

A Cosmos DB container has 4000 RU/s provisioned across multiple partitions. What happens when a request exceeds the available throughput?

  1. The request is automatically retried with higher priority
  2. The request is throttled and returns HTTP 429 Correct answer.
  3. The request is queued until throughput is available
  4. The container automatically scales to unlimited RU/s

Explanation

When a request exceeds the provisioned throughput, Azure Cosmos DB throttles it and returns HTTP status 429 (Too Many Requests). The client should implement retry logic with backoff. The service does not automatically increase RU/s or queue requests indefinitely.

Question 33Design and implement data storage

Which Azure Storage feature prevents accidental deletion of blobs for a defined retention period while allowing normal read and write operations?

  1. Blob soft delete Correct answer.
  2. Object replication
  3. Immutable storage with legal hold
  4. Azure Backup for blobs

Explanation

Blob soft delete retains deleted blobs for a specified retention period, allowing recovery while normal operations continue. Immutable storage with legal hold prevents modification or deletion but blocks writes. Azure Backup and object replication serve different purposes.

Question 34Design and implement data storage

Which mechanism provides time-limited, scoped access to a single blob without sharing account keys?

  1. Stored access policy
  2. User delegation SAS Correct answer.
  3. Service SAS
  4. Account SAS

Explanation

A user delegation SAS is secured with Azure AD credentials and can be scoped to a single blob with a limited lifetime, without using account keys. Account and service SAS tokens are signed with account keys. Stored access policies define permissions but are not a SAS type themselves.

Question 35Design and implement data storage

In Azure Synapse dedicated SQL pool, a query repeatedly moves a small dimension table during joins. Which technique reduces this data movement?

  1. Replicate the dimension table Correct answer.
  2. Partition the dimension table
  3. Round-robin distribute the dimension table
  4. Hash distribute the dimension table on a different key

Explanation

Replicating a small dimension table creates a full copy on every compute node, eliminating the need to shuffle its rows during joins. Round-robin or hash distribution on a different key may still require data movement. Partitioning does not address join data movement.

Question 36Design and implement data storage

Which Azure Data Factory mapping data flow transformation should be used to keep only rows that satisfy a condition before loading them?

  1. Join
  2. Derived Column
  3. Aggregate
  4. Filter Correct answer.

Explanation

The Filter transformation in mapping data flows evaluates a condition and passes only rows that meet it. Derived Column adds or modifies columns, Aggregate summarizes rows, and Join combines datasets. Filtering early reduces downstream data volume.

Question 37Design and implement data storage

A pipeline must write files into a folder path that includes the pipeline run date, such as /raw/2024/06/01/. Which feature provides this dynamic path?

  1. Pipeline parameters with dynamic expressions Correct answer.
  2. Trigger recurrence schedule
  3. Static dataset parameters
  4. Linked service annotations

Explanation

Pipeline parameters combined with dynamic expressions (for example, using utcnow() and formatDateTime()) allow folder paths to be constructed at runtime based on the run date. Static parameters cannot change per run. Linked service annotations and trigger schedules do not build dynamic paths.

Question 38Design and implement data storage

You are designing storage for a streaming pipeline that must support upserts and time travel on a data lake. Which combination should you choose?

  1. CSV files with partitioning
  2. Azure Blob Storage with snapshots
  3. Parquet files with a Hive metastore
  4. Delta Lake on Azure Data Lake Storage Gen2 Correct answer.

Explanation

Delta Lake on Azure Data Lake Storage Gen2 provides ACID transactions, upserts via MERGE, and time travel through its transaction log. Plain Parquet, CSV, or Blob Storage snapshots do not natively support these features. Delta Lake is designed for reliable streaming and batch workloads.

Question 39Design and implement data storage

A dedicated SQL pool table is hash-distributed on a column used by many joins, but some queries join on a different column. What is the effect on those queries?

  1. They require data movement to redistribute rows for the join Correct answer.
  2. They fail because the join column is not the distribution key
  3. They use round-robin distribution for the join
  4. They automatically use broadcast joins without movement

Explanation

When a join uses a column other than the distribution key, rows must be moved across compute nodes to align matching values, which adds overhead. The query does not fail, and broadcast joins are not automatic for large tables. Round-robin distribution is a table property, not a per-query fallback.

Question 40Design and implement data storage

Which Azure Synapse Analytics feature allows you to define external tables over files in Azure Data Lake Storage Gen2 for querying with T-SQL?

  1. Dedicated SQL pool with PolyBase
  2. Serverless SQL pool with OPENROWSET Correct answer.
  3. Data Explorer pool
  4. Spark pool with Delta tables

Explanation

Serverless SQL pools support OPENROWSET and external tables to query files in Azure Data Lake Storage Gen2 directly with T-SQL. Dedicated SQL pools use PolyBase for loading, not ad hoc external queries. Spark pools and Data Explorer pools use different query languages.

Question 41Design and implement data storage

Which Azure Cosmos DB consistency level guarantees that reads never see out-of-order writes and offers the strongest consistency?

  1. Session
  2. Eventual
  3. Bounded staleness
  4. Strong Correct answer.

Explanation

Strong consistency guarantees linearizability, meaning reads always see the most recent committed write and never observe out-of-order writes. Bounded staleness, session, and eventual consistency offer weaker guarantees. Strong is the highest consistency level available.

Question 42Design and implement data storage

You need to store large binary files in Azure Storage and serve them with low latency to users worldwide. Which storage tier and replication option should you choose?

  1. Hot tier with read-access geo-redundant storage Correct answer.
  2. Archive tier with geo-redundant storage
  3. Cool tier with locally redundant storage
  4. Cold tier with zone-redundant storage

Explanation

The hot tier is optimized for frequent access with low latency, and read-access geo-redundant storage (RA-GRS) replicates data to a secondary region with read access, improving global read latency. Cool and archive tiers are for infrequent access. Locally redundant storage does not provide geographic distribution.

Question 43Design and implement data storage

Which Delta Lake operation should be used to apply updates and inserts from a source DataFrame to a target Delta table in a single atomic transaction?

  1. INSERT OVERWRITE
  2. COPY INTO
  3. VACUUM
  4. MERGE INTO Correct answer.

Explanation

MERGE INTO performs upserts by matching source rows to target rows and applying updates, inserts, or deletes atomically. INSERT OVERWRITE replaces data, COPY INTO loads files, and VACUUM removes old files. MERGE is the standard for change data capture and upserts.

Question 44Design and implement data storage

A team wants to automatically compact small files and optimize data layout in a Delta table without manual commands. Which feature should they enable?

  1. Time travel
  2. Auto Optimize Correct answer.
  3. Delta Sharing
  4. Change Data Feed

Explanation

Auto Optimize automatically compacts small files after writes and can optimize data layout, reducing the need for manual OPTIMIZE commands. Change Data Feed tracks row-level changes, Delta Sharing shares data externally, and time travel queries historical versions. Auto Optimize is designed for automatic file management.

Question 45Design and implement data storage

Which Azure Data Factory activity should be used to copy data from an on-premises SQL Server to Azure Data Lake Storage Gen2 through a self-hosted integration runtime?

  1. Get Metadata activity
  2. Lookup activity
  3. Stored Procedure activity
  4. Copy activity Correct answer.

Explanation

The Copy activity moves data between supported sources and sinks, and a self-hosted integration runtime enables connectivity to on-premises SQL Server. Lookup retrieves a single value, Get Metadata inspects dataset structure, and Stored Procedure executes database procedures. Copy is the correct activity for data movement.

Question 46Design and implement data storage

You are designing a partitioned Delta table and want to avoid the small file problem. Which approach is most effective?

  1. Increase the number of partitions to one per row
  2. Partition only on low-cardinality columns and use OPTIMIZE Correct answer.
  3. Partition on every column to maximize pruning
  4. Avoid partitioning entirely and use Z-ORDER only

Explanation

Partitioning on low-cardinality columns limits the number of directories and files, and running OPTIMIZE compacts small files. Partitioning on every column or creating one partition per row explodes file counts. Avoiding partitioning may be acceptable but does not address compaction directly.

Question 47Design and implement data storage

Which Azure Synapse dedicated SQL pool distribution type is best for a large fact table that is joined on a single key and has many rows?

  1. Hash distribution on the join key Correct answer.
  2. Round-robin distribution
  3. Replicated distribution
  4. Partitioned distribution

Explanation

Hash distributing a large fact table on the frequently joined key co-locates matching rows on the same compute node, minimizing data movement during joins. Round-robin spreads rows evenly but does not align joins. Replicated distribution is for small tables. Partitioned is not a distribution type.

Question 48Design and implement data storage

Which Azure Storage access tier is most cost-effective for blobs that are rarely accessed but must be available within milliseconds when needed?

  1. Premium
  2. Archive
  3. Hot
  4. Cool Correct answer.

Explanation

The cool tier is designed for infrequently accessed data that still requires low-latency access, offering lower storage costs than hot but higher access costs. Archive is for long-term retention with rehydration delays. Hot is for frequent access, and premium is for high-performance scenarios.

Question 49Design and implement data storage

A Cosmos DB container uses a partition key with low cardinality, causing hot partitions. Which strategy best mitigates this?

  1. Increase the provisioned RU/s
  2. Enable multi-region writes
  3. Switch to strong consistency
  4. Use a synthetic partition key with higher cardinality Correct answer.

Explanation

A synthetic partition key combines the original key with additional values to increase cardinality, distributing data and throughput more evenly across partitions. Increasing RU/s does not fix hot partitions. Multi-region writes and strong consistency do not address partition key cardinality.

Question 50Design and implement data storage

Which Azure Data Lake Storage Gen2 feature provides fine-grained access control at the directory and file level using POSIX-style permissions?

  1. Storage account keys
  2. Access control lists (ACLs) Correct answer.
  3. Role-based access control (RBAC)
  4. Shared access signatures

Explanation

Access control lists (ACLs) in Data Lake Storage Gen2 provide POSIX-style permissions at the directory and file level, enabling fine-grained access control. RBAC operates at the management plane and is less granular. SAS tokens and account keys are broader access mechanisms.