Adyen India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines, storage systems, and processing frameworks that move and transform data across an organisation. In a global payments tech company, they work on high-volume, low-latency data flows for transactions, risk, and analytics, ensuring reliability and scalability. Day to day involves writing ETL/ELT code, tuning SQL and Spark jobs, and collaborating with analysts and product teams.

Role and Technical Knowledge

1. 1

   What is the difference between ETL and ELT, and when would you choose one over the other in a payments company?

   Explain extraction, transformation, loading order. For payments, ELT is common with cloud warehouses for scalability and raw data retention; ETL suits strict compliance or pre-processing needs.

2. 2

   How would you design a data model for transaction data that supports both real-time fraud detection and monthly reporting?

   Cover a star schema with fact and dimension tables for reporting, plus a streaming layer or change data capture for real-time. Mention partitioning by date and indexing on transaction ID.

3. 3

   Explain how Apache Spark handles a large join operation and what you can do to avoid performance bottlenecks.

   Discuss shuffling, broadcast joins for small tables, partitioning, and salting for skewed keys. Mention monitoring via Spark UI and tuning executor memory.

4. 4

   What are window functions in SQL? Give an example of using them to find the latest transaction per customer.

   Define OVER with PARTITION BY and ORDER BY. Show ROW_NUMBER or RANK to pick the most recent row per customer, and explain how it differs from GROUP BY.

Practical Scenarios

1. 5

   A daily ETL job that loads transaction data into the warehouse has been failing for two days. How do you debug and recover?

   Check logs and error messages, verify source availability, review recent code or schema changes. For recovery, backfill missing partitions and add alerting and idempotency to prevent recurrence.

2. 6

   You need to process 500 GB of logs per day with a Python script that currently takes 10 hours. How would you optimise it?

   Move to distributed processing with Spark, use columnar formats like Parquet, filter early, and increase parallelism. Mention profiling to find bottlenecks before scaling hardware.

3. 7

   How would you ensure data quality and consistency across multiple pipelines feeding a risk dashboard?

   Implement validation checks at ingestion, use schema enforcement, add data quality tests (nulls, duplicates, ranges), and set up monitoring with alerts. Mention a data catalog and lineage tracking.

4. 8

   Describe how you would handle a schema change in an upstream source without breaking downstream reports.

   Use schema evolution features, maintain a staging layer, and apply backward-compatible changes. Communicate with downstream teams and version the schema.

Coding and SQL Problems

1. 9

   Given a table of transactions with columns user_id, amount, and timestamp, write a SQL query to find users whose total transaction amount in the last 7 days exceeds 10,000.

   Use a WHERE clause on timestamp, GROUP BY user_id, SUM(amount), and HAVING. Mention handling time zones and using CURRENT_DATE or a parameter.

2. 10

   Write a Python function to find the top 3 most frequent merchants from a list of transaction records.

   Use a dictionary or collections.Counter to count frequencies, then sort and slice. Discuss time complexity O(n log n) and edge cases like ties.

3. 11

   How would you detect duplicate transactions in a large dataset using SQL?

   Use GROUP BY on key columns (user_id, amount, timestamp) with HAVING COUNT(*) > 1. For large data, mention window functions or hashing to reduce load.

4. 12

   Given two tables, customers and orders, write a query to find customers who have never placed an order.

   Use LEFT JOIN and filter WHERE orders.id IS NULL, or use NOT EXISTS. Explain why NOT IN can be risky with nulls.

Behavioural and HR

1. 13

   Tell me about a time you had to learn a new technology quickly for a project. How did you approach it?

   Use STAR format. Highlight self-learning, resources used, and the outcome. Show curiosity and ability to apply new skills under deadline.

2. 14

   Describe a situation where you disagreed with a teammate on a technical approach. How did you resolve it?

   Focus on data-driven discussion, listening, and compromise. Show you value team success over being right, and mention the final decision and result.

3. 15

   Why do you want to work as a Data Engineer in the payments domain?

   Connect personal interest in scalable systems and real-time data to the impact of payments on millions. Mention eagerness to learn about fraud detection and reliability.

4. 16

   How do you prioritise tasks when you have multiple deadlines and limited time?

   Explain assessing impact and urgency, communicating with stakeholders, and breaking work into smaller chunks. Give an example from college projects or internships.