Q: How do you create a rank column using Window functions?

Window functions in PySpark allow you to compute rankings over a partition of rows without collapsing the DataFrame. There are three ranking functions, each behaving differently with ties. Setup: 1. RANK() — Gaps after ties 2. DENSE RANK() — No gaps after ties 3. ROW NUMBER() — Unique sequential, ties broken arbitrarily Common use case — Keep top N records per group: Equivalent SQL:

Q: What is the binary copy method in ADF?

Binary copy in Azure Data Factory (ADF) is a Copy Activity mode that transfers files as is without any parsing, deserialization, or schema translation. The data is treated as a raw byte stream from source to sink. Key characteristics: No data transformation or format conversion Preserves the exact original file (including metadata like timestamps, permissions where supported) Fastest copy mode since ADF doesn't need to parse file content Supports any file format (CSV, Parquet, JSON, images, ZIP, binaries) Ideal for file migration and staging scenarios When to use binary copy: Migrating files from on premises to Azure Blob / ADLS without transformation Archiving raw files exactly as received from source systems Copying files between storage accounts (e.g., ADLS → Azure Blob) When you want to preserve the original file and transform it in a later step Configuration in ADF: 1. In Copy Activity, set the source and sink datasets to use Binary format 2. Both source and sink linked services must be file based (Blob, ADLS, SFTP, FTP, HTTP, etc.) 3. Enable Preserve option to retain file metadata if supported by the connector Example use case: Then a separate Databricks notebook or Data Flow parses the raw file in the bronze → silver transformation step. Limitations: Cannot be used when source/sink is a relational database (SQL, Cosmos DB) Cannot apply column mappings, filtering, or transformations Wildcard file patterns and partition reading are supported Binary copy is a core pattern in medallion architecture where raw data is landed unmodified before any transformation.

Q: How do you monitor and optimize Azure Synapse performance?

Azure Synapse Analytics performance monitoring and optimization spans both the Dedicated SQL Pool and Spark Pool layers. Monitoring Tools: 1. Synapse Studio Monitor Hub Pipeline runs, SQL requests, Spark applications, data flow activities View active queries, execution time, resource utilization in real time 2. Dynamic Management Views (DMVs) for SQL Pool 3. Azure Monitor + Log Analytics — stream Synapse diagnostic logs for historical analysis Optimization Strategies: Distribution: Use HASH distribution on high cardinality join/group by columns for large fact tables Use REPLICATE for small dimension tables (< 2GB) Avoid ROUND ROBIN for tables frequently joined or aggregated Indexing: Default Clustered Columnstore Index (CCI) is optimal for analytical queries on large tables Use Heap for staging/temp tables with frequent inserts Rebuild fragmented CCI: ALTER INDEX ALL ON table name REBUILD Statistics: Workload Management: Define workload groups and classifiers to allocate concurrency and memory to user groups Pause the dedicated pool when not in use to save cost Scale DWU: Scale up Data Warehouse Units during heavy batch loads, scale down during off hours

Q: Write Python code to identify duplicates and count them.

Here are multiple approaches to identify and count duplicates in Python: 1. Using collections.Counter on a list 2. Identifying duplicate rows in a list of dictionaries 3. Using pandas for tabular data (most common in data engineering) 4. Using set to find unique duplicates efficiently

Q: Key features of Azure DevOps.

Azure DevOps is Microsoft's integrated platform for end to end software and data pipeline delivery. It consists of five core services: 1. Azure Boards Agile project management: Kanban boards, backlogs, sprints, work items Track bugs, user stories, tasks, epics Integration with GitHub commits and pull requests 2. Azure Repos Hosted Git repositories (or TFVC for legacy) Branch policies: require PR reviews, status checks, linked work items Used to version control ADF pipelines, Databricks notebooks, dbt models, SQL scripts 3. Azure Pipelines CI/CD automation for build, test, and deployment YAML based pipeline definitions stored in the repo Supports multi stage pipelines: dev → staging → production Pre built tasks for ADF deployment, Databricks, Terraform, Docker Example data engineering use case: on PR merge, deploy ADF ARM template to production 4. Azure Artifacts Package management: host and share NuGet, PyPI, npm, Maven packages Create private PyPI feeds for internal Python libraries used in Spark/Databricks jobs 5. Azure Test Plans Manual and automated test management Load testing integration Data Engineering Specific Features: ADF CI/CD integration : Export ADF as ARM templates from Git, deploy via release pipeline Databricks CI/CD : Deploy notebooks, wheels, and job configurations Environment gates : Require approvals before promoting to production Service connections : Securely connect to Azure subscriptions, GitHub, Docker registries Variable groups + Key Vault integration : Store environment specific secrets securely Key benefit: Azure DevOps enables infrastructure as code and data pipeline versioning, enabling rollback, audit trails, and collaborative development.

