Tenable 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. In global tech product companies, they work on batch and streaming ingestion, data quality, and performance tuning for petabyte-scale datasets. Freshers typically start by writing SQL and Python jobs, fixing pipeline failures, and learning cloud data services.

Role and technical knowledge

1. 1

   What is the difference between ETL and ELT, and when would you choose one over the other?

   Explain that ETL transforms before loading while ELT loads raw data first. Mention that ELT suits cloud warehouses with strong compute, while ETL is useful for strict schema or on-prem systems.

2. 2

   Explain star schema vs snowflake schema with a simple example.

   Describe a fact table with dimension tables. Star keeps dimensions denormalized; snowflake normalizes them into multiple tables. Give an example like sales fact with product and date dimensions.

3. 3

   How does Apache Spark handle data processing differently from a single-machine Python script?

   Cover distributed in-memory processing, lazy evaluation, and partitioning. Mention that Spark splits work across executors and handles failures via lineage.

4. 4

   What are common file formats in data engineering and when do you use Parquet over CSV?

   Parquet is columnar, compressed, and schema-aware, good for analytics. CSV is row-based, human-readable, but slow for large scans. Mention Avro or ORC as alternatives.

Practical scenarios

1. 5

   A daily batch job that loads data into your warehouse is running 3 hours late. How do you debug it?

   Check logs for failures, source data volume changes, resource contention, and slow queries. Suggest isolating the step, checking upstream dependencies, and adding monitoring.

2. 6

   You need to deduplicate records from a source system that sometimes sends the same event twice. How would you design the pipeline?

   Use a unique key or event ID, apply window functions like ROW_NUMBER to pick the latest, and write idempotent loads. Mention partitioning and merge/upsert patterns.

3. 7

   How would you handle a schema change in a source table without breaking downstream reports?

   Discuss schema evolution, adding nullable columns, versioning, and using a staging layer. Mention alerting and backward-compatible changes.

4. 8

   You have a CSV file of 50 GB on a single machine and need to load it into a cloud warehouse. What is your approach?

   Split the file, upload to cloud storage, use a bulk load command or a distributed job. Mention compression, parallel loading, and error handling for bad rows.

SQL and coding

1. 9

   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. Explain handling ties and nulls.

2. 10

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

   Use LAG or LEAD window functions to compare dates, then filter where the difference is one day. Mention date functions and partitioning by user.

3. 11

   How would you find duplicate rows in a table using SQL?

   Group by all columns and use HAVING COUNT(*) > 1. Alternatively use ROW_NUMBER partitioned by key columns.

4. 12

   In Python, how would you read a large JSON file line by line and compute the average of a numeric field?

   Use a generator or iterate over lines, parse each JSON object, accumulate sum and count. Avoid loading the whole file into memory.

Behavioural and HR

1. 13

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

   Use STAR format. Focus on the steps you took, resources used, 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. How did you handle it?

   Show ownership, transparency, and a fix. Emphasize communication and preventing recurrence.

3. 15

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

   Talk about building scalable systems, working with large datasets, and long-term product impact. Avoid criticizing services companies.

4. 16

   Where do you see yourself in three years as a Data Engineer?

   Show a growth path from individual contributor to owning pipelines or mentoring. Align with learning cloud, big data, and data reliability.