Azure Data Engineer Interview Questions

Quick answer

Most Azure data engineer interview questions come from six areas: Azure Data Lake Storage Gen2, Azure Data Factory, Azure Databricks with PySpark, SQL and data modeling, live coding, and scenario-based design. Freshers get more concept questions; engineers with 3+ years get more scenario, optimization and managerial questions.

Key takeaways

1. Expect a mix: concepts (what is an integration runtime?), live coding (write the SQL or PySpark) and scenarios (how would you load only new data?).
2. SQL is tested in almost every round, especially window functions, deduplication and top-N queries.
3. Databricks and PySpark questions decide most mid-level interviews.
4. Scenario answers win offers: explain the trade-off, not just the tool.
5. Senior roles add a managerial round on design, incidents and leading a team.
6. Microsoft Fabric questions are growing in 2026, so know OneLake, Lakehouse and Warehouse basics.

1. What does an Azure data engineer do?

Short answer: An Azure data engineer builds and runs the pipelines that move data from source systems into a cloud data lake or warehouse, cleans and models it, and makes it reliable for reports, analytics and AI.

Interviewers often open with "walk me through your day". A typical answer covers:

• Ingest: copy data from databases, APIs and files into ADLS Gen2 using Azure Data Factory or Fabric pipelines.
• Transform: clean, join and aggregate data in Azure Databricks with PySpark and SQL, usually in Bronze, Silver and Gold layers.
• Model and serve: build fact and dimension tables for Power BI, Synapse or Fabric Warehouse.
• Secure and govern: manage access with Microsoft Entra ID, managed identities, Key Vault and Unity Catalog.
• Monitor and fix: watch pipeline runs, handle failures, tune slow jobs and control cost.

2. Azure fundamentals and ADLS Gen2 interview questions

Short answer: Interviewers check that you know where data lives in Azure, how it is organized and how it is secured.

Q1. What is Azure Data Lake Storage Gen2?

Answer: ADLS Gen2 is Azure Blob Storage with the hierarchical namespace turned on. It is the standard storage layer for Azure data lakes and lakehouses.

How it works:

• The hierarchical namespace gives real folders, so renaming or deleting a folder is one fast operation.
• It supports Azure RBAC and POSIX-style ACLs on folders and files.
• Spark, Databricks, Synapse and Fabric read it through the abfss:// driver.

Example: abfss://raw@mystorage.dfs.core.windows.net/sales/2026/10/20/orders.parquet

Interview tip: Say that ADLS Gen2 holds your Bronze, Silver and Gold layers, and that it is far cheaper than a database for large raw data.

Q2. What is the difference between Azure Blob Storage and ADLS Gen2?

Answer: Both are object storage on the same platform, but ADLS Gen2 adds features for analytics.

How it works:

• Blob Storage: flat namespace; "folders" are only name prefixes. Good for images, backups and app files.
• ADLS Gen2: hierarchical namespace, folder-level ACLs and atomic directory operations. Good for analytics.
• Both share the same tiers, redundancy options and pricing model.

Example: Renaming a folder with 1 million files is one metadata operation in ADLS Gen2, but 1 million copy-and-delete calls in flat Blob Storage.

Interview tip: Mention that you enable the hierarchical namespace when you create the storage account.

Q3. How do you secure data in ADLS Gen2?

Answer: Use layers of security: identity, permissions, network and secrets.

How it works:

1. Azure RBAC for broad access, such as Storage Blob Data Reader on a container.
2. ACLs for fine-grained access on folders and files.
3. Managed identities so ADF and Databricks need no passwords.
4. Private endpoints and firewall rules to block public network access.
5. Azure Key Vault for any secrets that remain.

Example: ADF's managed identity gets Storage Blob Data Contributor on the raw container only.

Interview tip: Mention least privilege: each service gets only the access it needs.

Q4. What are storage access tiers?

Answer: Tiers balance storage cost against access cost: Hot, Cool, Cold and Archive.

How it works:

• Hot: highest storage cost, lowest read cost; for data used every day.
• Cool and Cold: cheaper storage for data read occasionally, with minimum retention periods.
• Archive: cheapest storage, but the data is offline and must be rehydrated (which can take hours) before reading.
• Lifecycle management rules move data between tiers automatically by age.

Example: Keep the last 90 days of raw files in Hot, move older files to Cool, and archive files older than 2 years.

Interview tip: Link tiers to cost optimization; interviewers like candidates who think about cost.

Q5. What is a managed identity and why use it?

Answer: A managed identity is an identity in Microsoft Entra ID that Azure creates and manages for a resource such as Data Factory or Databricks.

How it works:

• System-assigned: tied to one resource and deleted with it.
• User-assigned: a standalone identity that you can attach to several resources.
• You grant the identity RBAC roles, and the service gets tokens automatically, so there are no passwords or keys to store or rotate.

Example: ADF's system-assigned identity gets read access to Key Vault and write access to ADLS.

Interview tip: Say you prefer managed identities over account keys and SAS tokens because they remove secrets from code.

Q6. What redundancy options does Azure Storage offer?

Answer: Redundancy decides how many copies of your data exist and where.

How it works:

• LRS: three copies in one datacenter.
• ZRS: three copies across availability zones in one region.
• GRS / RA-GRS: LRS plus a copy in the paired region; RA-GRS also allows reads from the secondary.
• GZRS / RA-GZRS: ZRS plus geo-replication to the paired region.

Example: Use ZRS for a production lake and GZRS when the business needs regional disaster recovery.

Interview tip: Connect the choice to RPO and RTO requirements and to cost.

Q7. What is a SAS token?

Answer: A shared access signature is a signed URL that gives limited, time-bound access to storage without sharing the account key.

How it works:

• You choose the permissions (read, write, list), the resource and an expiry time.
• User delegation SAS is signed with Microsoft Entra credentials and is the most secure type.
• Service and account SAS are signed with the account key, so they are harder to revoke.

Example: Give a vendor a read-only SAS for one container that expires in 24 hours.

Interview tip: Say you use short expiry times and prefer managed identities for internal services.

Q8. How do data pipelines use Azure Key Vault?

Answer: Key Vault stores secrets, keys and certificates centrally, so pipelines never hard-code passwords.

How it works:

• ADF linked services reference a Key Vault secret instead of a password.
• Databricks uses Key Vault-backed secret scopes, read with dbutils.secrets.get().
• Access is granted with RBAC, and every secret access is logged.

Example: The SQL Server password is stored as secret sql-src-password, and the ADF linked service points to it.

Interview tip: Mention secret rotation: update the secret once in Key Vault and every pipeline picks it up.

Q9. What is the difference between a service principal and a managed identity?

Answer: Both are identities that applications use, but you manage a service principal's credentials, while Azure manages a managed identity's.

How it works:

• Service principal: an app registration with a client secret or certificate that you must store and rotate; it works from anywhere, including outside Azure.
• Managed identity: tied to an Azure resource, with no credentials to handle; it works only from that resource.

Example: A GitHub Actions deployment uses a service principal (or federated credentials); ADF inside Azure uses its managed identity.

Interview tip: Say you choose managed identities whenever the workload runs inside Azure.

Q10. What is a resource group?

Answer: A logical container for Azure resources that share a lifecycle, permissions and billing tags.

How it works:

• You can deploy, secure and delete all resources in a group together.
• RBAC roles and Azure Policy can be applied at the resource group level.
• Tags on resource groups help split costs by project or environment.

Example: rg-dataplatform-prod holds the production ADLS account, ADF, Databricks workspace and Key Vault.

Interview tip: Mention separate resource groups (or subscriptions) for dev, test and prod.

Q11. Which file formats are common in a data lake, and why is Parquet preferred?

Answer: Common formats are CSV, JSON, Avro, Parquet and Delta. Parquet is preferred for analytics because it is columnar and compressed.

How it works:

• Columnar storage: queries read only the columns they need.
• Compression and encoding: files are much smaller than CSV.
• Statistics: min and max values let engines skip data that does not match a filter.
• Delta adds a transaction log on top of Parquet for ACID transactions.

Example: A 10 GB CSV file often shrinks to around 1–2 GB as Parquet, and a query that needs 3 of 50 columns reads only those 3.

Interview tip: Say raw data may land as CSV or JSON, but you convert it to Delta or Parquet in Silver.

Q12. How do you organize folders in a data lake?

Answer: Organize by layer, then by source and entity, then by date, with consistent naming.

How it works:

1. Separate containers per layer: bronze, silver, gold.
2. Folders per source system and table: bronze/sap/customers/.
3. Date partitions for raw files: year=2026/month=10/day=20/.
4. Separate containers make access control and lifecycle rules simpler.

Example: bronze/salesforce/accounts/year=2026/month=10/day=20/accounts_0800.json

Interview tip: Mention that Delta tables in Silver and Gold are usually managed through Unity Catalog instead of raw folder paths.

Q13. What is a private endpoint?

Answer: A private endpoint gives an Azure service a private IP address inside your virtual network.

How it works:

• Traffic to the service stays on the Microsoft backbone network, not the public internet.
• Private DNS zones resolve the service name to the private IP.
• You can then disable public network access on the storage account.

Example: ADLS, Key Vault and Azure SQL each get a private endpoint in the data platform VNet, and Databricks is deployed with VNet injection.

Interview tip: Mention that ADF needs a managed virtual network or self-hosted runtime to reach private endpoints.

Q14. What is Azure Event Hubs used for?

Answer: Event Hubs is a big-data streaming ingestion service that takes in large volumes of events from apps, devices and logs.

How it works:

• Producers send events to an event hub, which is split into partitions for parallel reads.
• Consumer groups let several applications read the same stream independently.
• It supports the Kafka protocol, so Kafka clients work without code changes.
• Capture can write events to ADLS automatically.

Example: Website clickstream events go to Event Hubs, and Databricks Structured Streaming writes them to a Bronze Delta table.

Interview tip: Compare it with Kafka and mention partitions and consumer groups.

Q15. What is the difference between Azure SQL Database and a data lake?

Answer: Azure SQL Database is a managed relational database; a data lake is low-cost file storage for any data at large scale.

How it works:

• Azure SQL Database: fixed schemas, transactions, indexes; best for applications and smaller analytics.
• Data lake: files in any format (CSV, JSON, Parquet, Delta), cheap storage, processed by Spark or SQL engines.
• A lakehouse adds Delta tables on the lake to get database-like reliability.

Example: Orders are captured in Azure SQL Database, then copied daily into the lake for history and analytics.

Interview tip: Say you use both: SQL for operational data and the lake for analytics at scale.

3. Azure Data Factory interview questions

Short answer: ADF questions test pipelines, activities, integration runtimes, triggers, incremental loading, parameters and CI/CD. For a deeper set, see our 140 Azure Data Factory interview questions with scenarios.

Q16. What are the main components of Azure Data Factory?

Answer: ADF has six building blocks that work together to move and transform data.

How it works:

• Pipeline: a logical group of activities, such as "load sales".
• Activity: one step, such as Copy, Lookup or Databricks Notebook.
• Dataset: points to the data an activity uses, such as a table or folder.
• Linked service: the connection details for a data store or compute.
• Integration runtime: the compute that runs the activities.
• Trigger: decides when the pipeline runs.

Example: A trigger at 1 AM runs a pipeline whose Copy activity reads a SQL dataset (via a SQL linked service) and writes a Parquet dataset in ADLS.

Interview tip: Explain how the pieces connect, not just their names.

Q17. What are the types of integration runtime?

Answer: The integration runtime (IR) is ADF's compute. There are three types.

How it works:

• Azure IR: fully managed and serverless; connects to cloud data stores and runs mapping data flows.
• Self-hosted IR: software you install on a VM or on-premises machine; reaches on-premises or private-network sources.
• Azure-SSIS IR: a managed cluster that runs existing SSIS packages.

Example: Use a self-hosted IR to copy from an on-premises Oracle database into ADLS.

Interview tip: Mention high availability: install the self-hosted IR on two or more nodes.

Q18. What is the difference between a linked service and a dataset?

Answer: A linked service is how to connect; a dataset is what data to use through that connection.

How it works:

• Linked service: server name, account, authentication method, and the IR to use.
• Dataset: a specific table, file or folder plus its format and schema.
• One linked service can serve many datasets.

Example: One Azure SQL linked service, with datasets for dbo.Customers and dbo.Orders.

Interview tip: Mention parameterized datasets, so one dataset can point to any table.

Q19. What trigger types does ADF support?

Answer: ADF has four trigger types for different scheduling needs.

How it works:

• Schedule trigger: runs on a calendar schedule, such as daily at 2 AM.
• Tumbling window trigger: fixed, non-overlapping time windows with backfill, retries and dependencies.
• Storage event trigger: runs when a blob is created or deleted.
• Custom event trigger: runs on Event Grid custom events.

Example: A storage event trigger starts the pipeline as soon as sales_20261020.csv lands in the raw container.

Interview tip: Say you choose event triggers when file arrival times are unpredictable.

Q20. How do you implement an incremental load in ADF?

Answer: Load only new or changed rows using a watermark column, such as the last modified date.

How it works:

1. Store the last watermark per table in a control table.
2. A Lookup activity reads the old watermark.
3. Another Lookup gets the current maximum value from the source.
4. The Copy activity loads rows between the two values.
5. A Stored Procedure or Script activity updates the watermark.

Example: SELECT * FROM Orders WHERE ModifiedDate > '@{activity('OldWatermark').output.firstRow.wm}'

Interview tip: Mention change data capture as the alternative when the source supports it, and explain how you handle deletes.

Q21. What is the difference between the Lookup and Get Metadata activities?

Answer: Lookup reads data; Get Metadata reads information about files and folders.

How it works:

