dp 203 case study questions Advanced Practice Exam: Hard Questions 2025

You are designing an Azure Synapse Analytics dedicated SQL pool to serve BI dashboards with strict latency requirements. Data is ingested hourly into a large fact table (6+ billion rows) and several small dimensions. The workload is read-heavy with frequent joins on CustomerId and DateKey. Updates arrive as late-arriving facts and must be applied without long blocking. Which design choice provides the best overall performance and maintainability while minimizing data movement and reducing update overhead?

You manage an Azure Data Lake Storage Gen2 account used by multiple teams. A new workload requires ingesting millions of small JSON files daily from IoT devices, then running both Spark ETL and serverless SQL queries. Queries frequently filter by deviceId and eventDate. You need to minimize metadata overhead and improve query performance while keeping raw data immutable. What is the best approach?

You are implementing incremental processing in Azure Data Factory (ADF) from an operational SQL database to ADLS Gen2. Source tables have a ModifiedDate column, but updates can occur out of order and the clock on the source system can drift. You must ensure no data loss and minimal reprocessing. Which approach is most robust?

A Spark job in Azure Synapse reads streaming data (Event Hubs) and writes to Delta Lake in ADLS Gen2. You observe occasional duplicates in downstream queries after job restarts, and sometimes the stream fails with checkpoint corruption due to accidental deletion. You need exactly-once processing semantics as much as possible and operational safeguards. What should you do?

You run an ADF mapping data flow that joins a 5 TB fact dataset with a 50 MB dimension dataset. The job intermittently fails due to out-of-memory errors and long shuffle times. The dimension changes daily and must be reflected. What is the best optimization to reduce shuffle and memory pressure while maintaining correctness?

A Synapse Spark ETL writes curated Delta tables. Another team queries those tables via Synapse serverless SQL using OPENROWSET. They report that new columns added by Spark appear as NULL in serverless SQL queries until they manually refresh metadata. You need schema evolution to be reliably visible with minimal manual steps. What should you implement?

Your organization uses Synapse dedicated SQL pool for a multi-tenant analytics platform. You must prevent data exfiltration while allowing analysts to create external tables and run PolyBase/COPY from ADLS Gen2. Security requires that credentials are not embedded in code and that access is auditable and revocable. Which solution best meets these requirements?

A dedicated SQL pool experiences intermittent query slowdowns and occasional 'insufficient resources' errors during peak loads. You suspect poor workload isolation between ETL (large loads) and BI (short queries). You need to guarantee responsiveness for BI queries while still allowing ETL to run. What should you implement?

You are troubleshooting a Synapse dedicated SQL pool where COPY INTO loads from ADLS suddenly fail with authorization errors, but only for some pipelines. The storage account uses hierarchical namespace and ACLs. The managed identity has Storage Blob Data Contributor at the account scope. What is the most likely cause and fix?

A Delta Lake table in ADLS is used for downstream reporting. A Spark job performs frequent upserts (MERGE) and deletes for GDPR compliance. Over time, query performance degrades and storage grows rapidly. You must improve read performance and control storage without breaking time travel requirements beyond 30 days. What should you do?