ManageEngine 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 lakes, so analysts and ML models can use it reliably. In global tech product companies, you will write ETL/ELT jobs, model data in SQL, and keep batch and streaming pipelines healthy on cloud infrastructure. Expect close work with backend, analytics, and DevOps teams, plus on-call ownership of data freshness and quality.

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 cloud data platform?

   Explain that ETL transforms before loading while ELT loads raw data first and transforms inside the warehouse. Mention cloud elasticity, cost, and schema flexibility as deciding factors.

2. 2

   How would you design a star schema for a product usage analytics system at a software company?

   Identify fact tables like usage events and dimension tables like user, product, and date. Discuss grain, surrogate keys, and why denormalized dimensions help query performance.

3. 3

   Explain how you would handle slowly changing dimensions (SCD) Type 1 and Type 2 with an example.

   Type 1 overwrites history, Type 2 adds new rows with effective dates and current flags. Give a concrete example like a customer address change and the impact on historical reporting.

4. 4

   What is the difference between batch and stream processing, and what are common tools for each?

   Batch processes bounded data on a schedule; stream processes unbounded data continuously. Name tools like Spark for batch and Kafka with Flink or Spark Streaming for streams, and mention latency vs throughput trade-offs.

Practical Scenarios and Problem Solving

1. 5

   A daily pipeline that loads sales data is running two hours late. How do you debug and fix it?

   Check source availability, job logs, data volume spikes, and downstream dependencies. Suggest adding monitoring, alerting, retries, and backfill strategy, and communicate impact to stakeholders.

2. 6

   You need to deduplicate millions of records in a table using SQL. How would you approach it?

   Use ROW_NUMBER() with PARTITION BY the business key and ORDER BY a timestamp or primary key, then filter rn = 1. Discuss performance considerations like partitioning and indexing.

3. 7

   How would you optimize a slow Spark job that joins a large fact table with a small dimension table?

   Mention broadcast join for the small table, repartitioning, caching, and avoiding shuffles. Also check for data skew and use salting if needed.

4. 8

   Design a simple data pipeline to ingest logs from multiple application servers into a data warehouse.

   Cover ingestion with a message queue or log shipper, storage in a data lake, transformation with Spark or SQL, and loading into the warehouse. Include scheduling, monitoring, and schema evolution.

Coding and SQL

1. 9

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

   Use a subquery with MAX below the highest, or DENSE_RANK() OVER (ORDER BY salary DESC) and filter rank = 2. Handle ties and nulls.

2. 10

   Given a list of integers in Python, return the top K frequent elements.

   Use a hash map for counts and a heap or sorted buckets for top K. Discuss time and space complexity.

3. 11

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

   Use SUM(sales) OVER (PARTITION BY product_id ORDER BY sale_date). Explain window frame defaults and how to handle missing dates.

4. 12

   How would you read a large CSV file in Python without loading it all into memory?

   Mention chunked reading with pandas or csv module, generators, and processing row by row. Discuss when to use Dask or PySpark for larger-than-memory data.

Behavioural and HR

1. 13

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

   Use STAR format: situation, task, action, result. Highlight self-learning resources, time management, and a concrete outcome like a working prototype.

2. 14

   Describe a situation where you found a data quality issue in a project. What did you do?

   Show ownership by validating data, tracing root cause, fixing the pipeline, and adding checks. Mention communication with the team and preventing recurrence.

3. 15

   Why do you want to work as a Data Engineer in a product company rather than a services company?

   Focus on building long-term scalable systems, ownership of data infrastructure, and learning from large-scale product data. Avoid criticizing services companies.

4. 16

   Are you comfortable with relocation and working in shifts if required for global support?

   Be honest and flexible. If yes, state willingness to relocate and adapt. If no, explain constraints clearly and show openness to discussion.