• Lookup: runs a query or reads a file and returns rows (first row only, or up to 5,000 rows).
• Get Metadata: returns properties such as childItems, size, lastModified or exists.
• Both outputs feed later activities such as If Condition or ForEach.

Example: Get Metadata lists the files in a folder, then ForEach copies each file.

Interview tip: Mention the Lookup row and size limits, and that large lists should come from a control table instead.

Q22. How do you handle failures in an ADF pipeline?

Answer: Combine retries, failure paths, logging and alerts.

How it works:

1. Set retry count and interval on activities for temporary errors.
2. Use dependency conditions (Succeeded, Failed, Completed, Skipped) to run error-handling steps.
3. Log run ID, error message and row counts to a logging table.
4. Send alerts through Azure Monitor or a Logic App to email or Teams.

Example: If the Copy activity fails, a Failed path calls a Logic App that posts the error to the team's Teams channel.

Interview tip: Mention that you design pipelines to be safely re-runnable (idempotent).

Q23. What are mapping data flows?

Answer: Mapping data flows are visual, code-free transformations in ADF that run on managed Spark clusters.

How it works:

• You build transformations such as join, aggregate, derived column, pivot and alter row in a visual canvas.
• ADF turns the flow into Spark code and runs it on an Azure IR.
• They support schema drift and debug mode with data previews.

Example: A data flow joins customers and orders, calculates total spend, and upserts the result into a SQL table.

Interview tip: Say you use data flows for simple, low-code logic and Databricks for complex or large-scale transformations.

Q24. What does the Copy activity do?

Answer: It copies data from a source to a sink, with optional format conversion and column mapping.

How it works:

• Supports many connectors (databases, files, SaaS apps, REST APIs).
• Converts formats, such as CSV to Parquet.
• Offers parallel copies, staging, fault tolerance and logging.

Example: Copy a SQL Server table to ADLS as Parquet, partitioned by date.

Interview tip: Mention the performance settings: DIUs, parallel copies and source partitioning.

Q25. How does the ForEach activity work?

Answer: ForEach loops over an array and runs inner activities for each item.

How it works:

• The input is usually the output of a Lookup or Get Metadata activity.
• Inside the loop, @item() gives the current element.
• It runs in parallel by default (batch count up to 50), or sequentially if you choose.

Example: Lookup returns 40 table names; ForEach runs a parameterized Copy for each table with batch count 10.

Interview tip: Mention that a ForEach cannot contain another ForEach, so you call a child pipeline instead.

Q26. What is the difference between pipeline parameters and variables?

Answer: Parameters are inputs fixed for a run; variables can change during the run.

How it works:

• Parameters: passed by a trigger or parent pipeline, read with @pipeline().parameters.name, read-only.
• Variables: created in the pipeline and changed with Set Variable and Append Variable.
• Variables inside a parallel ForEach are not safe to update, because iterations share them.

Example: Parameter loadDate is passed by the trigger; variable fileList collects processed file names.

Interview tip: Mention the parallel ForEach issue; it shows real experience.

Q27. What are global parameters?

Answer: Global parameters are constants defined once at the factory level and used by every pipeline.

How it works:

• Defined in the Manage hub.
• Referenced with @pipeline().globalParameters.name.
• Overridden per environment during CI/CD deployment.

Example: A global parameter environment is dev in development and prod in production.

Interview tip: Say global parameters avoid hard-coding environment values in each pipeline.

Q28. What is the Execute Pipeline activity?

Answer: It runs one pipeline (the child) from another (the parent).

How it works:

• The parent passes parameters to the child.
• Wait on completion decides whether the parent waits for the child to finish.
• It is used to build modular, reusable pipelines and to nest loops.

Example: A master pipeline runs "ingest", then "transform", then "publish" child pipelines in order.

Interview tip: Mention that this design makes reruns easier: you rerun only the failed child.

Q29. How do ADF expressions work?

Answer: Expressions add dynamic values to pipelines using the @ syntax.

How it works:

• System variables: @pipeline().RunId, @trigger().startTime.
• Functions: @concat(), @formatDateTime(), @utcNow(), @addDays().
• Activity outputs: @activity('Lookup1').output.firstRow.col.
• String interpolation: @{...} inside text.

Example: @concat('raw/sales/', formatDateTime(utcNow(), 'yyyy/MM/dd'), '/') builds a dated folder path.

Interview tip: Write one expression from memory in the interview; it proves hands-on experience.

Q30. How do you build a metadata-driven pipeline?

Answer: Store what to load in a control table, and let one generic pipeline read it and load everything.

How it works:

1. A control table lists source, target, load type and watermark for each table.
2. A Lookup reads the active rows.
3. A ForEach loops over them.
4. A parameterized Copy (or child pipeline) loads each table.
5. Results and new watermarks are written back to the control table.

Example: Adding a new table to the load means inserting one row in the control table, with no pipeline change.

Interview tip: This is one of the most valued answers for 3+ years roles; explain how you log and rerun failures.

Q31. What is the difference between a schedule trigger and a tumbling window trigger?

Answer: A schedule trigger fires at clock times; a tumbling window trigger processes fixed time windows with state.

How it works:

• Schedule: many-to-many with pipelines, no memory of past runs, no backfill.
• Tumbling window: one pipeline per trigger, each run gets windowStart and windowEnd, supports backfill, retries, concurrency and dependencies on other tumbling windows.

Example: An hourly tumbling window trigger backfills the last 7 days by creating 168 window runs.

Interview tip: Mention that tumbling windows are best for time-sliced loads that must not miss a period.

Q32. How does ADF connect to an on-premises SQL Server?

Answer: Through a self-hosted integration runtime installed inside the on-premises network.

How it works:

1. Install the self-hosted IR on a Windows machine that can reach SQL Server.
2. Register it with the factory using an authentication key.
3. Create a SQL Server linked service that uses this IR.
4. Store the password in Key Vault.

Example: The IR makes only outbound HTTPS connections to Azure, so no inbound firewall ports are needed.

Interview tip: Mention installing two nodes for high availability and keeping the IR updated.

Q33. What is the Until activity?

Answer: Until repeats its inner activities until a condition becomes true, like a do-until loop.

How it works:

• The condition is an expression checked after each iteration.
• A timeout stops the loop if the condition never becomes true.
• A Wait activity inside the loop controls the polling interval.

Example: Poll every 10 minutes with Get Metadata until the _SUCCESS file exists, with a 3-hour timeout.

Interview tip: Mention that event triggers are often better than polling when possible.

Q34. What are the If Condition and Switch activities?

Answer: They add branching logic to pipelines.

How it works:

• If Condition: evaluates a true or false expression and runs the True or False activities.
• Switch: compares a value with several cases and runs the matching branch, with a default branch.

Example: If the Lookup row count is 0, skip the load and log "no new data"; otherwise run the Copy.

Interview tip: Mention that ADF limits how control activities can be nested, so complex branching often goes into child pipelines.

Q35. How do you run a Databricks notebook from ADF?

Answer: Use the Databricks Notebook activity with a Databricks linked service.

How it works:

1. Create a Databricks linked service (job cluster, existing cluster or instance pool), using managed identity or a token from Key Vault.
2. Set the notebook path and pass base parameters.
3. In the notebook, read them with dbutils.widgets.get().
4. Return a value with dbutils.notebook.exit(), read in ADF as @activity('Nb').output.runOutput.

Example: ADF passes load_date, and the notebook processes only that day's files.

Interview tip: Mention using job clusters to cut cost and Databricks Workflows as an alternative orchestrator.

Q36. What is the Web activity used for?

Answer: The Web activity calls a REST API from a pipeline.

How it works:

• Supports GET, POST, PUT and DELETE with headers and a body.
• Can authenticate with managed identity.
• The response is available to later activities.

Example: Call a Logic App URL to send a success email, or call the Power BI REST API to refresh a dataset after the load.

Interview tip: Mention the Webhook activity for APIs that call back when a long task finishes.

Q37. How do you implement CI/CD for Azure Data Factory?

Answer: Use Git integration in development and automated deployment of ARM templates to test and production.

How it works:

1. Connect the dev factory to Git (Azure Repos or GitHub) and work in feature branches.
2. Merge to the collaboration branch through pull requests.
3. Build ARM templates automatically with the ADF npm package (or the Publish button).
4. A release pipeline deploys to test and prod with environment-specific parameter files.
5. Stop triggers before deployment and restart them after.

Example: The prod parameter file replaces the dev Key Vault URL and storage account name.

Interview tip: Mention that only the dev factory is connected to Git; test and prod are deployed by the pipeline.

Q38. What is the difference between a debug run and a triggered run?

Answer: A debug run tests your draft; a triggered run executes the published pipeline.

How it works:

• Debug: runs from the authoring canvas without publishing, shows results inline, and is not tied to triggers.
• Triggered: runs the published version on schedule or manually, and appears in the Monitor hub.

Example: You debug a new Copy activity with a small date range before publishing.

Interview tip: Mention that debug runs still use real compute and real data, so test against dev sources.

Q39. How do you monitor ADF pipelines?

Answer: Use the Monitor hub for daily checks, and Azure Monitor and Log Analytics for alerts and history.

How it works:

• Monitor hub: run status, duration, inputs, outputs and errors for every activity.
• Azure Monitor alerts: notify on failed runs or long durations.
• Diagnostic settings: send logs to Log Analytics for KQL queries and dashboards (longer retention than the Monitor hub).
• Custom logging table: row counts and business-level checks.

Example: A KQL query lists all pipelines that failed in the last 24 hours with their error messages.

Interview tip: Mention that the Monitor hub keeps run data for 45 days, so you send logs to Log Analytics for longer history.

Q40. How do you pass the output of one activity to another?

Answer: Reference the earlier activity's output in an expression.

How it works:

• Copy: @activity('Copy1').output.rowsCopied
• Lookup (single row): @activity('Lookup1').output.firstRow.columnName
• Lookup (many rows): @activity('Lookup1').output.value
• Notebook: @activity('Nb1').output.runOutput

Example: Write rowsCopied into the logging table after each Copy activity.

Interview tip: Mention that the dependency (green arrow) must exist so the output is available.

Q41. What is a Data Integration Unit (DIU)?

Answer: A DIU is a measure of compute power (CPU, memory and network) for the Copy activity on the Azure IR.

How it works:

• ADF chooses DIUs automatically, or you set a value.
• More DIUs can speed up large copies between cloud stores.
• Cost is based on DIU-hours, so more DIUs can cost more.

Example: Raising DIUs for a large file-to-file copy can cut the run time, while small copies see no benefit.

Interview tip: Say you test DIU settings and check the copy monitoring details for the real bottleneck.

Q42. How do you improve Copy activity performance?

Answer: Find the bottleneck first, then tune the source, the runtime or the sink.

How it works:

1. Increase DIUs and parallel copies.
2. Use source partitioning (physical or dynamic range) for large tables.
3. Use compressed columnar formats such as Parquet.
4. Keep the source, sink and IR in the same region.
5. Scale up or out the self-hosted IR if it is the bottleneck.
6. Use staged copy with COPY INTO when loading Synapse.

Example: Dynamic range partitioning on OrderID can turn one long single-threaded read into many parallel reads.

Interview tip: Mention the copy monitoring view, which shows time spent in queue, read and write.

Q43. What is the difference between Azure Data Factory and Synapse pipelines?

Answer: They share the same pipeline engine, but they live in different places and have a few feature differences.

How it works:

• ADF: a standalone service; includes the Azure-SSIS IR and some features Synapse lacks.
• Synapse pipelines: built into a Synapse workspace next to SQL and Spark pools.
• Fabric Data Factory: the SaaS version inside Microsoft Fabric, with pipelines and Dataflow Gen2.

Example: A team already using Synapse SQL pools keeps its orchestration in Synapse pipelines in the same workspace.

Interview tip: Mention Fabric Data Factory to show you follow the newest platform.

Q44. How do you keep secrets out of ADF linked services?

Answer: Never store passwords in pipeline JSON; reference Key Vault or use identity-based authentication.

How it works:

• Create a Key Vault linked service and give ADF's managed identity "get secret" access.
• Point linked service passwords and connection strings to Key Vault secrets.
• Use managed identity authentication for Azure services such as ADLS and Azure SQL.

Example: The Snowflake password is stored in Key Vault; the linked service holds only the secret name.

Interview tip: Mention that this keeps secrets out of Git when CI/CD is enabled.

Q45. What is fault tolerance in the Copy activity?

Answer: Fault tolerance lets the Copy activity skip bad rows or files instead of failing the whole run.

How it works:

• Skip incompatible rows, such as data type or primary key errors.
• Skip missing or forbidden files in file copies.
• Log skipped rows to a storage location for review.

Example: A CSV row with text in a numeric column is skipped and logged, while the other rows load.

Interview tip: Say you always review the skipped-row log and alert if it is not empty.

4. Azure Databricks and PySpark interview questions

Short answer: Databricks rounds test Spark internals, Delta Lake, performance tuning, streaming and Unity Catalog. For a deeper set, see our 150 PySpark and Databricks interview questions with code.

Q46. What is Delta Lake and why is it used?

Answer: Delta Lake is an open table format that adds database-like reliability to files in a data lake.

How it works:

• Data is stored as Parquet files plus a transaction log (_delta_log).
• The log gives ACID transactions, so readers never see half-written data.
• It adds schema enforcement, time travel, MERGE, UPDATE and DELETE.

Example: Two jobs write to the same table at once; Delta's optimistic concurrency makes sure the table never ends up corrupted.

Interview tip: Say Delta solves the classic data lake problems: no transactions, no updates and messy small files.

Q47. What is the medallion architecture?

Answer: A layered lakehouse design that improves data quality step by step: Bronze, Silver and Gold.

How it works:

