50 dp 203 case study questions Practice Questions: Question Bank 2025

You are designing a data lake in Azure Data Lake Storage Gen2 to store raw files from multiple source systems. You must enforce folder-level access so that each team can only read and write to its own folder using Azure AD identities. What should you use?

You need to ingest daily CSV files from an SFTP server into Azure Data Lake Storage Gen2 with minimal code and built-in scheduling. The ingestion must copy the files as-is without transformations. Which service should you use?

You have an Azure Synapse dedicated SQL pool. You want to minimize the cost of loading a large fact table while maximizing load throughput. Which approach is recommended?

You manage an Azure SQL Database used as a serving layer for curated data. You need to ensure data at rest is encrypted without requiring application changes. What should you enable?

A pipeline in Azure Data Factory loads data from ADLS Gen2 into an Azure Synapse dedicated SQL pool. You must prevent duplicate rows when the pipeline reruns for the same date due to upstream retries. What is the best approach?

You run Spark jobs in Azure Synapse that read Parquet files from ADLS Gen2. A query frequently filters by eventDate and customerId. You want to reduce the amount of data scanned and improve performance without changing the compute. What should you do?

A team reports that a Synapse dedicated SQL pool query suddenly runs much slower after a large data load. You suspect skewed data distribution is causing excessive data movement. Which feature should you use first to diagnose data distribution and skew?

You need to process streaming IoT telemetry and land it into ADLS Gen2 in near real time, while also performing windowed aggregations (for example, 5-minute averages) before writing results to Azure Synapse. Which solution best fits?

Your organization requires that secrets used by Azure Data Factory linked services (such as database passwords) are not stored in ADF and can be rotated centrally. Pipelines must retrieve secrets at runtime without code changes to activities. What should you implement?

You need to implement an incremental load from a source SQL database to a Delta table in a lakehouse using Spark. The source table has an increasing identity column and supports change tracking via a lastModifiedDate column that can be updated out of order. You must correctly capture late updates and ensure exactly-once effects in the Delta target. What is the best approach?

You need to create an Azure Data Lake Storage Gen2 container and ensure analysts can only read data while the data engineering team can read and write. The analysts should not be able to list or read data in other containers in the same storage account. What should you use?

You ingest CSV files daily into Azure Data Lake Storage Gen2. A Synapse serverless SQL pool queries the files directly using OPENROWSET. Queries are slow because each query scans all files. What should you do to improve query performance with minimal operational overhead?

A pipeline in Azure Data Factory fails with an authentication error when writing to Azure Data Lake Storage Gen2. You must fix the issue using best practices and without storing secrets in the pipeline. What should you configure?

You stream IoT telemetry into Azure Event Hubs and process it using Azure Stream Analytics (ASA). You must write the output to a Synapse dedicated SQL pool table with the highest throughput and support for parallel loads. What should you use for the ASA output sink?

A Synapse Spark job writes curated data to ADLS Gen2. Downstream consumers need to query only committed, consistent snapshots and must not see partial writes. Which storage format and write approach best meets this requirement?

You are designing a slowly changing dimension (SCD) Type 2 load into a Synapse dedicated SQL pool. The source delivers full extracts each day. You need to identify new and changed rows efficiently. What is the recommended pattern?

Your Synapse dedicated SQL pool has unpredictable query performance due to resource contention between ELT loads and ad-hoc analytics. You need to ensure ad-hoc queries always have reserved resources. What should you configure?

A Data Factory pipeline runs a Mapping Data Flow and intermittently fails with out-of-memory errors during a join. You need to reduce memory pressure while keeping the transformation in Data Flow. What should you do?

You must design an enterprise data platform where curated datasets in ADLS Gen2 can be discovered and consumed by multiple teams. Requirements: central catalog, business glossary support, data lineage from ADF/Synapse pipelines, and controlled access requests. Which service best meets these requirements?

You have an Azure Synapse dedicated SQL pool with very large tables stored as clustered columnstore indexes. After daily loads, query performance degrades significantly. Investigation shows a high percentage of small rowgroups and many deleted rows. What should you do to restore columnstore efficiency?

You are ingesting JSON files into Azure Data Lake Storage Gen2. Each file contains new attributes over time, and downstream users want to query all attributes without frequently changing table definitions. Which approach should you implement in Azure Synapse Analytics to handle schema drift with minimal maintenance?

A data pipeline in Azure Data Factory must copy hourly CSV files from an SFTP server to Azure Data Lake Storage Gen2. The pipeline should automatically pick up any new files and avoid re-copying files that were already ingested. What is the recommended approach?

You need to allow an Azure Synapse Analytics workspace to read data from an Azure Data Lake Storage Gen2 account without managing shared keys. What should you use?

A Spark job in Azure Synapse reads Parquet data from ADLS Gen2 and performs heavy joins. The job frequently fails with out-of-memory errors on executors. Which change is MOST likely to resolve the issue with minimal code changes?

You run a nightly ELT process in a dedicated SQL pool. Recently, query performance degraded significantly after loading a large fact table. You suspect high fragmentation in columnstore indexes. What is the recommended remediation?

