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

Questions 46–50

1. 46foundation · easy

   Which of the following is a common technique used to identify new or changed rows for an incremental data load?

   • AUsing a random sample of rows from the source
   • BDeleting all rows in the destination table before loading
   • CLoading all rows and then deduplicating them
   • DUsing a watermark column that stores the last processed timestamp or high-water mark
   Reveal Solution Discussion Select an answer first
2. 47foundation · easy

   What is the primary purpose of exploratory data analysis (EDA)?

   • ATo enforce data quality rules on a dataset
   • BTo deploy machine learning models to production
   • CTo optimize query performance
   • DTo understand the main characteristics of a dataset and uncover patterns
   Reveal Solution Discussion Select an answer first
4. 48application · medium

   A retail company loads daily sales data from an on-premises SQL Server into Azure Synapse Analytics. The source table has a LastModifiedDate column that is updated whenever a row changes. The pipeline runs every hour and must only process rows that changed since the previous run. You need to configure the Azure Synapse Pipeline to perform an incremental load. What should you do?

   • AUse a Data Flow activity with a Source transformation that reads the entire table and a Sink that uses upsert on LastModifiedDate.
   • BUse a Lookup activity to get the previous high-water mark, then a Copy activity with a query that filters on LastModifiedDate > the high-water mark.
   • CUse a Copy activity that truncates the destination table and reloads all rows from the source each time.
   • DUse a Stored Procedure activity that runs a MERGE statement joining on LastModifiedDate to upsert all rows.
   Reveal Solution Discussion Select an answer first
5. 49application · medium

   A company ingests data from a legacy system that sends files encoded in Base64. The data is processed using Azure Synapse Analytics with Apache Spark. You need to decode the Base64 data before further processing. What should you do?

   • AUse a Stream Analytics query with the decode function.
   • BUse the base64 decoder in a Spark UDF or the built-in decode function.
   • CUse a T-SQL query with the CAST function to decode Base64.
   • DUse a Data Factory Copy activity to decode Base64 during the copy.
   Reveal Solution Discussion Select an answer first
6. 50application · medium

   A healthcare organization receives patient records as JSON files in Azure Blob Storage. Each file contains an array of records with nested fields. They use Azure Data Factory to load the data into an Azure SQL Database. The data contains duplicate patient IDs that must be removed. You need to transform the JSON into a relational format and remove duplicates during the load. What should you configure?

   • AA Data Flow with a 'Select' transformation to choose fields, but no duplicate removal.
   • BA Mapping Data Flow with a 'Flatten' transformation, a 'Distinct' transformation on patient ID, and a sink to Azure SQL Database.
   • CA Copy activity that copies the JSON files directly to Azure SQL Database.
   • DA Lookup activity to read the JSON, then a Stored Procedure activity to insert the data.
   Reveal Solution Discussion Select an answer first