dp 203 case study questions Intermediate Practice Exam: Medium Difficulty 2025

You are designing a data lake solution in Azure Data Lake Storage Gen2 for a financial services company. The solution must support hierarchical namespace, fine-grained access control at the folder and file level, and integration with Azure Active Directory. Different departments need access to specific folders within the same container. What security approach should you implement?

Your organization is ingesting streaming data from IoT devices into Azure Event Hubs. You need to process this data using Azure Databricks with exactly-once processing semantics and maintain checkpoint information for fault tolerance. The processed data should be written to Azure Synapse Analytics. Which approach should you use?

You are implementing a data pipeline in Azure Synapse Analytics that processes sales data from multiple regions. The pipeline needs to execute stored procedures in a dedicated SQL pool only after successfully loading data from Azure Blob Storage. If the stored procedure fails, the pipeline should retry three times with a 5-minute interval between retries. How should you configure the pipeline?

A data engineer needs to implement column-level security in Azure Synapse Analytics dedicated SQL pool for a customer database. The Marketing team should see all columns, while the Support team should not see sensitive columns like CreditCardNumber and SSN. What is the most efficient approach to implement this requirement?

You are designing an Azure Data Factory pipeline that needs to incrementally load data from an on-premises SQL Server database to Azure SQL Database. The source table has a LastModifiedDate column. The pipeline should run daily and only copy records that were modified since the last successful run. What components should you use?

Your company uses Azure Synapse Analytics with a dedicated SQL pool. Query performance has degraded over time, and you notice that most queries involve filtering on the OrderDate column and joining with a dimension table. The fact table has 500 million rows. What combination of optimizations should you implement to improve query performance?

You are implementing a data lake architecture where raw data lands in a Bronze layer, curated data resides in a Silver layer, and aggregated data is in a Gold layer. You need to implement a process that validates data quality during the Bronze-to-Silver transformation, logs any quality issues to a separate table, and continues processing valid records. Which Azure Databricks approach should you use?

Your organization needs to monitor Azure Data Factory pipeline runs and send alerts when any pipeline fails. The alert should include the pipeline name, run ID, and error message, and should be sent to a Microsoft Teams channel. What solution should you implement?

You need to design a storage solution for a data warehouse in Azure that will store 10 TB of historical data that is rarely accessed but must be retained for 7 years for compliance. The data needs to be queried occasionally (less than once per month) with acceptable latency of several hours. What is the most cost-effective storage configuration?

You are implementing a Delta Lake table in Azure Databricks that tracks customer transactions. The table receives continuous updates throughout the day. Analysts need to run reports on a consistent snapshot of data from 8 AM each day, while data engineers need to access the latest data. Some queries are running slowly due to small files accumulating from streaming writes. What combination of Delta Lake features should you implement?