Domo 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 a global product company, you will write SQL and Python, work with cloud services, and ensure data is reliable, timely, and query-ready for analysts and data scientists. Expect close collaboration with backend, analytics, and product teams.

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 and transforms inside the warehouse. Mention that ELT suits cloud warehouses with strong compute, while ETL may be needed for strict compliance or limited storage.

2. 2

   Explain star schema and snowflake schema with a simple example.

   Describe a fact table (sales) linked to dimension tables (customer, product, date). Star keeps dimensions denormalized; snowflake normalizes them further. Mention trade-offs in query simplicity and storage.

3. 3

   How would you handle duplicate records in a data pipeline?

   Cover detection using primary keys or row hashes, deduplication with window functions like ROW_NUMBER, and prevention through idempotent writes and unique constraints. Mention monitoring for duplicates.

4. 4

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

   Data lake stores raw, schema-on-read files cheaply; warehouse stores structured, schema-on-write data optimized for SQL. Mention that modern lakehouse architectures combine both.

Practical scenarios and problem solving

1. 5

   A daily pipeline that loads sales data is failing intermittently. How would you debug it?

   Start with logs and error messages, check source availability, data volume changes, and schema drift. Suggest adding retries, alerts, and a staging layer to isolate failures.

2. 6

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

   Use a subquery with MAX and a WHERE clause, or a window function like DENSE_RANK. Explain handling ties and nulls.

3. 7

   You need to join a large fact table with a small dimension table. How would you optimize the join?

   Mention broadcast join if using Spark, filtering early, and partitioning on join keys. For SQL, ensure statistics are up to date and consider indexing.

4. 8

   How would you design a pipeline to process 100 GB of daily log files and make them queryable?

   Propose storing raw files in cloud storage, using a batch tool like Spark to clean and partition by date, then writing to a columnar format like Parquet in a warehouse or lakehouse. Add orchestration with Airflow and data quality checks.

Tools and technologies

1. 9

   What is Apache Airflow and how have you used it in a project?

   Explain DAGs, operators, and scheduling. If you have no work experience, describe a college project where you scheduled a script or used cron and how Airflow would improve it.

2. 10

   How does partitioning improve query performance in a data warehouse?

   Partitioning divides data by a column like date, so queries scan only relevant partitions. Mention that over-partitioning can hurt and that pruning depends on filter usage.

3. 11

   What are the advantages of storing data in Parquet or ORC format?

   Columnar formats compress well and allow reading only needed columns, speeding up analytics. They also support schema evolution and work well with Spark and Hive.

4. 12

   Explain how you would use Python to read a CSV, clean it, and write to a database.

   Mention pandas for reading and cleaning, handling missing values and types, then using SQLAlchemy or a database connector to insert in batches. Add error handling and logging.

Behavioural and HR questions

1. 13

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

   Use the STAR method. Focus on the steps you took, resources used, and the outcome. Show curiosity and ability to self-learn.

2. 14

   Describe a situation where you found a mistake in your own work. How did you handle it?

   Be honest about the mistake, explain how you fixed it, and what process you put in place to avoid recurrence. Emphasize ownership and learning.

3. 15

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

   Talk about building long-term data platforms, working with large scale data, and seeing direct impact on product features. Avoid criticizing services companies.

4. 16

   How do you prioritize when you have multiple data requests from different teams?

   Mention assessing business impact, urgency, and effort. Communicate with stakeholders, and use a simple framework like impact vs effort. Show willingness to ask for guidance.