Q: How do you handle schema drift in ADF?

Schema drift occurs when source data structure changes unexpectedly — new columns appear, columns are removed, or data types change. ADF's Mapping Data Flows provides built in schema drift handling. 1. Enable Allow Schema Drift on Source In the source transformation of a Mapping Data Flow, enable Allow Schema Drift ADF will accept any incoming columns without failing, even those not defined in the dataset schema projection 2. Enable Auto Mapping on Sink In the sink transformation, enable Auto Mapping (or use Allow Schema Drift on sink too) New columns from the source will be automatically passed through to the sink 3. Rule Based Column Mapping (flexible patterns) Instead of explicit column mappings, use rule based mappings with patterns: matchesPattern('. ') — maps all columns regardless of name type == 'string' — maps all string columns Combine with transformations: toString($$) to cast drifted columns 4. Infer Drifted Column Types Enable this option on the source to have ADF automatically infer data types for schema drifted columns Without this, drifted columns are treated as strings 5. Handling Schema Drift in Copy Activity Use Parquet, JSON, or Avro sinks (schema flexible formats) instead of SQL sinks If writing to SQL, use a pre copy script to run ALTER TABLE dynamically 6. Schema Change Detection Use the Get Metadata activity to compare current schema against expected schema Fail the pipeline or send alerts via Logic Apps/email when schema changes are detected Log schema differences to a monitoring table for review Best practice: Accept and land all columns in the raw/bronze layer (schema drift ON), then enforce strict schemas at the silver/gold layer with explicit transformations and validation.

Q: Explain denormalization and its use cases.

Denormalization is the process of intentionally introducing redundancy into a database schema by combining tables (merging normalized tables) or duplicating data, trading storage efficiency for query performance. Contrast with Normalization: Normalized (3NF): Data stored in separate related tables, minimal redundancy, complex JOINs needed to query Denormalized : Data combined into fewer, wider tables, some redundancy, faster reads with fewer JOINs Techniques: 1. Pre joining tables — merge dimension and fact tables 2. Storing aggregates — pre compute and store SUM, COUNT to avoid expensive aggregations at query time 3. Duplicating columns — repeat frequently accessed columns in child tables to avoid lookups Use cases in data engineering: Data Warehouses / Star Schema : Dimension tables are pre joined into wide fact tables in analytics layers OLAP / Reporting : BI tools like Tableau/Power BI perform better on wide flat tables Delta Lake Gold Layer : Fully denormalized tables optimized for specific reporting queries NoSQL / Document Stores : MongoDB/DynamoDB embed related data to avoid expensive lookups Streaming Aggregations : Pre aggregate event streams to reduce downstream query load Trade offs: Aspect Normalized Denormalized Storage Efficient More space Write performance Better (one update) Slower (update redundant copies) Read performance Slower (JOINs) Faster (pre joined) Data consistency Easier Harder (redundancy drift) In practice: Use normalization for OLTP systems (transactions, consistency critical) and denormalization for OLAP/analytical systems (read heavy, aggregation heavy).

Question 1

Explain broadcast join in PySpark.

Accepted Answer

A broadcast join in PySpark is a join optimization where the smaller DataFrame is broadcast (copied in full) to every executor node, eliminating the need to shuffle the larger DataFrame across the network. How it works: Spark serializes the small DataFrame and sends a copy to every executor Each executor performs the join locally with its partition of the large DataFrame No data movement for the large DataFrame → massive performance improvement When to use: One table is significantly smaller than the other (typically < 10MB by default, configurable) The small table fits comfortably in executor memory Common scenarios: joining a large fact table with a small dimension/lookup table Usage: Auto broadcast threshold: Benefits vs. Sort Merge Join: No shuffle phase (no disk I/O or network transfer for large table) Significantly faster for small large table joins Reduces stage count in the execution plan Limitation: If the broadcast table is too large, it causes OOM on executors. Monitor with Spark UI's SQL tab to verify BroadcastHashJoin is used.

