Top 50 Big 4 Azure Data Engineer Interview Questions (3–8 Years Experience)

Published On: July 17, 2026

Azure Data Factory (ADF)

1. Explain the Architecture of Azure Data Factory

Interview Answer

Azure Data Factory (ADF) is Microsoft’s cloud-based ETL/ELT and data integration service used to move, transform, and orchestrate data from multiple sources.

Architecture Components

                Data Sources
      SQL | Oracle | SAP | API | Blob
                 |
                 |
          Linked Services
                 |
                 |
              Datasets
                 |
                 |
            Pipelines
          /     |      \
     Copy   Data Flow   Notebook
        Activity         Activity
                 |
           Integration Runtime
                 |
          Azure SQL / Synapse
           Data Lake / Snowflake

Components Explained

1. Pipeline

Pipeline is a logical grouping of activities.

Example

Extract Data
      ↓
Transform Data
      ↓
Load into Data Warehouse

2. Activity

Activities perform actual work.

Examples

Copy Activity
Data Flow Activity
Stored Procedure Activity
Lookup Activity
If Condition
Until Loop
Execute Pipeline

3. Dataset

Dataset represents the data structure.

Example

Sales.csv

Azure SQL Table

Customer Table

Parquet File

4. Linked Service

Connection information.

Example

Azure SQL

Connection String

Username

Password

Server Name

5. Integration Runtime (IR)

Execution engine.

ADF cannot move data without IR.

6. Trigger

Starts pipeline automatically.

Examples

Schedule Trigger
Tumbling Window
Event Trigger

7. Monitoring

Pipeline Runs

Activity Runs

Error Logs

Alerts

Real-world Example

Retail company

SAP ERP
      ↓
ADF Copy Activity
      ↓
Azure Data Lake
      ↓
Mapping Data Flow
      ↓
Azure Synapse
      ↓
Power BI

Interview Tip

Always explain architecture from

Source → Pipeline → Activities → Integration Runtime → Destination

2. Difference Between Mapping Data Flow and Wrangling Data Flow

Mapping Data Flow	Wrangling Data Flow
Built on Apache Spark	Built on Power Query
Developer Friendly	Business User Friendly
Large Data	Small/Medium Data
Highly Scalable	Less Scalable
Code Generated Automatically	No Code
ETL	Data Preparation

Mapping Data Flow

Used for

Join
Aggregate
Derived Column
Window
Pivot
Surrogate Key

Spark cluster executes transformations.

Wrangling Data Flow

Uses Power Query engine.

Mostly used by analysts.

Good for

Cleaning
Rename columns
Remove duplicates
Filter

Interview Example

10 TB sales data

Use

✅ Mapping Data Flow

Excel cleaning

Use

✅ Wrangling Data Flow

Best Practice

Production projects mostly use Mapping Data Flow.

3. How do you implement Incremental Data Loading in ADF?

Instead of loading entire data every day,

Load only changed records.

Method 1 — Watermark Column

Table

Customer

ID

Name

ModifiedDate

ADF stores last loaded date.

Last Run

2025-03-10

Next Query

SELECT *
FROM Customer
WHERE ModifiedDate >
'2025-03-10'

Only new records come.

Method 2 — SQL Change Tracking

ADF reads

Changed Rows

Only changed rows copied.

Method 3 — Change Data Capture (CDC)

Reads

Insert

Update

Delete

Ideal for enterprise systems.

Method 4 — Timestamp

CreatedDate

UpdatedDate

Simple implementation.

Real Project

Oracle ERP

↓

ADF Lookup Activity

↓

Read Last Watermark

↓

Copy Activity

↓

Azure Data Lake

↓

Update Watermark Table

Benefits

Fast

Low Cost

Less Compute

4. How do you Optimize ADF Pipeline Performance?

1. Parallel Copy

Increase

Parallel Copies = 8

instead of 1.

2. Partition Data

Split data

Load simultaneously.

3. Filter at Source

Instead of

SELECT *

Use

WHERE ModifiedDate >

4. Staging

Large SQL migration

SQL

↓

Blob Storage

↓

Synapse

Faster.

5. Self-hosted IR Scaling

Increase

CPU

RAM

Concurrent Jobs

6. Disable Debug

Debug cluster costs money.

7. Compression

Use

Parquet

Snappy

instead of CSV.

8. Avoid Too Many Activities

Merge logic wherever possible.

Interview Tip

Mention

“ADF itself doesn’t transform data fast.

Spark cluster performance depends on partitioning.”

5. Explain Triggers in Azure Data Factory

Triggers automatically execute pipelines.

Types

Schedule Trigger

Example

Daily

1 AM

Tumbling Window Trigger

Runs for fixed time windows.

Example

Hourly

Daily

Monthly

Supports rerun.

Best for incremental loads.

Event Trigger

Starts pipeline when

Blob Uploaded

Blob Deleted

Real-time processing.

Real-world Example

Vendor uploads

Sales.csv

↓

Blob Storage

↓

Event Trigger

↓

ADF Pipeline

↓

Data Lake

Best Practice

Batch

→ Schedule Trigger

Streaming

→ Event Trigger

6. How do you Handle Failures and Retries in ADF Pipelines?

ADF provides multiple options.

Retry Policy

Example

Retry Count = 3

Retry Interval = 60 sec

Dependency Conditions

Success

Failure

Skipped

Completed

Try-Catch Pattern

Pipeline

Main Activity

↓

Failure

↓

Send Email

↓

Log Error

Logging

Store

Pipeline Name

Activity

Error Message

Timestamp

Alerts

Azure Monitor

Logic Apps

Email Notification

Resume

Restart from failed activity.

Interview Example

SQL timeout

↓

Retry

↓

Success

No manual intervention.

7. What are Integration Runtime Types?

Integration Runtime is the execution engine.

Azure IR

Microsoft managed.

Used for

Azure → Azure

Cloud → Cloud

Self-hosted IR

Installed on

Windows Server

On-premise VM

Used for

SQL Server

Oracle

SAP

File Share

Azure SSIS IR

Runs SSIS packages.

Lift-and-shift migration.

Comparison

Azure IR	Self-hosted IR	SSIS IR
Cloud	On-prem	SSIS
Managed	User Managed	Managed
No Installation	Install Required	SSIS Execution

Interview Example

Company database inside firewall

↓

Install Self-hosted IR

↓

ADF securely accesses database.

8. How do you Securely Connect ADF to On-premises SQL Server?

Most common interview question.

Architecture

ADF

↓

Self-hosted IR

↓

Firewall

↓

SQL Server

Steps

Install Self-hosted IR.

Connect SQL Server.

Use Windows Authentication or SQL Authentication.

Store credentials in

Azure Key Vault.

ADF never directly accesses SQL Server.

All communication is encrypted.

Best Practices

✔ Azure Key Vault

✔ Private Endpoint

✔ Managed Identity

✔ Least Privilege Access

✔ Network Security Groups

Interview Tip

Never store passwords directly in Linked Services.

9. What is Parameterization in ADF?

Parameterization makes pipelines reusable.

Instead of creating multiple pipelines,

Use one pipeline with parameters.

Example

Pipeline Parameter

TableName

Run

Customer

Orders

Same pipeline.

Dataset Parameter

FileName

Sales.csv

Customer.csv

Orders.csv

Benefits

Reusable

Dynamic

Easy Maintenance

Reduced Development Time

Real-world Example

One pipeline loads

100 tables

using parameters.

No need to build

100 pipelines.

10. Explain a Real-world ETL Pipeline You Built Using ADF

Interview Answer

In my previous project, we built a metadata-driven ETL pipeline using Azure Data Factory to ingest sales and customer data from an on-premises SQL Server into Azure Synapse Analytics for reporting.

Architecture

On-prem SQL Server
        │
Self-hosted Integration Runtime
        │
ADF Copy Activity
        │
Azure Data Lake (Raw Zone)
        │
Mapping Data Flow
        │
Azure Data Lake (Curated Zone)
        │
Azure Synapse Analytics
        │
Power BI

Pipeline Flow