• Bronze: raw data as it arrives, append-only, kept for replay.
• Silver: cleaned, deduplicated, typed and joined data.
• Gold: business-level aggregates and star schemas for reporting.

Example: Bronze holds raw order JSON, Silver holds clean orders with customer IDs, and Gold holds daily sales by region for Power BI.

Interview tip: Explain what checks happen between layers, not just the names.

Q48. What is the difference between narrow and wide transformations?

Answer: Narrow transformations work within one partition; wide transformations need data from many partitions, which causes a shuffle.

How it works:

• Narrow: select, filter, withColumn, map; no data moves between executors.
• Wide: groupBy, join, distinct, orderBy, repartition; data is shuffled across the network.
• Each shuffle creates a new stage in the Spark job.

Example: df.filter("country = 'IN'") is narrow; df.groupBy("country").count() is wide.

Interview tip: Say you reduce shuffles to speed up jobs, for example by filtering before joins.

Q49. What is the difference between repartition and coalesce?

Answer: repartition reshuffles data into any number of partitions; coalesce only reduces partitions without a full shuffle.

How it works:

• repartition(n) or repartition("col"): full shuffle, even partition sizes, can increase or decrease.
• coalesce(n): merges existing partitions, cheaper, but partitions can be uneven.

Example: Use coalesce(10) before writing a small result to avoid 200 tiny files; use repartition("date") before writing a large table partitioned by date.

Interview tip: Mention that coalesce(1) on big data creates one slow task, so avoid it.

Q50. When do you use a broadcast join?

Answer: When one side of a join is small enough to fit in each executor's memory.

How it works:

• Spark sends the small table to every executor.
• The large table is joined in place, with no shuffle.
• Spark broadcasts automatically below spark.sql.autoBroadcastJoinThreshold (10 MB by default), or you use the broadcast() hint.

Example: orders.join(broadcast(countries), "country_code") joins a 1-billion-row table with a 250-row lookup table.

Interview tip: Warn that broadcasting a large table can cause out-of-memory errors.

Q51. What do OPTIMIZE, Z-ORDER and VACUUM do?

Answer: They are Delta maintenance commands for performance and storage.

How it works:

• OPTIMIZE: compacts many small files into larger ones.
• ZORDER BY (col): co-locates similar values so filters skip more files.
• VACUUM: deletes files no longer referenced by the table after the retention period (7 days by default), which also limits time travel.
• Liquid clustering is the newer alternative to partitioning and Z-ordering.

Example: OPTIMIZE silver.orders ZORDER BY (customer_id) speeds up queries that filter by customer.

Interview tip: Mention scheduling these as a maintenance job, or using predictive optimization.

Q52. What is Auto Loader?

Answer: Auto Loader is a Databricks feature that incrementally ingests new files from cloud storage.

How it works:

• Uses the cloudFiles source with Structured Streaming.
• Tracks processed files in a checkpoint, so each file is loaded once.
• Detects new files by directory listing or file notifications.
• Supports schema inference and schema evolution.

Example: New JSON files landing in raw/orders/ are loaded into bronze.orders every hour with trigger(availableNow=True).

Interview tip: Compare it with ADF copy: Auto Loader is simpler for continuous file ingestion at scale.

Q53. What is Unity Catalog?

Answer: Unity Catalog is Databricks' central governance layer for data and AI assets.

How it works:

• A three-level namespace: catalog.schema.table.
• Central access control with SQL GRANT statements.
• Built-in auditing, lineage and data discovery.
• Shared across all workspaces attached to the same metastore.

Example: GRANT SELECT ON TABLE gold.sales TO analysts gives the analyst group read access.

Interview tip: Mention row filters and column masks for fine-grained security.

Q54. Write PySpark to remove duplicate rows, keeping the latest record per customer.

Answer: Rank rows per customer by update time and keep rank 1.

from pyspark.sql import Window
from pyspark.sql.functions import row_number, col
w = Window.partitionBy("customer_id").orderBy(col("updated_at").desc())
latest = df.withColumn("rn", row_number().over(w)).filter("rn = 1").drop("rn")

How it works:

• partitionBy groups rows by customer.
• orderBy(... desc()) puts the newest row first.
• row_number() numbers rows, and the filter keeps only the first.

Interview tip: Mention that dropDuplicates() keeps an arbitrary row, so use a window when you need the latest one.

Q55. Explain the Spark architecture.

Answer: Spark has a driver that plans the work and executors that do it, coordinated by a cluster manager.

How it works:

• Driver: runs your code, builds the plan (DAG) and schedules tasks.
• Cluster manager: allocates resources.
• Executors: run tasks and cache data on worker nodes.
• An action creates a job, split into stages at shuffle boundaries, and each stage into tasks, one per partition.

Example: A job reading 400 partitions creates 400 tasks spread across the executors' cores.

Interview tip: Draw the driver-executor diagram if there is a whiteboard.

Q56. What is lazy evaluation in Spark?

Answer: Spark records transformations but runs nothing until an action is called.

How it works:

• Transformations build a logical plan.
• An action (count, show, write) triggers execution.
• Spark optimizes the whole plan first, such as pushing filters down to the source.

Example: df.filter(...).select(...) returns instantly; .count() actually reads the data.

Interview tip: Mention that this is why errors sometimes appear only at the action.

Q57. What is the difference between transformations and actions?

Answer: Transformations define a new DataFrame; actions run the plan and return or write results.

How it works:

• Transformations: select, filter, join, groupBy, withColumn; lazy.
• Actions: count, collect, show, take, write; trigger a job.

Example: Calling df.count() and then df.write runs the plan twice unless you cache the DataFrame.

Interview tip: Warn that collect() on big data can crash the driver.

Q58. What is the Catalyst optimizer?

Answer: Catalyst is Spark SQL's query optimizer that turns your query into an efficient execution plan.

How it works:

1. Analysis: resolves column and table names.
2. Logical optimization: predicate pushdown, column pruning, constant folding.
3. Physical planning: chooses join strategies and other operators.
4. Code generation: compiles parts of the plan into efficient code.

Example: A filter written after a join is pushed below the join, so less data is joined.

Interview tip: Mention df.explain() to read the physical plan.

Q59. What is Adaptive Query Execution (AQE)?

Answer: AQE re-optimizes a query while it runs, using real statistics from completed stages.

How it works:

• Coalesces many small shuffle partitions into fewer, bigger ones.
• Switches join strategy to broadcast when one side turns out small.
• Splits skewed partitions in joins.
• It is enabled by default in recent Spark and Databricks versions.

Example: A join planned as sort-merge becomes a broadcast join after Spark sees a filtered table is only 5 MB.

Interview tip: Say you check AQE is on before hand-tuning shuffle partitions.

Q60. What is data skew and how do you fix it?

Answer: Skew is when a few keys hold most of the data, so a few tasks run far longer than the rest.

How it works:

• Spot it in the Spark UI: one task takes minutes while others take seconds.
• Fixes: AQE skew-join handling, broadcast the small side, salt the hot key (add a random suffix), or handle nulls and hot keys separately.

Example: 40% of orders have customer_id = null; filtering nulls before the join removes the skew.

Interview tip: Explain salting step by step; it is a favourite follow-up question.

Q61. What is the difference between cache and persist?

Answer: Both keep a DataFrame for reuse; persist lets you choose the storage level.

How it works:

• cache() uses the default level (memory and disk for DataFrames).
• persist(StorageLevel.X) chooses memory only, disk only, serialized and so on.
• Both are lazy: data is stored at the next action.
• Call unpersist() to free memory.

Example: Cache a cleaned DataFrame that feeds three different aggregations.

Interview tip: Say you cache only when a DataFrame is reused, not by habit.

Q62. What does spark.sql.shuffle.partitions control?

Answer: It sets how many partitions Spark creates after a shuffle; the default is 200.

How it works:

• Too few partitions create huge tasks and memory pressure.
• Too many create tiny tasks and scheduling overhead.
• AQE can coalesce partitions automatically at runtime.

Example: For a 2 GB shuffle, 200 partitions is fine; for 2 TB, you may need several thousand, or let AQE decide.

Interview tip: Mention a rough target of 100–200 MB per partition.

Q63. What is the difference between an RDD and a DataFrame?

Answer: An RDD is Spark's low-level distributed collection; a DataFrame is a distributed table with a schema and an optimizer.

How it works:

• RDD: no schema, no Catalyst optimization, functional API.
• DataFrame: named columns, optimized by Catalyst and Tungsten, SQL-like API.
• DataFrames are usually much faster and easier to write.

Example: rdd.map(lambda x: ...) versus df.withColumn("total", col("qty") * col("price")).

Interview tip: Say you use DataFrames for almost everything and RDDs only for rare low-level cases.

Q64. Why should you avoid Python UDFs?

Answer: Python UDFs are slow because they move data between the JVM and Python and block Catalyst optimizations.

How it works:

• Each row is serialized to Python, processed, then sent back.
• Catalyst cannot see inside the UDF to optimize it.
• Alternatives: built-in functions in pyspark.sql.functions, SQL expressions, or vectorized pandas UDFs.

Example: Replace a UDF that upper-cases names with the built-in upper(col("name")).

Interview tip: Mention that built-in functions are often many times faster than plain Python UDFs.

Q65. What is the difference between a job cluster and an all-purpose cluster?

Answer: Job clusters run scheduled jobs and shut down afterwards; all-purpose clusters are for interactive work.

How it works:

• Job cluster: created per job run, terminated when done, lower DBU rate.
• All-purpose cluster: shared by users for notebooks, kept running with auto-termination.
• Serverless compute removes cluster management for supported workloads.

Example: Development happens on an all-purpose cluster; the nightly production job runs on a job cluster.

Interview tip: Mention that running production jobs on all-purpose clusters is a common cost mistake.

Q66. What are Databricks Workflows (Jobs)?

Answer: Workflows is Databricks' built-in orchestrator for running tasks on a schedule or trigger.

How it works:

• A job contains tasks: notebooks, Python scripts, SQL, dbt, pipelines or other jobs.
• Tasks have dependencies, retries, timeouts and parameters.
• Triggers include schedules, file arrival and continuous runs.
• Notifications go to email, Slack or Teams.

Example: Task 1 ingests to Bronze, tasks 2 and 3 build Silver tables in parallel, and task 4 builds Gold.

Interview tip: Compare it with ADF: Workflows for Databricks-only pipelines, ADF when you orchestrate many Azure services.

Q67. What are Delta Live Tables (Lakeflow Declarative Pipelines)?

Answer: A declarative framework where you define tables and quality rules, and Databricks runs the pipeline for you.

How it works:

• You declare streaming tables and materialized views in SQL or Python.
• Databricks works out dependencies, orchestration and incremental processing.
• Expectations enforce data quality: warn, drop bad rows or fail.
• CDC with SCD Type 1 or 2 is built in.

Example: CONSTRAINT valid_id EXPECT (id IS NOT NULL) ON VIOLATION DROP ROW

Interview tip: Mention the current name, Lakeflow Declarative Pipelines, to show you are up to date.

Q68. What is Delta time travel?

Answer: Time travel lets you query or restore earlier versions of a Delta table.

How it works:

• Every write creates a new table version in the transaction log.
• Query with VERSION AS OF 12 or TIMESTAMP AS OF '2026-10-19'.
• Roll back with RESTORE TABLE ... TO VERSION AS OF 12.
• History is limited by log retention and VACUUM.

Example: A bad load corrupts a table; you RESTORE it to the version before the load.

Interview tip: Mention DESCRIBE HISTORY table to see versions and operations.

Q69. What is the difference between schema enforcement and schema evolution?

Answer: Enforcement blocks writes that do not match the table schema; evolution lets the schema change on purpose.

How it works:

• Enforcement: a write with an extra or wrong-typed column fails by default.
• Evolution: option("mergeSchema", "true") adds new columns during the write.
• Type changes usually need an explicit overwrite or table change.

Example: A new discount column in the source fails the write until you enable mergeSchema.

Interview tip: Say you enable evolution in Bronze but keep stricter control in Silver and Gold.

Q70. What is Delta Change Data Feed?

Answer: Change Data Feed (CDF) records row-level changes between table versions.

How it works:

• Turn it on with delta.enableChangeDataFeed = true.
• Each change has _change_type (insert, update_preimage, update_postimage, delete), _commit_version and _commit_timestamp.
• Read with readChangeFeed in batch or streaming.

Example: The Gold job reads only Silver changes since version 120 instead of the whole table.

Interview tip: Mention that CDF makes incremental Silver-to-Gold processing efficient.

Q71. What is Structured Streaming?

Answer: Spark's streaming engine, which treats a live stream as a table that keeps growing.

How it works:

• Data is processed in micro-batches (or continuously in some modes).
• Checkpoints store progress for fault tolerance.
• Output modes: append, update and complete.
• Triggers: processing-time intervals or availableNow for batch-style runs.

Example: Read from Event Hubs, aggregate clicks per minute, and write to a Delta table.

Interview tip: Mention exactly-once delivery when using checkpoints with Delta sinks.

Q72. What is a checkpoint in Structured Streaming?

Answer: A checkpoint is a storage folder where a streaming query saves its progress and state.

How it works:

• Stores processed offsets, committed batches and aggregation state.
• On restart, the query resumes exactly where it stopped.
• Each query needs its own unique checkpoint location.

Example: .option("checkpointLocation", "/checkpoints/orders_bronze")

Interview tip: Warn that deleting or sharing a checkpoint causes data to be reprocessed or lost.

Q73. What is a watermark in Structured Streaming?

Answer: A watermark tells Spark how late event-time data can arrive before it is ignored.

How it works:

• withWatermark("event_time", "10 minutes") keeps state for 10 minutes past the latest event time.
• Late events inside the threshold are still counted.
• Older state is dropped, which keeps memory under control.

