Zuora 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. At product companies like Zuora India, they work on subscription and billing data, ensuring reliable ingestion, modelling, and delivery for reporting and machine learning. Day to day involves writing SQL and Python, orchestrating ETL jobs, and debugging data quality issues 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 in the warehouse. Mention that ELT suits cloud warehouses with cheap storage and scalable compute.

2. 2

   How would you model a subscription billing dataset for analytics?

   Talk about fact tables for invoices and payments, dimension tables for customers, plans, and dates, and how to handle recurring charges. Mention slowly changing dimensions for plan changes.

3. 3

   Explain the difference between a star schema and a snowflake schema with an example.

   Star schema has denormalized dimensions directly linked to a fact table; snowflake normalizes dimensions into multiple tables. Give a sales or subscription example and note query simplicity vs storage trade-offs.

4. 4

   What are window functions in SQL and give a use case in a data pipeline?

   Describe functions like ROW_NUMBER, RANK, and SUM OVER. Use case: deduplicating records or calculating running totals per customer without collapsing rows.

Practical scenarios

1. 5

   A daily pipeline that loads billing data failed overnight. How do you debug and recover?

   Check logs for the failing task, verify source availability and schema changes, and confirm if the job is idempotent. Then rerun only the failed partition and add a data quality check to catch it earlier.

2. 6

   You need to process a 100 GB CSV file with Python. What approach do you take?

   Avoid loading it all into memory. Mention chunked reading with pandas, using a distributed tool like Spark, or converting to Parquet and processing in partitions.

3. 7

   How do you ensure data quality in a pipeline that feeds a revenue dashboard?

   Add validation checks for nulls, duplicates, row counts, and business rules like total invoice amount matching source. Mention alerting, quarantine tables, and documenting expectations.

4. 8

   How would you design a pipeline to ingest data from an API that has rate limits and pagination?

   Cover handling pagination tokens, respecting rate limits with backoff, storing raw responses, and scheduling incremental loads. Mention idempotency so reruns do not duplicate data.

SQL and coding problems

1. 9

   Write a SQL query to find the second highest invoice amount per customer.

   Use a window function like DENSE_RANK or ROW_NUMBER partitioned by customer and ordered by amount descending, then filter for rank 2. Explain handling ties.

2. 10

   Given a table of subscription events, write a query to find customers who upgraded their plan in the last 30 days.

   Filter events by type and date, join to customer and plan tables, and compare old and new plan values. Mention using LAG to detect changes if events are snapshots.

3. 11

   In Python, how would you remove duplicate records from a list of dictionaries based on a key?

   Show a solution using a seen set while iterating, or using pandas drop_duplicates. Discuss preserving order and handling missing keys.

4. 12

   Write a SQL query to calculate monthly recurring revenue (MRR) from a subscriptions table.

   Group by month, sum active subscription amounts, and handle proration or plan changes. Mention filtering out cancelled subscriptions and using date ranges correctly.

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 to learn, how you applied it, and the outcome. Show curiosity and self-learning.

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 caught it, and what process you added to prevent recurrence. Emphasize ownership and attention to detail.

3. 15

   Why do you want to work as a Data Engineer at a product company like Zuora India?

   Connect your interest in data pipelines to subscription business models and large-scale data. Mention wanting to build reliable systems that power analytics and customer insights.

4. 16

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

   Talk about understanding business impact, deadlines, and dependencies. Mention communicating with stakeholders and using a simple priority matrix.