Question 1

Explain the differences between Repartition and Coalesce. When would you use each?

Accepted Answer

**Repartition(n)**: Full shuffle; creates exactly n partitions. Can increase or decrease. **Coalesce(n)**: Merges partitions without full shuffle; only decreases. **Why it matters**: Shuffle is expensive—network and disk I/O. Coalesce avoids shuffle when reducing partitions by merging within existing partitions. **When Repartition**: Increasing partitions, fixing skew (repartition by key), or before a join to align partition counts....

Question 2

How do you optimize Spark jobs for better performance? Mention at least 5 techniques.

Accepted Answer

1) Broadcast joins for small tables—avoid shuffle. 2) Predicate pushdown—filter at source (Parquet/ORC) to reduce scan. 3) Partition tuning—spark.sql.shuffle.partitions ~2–4× cores; match partition columns to filter/join keys. 4) Cache only when reused; unpersist when done to free memory. 5) Prefer Spark SQL over UDFs—Catalyst optimization. 6) Skew handling—salted keys, AQE skew join. 7) Kryo serialization for RDD; avoid Java default. 8) Coalesce before write to avoid small files....

Question 3

Retrieve the most recent sale_timestamp for each product (Latest Transaction).

Accepted Answer

**SQL (full row)**: `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_timestamp DESC) AS rn FROM sales) t WHERE rn = 1` — Returns the full record. **SQL (timestamp only)**: `SELECT product_id, MAX(sale_timestamp) FROM sales GROUP BY product_id` — Use when you only need the timestamp. **PySpark**: `df.withColumn('rn', F.row_number().over(Window.partitionBy('product_id').orderBy(F.desc('sale_timestamp')))).filter(F.col('rn') == 1).drop('rn')` — Full row....

Question 4

Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.

Accepted Answer

**Architectural Logic**: All three assign ordinals over partitions but differ in tie-handling and downstream semantics. ROW_NUMBER(): Deterministic unique ordering—required for deduplication (pick one row per key). RANK(): Ties get same rank, next rank skips—classic leaderboard (e.g., Olympic medals). DENSE_RANK(): Ties get same rank, no skip—"top N per category" when N must be exact count. **Why**: ROW_NUMBER needs ORDER BY with a tie-breaker for determinism. RANK reflects competitive gaps....

Question 5

Difference between where and having clause with examples.

Accepted Answer

**Architectural Logic**: WHERE filters rows before aggregation; HAVING filters groups after aggregation. Execution order: FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT. **Why**: WHERE enables predicate pushdown—filtering early reduces rows before shuffle and aggregation, saving memory and I/O. HAVING operates on aggregates; pushing filter logic into WHERE when possible is critical for performance....

Question 6

Explain the difference between UNION and UNION ALL.

Accepted Answer

**Architectural Logic**: UNION concatenates result sets and deduplicates (implicit DISTINCT); UNION ALL concatenates and keeps all rows. **Why**: UNION requires sort/hash to detect duplicates—O(n log n) or O(n) with hash. UNION ALL is a simple concatenation—O(n). **Scalability**: UNION doubles I/O (read both, write deduped). At scale, UNION on large inputs can spill and bottleneck. **Cost**: UNION can 2–5x bytes processed vs UNION ALL when inputs are large....

Question 7

What are primary keys and foreign keys? Why are they important?

Accepted Answer

**Architectural Logic**: PK: Unique, NOT NULL identifier for entity integrity; often clustered index. FK: Reference to PK in another table; enforces referential integrity (no orphans). **Why**: PKs enable uniqueness checks, join performance, replication. FKs prevent orphaned child rows and encode relationships. In warehouses, PKs/FKs are often logical (not enforced) for load performance....

Question 8

What is a self-join, and when would you use it?

Accepted Answer

**Architectural Logic**: Self-join = table joined to itself with aliases. Use cases: hierarchies (emp→manager), same-table comparisons (find pairs with same attribute, different on another), sequential comparison (before LAG/LEAD existed). **Why**: Hierarchy: e JOIN emp m ON e.mgr_id = m.id. Comparison: find users who share signup_date but different region. **Scalability**: Self-join doubles table scan; for hierarchies, recursive CTEs or closure tables scale better....

Question 9

What is normalization and denormalization? When would you use each?

Accepted Answer

**Architectural Logic**: Normalization (1NF→3NF): Remove redundancy, enforce integrity. OLTP optimized for writes, consistency. Denormalization: Add redundancy for read performance. OLAP/data marts optimized for analytics. **Why**: Normalized = single source of truth, easier updates. Denormalized = fewer joins, faster queries, larger storage. **Scalability**: Normalized scales for writes; denormalized scales for reads but requires ETL to maintain....

Question 10

What is the difference between a clustered and non-clustered index?

Accepted Answer

**Architectural Logic**: Clustered: Data stored in index order; one per table (usually PK). Table = clustered index. Non-clustered: Separate structure with pointers to data; multiple per table. **Why**: Clustered optimizes range scans (e.g., ORDER BY PK); inserts may cause page splits. Non-clustered optimizes lookups and covering queries. **Scalability**: Clustered on wrong key can hurt insert performance. Non-clustered adds write amplification....

Presidio Data Engineer Interview Questions

Companies asking these questions

All 52 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Presidio Data Engineer Interview Questions

Companies asking these questions

All 52 Questions

More Interview Prep Guides

Get All Answers in PDF Format