Example: Counting clicks per 5-minute window with a 10-minute watermark.

Interview tip: Explain the trade-off: a longer watermark is more accurate but uses more memory.

Q74. What is Photon?

Answer: Photon is Databricks' native vectorized query engine, written in C++.

How it works:

• Runs SQL and DataFrame operations on columnar data in batches.
• Works with existing code; no changes are needed.
• Speeds up scans, joins and aggregations, especially on Delta tables.

Example: Turning on Photon for a SQL warehouse or job cluster can significantly speed up heavy aggregations.

Interview tip: Mention that Photon has a higher DBU rate, so check that the speed-up saves money overall.

Q75. What are Databricks secret scopes?

Answer: Secret scopes store credentials securely so notebooks never show passwords in code.

How it works:

• Azure Key Vault-backed: secrets live in Key Vault.
• Databricks-backed: secrets stored by Databricks.
• Read with dbutils.secrets.get(scope="kv", key="sql-password").
• Secret values are shown as [REDACTED] in notebook output.

Example: A JDBC read uses the password from a Key Vault-backed scope.

Interview tip: Mention access control lists on scopes to limit who can read secrets.

Q76. How do you connect Databricks to ADLS Gen2 securely?

Answer: With Unity Catalog storage credentials and external locations, using a managed identity.

How it works:

1. Create an Access Connector for Azure Databricks (it has a managed identity).
2. Grant it Storage Blob Data Contributor on the storage account.
3. Create a storage credential and an external location in Unity Catalog.
4. Grant users access with GRANT READ FILES ON EXTERNAL LOCATION ....

Example: Users read abfss://raw@mystorage.dfs.core.windows.net/ without any keys in notebooks.

Interview tip: Say mounts and account keys are the older approach; Unity Catalog is the recommended way.

Q77. What is the difference between managed and external tables?

Answer: The difference is who owns the data files and what DROP TABLE does.

How it works:

• Managed table: Unity Catalog manages the storage location; dropping the table deletes the data (after a retention period).
• External table: data lives at a path you control; dropping the table removes only the metadata.

Example: Gold tables are managed; raw files shared with other tools stay as external tables.

Interview tip: Mention that managed tables get more automatic optimizations in Databricks.

Q78. When should you partition a Delta table, and when should you avoid it?

Answer: Partition only large tables by a low-cardinality column that queries filter on.

How it works:

• Good: tables of roughly 1 TB or more partitioned by date.
• Bad: small tables or high-cardinality columns (like customer_id), which create many tiny files.
• Aim for each partition to hold at least about 1 GB.
• For new tables, liquid clustering is usually better than partitioning.

Example: A 5 TB events table partitioned by event_date; a 20 GB customers table not partitioned.

Interview tip: Over-partitioning is a common mistake; mention it.

Q79. How do you read only new data from a Delta table?

Answer: Read it incrementally with streaming, Change Data Feed or a watermark column.

How it works:

• Streaming read: spark.readStream.table("silver.orders") with a checkpoint reads only new commits.
• Change Data Feed: reads inserts, updates and deletes between versions.
• Watermark column: filter updated_at > last_processed_time.

Example: The Gold job uses readStream with trigger(availableNow=True) to process only new Silver rows each run.

Interview tip: Mention that a plain streaming read expects append-only sources; use CDF when there are updates.

Q80. What is dbutils?

Answer: Databricks Utilities, a set of helper tools available in notebooks.

How it works:

• dbutils.fs: list, copy and move files.
• dbutils.secrets: read secrets.
• dbutils.widgets: notebook parameters.
• dbutils.notebook: run another notebook or return a value with exit().

Example: dbutils.widgets.get("load_date") reads the date passed by ADF or a job.

Interview tip: Mention that dbutils works only in Databricks, so keep core logic in plain PySpark functions for testing.

5. SQL and data modeling interview questions

Short answer: Expect to write SQL live, especially joins and window functions, and to explain star schemas, grain and slowly changing dimensions.

Q81. How do you find the second-highest salary in SQL?

Answer: Use a subquery for a quick answer, or DENSE_RANK for a flexible one that handles ties.

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

How it works:

• The inner query finds the highest salary.
• The outer query finds the highest salary below it.
• For the Nth highest, use DENSE_RANK() OVER (ORDER BY salary DESC) and filter rnk = N.

Interview tip: Ask whether ties count as one rank; it shows you think about edge cases.

Q82. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

Answer: All three number rows in order, but they treat ties differently.

How it works:

• ROW_NUMBER: always unique (1, 2, 3, 4), even for ties.
• RANK: ties share a number, then numbers skip (1, 1, 3, 4).
• DENSE_RANK: ties share a number with no gaps (1, 1, 2, 3).

Example: Salaries 900, 900, 800 give ROW_NUMBER 1, 2, 3; RANK 1, 1, 3; DENSE_RANK 1, 1, 2.

Interview tip: Use ROW_NUMBER for deduplication and DENSE_RANK for "top N values".

Q83. What is the difference between a star schema and a snowflake schema?

Answer: Both have a central fact table; the difference is whether dimensions are flat or normalized.

How it works:

• Star: each dimension is one denormalized table joined directly to the fact.
• Snowflake: dimensions are split into sub-tables (for example, product → category → department).
• Star schemas need fewer joins and are faster and simpler for BI tools.

Example: fact_sales joins to dim_customer, dim_product and dim_date.

Interview tip: Say Power BI works best with star schemas.

Q84. What are SCD Type 1 and Type 2?

Answer: Slowly changing dimension types decide how to handle changes to dimension attributes.

How it works:

• Type 1: overwrite the old value; no history.
• Type 2: close the current row (end_date, is_current = false) and insert a new row with a new surrogate key; full history.

Example: A customer moves from Hyderabad to Bengaluru. Type 1 shows only Bengaluru; Type 2 keeps both rows so old sales stay linked to Hyderabad.

Interview tip: Mention that Type 2 is built with MERGE in Delta Lake or with DLT/Lakeflow APPLY CHANGES.

Q85. What is the difference between a fact table and a dimension table?

Answer: Fact tables store measurable events; dimension tables describe them.

How it works:

• Fact: numeric measures (amount, quantity) plus foreign keys; many rows; grows fast.
• Dimension: descriptive attributes (name, category, city); fewer rows; used to filter and group.

Example: fact_sales(date_key, product_key, customer_key, amount) and dim_product(product_key, name, category).

Interview tip: Always mention the grain of the fact table.

Q86. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping; HAVING filters groups after aggregation.

How it works:

• WHERE cannot use aggregates such as SUM().
• HAVING works on aggregated results.
• Filtering early with WHERE is faster.

Example: SELECT customer_id, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING SUM(amount) > 10000;

Interview tip: Mention the logical query order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Q87. What types of joins are there?

Answer: Joins combine rows from tables based on a condition.

How it works:

• INNER: only matching rows.
• LEFT / RIGHT: all rows from one side plus matches.
• FULL OUTER: all rows from both sides.
• CROSS: every combination.
• SELF: a table joined to itself.
• Semi / anti: EXISTS and NOT EXISTS (or LEFT SEMI and LEFT ANTI in Spark).

Example: A LEFT ANTI join finds customers with no orders.

Interview tip: Warn about duplicate keys causing row explosion in joins.

Q88. What is the difference between UNION and UNION ALL?

Answer: UNION removes duplicates; UNION ALL keeps every row.

How it works:

• UNION sorts or hashes rows to remove duplicates, which costs time.
• UNION ALL simply appends results, so it is faster.
• Both need the same number of columns with compatible types.

Example: Combining 2025 and 2026 order tables that cannot overlap: use UNION ALL.

Interview tip: Say you default to UNION ALL unless you really need deduplication.

Q89. What is the difference between DELETE, TRUNCATE and DROP?

Answer: They remove different things: rows, all rows, or the whole table.

How it works:

• DELETE: removes selected rows with WHERE; logged row by row; can be rolled back in a transaction.
• TRUNCATE: removes all rows quickly; resets identity; minimal logging.
• DROP: removes the table structure and data.

Example: DELETE FROM staging WHERE load_date = '2026-10-20' to reload one day.

Interview tip: Mention that in Delta Lake, DELETE creates a new version you can time-travel back to.

Q90. What is a CTE?

Answer: A common table expression is a named, temporary result defined with WITH and used in the next query.

How it works:

• Makes long queries readable by breaking them into steps.
• Can be referenced more than once in the same statement.
• Recursive CTEs handle hierarchies such as org charts.

Example: WITH paid AS (SELECT * FROM orders WHERE status = 'paid') SELECT customer_id, COUNT(*) FROM paid GROUP BY customer_id;

Interview tip: Mention that CTEs improve readability but are not automatically cached.

Q91. What are window functions?

Answer: Functions that calculate across a set of related rows while keeping every row in the result.

How it works:

• Syntax: function() OVER (PARTITION BY ... ORDER BY ... ROWS ...).
• Ranking: ROW_NUMBER, RANK, DENSE_RANK.
• Offset: LAG, LEAD.
• Aggregates: SUM, AVG, COUNT over a window.

Example: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) gives a running total per customer.

Interview tip: Window functions are the most tested SQL topic; practise them daily.

Q92. What do LAG and LEAD do?

Answer: LAG reads a value from an earlier row; LEAD reads from a later row in the same window.

How it works:

• LAG(col, n, default) looks back n rows.
• LEAD(col, n, default) looks forward n rows.
• Used for period-over-period changes and gaps between events.

Example: revenue - LAG(revenue) OVER (ORDER BY month) gives the monthly change.

Interview tip: Mention the default value for the first row, which otherwise returns NULL.

Q93. How do you find duplicate rows?

Answer: Group by the columns that define a duplicate and keep groups with more than one row.

How it works:

• GROUP BY the business key columns.
• HAVING COUNT(*) > 1 returns only duplicates.
• Use ROW_NUMBER() to see or delete the extra copies.

Example: SELECT email, COUNT(email) AS cnt FROM customers GROUP BY email HAVING COUNT(email) > 1;

Interview tip: Ask what defines a duplicate: all columns, or a business key.

Q94. What is the difference between a clustered and a non-clustered index?

Answer: A clustered index decides how rows are physically stored; a non-clustered index is a separate lookup structure.

How it works:

• Clustered: one per table; rows are stored in key order.
• Non-clustered: many per table; stores key values with pointers to rows.
• Indexes speed up reads but slow down inserts and updates.

Example: A clustered index on order_id and a non-clustered index on customer_id for customer lookups.

Interview tip: Mention that data lakes use partitioning, clustering and file statistics instead of traditional indexes.

Q95. What is normalization?

Answer: Organizing tables to reduce duplicate data and update errors.

How it works:

• 1NF: atomic values, no repeating groups.
• 2NF: no partial dependency on part of a composite key.
• 3NF: no column depends on another non-key column.
• Warehouses often denormalize to reduce joins.

Example: Storing the customer's city only in customers, not in every order row.

Interview tip: Say OLTP systems normalize, while analytics models denormalize for speed.

Q96. What is the difference between OLTP and OLAP?

Answer: OLTP runs day-to-day transactions; OLAP runs analysis over historical data.

How it works:

• OLTP: many small reads and writes, normalized tables, current data, fast inserts.
• OLAP: large scans and aggregations, star schemas, history, fast reads.

Example: The order-entry app writes to Azure SQL (OLTP); the sales dashboard reads from a lakehouse Gold layer (OLAP).

Interview tip: Explain that data engineers move data from OLTP systems to OLAP platforms.

Q97. What is a surrogate key?

Answer: A system-generated key used in a dimension instead of the natural business key.

How it works:

• Generated with an identity column, sequence or hash.
• Stays stable even if business keys change or come from several systems.
• Required for SCD Type 2, where one customer has several rows.

Example: Customer C123 has rows with surrogate keys 501 (old address) and 734 (new address).

Interview tip: Mention identity columns in Delta Lake (GENERATED ALWAYS AS IDENTITY).

Q98. What is a degenerate dimension?

Answer: A dimension value stored directly in the fact table, with no dimension table of its own.

How it works:

• Usually a transaction identifier with no other attributes.
• Useful for grouping fact rows that belong to the same transaction.

Example: invoice_number in fact_sales groups all line items of one invoice.

Interview tip: A short definition plus an example is enough for this one.

Q99. What is a conformed dimension?

Answer: A dimension shared by several fact tables with the same keys and meaning everywhere.

How it works:

• Built once and reused across data marts.
• Lets you compare facts across business processes.

Example: dim_date and dim_customer are used by both fact_sales and fact_returns.

Interview tip: Mention that conformed dimensions keep reports consistent across teams.

Q100. What is the grain of a fact table?

Answer: The grain is the level of detail that one row represents.

How it works:

• Decide the grain first, before choosing measures and dimensions.
• All measures must match the grain.
• A finer grain gives more flexibility but more rows.

Example: "One row per order line per day" versus "one row per order".

Interview tip: Mixing grains in one fact table is a classic modeling mistake; mention it.

Q101. What types of fact tables are there?

Answer: There are four common types, based on how events are recorded.

How it works:

• Transaction: one row per event, such as each sale.
• Periodic snapshot: state at regular intervals, such as daily account balance.
• Accumulating snapshot: one row per process, updated at milestones, such as order to delivery.
• Factless: records events with no measures, such as student attendance.

Example: An accumulating snapshot tracks order date, ship date and delivery date in one row.

Interview tip: Give one example for each type.

Q102. What is SCD Type 3?

Answer: SCD Type 3 keeps limited history by storing the previous value in an extra column.

How it works:

• Adds columns such as previous_region next to current_region.
• Keeps only one earlier value, not full history.

