Nightly Customer Export Cleanup
Each night a CSV export lands in a shared folder: 48,000 rows with columns CustomerID, Email, SignupDate, Country, and LifetimeAmount. The source team warns that duplicate CustomerID rows occur when a customer changes country; the latest SignupDate row is authoritative. Email values arrive with leading and trailing spaces, mixed case, and occasional blanks; signup dates appear as yyyy-MM-dd, dd/MM/yyyy, and MM/dd/yyyy; country values include 'US', 'USA', and 'United States'. Downstream finance needs one clean table plus a reject file. The bot must finish in under 10 minutes and the machine has 8 GB RAM. A developer proposes loading everything into a List(Of String) and using Regex to split.
1. Choose the data structure and processing approach for the 48,000-row file. Explain why the developer's List(Of String)+Regex proposal is or is not appropriate.
Read the worked answer
48,000 rows by 5 columns is small enough for a DataTable on an 8 GB machine; memory is not the constraint. A List(Of String)+Regex split is fragile because commas and quoted fields in Email or Country can break column alignment, and it loses typed columns. Use Read CSV into a DataTable, or a Dictionary(Of String, DataRow) keyed by CustomerID for deduplication. The dictionary gives O(1) lookup and avoids repeated sorting.
Process in one pass if possible: normalize, validate, parse dates, then compare with the existing row for that CustomerID and keep the later SignupDate. A full sort is acceptable but unnecessary. The 10-minute limit is comfortable if you avoid per-row Excel or UI calls and avoid compiling a new Regex inside the loop.
2. Specify the order of normalization and deduplication, including how to treat ambiguous dates and invalid emails.
Read the worked answer
Normalize first, then validate, then parse dates, then deduplicate. Email: trim, lowercase, validate with a simple pattern; blanks or invalid values go to the reject file with the original row and reason. Country: map variants ('US','USA','United States') to a canonical code via a lookup table, not nested Ifs.
Dates: parse with explicit formats. dd/MM/yyyy versus MM/dd/yyyy is ambiguous when both day and month are 12 or less; do not let the runtime culture decide silently. If the source has a per-row locale or country, use it; otherwise send ambiguous dates to the reject file for review. Deduplicate only rows with a valid parsed date, keeping the maximum SignupDate per CustomerID. Rows rejected for date ambiguity must not overwrite a good row. Preserve original values in the reject file for audit.