Temenos India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain pipelines that move data from source systems into warehouses and lakes, ensuring it is clean, reliable, and query-ready for analytics and machine learning. In global tech companies, they work with cloud platforms, big data tools, and SQL to support real-time and batch processing. Day to day includes writing ETL jobs, optimising queries, and collaborating with analysts and data scientists.

Role and Technical Knowledge

1. 1

   Explain the difference between ETL and ELT, and when you would choose one over the other.

   Strong answer defines extract-transform-load vs extract-load-transform, and mentions that ELT is preferred with cloud warehouses like Snowflake or BigQuery due to scalable compute.

2. 2

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

   Cover that SCD tracks changes in dimension attributes. Type 1 overwrites old data, Type 2 adds new rows with effective dates and current flags.

3. 3

   Write a SQL query to find the second highest salary from an Employee table.

   Use a subquery with MAX or a window function like DENSE_RANK. Mention handling ties and nulls.

4. 4

   What are the key differences between a data lake and a data warehouse?

   Data lake stores raw, schema-on-read data cheaply; warehouse stores structured, schema-on-write data optimised for queries. Mention use cases like ML vs BI.

Practical Scenarios

1. 5

   You need to build a pipeline that ingests daily sales data from multiple CSV files into a cloud warehouse. Outline your approach.

   Mention steps: ingest to cloud storage, validate schema and data quality, transform using Spark or SQL, load into warehouse, and schedule with Airflow. Include error handling and logging.

2. 6

   A pipeline that usually runs in 30 minutes suddenly takes 3 hours. How do you troubleshoot?

   Check for data volume spikes, skewed joins, missing indexes, resource contention, or upstream delays. Suggest monitoring logs and adding alerts.

3. 7

   How would you handle duplicate records in a streaming data source?

   Use deduplication logic based on unique keys, watermarks, or windowing. Mention idempotent writes and exactly-once semantics if using Kafka or Spark Streaming.

4. 8

   You need to join a large fact table with a small dimension table in Spark. What optimisation would you use?

   Use broadcast join to avoid shuffling the large table. Explain that it works when one side fits in memory.

Behavioural and HR

1. 9

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

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

2. 10

   Describe a situation where you found a mistake in your work or a teammate's work. How did you handle it?

   Emphasise ownership, transparency, and fixing the issue without blaming. Mention preventive measures like code reviews or tests.

3. 11

   Why do you want to work as a Data Engineer in a global tech company?

   Connect your interest in data pipelines and cloud technologies to the scale and impact of global products. Mention eagerness to work with diverse teams and modern tools.

4. 12

   How do you prioritise tasks when you have multiple deadlines?

   Talk about assessing urgency and impact, communicating with stakeholders, and using tools like Jira or Trello. Give a brief example from college projects.

Coding and Problem Solving

1. 13

   Given a list of integers, write a Python function to return the top k frequent elements.

   Use a dictionary to count frequencies, then sort or use heapq.nlargest. Mention time complexity O(n log k) and edge cases like k > unique elements.

2. 14

   Write a SQL query to calculate the running total of sales per day for each product.

   Use SUM() OVER (PARTITION BY product ORDER BY date). Explain window frame and ordering.

3. 15

   How would you find and remove duplicate rows from a table using SQL?

   Use ROW_NUMBER() OVER (PARTITION BY all columns ORDER BY id) and delete where row number > 1. Or use DISTINCT into a temp table.

4. 16

   Implement a function to check if a string is a palindrome, ignoring case and non-alphanumeric characters.

   Use two pointers or filter and reverse. Discuss time O(n) and space O(n) or O(1) with two pointers.