Ingest and Transform Data DP-203 Practice Questions (Page 5)

Questions 21–25

1. 21application · medium

   A logistics company uses Azure Synapse Pipelines to ingest shipment tracking data from multiple REST APIs into a dedicated SQL pool. The APIs sometimes return partial data or fail intermittently. You need to ensure that the pipeline retries failed activities and logs errors for later analysis, without reprocessing the entire dataset. What should you configure?

   • AConfigure a retry policy on the pipeline and use a 'Skip on success' property on the Copy activity.
   • BSet the retry policy on each Copy activity to 3, and add an error output to write failed rows to a separate table.
   • CUse a Web activity to call the API and ignore any failures.
   • DSet the pipeline to run on a schedule and use a full load each time.
   Reveal Solution Discussion Select an answer first
2. 22application · medium

   A marketing analytics team receives customer data from multiple sources. The data has inconsistent date formats, missing values in the 'age' column, and some records have the same customer ID but different email addresses. You need to clean the data to ensure consistency and accuracy before loading it into a data warehouse. What should you do?

   • AUse a Spark notebook to drop all rows with missing values.
   • BUse Azure Data Factory Mapping Data Flow to parse dates, impute missing ages with the mean, and use a Conditional Split to keep only one record per customer ID.
   • CUse a Copy activity to load the raw data and then use T-SQL to clean it in the warehouse.
   • DUse Azure Stream Analytics to clean the data in real time.
   Reveal Solution Discussion Select an answer first
4. 23expert · hard

   A logistics company has a normalized data model in Azure Synapse Analytics with separate tables for shipments, customers, and locations. Reporting queries frequently join these tables and aggregate by customer region and shipment status. Query performance is slow. You need to improve read performance for the reporting workload while minimizing the impact on data loading. What should you do?

   • AUse a query hint to force a hash join in all reporting queries.
   • BAdd more indexes to the join columns in all tables.
   • CDenormalize the tables by adding customer region and shipment status columns to the shipment table.
   • DCreate a materialized view that pre-joins and aggregates the data by customer region and shipment status.
   Reveal Solution Discussion Select an answer first
5. 24expert · hard

   A financial services company has a normalized star schema in Azure Synapse Analytics with a large fact table (billions of rows) and several dimension tables. Reporting queries join the fact table to dimensions and aggregate by multiple attributes. Query performance is poor. You need to improve read performance for the reporting workload while minimizing storage overhead. What should you do?

   • ANormalize the fact table further by splitting it into multiple smaller tables based on each dimension.
   • BCreate additional indexes on the fact table for each foreign key column used in joins.
   • CReplace the fact table with a view that joins all dimensions and materialize it as a clustered columnstore index.
   • DDenormalize the schema by adding frequently used dimension attributes directly into the fact table.
   Reveal Solution Discussion Select an answer first
6. 25foundation · easy

   Which T-SQL function in Azure Synapse Analytics is used to parse JSON and extract values from a JSON string?

   • ASTRING_SPLIT
   • BJSON_VALUE
   • CJSON_QUERY
   • DOPENJSON
   Reveal Solution Discussion Select an answer first