DP-203 Practice Test 8 — 10 Questions

Question 1

You have an Azure Synapse Analytics dedicated SQL pool that handles both high-priority real-time queries and low-priority batch jobs. You need to ensure that high-priority queries always get the resources they need, while batch jobs do not starve. What should you configure?

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

Accepted Answer

Create workload groups for high-priority and low-priority queries, assigning appropriate importance and resource percentages. Workload groups in a dedicated SQL pool let you classify requests into groups and assign each group an importance level (e.g., HIGH for real-time queries, LOW for batch) plus a resource percentage (min/max memory and concurrency). This ensures high-priority queries preempt lower-priority ones for resources while guaranteeing batch jobs a floor so they don't starve. It is the native workload management mechanism for dedicated SQL pools.

Answer

Enable result-set caching for the high-priority queries

Answer

Enable data compression on the tables used by batch jobs

Answer

Create materialized views for the batch job queries

Question 2

Your team is using Azure Synapse Analytics to process sensitive customer data. You need to ensure that column-level security is applied to a specific table so that only users with a certain role can view certain columns. Which feature should you use?

Accepted Answer

Column-level security (CLS). Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table based on the user's role or permissions. It is implemented using GRANT and DENY statements on individual columns, ensuring that only authorized users can view sensitive columns. This directly addresses the requirement to apply column-level security to a specific table.

Answer

Row-level security (RLS)

Answer

Azure Purview data policies

Answer

Dynamic data masking (DDM)

Question 3

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 4

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

Accepted Answer

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

Answer

It configures the workspace to use a user-assigned managed identity

Answer

It disables public network access to the workspace

Answer

It creates the workspace in a private endpoint configuration

Question 5

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

Accepted Answer

Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'. Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Answer

Set the 'Staging' option to 'Use staging'

Answer

Enable staging for the sink and use PolyBase

Answer

Set the 'Partition option' to 'Round robin' on the source

Question 6

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

Accepted Answer

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

Answer

Store all data in Azure SQL Database with partitioning and drop older partitions

Answer

Use Azure Data Lake Storage Gen2 with a single storage tier

Answer

Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

Question 7

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

Accepted Answer

Use an auto-scaling cluster with spot instances.. Auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Answer

Use a fixed-size cluster with premium tier.

Answer

Use a Photon-accelerated cluster with premium tier.

Answer

Use an interactive cluster with a large number of workers.

Question 8

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

Accepted Answer

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

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 9

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

Accepted Answer

Use mapping data flows in Azure Synapse pipelines.. Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless. Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Answer

Use Azure Stream Analytics to transform the data in real time.

Answer

Use PolyBase to load data and then use T-SQL stored procedures to transform.

Answer

Use Azure Databricks notebooks with Spark to perform transformations.

Question 10

You have an Azure Synapse Analytics dedicated SQL pool that handles both high-priority real-time queries and low-priority batch jobs. You need to ensure that high-priority queries always get the resources they need, while batch jobs do not starve. What should you configure?

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

Accepted Answer

Increase the resource class for the user executing the queries.. Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Answer

Redistribute the tables using hash distribution.

Answer

Rebuild all columnstore indexes.

Answer

Add partitioning to the tables.