In Part 1, we covered the fundamentals of Spark, PySpark, Databricks, DAGs, lazy evaluation, partitioning, data skew, salting, AQE, migration validation, and common PySpark coding questions.
But experienced Data Engineer interviews usually go one level deeper.
Interviewers want to know:
Can you actually design, optimize, and troubleshoot a production data platform?
In this article, we’ll cover advanced interview questions around Delta Lake, incremental processing, CDC, Spark performance, joins, partitioning, Databricks architecture, and production troubleshooting.
1. What Is Delta Lake?
Delta Lake is a storage layer that provides reliability and transactional capabilities on top of cloud object storage.
It adds features such as:
- ACID transactions
- Schema enforcement
- Schema evolution
- Time travel
- MERGE operations
- UPDATE and DELETE support
- Version history
A simplified architecture looks like:
Delta Lake
|
+-------------+-------------+
| | |
ACID Time Travel MERGE
| | |
+-------------+-------------+
|
Parquet Files
|
Cloud Storage
Delta tables are commonly used in Databricks-based Lakehouse architectures.
2. What Is the Difference Between Parquet and Delta Lake?
Parquet is primarily a columnar file format.
Delta Lake is a storage layer that uses Parquet files along with a transaction log.
Parquet
Data
↓
Parquet Files
Delta
Data
↓
Parquet Files
+
Transaction Log
The transaction log enables features such as:
- ACID transactions
- Versioning
- Schema enforcement
- Time travel
- Reliable concurrent writes
Therefore, Delta is more than simply another file format.
3. What Is Time Travel in Delta Lake?
Delta Lake maintains table versions.
You can query an earlier version of a table.
For example:
SELECT *
FROM customers VERSION AS OF 10;
Or query data as of a timestamp:
SELECT *
FROM customers
TIMESTAMP AS OF '2026-01-01';
This can be useful for:
- Auditing
- Debugging
- Recovering from accidental changes
- Reproducing historical data
Interview Follow-up
“Does time travel mean Delta keeps unlimited historical data forever?”
No.
Retention policies and cleanup operations affect how long historical files remain available.
4. What Is VACUUM?
VACUUM removes old data files that are no longer referenced by the current Delta table state and are outside the configured retention period.
For example:
VACUUM customers;
A common interview question is:
“What happens if you VACUUM and then try to access an old version?”
If the underlying files required by that version have been deleted, that historical version may no longer be queryable.
Important
Don’t treat VACUUM as a generic performance optimization command.
Its primary purpose is storage cleanup.
5. What Is OPTIMIZE?
OPTIMIZE is used to improve the physical layout of Delta data, particularly by compacting small files.
For example:
OPTIMIZE customers;
Small-file problems can occur when pipelines continuously write many small files.
Instead of:
10,000 small files
compaction can produce:
Fewer larger files
This can improve read performance.
Depending on the workload, additional data-layout techniques may also be used.
6. What Is the Small File Problem?
Suppose a pipeline writes:
1 million records
but creates:
50,000 tiny files
Reading the data may become inefficient because Spark has to manage a very large number of files.
Potential solutions include:
- Compaction
OPTIMIZE- Better write strategies
- Appropriate partitioning
- Avoiding excessive small incremental writes
Interview Question
“Would you partition a table by every column to improve performance?”
No.
Over-partitioning can create huge numbers of small files and hurt performance.
Partitioning should be based on common filtering patterns and data distribution.
7. How Do You Choose a Partition Column?
Suppose you have:
transaction_id
customer_id
country
transaction_date
A common candidate for partitioning might be:
transaction_date
if queries frequently filter by date.
For example:
WHERE transaction_date = '2026-07-01'
Partition pruning can allow Spark to read only relevant partitions.
However, a high-cardinality column such as:
transaction_id
is generally a poor partition choice.
Rule of thumb
Choose columns that:
- Are frequently used for filtering
- Have reasonable cardinality
- Produce appropriately sized partitions
8. What Is Partition Pruning?
Suppose a table is partitioned by:
year
month
and the query is:
SELECT *
FROM sales
WHERE year = 2026
AND month = 7;
Spark can potentially read only the relevant partition instead of scanning the entire table.
This is called partition pruning.
It can significantly reduce:
- Data scanned
- I/O
- Execution time
9. What Is Predicate Pushdown?
Predicate pushdown means applying filters as close to the data source as possible.
Instead of:
Read everything
↓
Filter later
the system tries to do:
Filter at source
↓
Read only relevant data
For example:
df = (
spark.read
.parquet("sales")
.filter("year = 2026")
)
With supported formats and conditions, Spark can push filtering closer to the storage layer.
This reduces unnecessary data scanning.
10. What Is the Difference Between Partition Pruning and Predicate Pushdown?
They are related but different.
Partition Pruning
Skips entire partitions.
Partition 2024 → Skip
Partition 2025 → Skip
Partition 2026 → Read
Predicate Pushdown
Pushes filtering closer to the data files.
Read File
↓
Apply Filter Early
↓
Return Matching Rows
Both techniques help reduce unnecessary data processing.
11. How Do You Implement an Incremental Pipeline?
Imagine a source table:
customer_id
name
updated_at
Instead of processing the entire table every day, we process only changed records.
For example:
incremental_df = source_df.filter(
F.col("updated_at") > last_watermark
)
The watermark can be stored in:
- A control table
- Metadata table
- Pipeline configuration
- External state store
The general architecture becomes:
Source
|
| New / Updated Records
v
Incremental Processing
|
v
Delta Table
This can dramatically reduce processing cost.
12. How Do You Handle Upserts in Delta Lake?
Suppose the source contains:
customer_id = 101
name = "John"
and the target already contains:
customer_id = 101
name = "John Smith"
We need to update the existing record.
For a new customer, we insert.
This is an upsert.
Conceptually:
MERGE INTO target t
USING source s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
UPDATE SET *WHEN NOT MATCHED THEN
INSERT *
This is one of the most important Delta Lake concepts.
13. What Problem Can Occur During MERGE?
Suppose the source contains:
customer_id
101
101
102
If the target expects one record per customer, the MERGE may have problems because the source contains duplicate keys.
Therefore, you may need to deduplicate the source first.
For example:
window_spec = (
Window
.partitionBy("customer_id")
.orderBy(F.col("updated_at").desc())
)
latest_df = (
source_df
.withColumn("rn", F.row_number().over(window_spec))
.filter("rn = 1")
.drop("rn")
)
Then perform the MERGE.
Interview Tip
Always think about idempotency.
Running the same pipeline twice should not create duplicate records or corrupt the target.
14. What Is Idempotency in Data Engineering?
An idempotent pipeline produces the same final result even if the same input is processed multiple times.
For example:
Run 1 → Process Batch A
Run 2 → Accidentally Process Batch A Again
The final output should remain correct.
Techniques include:
- MERGE
- Deduplication
- Unique keys
- Batch IDs
- Watermarks
- Transactional writes
Idempotency is critical in production pipelines.
15. How Would You Handle a Failed Pipeline?
Suppose:
Source
↓
Bronze
↓
Silver
↓
Gold
The pipeline fails during the Silver-to-Gold step.
A robust approach should consider:
- Retry strategy
- Checkpointing
- Idempotent processing
- Logging
- Alerts
- Error handling
- Partial data cleanup
A good pipeline should be restartable without duplicating data.
Interview Question
“If the pipeline fails halfway through, can you safely rerun it?”
A strong answer should explain how the pipeline was designed for recovery.
16. How Do You Handle Slowly Changing Dimensions?
A common Data Warehouse interview topic is Slowly Changing Dimensions (SCD).
SCD Type 1
Overwrite the old value.
Before:
Customer → Delhi
After:
Customer → Gurgaon
Only the latest value remains.
SCD Type 2
Maintain history.
Customer | City | Start Date | End Date | Current
101 | Delhi | 2024-01-01 | 2025-01-01 | No
101 | Gurgaon | 2025-01-01 | NULL | Yes
SCD Type 2 is useful when historical tracking is required.
Delta MERGE is often used to implement such patterns.
17. How Would You Handle a Late-Arriving Record?
Suppose an event from:
2026-07-01
arrives on:
2026-07-10
This is a late-arriving record.
The pipeline needs to determine:
- Should historical data be updated?
- Should the record be inserted?
- Does it affect downstream aggregations?
- Should historical partitions be reprocessed?
Possible strategies include:
- Reprocessing a time window
- MERGE
- Backfill
- Watermark adjustment
The correct solution depends on the business requirements.
18. How Do You Handle Schema Evolution?
Suppose today’s data is:
id
name
Tomorrow the source adds:
The schema has evolved.
A robust pipeline needs to decide whether to:
- Reject the new schema
- Automatically evolve it
- Explicitly alter the target schema
Delta Lake supports schema evolution in appropriate scenarios.
However, blindly allowing schema evolution can also introduce unexpected changes.
Schema governance is important in production environments.
19. How Do You Design a Reliable Databricks Pipeline?
A production-grade architecture could look like:
Source Systems
|
v
ADF / Workflow
|
v
ADLS / Storage
|
v
Bronze / Raw
|
v
Silver / Trusted
|
v
Gold / Unified
|
+----------+----------+
| | |
v v v
Snowflake Denodo BI
A reliable implementation should include:
- Incremental processing
- Data quality checks
- Schema validation
- Error handling
- Retry mechanisms
- Monitoring
- Alerting
- CI/CD
- Access control
- Idempotency
20. How Would You Explain Your Databricks Project in an Interview?
Avoid saying:
“I worked on Databricks and created ETL pipelines.”
Instead, explain the project using this structure:
1. Business Problem
What problem were you solving?
2. Source Systems
Where did the data come from?
SQL Server
Oracle
APIs
SAS
Files
3. Ingestion
How did data enter the platform?
ADF
ADLS
Databricks
4. Processing
What did you do in PySpark?
Filtering
Joins
Deduplication
Aggregations
Business Rules
5. Storage
Where did you store the data?
Delta Lake
Snowflake
6. Orchestration
How were pipelines scheduled?
ADF
Databricks Workflows
7. Monitoring
How did you detect failures?
Azure Monitor
Log Analytics
Alerts
8. Optimization
What performance problems did you solve?
Data Skew
Shuffle
Partitioning
Broadcast Join
AQE
9. Outcome
What business or technical improvement did you deliver?
This structure makes your answer much more credible and easier for the interviewer to evaluate.
The Real Difference Between a Good and Great Candidate
A good candidate knows:
“What is a broadcast join?”
A great candidate can explain:
“We had a 2 TB fact table and a 20 MB dimension table. The join was causing a large shuffle, so we broadcast the dimension table. We validated the execution plan and confirmed that Spark used a broadcast hash join, reducing shuffle and improving runtime.”
The difference is practical experience.
Similarly:
A good candidate knows:
“Salting handles data skew.”
A great candidate can explain:
“One customer represented 30% of the data, causing a single task to run much longer than the others. We confirmed the skew in Spark UI and used salting for the affected keys. We also evaluated AQE skew join optimization before deciding whether manual salting was necessary.”
That’s the level of detail interviewers look for in experienced Data Engineers.
Final Interview Preparation Checklist
Before your next Databricks interview, make sure you can confidently explain:
Spark
- Driver and Executor
- Jobs, Stages, Tasks
- DAG
- Lazy Evaluation
- Narrow vs Wide Transformations
- Shuffle
- Partitioning
Performance
- Data Skew
- Salting
- AQE
- Broadcast Join
- Repartition vs Coalesce
- Spark UI
- Predicate Pushdown
- Partition Pruning
Delta Lake
- ACID Transactions
- Time Travel
- MERGE
- OPTIMIZE
- VACUUM
- Schema Evolution
- Small File Problem
Data Engineering
- Incremental Loads
- CDC
- SCD Type 1 and Type 2
- Idempotency
- Data Quality
- Migration Validation
Production
- Monitoring
- Logging
- Alerting
- Retry
- Failure Recovery
- CI/CD
The most important lesson is simple:
Don’t just memorize the definition. Understand the problem the technology is solving.
If an interviewer asks about salting, think about data skew.
If they ask about AQE, think about runtime optimization.
If they ask about broadcast joins, think about avoiding expensive shuffles.
If they ask about Delta MERGE, think about incremental upserts and idempotency.
And if they ask about Spark UI, think about finding the real bottleneck instead of blindly restarting the cluster.
That’s how you move from answering Data Engineering interview questions to demonstrating real production expertise.