Question 1

Tell me about yourself and your experience.

Accepted Answer

**Situation**: I joined the data org when our pipelines were monolithic, causing 4+ hour delays and frequent outages affecting downstream dashboards and ML models.

**Task**: I was tasked with redesigning the data platform to support real-time decisioning while improving reliability and cost efficiency.

**Action**: I led a cross-functional team of 5 engineers to architect a medallion (Bronze/Silver/Gold) architecture on Delta Lake....

Question 2

Implement a Spark job to find the top 10 most frequent words in a large text file.

Accepted Answer

Core logic: read text → split → explode → filter empty → groupBy → count → orderBy desc → limit 10. Code: from pyspark.sql import functions as F; df = spark.read.text("path/to/file.txt"); words = df.select(F.explode(F.split(F.col("value"), "\s+")).alias("word")); top10 = words.filter(F.length(F.col("word")) > 0).groupBy("word").count().orderBy(F.desc("count")).limit(10). **Why \s+**: Handles multiple spaces/tabs; more robust than single space....

Question 3

Combine records by name with concatenated course values

Accepted Answer

**SQL (PostgreSQL)**: SELECT name, STRING_AGG(course, ', ' ORDER BY course) AS courses FROM student_courses GROUP BY name. **MySQL**: GROUP_CONCAT. **Spark**: concat_ws(', ', collect_list('course')) with groupBy. Use collect_set for dedupe....

Question 4

Reverse operation for splitting values back to original format

Accepted Answer

Join back: SQL STRING_AGG(value, ',') or array_to_string(array_agg(col), ','). Python ','.join(list). Spark: groupBy.agg(collect_list).alias('vals').withColumn('restored', concat_ws(',','vals')). Preserve order: array_agg with ORDER BY. Handle nulls....

Question 5

Sort and merge arrays

Accepted Answer

**Why Merge Sorted Arrays:** Mergesort's merge step, merge K sorted streams (logs from multiple sources), and combining partitioned data.

**Two Arrays:** Two pointers, O(m+n). K arrays: (1) Merge pairwise—O(N log K). (2) Heap of (value, stream_id)—O(N log K). Python: heapq.merge(a, b, c)—lazy, memory-efficient for large streams.

**Scalability:** heapq.merge doesn't load all into memory—streams. For Spark: merge sorted part files via sortMerge join....

Question 6

Count records for INNER JOIN and LEFT JOIN

Accepted Answer

**Architectural Logic**: Join type determines output cardinality and null semantics. **INNER JOIN**: Only matching rows from both tables; count ≤ min(left, right) when 1:1 or N:1. **LEFT JOIN**: All left rows; right columns NULL where no match; count = left count (if left is 'one' side). **Cardinality Rules**: INNER can reduce rows (excludes orphans); LEFT preserves left. Use COUNT(*) for total rows; COUNT(right.key) for non-null matches (LEFT JOIN gives matched count)....

Question 7

Create partitioned table

Accepted Answer

**Architectural Logic**: Partitioning enables partition pruning—critical for cost and performance at scale. **BigQuery**: `CREATE TABLE dataset.sales (...) PARTITION BY sale_date` or `PARTITION BY DATE(created_at)`. **Snowflake**: `PARTITION BY (sale_date)` or expression. **Hive/Spark**: `PARTITIONED BY (sale_date DATE)`. **Why Partition**: Predicate on partition column skips non-matching partitions; 90% cost reduction on date-filtered queries....

Question 8

Find average salary for each manager – Assume a table with manager_id and employee_salary

Accepted Answer

SELECT manager_id, AVG(employee_salary) AS avg_salary FROM employees GROUP BY manager_id. **Why simple**: Direct aggregation. **Consider**: HAVING AVG(employee_salary) > X to filter; exclude NULL manager_id if not applicable....

Pubmatic Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Pubmatic Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies