CockroachDB India Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain systems that collect, store, and transform data into usable formats for analytics and machine learning. In global tech companies, they work on distributed pipelines, data warehouses, and real-time streaming systems, ensuring data reliability and scalability. Day to day involves writing code, debugging data issues, and optimising queries across large datasets.

Role and Technical Knowledge

1. 1

   Explain the difference between ETL and ELT. When would you choose one over the other?

   Strong answers define both, mention transformation order, and discuss use cases like cloud data warehouses where ELT is preferred for scalability.

2. 2

   What is a distributed database, and how does it ensure consistency and availability?

   Cover concepts like CAP theorem, replication, consensus algorithms (e.g., Raft), and trade-offs between consistency and latency.

3. 3

   How would you optimise a slow SQL query on a large table?

   Mention indexing, query plan analysis, partitioning, avoiding SELECT *, and using appropriate joins and filters.

4. 4

   Describe how you would design a data pipeline for real-time analytics.

   Include ingestion (e.g., Kafka), processing (e.g., Spark Streaming), storage (e.g., a columnar store), and serving layer, while addressing fault tolerance and latency.

Practical Scenarios

1. 5

   You notice that a daily ETL job is taking longer each day. How would you diagnose and fix the issue?

   Discuss monitoring logs, checking data volume growth, identifying bottlenecks (e.g., skewed joins), and optimising code or infrastructure.

2. 6

   A data source schema changes unexpectedly, breaking your pipeline. What steps do you take?

   Strong answers cover alerting, schema validation, versioning, and implementing backward-compatible changes or schema evolution.

3. 7

   How would you handle duplicate records in a streaming pipeline?

   Mention deduplication techniques like unique keys, windowing, idempotent writes, and exactly-once processing semantics.

4. 8

   You need to migrate a large on-premise data warehouse to the cloud. Outline your approach.

   Cover assessment, choosing a cloud service, data transfer methods, schema conversion, testing, and cutover strategy with minimal downtime.

Behavioural and HR

1. 9

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

   Use STAR method; highlight self-learning, resources used, and how you applied it to deliver results.

2. 10

   Describe a situation where you had to work with a team to solve a complex data problem.

   Emphasise collaboration, communication, and your specific contribution to the solution.

3. 11

   Why do you want to work as a Data Engineer at a company like ours?

   Show passion for data infrastructure, mention the company's scale or tech stack, and align with your career goals.

4. 12

   How do you handle tight deadlines or pressure when data issues arise?

   Demonstrate prioritisation, calm problem-solving, and communication with stakeholders.

Coding and SQL

1. 13

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

   Use subquery with LIMIT/OFFSET or window function DENSE_RANK; explain edge cases like ties.

2. 14

   Given a list of numbers, write a Python function to return the cumulative sum.

   Show iterative or using itertools.accumulate; discuss time and space complexity.

3. 15

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

   Use GROUP BY with HAVING COUNT(*) > 1; mention using window functions for more detail.

4. 16

   Implement a function to reverse a string in Python without using built-in reverse.

   Use slicing or two-pointer approach; explain choice and efficiency.