Supabase Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines, storage, and processing systems that move and transform data for analytics, machine learning, and product features. In global tech companies, they work with cloud data warehouses, streaming systems, and orchestration tools to ensure data is reliable, timely, and queryable. Day to day, they write SQL and Python, debug pipeline failures, and collaborate with analysts 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 is common with cloud warehouses because storage is cheap and compute is scalable.

2. 2

   How would you model a many-to-many relationship in a data warehouse?

   Describe using a bridge table with foreign keys to both entities, and possibly adding a weight or count column. Mention that star schemas often denormalize for query performance.

3. 3

   Explain how you would handle slowly changing dimensions (SCD) Type 2.

   Cover adding new rows with effective dates and a current flag, preserving history. Mention that this is useful for tracking changes in customer or product attributes over time.

4. 4

   What are the trade-offs between batch and streaming processing?

   Batch is simpler, cheaper, and good for periodic reporting; streaming gives low latency but adds complexity in exactly-once semantics and state management. Give an example like real-time fraud detection for streaming.

Practical scenarios and problem solving

1. 5

   A daily pipeline that loads sales data has been failing for three days. How do you debug and recover?

   Start by checking logs and error messages, verify upstream data availability, and test the query on a small sample. Then backfill missed partitions after fixing the root cause, and add monitoring alerts.

2. 6

   You need to join a 1 TB fact table with a 10 GB dimension table. How would you optimize the join?

   Suggest broadcasting the smaller dimension table if using Spark, or filtering and partitioning the fact table. Mention that in a warehouse, clustering or partitioning on join keys helps.

3. 7

   How would you design a pipeline to ingest clickstream events from a web app into a data warehouse with near real-time latency?

   Propose using a message queue like Kafka or Pub/Sub, a stream processor like Spark Streaming or Flink, and writing to a warehouse with micro-batches. Include schema validation and dead-letter queues for bad events.

4. 8

   A stakeholder reports that a dashboard shows incorrect totals. How do you investigate?

   Trace the data lineage from dashboard to source, check for duplicate rows, filter mismatches, or time zone issues. Reproduce the number with a direct SQL query and compare with the dashboard's logic.

SQL and coding deep dive

1. 9

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

   Use a subquery with MAX and a WHERE clause less than the max, or use DENSE_RANK() window function. Mention handling ties and nulls.

2. 10

   Given a table of user events with timestamps, how would you calculate the number of daily active users for each day?

   Group by date and count distinct user IDs. Mention that you may need to truncate timestamps to day and handle time zones consistently.

3. 11

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

   Use GROUP BY on the key columns with HAVING COUNT(*) > 1, or use ROW_NUMBER() over a partition. Explain that duplicates can come from bad joins or missing primary keys.

4. 12

   In Python, how would you read a large CSV file that does not fit in memory and compute the average of a column?

   Use chunked reading with pandas read_csv in chunks, or use Dask or PySpark. Accumulate sum and count per chunk, then compute the final average.

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: describe the situation, the technology, how you learned it (docs, tutorials, small prototypes), and the outcome. Emphasize curiosity and self-learning.

2. 14

   Describe a situation where you disagreed with a teammate on a technical approach. How did you handle it?

   Focus on listening, data-driven discussion, and compromise. Show that you can disagree respectfully and align on the best solution for the project.

3. 15

   Why do you want to work as a data engineer at a global tech company?

   Connect your interest in building reliable data infrastructure to the company's scale and data-driven products. Mention specific aspects like remote collaboration or open-source culture if relevant.

4. 16

   How do you prioritize tasks when you have multiple deadlines?

   Explain using impact and urgency, communicating with stakeholders, and breaking work into smaller milestones. Give an example from a college project or internship.