Adaptive Insights India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers at global tech companies like Adaptive Insights India build and maintain data pipelines that move and transform data from source systems into analytics-ready stores. Day to day, they write SQL and Python, work with cloud services, and ensure data quality for reporting and machine learning. Freshers typically start by fixing pipeline issues, writing transformations, and learning the company's data stack.

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 warehouse?

   Explain that ETL transforms before loading while ELT loads raw then transforms using warehouse compute. Mention cost, data volume, and flexibility as decision factors.

2. 2

   How would you design a star schema for a sales reporting system? Name the fact and dimension tables.

   Describe a fact table with sales amount and quantity, and dimension tables for date, product, customer, and store. Highlight primary and foreign keys.

3. 3

   Explain how you would handle slowly changing dimensions (SCD) Type 2 in a data pipeline.

   Cover adding new rows with effective dates and current flag, and using surrogate keys. Mention merge or upsert logic in SQL.

4. 4

   What are the key differences between a data lake and a data warehouse, and where would you store raw logs versus cleaned reports?

   State that data lakes store raw, schema-on-read files while warehouses store structured, schema-on-write tables. Place raw logs in the lake and cleaned reports in the warehouse.

SQL and Python practical questions

1. 5

   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 how NULLs and ties are handled.

2. 6

   Given a table of daily user logins, write a query to find users who logged in on at least three consecutive days.

   Use window functions like LAG or ROW_NUMBER to group consecutive dates. Show the logic step by step.

3. 7

   How would you read a large CSV file in Python and compute the average of a numeric column without loading everything into memory?

   Mention using pandas with chunksize or Python's csv module with a loop. Track sum and count incrementally.

4. 8

   Write a Python function to remove duplicates from a list while preserving order, and explain its time complexity.

   Use a set to track seen items and a new list for output. State O(n) time and O(n) space.

Scenario based and troubleshooting

1. 9

   Your daily data pipeline failed at 2 AM and the dashboard shows stale data. How do you debug and recover?

   Check logs for the failing task, verify source availability, and rerun from the last successful checkpoint. Communicate impact and add monitoring.

2. 10

   A new data source has inconsistent date formats and missing values. How would you clean and load it into your warehouse?

   Profile the data, standardize dates, apply default or null handling rules, and validate before loading. Document assumptions.

3. 11

   You need to process 1 TB of JSON files daily. What tools and steps would you use to make this scalable and cost effective?

   Suggest storing in cloud object storage, using Spark or a distributed engine, partitioning by date, and compressing files. Mention incremental processing.

4. 12

   How would you ensure data quality in a pipeline that feeds a financial report used by executives?

   Add checks for row counts, nulls, duplicates, and range validations. Set up alerts and a quarantine table for bad records.

Behavioural and HR questions

1. 13

   Tell me about a time you learned a new technology quickly for a project or assignment.

   Use the STAR method. Focus on the steps you took, the resources used, and the outcome. Show curiosity and self learning.

2. 14

   Describe a situation where you had to work with a teammate who had a different opinion on a technical approach.

   Highlight listening, data driven discussion, and compromise. End with a positive result and what you learned about teamwork.

3. 15

   Why do you want to work as a Data Engineer at a fast growing global tech company, and how do you keep your skills current?

   Connect personal interest in data pipelines to the company's data driven products. Mention blogs, courses, or side projects you follow.

4. 16

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

   Show realistic growth from fresher to owning pipelines or mentoring. Align with the company's need for reliable data infrastructure.