Data Engineering Challenge: Golden Customer Record Builder

Scenario

You are working on a data platform that collects customer information from various departments. Each group maintains its own version of the customer records, leading to duplicates and inconsistencies. Your task is to create a single golden customer record for each unique customer.

Input Files

Download the files below and assume they are ingested from different teams:

• customers_a.csv
• customers_b.csv

Your Task

1. Load both CSVs using Python (Pandas or PySpark).
2. Standardize schemas:
   • customers_a.csv has first_name and last_name
   • customers_b.csv has a full_name field — split it into first_name and last_name
3. Identify matching customers using the email field
   (assume email is a unique identifier)
4. Merge customer records into a single "golden record", keeping:
   • The most recent value for each field based on updated_at
   • Any non-conflicting fields (e.g., include phone number if available)
5. Output the final golden record table as either:
   • A Pandas DataFrame, or
   • A new CSV file

Bonus (Optional)

• Add a confidence score per record based on how many sources contributed
• Discuss how you would productionize this using:
  • Orchestration (e.g., Airflow, Dagster)
  • Data modeling (e.g., dbt)
  • Scalable execution (e.g., Spark)
• Handle fuzzy matches for names or emails (e.g., small typos)

Skills Tested

• Data wrangling and transformation
• Schema alignment and normalization
• Deduplication and conflict resolution
• Master data management (MDM) thinking