Fiserv India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines that move, clean, and store data so analysts and data scientists can use it. In global tech companies, they work with cloud platforms, SQL, and distributed systems to handle large volumes of financial or customer data. A fresher typically starts by writing SQL queries, fixing pipeline failures, and learning tools like Spark, Airflow, and cloud data warehouses.

Role and Technical Knowledge

1. 1

   What is the difference between a data lake and a data warehouse, and when would you use each in a fintech company?

   Explain that a data lake stores raw, schema-on-read data cheaply, while a warehouse stores processed, schema-on-write data for fast queries. Give an example like storing raw transaction logs in a lake and aggregated daily balances in a warehouse.

2. 2

   How would you handle a slowly changing dimension (SCD) Type 2 for a customer address table?

   Describe adding new rows with effective dates and a current flag, and updating the old row's end date. Mention why history matters for regulatory reporting in finance.

3. 3

   Explain how Spark handles a join between a very large table and a small lookup table.

   Mention broadcast join: the small table is sent to every executor to avoid shuffling the large table. Note when it fails (small table too large for memory).

4. 4

   What are the key differences between Parquet and CSV file formats for a data pipeline?

   Cover columnar storage, compression, schema evolution, and faster reads for analytics in Parquet. CSV is row-based, human-readable, but slower and larger.

Practical Scenarios and Problem Solving

1. 5

   Your daily ETL job that loads payment data into the warehouse failed at 2 AM. How do you debug and recover?

   Check logs for the error, identify if it is a source delay, schema change, or resource issue. Fix, rerun the failed partition, and add alerting or idempotency to prevent duplicate loads.

2. 6

   You need to deduplicate millions of transaction records where the same transaction ID appears multiple times. Write a SQL approach.

   Use ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY updated_at DESC) and filter for row_number = 1. Mention handling ties with a secondary sort column.

3. 7

   A business user reports that yesterday's sales dashboard shows zero for one region. Walk through your investigation.

   Check source data for that region, verify the pipeline ran for all partitions, look for filter bugs or timezone issues. Then validate the fix and backfill missing data.

4. 8

   How would you design a pipeline to ingest real-time stock trades and make them available for near real-time analytics?

   Propose a streaming source (Kafka), a processing layer (Spark Structured Streaming or Flink), and a sink (e.g., Cassandra or a real-time OLAP store). Mention exactly-once semantics and late data handling.

SQL and Coding

1. 9

   Write a SQL query to find the second highest salary from an employee table without using LIMIT or TOP.

   Use a subquery with MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employee). Or use DENSE_RANK() and filter for rank = 2.

2. 10

   Given a table of user logins with timestamps, write a query to find users who logged in on consecutive days.

   Use LAG or LEAD to compare dates, or self-join on date + 1. Mention handling date truncation and time zones.

3. 11

   How do you optimize a slow SQL query that joins three large tables?

   Check indexes on join keys, avoid SELECT *, filter early, use EXPLAIN plan, and consider partitioning or materialized views. Mention statistics updates.

4. 12

   Write a Python function to read a CSV file, filter rows where amount > 1000, and write the result to a new CSV.

   Use pandas: read_csv, boolean indexing, to_csv. Mention error handling for missing files and memory considerations for large files.

Behavioural and HR

1. 13

   Tell me about a time you had to learn a new technology quickly for a project or assignment.

   Use STAR format: describe the situation, the technology, how you learned it (docs, tutorials, practice), and the outcome. Show curiosity and self-learning.

2. 14

   Describe a situation where you found a mistake in your own work or someone else's data. How did you handle it?

   Emphasize ownership, transparency, and fixing the root cause. Mention informing stakeholders and adding a check to prevent recurrence.

3. 15

   Why do you want to work as a Data Engineer in the financial technology sector?

   Connect personal interest in data pipelines and large-scale systems with the impact of reliable data in payments or banking. Mention desire to learn cloud and big data tools.

4. 16

   How do you prioritize when you have multiple tasks with tight deadlines?

   Talk about assessing impact and urgency, communicating with the team, and breaking work into smaller chunks. Give an example from college projects or internships.