Example: After a sales territory change, reports can show sales by both old and new region.

Interview tip: Say Type 3 is rare; Type 1 and Type 2 are used most.

Q103. What is Data Vault modeling?

Answer: A modeling method designed for integrating many changing sources while keeping full history.

How it works:

• Hubs: unique business keys.
• Links: relationships between hubs.
• Satellites: descriptive attributes with load dates and history.
• Usually feeds star schemas for reporting.

Example: hub_customer, link_customer_order, sat_customer_details.

Interview tip: Say Data Vault suits large enterprises with many sources; star schemas suit reporting.

Q104. How do you optimize a slow SQL query?

Answer: Read the execution plan, find the expensive step, and fix that first.

How it works:

1. Check the execution plan for scans, sorts and large joins.
2. Add or fix indexes (or partitioning and clustering in a lakehouse).
3. Select only needed columns and filter early.
4. Avoid functions on filtered columns, such as YEAR(order_date) = 2026.
5. Rewrite correlated subqueries as joins, and update statistics.

Example: Replacing WHERE YEAR(order_date) = 2026 with a date range lets the database use the index.

Interview tip: Always start with "I check the execution plan".

Q105. What does the MERGE statement do?

Answer: MERGE updates, inserts or deletes rows in a target table based on a source, in one statement.

How it works:

• ON defines the match condition, usually the business key.
• WHEN MATCHED THEN UPDATE changes existing rows.
• WHEN NOT MATCHED THEN INSERT adds new rows.
• Optional WHEN NOT MATCHED BY SOURCE THEN DELETE (where supported) removes rows missing from the source.

Example: MERGE INTO silver.customers t USING updates s ON t.id = s.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Interview tip: Mention that duplicate keys in the source make MERGE fail, so deduplicate first.

6. Microsoft Fabric and Azure Synapse interview questions

Short answer: In 2026, interviewers increasingly ask about Microsoft Fabric alongside Synapse, especially OneLake, Lakehouse, Warehouse and Direct Lake.

Q106. What is OneLake in Microsoft Fabric?

Answer: OneLake is the single, organization-wide data lake that comes automatically with every Fabric tenant.

How it works:

• All Fabric items (Lakehouse, Warehouse, KQL databases) store their data in OneLake.
• Tables are stored in open Delta Parquet format.
• Shortcuts point to data elsewhere without copying it.
• It is built on ADLS Gen2, so ADLS APIs and tools work with it.

Example: The finance and sales teams each have workspaces, but all their data lives in one OneLake.

Interview tip: Describe OneLake as "OneDrive for data".

Q107. What is the difference between a Fabric Lakehouse and a Fabric Warehouse?

Answer: A Lakehouse is Spark-first and handles files and tables; a Warehouse is SQL-first with full T-SQL write support.

How it works:

• Lakehouse: files and Delta tables, written with Spark notebooks or pipelines; a read-only SQL analytics endpoint for queries.
• Warehouse: tables created and changed with T-SQL (INSERT, UPDATE, DELETE, multi-table transactions).
• Both store data as Delta in OneLake and can be queried together.

Example: Data engineers build Silver in a Lakehouse with PySpark; SQL developers build the Gold star schema in a Warehouse.

Interview tip: Choose by team skills: Spark teams use a Lakehouse, SQL teams use a Warehouse.

Q108. What is the difference between dedicated and serverless SQL pools in Synapse?

Answer: Dedicated pools are provisioned data warehouse compute; serverless pools query lake files on demand.

How it works:

• Dedicated SQL pool: fixed size in DWUs, data loaded into distributed tables, billed while running, can be paused.
• Serverless SQL pool: no infrastructure, queries Parquet, CSV and Delta files in ADLS, billed per TB processed.

Example: Use serverless SQL for ad hoc exploration of raw files; use a dedicated pool for a large, heavily used enterprise warehouse.

Interview tip: Mention pausing dedicated pools outside business hours to save cost.

Q109. What are the distribution types in a Synapse dedicated SQL pool?

Answer: Distribution decides how table rows are spread across the pool's 60 distributions.

How it works:

• Hash: rows placed by a column's hash; best for large fact tables joined on that column.
• Round-robin: rows spread evenly at random; good for staging tables.
• Replicated: a full copy on each compute node; best for small dimension tables.

Example: fact_sales hash-distributed on customer_key, dim_date replicated, staging tables round-robin.

Interview tip: Explain that the right distribution avoids data movement during joins.

Q110. What is Microsoft Fabric?

Answer: Microsoft Fabric is an all-in-one SaaS analytics platform built on OneLake.

How it works:

• Workloads: Data Factory, Data Engineering (Spark), Data Warehouse, Data Science, Real-Time Intelligence and Power BI.
• One storage layer (OneLake), one security model and one capacity-based billing.
• No infrastructure to manage.

Example: A team ingests with Fabric pipelines, transforms in notebooks, and reports in Power BI without leaving Fabric.

Interview tip: Compare it with the Azure stack: Fabric is SaaS, while ADF, Databricks and Synapse are separate PaaS services.

Q111. What is a Fabric capacity?

Answer: A capacity is the pool of compute that Fabric workspaces share, measured in capacity units (CUs).

How it works:

• Bought as F SKUs (F2, F4, F8 and larger).
• All workloads in assigned workspaces consume the same capacity.
• Pay-as-you-go capacities can be paused; reservations give a discount.
• Smoothing and throttling manage short spikes.

Example: Dev workspaces on an F4 capacity, production on an F64.

Interview tip: Mention the Capacity Metrics app for monitoring usage.

Q112. What are OneLake shortcuts?

Answer: Shortcuts are pointers that make data in other locations appear inside OneLake without copying it.

How it works:

• Targets include other OneLake items, ADLS Gen2, Amazon S3, Google Cloud Storage and Dataverse.
• Data stays at the source; Fabric engines read it in place.
• Security uses the source permissions or the shortcut's connection.

Example: A Lakehouse shortcut to an existing ADLS Gen2 Silver folder lets Fabric use it with no migration.

Interview tip: Say shortcuts avoid duplicate copies and make migrations gradual.

Q113. What is Direct Lake mode?

Answer: A Power BI storage mode that reads Delta tables in OneLake directly.

How it works:

• No scheduled import refresh copies the data.
• Performance is close to Import mode because data is loaded into memory on demand.
• When the Delta table changes, the model picks up the changes.
• It can fall back to DirectQuery in some cases.

Example: A Gold sales table in a Lakehouse feeds a Direct Lake semantic model with near-real-time data.

Interview tip: Mention V-Order, which makes Direct Lake reads faster.

Q114. When do you use a Fabric notebook versus Dataflow Gen2?

Answer: Use notebooks for code-heavy or large transformations, and Dataflow Gen2 for low-code transformations.

How it works:

• Notebook: PySpark, Spark SQL, Scala or R; best for large data, complex logic and reusable code.
• Dataflow Gen2: Power Query interface with 300+ transformations; best for analysts and small to medium data.

Example: A data engineer deduplicates 500 million rows in a notebook; an analyst cleans a 10,000-row Excel file in Dataflow Gen2.

Interview tip: Mention cost: very large Dataflow Gen2 jobs can use more capacity than Spark.

Q115. What is the SQL analytics endpoint?

Answer: A read-only T-SQL endpoint that Fabric creates automatically for every Lakehouse.

How it works:

• Exposes Lakehouse Delta tables as SQL tables.
• Supports SELECT, views and functions, but not writes to the tables.
• BI tools and SQL clients connect to it like a SQL Server database.

Example: An analyst queries the Lakehouse orders table from SQL Server Management Studio.

Interview tip: Say writes go through Spark, while reads can use SQL.

Q116. What is V-Order?

Answer: V-Order is a write-time optimization that Fabric applies to Parquet and Delta files.

How it works:

• Applies special sorting, row group distribution and compression.
• Files remain standard Parquet, readable by any engine.
• Makes reads faster for Power BI (Direct Lake) and SQL engines.

Example: Tables written by Fabric Spark with V-Order give faster Direct Lake reports.

Interview tip: Mention the small write-time cost in exchange for faster reads.

Q117. What is Mirroring in Fabric?

Answer: Mirroring continuously replicates a database into OneLake as Delta tables, without building pipelines.

How it works:

• Supported sources include Azure SQL Database, Azure Cosmos DB, Snowflake and others.
• Changes are replicated in near real time.
• Mirrored data can be queried with SQL, Spark or Power BI.

Example: An Azure SQL operational database is mirrored so analysts query fresh data without loading the source.

Interview tip: Compare it with ADF CDC pipelines: mirroring needs much less setup.

Q118. What is Real-Time Intelligence in Fabric?

Answer: The Fabric workload for streaming and event data.

How it works:

• Eventstreams capture events from sources such as Event Hubs or IoT devices.
• An Eventhouse stores events in KQL databases.
• Data is queried with KQL (Kusto Query Language).
• Real-Time Dashboards and Activator alerts act on the data.

Example: Factory sensor events stream into an Eventhouse, and an alert fires when temperature exceeds a limit.

Interview tip: Mention that KQL is excellent for time-series and log analytics.

Q119. How do you do CI/CD in Microsoft Fabric?

Answer: Use Git integration for version control and deployment pipelines to promote content.

How it works:

1. Connect each development workspace to an Azure DevOps or GitHub branch.
2. Developers commit item changes from the workspace.
3. Deployment pipelines promote items from development to test to production.
4. Deployment rules change connections per stage.

Example: A notebook and a semantic model move from dev to prod with the prod Lakehouse connection applied automatically.

Interview tip: Mention that the Fabric REST APIs and fabric-cicd tooling can automate this further.

Q120. What is Azure Synapse Analytics?

Answer: An integrated Azure analytics service that combines data warehousing, big data and pipelines.

How it works:

• Dedicated and serverless SQL pools for SQL analytics.
• Apache Spark pools for big data.
• Pipelines for data integration.
• Synapse Studio as one workspace UI.

Example: A company runs its enterprise data warehouse on a dedicated SQL pool and data science on Spark pools.

Interview tip: Mention that Microsoft's newer investment is in Fabric, which many Synapse customers are evaluating.

Q121. How do you load data into a Synapse dedicated SQL pool?

Answer: Use the COPY INTO statement or PolyBase to load files from ADLS in parallel.

How it works:

1. Land files in ADLS (preferably compressed Parquet or CSV).
2. Load them into a round-robin staging table with COPY INTO.
3. Transform and insert into the final hash-distributed tables with CREATE TABLE AS SELECT (CTAS).

Example: COPY INTO stg.sales FROM 'https://mystorage.blob.core.windows.net/raw/sales/*.parquet' WITH (FILE_TYPE = 'PARQUET')

Interview tip: Say COPY INTO is the recommended, simplest loading method.

Q122. What is CETAS?

Answer: CREATE EXTERNAL TABLE AS SELECT writes a query's result to storage and creates an external table over it.

How it works:

• Runs a SELECT in parallel.
• Writes the result files (for example, Parquet) to ADLS.
• Creates an external table pointing to those files.

Example: A serverless SQL pool uses CETAS to save a cleaned, aggregated dataset as Parquet for other teams.

Interview tip: Mention it as a way to persist results from serverless SQL, which has no regular tables.

Q123. What is OPENROWSET in serverless SQL?

Answer: OPENROWSET queries files in the data lake directly, without creating a table first.

How it works:

• Point it at a file path or wildcard.
• Choose the format: Parquet, CSV or Delta.
• Schema is inferred, or you set it with a WITH clause.

Example: SELECT TOP 10 * FROM OPENROWSET(BULK 'https://mystorage.dfs.core.windows.net/raw/sales/*.parquet', FORMAT = 'PARQUET') AS rows;

Interview tip: Mention filtering on folder partitions with filepath() to reduce data scanned and cost.

Q124. How do you choose a distribution column in a dedicated SQL pool?

Answer: Choose a column that spreads data evenly and is used in joins and aggregations.

How it works:

• Many distinct values (high cardinality).
• Few nulls and no dominant value, to avoid skew.
• Frequently used in JOIN and GROUP BY.
• Not a column usually filtered to one value, such as a single date.

Example: customer_key for a sales fact table that joins to customers.

Interview tip: Mention checking skew with DBCC PDW_SHOWSPACEUSED.

Q125. What are materialized views and result set caching in Synapse?

Answer: Both speed up repeated queries in a dedicated SQL pool.

How it works:

• Materialized views: store pre-computed aggregates, kept up to date automatically; the optimizer can use them even if the query does not reference them.
• Result set caching: stores the results of a query, so an identical repeated query returns from cache.

Example: A materialized view of daily sales by region makes a heavily used dashboard query much faster.

Interview tip: Mention that cached results are invalidated when the underlying data changes.

7. PySpark, Python and SQL coding interview questions

Short answer: Most Azure data engineer interviews include a live coding round. Expect window functions, deduplication, top-N per group, nested JSON, joins and Delta MERGE. Explain your logic as you type.

Q126. Write PySpark to calculate a running total of sales per customer.

Answer: Use a window partitioned by customer, ordered by date, from the first row to the current row.

from pyspark.sql import Window
from pyspark.sql.functions import sum as sum_
w = (Window.partitionBy("customer_id").orderBy("order_date")
     .rowsBetween(Window.unboundedPreceding, Window.currentRow))
df = df.withColumn("running_total", sum_("amount").over(w))

How it works:

• partitionBy restarts the total for each customer.
• orderBy sets the sequence of orders.
• rowsBetween(unboundedPreceding, currentRow) sums all rows up to the current one.

Interview tip: Import sum with an alias so it does not hide Python's built-in sum.

Q127. Write PySpark to flatten nested JSON with an array of items.

Answer: Read the JSON, explode the array into rows, then select the nested fields.