A Schedule Trigger started the pipeline every night at 1 AM.
A Lookup Activity read metadata (table names, watermark values, load type) from a control table.
A ForEach Activity looped through each table.
A Copy Activity loaded only incremental records using the ModifiedDate watermark column.
A Mapping Data Flow performed data cleansing, joins, derived columns, and removed duplicates.
Cleaned data was written to the curated layer in Azure Data Lake.
A Stored Procedure Activity loaded the curated data into Azure Synapse using MERGE logic for upserts.
The watermark table was updated with the latest successful load time.
Azure Monitor generated alerts if any activity failed, and retry policies automatically handled transient failures.

Optimizations Implemented

Incremental loading using watermark columns.
Parallel processing with ForEach and Copy Activity concurrency.
Parquet format with Snappy compression for better performance and lower storage cost.
Azure Key Vault to securely store database credentials.
Parameterized pipelines to support loading over 150 tables with a single reusable design.
Self-hosted Integration Runtime for secure connectivity to the on-premises SQL Server.

Business Outcome

Reduced daily ETL processing time from approximately 5 hours to 90 minutes.
Minimized data transfer by loading only changed records.
Improved reliability with automated retries, monitoring, and alerting.
Enabled near real-time dashboards in Power BI with consistent, high-quality data.

Azure Databricks

1. What are the benefits of Azure Databricks over traditional Spark clusters?

Interview Answer

Azure Databricks is a cloud-based analytics platform built on Apache Spark. It provides an optimized, managed Spark environment with additional enterprise features that reduce development effort and improve performance.

Unlike a traditional Spark cluster, where engineers are responsible for installing, configuring, scaling, monitoring, and securing the cluster, Azure Databricks manages these tasks automatically.

Traditional Spark Cluster Architecture

Developer
    │
Apache Spark
    │
Cluster Setup
    │
Hadoop/YARN
    │
Storage

You are responsible for:

Installing Spark
Configuring clusters
Managing executors
Monitoring
Scaling
Security
Upgrades

Azure Databricks Architecture

Developer
     │
Notebook
     │
Databricks Workspace
     │
Managed Spark Cluster
     │
Delta Lake
     │
ADLS / Synapse / SQL

Most infrastructure management is handled automatically.

Advantages

1. Auto Scaling

Automatically increases or decreases worker nodes based on workload.

Example:

Night ETL → 20 Workers
Daytime → 4 Workers

No manual intervention required.

2. Auto Termination

Idle clusters shut down automatically.

Benefits:

Reduces Azure costs
Saves compute resources

3. Optimized Spark Engine

Databricks Runtime includes performance optimizations over open-source Spark.

Typically:

Faster SQL queries
Faster joins
Better memory management

4. Delta Lake Support

Built-in support for:

ACID Transactions
Time Travel
Schema Evolution
MERGE
UPDATE
DELETE

Traditional Spark does not provide these features natively.

5. Collaborative Notebooks

Supports:

Python
SQL
Scala
R

Multiple developers can work in the same notebook simultaneously.

6. Job Scheduling

Supports:

Scheduled jobs
Workflow orchestration
Notifications
Retry mechanisms

7. ML Integration

Integrated with:

MLflow
TensorFlow
PyTorch
Scikit-Learn

8. Security

Supports:

Azure AD Authentication
Unity Catalog
Role-Based Access Control (RBAC)
Credential Passthrough

Real-world Example

Retail Company

Oracle Database
        │
Azure Data Factory
        │
Azure Data Lake
        │
Azure Databricks
        │
Delta Tables
        │
Azure Synapse
        │
Power BI

Interview Tip

Mention that Azure Databricks reduces cluster administration and allows engineers to focus on building data pipelines instead of managing infrastructure.

2. Explain the difference between RDD, DataFrame, and Dataset

Interview Answer

Apache Spark provides three major data abstractions:

RDD
DataFrame
Dataset

Each has different capabilities.

RDD (Resilient Distributed Dataset)

RDD is the original Spark data structure.

Characteristics:

Low-level API
Immutable
Distributed across nodes
Fault tolerant

Example

rdd = sc.textFile("/sales.csv")

Advantages:

Full control
Supports complex transformations

Disadvantages:

No query optimization
Slower
Higher memory usage

DataFrame

DataFrame is a distributed table with rows and columns.

Example

df = spark.read.csv("/sales.csv", header=True)

Benefits:

Structured data
SQL support
Catalyst Optimizer
Tungsten Engine

Much faster than RDD.

Dataset

Dataset combines:

DataFrame optimization
Strong typing

Mostly used in Scala and Java.

Rarely used in PySpark because Python does not support typed Datasets.

Comparison

Feature	RDD	DataFrame	Dataset
Type Safe	Yes	No	Yes
Optimization	No	Yes	Yes
Performance	Slow	Fast	Fast
SQL Support	No	Yes	Yes
Schema	No	Yes	Yes
Python Support	Yes	Yes	Limited

Which One Do Companies Use?

Production Databricks projects mostly use DataFrames because they provide the best balance of performance, optimization, and ease of development.

3. How do you optimize Spark jobs in Databricks?

Interview Answer

Spark optimization focuses on reducing execution time, minimizing shuffle operations, and using cluster resources efficiently.

1. Use DataFrames Instead of RDDs

DataFrames leverage the Catalyst Optimizer and Tungsten execution engine.

2. Partition Data Properly

Bad:

1 Partition

Good:

100 Partitions

Balanced partitions improve parallelism.

3. Cache Frequently Used Data

df.cache()

df.persist()

Avoids recomputation.

4. Broadcast Small Tables

broadcast(customer_df)

Useful when:

Customer table = 50 MB
Sales table = 1 TB

Reduces shuffle during joins.

5. Filter Early

Instead of loading all records:

df.filter(col("year")==2025)

Reduces data movement.

6. Avoid Wide Transformations

Wide operations cause shuffle.

Examples:

groupBy
join
distinct
orderBy

Use only when necessary.

7. Use Delta Lake

Delta provides:

Data skipping
File compaction
Better metadata management

8. Optimize Cluster Configuration

Choose:

Appropriate worker count
Executor memory
Core allocation

Real Example

A daily ETL job processing 800 GB of data was reduced from 2 hours to 40 minutes by using partition pruning, broadcast joins, caching, and Delta OPTIMIZE.

4. What causes data skew, and how do you resolve it?

Interview Answer

Data skew occurs when one partition contains significantly more data than others.

Example:

CustomerID

1001 → 2 million rows

1002 → 20 rows

1003 → 15 rows

One executor processes most of the data while others remain idle.

Problems

Long-running tasks
Executor memory issues
Poor cluster utilization

Solutions

1. Salting

Add a random key to distribute skewed values.

2. Broadcast Join

Broadcast the smaller table to all executors.

3. Repartition

df.repartition(200)

Creates more balanced partitions.

4. Adaptive Query Execution (AQE)

Automatically detects skewed partitions and splits them.

spark.conf.set("spark.sql.adaptive.enabled","true")

5. Filter Before Join

Reduce data volume before performing joins.

Real-world Example

An e-commerce sales table had one product accounting for 40% of transactions. By enabling AQE and using a broadcast join for the product dimension, job time reduced from 95 minutes to 35 minutes.

5. Explain partitioning and bucketing in Spark

Partitioning

Partitioning divides data into smaller logical chunks for parallel processing.

Example:

Sales

2024

2025

2026

Query:

WHERE Year=2025

Spark reads only the 2025 partition.

Benefits:

Faster reads
Less I/O
Partition pruning

Bucketing

Bucketing distributes data into a fixed number of files using a hash function.

Example:

Bucket 1

Bucket 2

Bucket 3

Bucket 4

Useful for:

Large joins
Frequent aggregations

Difference

Partitioning	Bucketing
Directory based	Hash based
Good for filtering	Good for joins
Dynamic	Fixed buckets
Supports partition pruning	Reduces shuffle during joins

6. What is Delta Lake, and why is it important?

Interview Answer

Delta Lake is an open-source storage layer built on top of Parquet that adds database-like capabilities to data lakes.

It solves common data lake problems such as inconsistent reads, duplicate data, and lack of transaction support.

Features

ACID Transactions

Ensures reliable concurrent reads and writes.

Schema Enforcement

Rejects invalid schemas.

Schema Evolution

Allows adding new columns safely.

Time Travel

Query previous versions of data.

MERGE

Supports UPSERT operations.

UPDATE

Update existing records.

DELETE

Delete records efficiently.

Data Versioning

Maintains transaction history.

Real-world Example

