In the first three parts of this series, we covered SQL, PySpark, Spark architecture, window functions, partitioning, data skew, salting, AQE, and performance optimization.

But real Data Engineering interviews don’t stop at technical definitions.

At the mid-to-senior level, interviewers increasingly ask scenario-based questions:

“How would you design this pipeline?”

“How would you migrate this system?”

“How do you validate that the migrated data is correct?”

“What happens if the pipeline fails halfway through?”

“How do you process only new or changed records?”

These questions test whether you can think like a Data Engineer—not just write code.

Let’s explore some of the most common scenarios.

Follow the complete learning paths:

Apache Spark
Learn Spark Architecture, Driver vs Executor, Lazy Evaluation, DAGs, Shuffles, Partitioning, AQE, Data Skew, and Performance Tuning.

[Explore Apache Spark Tutorials →]

1. How would you design an end-to-end data pipeline?

Suppose a company receives data from multiple sources:

SQL databases
APIs
CSV files
SaaS applications

The business wants the data available for analytics.

A typical architecture could look like:

			
Source Systems
      ↓
Ingestion
      ↓
Raw / Bronze Layer
      ↓
Cleaning & Standardization
      ↓
Silver Layer
      ↓
Business Transformations
      ↓
Gold Layer
      ↓
Data Warehouse / BI
      ↓
Consumers

		

In an Azure Databricks environment, this might involve:

			
Source
  ↓
Azure Data Factory
  ↓
ADLS
  ↓
Databricks
  ↓
Delta Lake
  ↓
Power BI / Snowflake / Applications

		

Azure Data Factory can be used for orchestration and ingestion, while Databricks handles large-scale transformations.

A strong interview answer should explain:

Where data comes from
How it is ingested
Where raw data is stored
How transformations happen
How data quality is checked
How the pipeline is monitored
How failures are handled
How data is exposed to consumers

The interviewer is looking for the complete flow, not just a list of tools.

2. What is the Medallion Architecture?

The Medallion Architecture organizes data into multiple layers.

The commonly used structure is:

			
Bronze
   ↓
Silver
   ↓
Gold

		

Bronze Layer

Contains raw or minimally transformed data.

Typical characteristics:

Preserves source data
Used for traceability
Allows reprocessing
Contains ingestion metadata

Example:

			
source_system
ingestion_timestamp
file_name
record_id
raw_data

		

Silver Layer

Contains cleaned and standardized data.

Typical operations include:

Deduplication
Data type conversion
Null handling
Standardization
Joins
Business rules

Gold Layer

Contains business-ready datasets.

Examples:

			
customer_360
monthly_revenue
product_performance
customer_churn

The Gold layer is designed for consumption by:

BI dashboards
Data analysts
Data scientists
ML systems
Applications

Interview Tip

Don’t simply say:

“Bronze is raw, Silver is cleaned, Gold is aggregated.”

Explain why the layers exist.

The primary benefits are:

Data traceability
Separation of concerns
Reusability
Easier debugging
Controlled transformation stages

3. What is an incremental load?

Suppose a source table contains 1 billion records.

Every day, you need to load new data into your data lake.

A naive approach would be:

			
Day 1 → Load 1 billion rows
Day 2 → Load 1 billion rows
Day 3 → Load 1 billion rows

This is extremely inefficient.

Instead, we use incremental loading.

For example:

			
Day 1 → Load all historical data
Day 2 → Load records created after Day 1
Day 3 → Load records created after Day 2

A common approach is to use:

			
created_at
updated_at
last_modified_timestamp

The pipeline stores a watermark:

Last successful timestamp

Then the next execution queries:

			
SELECT *
FROM source
WHERE updated_at > :last_watermark;

This dramatically reduces the amount of data processed.

4. What is a watermark?

A watermark represents the point up to which data has already been successfully processed.

For example:

			
Last successful processing time:
2026-08-01 10:00:00

The next pipeline may process:

updated_at > '2026-08-01 10:00:00'

After successful processing, the watermark is updated.

A robust design should update the watermark only after successful processing.

Otherwise, if the pipeline fails halfway through and the watermark is updated prematurely, some records may never be processed.

5. How do you handle late-arriving data?

Suppose an event occurred on:

August 1

But due to network problems, it arrived on:

August 3

A simple watermark based on arrival time may miss it.

Possible solutions include:

Lookback Window

Instead of processing only new records:

updated_at > August 2

process:

updated_at > August 1

This reprocesses a small overlap.

Duplicates can then be handled using:

Deduplication
MERGE
Primary keys

Event-Time Processing

Use the actual event timestamp rather than ingestion timestamp.

This is especially important for streaming systems.

CDC

Change Data Capture can capture:

Inserts
Updates
Deletes

