Question 1

How do you handle null values in PySpark?

Accepted Answer

PySpark provides several methods to handle null values depending on the use case: Best practices : Use dropna() when nulls indicate corrupted data Use fillna() for known default values (e.g., 0 for missing counts) Use mean/median imputation for ML feature columns Log null counts before and after treatment for data quality auditing

Question 2

What is AQE (Adaptive Query Execution) in Databricks?

Accepted Answer

Adaptive Query Execution (AQE) is a Spark 3.0+ optimization framework that re optimizes query plans at runtime using actual statistics collected during execution, rather than relying solely on static compile time estimates. Three main AQE features : 1. Dynamically coalescing shuffle partitions : After a shuffle, AQE merges small partitions into fewer, larger ones — reducing task overhead. Controlled by spark.sql.adaptive.coalescePartitions.enabled=true . 2. Dynamically switching join strategies : If a table turns out smaller than the broadcast threshold after filtering, AQE can upgrade a sort merge join to a broadcast hash join at runtime — eliminating a costly shuffle. 3. Dynamically handling data skew : AQE detects skewed partitions (one partition much larger than others) and splits them into smaller sub partitions, distributing the load more evenly. Enabling AQE (enabled by default in Databricks Runtime 7.3+): Interview tip : AQE is most impactful on queries with unpredictable data distributions or queries that join large tables where filter selectivity is hard to estimate at compile time.

Question 3

How do you implement error handling in ADF pipelines?

Accepted Answer

Azure Data Factory (ADF) provides multiple levels of error handling: 1. Activity level error handling : Each activity has On Success , On Failure , and On Completion dependency conditions Connect a failure branch to a notification activity (e.g., send email via Logic Apps or call a webhook) 2. Try Catch pattern : 3. Retry policies : Configure Retry count and Retry interval on each activity for transient failures (network timeouts, API throttling). 4. Pipeline level error handling : Use Execute Pipeline activity with Wait on completion = true to propagate errors Use If Condition activities to branch based on output variables 5. Logging and alerting : Enable Azure Monitor diagnostic logs for ADF — captures pipeline run details, activity errors, trigger events Set up Alert Rules in Azure Monitor on PipelineFailedRuns metric Write error details to an audit table in Azure SQL using a stored procedure activity Best practice : Always implement a Dead Letter pattern — failed records should be written to a separate error container/table with the error message, timestamp, and source for investigation.

Question 4

How do you track file names in ADF copy activity outputs?

Accepted Answer

In ADF, the Copy Activity exposes an output object with metadata about transferred files. You can capture file names using the activity output variable. Method 1: Use the activity output in a downstream activity Method 2: Enable logging in Copy Activity In the Copy Activity settings, enable "Logging settings" Point to an ADLS/Blob path for the log ADF writes a CSV log file listing all files copied, skipped, or failed with full paths Method 3: Use Get Metadata activity before Copy In the ForEach, @item().name gives the current file name, which can be passed as a pipeline variable or logged. Method 4: Parameterize the sink path with file name Use case : Tracking file names is critical for incremental load auditing — maintaining a watermark table that records which files have been processed to avoid re processing.

Question 5

Write a SQL query to calculate cumulative sum.

Accepted Answer

Cumulative sum (running total) is computed using the SUM() window function with an ordered frame: Key syntax : ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the standard frame for a running total. In most databases, ORDER BY alone in the window defaults to this frame, but being explicit improves readability. In PySpark :

Question 6

Explain the role of Azure Key Vault.

Accepted Answer

Azure Key Vault is a cloud service for securely storing and managing sensitive information — secrets, encryption keys, and certificates — centralizing credential management across Azure services. Key roles in data engineering : 1. Secret management : Store database passwords, API keys, storage account keys, connection strings. Services retrieve secrets at runtime rather than embedding them in code or config files. 2. Encryption key management : Manage Customer Managed Keys (CMK) for encrypting data at rest in Azure Storage, Azure SQL, Synapse Analytics. 3. Certificate management : Store SSL/TLS certificates used by applications and data pipelines. Integration with ADF : Create a Linked Service pointing to Key Vault Reference secrets using @Microsoft.KeyVault(SecretUri=...) syntax in other linked services ADF retrieves the secret at pipeline runtime using its Managed Identity Access control : Uses RBAC (role based) or access policies to grant specific identities (users, apps, managed identities) permission to get , list , set , or delete secrets. Best practices : Enable Soft Delete and Purge Protection to recover accidentally deleted secrets Use Managed Identities (not service principals with passwords) for Azure service access Rotate secrets regularly and audit access via Key Vault logs in Azure Monitor

Question 7

How do you automate ETL using Databricks Workflows?

Accepted Answer

Databricks Workflows (formerly Jobs) is the native orchestration layer for automating notebooks, Python scripts, Delta Live Tables pipelines, and dbt tasks. Creating a workflow : 1. Go to Workflows in Databricks UI → Create Job 2. Add tasks: Notebook, Python script, JAR, Spark Submit, SQL, dbt, or Delta Live Tables 3. Define dependencies between tasks (task A → task B creates a DAG) 4. Set a schedule (cron expression or every X minutes) 5. Configure cluster per task (job cluster for isolation, all purpose for dev) Key features : Task parameters : Pass values between tasks via dbutils.jobs.taskValues.set() and .get() Conditional execution : Run tasks based on upstream status (success/failure/all) Retries : Configure retry count and backoff interval per task Alerting : Email/webhook on success, failure, or timeout CI/CD integration : Use Databricks CLI or Terraform to define workflows as code and deploy via GitHub Actions or Azure DevOps pipelines.

Question 8

How do you set up disaster recovery for ADLS?

Accepted Answer

Azure Data Lake Storage (ADLS) Gen2 disaster recovery involves geo redundancy, failover, and backup strategies: 1. Redundancy options (choose at account creation): LRS (Locally Redundant Storage): 3 copies in one datacenter — no DR ZRS (Zone Redundant): 3 copies across availability zones in one region GRS (Geo Redundant): Async replication to secondary region, RPO ~15 min RA GRS (Read Access GRS): GRS + read access to secondary region endpoint GZRS / RA GZRS : Zone redundant primary + geo replicated secondary (highest durability) 2. Failover : In case of regional outage, initiate account failover in Azure Portal — promotes secondary to primary (customer initiated failover). Note: data replicated asynchronously, so some data loss is possible (RPO is not zero). 3. Soft delete : Enable soft delete for blobs and containers (retention 7–365 days) to recover accidental deletions. 4. Versioning : Enable blob versioning to maintain previous versions of files. 5. Cross region backup : Use ADF or Azure Data Share to periodically copy critical data to another storage account in a different region. RPO/RTO targets : For mission critical pipelines, use RA GRS with automated failover scripts and test DR drills quarterly.

EY Interview Questions