Banking systems update customer information throughout the day. Delta Lake guarantees consistent reads and prevents data corruption during concurrent writes.

7. Explain Delta Time Travel with a real use case

Interview Answer

Delta Time Travel allows querying historical versions of a Delta table.

Example:

SELECT *
FROM sales VERSION AS OF 5;

SELECT *
FROM sales TIMESTAMP AS OF '2025-01-01';

Real-world Use Case

A data engineer accidentally deleted customer records.

Instead of restoring from backups:

RESTORE TABLE customer TO VERSION AS OF 10;

The table is restored within minutes.

Benefits

Easy rollback
Audit history
Data recovery
Regulatory compliance

8. What is the difference between OPTIMIZE and VACUUM?

OPTIMIZE

Purpose:

Combines many small files into fewer large files.

Benefits:

Faster queries
Better scan performance
Reduced metadata overhead

Example:

OPTIMIZE sales;

VACUUM

Purpose:

Deletes old, unused data files no longer referenced by the Delta transaction log.

Example:

VACUUM sales RETAIN 168 HOURS;

Difference

OPTIMIZE	VACUUM
Improves performance	Frees storage space
Compacts files	Deletes obsolete files
Keeps all data versions	Removes old versions beyond retention
Used for query optimization	Used for housekeeping

Interview Tip

Run OPTIMIZE regularly for performance and VACUUM after the retention period to reclaim storage.

9. How do you tune Spark performance?

Interview Answer

Spark performance tuning involves optimizing computation, memory, storage, and shuffle operations.

Best Practices

Use DataFrames instead of RDDs.
Enable Adaptive Query Execution (AQE).
Cache frequently accessed datasets.
Use broadcast joins for small dimension tables.
Partition data appropriately.
Avoid unnecessary shuffles.
Use column pruning (select only required columns).
Filter data as early as possible.
Use Delta Lake with OPTIMIZE.
Monitor jobs using the Spark UI to identify bottlenecks.
Configure executor memory and cores based on workload.

Real Example

By enabling AQE, using broadcast joins, and optimizing Delta tables, a reporting pipeline processing 1.2 TB of data reduced execution time from 3 hours to 55 minutes.

10. Explain your Databricks notebook workflow

Interview Answer

In one of my projects, we built a metadata-driven Databricks workflow to process daily sales data from Azure Data Lake into Delta Lake and Azure Synapse.

Workflow

Azure Data Factory
        │
Triggers Databricks Notebook
        │
Read Raw Files from ADLS
        │
Data Validation
        │
Data Cleaning
        │
Transformations
        │
Write to Delta Bronze
        │
Transform to Silver
        │
Business Aggregations to Gold
        │
OPTIMIZE & VACUUM
        │
Load into Azure Synapse
        │
Power BI Reporting

Steps Performed

ADF triggered the notebook daily after new files arrived.
The notebook read raw CSV and Parquet files from Azure Data Lake.
Data quality checks validated schema, null values, and duplicates.
Business transformations included joins, aggregations, derived columns, and filtering.
Data was written to the Bronze, Silver, and Gold Delta layers.
OPTIMIZE was executed to compact small files, and VACUUM cleaned obsolete files after the retention period.
Processed data was loaded into Azure Synapse for reporting.
Notebook execution status and logs were monitored using Databricks Jobs and Azure Monitor.

Best Practices Used

Parameterized notebooks for reusability.
Secrets stored in Azure Key Vault.
Delta Lake for ACID transactions and schema evolution.
Auto-scaling clusters to optimize costs.
Git integration for version control.
Scheduled workflows with retries and notifications for failure handling.

Azure Synapse Analytics

1. What is Azure Synapse Analytics?

Interview Answer

Azure Synapse Analytics is Microsoft’s unified analytics platform that combines Data Warehousing, Big Data Analytics, Data Integration, and Business Intelligence into a single service.

It allows organizations to ingest, transform, store, analyze, and visualize large volumes of structured and unstructured data.

It integrates seamlessly with:

Azure Data Factory
Azure Data Lake Storage Gen2
Azure Databricks
Power BI
Apache Spark
Azure Machine Learning

Azure Synapse Architecture

             Data Sources
---------------------------------------
SQL | Oracle | SAP | API | CSV | JSON
---------------------------------------
                │
        Azure Data Factory
                │
       Azure Data Lake (Raw)
                │
      Azure Databricks / Spark
                │
     Azure Synapse Analytics
   (Dedicated / Serverless SQL)
                │
            Power BI

Main Components

1. SQL Pools

There are two SQL engines:

Dedicated SQL Pool
Serverless SQL Pool

2. Apache Spark Pool

Used for:

Machine Learning
ETL
Data Engineering
Streaming

3. Synapse Pipelines

Similar to Azure Data Factory.

Used for:

Copy Data
Scheduling
Workflow Orchestration

4. Data Explorer

Used for:

Log Analytics
IoT Data
Time-series Data

5. Studio

Web-based interface for

SQL
Spark
Monitoring
Pipelines
Security

Features

Massively Parallel Processing (MPP)
Petabyte-scale analytics
Distributed SQL engine
Integration with Power BI
Delta Lake support
Role-based security
Built-in monitoring

Real-world Example

An e-commerce company stores daily transaction data in Azure Data Lake. Azure Synapse loads and aggregates the data for dashboards used by finance, sales, and operations teams.

Interview Tip

Mention that Synapse is a complete analytics platform, not just a data warehouse.

2. Dedicated SQL Pool vs Serverless SQL Pool

Dedicated SQL Pool

Dedicated SQL Pool provides reserved compute resources.

Resources remain allocated until paused.

Suitable for:

Enterprise Data Warehousing
High-performance reporting
Predictable workloads

Example

CREATE TABLE Sales
(
    SalesID INT,
    Amount DECIMAL(10,2)
);

Serverless SQL Pool

Serverless SQL Pool does not store data.

It reads files directly from Azure Data Lake.

You pay only for the amount of data processed.

Example

SELECT *
FROM OPENROWSET(
    BULK 'sales/*.parquet',
    DATA_SOURCE='SalesLake',
    FORMAT='PARQUET'
) AS Sales;

Comparison

Feature	Dedicated SQL Pool	Serverless SQL Pool
Storage	Internal	External Data Lake
Cost	Reserved Compute	Pay Per Query
Performance	Very Fast	Depends on Files
Best For	Data Warehouse	Ad-hoc Analytics
Indexes	Supported	Not Supported
Materialized Views	Yes	No

Interview Tip

Dedicated SQL Pool is used for production reporting.

Serverless SQL Pool is ideal for exploration and ad-hoc querying.

3. Explain PolyBase and COPY INTO

PolyBase

PolyBase is a high-performance data loading technology.

It reads data directly from:

Azure Blob Storage
Azure Data Lake
Hadoop

without first copying it locally.

Architecture

Azure Data Lake
       │
   PolyBase
       │
Dedicated SQL Pool

Example

CREATE EXTERNAL TABLE Sales
(
    ID INT,
    Amount FLOAT
)
WITH
(
    LOCATION='sales/',
    DATA_SOURCE=SalesLake,
    FILE_FORMAT=ParquetFormat
);

COPY INTO

COPY INTO is Microsoft’s newer and simpler data loading command.

Example

COPY INTO Sales
FROM 'https://storage/sales.csv'
WITH
(
    FILE_TYPE='CSV'
);

Difference

PolyBase	COPY INTO
Older	Newer
More Configuration	Simple Syntax
External Tables	Direct Loading
High Performance	High Performance
Enterprise ETL	Modern ETL

Best Practice

Microsoft recommends using COPY INTO for new Synapse implementations because it is easier to configure and maintain.

4. How do you optimize Synapse query performance?

Interview Answer

Optimizing Synapse involves reducing data movement, improving scan efficiency, and maximizing parallel processing.

1. Choose the Right Distribution

Avoid unnecessary data movement.

2. Partition Large Tables

Example

Sales

2023

2024

2025

Only required partitions are scanned.

3. Columnstore Index

Default index for large fact tables.

Benefits:

High compression
Faster scans

4. Statistics

Keep statistics updated.

UPDATE STATISTICS Sales;

5. Materialized Views

Precompute expensive joins and aggregations.

6. Result Set Caching

Frequently executed queries return cached results.

7. Filter Early

Instead of:

SELECT *
FROM Sales;

Use:

SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

