Build a pipeline to load CSVs into a database

WHAT THIS TESTS: This is a foundational data engineering question. It tests your ability to connect standard cloud services into a coherent, automated, and event-driven pipeline. The interviewer is looking for your default "simple is better" architecture. They want to see if you reach for serverless and event-driven patterns first, rather than over-engineering with VMs, cron jobs, or complex orchestration tools for a simple task.

A GOOD ANSWER COVERS: A good answer outlines four main components. First, the trigger: an event notification on the cloud storage bucket (e.g., AWS S3 Event Notification) that fires on object creation. Second, the processor: a serverless function (e.g., AWS Lambda) that receives the event. This function should have the necessary permissions to read from the bucket and write to the database. Third, the logic: inside the function, you stream the CSV, parse it row by row, and batch the data for insertion. Batching is key to avoid overwhelming the database with single-row inserts. Fourth, the destination: a relational database service (e.g., AWS RDS), where the function executes a batch INSERT or MERGE statement. A great answer also mentions error handling, like moving malformed files to a separate "error" or "dead-letter" bucket.

COMMON WRONG ANSWERS: The most common mistake is proposing a polling mechanism, like a cron job running on an EC2 instance that checks the bucket every minute. This is inefficient, introduces latency, and is not event-driven. Another red flag is describing a manual process. A less severe but still weak answer would be to read the entire CSV into the function's memory, which is not scalable and can easily fail with files larger than the available memory (e.g., >256MB for a small Lambda function). Finally, inserting rows one-by-one is a major performance anti-pattern.

LIKELY FOLLOW-UPS: How would you handle schema changes in the CSV? How would you scale this if you received 1,000 files per minute instead of one per day? What if a single file is 100GB? (Hint: This pushes you from Lambda towards services like AWS Glue or container-based processing). How do you handle duplicate data from a file being uploaded twice?

ONE CONCRETE EXAMPLE: A daily 10MB CSV file is dropped into an AWS S3 bucket. An S3 Event Notification for s3:ObjectCreated:* triggers a Python-based AWS Lambda function. The function is configured with 1024MB of memory and a 60-second timeout. It uses the boto3 S3 client to get a streaming body of the object, the csv standard library to parse it, and batches records into groups of 1,000. It then uses the psycopg2 library to connect to an AWS RDS PostgreSQL instance and performs a single INSERT INTO ... VALUES (...), (...), ... statement per batch. If parsing fails, the file is moved to a separate my-bucket-dead-letter S3 bucket for manual inspection.