DP-203 Practice Test 28 — 15 Questions

Question 1

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Partition the table on the date column.. Partitioning the table on the date column enables partition elimination, which allows the query to scan only the relevant partitions instead of the entire table. Option A is incorrect because a nonclustered index on the date column can help with point lookups but is less effective for large scans and filtering on a range of dates. Option B is incorrect because a clustered columnstore index improves compression and scan performance but does not provide partition elimination for date filtering. Option C is incorrect because round-robin distribution distributes data evenly but does not optimize for filtering on a specific column.

Answer

Create a nonclustered index on the date column.

Answer

Create a clustered columnstore index on the table.

Answer

Change the distribution to round-robin.

Question 2

You are running a Spark notebook in Azure Synapse Analytics that reads from a Delta table and writes to a Parquet file. The job fails with the error: 'AnalysisException: Table or view not found: bronze.sales'. The table exists in the lakehouse. What is the most likely cause?

Accepted Answer

The table is not registered in the Spark metastore; it is only in the lakehouse.. In Azure Synapse Analytics, a Spark notebook uses its own Spark metastore to resolve table references. If the table 'bronze.sales' exists only in the lakehouse (i.e., as a Delta table in the underlying storage) but is not registered in the Spark metastore, the Spark engine cannot find it and throws an AnalysisException. The error indicates a metadata resolution failure, not a permission or data corruption issue.

Answer

The user does not have read permission on the table.

Answer

The Parquet file format is incompatible with the Delta source.

Answer

The Delta table is corrupted.

Question 3

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

Accepted Answer

SQL Server Integration Services (SSIS) package. 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.

Answer

PolyBase from external table

Question 4

A company uses Azure Synapse Analytics with a dedicated SQL pool. Data engineers need to implement column-level security so that only users with the 'Manager' role can see salary columns. Which TWO actions should they take?

Accepted Answer

Create a stored procedure that checks the user's role and returns the appropriate columns.. To implement column-level security in Azure Synapse Analytics dedicated SQL pool, a valid approach is to create a stored procedure that checks the user's role and returns only permitted columns, as dedicated SQL pool does not support column-level GRANT permissions or native column-level security. GRANT SELECT on a specific column (Option E) is not supported in dedicated SQL pool and will result in an error. Options C and D describe components of row-level security, not column-level security.

Answer

Grant the 'Manager' role SELECT permission on the security policy.

Answer

Create a security policy with a filter predicate on the salary column using the function, and set the state to ON with BLOCK predicate.

Answer

Create an inline table-valued function that returns 1 if the user is a member of the 'Manager' role, else 0.

Answer

Use GRANT SELECT ON OBJECT::[dbo].[Employee](Salary) TO [Manager] to grant access to the salary column.

Question 5

You are reviewing the ARM template above. The storage account is created with hierarchical namespace enabled (isHnsEnabled: true). After deployment, you need to ensure that the 'data-engineers' group can execute but not read the contents of the root directory. What should you do?

Accepted Answer

Use the Azure portal to set ACLs on the root directory, granting execute permission to the data-engineers group without read permission. In Azure Data Lake Storage Gen2 (with hierarchical namespace enabled), ACLs are used to set granular permissions at the directory or file level. To grant only execute permission on the root directory without read, you must set an ACL entry that specifically grants execute (--x) to the 'data-engineers' group. Option A is incorrect because disabling hierarchical namespace would change the storage type and not achieve the desired ACL-based permission. Option B is incorrect because the Storage Blob Data Reader role grants read and list access, not just execute. Option C is incorrect because firewall rules control network access, not permissions on directories.

Answer

Modify the ARM template to set the 'isHnsEnabled' property to false and redeploy

Answer

Assign the Storage Blob Data Reader role to the data-engineers group at the storage account level

Answer

Configure a firewall rule to allow only the data-engineers group's IP addresses

Question 6

Which THREE methods can you use to authenticate an Azure Data Factory pipeline to access Azure Data Lake Storage Gen2? (Choose THREE)

Accepted Answer

Use a service principal with a client secret or certificate.. The correct answers are A, B, and C. Azure Data Factory supports three authentication methods for ADLS Gen2: service principal with client secret or certificate (A), SAS token (B), and managed identity (C). Service principal uses an Azure AD application. SAS tokens provide scoped access. Managed identity allows Data Factory to authenticate without storing credentials. User account (D) and storage account access key (E) are not valid for ADLS Gen2 authentication.

Answer

Use a user account with password.

Answer

Use the storage account access key.

Question 7

You are an Azure administrator. You apply the Azure Policy shown in the exhibit to a management group. What is the outcome of this policy?

Accepted Answer

It denies storage accounts that allow public network access.. The policy denies the creation of storage accounts that allow public network access. Therefore, Option B is correct. Option A is incorrect because the policy does not require a firewall rule; it denies public access entirely. Option C is incorrect because the policy does not address HTTPS enforcement. Option D is incorrect because the policy only denies storage accounts that allow public network access, not all storage accounts.

Answer

It allows storage accounts only if they have a firewall rule.

Answer

It requires all storage accounts to use HTTPS only.

Answer

It denies the creation of any new storage account.

Question 8

You are a data engineer for a gaming company that uses Azure Data Lake Storage Gen2. The data lake stores player event data in JSON format. The data is organized by date and event type. The analytics team frequently runs queries that filter by player ID to analyze individual player behavior. These queries are slow because they scan entire daily partitions. You need to improve the performance of queries that filter by player ID without restructuring the entire data lake. The data is stored as JSON files. What should you do?