8. Reduce Data Movement

Choose proper table distribution to minimize shuffle operations.

Real-world Example

A finance reporting query that originally took 12 minutes was reduced to 2 minutes by using hash distribution, partitioning by transaction date, and creating materialized views.

5. What are distribution methods in Synapse?

Interview Answer

Synapse distributes table data across 60 distributions to enable Massively Parallel Processing (MPP).

There are three distribution methods.

1. Hash Distribution

Rows are distributed based on a hash of a selected column.

Example

DISTRIBUTION = HASH(CustomerID)

Best for:

Large fact tables
Frequent joins

2. Round Robin

Rows are distributed evenly without considering column values.

Example

DISTRIBUTION = ROUND_ROBIN

Best for:

Staging tables
Temporary tables

3. Replicated Table

Entire table is copied to every compute node.

Best for:

Small dimension tables
Lookup tables

Comparison

Distribution	Best Use
Hash	Large Fact Tables
Round Robin	Staging Tables
Replicated	Small Dimension Tables

Interview Tip

Hash distribution minimizes data movement during joins and is the preferred option for large transactional tables.

6. Explain partitioning strategies in Synapse

Interview Answer

Partitioning divides a large table into smaller logical segments based on a column.

Example

Sales Table

Query

SELECT *
FROM Sales
WHERE OrderDate='2025-03-01';

Only the relevant partition is scanned.

Common Partition Keys

Date
Month
Year
Region

Benefits

Faster query execution
Easier maintenance
Faster data loading
Partition elimination

Best Practice

Partition only very large tables (typically over one billion rows) and avoid creating too many small partitions.

7. How do you secure Synapse Analytics?

Interview Answer

Security is implemented using multiple layers.

1. Azure Active Directory

Centralized authentication.

2. Role-Based Access Control (RBAC)

Assign permissions such as:

Synapse Administrator
SQL Administrator
Reader

3. Managed Identity

Avoid storing credentials in code.

4. Private Endpoint

Keep Synapse accessible only through private networks.

5. Firewall Rules

Restrict IP access.

6. Transparent Data Encryption (TDE)

Encrypts data at rest.

7. Dynamic Data Masking

Hide sensitive columns.

Example:

9876543210

↓

98XXXXXX10

8. Row-Level Security

Different users see only authorized rows.

Example:

North Region Manager sees only North region sales.

9. Azure Key Vault

Store:

Passwords
Secrets
Certificates

Securely outside application code.

Interview Tip

Mention Azure AD, RBAC, Managed Identity, Private Endpoints, and Key Vault together to demonstrate a comprehensive security approach.

8. What monitoring tools do you use for Synapse?

Interview Answer

Monitoring ensures pipeline reliability and query performance.

1. Synapse Studio Monitor

Track:

Pipeline runs
SQL requests
Spark jobs
Activity history

2. Azure Monitor

Collects:

Metrics
Alerts
Logs

3. Log Analytics

Analyze historical logs.

4. Query Performance Insights

Monitor:

Long-running queries
CPU usage
Memory utilization

5. Spark UI

Used for:

Stage execution
Shuffle operations
Executor performance

6. Azure Advisor

Provides recommendations for performance and cost optimization.

Real-world Example

A scheduled pipeline failure generated an Azure Monitor alert, which triggered an email notification. Investigation through Synapse Studio identified a storage permission issue, allowing the team to resolve it quickly.

9. Explain workload management in Synapse

Interview Answer

Workload management controls how compute resources are allocated among different users and workloads.

It ensures critical queries receive sufficient resources while preventing less important workloads from consuming all available compute.

Components

Workload Groups

Allocate CPU and memory to different workload categories.

Example:

ETL Jobs
Reporting
Ad-hoc Queries

Resource Classes

Assign memory based on user requirements.

Examples:

SmallRC
MediumRC
LargeRC
XLargeRC

Workload Classifiers

Automatically route queries into appropriate workload groups.

Real-world Example

An organization separated ETL workloads from Power BI reporting workloads. ETL jobs ran overnight with higher resource allocation, while daytime reporting queries were prioritized to ensure fast dashboard performance.

Benefits

Prevents resource contention
Improves query response times
Ensures predictable performance
Supports multiple concurrent workloads

10. Describe a Synapse migration project you worked on

Interview Answer

In one of my projects, we migrated an on-premises SQL Server data warehouse to Azure Synapse Analytics to improve scalability, performance, and reporting capabilities.

Architecture

On-Prem SQL Server
        │
Azure Data Factory
        │
Azure Data Lake Storage
        │
Azure Synapse Analytics
        │
Power BI

Migration Steps

Step 1

Analyzed:

Existing schema
Stored procedures
ETL jobs
Data volumes

Step 2

Used Azure Data Factory to extract historical and incremental data from SQL Server into Azure Data Lake.

Step 3

Loaded data into Azure Synapse using COPY INTO.

Step 4

Designed tables with:

Hash distribution for fact tables
Replicated distribution for dimension tables
Partitioning by transaction date

Step 5

Created:

Columnstore indexes
Materialized views
Statistics

to improve reporting performance.

Step 6

Migrated reporting dashboards to Power BI connected to Synapse.

Security

Azure AD authentication
RBAC
Managed Identity
Azure Key Vault
Private Endpoints

Monitoring

Used:

Synapse Studio Monitor
Azure Monitor
Log Analytics

to track pipeline health, query performance, and resource utilization.

Business Outcome

Reduced daily ETL execution time from 6 hours to 1.5 hours.
Improved reporting query performance by approximately 70%.
Enabled scalable analytics on several terabytes of data.
Reduced infrastructure maintenance through a fully managed cloud platform.

Azure Storage & Data Lake

1. What is Azure Data Lake Storage Gen2?

Interview Answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is Microsoft’s cloud-based, highly scalable storage service designed for Big Data Analytics. It combines the capabilities of Azure Blob Storage with a hierarchical file system (HNS), making it optimized for analytics workloads.

It is commonly used as the central storage layer in modern data platforms with services like:

Azure Data Factory
Azure Databricks
Azure Synapse Analytics
HDInsight
Power BI
Azure Machine Learning

Architecture

                Data Sources
------------------------------------------------
SQL | Oracle | SAP | APIs | IoT | CSV | JSON
------------------------------------------------
                     │
            Azure Data Factory
                     │
          Azure Data Lake Storage Gen2
                     │
        Azure Databricks / Spark
                     │
        Azure Synapse Analytics
                     │
                Power BI

Key Features

1. Hierarchical Namespace (HNS)

Unlike Blob Storage, ADLS Gen2 organizes data in folders and directories.

Example

Raw
   Sales
      2025
         July
             sales.csv

Benefits

Faster file operations
Better organization
Efficient rename and move operations

2. Massively Scalable

Supports:

Petabytes of data
Billions of files

Suitable for enterprise-scale analytics.

3. Low Cost

Storage is separated from compute.

You only pay for:

Storage used
Transactions
Data transfer (where applicable)

4. High Availability

Supports:

LRS (Locally Redundant Storage)
ZRS (Zone Redundant Storage)
GRS (Geo Redundant Storage)
RA-GRS (Read Access Geo Redundant Storage)

5. Security

Supports

Azure Active Directory (AAD)
RBAC
ACLs (Access Control Lists)
Managed Identity
Private Endpoints
Customer-managed keys

6. Multiple File Formats

Supports:

CSV
Parquet
Avro
JSON
ORC
Delta

7. Integration

Native integration with:

Azure Data Factory
Databricks
Synapse
Power BI

Real-world Example

A retail company receives sales data every hour from hundreds of stores.

Pipeline:

Stores
   │
ADF Copy Activity
   │
ADLS Gen2 (Raw Zone)
   │
Databricks
   │
ADLS Curated
   │
Synapse
   │
Power BI

ADLS acts as the central storage layer.

Interview Tip

Mention that ADLS Gen2 combines the scalability of Blob Storage with a hierarchical file system, making it ideal for analytics and data lake architectures.

2. Explain Bronze, Silver, and Gold Architecture

Interview Answer

Bronze, Silver, and Gold is a multi-layer data lake architecture used to organize and improve data quality as it moves through the analytics pipeline.

This architecture is widely used in:

Azure Databricks
Delta Lake
Microsoft Fabric
Lakehouse implementations

Architecture

