Five9 India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines that move and transform data from source systems into warehouses and analytics platforms. At global tech companies like Five9 India, they work with cloud services, SQL, and distributed processing tools to ensure reliable, timely data for reporting, machine learning, and customer-facing products. Freshers typically start by writing ETL jobs, fixing data quality issues, and optimising queries under senior guidance.

Role and technical knowledge

1. 1

   Explain the difference between ETL and ELT. When would you choose one over the other in a cloud environment?

   Cover extraction, transformation, and loading order, plus how cloud warehouses like Snowflake or BigQuery make ELT common due to cheap storage and scalable compute. Mention data volume, latency needs, and transformation complexity as decision factors.

2. 2

   What is a slowly changing dimension (SCD)? Describe Type 1 and Type 2 with an example.

   Define SCD as tracking changes in dimension attributes over time. Type 1 overwrites old values, Type 2 adds new rows with effective dates and current flags. Use a customer address change as a clear example.

3. 3

   How does Apache Spark differ from traditional MapReduce for data processing?

   Highlight in-memory computation, lazy evaluation, DAG optimisation, and richer APIs in Spark versus disk-based MapReduce. Mention speed gains and suitability for iterative machine learning workloads.

4. 4

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

   Use a subquery with MAX or a window function like DENSE_RANK. Explain the logic step by step and mention handling ties or nulls. Show awareness of performance on large tables.

Practical scenarios

1. 5

   A daily ETL job that loads customer call records into the warehouse failed at 2 AM. How would you debug and recover it?

   Start by checking logs and error messages, verify source availability and schema changes, then rerun the job after fixing the root cause. Mention alerting, idempotency, and backfilling missed data to avoid duplicates.

2. 6

   Your pipeline processes 10 million rows but now takes 6 hours instead of 1 hour. How would you investigate and optimise it?

   Look at data skew, partitioning, shuffle operations, and inefficient joins. Suggest techniques like broadcast joins, repartitioning, caching, and pushing filters early. Mention monitoring tools and testing changes on a sample.

3. 7

   How would you design a pipeline to ingest real-time clickstream data and make it available for dashboards with low latency?

   Propose a streaming source like Kafka, a processing layer like Spark Structured Streaming or Flink, and a sink like a cloud warehouse or NoSQL store. Discuss trade-offs between latency, cost, and exactly-once processing.

4. 8

   You notice duplicate records in a reporting table. How do you find the cause and prevent it from happening again?

   Trace back through the pipeline to find where duplicates are introduced, such as missing deduplication keys or repeated runs. Suggest using primary keys, merge statements, and idempotent writes. Add data quality tests to catch issues early.

Coding and data structures

1. 9

   Given a list of integers, write a function to return the top K frequent elements. Explain your approach and complexity.

   Use a hash map for counts and a heap or bucket sort for top K. Discuss time and space complexity. Show clean code with edge cases like empty input or K larger than unique elements.

2. 10

   How would you remove duplicates from a large file that does not fit into memory?

   Mention external sorting, chunking, or using a hash-based partition to process subsets. Explain how to merge results and maintain order if needed. Show awareness of disk I/O and memory constraints.

3. 11

   Write a Python function to flatten a nested JSON object into a single-level dictionary with dot-separated keys.

   Use recursion to traverse nested dictionaries and lists. Handle lists by indexing or joining keys. Test with mixed nesting and mention how this helps in ETL transformations.

4. 12

   Explain how you would find the longest common prefix among an array of strings.

   Compare characters column by column or sort the array and compare first and last strings. Discuss time complexity and edge cases like empty array or single string. Write clear, readable code.

Behavioural and HR questions

1. 13

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

   Use the STAR method: describe the situation, the technology, your learning steps, and the outcome. Highlight self-study, documentation, and asking for help. Show enthusiasm for continuous learning.

2. 14

   Describe a situation where you found a mistake in your work or a teammate's work. What did you do?

   Focus on ownership, transparency, and fixing the issue without blaming others. Explain how you communicated, corrected it, and added a check to prevent recurrence. Show integrity and teamwork.

3. 15

   Why do you want to work as a Data Engineer in a global tech company, and how do you handle working across time zones or shifts?

   Connect your interest to building scalable data systems and the company's products. Mention flexibility, clear communication, and planning around overlapping hours. Avoid sounding rigid about shift preferences.

4. 16

   Where do you see yourself in three years, and how does this role fit your goals?

   Show a realistic growth path, such as deepening expertise in cloud data platforms or leading small pipeline projects. Align your goals with the company's needs and express commitment to learning. Avoid vague answers like 'in a good position'.