A data engineering team needs to ensure that secrets used by Azure Data Factory linked services (for example, database credentials) are centrally managed and automatically rotated. Which solution best meets the requirement?

You are designing a lakehouse on ADLS Gen2 where multiple teams will write to the same set of tables and require ACID transactions, schema enforcement, and time travel. Which storage format and approach should you choose in Azure Synapse Spark?

You use Azure Synapse dedicated SQL pool. A query that aggregates a very large fact table and joins to multiple dimensions is slow. The fact table is distributed ROUND_ROBIN and the largest dimension is REPLICATE. You observe high data movement in query plans. Which design change is MOST likely to reduce data movement for common star-schema queries?

A streaming solution ingests events into Azure Event Hubs and processes them using Spark Structured Streaming in Azure Synapse. You need exactly-once processing semantics when writing results to the lake so that reprocessing after failures does not create duplicates. What is the best approach?

Your organization requires private network access only. You must allow Azure Synapse pipelines to connect to an Azure SQL Database without traversing the public internet. The Synapse workspace uses a managed virtual network. Which configuration should you implement?

You are designing a data lake on Azure Data Lake Storage Gen2 for a large analytics platform. Multiple teams will access data via Azure Synapse and Azure Databricks. You must enforce file- and folder-level permissions with POSIX-like ACLs. What should you use?

You ingest a daily full snapshot of customer data (hundreds of millions of rows) into a dedicated SQL pool in Azure Synapse Analytics. Queries typically filter by snapshot date. You need to improve query performance and simplify loading. What should you implement?

You are building an Azure Data Factory pipeline to copy data from an on-premises SQL Server to Azure SQL Database. The on-premises network does not allow inbound connections. What do you need to install to enable the connection?

A Synapse Spark job writes Parquet files to ADLS Gen2. Downstream consumers report that the output contains many very small files, which slows read performance. What is the most appropriate fix?

You orchestrate a pipeline that must execute 20 independent copy activities in parallel. However, you must limit concurrency to avoid saturating the source system. Which Azure Data Factory capability should you use?

You have streaming telemetry arriving in Azure Event Hubs. You need to aggregate events into 5-minute windows and write results to Azure Data Lake Storage Gen2 in near real time. Which solution is most appropriate?

You need to allow a Synapse pipeline to read secrets (such as database passwords) without storing them in code or pipeline JSON. You also must enable secret rotation without modifying pipelines. What should you use?

A Delta Lake table in Azure Databricks is frequently updated and queried. You notice query performance degradation over time due to many small files and outdated statistics. Which maintenance approach is recommended?

You manage a dedicated SQL pool in Azure Synapse. Users report inconsistent performance during concurrent BI workloads. You need to identify which queries are consuming the most resources and causing contention. What should you use?

A data engineering team must ensure that only corporate-managed devices can access sensitive curated data in ADLS Gen2 through Synapse serverless SQL. Users authenticate with Microsoft Entra ID. You need to enforce device compliance as a condition of access. What should you implement?

You need to store raw IoT JSON files in Azure Data Lake Storage Gen2. Data producers must only be able to write new files (no reads, no deletes, no overwrites). What is the BEST way to enforce this requirement?

A Synapse dedicated SQL pool is loading data from parquet files in ADLS Gen2. Loads are slow and you observe significant data movement during INSERT...SELECT operations. Which table design choice is MOST likely to reduce data movement for large fact table loads?

You are building a near-real-time ingestion pipeline using Azure Stream Analytics (ASA). Events occasionally arrive late and out of order. You need correct aggregations in 5-minute windows. Which ASA feature should you configure?

A Data Factory pipeline copies data from an on-premises SQL Server to ADLS Gen2 via a self-hosted integration runtime (SHIR). The copy activity intermittently fails with timeouts during peak hours. Which action is the BEST first step to improve reliability without redesigning the pipeline?

You maintain a Delta Lake table in Azure Databricks. Downstream consumers require the ability to query the table as of a specific point in time to reproduce historical reports. What should you use?

A Spark job in Azure Synapse Analytics reads a large ADLS Gen2 folder with millions of small files and runs slowly. You want to improve performance with minimal changes while preserving the data format. What should you do?

You need to orchestrate an end-to-end pipeline: (1) run a Spark notebook, (2) if it succeeds, execute a stored procedure in a Synapse dedicated SQL pool, and (3) if either step fails, send an alert. Which service is BEST suited to implement this orchestration with built-in activity dependencies and failure paths?

You are designing security for a data lake in ADLS Gen2. The organization requires that access decisions be made using centralized, role-based assignments at scale, and that users should not need to manage file-level ACLs directly. What is the BEST approach?

A Synapse dedicated SQL pool shows poor query performance due to skew: some distributions have much more data than others. You suspect a hash-distributed table uses a skewed key. What should you do FIRST to validate and address the issue?

Your organization must ensure that sensitive columns (for example, National ID) are never exposed in query results unless a user is explicitly authorized. Analysts should still be able to query other columns without modification to their SQL. You are using Azure Synapse dedicated SQL pool. What is the BEST solution?