To ingest CustomerMaster and keep only the latest record for each Customer ID, which combination of applied steps should you implement in a Dataflow transformation?

To ingest CustomerMaster and keep only the latest record for each Customer ID, which combination of applied steps should you implement in a Dataflow transformation?

• A. Sort on Customer ID, EffectiveDate and remove duplicates on the Customer ID column
• B. Group by Customer ID and take max(EffectiveDate)
• C. Remove duplicates on the EffectiveDate
• D. Filter to the most recent record per Customer ID using a window function

Answer: (A)

To keep only the latest record per customer, you need a definitive way to identify the most recent row for each ID and then collapse duplicates to a single row. Sorting by Customer ID groups the records for each customer together and ordering by EffectiveDate places the most recent date in a predictable position within that group. Then removing duplicates on the Customer ID column reduces each group to one row—the one at the chosen position, which, if you arrange EffectiveDate in the correct order, is the latest record. This approach preserves the full details of the latest record without needing extra steps to fetch corresponding fields from another row, unlike a simple group-by with max(EffectiveDate) which would give you only the date and would require additional joins to recover the full record. The other options either don’t guarantee per-customer recency, or complicate the workflow with windowing or additional joins, making the sort-and-deduplicate method the clearest way to retain the most recent entry for each Customer ID.

To keep only the latest record per customer, you need a definitive way to identify the most recent row for each ID and then collapse duplicates to a single row. Sorting by Customer ID groups the records for each customer together and ordering by EffectiveDate places the most recent date in a predictable position within that group. Then removing duplicates on the Customer ID column reduces each group to one row—the one at the chosen position, which, if you arrange EffectiveDate in the correct order, is the latest record. This approach preserves the full details of the latest record without needing extra steps to fetch corresponding fields from another row, unlike a simple group-by with max(EffectiveDate) which would give you only the date and would require additional joins to recover the full record. The other options either don’t guarantee per-customer recency, or complicate the workflow with windowing or additional joins, making the sort-and-deduplicate method the clearest way to retain the most recent entry for each Customer ID.