from pyspark.sql.functions import explode, col
raw = spark.read.option("multiLine", True).json("abfss://raw@mystorage.dfs.core.windows.net/orders/")
items = (raw.select("order_id", explode("items").alias("item"))
         .select("order_id", col("item.sku"), col("item.qty"), col("item.price")))

How it works:

• multiLine reads JSON documents that span several lines.
• explode creates one row per array element.
• Dot notation (item.sku) reads fields from the struct.

Interview tip: Mention explode_outer to keep orders whose items array is empty or null.

Q128. Write PySpark to upsert new data into a Delta table.

Answer: Use the Delta MERGE API to update matching rows and insert new ones.

from delta.tables import DeltaTable
target = DeltaTable.forName(spark, "silver.customers")
(target.alias("t")
   .merge(updates.alias("s"), "t.customer_id = s.customer_id")
   .whenMatchedUpdateAll()
   .whenNotMatchedInsertAll()
   .execute())

How it works:

• The merge condition matches rows on the business key.
• Matched rows are updated; unmatched rows are inserted.
• This is an SCD Type 1 upsert.

Interview tip: Deduplicate updates first, because duplicate source keys make MERGE fail.

Q129. Write Python to flatten a nested dictionary (for example, an API response).

Answer: Use a recursive function that joins parent and child keys with a dot.

def flatten(d, parent=""):
    out = {}
    for k, v in d.items():
        key = f"{parent}.{k}" if parent else k
        if isinstance(v, dict):
            out.update(flatten(v, key))
        else:
            out[key] = v
    return out

How it works:

• For each key, build the full key path.
• If the value is a dictionary, call the function again (recursion).
• Otherwise store the value under the full key.

Interview tip: flatten({"id": 1, "address": {"city": "Hyderabad"}}) returns {"id": 1, "address.city": "Hyderabad"}; say how you would handle lists too.

Q130. Write SQL to find the top 3 products by revenue in each category.

Answer: Aggregate revenue per product, rank inside each category, and keep ranks 1 to 3.

SELECT category, product, revenue
FROM (
  SELECT category, product, SUM(amount) AS revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rnk
  FROM sales
  GROUP BY category, product
) t
WHERE rnk <= 3;

How it works:

• GROUP BY calculates revenue per product.
• PARTITION BY category ranks within each category.
• The outer query filters the top 3.

Interview tip: Explain why you chose DENSE_RANK over ROW_NUMBER (ties).

Q131. Write SQL to delete duplicate rows and keep the latest one.

Answer: Number duplicates with ROW_NUMBER, newest first, and delete rows numbered above 1.

WITH d AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM customers
)
DELETE FROM d WHERE rn > 1;

How it works:

• PARTITION BY email groups duplicates.
• ORDER BY created_at DESC makes the newest row number 1.
• Deleting through the CTE removes the older copies (SQL Server and Azure SQL syntax).

Interview tip: In Delta Lake, write the deduplicated result back with MERGE or overwrite the table.

Q132. Write PySpark to find customers with more than 5 orders.

Answer: Group by customer, count orders, and filter the count.

from pyspark.sql.functions import count
result = (orders.groupBy("customer_id")
          .agg(count("*").alias("order_count"))
          .filter("order_count > 5"))

How it works:

• groupBy groups rows per customer.
• agg(count(...)) counts orders.
• filter works like SQL HAVING.

Interview tip: Write the SQL version too: GROUP BY customer_id HAVING COUNT(*) > 5.

Q133. Write PySpark to join orders with customers and fill missing values.

Answer: Use a left join to keep every order, then fill nulls from unmatched customers.

df = (orders.join(customers, on="customer_id", how="left")
      .fillna({"country": "Unknown"}))

How it works:

• how="left" keeps orders even when the customer is missing.
• fillna replaces nulls in chosen columns with defaults.

Interview tip: Mention checking how many orders had no customer; it may be a data quality issue.

Q134. Write PySpark to add the previous month's sales next to each row.

Answer: Use lag over a window partitioned by product and ordered by month.

from pyspark.sql import Window
from pyspark.sql.functions import lag
w = Window.partitionBy("product").orderBy("month")
df = df.withColumn("prev_sales", lag("sales").over(w))

How it works:

• lag("sales") takes the value from the previous row in the window.
• The first month per product gets null.

Interview tip: Add a growth column next: (sales - prev_sales) / prev_sales.

Q135. Write PySpark to read a CSV file with a fixed schema.

Answer: Define a StructType schema and pass it to the reader instead of inferring it.

from pyspark.sql.types import StructType, StructField, IntegerType, StringType
schema = StructType([StructField("id", IntegerType()), StructField("name", StringType())])
df = (spark.read.option("header", True).schema(schema)
      .csv("abfss://raw@mystorage.dfs.core.windows.net/customers/"))

How it works:

• A fixed schema avoids an extra pass over the data to infer types.
• Wrong types become nulls (or fail, with mode="FAILFAST").

Interview tip: Mention badRecordsPath or _rescued_data in Databricks to capture bad rows.

Q136. Write PySpark to pivot sales by month.

Answer: Group by product, pivot on month, and aggregate the amount.

months = ["2026-01", "2026-02", "2026-03"]
pivot = sales.groupBy("product").pivot("month", months).sum("amount")

How it works:

• pivot("month") turns each month value into a column.
• Passing the list of values avoids an extra job to find distinct months.

Interview tip: Mention unpivot (or stack) for the reverse operation.

Q137. Write PySpark to save a DataFrame as a partitioned Delta table.

Answer: Write with the Delta format, choose a mode and partition column, and save as a table.

(df.write.format("delta").mode("overwrite")
   .partitionBy("order_date").saveAsTable("silver.orders"))

How it works:

• mode("overwrite") replaces the data; append adds to it.
• partitionBy creates a folder per date.
• saveAsTable registers the table in the catalog.

Interview tip: Mention replaceWhere to overwrite only selected partitions.

Q138. Write Auto Loader code to ingest new JSON files into a Bronze table.

Answer: Read with the cloudFiles source and write as a stream with a checkpoint.

df = (spark.readStream.format("cloudFiles")
      .option("cloudFiles.format", "json")
      .option("cloudFiles.schemaLocation", "/checkpoints/orders/schema")
      .load("abfss://raw@mystorage.dfs.core.windows.net/orders/"))
(df.writeStream.option("checkpointLocation", "/checkpoints/orders")
   .trigger(availableNow=True).toTable("bronze.orders"))

How it works:

• schemaLocation stores the inferred schema and tracks changes.
• The checkpoint records which files were loaded.
• availableNow=True processes all new files and then stops, like a batch job.

Interview tip: Explain that you schedule this with Workflows or ADF for incremental batch loads.

Q139. Write SQL to find employees who earn more than their manager.

Answer: Self-join the employees table to itself on the manager ID.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

How it works:

• Alias e is the employee and m is the manager.
• The join links each employee to their manager's row.
• The filter compares the two salaries.

Interview tip: Mention that employees without a manager are excluded by the inner join.

Q140. Write SQL to calculate a running total of sales by date.

Answer: Use SUM as a window function ordered by date.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM sales;

How it works:

• ORDER BY order_date sets the sequence.
• ROWS UNBOUNDED PRECEDING sums from the first row to the current row.

Interview tip: Mention the difference between ROWS and RANGE when dates repeat.

Q141. Write SQL to find customers who never placed an order.

Answer: Left join customers to orders and keep rows with no match.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

How it works:

• The left join keeps every customer.
• Customers with no orders get nulls in the order columns.
• The filter keeps only those customers.

Interview tip: Mention NOT EXISTS as an alternative that handles nulls safely.

Q142. Write SQL to calculate month-over-month revenue change.

Answer: Use LAG to bring the previous month's revenue into the current row.

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

How it works:

• LAG(revenue) returns the previous month's value.
• The first month returns null because there is no earlier row.

Interview tip: Add the percentage change and guard against division by zero.

Q143. Write SQL to find the third-highest salary.

Answer: Rank distinct salaries with DENSE_RANK and keep rank 3.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3;

How it works:

• DENSE_RANK gives equal salaries the same rank with no gaps.
• Filtering rnk = 3 returns the third distinct salary.

Interview tip: Mention that this works for any N by changing one number.

Q144. Write Python to count the most common words in a file.

Answer: Read the file, split it into words and count them with Counter.

from collections import Counter
with open("log.txt") as f:
    counts = Counter(f.read().lower().split())
print(counts.most_common(5))

How it works:

• lower() makes counting case-insensitive.
• split() breaks the text into words.
• most_common(5) returns the five most frequent words.

Interview tip: For very large files, read line by line instead of loading the whole file.

Q145. Write Python to read every page of a paginated REST API.

Answer: Loop until the API stops returning a next-page link.

import requests
rows, url = [], "https://api.example.com/orders?page=1"
while url:
    data = requests.get(url, timeout=30).json()
    rows.extend(data["items"])
    url = data.get("next")

How it works:

• Each response returns a page of items and a next link.
• The loop collects items and follows next until it is empty.

Interview tip: Mention retries, rate limits and raise_for_status() for production code.

8. Scenario-based Azure data engineer interview questions

Short answer: Scenario questions check judgement. Explain your approach, the trade-offs and how you would verify the result.

Q146. A daily pipeline that took 20 minutes now takes 2 hours. How do you troubleshoot it?

Answer: Find which step slowed down, find out why, fix the biggest bottleneck and compare run times.

How it works:

1. Compare activity durations in the run history to find the slow step.
2. Check data volume growth at the source.
3. For copies: check source query time, IR load and throughput.
4. For Spark: check the Spark UI for skew, spills, small files and missing broadcast joins.
5. Fix one thing at a time and measure again.

Example: The source table tripled in size and the job did a full load; switching to an incremental load brought it back to 15 minutes.

Interview tip: Interviewers want a method, not a guess; walk through the steps in order.

Q147. Source files sometimes arrive late. How do you design for that?

Answer: Trigger on arrival instead of fixed times, and make loads safe to rerun.

How it works:

• Use storage event triggers or Auto Loader instead of fixed schedules.
• Make loads idempotent with MERGE on business keys.
• Reprocess a rolling window (for example, the last 3 days) to catch late data.
• Alert if a file has not arrived by the SLA time.

Example: A 7 AM check alerts the team if the vendor file is still missing, and the load runs automatically when it lands.

Interview tip: Mention idempotency; it is the key word interviewers listen for.

Q148. The same file was loaded twice. How do you prevent duplicates?

Answer: Track which files were processed and load with MERGE instead of plain appends.

How it works:

• Keep processed file names (and checksums) in a control table, or rely on Auto Loader checkpoints.
• Use MERGE on a business key so repeated rows update instead of duplicate.
• Add a uniqueness check after the load.

Example: Auto Loader skips orders_20261020.json because its checkpoint already lists it.

Interview tip: Also explain how you would clean the duplicates that already loaded.

Q149. A new column appeared in the source. What happens and how do you handle it?

Answer: This is schema drift. Decide whether to accept the column, then handle it on purpose.

How it works:

• Delta schema enforcement fails the write by default.
• In Bronze, allow evolution with mergeSchema or Auto Loader schema evolution.
• In ADF data flows, turn on "Allow schema drift".
• Review the change, then add the column to Silver and Gold models deliberately.

Example: Auto Loader adds the new discount column to Bronze and alerts the team; Silver is updated after review.

Interview tip: Say you never let schema changes flow into Gold without review.

Q150. How would you reduce the cost of an Azure data platform?

Answer: Measure where the money goes, then cut idle compute, wasteful processing and expensive storage.

How it works:

• Use job clusters and auto-termination; avoid always-on clusters.
• Right-size clusters and use autoscaling.
• Process incrementally instead of full reloads.
• Compact small files and move old data to cooler tiers.
• Pause Synapse or Fabric capacity when idle, and use reservations for steady use.

Example: Moving nightly jobs from an all-purpose cluster to job clusters and switching to incremental loads can cut compute costs a lot.

Interview tip: Give numbers from your own project if you can.

Q151. A Spark job fails with out-of-memory errors. What do you do?

Answer: Find whether the driver or an executor runs out of memory, then fix the cause before adding memory.

How it works:

• Driver OOM: usually collect(), toPandas() or a large broadcast; remove them.
• Executor OOM: usually skew or huge partitions; check the Spark UI.
• Increase partitions, fix skew, avoid caching large data unnecessarily.
• Only then increase executor or driver memory.

Example: A toPandas() on 50 million rows crashed the driver; writing the result to Delta instead fixed it.

Interview tip: Say "first I check whether it is the driver or an executor".

Q152. Too many small files are slowing down queries. How do you fix it?

Answer: Compact existing files and change how data is written.

How it works:

• Run OPTIMIZE to compact files.
• Turn on optimized writes and auto compaction for Delta tables.
• Write in larger batches instead of tiny frequent writes.
• Avoid partitioning by high-cardinality columns.

Example: A streaming job wrote thousands of tiny files per day; auto compaction plus a daily OPTIMIZE fixed query times.

Interview tip: Explain why small files hurt: too much metadata and too many tasks.

Q153. The business now wants data every 5 minutes instead of daily. How do you redesign?

Answer: Move from daily batch to streaming or frequent micro-batches.

How it works:

1. Capture changes at the source (CDC, Event Hubs or file events).
2. Ingest with Auto Loader or Structured Streaming into Bronze.
3. Use MERGE into Silver so updates are handled safely.
4. Set a 5-minute trigger interval.
5. Monitor latency, cost and small files.

Example: Order events go through Event Hubs, and a streaming job updates the Gold sales table every 5 minutes for a Direct Lake report.

Interview tip: Ask what "real time" really means to the business; it affects cost.