Source Systems
      │
ADF / Streaming
      │
-------------------------
Bronze Layer (Raw Data)
-------------------------
      │
Data Cleaning
Validation
Deduplication
      │
-------------------------
Silver Layer (Clean Data)
-------------------------
      │
Business Logic
Aggregations
Joins
KPIs
      │
-------------------------
Gold Layer
Business Ready Data
-------------------------
      │
Power BI / Reports

Bronze Layer (Raw)

Purpose:

Store raw, unmodified data exactly as received.

Characteristics:

No transformations
Original format retained
Historical archive
Supports reprocessing

Example

sales_20260717.csv

If a downstream process fails, the original data is still available.

Silver Layer (Cleaned Data)

Purpose:

Improve data quality.

Typical transformations:

Remove duplicates
Handle null values
Correct data types
Standardize formats
Apply basic business validations

Example

Before

CustomerID	Name	Amount
101	Amit	NULL
101	Amit	NULL

After

CustomerID	Name	Amount
101	Amit	500

Gold Layer (Business Data)

Purpose:

Provide analytics-ready datasets.

Typical operations:

Joins
Aggregations
KPIs
Fact and dimension tables

Example

Monthly Sales Summary

Region

Revenue

Profit

Growth %

Power BI connects directly to Gold.

Benefits

Better data quality
Easier troubleshooting
Historical storage
Reprocessing capability
Faster reporting
Clear separation of responsibilities

Real-world Example

An insurance company:

Bronze:

Raw policy data

Silver:

Clean policy records
Remove duplicate customers
Validate premium amounts

Gold:

Daily premium reports
Claim analytics
Executive dashboards

Interview Tip

A simple way to remember:

Bronze = Raw
Silver = Clean
Gold = Business Ready

3. How do you manage large files in ADLS?

Interview Answer

Managing large files efficiently is critical for performance and cost optimization.

1. Use Parquet Instead of CSV

CSV

Large size
Slow reads

Parquet

Compressed
Columnar
Faster queries

Example

CSV
500 GB

↓

Parquet

120 GB

2. Partition Data

Example

Sales

Year=2025

Month=07

Day=17

Queries read only the required partitions.

3. File Size Optimization

Avoid:

1 KB files

Millions of files

Instead:

100 MB–1 GB files

This reduces metadata overhead and improves parallel processing.

4. Compression

Use:

Snappy
Gzip
ZSTD (where supported)

Benefits:

Less storage
Faster transfer
Lower cost

5. Delta Lake

Delta automatically manages:

Transaction logs
File compaction
Metadata

6. Compaction

In Databricks:

OPTIMIZE sales;

Combines many small files into fewer larger files.

7. Folder Structure

Good

Sales

2025

07

17

Bad

Everything in one folder

Real-world Example

An IoT platform generated millions of 5 KB files every day. By compacting them into 256 MB Parquet files, query execution time reduced significantly and storage operations became much more efficient.

Best Practices

Use Parquet or Delta format.
Partition by frequently filtered columns (e.g., date).
Compact small files regularly.
Avoid deeply nested folder structures.
Monitor storage usage and transaction counts.

4. What are lifecycle management policies in Azure Storage?

Interview Answer

Lifecycle management policies automatically move or delete data based on predefined rules.

They help reduce storage costs by moving infrequently accessed data to cheaper storage tiers.

Storage Tiers

Hot

Frequently accessed
Highest storage cost
Lowest access cost

Cool

Infrequently accessed
Lower storage cost
Higher access cost

Example Lifecycle Policy

New File

↓

0–30 Days

Hot Tier

↓

31–90 Days

Cool Tier

↓

After 180 Days

Archive

↓

After 7 Years

Delete

Benefits

Automatic cost optimization
No manual intervention
Compliance with retention policies
Reduced storage expenses

Real-world Example

A financial institution retains transaction logs:

First 30 days → Hot
Next 6 months → Cool
After 6 months → Archive
Delete after 7 years as per company policy

Best Practices

Define lifecycle rules based on business needs.
Use Archive only for rarely accessed data.
Test policies before applying them to production.
Monitor policy execution using Azure Storage metrics.

5. How do you secure Azure Storage accounts?

Interview Answer

Security is implemented using multiple layers to protect data both at rest and in transit.

1. Azure Active Directory (AAD)

Use Azure AD for authentication instead of storage account keys wherever possible.

2. Role-Based Access Control (RBAC)

Assign least-privilege roles such as:

Storage Blob Data Reader
Storage Blob Data Contributor
Storage Blob Data Owner

3. Access Control Lists (ACLs)

Set permissions at the folder and file level.

Example:

Finance Folder

Manager

Read + Write

Employee

Read Only

4. Managed Identity

Azure services like ADF, Databricks, and Synapse can securely access storage without storing passwords.

5. Private Endpoint

Restrict storage access to private virtual networks.

Public internet access is disabled.

6. Firewall Rules

Allow access only from approved IP addresses or virtual networks.

7. Encryption

Data is encrypted:

At rest (Storage Service Encryption)
In transit (HTTPS/TLS)

Optionally use customer-managed keys in Azure Key Vault.

8. Shared Access Signatures (SAS)

Grant temporary, limited access to storage resources.

Example:

Valid for:

2 Hours

Read Only

9. Soft Delete & Versioning

Protect against accidental deletion by enabling:

Blob soft delete
Container soft delete
Versioning

10. Azure Defender for Storage

Provides threat detection for:

Suspicious access
Malware scanning (where applicable)
Unusual activity patterns

Real-world Example

In a production environment:

Azure Data Factory and Databricks use Managed Identity.
Storage account access is restricted through Private Endpoints and Firewall Rules.
Secrets are stored in Azure Key Vault.
RBAC and ACLs enforce least-privilege access.
Lifecycle policies move older data to the Cool and Archive tiers to optimize costs.

SQL & Data Warehousing

1. How do you optimize SQL queries handling billions of records?

Interview Answer

When working with billions of records, query optimization is essential to reduce execution time, CPU usage, memory consumption, and disk I/O. The goal is to retrieve only the required data using the most efficient execution plan.

Example Scenario

Suppose you have a Sales table containing 5 billion records.

Sales
---------------------------------
SaleID
CustomerID
ProductID
OrderDate
Region
Amount

Business Requirement:

Retrieve sales for the year 2025.

1. Avoid SELECT *

❌ Bad

SELECT *
FROM Sales;

It reads every column, increasing I/O.

✅ Good

SELECT CustomerID,
       Amount,
       OrderDate
FROM Sales
WHERE OrderDate >= '2025-01-01';

Reads only required columns.

2. Create Proper Indexes

Example

CREATE INDEX IX_OrderDate
ON Sales(OrderDate);

The database can locate rows quickly without scanning the entire table.

3. Partition Large Tables

Instead of storing all records together:

Query

SELECT *
FROM Sales
WHERE OrderDate='2025-06-01';

Only the 2025 partition is scanned.

4. Filter Early

Instead of

SELECT *
FROM Sales;

Use

SELECT *
FROM Sales
WHERE Region='North';

Reduces data processed.

5. Use EXISTS Instead of IN

Large tables

SELECT CustomerID
FROM Customer c
WHERE EXISTS
(
SELECT 1
FROM Sales s
WHERE s.CustomerID=c.CustomerID
);

Usually performs better than IN on large datasets because the optimizer can stop after finding the first match.

6. Avoid Functions in WHERE Clause

❌

WHERE YEAR(OrderDate)=2025

May prevent index usage.

✅

WHERE OrderDate>='2025-01-01'
AND OrderDate<'2026-01-01'

Allows index seeks.

7. Update Statistics

UPDATE STATISTICS Sales;

Accurate statistics help the optimizer choose better execution plans.

8. Use Appropriate JOINs

Avoid unnecessary joins.

Always join on indexed columns.

9. Review Execution Plan

Look for:

Table Scans
High Cost Operators
Missing Indexes
Expensive Sorts
Excessive Hash Joins

10. Archive Old Data

Move historical records to archive tables.

Instead of

10 Billion Records

Active table

500 Million Records

This improves query performance.

Real-world Example

An insurance company had a 4-billion-row claims table.

Optimizations:

Partitioned by ClaimDate.
Created a clustered index on ClaimID.
Added non-clustered indexes on CustomerID and PolicyNumber.
Rewrote queries to avoid SELECT *.
Updated statistics regularly.

