Question 1

How do quarantine tables ensure data quality in downstream pipelines?

Accepted Answer

**Quarantine tables** store rows failing validation (schema, business rules, duplicates). Downstream reads only from validated path—ensuring quality. **Flow**: Validate → route pass to main, fail to quarantine. **Schema**: Mirror main + error_code, error_message, reject_reason. **Governance**: Periodic review; remediate or discard; alert on volume spike. **Why**: Prevents bad data propagation; traceability for debugging....

Question 2

How does AQE optimize join operations dynamically?

Accepted Answer

**Adaptive Query Execution** (Spark 3.x): Optimizes at runtime using actual statistics. **Join optimizations**: (1) Coalesce partitions after shuffle—merge small partitions. (2) Convert sort-merge to broadcast when runtime shows one side small. (3) Skew join—split large partition, replicate small side. **Enable**: spark.sql.adaptive.enabled=true. **Why**: Planner uses estimates; AQE corrects with real data....

Question 3

How does improper partitioning affect Spark job performance?

Accepted Answer

**Skew**: Few partitions have most data—stragglers; one task runs 10× longer. **Too many**: Scheduler overhead; small tasks inefficient; many small files. **Too few**: Underutilized cluster; OOM on large partitions. **Wrong key**: Full shuffle; no pruning. **Symptoms**: Uneven task progress; OOM; long tail....

Question 4

What metrics would you analyze to determine if your partitioning strategy is effective?

Accepted Answer

Partition effectiveness metrics: (1) Partition pruning—% of partitions scanned vs total; aim for high pruning on typical queries. (2) Data skew—partition sizes; avoid huge variance. (3) Small files—count of files < threshold; need compaction. (4) Query latency—p50, p99. (5) Scan size—bytes read per query. (6) Compaction frequency. Tools: Spark UI, query explain, table stats. Best practice: Monitor over time; adjust partition keys based on query patterns....

PWC SQL Interview Questions

Companies asking these questions

All 4 Questions

More Interview Prep Guides

Get All Answers in PDF Format

PWC SQL Interview Questions

Companies asking these questions

All 4 Questions

More Interview Prep Guides

Get All Answers in PDF Format