Question 2

How do you create a rank column using Window functions?

Accepted Answer

Window functions in PySpark allow you to compute rankings over a partition of rows without collapsing the DataFrame. There are three ranking functions, each behaving differently with ties. Setup: 1. RANK() — Gaps after ties 2. DENSE RANK() — No gaps after ties 3. ROW NUMBER() — Unique sequential, ties broken arbitrarily Common use case — Keep top N records per group: Equivalent SQL:

Question 3

What is the binary copy method in ADF?

Accepted Answer

Binary copy in Azure Data Factory (ADF) is a Copy Activity mode that transfers files as is without any parsing, deserialization, or schema translation. The data is treated as a raw byte stream from source to sink. Key characteristics: No data transformation or format conversion Preserves the exact original file (including metadata like timestamps, permissions where supported) Fastest copy mode since ADF doesn't need to parse file content Supports any file format (CSV, Parquet, JSON, images, ZIP, binaries) Ideal for file migration and staging scenarios When to use binary copy: Migrating files from on premises to Azure Blob / ADLS without transformation Archiving raw files exactly as received from source systems Copying files between storage accounts (e.g., ADLS → Azure Blob) When you want to preserve the original file and transform it in a later step Configuration in ADF: 1. In Copy Activity, set the source and sink datasets to use Binary format 2. Both source and sink linked services must be file based (Blob, ADLS, SFTP, FTP, HTTP, etc.) 3. Enable Preserve option to retain file metadata if supported by the connector Example use case: Then a separate Databricks notebook or Data Flow parses the raw file in the bronze → silver transformation step. Limitations: Cannot be used when source/sink is a relational database (SQL, Cosmos DB) Cannot apply column mappings, filtering, or transformations Wildcard file patterns and partition reading are supported Binary copy is a core pattern in medallion architecture where raw data is landed unmodified before any transformation.

Question 4

How do you monitor and optimize Azure Synapse performance?

Accepted Answer

Azure Synapse Analytics performance monitoring and optimization spans both the Dedicated SQL Pool and Spark Pool layers. Monitoring Tools: 1. Synapse Studio Monitor Hub Pipeline runs, SQL requests, Spark applications, data flow activities View active queries, execution time, resource utilization in real time 2. Dynamic Management Views (DMVs) for SQL Pool 3. Azure Monitor + Log Analytics — stream Synapse diagnostic logs for historical analysis Optimization Strategies: Distribution: Use HASH distribution on high cardinality join/group by columns for large fact tables Use REPLICATE for small dimension tables (< 2GB) Avoid ROUND ROBIN for tables frequently joined or aggregated Indexing: Default Clustered Columnstore Index (CCI) is optimal for analytical queries on large tables Use Heap for staging/temp tables with frequent inserts Rebuild fragmented CCI: ALTER INDEX ALL ON table name REBUILD Statistics: Workload Management: Define workload groups and classifiers to allocate concurrency and memory to user groups Pause the dedicated pool when not in use to save cost Scale DWU: Scale up Data Warehouse Units during heavy batch loads, scale down during off hours

Question 5

Write Python code to identify duplicates and count them.

Accepted Answer

Here are multiple approaches to identify and count duplicates in Python: 1. Using collections.Counter on a list 2. Identifying duplicate rows in a list of dictionaries 3. Using pandas for tabular data (most common in data engineering) 4. Using set to find unique duplicates efficiently

Question 6

Key features of Azure DevOps.

Accepted Answer