Q154. The source system hard-deletes rows. How do you capture deletes?

Answer: Use change data capture, or compare snapshots to find missing rows.

How it works:

• CDC at the source (such as SQL Server CDC) records deletes.
• Snapshot comparison: an anti-join between the full source and the target finds deleted keys.
• In the target, mark rows with is_deleted = true (soft delete) to keep history.

Example: A nightly anti-join finds 120 customer IDs missing from the source and flags them as deleted.

Interview tip: Mention that watermark-based incremental loads cannot see deletes on their own.

Q155. How do you implement SCD Type 2 in Databricks?

Answer: Use MERGE to close old rows and insert new versions, or let Lakeflow Declarative Pipelines do it.

How it works:

1. Compare incoming rows with current rows on the business key.
2. For changed rows, set is_current = false and end_date on the old version.
3. Insert the new version with is_current = true and a new start_date.
4. Insert brand-new keys as current rows.

Example: A customer's address change closes row 501 and inserts row 734 with the new address.

Interview tip: Mention APPLY CHANGES / AUTO CDC ... STORED AS SCD TYPE 2 in declarative pipelines as the low-code option.

Q156. A report shows wrong totals after a load. How do you investigate?

Answer: Trace the numbers backwards layer by layer until you find where they change.

How it works:

1. Reconcile row counts and sums between source, Bronze, Silver and Gold.
2. Look for duplicates and join fan-out (duplicate keys multiplying rows).
3. Check filters, time zones and late-arriving data.
4. Compare table versions with Delta time travel.
5. Fix, backfill and add a data quality check.

Example: A duplicate product row doubled the sales of one product in Gold; deduplicating the dimension fixed it.

Interview tip: End with how you prevented it from happening again.

Q157. How do you add data quality checks to a pipeline?

Answer: Define rules for each layer, check them automatically, and decide what happens to bad data.

How it works:

• Rules: schema, not-null keys, valid ranges, uniqueness and referential integrity.
• Tools: Delta Live Tables / Lakeflow expectations, Great Expectations, or SQL checks.
• Actions: warn, drop and quarantine bad rows, or fail the pipeline.
• Track quality metrics over time and alert the data owner.

Example: Orders with a null customer_id go to a quarantine table, and the pipeline alerts if more than 1% are rejected.

Interview tip: Mention who owns the fix: the source team or the data team.

Q158. How would you migrate an on-premises SQL Server data warehouse to Azure?

Answer: Plan, migrate in phases, run old and new side by side, and cut over after reconciling.

How it works:

1. Assess: inventory tables, ETL jobs, reports and dependencies.
2. Land data: ADF with a self-hosted IR copies data to ADLS.
3. Rebuild: recreate transformations in Databricks or Fabric.
4. Load history, then switch to incremental loads.
5. Parallel run: compare outputs and reports.
6. Cut over and decommission the old system.

Example: SSIS packages are rebuilt as ADF pipelines and Databricks notebooks, migrated one subject area at a time.

Interview tip: Mention a rollback plan and stakeholder sign-off.

Q159. How do you load 500 tables with one pipeline?

Answer: Build a metadata-driven pipeline driven by a control table.

How it works:

1. A control table lists each source table, target, load type and watermark.
2. A Lookup reads active rows.
3. A ForEach runs parameterized Copy activities in parallel batches.
4. Each run logs status, row counts and the new watermark.
5. Failed tables can be rerun alone.

Example: 500 tables load nightly with a ForEach batch count of 20, and only 3 failed tables are rerun the next morning.

Interview tip: Mention source system limits, so you do not overload the database with too many parallel queries.

Q160. How do you handle PII and sensitive data?

Answer: Find it, restrict it, mask it and audit access to it.

How it works:

• Classify: scan with Microsoft Purview to find PII columns.
• Restrict: Unity Catalog grants, row filters and column masks.
• Protect: mask, hash or tokenize sensitive values; encrypt at rest and in transit.
• Audit: log and review who accessed what.

Example: Phone numbers are masked for analysts and visible only to the customer service group.

Interview tip: Mention compliance requirements such as India's DPDP Act or GDPR.

Q161. A pipeline must start only after a file arrives and another pipeline finishes. How do you design it?

Answer: Combine an event or dependency trigger with checks inside a parent pipeline.

How it works:

• Use a storage event trigger for the file.
• Use tumbling window trigger dependencies for the upstream pipeline.
• Or build a parent pipeline: run the upstream pipeline with Execute Pipeline (wait on completion), then check for the file with Get Metadata or Until, then run the load.

Example: The parent pipeline waits for the "load customers" pipeline, then polls for the orders file before building Gold.

Interview tip: Mention timeouts and alerts so the pipeline does not wait forever.

Q162. A Spark join is very slow. What do you check?

Answer: Read the physical plan and the Spark UI, then reduce shuffle and skew.

How it works:

1. Run df.explain() to see the join strategy.
2. Check whether one side is small enough to broadcast.
3. Filter rows and select columns before the join.
4. Check for skewed keys and null keys.
5. Make sure join key data types match.
6. Confirm AQE is enabled.

Example: A join on string vs integer keys caused casting and a full shuffle; fixing the types and broadcasting the small table made it many times faster.

Interview tip: Mention partition and file layout too, such as clustering both tables on the join key.

Q163. How do you reprocess one day of bad data?

Answer: Rerun the pipeline for that date only, overwriting just that day's data.

How it works:

• Pipelines take the processing date as a parameter.
• Overwrite only that partition with Delta replaceWhere, or MERGE the corrected rows.
• Rerun downstream jobs for the same date.
• Record the backfill in the run log.

Example: df.write.mode("overwrite").option("replaceWhere", "order_date = '2026-10-19'").saveAsTable("silver.orders")

Interview tip: Say that idempotent design makes backfills safe and routine.

Q164. How do you design an Azure data platform for disaster recovery?

Answer: Agree RTO and RPO with the business, then protect data, code and infrastructure to meet them.

How it works:

• Data: geo-redundant storage (GRS or GZRS), and Delta deep clones for critical tables.
• Code: everything in Git.
• Infrastructure: Bicep or Terraform so the platform can be rebuilt in another region.
• Runbook: documented, tested failover steps.

Example: RPO of 4 hours and RTO of 8 hours: GZRS storage plus a scripted rebuild of ADF and Databricks in the paired region.

Interview tip: Mention testing the failover; an untested plan is not a plan.

Q165. How do you control which users see which rows?

Answer: Apply row-level security where users query the data.

How it works:

• Databricks: Unity Catalog row filters and column masks, or dynamic views.
• Fabric Warehouse / Synapse: row-level security with security policies.
• Power BI: RLS roles in the semantic model.

Example: A row filter lets each regional manager see only their region's sales.

Interview tip: Say you base filters on group membership, not on individual users.

9. Azure data engineer interview questions by years of experience

Short answer: The questions get harder with experience. Freshers get concepts and SQL, 2–3 year engineers get pipelines and PySpark, 5-year engineers get architecture, and 10-year engineers get platform strategy and leadership.

Q166. What do interviewers ask freshers?

Answer: Freshers are tested on fundamentals, SQL and one project they can explain well.

How it works:

• Concepts: ADF components, ADLS Gen2, Delta Lake, medallion architecture.
• SQL: joins, GROUP BY, window functions.
• PySpark basics: read, filter, join, aggregate, write.
• One end-to-end project, explained in detail.

Example: "Explain your project: where the data came from, how you loaded it, and what the final report shows."

Interview tip: Depth on one project beats a long list of tools on your resume. To present your projects well, use our Azure data engineer resume samples.

Q167. What are Azure data engineer interview questions for 2 years of experience?

Answer: At 2 years, interviewers check that you have built and run real pipelines yourself.

How it works:

• Watermark-based incremental loads.
• Parameterized and metadata-driven pipelines.
• Error handling, retries and alerts.
• PySpark joins, aggregations and window functions.
• Data volumes and run times of your pipelines.

Example: "How many tables did you load, how often, and what did you do when a load failed?"

Interview tip: Prepare real numbers: tables, rows, run times, schedules.

Q168. What are Azure data engineer interview questions for 3 years of experience?

Answer: At 3 years, expect Delta Lake, Spark performance and CI/CD on top of the basics.

How it works:

• Delta MERGE, SCD Type 2 and time travel.
• Spark performance: shuffles, skew, partitions, broadcast joins.
• Auto Loader and streaming basics.
• Git and CI/CD for ADF and Databricks.
• Scenarios such as a slow or failed pipeline.

Example: "Your job slowed from 20 minutes to 2 hours. Walk me through what you check."

Interview tip: Answer scenarios step by step, as you would actually debug.

Q169. What are Azure data engineer interview questions for 4 years of experience?

Answer: At 4 years, interviewers expect end-to-end ownership of pipelines and their design choices.

How it works:

• Why you chose ADF, Databricks or Fabric for a task.
• How you handled failures, late data and reprocessing.
• How you tuned Spark jobs and reduced cost.
• How you set up CI/CD and environments.

Example: "Draw your current architecture and explain one design decision you would change."

Interview tip: Show measurable results, such as "cut run time by 60%".

Q170. What are Azure data engineer interview questions for 5 years of experience?

Answer: At 5 years, the focus shifts to architecture, governance and guiding others.

How it works:

• End-to-end lakehouse architecture.
• Metadata-driven ingestion frameworks.
• Unity Catalog governance and security.
• Streaming design and cost optimization.
• Code reviews and mentoring juniors.

Example: "Design a platform to ingest 50 sources, with batch and streaming, for 200 analysts."

Interview tip: Talk about trade-offs and why, not just tools.

Q171. What are Azure data engineer interview questions for 7 years of experience?

Answer: At 7 years, expect lead-level questions on design reviews, scale and stakeholders.

How it works:

• Reviewing and approving designs from other engineers.
• Performance and reliability at large scale.
• Governance, security and compliance.
• Estimation, planning and stakeholder communication.
• Migrations and leading small teams.

Example: "How would you plan and estimate a migration of 300 SSIS packages?"

Interview tip: Use leadership stories in STAR format.

Q172. What are Azure data engineer interview questions for 10 years of experience?

Answer: At 10+ years, interviews focus on platform strategy, architecture decisions and leadership.

How it works:

• Choosing between Databricks, Synapse and Fabric for an organization.
• Migrating on-premises warehouses or SSIS to Azure.
• Security, compliance, networking and disaster recovery.
• Budgeting and cost governance.
• Leading teams and working with business leaders.

Example: "Our company runs Synapse dedicated pools. Should we move to Fabric? How would you decide?"

Interview tip: Expect few syntax questions; prepare strong "why" and "what would you do" answers.

Q173. What do interviewers ask career switchers?

Answer: Career switchers are asked why they changed, how they learned and what they have built.

How it works:

• Your motivation for data engineering.
• How you learned: course, self-study, projects.
• SQL and Python basics.
• A documented project on GitHub.
• A certification such as DP-700 helps you get shortlisted.

Example: "You worked in testing for 3 years. Why data engineering, and what have you built?"

Interview tip: Link your past experience to data work, such as testing skills for data quality checks.

Q174. Which projects should freshers put on their resume?

Answer: One strong end-to-end project is better than many small tutorials.

How it works:

1. Ingest a real public dataset with ADF.
2. Build Bronze, Silver and Gold layers in Databricks.
3. Add incremental loading and data quality checks.
4. Build a Power BI report on Gold.
5. Put the code and an architecture diagram on GitHub.

Example: "NYC taxi trips: ADF ingests monthly files, Databricks cleans and aggregates them, and Power BI shows revenue by zone."

Interview tip: Be ready for "what problem did you face and how did you solve it?"

Q175. What do interviewers ask Azure data architects?

Answer: Architect interviews test design judgement across the whole platform.

How it works:

• Reference architectures and service selection.
• Networking, identity and security design.
• Governance and data ownership.
• Cost control and capacity planning.
• Disaster recovery.
• Central platform versus data mesh.
• Migration roadmaps.

Example: "Design a secure lakehouse for a bank, with private networking and auditing."

Interview tip: Always state assumptions and trade-offs before drawing the design.

10. Managerial round and senior or lead Azure data engineer questions

Short answer: Managerial rounds test how you design, communicate and lead. Answer with real examples using the STAR method: situation, task, action and result.

Q176. How would you design a data platform for a new client with 50 source systems?

Answer: Start with requirements, then design ingestion, storage, processing, governance and operations.

How it works:

1. Requirements: data volumes, latency (batch or streaming), users and compliance.
2. Ingestion: a metadata-driven ADF framework so one pipeline handles many tables.
3. Storage and processing: ADLS Gen2 with Bronze, Silver and Gold in Databricks.
4. Governance: Unity Catalog for access and lineage, Key Vault for secrets.
5. Operations: Git and CI/CD, monitoring, cost tracking and SLAs.

Example: 45 batch sources through ADF, 5 streaming sources through Event Hubs, all landing in one governed lakehouse.

Interview tip: Ask clarifying questions first; interviewers expect it.

Q177. A production pipeline failed and a business report will be late. What do you do?

Answer: Communicate early, restore the data, then fix the root cause.

How it works:

1. Tell stakeholders quickly, with impact and an expected time to fix.
2. Restore data first: rerun or backfill.
3. Find the root cause.
4. Share a short incident summary.
5. Add prevention: better alerts, data quality checks or retries.

Example: "The vendor changed a file format. We loaded the data with a quick fix by 10 AM, then added schema validation."

Interview tip: Show calm ownership, not blame.

Q178. What should you ask the interviewer at the end?

Answer: Ask questions that show interest in the team's data and how success is measured.

How it works:

• What is your data stack: ADF, Databricks, Fabric?
• How big is the team, and how is work split?
• How do you monitor data quality?
• What does success look like in the first 90 days?