Accepted Answer

Convert the data from JSON to Parquet format and partition by player ID. Converting JSON to Parquet enables columnar storage, which significantly reduces I/O by reading only the columns needed for queries. Partitioning by player ID further improves performance by allowing partition elimination, so queries filter only the relevant partitions instead of scanning entire daily partitions. This approach directly addresses the slow queries without restructuring the entire data lake.

Answer

Compress the JSON files using gzip to reduce I/O

Answer

Create indexes on the player ID field in the data lake

Answer

Repartition the data by hour to reduce the data scanned per partition

Question 9

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

Accepted Answer

Check the input data schema and ensure it matches the query. Input deserialization errors indicate that the incoming data format does not match the schema defined in the Stream Analytics query. Increasing watermark delay is a symptom of this mismatch, as the job cannot parse events correctly and falls behind. Checking and aligning the input schema with the query is the first and most direct troubleshooting step.

Answer

Change the output to a different sink

Answer

Increase the number of Streaming Units (SUs)

Answer

Set the watermark delay threshold higher

Question 10

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

Accepted Answer

Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*). Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Answer

Increase the maximum query concurrency limit

Answer

Provision a dedicated SQL pool with more DTUs

Answer

Create a clustered columnstore index on the date column

Question 11

You are developing a data processing pipeline in Azure Synapse Analytics. The pipeline uses a mapping data flow to transform data from Azure Data Lake Storage Gen2 to a dedicated SQL pool. The data flow includes a Derived Column transformation that uses the expression: `iif(isnull(Column1), 'Default', Column1)`. However, the transformation is not handling NULL values correctly. What is the most likely cause?

Accepted Answer

The function names in the expression are case-sensitive; use 'isNull' instead of 'isnull'.. The function names in Azure Synapse mapping data flows are case-sensitive. The expression uses `isnull` (all lowercase), but the correct function name is `isNull` (capital 'N'). When the function name is typed with incorrect casing, the Derived Column transformation fails to recognize the function and does not evaluate the null check correctly, leading to NULL values not being replaced with 'Default'.

Answer

The Derived Column transformation does not support the iif function; use a Conditional Split instead.

Answer

The column data type is not string; convert Column1 to string first.

Answer

The expression must use ternary operator syntax: `Column1 == null ? 'Default' : Column1`.

Question 12

You are configuring security for an Azure Synapse Analytics workspace that uses a serverless SQL pool. The workspace is connected to Azure Data Lake Storage Gen2 via a managed identity. You need to ensure that only the Synapse workspace can access the storage account, and no other Azure service or user can access it directly. The storage account should not be accessible from the public internet. What should you do?

Accepted Answer

Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access.. Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access. This ensures that only the Synapse workspace, which is connected via the private endpoint, can access the storage account. The managed identity is used for authentication, but the private endpoint restricts network access. Option A is incorrect because public IP addresses can change and do not provide secure, private connectivity. Option B is incorrect because allowing trusted Microsoft services would permit other Azure services to access the storage account, not just the Synapse workspace. Option C is incorrect because RBAC alone does not restrict network access; the storage account would still be publicly accessible.

Answer

Configure the storage account firewall to allow only the Synapse workspace's public IP address.

Answer

Enable 'Allow trusted Microsoft services to access this storage account' on the firewall.

Answer

Use Azure RBAC to assign the Storage Blob Data Contributor role to the Synapse workspace managed identity.

Question 13

Which THREE factors should you consider when designing a monitoring strategy for Azure Synapse Analytics dedicated SQL pool performance?

Accepted Answer

Use dynamic management views (DMVs) to identify long-running queries.. Dynamic management views (DMVs) in Azure Synapse Analytics dedicated SQL pool, such as sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps, provide real-time insight into query execution, allowing you to identify long-running queries, monitor resource consumption, and detect performance bottlenecks. This is a foundational monitoring practice for tuning workload performance.

Answer

Ensure data is evenly distributed across distributions.

Answer

Configure automatic index rebuild for columnstore indexes.

Question 14

Drag and drop the steps to convert data from CSV to Parquet format using Azure Data Factory into the correct order.

Accepted Answer

Define source CSV dataset, then define sink Parquet dataset, then create copy activity with mapping, then run and monitor.. Define source (CSV) and sink (Parquet) datasets, then a copy activity with mapping, run, and monitor.

Answer

Define sink Parquet dataset, then define source CSV dataset, then create copy activity with mapping, then run and monitor.

Answer

Define source CSV dataset, then create copy activity with mapping, then define sink Parquet dataset, then run and monitor.

Answer

Create copy activity with mapping, then define source CSV dataset, then define sink Parquet dataset, then run and monitor.

Question 15

Drag and drop the steps to set up Azure Purview for data cataloging and lineage tracking into the correct order.

Accepted Answer

Create Azure Purview account, then Register data sources, then Scan data sources, then Set classifications, then Explore catalog. First create the Purview account, then register sources, scan them, set classifications, and finally explore the catalog.

Answer

Create Azure Purview account, then Register data sources, then Set classifications, then Scan data sources, then Explore catalog

Answer

Register data sources, then Create Azure Purview account, then Scan data sources, then Set classifications, then Explore catalog

Answer

Create Azure Purview account, then Scan data sources, then Register data sources, then Set classifications, then Explore catalog