Azure DevOps is Microsoft's integrated platform for end to end software and data pipeline delivery. It consists of five core services: 1. Azure Boards Agile project management: Kanban boards, backlogs, sprints, work items Track bugs, user stories, tasks, epics Integration with GitHub commits and pull requests 2. Azure Repos Hosted Git repositories (or TFVC for legacy) Branch policies: require PR reviews, status checks, linked work items Used to version control ADF pipelines, Databricks notebooks, dbt models, SQL scripts 3. Azure Pipelines CI/CD automation for build, test, and deployment YAML based pipeline definitions stored in the repo Supports multi stage pipelines: dev → staging → production Pre built tasks for ADF deployment, Databricks, Terraform, Docker Example data engineering use case: on PR merge, deploy ADF ARM template to production 4. Azure Artifacts Package management: host and share NuGet, PyPI, npm, Maven packages Create private PyPI feeds for internal Python libraries used in Spark/Databricks jobs 5. Azure Test Plans Manual and automated test management Load testing integration Data Engineering Specific Features: ADF CI/CD integration : Export ADF as ARM templates from Git, deploy via release pipeline Databricks CI/CD : Deploy notebooks, wheels, and job configurations Environment gates : Require approvals before promoting to production Service connections : Securely connect to Azure subscriptions, GitHub, Docker registries Variable groups + Key Vault integration : Store environment specific secrets securely Key benefit: Azure DevOps enables infrastructure as code and data pipeline versioning, enabling rollback, audit trails, and collaborative development.

Question 7

How do you handle schema drift in ADF?

Accepted Answer

Schema drift occurs when source data structure changes unexpectedly — new columns appear, columns are removed, or data types change. ADF's Mapping Data Flows provides built in schema drift handling. 1. Enable Allow Schema Drift on Source In the source transformation of a Mapping Data Flow, enable Allow Schema Drift ADF will accept any incoming columns without failing, even those not defined in the dataset schema projection 2. Enable Auto Mapping on Sink In the sink transformation, enable Auto Mapping (or use Allow Schema Drift on sink too) New columns from the source will be automatically passed through to the sink 3. Rule Based Column Mapping (flexible patterns) Instead of explicit column mappings, use rule based mappings with patterns: matchesPattern('. ') — maps all columns regardless of name type == 'string' — maps all string columns Combine with transformations: toString($$) to cast drifted columns 4. Infer Drifted Column Types Enable this option on the source to have ADF automatically infer data types for schema drifted columns Without this, drifted columns are treated as strings 5. Handling Schema Drift in Copy Activity Use Parquet, JSON, or Avro sinks (schema flexible formats) instead of SQL sinks If writing to SQL, use a pre copy script to run ALTER TABLE dynamically 6. Schema Change Detection Use the Get Metadata activity to compare current schema against expected schema Fail the pipeline or send alerts via Logic Apps/email when schema changes are detected Log schema differences to a monitoring table for review Best practice: Accept and land all columns in the raw/bronze layer (schema drift ON), then enforce strict schemas at the silver/gold layer with explicit transformations and validation.

Question 8

Explain denormalization and its use cases.

Accepted Answer

Denormalization is the process of intentionally introducing redundancy into a database schema by combining tables (merging normalized tables) or duplicating data, trading storage efficiency for query performance. Contrast with Normalization: Normalized (3NF): Data stored in separate related tables, minimal redundancy, complex JOINs needed to query Denormalized : Data combined into fewer, wider tables, some redundancy, faster reads with fewer JOINs Techniques: 1. Pre joining tables — merge dimension and fact tables 2. Storing aggregates — pre compute and store SUM, COUNT to avoid expensive aggregations at query time 3. Duplicating columns — repeat frequently accessed columns in child tables to avoid lookups Use cases in data engineering: Data Warehouses / Star Schema : Dimension tables are pre joined into wide fact tables in analytics layers OLAP / Reporting : BI tools like Tableau/Power BI perform better on wide flat tables Delta Lake Gold Layer : Fully denormalized tables optimized for specific reporting queries NoSQL / Document Stores : MongoDB/DynamoDB embed related data to avoid expensive lookups Streaming Aggregations : Pre aggregate event streams to reduce downstream query load Trade offs: Aspect Normalized Denormalized Storage Efficient More space Write performance Better (one update) Slower (update redundant copies) Read performance Slower (JOINs) Faster (pre joined) Data consistency Easier Harder (redundancy drift) In practice: Use normalization for OLTP systems (transactions, consistency critical) and denormalization for OLAP/analytical systems (read heavy, aggregation heavy).

Persistent Interview Questions