Result:

Query time reduced from 18 minutes to 45 seconds.

Interview Tip

Mention:

“I first check the execution plan, identify table scans, add appropriate indexes, partition large tables, filter data early, and ensure statistics are updated.”

2. Explain Clustered vs Non-Clustered Indexes

Interview Answer

Indexes improve data retrieval speed.

There are two major types:

Clustered Index
Non-Clustered Index

Clustered Index

A clustered index determines the physical order of rows on disk.

A table can have only one clustered index because data can be physically ordered only one way.

Example

CREATE CLUSTERED INDEX IX_SaleID
ON Sales(SaleID);

Example

Without Index

After Clustered Index

Advantages

Very fast range queries.
Fast sorting.
Efficient primary key lookups.

Non-Clustered Index

A non-clustered index stores a separate structure containing indexed columns and pointers to the actual rows.

A table can have many non-clustered indexes.

Example

CREATE NONCLUSTERED INDEX IX_Customer
ON Sales(CustomerID);

Structure

CustomerID

1001 → Row 50

1002 → Row 320

1003 → Row 20

Advantages

Fast searches.
Supports multiple search patterns.
Ideal for frequently filtered columns.

Comparison

Feature	Clustered	Non-Clustered
Physical Order	Yes	No
Maximum Per Table	1	Many
Best For	Primary Key, Range Queries	Search Columns
Storage	Data Pages	Separate Index Structure
Lookup Speed	Very Fast	Fast

Real-world Example

Orders table:

Clustered Index → OrderID
Non-Clustered Index → CustomerID, OrderDate, ProductID

This supports fast primary key lookups while optimizing common search queries.

Interview Tip

A clustered index defines how data is stored physically, while a non-clustered index is a separate lookup structure pointing to the data.

3. What is Slowly Changing Dimension (SCD)? Explain Type 1 and Type 2.

Interview Answer

A Slowly Changing Dimension (SCD) is a data warehousing concept used to manage changes in dimension data over time.

Example:

Customer changes:

Address
Phone Number
City

The question is whether to overwrite the old value or preserve history.

Example

Customer Table

CustomerID

Name

City

Initially

CustomerID	Name	City
101	Rahul	Delhi

Customer moves to Mumbai.

SCD Type 1

Old value is overwritten.

Before

|101|Rahul|Delhi|

After

|101|Rahul|Mumbai|

Advantages

Simple implementation.
Less storage.

Disadvantages

No historical tracking.

SQL Example

UPDATE Customer
SET City='Mumbai'
WHERE CustomerID=101;

SCD Type 2

History is preserved.

Instead of updating the row, insert a new version.

Before

CustomerID	City	StartDate	EndDate	Current
101	Delhi	2023-01-01	9999-12-31	Yes

After

CustomerID	City	StartDate	EndDate	Current
101	Delhi	2023-01-01	2025-05-31	No
101	Mumbai	2025-06-01	9999-12-31	Yes

Advantages

Complete historical tracking.
Supports audits and trend analysis.

Disadvantages

Requires more storage.
Slightly more complex ETL logic.

Real-world Example

A bank needs to know which branch a customer belonged to when a loan was approved. SCD Type 2 preserves that historical information.

Comparison

Feature	Type 1	Type 2
History	No	Yes
Update	Overwrite	Insert New Row
Storage	Low	Higher
Audit	Not Possible	Supported

Interview Tip

Type 1 is suitable when history is not important (e.g., correcting spelling mistakes). Type 2 is preferred when historical analysis is required.

4. Explain Star Schema vs Snowflake Schema.

Interview Answer

Both are dimensional modeling techniques used in data warehouses.

Star Schema

Fact table is connected directly to denormalized dimension tables.

Architecture

          Product
             │
Customer ─ Fact Sales ─ Date
             │
          Store

Fact Table

Sales

CustomerID

ProductID

DateID

Amount

Advantages

Simple design.
Faster queries.
Fewer joins.

Disadvantages

Data redundancy in dimensions.

Snowflake Schema

Dimensions are normalized into multiple related tables.

Architecture

Category
     │
 Product
     │
Fact Sales
     │
 Customer
     │
 Region

Advantages

Reduced redundancy.
Better data consistency.

Disadvantages

More joins.
Slightly slower queries.

Comparison

Feature	Star	Snowflake
Normalization	No	Yes
Query Speed	Faster	Slightly Slower
Storage	More	Less
Joins	Fewer	More
Complexity	Simple	Complex

Real-world Example

An e-commerce analytics platform often uses a Star Schema because Power BI dashboards require fast query performance.

A master data management system may use a Snowflake Schema to reduce redundancy and maintain consistency across shared dimensions.

Interview Tip

For reporting and BI, Star Schema is usually preferred because it minimizes joins and delivers better query performance.

5. How do you identify and eliminate duplicate records?

Interview Answer

Duplicates can occur due to repeated file loads, application issues, or missing primary key constraints.

The first step is to identify duplicates, then remove or prevent them based on business rules.

Step 1: Identify Duplicates

Example

Customer

101

Rahul

101

Rahul

SQL

SELECT CustomerID,
       COUNT(*) AS DuplicateCount
FROM Customer
GROUP BY CustomerID
HAVING COUNT(*) > 1;

This returns only duplicated Customer IDs.

Step 2: Remove Duplicates Using ROW_NUMBER()

WITH CTE AS
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY CustomerID
ORDER BY CustomerID
) AS RN
FROM Customer
)
DELETE FROM CTE
WHERE RN > 1;

The first record is kept, and additional duplicates are removed.

Step 3: Prevent Future Duplicates

Add Primary Keys or Unique Constraints.
Validate data before loading.
Use MERGE statements or upsert logic in ETL.
Perform deduplication in Azure Data Factory or Databricks before loading into the warehouse.

Real-world Example

A CRM system occasionally sent duplicate customer records due to retry logic in an upstream API. During the ETL process:

Data landed in the Bronze layer.
A Databricks notebook used ROW_NUMBER() to identify duplicates based on CustomerID and Email.
Only the latest record for each customer was written to the Silver layer.
A unique constraint on the warehouse table prevented future duplicate inserts.

This eliminated duplicate customer records while preserving the most recent information.

Interview Tip

When discussing duplicates, mention both detection (GROUP BY, ROW_NUMBER()) and prevention (constraints, MERGE logic, ETL validation). Interviewers often look for a complete end-to-end approach rather than just a SQL query.

Python & PySpark

1. How do you handle null values in PySpark?

Interview Answer

Handling null values is one of the most important steps in data engineering because nulls can cause incorrect aggregations, failed joins, inaccurate reports, and machine learning issues.

PySpark provides multiple ways to identify, replace, remove, and process null values depending on the business requirement.

Sample Data

CustomerID	Name	City	Salary
101	Rahul	Delhi	50000
102	NULL	Mumbai	60000
103	Amit	NULL	NULL
104	NULL	NULL	70000

1. Find Null Values

from pyspark.sql.functions import col,isnull

df.filter(col("Name").isNull()).show()

df.filter(isnull("Salary")).show()

2. Count Null Values

from pyspark.sql.functions import col,sum

df.select([
sum(col(c).isNull().cast("int")).alias(c)
for c in df.columns
]).show()

Output

Column	Null Count
Name	2
City	2
Salary	1

3. Drop Null Records

Remove rows containing null values.

df.dropna().show()

Drop only when all values are null.

df.dropna(how="all")

Drop rows if Salary is null.

df.dropna(subset=["Salary"])

4. Replace Null Values

Replace Salary with 0.

df.fillna({"Salary":0})

Replace City

df.fillna({"City":"Unknown"})

Multiple columns

df.fillna({
"City":"Unknown",
"Salary":0
})

5. Replace Using when()

from pyspark.sql.functions import when

df=df.withColumn(
"City",
when(col("City").isNull(),"Unknown")
.otherwise(col("City"))
)

Useful for applying business rules.

6. Replace with Mean

mean_salary=df.selectExpr("avg(Salary)").first()[0]

df=df.fillna({"Salary":mean_salary})

Common in analytics and machine learning.

7. SQL Approach

df.createOrReplaceTempView("customer")

SELECT
COALESCE(City,'Unknown')
FROM customer

Real-world Example

A telecom company received daily customer files with missing city and income values.