Example: "What is the biggest data challenge the team is facing this year?"

Interview tip: Avoid asking only about salary or leave in technical rounds.

Q179. How do you answer "Tell me about yourself" as a data engineer?

Answer: Give a 60–90 second story: current role, strongest project and why this job.

How it works:

1. Current role, company type and tech stack.
2. One strong project with a measurable result.
3. Why this role is the right next step.

Example: "I'm an Azure data engineer with 3 years' experience. I built a metadata-driven ADF and Databricks platform that loads 200 tables daily and cut run time by 50%. I want to work on larger streaming workloads, which this role offers."

Interview tip: Practise it out loud; it sets the tone for the whole interview.

Q180. How do you explain your current project architecture?

Answer: Walk through the data flow from left to right, then add numbers and your role.

How it works:

1. Sources: databases, APIs, files.
2. Ingestion: ADF or Auto Loader.
3. Storage: ADLS Bronze, Silver and Gold.
4. Processing: Databricks or Fabric.
5. Serving: Synapse, Fabric Warehouse or Power BI.
6. Add volumes, schedules and what you personally built.

Example: "Daily, 120 tables from SAP and SQL Server, about 50 GB, land in Bronze by 2 AM and Gold is ready for Power BI by 6 AM."

Interview tip: Draw it if you can; a diagram makes your answer clearer.

Q181. What was the biggest challenge in your project?

Answer: Pick a real technical problem and explain it with the STAR method.

How it works:

• Situation: what was wrong.
• Task: what you had to achieve.
• Action: what you did, step by step.
• Result: the outcome in numbers.

Example: "A daily join took 2 hours because of skew. I found a hot key, salted it and enabled AQE. The run time dropped to 25 minutes."

Interview tip: Choose a problem you solved yourself, not one your team lead solved.

Q182. How do you handle changing requirements?

Answer: Clarify, assess the impact, agree priorities and deliver in small steps.

How it works:

1. Clarify the change with stakeholders.
2. Document it.
3. Estimate the impact on scope and dates.
4. Agree on priority with the product owner.
5. Deliver in small increments.

Example: A new KPI request was added to the next sprint instead of delaying the current release.

Interview tip: Show that you protect quality and deadlines, not just say yes.

Q183. How do you prioritize when many tasks are urgent?

Answer: Rank by business impact and deadline, and agree the order with your manager.

How it works:

• Production issues affecting users come first.
• Then committed deadlines, then improvements.
• Agree the order with your manager.
• Tell stakeholders clearly what will be delayed and why.

Example: A failed finance report load comes before a new dashboard feature.

Interview tip: Mention transparency: no surprises for stakeholders.

Q184. How do you ensure code quality in a data team?

Answer: Use reviews, tests, standards and automation.

How it works:

• Git with pull requests and code reviews.
• Unit tests for transformation logic (for example, with pytest).
• Naming and coding standards.
• Linting and CI checks before merge.
• Data quality tests on outputs.

Example: Every pull request runs pytest on PySpark functions and needs one approval.

Interview tip: Mention testing transformation functions with small sample DataFrames.

Q185. How do you estimate the effort for a new pipeline?

Answer: Break the work into parts, estimate each, compare with past work and add a buffer.

How it works:

• Source analysis and access.
• Ingestion development.
• Transformations and modeling.
• Testing and data validation.
• Deployment and documentation.
• Add a buffer for unknowns, such as source data issues.

Example: "Similar pipelines took 5–7 days, plus 2 days because this source has no documentation."

Interview tip: Say you re-estimate when you learn more, and communicate changes early.

Q186. How do you explain a technical problem to a non-technical manager?

Answer: Lead with business impact, use plain words and offer options.

How it works:

1. Impact first: what is late or wrong, and who is affected.
2. Simple cause, without jargon.
3. Two or three options with time and cost.
4. Your recommendation.

Example: "The sales report will be 2 hours late because a supplier file arrived broken. We can wait for a corrected file or load yesterday's data with a note. I recommend the second."

Interview tip: Practise this; communication often decides senior offers.

Q187. How do you handle a disagreement with a teammate?

Answer: Discuss privately, focus on facts and goals, and find a solution together.

How it works:

• Talk one to one, not in a group.
• Listen to their reasons first.
• Compare options with facts or a quick test.
• Escalate only if you cannot agree.

Example: "We disagreed on partitioning. We tested both on real data and chose liquid clustering based on the results."

Interview tip: Show respect and a data-driven decision.

Q188. How do you onboard a new data engineer?

Answer: Give context, small early wins and regular support.

How it works:

• Share documentation and an architecture walk-through.
• Set up access and tools on day one.
• Give small first tasks.
• Pair them with a buddy.
• Review their first pull requests closely and check in regularly.

Example: First week: run and monitor existing pipelines; second week: add a new table to the metadata framework.

Interview tip: Mention keeping documentation up to date as part of onboarding.

Q189. Why do you want to leave your current company?

Answer: Keep it positive and focused on growth.

How it works:

• Larger scale or more complex data.
• New technology, such as Fabric or streaming.
• More ownership or leadership.
• Never criticize your current employer.

Example: "I've built strong batch pipelines, and I want to work on real-time data at a larger scale, which this role offers."

Interview tip: Connect your reason to what the new role offers.

Q190. What are your salary expectations?

Answer: Research first, then give a range based on your skills and the market.

How it works:

• Check current market ranges for your experience and city.
• Consider your skills: Databricks, Fabric, streaming, certifications.
• Give a range and stay open to the full package.

Example: "Based on my 3 years with ADF and Databricks, I'm looking for ₹X–Y LPA, but I'm open to discussing the full package."

Interview tip: See our Azure data engineer salary in India guide for current ranges.

11. Azure DevOps, security and monitoring interview questions

Short answer: Senior interviews check that you can ship pipelines safely: Git and CI/CD, environments, monitoring, alerting, governance and cost control.

Q191. How is Azure DevOps used in data engineering?

Answer: Azure DevOps manages code, automated deployments and work tracking for data projects.

How it works:

• Azure Repos: Git repositories for ADF, notebooks and SQL.
• Azure Pipelines: CI/CD to test and deploy to test and production.
• Azure Boards: sprint planning and work items.

Example: A merge to main triggers a pipeline that deploys ADF templates and Databricks bundles to test, then to prod after approval.

Interview tip: Mention approval gates before production deployments.

Q192. What are Databricks Asset Bundles?

Answer: A way to define Databricks jobs, pipelines and code as files and deploy them consistently.

How it works:

• A databricks.yml file describes jobs, clusters and settings.
• Targets (dev, test, prod) override environment settings.
• The Databricks CLI validates and deploys: databricks bundle deploy -t prod.
• Works with any CI/CD tool.

Example: The same bundle deploys the nightly job to dev with a small cluster and to prod with a larger one.

Interview tip: Say bundles replace manual job creation in the UI.

Q193. Which Git branching strategy do you use?

Answer: Short-lived feature branches merged into a protected main branch through pull requests.

How it works:

• Create a feature branch per task.
• Open a pull request with review and automated checks.
• Merge to main; main deploys to test.
• Tag releases for production.

Example: feature/add-orders-table → pull request → main → release v1.4.

Interview tip: Mention branch protection rules and required reviewers.

Q194. What is infrastructure as code?

Answer: Defining cloud resources in code files so environments are created the same way every time.

How it works:

• Tools: Bicep, ARM templates or Terraform.
• Templates are stored in Git and reviewed like code.
• Pipelines deploy them to each environment.

Example: A Bicep file creates the storage account, Key Vault, ADF and Databricks workspace for a new environment in minutes.

Interview tip: Mention that IaC also supports disaster recovery.

Q195. How do you manage development, test and production environments?

Answer: Keep environments separate and promote changes through CI/CD.

How it works:

• Separate resources or workspaces for dev, test and prod.
• Environment-specific configuration (parameters, Key Vaults).
• Automated promotion with approvals.
• Least-privilege access in production; no manual changes there.

Example: Developers can edit dev freely, but only the deployment pipeline can change prod.

Interview tip: Mention test data: production-like volumes without exposing real PII.

Q196. What are Azure Monitor and Log Analytics?

Answer: Azure Monitor collects metrics and logs; Log Analytics stores logs and lets you query them with KQL.

How it works:

• Diagnostic settings send ADF, Databricks and Synapse logs to a Log Analytics workspace.
• KQL queries find failures, durations and trends.
• Alerts and workbooks (dashboards) are built on top.

Example: ADFActivityRun | where Status == "Failed" | summarize count() by PipelineName

Interview tip: Write one simple KQL query in the interview; it shows hands-on experience.

Q197. How do you get alerted when a pipeline fails?

Answer: Set up automatic alerts so failures reach the right people quickly.

How it works:

• Azure Monitor alert rules on failed pipeline runs.
• Failure paths in pipelines that call a Logic App for email or Teams.
• Databricks job notifications on failure.
• Route alerts to an on-call channel with clear details.

Example: A failed run posts the pipeline name, error and run link to the team's Teams channel.

Interview tip: Mention avoiding alert fatigue by alerting only on actionable failures.

Q198. What is Microsoft Purview?

Answer: Microsoft's data governance service for cataloging, classifying and tracing data.

How it works:

• Data map: scans sources across Azure, on-premises and Fabric.
• Catalog: lets users search and understand data.
• Classification: finds sensitive data such as PII.
• Lineage: shows how data flows from source to report.

Example: Purview shows that a Power BI report gets its data from a Gold table built from SAP.

Interview tip: Compare it with Unity Catalog: Purview spans the whole organization, Unity Catalog governs Databricks.

Q199. What is the principle of least privilege?

Answer: Give every user and service only the access they need, and nothing more.

How it works:

• Assign roles to groups, not individuals.
• Use the narrowest scope (container, schema or table).
• Separate read and write roles.
• Review access regularly and remove unused rights.

Example: The reporting service can read Gold only; it cannot read Bronze or write anywhere.

Interview tip: Mention Privileged Identity Management for temporary admin access.

Q200. How do you monitor and control Azure data platform costs?

Answer: Make costs visible, set limits, and remove waste continuously.

How it works:

• Budgets and alerts in Azure Cost Management.
• Tags per project and environment for cost reports.
• Job clusters with auto-termination and autoscaling.
• Pause Fabric or Synapse capacity when idle.
• Reservations for steady workloads.
• Regular reviews of DBU usage and storage growth.

Example: A weekly cost report by tag shows dev clusters left running at night, which are then set to auto-terminate after 30 minutes.

Interview tip: Say you treat cost as a metric, like run time and data quality.

12. Company-wise Azure data engineer interview questions

Short answer: Large IT services companies in India test the same core skills (SQL, ADF, Databricks and PySpark) but differ in round structure and emphasis. Processes change by team and project, so check recent candidate reviews on Glassdoor before your interview.

TCS Azure data engineer interview questions

• Usually a technical round, a managerial round and an HR round.
• Commonly reported topics: ADF pipelines and triggers, incremental loads, SQL joins and window functions, and PySpark basics.
• Be ready to explain your project architecture end to end.

Accenture Azure data engineer interview questions

• Technical rounds are often scenario-heavy: failed pipelines, performance tuning and client requirements.
• Expect Databricks, Delta Lake and SQL coding, plus how you work with clients.

Deloitte Azure data engineer interview questions

• Expect consulting-style questions: design a solution, justify tool choices and explain trade-offs.
• Clear communication matters as much as the technical answer.

Infosys Azure data engineer interview questions

• Commonly reported topics: ADF activities, SQL queries, PySpark transformations, and Azure storage and security basics.
• For experienced roles, expect questions on your project's data volumes and optimizations.

Capgemini Azure data engineer interview questions

• Expect ADF, Databricks, SQL and data modeling questions, often with a live SQL or PySpark task.
• Freshers' drives usually include an aptitude and pseudo-code test before interviews.

Cognizant Azure data engineer interview questions

• Commonly reported topics: ADF and Databricks pipelines, SQL window functions, SCD types and Spark optimization.
• Scenario questions on duplicates, late data and failures are common.

13. How to prepare for an Azure data engineer interview

Short answer: Build one strong end-to-end project, practise SQL and PySpark coding daily, and rehearse scenario and managerial answers out loud. For a full month-by-month plan, follow our Azure data engineer roadmap.

1. Build a project: ingest data with ADF, transform it in Databricks into Bronze, Silver and Gold layers, and serve it to Power BI.
2. Practise SQL window functions, deduplication and top-N queries until you can write them without notes.
3. Learn PySpark and Python basics: joins, aggregations, window functions, JSON handling and writing Delta tables.
4. Prepare 5 scenario stories using situation, action and result.
5. Add a certification if you are a fresher or switching careers. See our Azure data engineer certification guide and DP-700 practice questions.
6. Know your market value before HR rounds: see our Azure data engineer salary in India guide.

14. How Brolly Academy helps

• Azure Data Engineer Training in Hyderabad: 2–2.5 months, from ₹9,999, covering Azure Data Factory, Databricks, PySpark, SQL and Python with real-time projects.
• Mock interviews with feedback, resume building for Azure data roles and placement support.
• Classroom, online and hybrid batches in English and Telugu, with a free demo.
• Explore all courses at Brolly Academy.

Frequently asked questions

Sources and further reading

• Microsoft Learn — Azure Data Factory documentation
• Microsoft Learn — Integration runtime in Azure Data Factory
• Microsoft Learn — Azure Data Lake Storage introduction
• Microsoft Learn — What is Microsoft Fabric?
• Microsoft Learn — Azure Databricks documentation
• Delta Lake — Delta Lake documentation
• Apache Spark — PySpark documentation
• Python Software Foundation — Python documentation
• Wikipedia — Apache Spark