This allows downstream systems to stay synchronized with the source.

6. What is CDC?

CDC stands for Change Data Capture.

Instead of repeatedly reading an entire table, CDC captures changes.

For example:

			
Initial State
Customer 101 → Ved → Delhi

Later:

Customer 101 → Ved → Gurgaon

CDC captures:

			
UPDATE
customer_id = 101
city = Gurgaon

CDC may also capture:

			
INSERT
UPDATE
DELETE

This is particularly useful for:

Data warehouse synchronization
Lakehouse ingestion
Real-time pipelines
Database replication

7. How would you implement CDC in Delta Lake?

A common pattern is to use MERGE.

For example:

			
MERGE INTO target AS t
USING source AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET *
WHEN NOT MATCHED THEN
    INSERT *

		

For deletes, the logic can be extended depending on how the source represents delete events.

The important point is that MERGE allows us to perform an upsert operation.

This avoids rewriting the entire target dataset.

8. What is Delta Lake?

Delta Lake provides a transactional storage layer on top of data lake storage.

It adds capabilities such as:

ACID transactions
Schema enforcement
Schema evolution
Time travel
MERGE / UPSERT
Version history

Without transactional guarantees, concurrent writes to data lake files can become difficult to manage.

Delta Lake maintains transaction information in the _delta_log.

Conceptually:

			
Data Files
    +
Transaction Log
    ↓
Delta Table

		

This makes the data lake more reliable for production workloads.

9. What is Time Travel in Delta Lake?

Delta Lake maintains table versions.

This allows you to query historical versions of data.

For example:

			
SELECT *
FROM customers VERSION AS OF 10;

Or using a timestamp:

			
SELECT *
FROM customers
TIMESTAMP AS OF '2026-08-01 10:00:00';

This can be useful for:

Debugging
Auditing
Recovering previous states
Comparing data versions

Interview Tip

Don’t confuse Time Travel with a backup system.

Retention and vacuum policies determine how long historical files remain available.

10. How would you validate a migration?

Suppose you migrate:

			
Legacy System
      ↓
New Databricks Platform

The key question is:

How do you prove that the new system produces the same results?

A strong validation strategy should include multiple levels.

1. Record Count Validation

Compare:

			
Source Count
vs.
Target Count

2. Aggregate Validation

Compare:

			
SUM(amount)
COUNT(*)
MIN(date)
MAX(date)

3. Key-Level Validation

Compare records using business keys.

For example:

			
customer_id
transaction_id
order_id

4. Data Distribution Validation

Compare:

			
Records per day
Records per customer
Records per category

5. Business Rule Validation

Verify that transformations produce the expected business outcomes.

For example:

Source Revenue = Target Revenue

6. Reconciliation

Create a reconciliation report:

			
Source Records      10,000,000
Target Records      10,000,000
Missing Records     0
Extra Records       0
Amount Difference   0

		

A strong migration project usually runs both systems in parallel for a defined validation period before cutover.

11. How do you handle pipeline failures?

Imagine a pipeline with five steps:

			
Extract
  ↓
Transform
  ↓
Load
  ↓
Validate
  ↓
Publish

		

Suppose the pipeline fails during Load.

A production-ready system should provide:

Error logging
Retry mechanisms
Alerts
Checkpointing
Idempotency
Failure notifications

The pipeline should also be designed so that rerunning it does not create duplicates.

This is where idempotency becomes important.

12. What does idempotency mean in Data Engineering?

An operation is idempotent if executing it multiple times produces the same final result as executing it once.

Suppose a pipeline processes:

Transaction ID = 101

If the pipeline fails after writing the record and is restarted, it should not create:

			
101
101

Instead, the pipeline should safely produce:

Techniques include:

MERGE
Deduplication
Unique keys
Checkpoints
Transactional writes

Idempotency is a key concept in reliable pipelines.

13. How would you design a pipeline for reprocessing?

Suppose a transformation bug caused incorrect data to be generated for the last seven days.

You need to reprocess historical data.

A good architecture separates:

			
Raw Data
     ↓
Transformation Logic
     ↓
Curated Data

		

If raw data is retained, you can fix the transformation logic and replay the data.

This is one of the major advantages of preserving raw data in a Bronze layer.

You should also consider:

Partition pruning
Incremental reprocessing
Versioned transformation logic
Idempotent writes

14. How do you handle schema evolution?

Imagine the source initially sends:

			
customer_id
name
email

Later, the source adds:

phone_number

A robust pipeline should be able to handle such changes safely.

Schema evolution strategies include:

Explicit schema management
Schema validation
Schema evolution
Backward-compatible changes
Alerts for unexpected changes

Not every schema change should automatically be accepted.

For example, silently accepting a column type change from:

INTEGER → STRING

could introduce serious downstream problems.

Therefore, schema evolution should be combined with data quality checks and governance.

15. How do you monitor a production data pipeline?

Monitoring should cover more than pipeline success or failure.

Important metrics include:

Pipeline Metrics

			
Success / Failure
Execution Duration
Retry Count

Data Metrics

			
Record Count
Null Count
Duplicate Count
Freshness

Infrastructure Metrics

			
CPU
Memory
Executor Utilization
Shuffle
Storage

		

Business Metrics

			
Revenue
Transactions
Active Customers

A pipeline can technically succeed while producing incorrect data.

For example:

			
Pipeline Status: SUCCESS
Records Processed: 0

The pipeline technically succeeded, but the business outcome may be incorrect.

This is why data observability is becoming increasingly important.

16. What is the difference between ETL and ELT?

ETL

			
Extract
  ↓
Transform
  ↓
Load

		

Data is transformed before loading into the target system.

ELT

			
Extract
  ↓
Load
  ↓
Transform

		

Raw data is first loaded into the target platform.

Transformations happen later.

Modern cloud data platforms often favor ELT because scalable compute engines can perform transformations efficiently after data is loaded.

However, the correct choice depends on:

Architecture
Security
Data volume
Compute availability
Compliance requirements

17. How would you optimize a pipeline processing 1 TB of data?

A good answer should not immediately say:

“I’ll increase the cluster size.”

Start by finding the bottleneck.

A structured approach:

			
1. Identify slow stage
        ↓
2. Check Spark UI
        ↓
3. Check shuffle
        ↓
4. Check data skew
        ↓
5. Review join strategy
        ↓
6. Check partitioning
        ↓
7. Check file sizes
        ↓
8. Review transformations
        ↓
9. Optimize
        ↓
10. Benchmark again

		

Possible optimizations:

Predicate pushdown
Column pruning
Partition pruning
Broadcast joins
AQE
Salting
Appropriate partitioning
Avoiding unnecessary shuffles
Caching only when beneficial
Optimizing file sizes

The most important principle is:

Measure first, optimize second.

18. What makes a good Data Engineer?

A good Data Engineer is not simply someone who knows:

			
Python
SQL
Spark
Databricks
AWS
Azure

		

The real skill is knowing how these technologies fit together.

A strong Data Engineer can answer:

Where does the data come from?

How do I ingest it?

How do I process it efficiently?

How do I handle failures?

How do I validate the output?

How do I monitor the pipeline?

How do I recover from incorrect data?

How do I make the pipeline scalable?

The difference between a beginner and an experienced engineer is often the ability to think about the entire lifecycle of data.

Final Takeaway

Data Engineering interviews increasingly focus on practical scenarios rather than isolated definitions.

Knowing what a Delta table is is useful.

Knowing how to design a reliable incremental pipeline using Delta Lake, CDC, MERGE, data quality checks, monitoring, and recovery strategies is much more valuable.

The strongest candidates combine three layers of knowledge:

                    Data Engineering
                          │
          ┌───────────────┼───────────────┐
          │               │               │
        SQL            Distributed      Systems
                        Processing       Design
          │               │               │
      Analytics          Spark          Reliability
          │               │               │
       Business         PySpark         Monitoring
       Logic           Databricks       Recovery

If you can explain not only how to write the code, but also why the architecture works, how it scales, how it fails, and how you recover from failure, you’re thinking like a production Data Engineer.

Follow me on medium

Read all Data Engineering Tutorials here

Follow the complete learning paths:

1. How would you design an end-to-end data pipeline?

2. What is the Medallion Architecture?

Bronze Layer

Silver Layer

Gold Layer

Interview Tip

3. What is an incremental load?

4. What is a watermark?

5. How do you handle late-arriving data?

Lookback Window

Event-Time Processing

CDC

6. What is CDC?

7. How would you implement CDC in Delta Lake?

8. What is Delta Lake?

9. What is Time Travel in Delta Lake?

Interview Tip

10. How would you validate a migration?

1. Record Count Validation

2. Aggregate Validation

3. Key-Level Validation

4. Data Distribution Validation

5. Business Rule Validation

6. Reconciliation

11. How do you handle pipeline failures?

12. What does idempotency mean in Data Engineering?

13. How would you design a pipeline for reprocessing?

14. How do you handle schema evolution?

15. How do you monitor a production data pipeline?

Pipeline Metrics

Data Metrics

Infrastructure Metrics

Business Metrics

16. What is the difference between ETL and ELT?

ETL

ELT

17. How would you optimize a pipeline processing 1 TB of data?

18. What makes a good Data Engineer?

Final Takeaway

Share this:

Like this:

Related

Leave a Comment Cancel reply