Solution:

City → “Unknown”
Salary → Average salary
CustomerID NULL → Record rejected

Result:

Data quality improved, and reporting errors were eliminated.

Best Practices

✔ Never delete records blindly.

✔ Understand business meaning before replacing nulls.

✔ Use default values only where appropriate.

✔ Reject records if mandatory columns are missing.

Interview Tip

Always explain how you identify nulls, how you decide whether to replace or remove them, and how business rules influence the approach.

2. Explain Window Functions with a real-world example

Interview Answer

Window Functions perform calculations across a group of related rows while retaining every row in the output.

Unlike GROUP BY, they do not collapse rows into a single result.

Why Use Window Functions?

Common use cases:

Ranking
Running totals
Moving averages
Deduplication
Latest record selection
Previous and next values

Example Data

Employee	Department	Salary
Rahul	IT	60000
Amit	IT	70000
Neha	IT	65000
Riya	HR	50000
Priya	HR	55000

Rank Employees

from pyspark.sql.window import Window
from pyspark.sql.functions import rank

windowSpec=Window.partitionBy("Department").orderBy(col("Salary").desc())

df.withColumn("Rank",rank().over(windowSpec))

Output

Employee	Department	Salary	Rank
Amit	IT	70000	1
Neha	IT	65000	2
Rahul	IT	60000	3

Row Number

from pyspark.sql.functions import row_number

df.withColumn(
"RN",
row_number().over(windowSpec)
)

Commonly used for removing duplicate records.

Dense Rank

dense_rank()

Unlike rank(), it does not skip numbers after ties.

Running Total

from pyspark.sql.functions import sum

windowSpec=Window.orderBy("Date")

df.withColumn(
"RunningTotal",
sum("Sales").over(windowSpec)
)

Lag

Previous month’s sales

from pyspark.sql.functions import lag

lag("Sales",1)

Lead

Next month’s sales

lead("Sales",1)

Real-world Example

A banking application stores multiple address updates for each customer.

Requirement:

Keep only the latest address.

Solution:

Window.partitionBy("CustomerID")
.orderBy(col("UpdatedDate").desc())

Assign:

row_number()

Keep only:

RN=1

Result:

Latest customer record retained while older versions are ignored.

Interview Tip

A common production use of window functions is deduplication using ROW_NUMBER(), which interviewers frequently expect candidates to mention.

3. What are Broadcast Joins, and when should you use them?

Interview Answer

A Broadcast Join is an optimization technique where Spark sends a small table to every executor so that joins can be performed locally without shuffling the large table.

This significantly reduces network traffic and improves performance.

Example

Sales Table

1 Billion Rows

Customer Table

5 MB

Without Broadcast

Executor

Shuffle

Executor

Shuffle

Executor

Large data movement occurs.

With Broadcast

Customer Table

↓

Copied to every Executor

↓

Local Join

↓

No Shuffle

PySpark Example

from pyspark.sql.functions import broadcast

result=sales.join(
broadcast(customer),
"CustomerID"
)

When to Use

Broadcast joins are suitable when:

One table is very small (typically less than a few hundred MB, depending on cluster memory).
The other table is very large.
Memory on executors is sufficient.

Benefits

Eliminates shuffle for the small table.
Faster joins.
Lower network overhead.
Better cluster utilization.

When Not to Use

Do not broadcast:

Two large tables.
Tables larger than executor memory.
Cases where the broadcast table changes frequently during execution.

Real-world Example

An e-commerce platform joins a 2 TB Sales table with a 20 MB Product table.

Using a broadcast join reduced execution time from 50 minutes to 12 minutes by eliminating shuffle for the product dimension.

Interview Tip

Mention that Broadcast Join is ideal for joining a very small dimension table with a very large fact table.

4. How do you optimize joins in Spark?

Interview Answer

Joins are among the most expensive operations in Spark because they often involve shuffle, where data moves across executors.

Optimization focuses on reducing shuffle, balancing data distribution, and choosing the correct join strategy.

1. Broadcast Small Tables

sales.join(
broadcast(customer),
"CustomerID"
)

Removes shuffle for the small table.

2. Partition Data

If both datasets are partitioned on the join key, Spark moves less data.

Example

sales.repartition("CustomerID")

3. Filter Before Join

Instead of joining all rows:

sales.filter(
col("Year")==2025
)

Join only filtered records.

4. Handle Data Skew

Problem

CustomerID

1001

20 Million Rows

Solutions:

Salting
Adaptive Query Execution (AQE)
Repartitioning

5. Use Correct Join Type

Instead of:

FULL OUTER JOIN

Use

INNER JOIN

when appropriate.

Less data movement.

6. Enable AQE

spark.conf.set(
"spark.sql.adaptive.enabled",
"true"
)

AQE automatically optimizes joins, detects skew, and adjusts execution plans.

7. Remove Unnecessary Columns

Select only required columns before joining.

sales.select(
"CustomerID",
"Amount"
)

Reduces memory and network usage.

8. Use Delta Lake

Optimized file layout and statistics improve join performance.

Real-world Example

A retail company joined a 900 GB Sales table with a 15 MB Product table.

Optimizations:

Broadcast Product.
Filter Sales by date before join.
Repartition on ProductID.
Enable AQE.

Execution time reduced from 70 minutes to 18 minutes.

Interview Tip

State that you always:

Filter early.
Broadcast small tables.
Minimize shuffle.
Handle skew.
Use AQE.

This demonstrates a practical optimization approach.

5. Explain caching and persistence in Spark.

Interview Answer

Spark uses lazy evaluation. Every action recomputes the lineage unless intermediate results are stored.

Caching and persistence store intermediate DataFrames or RDDs in memory (or disk) so they can be reused without recomputation.

Example Without Cache

Read Data

↓

Transform

↓

Action 1

↓

Read Again

↓

Transform Again

↓

Action 2

The same transformations are repeated.

With Cache

Read Data

↓

Transform

↓

Cache

↓

Action 1

↓

Action 2

↓

Action 3

The transformed data is reused.

Cache

df.cache()

Stores the DataFrame using Spark’s default storage level (MEMORY_AND_DISK for DataFrames in modern Spark).

Persist

from pyspark import StorageLevel

df.persist(StorageLevel.MEMORY_AND_DISK)

Persistence allows you to choose different storage levels.

Examples:

MEMORY_ONLY
MEMORY_AND_DISK
DISK_ONLY
MEMORY_AND_DISK_SER (RDDs)

Remove Cache

df.unpersist()

Frees memory when the cached data is no longer needed.

When to Use Cache

Use caching when:

The same DataFrame is reused multiple times.
Machine learning pipelines repeatedly access the same dataset.
Interactive analytics notebooks perform multiple actions on identical data.

Avoid caching data that is used only once, as it consumes cluster memory unnecessarily.

Cache vs Persist

Feature	Cache	Persist
Ease of Use	Simple	More Flexible
Storage Level	Default	User Chooses
Memory Only	Default behavior depends on API	Optional
Disk Support	Automatic if default storage level uses it	Configurable

Real-world Example

A fraud detection pipeline generated a cleaned transactions DataFrame that was used for:

Aggregations
Feature engineering
Machine learning
Reporting

Without caching, Spark recomputed the transformations for every stage.

After caching the cleaned DataFrame:

Execution time dropped from 48 minutes to 19 minutes.
CPU usage decreased significantly.
Cluster utilization improved.

Scenario-Based & DevOps

1. A pipeline processing 500 GB daily is running slowly. How would you troubleshoot it?

Interview Answer

When a production pipeline starts running slowly, I follow a structured troubleshooting approach instead of making random changes.

My objective is to identify whether the bottleneck is related to the source system, network, storage, Spark processing, SQL operations, or orchestration.

Step 1 – Identify the Bottleneck

First, I determine where the delay is occurring.

Example pipeline:

SQL Server
      │
Azure Data Factory
      │
Azure Data Lake
      │
Databricks
      │
Synapse
      │
Power BI

Questions I ask:

Is Copy Activity slow?
Is Spark transformation slow?
Is Synapse loading slow?
Is network throughput low?
Is storage throttling occurring?

Step 2 – Check Azure Data Factory Monitoring

Open

ADF → Monitor

Check

Pipeline Duration
Activity Duration
Retry Count
Failed Activities
Integration Runtime Performance

Example

