Build a Transaction CSV Cleaning Pipeline
Transaction CSV Cleaning Pipeline
You are provided with a raw CSV file containing customer transaction data. The file includes the following columns: date, amount, customer_id, payment_method, and notes.
Known Data Quality Issues
The raw data exhibits several quality issues that need to be addressed:
date: Dates appear in mixed formats, such asYYYY-MM-DD,MM/DD/YYYY, andJan 5, 2024.amount: Amounts contain currency symbols and thousands separators, for example,$1,234.56.customer_id: While leading zeros should be preserved, some IDs may have been stripped by spreadsheet software.payment_method: This column contains misspellings and inconsistent capitalization.
Task
Design a robust cleaning pipeline that effectively handles all these identified data quality issues. Additionally, explain what validations you would implement after the cleaning process to confirm that the data is suitable and usable for downstream analytics.
AI interview prep
Core dilemma
Designing a robust, scalable, and maintainable data cleaning pipeline that handles diverse, inconsistent data formats while ensuring data integrity and usability for downstream analytics, especially under varying data volumes and quality.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Build a Transaction CSV Cleaning Pipeline
Transaction CSV Cleaning Pipeline
You are provided with a raw CSV file containing customer transaction data. The file includes the following columns: date, amount, customer_id, payment_method, and notes.
Known Data Quality Issues
The raw data exhibits several quality issues that need to be addressed:
date: Dates appear in mixed formats, such asYYYY-MM-DD,MM/DD/YYYY, andJan 5, 2024.amount: Amounts contain currency symbols and thousands separators, for example,$1,234.56.customer_id: While leading zeros should be preserved, some IDs may have been stripped by spreadsheet software.payment_method: This column contains misspellings and inconsistent capitalization.
Task
Design a robust cleaning pipeline that effectively handles all these identified data quality issues. Additionally, explain what validations you would implement after the cleaning process to confirm that the data is suitable and usable for downstream analytics.