Question 1

Find Employees with Maximum Salary in Each Department

Accepted Answer

Window: SELECT * FROM (SELECT *, MAX(salary) OVER (PARTITION BY department) AS dept_max FROM employees) t WHERE salary = dept_max. Correlated subquery: WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = e.department). **Why window**: Single table scan; correlated subquery can be O(n²). **Multiple max-per-dept**: Both return all employees tied for max....

Question 2

How do these policies affect query performance?

Accepted Answer

**RLS (Row-Level Security)**: Adds predicate to every query; can use index if predicate column indexed; complex predicates = slower. **Column masking**: Per-row computation; adds overhead. **Result caching**: Speeds repeat queries; invalidated on write. **Trade-off**: Security vs. latency....

Question 3

How do you monitor and debug skewed partitions?

Accepted Answer

**Monitor**: Spark UI—task duration distribution; stragglers indicate skew. **Profile**: df.rdd.mapPartitions(lambda it: [sum(1 for _ in it)]).collect() for partition sizes. **Identify**: Skewed keys (e.g., NULL, default values). **Fix**: (1) Salting: add random suffix to key; join; aggregate. (2) Broadcast small side. (3) Two-phase aggregation. (4) AQE skew join (Spark 3.x)....

Question 4

How does it differ from static partition pruning?

Accepted Answer

**Static**: Partition values known at compile time—WHERE dt='2024-01-01'. Optimizer prunes at plan time. **Dynamic**: Values from subquery or parameter—WHERE dt IN (SELECT max_dt FROM control). May require runtime; optimizer may not prune until execution. **Trade-off**: Static allows full optimization; dynamic adds overhead....

Question 5

Number of Rows in Different Joins

Accepted Answer

Row counts: INNER ≤ MIN(L,R); LEFT = L (or more with duplicates); RIGHT = R; FULL ≥ MAX(L,R). With duplicates from join keys: can exceed. Example: L=100, R=50, INNER might be 0 to 50 depending on matches. LEFT returns at least 100; FULL at least 100 if all left rows have no match. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 6

What factors determine the optimal number of partitions for a large file?

Accepted Answer

Optimal partition count factors: (1) Data size—target 100MB–1GB per partition to avoid small files. (2) Query patterns—partition by filter columns (date, region). (3) Parallelism—partitions = parallelism in Spark; balance with overhead. (4) File count—too many partitions = small files, Namenode pressure (HDFS). (5) Skew—avoid high-cardinality columns that cause imbalance. Rule of thumb: num_partitions ≈ sqrt(data_size_GB) or match core count for single job....

Question 7

What is dynamic partition pruning, and how does it optimize query execution?

Accepted Answer

Dynamic partition pruning: Optimizer uses runtime filter values to skip partitions. E.g., JOIN fact table with dimension filtered by date; the date filter is pushed to the fact scan, so only matching partitions are read. In Spark: Enabled by default with AQE; broadcast-small-dimension pattern. Effect: Fewer I/O, faster scans. Example: fact partitioned by date; query filters dim by region and joins—Spark can prune fact partitions if correlation allows....

TCS SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format

TCS SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format