Copy Activity

Expected

10 min

Current

42 min

Now I know the bottleneck.

Step 3 – Check Source Database

Sometimes the issue is not Azure.

Possible causes

Missing Indexes
Blocking Sessions
Slow SQL Queries
Table Scan

Instead of

SELECT *
FROM Sales

Use

SELECT CustomerID,
Amount
FROM Sales
WHERE ModifiedDate>=@Watermark

Step 4 – Verify Incremental Load

Never reload

500 GB

every day.

Instead

Load

Only Changed Records

Use

Watermark
CDC
Change Tracking

Step 5 – Optimize Copy Activity

Increase

Parallel Copies

Instead of

Use

if the source supports parallel reads.

Step 6 – Review Integration Runtime

Check

CPU
Memory
Concurrent Jobs

For Self-hosted IR

Scale VM

Example

4 Core

↓

16 Core

Step 7 – Optimize Databricks

Check

Spark UI

Look for

Shuffle
Data Skew
Spill
Long Stages

Possible improvements

✔ Broadcast Join

✔ AQE

✔ Cache

✔ Repartition

✔ Delta OPTIMIZE

Step 8 – Check Small Files

Instead of

5 Million Files

100 KB each

Create

300 MB Parquet Files

Step 9 – Optimize Synapse

Check

Distribution
Partitioning
Columnstore Index
Statistics
Materialized Views

Step 10 – Monitor Azure Metrics

Azure Monitor

Log Analytics

Storage Metrics

Network Metrics

Real-world Example

Project

Insurance Company

Problem

Daily ETL

500 GB

3 Hours

↓

7 Hours

Root Cause

Millions of

Small CSV Files

Solution

Converted to

Parquet

Enabled

Partitioning

Broadcast Join

AQE

Delta OPTIMIZE

Result

7 Hours

↓

1 Hour 45 Minutes

Interview Tip

Always explain

Identify

↓

Measure

↓

Optimize

↓

Validate

Don’t jump directly into tuning.

2. How would you migrate an on-premises ETL solution to Azure?

Interview Answer

Migration should be done in phases to minimize business disruption.

Architecture

On-Prem SQL Server
        │
Self-hosted IR
        │
Azure Data Factory
        │
Azure Data Lake
        │
Azure Databricks
        │
Azure Synapse
        │
Power BI

Step 1

Assessment

Understand

ETL Jobs
Data Sources
Dependencies
Scheduling
Data Volume

Step 2

Migration Strategy

Choose

Lift and Shift
Rebuild
Hybrid

Step 3

Move Data

ADF Copy Activity

↓

ADLS

Use

Incremental Load

Step 4

Transformation

Old SSIS

↓

Databricks

ADF Mapping Data Flow

Step 5

Warehouse

Load

↓

Azure Synapse

Step 6

Validation

Compare

Row Count

Checksum

Aggregates

Step 7

Security

Use

Managed Identity
Key Vault
Private Endpoint

Step 8

Monitoring

Azure Monitor

Log Analytics

Alerts

Real-world Example

Migrated

Oracle

↓

Azure

Result

ETL reduced from 8 hours to 2.5 hours.
Infrastructure costs reduced by approximately 40%.
Automatic scaling handled peak loads efficiently.

3. Explain your CI/CD process for Azure Data Engineering projects.

Interview Answer

CI/CD automates development, testing, and deployment across environments (Dev → QA → UAT → Production).

Architecture

Developer

↓

Azure DevOps

↓

Git Repository

↓

Build Pipeline

↓

Release Pipeline

↓

Dev

↓

QA

↓

UAT

↓

Production

Development

Developers work in:

ADF
Databricks
Synapse

using Git integration.

Source Control

Use

Git Branches
Pull Requests
Code Reviews

Continuous Integration

Pipeline validates:

ARM/Bicep templates
Databricks notebooks
SQL scripts
Unit tests
Static code analysis (if applicable)

Continuous Deployment

Deploy using Azure DevOps or GitHub Actions.

Deployment order:

Development

↓

QA

↓

Production

Parameterize:

Storage Accounts
SQL Servers
Key Vaults
Environment-specific settings

Secrets

Never store passwords in code.

Use:

Azure Key Vault

Rollback

Maintain versioned releases.

If deployment fails:

Restore previous release.

Real-world Example

Our team deployed:

120 ADF pipelines
45 Databricks notebooks
60 SQL scripts

using Azure DevOps. Automated deployments reduced manual release effort from several hours to less than 20 minutes and eliminated configuration drift across environments.

4. Describe a production issue you resolved in Azure Data Factory or Databricks.

Interview Answer (STAR Method)

Situation

A retail company’s nightly ETL pipeline suddenly began failing after processing about 70% of the data.

Task

Identify the cause, restore the pipeline before business hours, and prevent future failures.

Action

Opened ADF Monitor to identify the failing activity.
Found that a Databricks notebook was throwing OutOfMemory errors.
Checked the Spark UI.
Discovered severe data skew caused by one customer representing a very large percentage of transactions.
Implemented:
- Broadcast join for small dimension tables.
- Repartitioning on a better key.
- Adaptive Query Execution (AQE).
- Increased executor memory appropriately.
Added monitoring and alerts through Azure Monitor.
Configured retry policies in ADF.

Result

Pipeline completed successfully.
Processing time reduced from 5 hours to 2 hours.
No similar failures occurred over the following months.
SLA compliance improved.

Interview Tip

Always answer production issue questions using the STAR format:

Situation
Task
Action
Result

Interviewers appreciate measurable outcomes.

5. Design an end-to-end Azure Data Engineering solution for processing 10 TB of data daily.

Interview Answer

For processing 10 TB of data per day, I would design a scalable, secure, and fault-tolerant Azure Lakehouse architecture.

High-Level Architecture

        Source Systems
-----------------------------------------
SQL | SAP | Oracle | APIs | IoT | Kafka
-----------------------------------------
                │
        Azure Data Factory
                │
 Azure Data Lake Storage Gen2
         (Bronze Layer)
                │
        Azure Databricks
      Data Validation & ETL
                │
 Azure Data Lake Storage Gen2
         (Silver Layer)
                │
 Business Transformations
 Aggregations / Joins / KPIs
                │
 Azure Data Lake Storage Gen2
          (Gold Layer)
                │
 Azure Synapse Analytics
                │
      Power BI Dashboards

Step 1 – Data Ingestion

Use Azure Data Factory to ingest data from:

SQL Server
Oracle
SAP
REST APIs
FTP
Event streams (where applicable)

Use:

Incremental loading
Watermark columns
CDC for transactional systems

Step 2 – Data Storage

Store raw data in ADLS Gen2 Bronze.

Use:

Parquet or Delta format
Date-based partitioning
Compression (Snappy)

Step 3 – Data Processing

Use Azure Databricks.

Perform:

Data cleansing
Deduplication
Business validations
Joins
Aggregations

Optimize using:

Broadcast joins
AQE
Partition pruning
Caching
Delta OPTIMIZE

Step 4 – Curated Data

Write clean datasets to:

Silver Layer

Business-ready datasets to:

Gold Layer

Step 5 – Analytics

Load curated data into Azure Synapse Analytics.

Design:

Hash-distributed fact tables
Replicated dimension tables
Columnstore indexes
Date partitioning

Step 6 – Reporting

Connect Power BI to Synapse.

Use:

Incremental refresh
Materialized views
Result set caching

Step 7 – Security

Implement:

Azure AD authentication
Managed Identity
Azure Key Vault
RBAC
ACLs
Private Endpoints
Encryption at rest and in transit

Step 8 – Monitoring

Monitor using:

Azure Monitor
Log Analytics
ADF Monitor
Databricks Jobs
Spark UI
Synapse Monitor

Configure:

Retry policies
Email alerts
SLA dashboards

Step 9 – CI/CD

Use:

Azure DevOps or GitHub Actions
Git branching strategy
Infrastructure as Code (ARM/Bicep/Terraform)
Environment-specific parameterization
Automated deployment to Dev → QA → UAT → Production

Expected Business Outcome

With this architecture:

Process 10 TB/day using distributed Spark processing.
Scale compute independently of storage.
Reduce costs with auto-scaling and auto-termination.
Achieve reliable, secure, and high-performance analytics.
Support near real-time reporting with enterprise-grade governance and monitoring.