Azure Data Engineer Interview Questions
Azure Data Engineer interview questions typically cover eight areas: Azure fundamentals, SQL, Python, data warehousing, ETL/ELT design, Azure Data Factory, Azure Databricks and Spark, and increasingly Microsoft Fabric. Freshers are tested on concepts and SQL logic; experienced candidates face scenario-based questions on pipeline failures, incremental loads, data skew and cost optimisation. Since DP-203 retired in March 2025, DP-700 (Fabric Data Engineer Associate) is the current Microsoft credential.
★★★★★
4.9/5 rated by 1329+ students · Google Verified
Table of Contents
Introduction
Five years ago, you could clear an Azure Data Engineer interview by explaining what a pipeline is and writing a decent JOIN. That interview no longer exists.
Two things changed. First, the supply side. Every training institute in Hyderabad, Bangalore and Pune started producing Azure Data Engineers, so a recruiter posting one opening now sorts through hundreds of profiles that all look identical on paper — same tools listed, same three “projects”, same certification badge. Second, the platform itself moved. Microsoft retired the DP-203 Azure Data Engineer Associate exam on 31 March 2025 and replaced it with DP-700, the Fabric Data Engineer Associate credential. Microsoft Fabric went generally available in 2024, and a large share of enterprises running Azure Synapse are now planning or executing a migration to it.
The practical result: interview panels stopped asking “what is” questions as their main filter and moved to “what did you do when it broke” questions. A hiring manager can tell within four minutes whether you have actually run a pipeline in production or only completed a tutorial. The tell is usually not knowledge — it’s the absence of failure stories.
What companies are actually testing for
Across service companies, product companies and captive GCCs, the assessment tends to break into five layers:
Layer | What is tested | Who gets tested on it |
SQL depth | Window functions, CTEs, query tuning, execution plans | Everyone, always |
Programming | Python for data manipulation, PySpark transformations | Everyone |
Platform knowledge | ADF, Databricks, Synapse, ADLS Gen2, Fabric | Everyone |
Distributed systems reasoning | Partitioning, shuffle, skew, memory management | Mid and senior |
Design and trade-offs | Architecture choices, cost, failure recovery, SLAs | Senior and lead |
Freshers are usually filtered on the first three layers. Experienced candidates get a light pass on layers one to three and then spend most of the interview on layers four and five, where the questions have no textbook answer.
One more shift worth naming honestly: SQL still eliminates more candidates than any Azure service does. Panels routinely report that candidates who can explain Delta Lake time travel confidently cannot write a correct self-join to find the second-highest salary per department. Do not skip fundamentals because they feel beneath your level.
How to use this guide
This article is organised the same way an Azure Data Engineer course is organised — module by module, from Azure fundamentals through to data modelling. There are 108 questions in total. Every question carries four things:
- Answer — what a panel wants to hear, in the length they want to hear it
- Real-time example — the production situation the question comes from
- Interview tip — what to add, or what to avoid saying
- Difficulty level — Beginner, Intermediate or Advanced
After the modules, you’ll find curated shortlists for freshers and experienced professionals, scenario-based questions, HR and manager-round questions, comparison tables, a four-week preparation roadmap, the mistakes that quietly cost people offers, rapid-fire questions, and a 15-question FAQ.
If you are a fresher: read every module in order. Do not jump to Fabric. The panel will not reach Fabric if your SQL round goes badly.
If you are experienced: skim the Beginner questions to check your explanations are crisp, then spend your time on the Advanced and Scenario questions in Modules 5, 6, 8, 9 and 11. Those are where mid-level candidates are separated from senior ones. If you would rather work through these modules with a mentor and lab access instead of alone, structured Azure Data Engineer training in Hyderabad follows the same sequence.
Module-Wise Azure Data Engineer Interview Questions
Module 1 — Azure Fundamentals
Short explanation
Azure fundamentals is the layer everything else sits on. Panels use it as a quick calibration round — three or four questions to check whether you understand the platform or have only used a handful of services inside it. Answers here should be short. Long answers to fundamentals questions signal padding.
Important concepts
Cloud service models (IaaS/PaaS/SaaS), regions and region pairs, Availability Zones, resource groups, Azure Resource Manager and its templates, the tenant → management group → subscription → resource group hierarchy, and basic cost governance.
Q1. What is cloud computing, and how is it different from an on-premises setup?
Difficulty: Beginner
Answer. Cloud computing is renting compute, storage and networking on demand from a provider instead of buying and running your own hardware. The differences that matter for a data engineer are three: you pay for what you consume rather than for peak capacity you provisioned years ago; you can scale up or down in minutes instead of quarters; and the provider handles the physical layer — power, cooling, hardware replacement — so your team’s time goes into the data platform, not the datacentre.
Real-time example. An on-premises SQL Server warehouse sized for the December sales peak sits idle at 20% utilisation for the other eleven months. The same workload on a Synapse dedicated SQL pool can be paused overnight and scaled up only for the peak window.
Interview tip. Give the elasticity and cost points, then stop. Do not recite the five NIST characteristics of cloud computing unless asked — it reads as memorised.
Q2. What is an Azure Region, and what is a Region Pair?
Difficulty: Beginner
Answer. A region is a set of datacentres in one geography connected by a low-latency network — Central India, for example. A region pair is two regions inside the same geography that Azure pairs for resilience, such as Central India and South India. Azure sequences planned platform updates across the pair rather than applying them simultaneously, and in a broad outage it prioritises recovery of one region in each pair. Several replication options, like GRS storage, replicate to the paired region by default.
Real-time example. A Hyderabad team hosts its production data platform in Central India and configures geo-redundant storage on the ADLS account, which asynchronously replicates to South India — satisfying the DR requirement without any cross-geography data movement.
Interview tip. Naming Central India and South India correctly as an actual pair scores better than an abstract answer. It suggests you’ve made real deployment decisions.
Q3. What are Availability Zones, and when do you use them?
Difficulty: Beginner
Answer. Availability Zones are physically separate locations within a single region, each with independent power, cooling and networking. They protect against a datacentre-level failure while keeping latency low enough for synchronous replication. You use them when a workload needs high availability inside a region. Regions protect against regional disasters; zones protect against a single facility failure. They solve different problems and are often used together.
Real-time example. A zone-redundant storage (ZRS) account for a landing zone keeps ingestion running when one Availability Zone goes down, so upstream systems continue writing without the source teams noticing.
Interview tip. The clean one-liner is: zone = within a region, region pair = across regions. Panels ask this specifically because candidates conflate the two.
Q4. What is a Resource Group, and how should you organise resources into them?
Difficulty: Beginner
Answer. A resource group is a logical container for resources that share a lifecycle. The practical rule is that resources you would deploy, update and delete together belong in the same group. A resource group has a location, which stores the group’s metadata, but the resources inside it can live in different regions. Access control, policies, tags and cost views can all be applied at the group level, which is why grouping by lifecycle matters more than grouping by service type.
Real-time example. rg-dataplatform-prod holds the ADF instance, ADLS account, Key Vault and Databricks workspace for production, while rg-dataplatform-dev mirrors it. Tearing down a dev environment is then one delete operation, not thirty.
Interview tip. Mention that a resource can only belong to one resource group at a time, and that resource groups can be used as an RBAC and cost-reporting boundary. That second point is what separates a real answer from a definition.
Q5. What is Azure Resource Manager (ARM)?
Difficulty: Intermediate
Answer. ARM is the deployment and management layer for Azure. Every request — from the portal, CLI, PowerShell, SDKs or REST — goes through ARM, which authenticates it, applies RBAC and Azure Policy, and then routes it to the relevant resource provider. Because all clients hit the same control plane, you get consistent behaviour regardless of tool. ARM also enables declarative, repeatable deployments through templates, plus dependency ordering, tagging and locks.
Real-time example. A team defines its entire data platform in an ARM/Bicep template and deploys identical dev, UAT and prod stacks by changing a parameter file — eliminating the “it works in dev” class of failures caused by hand-configured environments.
Interview tip. If you mention templates, be ready for the immediate follow-up on idempotency and incremental versus complete deployment mode. Know that incremental is the default.
Q6. ARM templates vs Bicep vs Terraform — which would you choose?
Difficulty: Intermediate
Answer. ARM templates are JSON and verbose; Bicep is a Microsoft DSL that compiles to ARM with far cleaner syntax and better tooling; Terraform is multi-cloud with its own state file and provider model. For an Azure-only estate, Bicep is usually the better choice because it is first-party, needs no state management, and supports new Azure features on day one. Terraform wins when you also manage AWS or GCP, or when your organisation has already standardised on it.
Real-time example. A team migrated 4,000 lines of ARM JSON to roughly 900 lines of Bicep and cut onboarding time for new engineers substantially, because the templates became readable.
Interview tip. Never answer this as “X is best”. Answer with the deciding variable — single-cloud versus multi-cloud, and existing organisational standard. Trade-off reasoning is the actual thing being scored.
Q7. Explain IaaS, PaaS and SaaS with data engineering examples.
Difficulty: Beginner
Answer. IaaS gives you virtual machines and you manage the OS and everything above — running SQL Server on an Azure VM, for example. PaaS gives you a managed service where the provider handles the OS, patching and infrastructure while you handle configuration and code — Azure SQL Database, Azure Data Factory, Synapse. SaaS is finished software you consume — Power BI Service, Microsoft 365. Modern Azure data platforms are predominantly PaaS, with IaaS reserved for legacy applications that cannot be re-platformed.
Real-time example. A lift-and-shift migration puts an old SSIS-dependent SQL Server on an Azure VM (IaaS) as a stopgap, then re-platforms the workload to ADF and Azure SQL Database (PaaS) in phase two.
Interview tip. Anchor every category to a service you have used. Generic pizza-as-a-service analogies land badly in technical interviews.
Q8. Explain the Azure hierarchy: tenant, management group, subscription, resource group.
Difficulty: Intermediate
Answer. A tenant is the Entra ID (formerly Azure AD) identity boundary for an organisation. Management groups sit below it and let you apply policy and RBAC across many subscriptions at once. A subscription is the billing and quota boundary — resource limits apply per subscription. Resource groups sit inside subscriptions and contain the actual resources. Permissions and policies flow downward through this hierarchy and are inherited.
Real-time example. An enterprise creates separate prod and non-prod subscriptions under a shared management group, applies a policy at the management group level blocking public network access on storage accounts, and it takes effect everywhere without per-resource configuration.
Interview tip. The inheritance point is the substance of this answer. Anyone can list the four levels; explaining that policy at a higher scope cannot be overridden at a lower one is what registers.
Q9. Scenario — Your company has customers in India and Europe. Data residency rules require EU personal data to stay in the EU. How do you design the regional layout?
Difficulty: Advanced
Answer. Split by data domain, not by convenience. EU personal data lands in and is processed in an EU region — West Europe or North Europe — with its own storage account, processing layer and Key Vault. Indian data stays in Central India. Where the business needs global reporting, aggregate or pseudonymise in-region first and move only the non-identifying aggregates across regions. Keep separate resource groups and separate access control per region so a misconfigured role assignment cannot expose cross-region data. Document the boundary explicitly, because auditors will ask for it.
Real-time example. A retail analytics platform keeps EU order-level data in West Europe, computes daily country-level revenue aggregates there, and copies only the aggregate table to Central India for the global dashboard.
Interview tip. Mention egress cost and latency as secondary factors, but lead with compliance. Leading with cost on a residency question suggests you’d let cost override a legal constraint.
Q10. Real-time — How do you control cost on an Azure data platform?
Difficulty: Advanced
Answer. In layers. Compute first, because it dominates the bill: pause Synapse dedicated pools outside business hours, set aggressive auto-termination on Databricks clusters, use job clusters rather than all-purpose clusters for scheduled runs, and right-size rather than defaulting to large. Storage second: lifecycle policies to move cold data to Cool and Archive tiers, and delete intermediate files. Then reduce waste: eliminate redundant full loads in favour of incremental, avoid unnecessary data movement across regions. Finally, govern: tag everything by cost centre, set budgets with alerts, and review the top five line items monthly.
Real-time example. One team found 40% of their Databricks spend came from all-purpose clusters left running by analysts overnight; switching to job clusters for scheduled workloads plus a 20-minute auto-termination on interactive ones removed most of it.
Interview tip. Cost questions are seniority signals. Give a concrete percentage or a specific lever you personally pulled. Vague “we optimised resources” answers are treated as never having owned a budget.
Module 2 — SQL
Short explanation
SQL is the highest-weight module in this entire guide. More Azure Data Engineer candidates fail on SQL than on any Azure service, and it is usually the first live round. Expect to write queries on a shared screen or whiteboard, not just describe concepts.
Important concepts
Joins and their NULL behaviour, CTEs and recursive CTEs, window functions, aggregate versus analytic functions, stored procedures and functions, clustered and non-clustered indexes, execution plans, and query tuning.
Q11. What are the types of JOIN in SQL?
Difficulty: Beginner
Answer. INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table plus matches from the right, filling NULLs where there is no match. RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN returns the Cartesian product. A SELF JOIN is a table joined to itself, typically for hierarchies. ANTI JOIN is not separate syntax but a pattern — a LEFT JOIN filtered on the right side being NULL — used to find rows that do not have a match.
Real-time example. In a daily reconciliation job, a LEFT JOIN from source to target filtered on target.id IS NULL identifies records the load missed.
Interview tip. Mentioning the anti-join pattern unprompted is a small but reliable differentiator — it is the pattern data engineers actually use most in validation work.
Q12. What is the difference between INNER JOIN and LEFT JOIN when NULLs are involved?
Difficulty: Beginner
Answer. INNER JOIN silently drops rows with no match, which is the single most common cause of quietly wrong pipeline output. LEFT JOIN keeps them and marks the missing side NULL. The subtle trap is putting a filter on the right table in the WHERE clause of a LEFT JOIN — that turns it back into an INNER JOIN, because NULL fails the comparison. The condition must go in the ON clause instead to preserve the outer behaviour.
Real-time example. A revenue report dropped 3% of orders because customers with no entry in the region dimension were being lost by an INNER JOIN. Switching to LEFT JOIN with an “Unknown” default surfaced the underlying data quality issue.
Interview tip. Bring up the WHERE-versus-ON trap. It is a standard follow-up, and pre-empting it saves the panel a question.
Q13. CTE vs subquery vs temp table — when do you use each?
Difficulty: Intermediate
Answer. A CTE is a named result set defined for a single statement. It improves readability, supports recursion, and can be referenced multiple times in that statement — but in most engines it is not materialised, so referencing it repeatedly may re-execute the logic. A subquery is inline and fine for simple single-use logic. A temp table is physically materialised, can be indexed, and carries statistics, which makes it the right choice when an intermediate result is large and reused across multiple statements. Rule of thumb: CTE for readability, temp table for performance on large reused intermediates.
Real-time example. A stored procedure using a CTE referenced four times ran the underlying scan four times. Writing the intermediate to an indexed temp table cut runtime from about nine minutes to under one.
Interview tip. The “CTEs are not always materialised” point is the answer that gets you past this question. Many candidates believe CTEs are automatically faster.
Q14. What is a recursive CTE and where have you used one?
Difficulty: Intermediate
Answer. A recursive CTE references itself and consists of an anchor member producing the initial rows, a UNION ALL, and a recursive member that joins back to the CTE, executing until it returns no rows. It is used for hierarchical or graph-shaped data — employee-manager chains, bill-of-materials explosion, category trees, or generating a date sequence. Always set a recursion limit (MAXRECURSION in T-SQL) so cyclic data cannot spin indefinitely.
Real-time example. Building a full organisational reporting chain from a flat employee(id, manager_id) table to compute the number of levels between each employee and the CEO.
Interview tip. Say “anchor member and recursive member” explicitly and mention MAXRECURSION. Both are markers that you have written one, not just read about it.
Q15. Explain ROW_NUMBER, RANK and DENSE_RANK.
Difficulty: Intermediate
Answer. All three assign numbers within a partition based on an ORDER BY. ROW_NUMBER always gives unique sequential numbers, arbitrarily breaking ties. RANK gives tied rows the same number and then skips — 1, 2, 2, 4. DENSE_RANK gives tied rows the same number without skipping — 1, 2, 2, 3. Choose ROW_NUMBER for deduplication where you need exactly one row, RANK when gaps carry meaning as in competition standings, DENSE_RANK for “top N distinct values” questions.
Real-time example. Deduplicating a CDC feed: ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY modified_date DESC) and keeping only rows numbered 1 gives the latest version of each record.
Interview tip. The deduplication use case is what a data engineering panel wants, more than the definition. Lead with the definitions, then immediately give the CDC example.
Q16. Write a query to find the second-highest salary in each department.
Difficulty: Intermediate
Answer. Use DENSE_RANK partitioned by department:
WITH ranked AS (
SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT employee_id, department_id, salary
FROM ranked
WHERE rnk = 2;
DENSE_RANK rather than ROW_NUMBER, because if two people share the top salary, the second-highest distinct salary is what’s being asked for. Departments with only one distinct salary correctly return no row.
Interview tip. Say out loud why you chose DENSE_RANK over ROW_NUMBER. Panels ask this exact question to see whether you reason about ties or pattern-match to a memorised solution.
Real-time example: the same pattern powers “second-largest order per customer” and “runner-up vendor per category” reporting requirements.
Q17. Stored procedure vs function — what is the difference?
Difficulty: Beginner
Answer. A stored procedure is a compiled block of T-SQL invoked with EXEC. It can perform DML, contain transactions, return multiple result sets, and use output parameters. A function must return a value — scalar or table — and cannot modify database state or manage transactions. Functions can be used inline in SELECT, WHERE and JOIN clauses; procedures cannot. Practically, use procedures for ETL logic and functions for reusable calculations.
Real-time example. A usp_LoadDimCustomer procedure performs the SCD Type 2 MERGE, while a scalar function fn_CleanPhoneNumber normalises phone formats inside the SELECT.
Interview tip. Warn about scalar functions in WHERE clauses causing row-by-row execution and killing performance on large tables. That caveat marks production experience.
Q18. Clustered vs non-clustered index — explain the difference.
Difficulty: Intermediate
Answer. A clustered index defines the physical storage order of the table’s rows, so there can be only one per table — the table is the index. A non-clustered index is a separate structure holding the key columns plus a pointer back to the row; you can have many. Non-clustered indexes speed up lookups but must be maintained on every insert, update and delete, so each one is a write-cost trade-off. INCLUDE columns can make an index “covering”, satisfying a query entirely from the index without touching the base table.
Real-time example. A staging table receiving 20 million rows nightly had six non-clustered indexes; dropping them before load and rebuilding afterwards cut load time roughly in half.
Interview tip. The drop-and-rebuild pattern for bulk loads is a strong thing to volunteer — it is a real data engineering practice, not a DBA trivia point.
Q19. How do you approach optimising a slow SQL query?
Difficulty: Advanced
Answer. Measure before changing anything. Read the actual execution plan and look for the expensive operators — table scans where a seek was expected, key lookups, hash joins on large inputs, and especially a large gap between estimated and actual row counts, which signals stale statistics. Then work through the usual causes: missing or unusable indexes, non-SARGable predicates (functions applied to a column in the WHERE clause), implicit type conversions, SELECT *, unnecessary DISTINCT hiding a join duplication problem, and parameter sniffing. Fix one thing at a time and re-measure, because stacked changes make it impossible to know what worked.
Real-time example. A query filtering WHERE YEAR(order_date) = 2025 was scanning the full table; rewriting it as a date range >= ‘2025-01-01’ AND < ‘2026-01-01’ made the index usable and reduced runtime from minutes to under a second.
Interview tip. Use the word “SARGable” and give the date-function example. It is the single most recognisable tuning answer and demonstrates you’ve read a plan.
Q20. Scenario — A nightly aggregation query on a 500-million-row fact table has gone from 20 minutes to 3 hours. Nothing in the code changed. What do you check?
Difficulty: Advanced
Answer. Since the code is unchanged, the cause is data, statistics or environment. Check in this order: has data volume grown sharply or has the distribution skewed, so one partition now dominates? Are statistics stale, causing the optimiser to pick a bad plan? Compare the current execution plan against a known good one — plan regression from parameter sniffing is common. Check for index fragmentation, blocking or a concurrent job now overlapping the window. On Synapse specifically, check whether the distribution key has become skewed and whether the resource class or workload group changed. Then look at platform-level changes: a scaled-down pool, a paused-and-resumed cluster losing cache.
Real-time example. A Synapse fact table hash-distributed on country_id was fine until one country grew to 60% of volume; redistributing on order_id restored even distribution across the 60 nodes.
Interview tip. Structure the answer as an ordered diagnostic checklist rather than a list of possibilities. Panels are testing debugging method, not the specific root cause.
Module 3 — Python
Short explanation
Python is tested for data manipulation and pipeline logic, not for algorithm puzzles. Expect questions on Pandas, JSON, APIs, error handling and file processing — plus PySpark, which is covered in Modules 8 and 9. Fresher rounds often include a small live coding task.
Important concepts
Functions and argument handling, file I/O with context managers, exception handling, core data structures, Pandas DataFrame operations, nested JSON flattening, REST API consumption with pagination and retries, OOP for reusable pipeline components, and generators for memory efficiency.
Q21. Explain *args and **kwargs.
Difficulty: Beginner
Answer. *args collects any number of positional arguments into a tuple; **kwargs collects any number of keyword arguments into a dictionary. They allow functions to accept a variable signature, which is useful for wrappers, decorators and configuration-driven code. The conventional order in a definition is positional parameters, then default parameters, then *args, then **kwargs.
Real-time example. A generic load_to_sink(df, sink_type, **options) function passes connection options straight through to different writers without needing a separate signature per sink.
Interview tip. The names are conventions — the operators * and ** are what matter. Saying that shows you understand the mechanism rather than the idiom.
Q22. How do you read and write files in Python, and why use a context manager?
Difficulty: Beginner
Answer. Use open() inside a with block: with open(path, ‘r’) as f:. The context manager guarantees the file handle is closed even if an exception is raised inside the block, which prevents file handle leaks and locked files in long-running processes. Modes are ‘r’ read, ‘w’ write and truncate, ‘a’ append, ‘rb’/’wb’ for binary. For large files, iterate line by line over the file object rather than calling read(), which loads the whole file into memory.
Real-time example. A 12 GB CSV crashed a VM with f.read(); switching to line-by-line iteration with a batch flush every 50,000 rows brought memory to a few hundred MB.
Interview tip. Mention the memory point without being asked. It is the difference between a scripting answer and a data engineering answer.
Q23. How does exception handling work in Python?
Difficulty: Beginner
Answer. try contains the risky code, except catches specific exception types, else runs only if no exception occurred, and finally always runs and is used for cleanup. Catch specific exceptions rather than bare except:, which swallows everything including KeyboardInterrupt and makes debugging painful. In pipelines, log the exception with context — file name, row number, batch ID — and decide deliberately whether to fail the run or quarantine the record and continue.
Real-time example. An ingestion job wraps per-file processing in a try/except, writes failures to a _quarantine folder with the error message, and completes the remaining files rather than aborting the entire batch.
Interview tip. The fail-fast versus quarantine decision is the interesting part. Say that it depends on whether the downstream consumer can tolerate partial data.
Q24. List vs tuple vs set vs dictionary — when do you use each?
Difficulty: Beginner
Answer. A list is ordered and mutable — general-purpose sequences. A tuple is ordered and immutable, so it can be a dictionary key and is safer for fixed records. A set is unordered with unique elements and gives O(1) membership testing, making it ideal for deduplication and lookups. A dictionary is a key-value mapping with O(1) access by key, used for lookups, configuration and JSON-shaped data.
Real-time example. Validating 2 million incoming IDs against a list of 500,000 valid IDs took several minutes; converting the valid IDs to a set reduced it to a few seconds, because list membership is O(n) and set membership is O(1).
Interview tip. That complexity point is the answer. Definitions alone read like a beginner tutorial; the O(n) versus O(1) contrast reads like someone who has profiled code.
Q25. What is the difference between a Pandas Series and a DataFrame, and how do you handle a file too large for memory?
Difficulty: Intermediate
Answer. A Series is a one-dimensional labelled array — effectively one column. A DataFrame is two-dimensional, a dictionary of Series sharing an index. For files too large for memory, use pd.read_csv(path, chunksize=n) to get an iterator of DataFrames and process chunk by chunk; specify dtype and usecols to cut memory further; convert repeated string columns to category. If chunking is still insufficient, that is the signal to move to Spark rather than fight Pandas.
Real-time example. A 4 GB file processed in 100,000-row chunks with explicit dtypes ran comfortably on a 8 GB worker instead of failing on read_csv.
Interview tip. Naming the point at which you’d abandon Pandas for Spark is a maturity signal. Candidates who insist Pandas can handle anything look inexperienced.
Q26. How do you flatten deeply nested JSON in Python?
Difficulty: Intermediate
Answer. For moderate volumes, pandas.json_normalize() handles nesting through the record_path and meta parameters, flattening arrays into rows and objects into dot-notation columns. For irregular or very deep structures, write a recursive function that walks the dictionary and builds compound keys. At scale, do it in Spark instead, using explode() for arrays and dot notation with select for structs. Always decide explicitly how arrays should behave: exploding creates rows and multiplies the record count, which is usually correct but must be intentional.
Real-time example. An API returning orders with a nested line-items array was normalised with record_path=[‘line_items’] and meta=[‘order_id’,’order_date’], producing one row per line item with order attributes carried down.
Interview tip. Mention the row-multiplication effect of exploding arrays. It is the mistake that silently inflates fact table counts.
Q27. How do you call a REST API in Python, handling pagination and failures?
Difficulty: Intermediate
Answer. Use the requests library. Handle pagination by looping while the response contains a next-page token or until a page returns fewer records than the page size. Wrap calls with retry logic using exponential backoff for transient failures (429 rate limits, 5xx errors), and respect the Retry-After header when present. Set explicit timeouts, because a request with no timeout can hang a pipeline indefinitely. Store credentials in Key Vault, never in code. Use a session object to reuse the underlying connection across calls.
Real-time example. A vendor API capped at 100 calls per minute returned 429s under load; adding exponential backoff with jitter and honouring Retry-After made a previously flaky nightly extract fully reliable.
Interview tip. The timeout point is the one candidates almost always miss, and it is the one that causes real production incidents. Say it.
Q28. How would you use OOP concepts in a data pipeline?
Difficulty: Intermediate
Answer. The common pattern is a base class defining the pipeline contract — extract(), transform(), load() — with concrete subclasses per source implementing the specifics. Inheritance gives shared logging, retry and validation behaviour for free; polymorphism lets an orchestrator run any source through the same interface; encapsulation keeps connection details inside the class. The caution is not to over-engineer: three classes to move one CSV is worse than a function.
Real-time example. A BaseIngestor class with SqlServerIngestor, ApiIngestor and SftpIngestor subclasses let the team onboard a new source by writing one subclass instead of copying a 300-line script.
Interview tip. Add the over-engineering caveat. It signals judgement, and panels notice when candidates apply patterns reflexively.
Q29. What is a generator, and why does it matter for data engineering?
Difficulty: Advanced
Answer. A generator is a function that uses yield to produce values lazily, one at a time, holding only the current item in memory rather than the entire collection. It matters because data engineering routinely processes datasets larger than RAM. A generator lets you build a processing pipeline — read, clean, filter, batch — where data flows through without any stage materialising the full set. Generators are single-pass and cannot be indexed, which is the trade-off.
Real-time example. Streaming a 50 GB compressed log file through a generator chain that parses, filters and batches into 10,000-record inserts, with memory staying flat throughout.
Interview tip. Contrast a list comprehension with a generator expression — [x for x in …] versus (x for x in …) — as the concrete illustration. It is a one-character difference with a large memory consequence.
Q30. Scenario — Your Python ingestion script sometimes runs twice due to retries, creating duplicate records. How do you fix it?
Difficulty: Advanced
Answer. Make the operation idempotent so that running it twice produces the same result as running it once. Options, roughly in order of preference: write to a deterministic path derived from the batch key so a re-run overwrites rather than appends; use MERGE or upsert on a business key instead of INSERT; maintain a control table recording processed batch identifiers and skip anything already marked complete; or stage into a temporary location and swap atomically only on success. Deleting-then-inserting within a transaction for the affected partition is also valid. Deduplicating after the fact is a patch, not a fix.
Real-time example. A daily extract writing to /raw/orders/date=2026-03-14/ overwrites its own partition on retry, so duplicates are structurally impossible rather than cleaned up later.
Interview tip. Use the word “idempotent” early and define it in one line. It is the exact term the panel is listening for, and the question exists to test whether you know it.
Module 4 — Data Warehousing
Short explanation
Data warehousing questions test whether you can design a model, not just move data. Panels use this module to separate people who build pipelines from people who build data products. Dimensional modelling knowledge is assumed at mid-level and above.
Important concepts
OLTP versus OLAP, star and snowflake schemas, fact table grain and types, dimension tables and surrogate keys, slowly changing dimensions, and late-arriving data.
Q31. What is the difference between OLTP and OLAP?
Difficulty: Beginner
Answer. OLTP systems handle day-to-day transactions — many small reads and writes, highly normalised, optimised for insert and update speed and row-level consistency. OLAP systems handle analysis — fewer, much larger read queries scanning millions of rows, denormalised into star schemas, optimised for aggregation and often columnar in storage. You do not run analytics on OLTP because the scans lock production tables and the normalised model requires expensive joins.
Real-time example. An e-commerce order system runs on Azure SQL Database (OLTP); a nightly pipeline loads it into a Synapse dedicated SQL pool star schema (OLAP) where the BI team runs year-over-year revenue analysis without touching production.
Interview tip. The “why not just query production” angle is the practical follow-up. Answer it before it’s asked.
Q32. What is a star schema?
Difficulty: Beginner
Answer. A star schema has a central fact table containing measures and foreign keys, surrounded by denormalised dimension tables holding descriptive attributes. It is called a star because of the shape of the diagram. It is the default choice for analytics because queries need only one join per dimension, which keeps query plans simple and fast, and because business users can understand it without a data dictionary.
Real-time example. FactSales with SalesAmount and Quantity, joined to DimCustomer, DimProduct, DimStore and DimDate — a “revenue by product category by month” query touches three tables.
Interview tip. Mention that Power BI and most BI tools are optimised for star schemas specifically. That connects modelling to the consumption layer, which is where the design actually pays off.
Q33. Star schema vs snowflake schema — which do you prefer and why?
Difficulty: Intermediate
Answer. A snowflake schema normalises the dimensions into sub-dimensions — DimProduct splitting out into DimCategory and DimSubCategory. It saves storage and reduces update anomalies but adds joins, which slows queries and complicates the model for end users. Star is the default for analytics because storage is cheap and query performance and usability are not. Snowflake is justified for very large, frequently changing dimensions, or where a shared hierarchy genuinely needs to be maintained in one place. If the distinction is still fuzzy, work through a worked comparison of snowflake schema vs star schema before your modelling round.
Real-time example. A product dimension with 50 million rows and a rapidly changing category hierarchy was snowflaked so category changes were applied in one small table rather than rewriting 50 million rows.
Interview tip. Give the storage-versus-performance trade-off, then commit to star as the default. Refusing to state a preference reads as evasive.
Q34. What are the types of fact table?
Difficulty: Intermediate
Answer. Transaction fact tables record one row per event at the finest grain — one row per order line. Periodic snapshot fact tables record state at regular intervals — daily account balance, monthly inventory. Accumulating snapshot fact tables have one row per process instance with multiple date columns updated as milestones complete — an order moving through placed, packed, shipped, delivered. There are also factless fact tables recording that an event occurred with no measure, such as student attendance, and aggregate fact tables holding pre-summarised data.
Real-time example. An order fulfilment analysis used an accumulating snapshot with order_date, pick_date, ship_date and deliver_date columns updated in place, enabling stage-duration analysis that a transaction fact could not support.
Interview tip. Naming all four types including factless, with one example each, is a strong signal. Most candidates name only transaction facts.
Q35. What is a surrogate key and why not just use the natural key?
Difficulty: Intermediate
Answer. A surrogate key is a meaningless system-generated integer used as the dimension’s primary key, with the business’s natural key stored as a regular attribute. Four reasons to use it: natural keys can change, and a changing primary key breaks every fact row referencing it; SCD Type 2 requires multiple rows per business entity, which a unique natural key cannot support; integer joins are faster and narrower than composite or string keys; and merging two source systems with overlapping natural keys is impossible without one.
Real-time example. After an acquisition, both companies had a customer with ID 1001. Surrogate keys let both load into the same dimension with source_system distinguishing them; natural keys would have collided.
Interview tip. The SCD Type 2 reason is the most important one for a data engineering panel — it links modelling to the loading pattern you’ll be asked about next.
Q36. Explain Slowly Changing Dimensions and their types.
Difficulty: Intermediate
Answer. SCDs describe how you handle attribute changes in dimension data over time. Type 0 never changes the value — fixed attributes like date of birth. Type 1 overwrites the old value, keeping no history; use it for corrections. Type 2 adds a new row with effective-from and effective-to dates and a current flag, preserving full history — the most common in warehousing. Type 3 adds a “previous value” column, keeping only one prior state. Type 4 moves history into a separate history table. Type 6 combines 1, 2 and 3.
Real-time example. A customer moving from Hyderabad to Bangalore: Type 1 would rewrite history so old sales appear to come from Bangalore; Type 2 keeps two rows so historical sales stay correctly attributed to Hyderabad.
Interview tip. Use exactly that “history would be wrong” example. It demonstrates you understand why Type 2 exists rather than just what it does.
Q37. How do you implement SCD Type 2 in a pipeline?
Difficulty: Advanced
Answer. Compare incoming records to the current version in the dimension, matching on the business key against rows where is_current = 1. Detect change by comparing tracked attributes — a hash of the concatenated attributes is a common efficient approach. For changed records, perform two operations: update the existing row’s end_date to now and set is_current = 0, then insert a new row with a fresh surrogate key, the new values, start_date of now, end_date as a high date or NULL, and is_current = 1. New business keys insert directly. In Databricks this is a two-step MERGE; in ADF Mapping Data Flows it uses an Alter Row transformation.
Real-time example. A customer dimension using a SHA-256 hash across nine tracked attributes so change detection is one column comparison instead of nine, which materially reduced MERGE time on a 20-million-row dimension.
Interview tip. Mention the hash-based change detection. It’s the practical optimisation that shows you’ve built this rather than described it.
Q38. Scenario — A fact record arrives for a customer who doesn’t exist in the dimension yet. What do you do?
Difficulty: Advanced
Answer. This is a late-arriving dimension, or early-arriving fact. Never drop the fact row — you would lose revenue. The standard solution is an inferred member: insert a placeholder dimension row with the business key and “Unknown” for all attributes, assign it a surrogate key, and link the fact to it. When the real dimension record arrives, update the placeholder in place — the surrogate key is unchanged, so the existing fact rows automatically become correct. Flag inferred members with a column so data quality reporting can track how many exist and how long they persist.
Real-time example. A point-of-sale feed regularly arrived before the nightly customer master sync. Inferred members kept sales totals complete, and a daily report on unresolved inferred members surfaced the sync timing problem that was the real root cause.
Interview tip. Say “inferred member” — it’s the Kimball term the panel is listening for. Also explicitly reject the alternatives of dropping the row or assigning a generic -1 unknown key, and explain why.
Module 5 — ETL & ELT
Short explanation
This module tests pipeline design thinking: how data moves, how you avoid reprocessing everything every night, and what happens when something fails halfway. Scenario questions here carry heavy weight for experienced candidates.
Important concepts
ETL versus ELT, batch versus streaming, full versus incremental loads, watermarking, change data capture, data validation and reconciliation, idempotency, and schema drift.
Q39. What is the difference between ETL and ELT?
Difficulty: Beginner
Answer. In ETL, data is extracted, transformed on a separate processing engine, then loaded into the target. In ELT, raw data is loaded into the target first and transformed there using the target’s compute. ELT has become the default in cloud platforms because storage is cheap enough to keep raw data, and engines like Synapse, Databricks and Fabric have enough compute to transform at scale — which also means the raw layer is preserved and transformations can be re-run without re-extracting from source. ETL still applies where data must be masked or filtered before it can legally land in the target.
Real-time example. A pipeline copies raw files into an ADLS bronze layer untransformed, then Databricks notebooks build silver and gold layers — ELT. A separate feed containing health records is tokenised in transit before landing, because the raw form is not permitted in the analytics store — ETL.
Interview tip. Give the compliance case where ETL is still correct. Answering “ELT is modern, ETL is legacy” is a common oversimplification panels push back on.
Q40. Batch processing vs stream processing — how do you choose?
Difficulty: Beginner
Answer. Batch processes bounded sets of data on a schedule; streaming processes unbounded data continuously as events arrive. The deciding factor is the business latency requirement, not technical preference. If decisions are made on daily or hourly data, batch is simpler, cheaper and easier to reprocess. If decisions need second-level freshness — fraud detection, live inventory, operational alerting — streaming is required. Streaming carries genuine costs: always-on compute, harder debugging, late and out-of-order event handling, and exactly-once semantics complexity.
Real-time example. A retailer runs nightly batch for sales reporting but a streaming pipeline through Event Hubs and Databricks Structured Streaming for real-time stock levels, because overselling online is an immediate business problem.
Interview tip. Lead with “what latency does the business actually need”. Candidates who default to streaming because it sounds advanced are flagged for over-engineering.
Q41. Full load vs incremental load — when do you use each?
Difficulty: Intermediate
Answer. A full load truncates and reloads everything: simple, self-correcting, and acceptable for small or dimension-sized tables. An incremental load moves only new or changed records since the last run: essential once volumes grow, since reloading 500 million rows nightly wastes compute and time. Incremental requires a reliable way to identify change — a modified timestamp, an incrementing ID, or CDC. Most real platforms run incremental daily with a periodic full reconciliation load — weekly or monthly — to catch drift caused by hard deletes or missed updates.
Real-time example. Small reference tables full-load nightly, the 800-million-row transactions table loads incrementally on last_modified_date, and a monthly full reconciliation catches any records the incremental logic missed.
Interview tip. The periodic-full-reconciliation practice is what separates a textbook answer from an operational one. Volunteer it.
Q42. What is a watermark and how do you implement watermark-based incremental loading?
Difficulty: Intermediate
Answer. A watermark is the stored high-water mark of the last successfully processed record — usually a maximum timestamp or ID. On each run you read the stored watermark, query the source for records greater than it, process them, and only after a successful load update the watermark to the new maximum. Storing it in a control table rather than deriving it from the target keeps the logic explicit and restartable. Two cautions: use > on the previous watermark and <= on a fixed run-time boundary to avoid gaps and duplicates at the edges, and be aware that watermarking on a modified date cannot detect hard deletes.
Real-time example. An ADF pipeline reads last_watermark from a control table with a Lookup activity, passes it into a parameterised source query, and updates it via a stored procedure activity only on successful copy completion.
Interview tip. The “update the watermark only after success” point is the whole question. Updating it before the load means a failure silently skips data forever.
Q43. What is Change Data Capture and how do you implement it in Azure?
Difficulty: Advanced
Answer. CDC captures inserts, updates and deletes at the source, including deletes — which timestamp-based incremental loading cannot detect. Implementation options in Azure: native SQL Server or Azure SQL CDC reading from the transaction log; ADF’s built-in change data capture resource and the change-data-capture options in Copy activity for supported sources; Debezium into Event Hubs for a log-based streaming approach; or Fabric Mirroring for near-real-time replication of supported databases into OneLake. Choose based on source support, latency requirement and whether you need deletes.
Real-time example. A finance system required deleted transactions to be reflected in reporting. Timestamp-based loading left them permanently in the warehouse; enabling SQL Server CDC and processing the delete operations fixed the reconciliation break.
Interview tip. The delete-detection point is the reason CDC exists. Say it first, then list the implementation options.
Q44. How do you validate data quality in a pipeline?
Difficulty: Intermediate
Answer. In layers, at defined checkpoints. Structural checks first — schema conformance, expected column presence and data types. Then completeness — source-to-target row counts, control totals on key measures, null checks on required fields. Then validity — referential integrity against dimensions, value ranges, allowed-value sets, duplicate business keys. Then reconciliation against the source’s own totals where available. Decide per check whether failure blocks the pipeline or quarantines records and raises a warning; log every check result to a data quality table so trends are visible rather than just individual failures.
Real-time example. A nightly load compares the source’s own daily control total for order value against the loaded fact total and fails the run if they diverge by more than 0.01% — catching a truncation bug on the second night rather than at the month-end close.
Interview tip. Mention logging results to a table for trending. It converts data quality from a pass/fail gate into something the team can actually manage.
Q45. What does idempotency mean in a data pipeline, and how do you achieve it?
Difficulty: Advanced
Answer. An idempotent pipeline produces the same end state whether it runs once or five times with the same input. It matters because retries are unavoidable — a network blip, a manual re-run, an orchestrator restart. Achieve it by making writes replace rather than append: overwrite a deterministic partition path keyed on the business date, use MERGE on business keys instead of INSERT, delete-then-insert the affected partition inside a transaction, or track processed batch IDs in a control table and skip completed ones. The anti-pattern is a blind append, which makes every retry a data corruption event.
Real-time example. A pipeline writing to /silver/orders/load_date=2026-03-14/ with overwrite mode can be re-run any number of times safely, so an on-call engineer can retry at 3am without checking anything first.
Interview tip. Frame it operationally — “it means my on-call teammate can safely re-run my pipeline without calling me”. Panels remember that framing.
Q46. Scenario — Your source system adds two new columns without notice and your pipeline starts failing. How do you handle schema drift going forward?
Difficulty: Advanced
Answer. Decide first what the policy should be, because “handle it automatically” is not always right. For a raw or bronze layer, be permissive: land the file as-is, or use schema evolution — ADF Mapping Data Flows support schema drift with late binding, and Delta Lake supports mergeSchema on write — so new columns are absorbed and nothing breaks. For curated silver and gold layers, be strict: pin the expected schema, and let unexpected changes raise an alert rather than silently propagate, because a renamed or retyped column can corrupt downstream logic quietly. Add a schema comparison step that logs drift and notifies the owning team. Longer term, fix it at the source with a data contract and change notification agreement.
Real-time example. A team enabled mergeSchema on the bronze Delta table so ingestion never breaks, and added a daily check comparing bronze schema against the silver mapping, which raises a ticket when a new column appears — deliberate adoption rather than automatic propagation.
Interview tip. The permissive-bronze, strict-silver distinction is the answer that gets a senior-level read. Blanket auto-evolution everywhere sounds convenient and is how bad data reaches dashboards.
Module 6 — Azure Data Factory
Short explanation
ADF is the orchestration backbone of most Azure data platforms and the most heavily questioned service in this list after SQL. Panels probe components first, then move quickly to parameterisation, error handling and scale. For a deeper drill on this service alone, see the dedicated set of Azure Data Factory interview questions.
Important concepts
Pipelines, activities, linked services, datasets, the three Integration Runtime types, Copy activity and staged copy, Mapping Data Flows, trigger types, parameters versus variables, metadata-driven design, and failure handling.
Q47. What is Azure Data Factory and what are its core components?
Difficulty: Beginner
Answer. ADF is a cloud ETL/ELT and orchestration service for building and scheduling data movement and transformation workflows. Core components: pipelines, which group activities into a unit of work; activities, the individual steps such as Copy, Lookup, Stored Procedure or Notebook; linked services, which are connection definitions to data stores and compute; datasets, which describe the structure and location of the data within a linked service; integration runtimes, the compute infrastructure that executes the activities; and triggers, which start pipelines. Microsoft’s own Azure Data Factory documentation is the reference to check when a panel asks about a component you have not touched.
Real-time example. A pipeline with a Lookup activity fetching a table list from a control table, a ForEach loop over it, and a Copy activity inside the loop moving each table into ADLS — the entire structure built from four of those components.
Interview tip. Explain the linked service versus dataset relationship precisely, because that is always the next question. Answering both together saves time and looks organised.
Q48. What is the difference between a Linked Service and a Dataset?
Difficulty: Beginner
Answer. A linked service is the connection string — where the system is and how to authenticate to it, such as the ADLS account with its managed identity credential. A dataset is a pointer to specific data inside that system, such as a particular folder path and file format, and it always references a linked service. The relationship is one-to-many: one linked service supports many datasets. In practice you create few linked services and many datasets — or, better, few parameterised datasets.
Real-time example. One ls_adls_prod linked service supports datasets for the bronze, silver and gold containers; the connection and authentication are defined once.
Interview tip. The analogy that works: linked service is the address and key to the building; dataset is the specific room. Then immediately mention that datasets should be parameterised — that’s where the follow-up goes.
Q49. What are the types of Integration Runtime and when do you use each?
Difficulty: Intermediate
Answer. Azure IR is fully managed and serverless, used for cloud-to-cloud movement and for running Mapping Data Flows; you can pin it to a region for data residency. Self-hosted IR is installed on a machine inside your network — on-premises or in a VNet — and is required when a source sits behind a firewall or has no public endpoint; it makes an outbound connection to ADF so no inbound firewall ports are needed. Azure-SSIS IR is a managed cluster that runs existing SSIS packages, used for lift-and-shift migrations rather than new development.
Real-time example. An on-premises SQL Server in a Hyderabad office is reached via a self-hosted IR installed on a server in that network, with the copy destination being ADLS via the same runtime; two nodes are configured for high availability.
Interview tip. The outbound-only connection point for self-hosted IR is worth stating — it answers the security team’s objection before it is raised, which is exactly the kind of thing that lands well in an interview.
Q50. How does the Copy activity work, and what is staged copy?
Difficulty: Intermediate
Answer. Copy activity moves data from a source to a sink, optionally with simple column mapping and format conversion; it does not do complex transformation. Performance is controlled through Data Integration Units, parallel copies, and partitioning of the source read. Staged copy routes data through an interim blob storage location, which is required or beneficial in three cases: when the source and sink cannot connect directly, when copying into a Synapse dedicated SQL pool where PolyBase or COPY INTO requires data staged in storage first, and when compressing data before transferring across a slow link.
Real-time example. Loading on-premises SQL Server data into a Synapse dedicated SQL pool with staged copy enabled, so the load path uses PolyBase from blob rather than a slow row-by-row insert — reducing a 40-minute load to a few minutes.
Interview tip. Say explicitly that Copy activity is not for transformation. Candidates who describe it as an ETL tool get probed until they concede it.
Q51. Copy activity vs Mapping Data Flow — when do you use each?
Difficulty: Intermediate
Answer. Copy activity is for movement with minimal change; it is fast and cheap. Mapping Data Flows provide visual, code-free transformation — joins, aggregates, derived columns, pivots, SCD handling via Alter Row — and execute on a Spark cluster managed by ADF, which means cluster startup time and higher cost. Use Copy for ingestion into a raw layer. Use Mapping Data Flows for transformation when the team prefers a low-code approach. If your team already uses Databricks, doing transformations there is usually cheaper and more maintainable than Data Flows, since you avoid a second Spark environment.
Real-time example. A team using Databricks for all transformation keeps ADF purely for orchestration and Copy — one Spark environment, one place to look when something breaks.
Interview tip. Naming the Data Flow cluster warm-up time as a real design consideration is a small detail that reads as hands-on experience.
Q52. What are the trigger types in ADF?
Difficulty: Beginner
Answer. Schedule triggers run pipelines on a wall-clock schedule and support many-to-many relationships with pipelines. Tumbling window triggers fire for fixed, non-overlapping, contiguous time windows, support backfill by running historical windows, allow dependency chaining between triggers, and have built-in retry — making them the right choice for time-partitioned incremental loads. Event-based triggers fire on blob created or deleted events via Event Grid, used for file-arrival-driven ingestion. A pipeline can also be started manually or through a REST/SDK call.
Real-time example. A tumbling window trigger with hourly windows and a WindowStart/WindowEnd parameter pair loads exactly one hour of data per run, and a three-month backfill is triggered by re-running historical windows rather than writing a separate script.
Interview tip. The backfill and dependency capabilities of tumbling window triggers are the differentiators. Most candidates describe them as “scheduled triggers with extra steps”, which misses the point.
Q53. Parameters vs variables in ADF — what’s the difference?
Difficulty: Intermediate
Answer. Parameters are read-only inputs passed into a pipeline, dataset or linked service at invocation time; their value is fixed for the run. Variables are mutable within a pipeline and are changed using Set Variable or Append Variable activities during execution. Global parameters are defined at the factory level and available to all pipelines, useful for environment-specific values. Parameterising datasets and linked services is what allows one pipeline to serve many tables and one factory definition to be promoted through dev, UAT and prod via CI/CD.
Real-time example. A single parameterised dataset with @dataset().folderPath and @dataset().fileName replaced 40 near-identical dataset definitions.
Interview tip. Mention the concurrency caveat: variables inside a ForEach loop running in parallel are shared at pipeline scope and can produce race conditions. That detail is a strong experience signal.
Q54. What is a metadata-driven pipeline and why build one?
Difficulty: Advanced
Answer. A metadata-driven pipeline stores the configuration for each source — table name, schema, load type, watermark column, destination path — in a control table, and uses one generic parameterised pipeline to process all of them. A Lookup activity reads the control table, a ForEach loop iterates it, and parameterised datasets and activities handle the specifics. Onboarding a new source becomes an insert into a control table rather than building a new pipeline. The benefits are maintainability, consistency of logging and error handling, and vastly reduced deployment surface. The trade-off is higher initial complexity and harder debugging for one-off cases.
Real-time example. 200 source tables handled by three generic pipelines — full load, incremental load, and CDC — driven by a control table, replacing what would otherwise have been 200 individually maintained pipelines.
Interview tip. Say the trade-off out loud. Metadata-driven design is genuinely harder to debug, and acknowledging that is more credible than presenting it as universally superior.
Q55. How do you handle errors and retries in ADF?
Difficulty: Intermediate
Answer. At the activity level, configure retry count and retry interval for transient failures, and set an appropriate timeout. Use the dependency conditions — Success, Failure, Completion, Skipped — to build explicit failure paths, for example a Failure path that writes to an error log table and sends an alert. Note that a pipeline is marked successful if its final activity succeeds, so a failure path that ends in a successful logging activity can mask the failure; use a Fail activity to explicitly mark the pipeline as failed when needed. Inside ForEach loops, decide whether one item’s failure should stop the batch. Monitor centrally by routing ADF logs to Log Analytics and alerting on failure patterns rather than individual runs.
Real-time example. A team’s pipelines were showing green while individual table loads failed, because the Failure path logged the error and completed successfully. Adding a Fail activity at the end of the error path made the pipeline status honest.
Interview tip. That false-green failure behaviour is a real ADF gotcha. Raising it unprompted is one of the strongest ADF signals you can give.
Q56. Scenario — You need to load 200 tables from an on-premises SQL Server into ADLS daily, incrementally. Design the solution.
Difficulty: Advanced
Answer. Use a self-hosted Integration Runtime with at least two nodes for high availability to reach the on-premises server. Build a control table holding each table’s name, schema, watermark column, current watermark value, load type and target path. Use one generic pipeline: a Lookup reads the control table, a ForEach loop iterates with a controlled batch count — parallelism tuned to what the source can tolerate, not the maximum — and inside the loop a Lookup gets the current watermark, a Copy activity runs a parameterised source query filtered on it, and a Stored Procedure activity updates the watermark only on success. Write to partitioned paths like /bronze/{schema}/{table}/load_date=yyyy-MM-dd/ in Parquet for the overwrite-based idempotency. Log each table’s outcome to a run-log table. Authenticate through managed identity and Key Vault. Schedule with a tumbling window trigger so backfill is available.
Real-time example. This exact pattern, with ForEach batch count set to 8 rather than the maximum 50, kept the source OLTP server’s CPU under its threshold during the load window — the constraint was the source system, not ADF.
Interview tip. Volunteering the source-load constraint is the mark of someone who has actually done this. Everyone describes the ForEach; few mention that hammering the source with 50 parallel connections gets you a call from the DBA team.
Module 7 — Azure Data Lake Storage Gen2
Short explanation
ADLS Gen2 questions are typically shorter but security-heavy. Panels want to know you can structure a lake sensibly and secure it correctly — access control is where most candidates give vague answers.
Important concepts
ADLS Gen2 versus Blob storage, hierarchical namespace, medallion zone structure, RBAC versus ACLs versus SAS, managed identity and Key Vault, access tiers and lifecycle management, file sizing and partitioning.
Q57. What is ADLS Gen2 and how does it differ from Blob Storage?
Difficulty: Beginner
Answer. ADLS Gen2 is Azure Blob Storage with the hierarchical namespace feature enabled, plus a Data Lake Storage endpoint. That single feature adds true directories, atomic directory-level operations like rename and delete, and POSIX-style access control lists at file and folder level. Blob storage has a flat namespace where folder-like paths are just prefixes, so renaming a “folder” means copying every object. For analytics workloads, the hierarchical namespace substantially improves performance on directory operations, which matters because Spark and other engines perform many of them.
Real-time example. Renaming a directory containing 100,000 files is a single metadata operation in ADLS Gen2, versus 100,000 copy-and-delete operations in flat blob storage.
Interview tip. Frame it as “Blob plus hierarchical namespace” rather than as a separate product. That is the accurate mental model and it makes the follow-up questions easier to answer.
Q58. What is the hierarchical namespace?
Difficulty: Beginner
Answer. The hierarchical namespace organises objects into a real directory tree rather than a flat list of keys with slashes in their names. It enables atomic, single-operation directory renames and deletes, directory-level ACLs, and faster metadata operations. It must be enabled at storage account creation; it cannot be turned on later without migration. This is the practical reason accounts intended for analytics should be created with it enabled from the start.
Real-time example. A team created a storage account without HNS, built three months of pipelines against it, then had to migrate everything when Databricks performance on directory listings became a bottleneck.
Interview tip. The “cannot be enabled after creation” point is a real operational trap and worth stating.
Q59. How do you structure folders in a data lake?
Difficulty: Intermediate
Answer. Use the medallion pattern as the top-level structure: bronze for raw ingested data in source format, silver for cleaned, conformed and deduplicated data, gold for business-level aggregates ready for consumption. Below that, organise by source system, then entity, then a date-based partition — /bronze/salesforce/accounts/ingest_date=2026-03-14/. Using key=value partition folder naming lets query engines perform partition pruning automatically. Keep separate containers or accounts for each environment, and set access control at the zone level so analysts can reach gold without seeing raw PII in bronze.
Real-time example. A lake with bronze locked to the engineering team’s service principals only, gold readable by the analytics group, and silver in between — a security model expressed through folder structure rather than bolted on afterwards.
Interview tip. The partition-pruning benefit of key=value naming is a specific, checkable detail. Include it.
Q60. Explain RBAC, ACLs and SAS — how do they interact?
Difficulty: Advanced
Answer. RBAC assigns roles at the account, container or resource scope — Storage Blob Data Reader, Contributor, Owner — and is coarse-grained but simple to manage through Entra ID groups. ACLs are POSIX-style read, write and execute permissions applied at individual directory and file level, giving fine-grained control; note that reading a file requires execute permission on every parent directory in the path. SAS tokens are time-limited signed URLs granting specific permissions without an Entra identity, used for external sharing or legacy clients. The interaction matters: RBAC is evaluated first, and a matching RBAC role grants access without ACLs being consulted. ACLs are only evaluated when RBAC does not already grant the permission — so an over-broad RBAC assignment silently defeats your carefully designed ACLs.
Real-time example. A team assigned Storage Blob Data Contributor at the account level for convenience, which made all their per-folder ACL restrictions inert. Removing the account-level role and using scoped RBAC plus ACLs restored the intended isolation.
Interview tip. The RBAC-overrides-ACL evaluation order is the answer that distinguishes real experience here. Almost no one gets to it, and it is the crux of the question.
Q61. How should a pipeline authenticate to ADLS Gen2?
Difficulty: Intermediate
Answer. Managed identity is the preferred method — the ADF or Databricks resource gets an Entra identity, you grant that identity a scoped RBAC role on the storage, and there is no secret to store, rotate or leak. Where a service principal is required, store its client secret in Key Vault and reference it from the linked service rather than embedding it. Account keys should be avoided: they grant full account access, cannot be scoped, and are painful to rotate. SAS tokens are acceptable for time-bound external access with narrow permissions.
Real-time example. ADF’s system-assigned managed identity granted Storage Blob Data Contributor scoped to the bronze container only — no credential in the pipeline definition, and nothing to rotate.
Interview tip. Explicitly ruling out account keys and explaining why is as important as naming managed identity. Security questions test judgement, not vocabulary.
Q62. What are storage access tiers and lifecycle management policies?
Difficulty: Intermediate
Answer. Hot tier has the highest storage cost and lowest access cost, for frequently read data. Cool has lower storage cost with higher access cost and a minimum retention period, for data accessed occasionally. Cold sits below Cool with a longer minimum retention. Archive is cheapest to store but data is offline and must be rehydrated over hours before it can be read. Lifecycle management policies automate transitions based on age or last-modified time — for example, move bronze files to Cool after 30 days, Archive after 180, and delete after 7 years to meet retention policy.
Real-time example. A raw landing zone holding seven years of files applied a lifecycle policy and reduced storage cost materially, since roughly 90% of the data had not been read in over a year.
Interview tip. Mention that Archive rehydration takes hours. Someone who archives data still needed for reprocessing creates an incident, and knowing that boundary matters.
Q63. What is the small file problem and how do you avoid it?
Difficulty: Advanced
Answer. Many tiny files degrade query performance because each file carries listing and open overhead, and Spark tasks spend more time on file management than on data. It typically arises from frequent micro-batch writes or from over-partitioning. Fix it by targeting file sizes in the region of 128 MB to 1 GB; using coalesce or repartition before writing to control output file count; running Delta Lake OPTIMIZE with compaction, or auto-compaction and optimised writes in Databricks; and partitioning on columns with reasonable cardinality — partitioning by date is usually right, by customer ID usually is not.
Real-time example. A streaming job writing every 30 seconds produced roughly 2,900 files per day per table; enabling auto-compaction and scheduling a daily OPTIMIZE reduced a downstream query from around eleven minutes to under two.
Interview tip. Give a target file size range. A specific number is far more convincing than “files shouldn’t be too small”.
Q64. Scenario — Databricks needs to read from ADLS Gen2 securely, and analysts must not see raw PII. Design the access model.
Difficulty: Advanced
Answer. Use Unity Catalog with an access connector and a storage credential mapped to a managed identity, so Databricks accesses storage through a governed identity rather than mounted account keys. Grant that identity scoped RBAC on the containers it needs. Register external locations for bronze, silver and gold, and grant catalog and schema privileges by group: engineering gets bronze, analysts get gold and the non-sensitive parts of silver. For PII specifically, either exclude sensitive columns from the silver and gold layers entirely, or use Unity Catalog column masking and row filters so the same table serves both audiences with different visibility. Avoid legacy DBFS mounts with account keys — they bypass identity-based governance and every user of the cluster inherits the same access.
Real-time example. A platform masking email and phone columns through Unity Catalog column masks lets analysts query the customer table directly while seeing masked values, removing the need for a duplicate redacted table.
Interview tip. Explicitly rejecting the mount-with-account-key approach and saying why is important. It is still common in older codebases, and panels want to know you would not reproduce it.
Module 8 — Azure Databricks
Short explanation
Databricks is where mid-level and senior interviews spend the most time, because it is where production Spark workloads actually run. Expect Delta Lake, Unity Catalog and performance tuning to dominate.
Important concepts
Control plane and data plane architecture, cluster types, notebooks and Workflows, Delta Lake and its features, Unity Catalog governance, Auto Loader and Structured Streaming, Spark optimisation, and cost control.
Q65. What is Azure Databricks and how is its architecture structured?
Difficulty: Beginner
Answer. Azure Databricks is a managed Apache Spark and lakehouse platform, offered as a first-party Azure service with Entra ID integration. Its architecture splits into a control plane, managed by Databricks, which hosts the web UI, job scheduler, notebook metadata and cluster manager; and a data plane in your Azure subscription, where the compute clusters run and where your data stays. That split is the security answer: your data does not leave your subscription boundary, only metadata and commands cross into the control plane.
Real-time example. A security review clears Databricks once the team demonstrates that cluster VMs and ADLS both sit inside the customer VNet, with no data persisted in the control plane.
Interview tip. The control plane / data plane split is the standard architecture question. Lead with it and the security follow-up is already answered.
Q66. What are the cluster types in Databricks and when do you use each?
Difficulty: Beginner
Answer. All-purpose clusters are interactive, shared by multiple users, and stay alive for exploratory notebook work. Job clusters are created for a single job run and terminated when it completes — cheaper per run, isolated, and the correct choice for scheduled production workloads. SQL warehouses are optimised for SQL analytics and BI tool connections, with serverless options for fast startup. The most common cost mistake in Databricks is running scheduled production jobs on all-purpose clusters. A fuller breakdown of the types of clusters in Databricks is worth reading before a Databricks-heavy round.
Real-time example. Moving 30 nightly jobs from a shared all-purpose cluster to job clusters cut the Databricks bill substantially and removed the cross-job interference that had been causing intermittent failures.
Interview tip. Volunteer the cost difference between all-purpose and job clusters. It is the single most useful thing a data engineer can know about Databricks billing.
Q67. What are Databricks Workflows and how do they relate to notebooks?
Difficulty: Beginner
Answer. Notebooks are the development surface — cells of Python, SQL, Scala or R executed against a cluster. Workflows (Jobs) are the orchestration layer: they run notebooks, Python scripts, JARs, dbt projects or SQL tasks as tasks in a DAG, with dependencies, retries, alerting, parameters and scheduling. Notebooks are fine for development but production logic should live in version-controlled code with notebooks as thin entry points, so it is testable and reviewable.
Real-time example. A workflow with a bronze ingestion task, two parallel silver transformation tasks and a gold aggregation task that runs only after both complete — the dependency graph expressed in the job definition rather than in glue code.
Interview tip. Say that production logic should not live entirely inside notebook cells. Panels are wary of candidates whose entire engineering practice is notebook-shaped.
Q68. What is Delta Lake and why use it over plain Parquet?
Difficulty: Intermediate
Answer. Delta Lake is an open storage layer that adds a transaction log to Parquet files. That log gives ACID transactions, so concurrent readers never see a half-written table; schema enforcement, so bad data is rejected rather than silently written; schema evolution when you explicitly allow it; time travel to query previous versions; and support for UPDATE, DELETE and MERGE, which plain Parquet on a data lake cannot do. Plain Parquet has no transaction concept, so a failed write leaves partial files that readers will happily consume.
Real-time example. A job failing halfway through a Parquet overwrite left a dashboard reporting 40% of actual revenue for two hours. On Delta, that write would simply not have been committed.
Interview tip. Use that partial-write failure scenario. It makes the value concrete rather than a feature list.
Q69. Explain Delta Lake time travel, MERGE, OPTIMIZE, Z-ORDER and VACUUM.
Difficulty: Intermediate
Answer. Time travel queries an earlier table version by version number or timestamp, used for auditing, reproducing a report, or recovering from a bad load with RESTORE. MERGE performs upserts and deletes in one atomic statement matched on a key — the foundation of SCD Type 2 and CDC application. OPTIMIZE compacts small files into larger ones to fix the small file problem. Z-ORDER co-locates related data in the same files based on chosen columns, improving data skipping for queries filtering on them. VACUUM physically deletes files no longer referenced by the log beyond a retention threshold — necessary for storage cost, but it destroys time travel beyond that threshold.
Real-time example. A bad upstream file corrupted a silver table; RESTORE TABLE silver.orders TO VERSION AS OF 412 recovered it in seconds instead of a multi-hour reload.
Interview tip. The VACUUM-destroys-time-travel trade-off is the detail worth including. Aggressive vacuuming to save storage has cost teams their recovery option.
Q70. What is Unity Catalog and what problem does it solve?
Difficulty: Intermediate
Answer. Unity Catalog is Databricks’ centralised governance layer. It provides a three-level namespace — catalog, schema, table — across all workspaces in a region, so a table has one identity rather than one per workspace. It gives fine-grained access control with GRANT statements, column masking and row filters; automatic data lineage; auditing; external locations and storage credentials that replace credential-bearing mounts; and Delta Sharing for governed sharing outside the organisation. Before Unity Catalog, permissions were per-workspace and per-cluster, which made consistent governance across a large estate close to impossible.
Real-time example. A company with seven workspaces consolidated onto one Unity Catalog metastore, replacing seven divergent permission models with a single set of group-based grants.
Interview tip. Mention lineage. It is the feature governance and compliance stakeholders care about most, and mentioning it signals you have been in those conversations.
Q71. What is Auto Loader and when would you use it?
Difficulty: Advanced
Answer. Auto Loader (cloudFiles) incrementally and efficiently ingests new files arriving in cloud storage, tracking which files have been processed using either directory listing or a file notification mode backed by Event Grid and a queue. It scales to very large numbers of files without re-listing the whole directory, supports schema inference and schema evolution with a rescued data column for unexpected fields, and provides exactly-once processing through checkpointing. Use it whenever files land continuously or in unpredictable batches; use a plain scheduled read only when the file set is small and predictable.
Real-time example. A partner dropping several thousand files a day at irregular times was handled with Auto Loader in file notification mode, replacing a custom manifest-tracking script that had been the team’s most frequent source of on-call pages.
Interview tip. The distinction between directory listing mode and file notification mode is the follow-up. Notification mode scales better for very high file counts.
Q72. How do you optimise a Spark job in Databricks?
Difficulty: Advanced
Answer. Diagnose from the Spark UI first — find the stage consuming the time and check whether it is shuffle-bound, skewed or spilling. Then apply the appropriate lever: broadcast the smaller side of a join when it fits in memory to eliminate the shuffle; rely on Adaptive Query Execution, which dynamically coalesces shuffle partitions, converts sort-merge joins to broadcast joins, and handles skew by splitting large partitions; fix skew explicitly with salting when AQE is insufficient; cache only datasets genuinely reused multiple times; filter and select columns as early as possible so less data enters the shuffle; keep file sizes healthy with OPTIMIZE and Z-ORDER; and right-size the cluster rather than assuming more nodes is better. Enabling Photon accelerates SQL and DataFrame operations at a higher DBU rate, which is often net cheaper because runtime falls.
Real-time example. A job spending 70% of its time in one shuffle stage was fixed with a broadcast hint on a 400 MB dimension table, cutting runtime from around 50 minutes to under ten.
Interview tip. Start with “look at the Spark UI”. Candidates who jump straight to a list of configuration settings appear to be reciting rather than diagnosing.
Q73. How do you control Databricks cost?
Difficulty: Intermediate
Answer. Job clusters for scheduled work, never all-purpose. Auto-termination on interactive clusters, typically 15 to 30 minutes. Right-size before scaling out — memory-optimised for shuffle-heavy work, compute-optimised for CPU-bound. Enable autoscaling with a sensible minimum and maximum rather than a fixed large cluster. Use spot instances for fault-tolerant workloads with on-demand drivers. Consider Photon where the runtime reduction outweighs the higher DBU rate. Avoid unnecessary recomputation by materialising expensive intermediate results. Finally, tag clusters by team and job so spend is attributable, and review the top consumers monthly.
Real-time example. Enabling spot instances for workers on non-critical batch jobs, with on-demand drivers to avoid job loss, produced a meaningful reduction with no reliability impact.
Interview tip. The spot-workers-with-on-demand-driver pattern is a specific, credible detail. Losing a driver kills the job; losing a worker does not.
Q74. Scenario — A Databricks job that ran in 25 minutes now takes 2 hours. Nothing in the code changed. How do you investigate?
Difficulty: Advanced
Answer. Compare a slow run against a fast one in the Spark UI and find which stage grew. Then work through causes by category. Data: has volume grown, or has distribution skewed so one partition now dominates? Has the number of small input files exploded? Storage: has the Delta table accumulated files without OPTIMIZE, so file listing and scanning dominate? Cluster: did autoscaling settle at fewer nodes, is the cluster now a different instance type, or is it competing with other workloads on a shared all-purpose cluster? Plan: has a broadcast join reverted to sort-merge because a dimension grew beyond the broadcast threshold? Upstream: is a source system slower, or is a dependency now running concurrently in the same window? Check spill to disk as an indicator of memory pressure.
Real-time example. A dimension table crossed the 10 MB default autoBroadcastJoinThreshold, so the join silently switched to a full shuffle. Raising the threshold restored the original runtime.
Interview tip. That broadcast-threshold regression is a real and satisfying answer to give. It also shows you understand that Spark’s plan can change without your code changing.
Module 9 — Apache Spark
Short explanation
Spark questions test distributed systems reasoning. Panels want to know whether you understand what happens across the cluster, not just which API to call. This is where mid-level candidates are separated from senior ones.
Important concepts
RDDs, DataFrames and Datasets; transformations and actions; lazy evaluation; narrow and wide transformations; shuffle; partitioning; caching; the Catalyst optimizer; and data skew.
Q75. RDD vs DataFrame vs Dataset — what’s the difference?
Difficulty: Beginner
Answer. RDD is the original low-level API: a distributed collection of objects with no schema, giving full control but no query optimisation. DataFrame is a distributed collection organised into named columns — conceptually a table — and because it has a schema, it goes through the Catalyst optimizer and Tungsten execution engine, which makes it substantially faster than equivalent RDD code. Dataset adds compile-time type safety and is available in Scala and Java; in Python, DataFrame is the Dataset API. Use DataFrames for essentially all work; drop to RDDs only for unusual low-level operations.
Real-time example. Rewriting a legacy RDD-based transformation as a DataFrame operation halved runtime with no change to logic, purely because Catalyst could then optimise the plan.
Interview tip. The reason DataFrames are faster — the optimizer can see the schema — is the point being tested. “DataFrames are faster” without the why is an incomplete answer.
Q76. What is lazy evaluation, and what’s the difference between a transformation and an action?
Difficulty: Beginner
Answer. Transformations — select, filter, join, groupBy — define a new dataset from an existing one but do not execute; Spark records them in a logical plan. Actions — count, collect, write, show — trigger execution of the accumulated plan. This laziness is what enables optimisation: because Spark sees the whole chain before running anything, Catalyst can push filters down to the source, prune unread columns, and combine operations. The practical implication is that the line that appears to fail is often the action, not the transformation where the actual bug lives.
Real-time example. A team spent an hour debugging a .write() that failed, when the actual error was a bad column reference in a select fifteen lines earlier that had never executed until the write triggered it.
Interview tip. Give the debugging implication. It is the practical consequence of lazy evaluation and it demonstrates lived experience.
Q77. What are narrow and wide transformations, and what is a shuffle?
Difficulty: Intermediate
Answer. A narrow transformation has each output partition depending on exactly one input partition — map, filter, union. No data moves between executors, so it is cheap and pipelined within a stage. A wide transformation requires data from multiple input partitions to produce one output partition — groupBy, join, distinct, repartition. This forces a shuffle: data is written to disk, transferred across the network, and read by other executors. Shuffles create stage boundaries and are the dominant cost in most Spark jobs, because they involve disk I/O, serialisation and network transfer.
Real-time example. Reordering a job to filter out 80% of rows before a groupBy rather than after cut shuffle volume proportionally and roughly halved the runtime.
Interview tip. “Minimise shuffles” is the headline, but say why shuffles are expensive — disk, network and serialisation. That specificity is what’s being scored.
Q78. repartition vs coalesce — what’s the difference?
Difficulty: Intermediate
Answer. repartition(n) performs a full shuffle to produce exactly n evenly-sized partitions, and can increase or decrease the count. coalesce(n) reduces partition count by merging existing partitions on the same executor without a full shuffle, which is much cheaper but can produce uneven partitions. Use coalesce when reducing partitions before a write to avoid many small files. Use repartition when you need even distribution — for example, after a heavy filter has left most partitions nearly empty, or when repartitioning by a column ahead of a join on that column.
Real-time example. A job writing 200 tiny files used coalesce(8) before the write and produced eight healthy files with no shuffle cost.
Interview tip. Note the trap: coalesce(1) on a large dataset forces everything through one task and frequently causes out-of-memory failures. Candidates use it casually to get a single output file.
Q79. When should you cache or persist a DataFrame?
Difficulty: Intermediate
Answer. Cache when a DataFrame is expensive to compute and used more than once — typically an intermediate result feeding several downstream branches, or a dataset used repeatedly in an iterative process. Without caching, Spark recomputes the entire lineage on each action. cache() uses the default MEMORY_AND_DISK storage level; persist() lets you choose. Caching is not free: it consumes executor memory that would otherwise be available for shuffles, so caching a dataset used only once is actively harmful. Unpersist when finished.
Real-time example. A cleaned dataset feeding four separate aggregations was being computed four times; caching it cut total runtime by roughly 60%.
Interview tip. Say explicitly that caching everything is a mistake. Over-caching is the common error, and naming it shows judgement rather than pattern-following.
Q80. What is Spark SQL and what does the Catalyst optimizer do?
Difficulty: Intermediate
Answer. Spark SQL is the module for structured data processing, allowing SQL queries alongside DataFrame operations against the same execution engine. Catalyst is its query optimizer: it takes the unresolved logical plan, resolves it against the catalog, applies rule-based optimisations such as predicate pushdown, column pruning and constant folding, then uses cost-based optimisation to select a physical plan — for instance choosing between broadcast hash join and sort-merge join. Because SQL and DataFrame code compile to the same optimised plan, there is no performance difference between them, which means you should choose whichever is more readable. The Apache Spark SQL programming guide is the authoritative reference if you want to trace how a plan is built.
Real-time example. Predicate pushdown means a filter written after a Parquet read is applied at the file level using footer statistics, so entire row groups are skipped without being read.
Interview tip. The “SQL and DataFrame perform identically” point is a frequent follow-up. Many candidates incorrectly assert one is faster.
Q81. What is data skew and how do you handle it?
Difficulty: Advanced
Answer. Skew occurs when a shuffle key’s values are unevenly distributed, so one or a few partitions hold far more data than the rest. Because a stage finishes only when its slowest task finishes, one huge partition makes the whole job wait — the classic symptom is 199 tasks completing in seconds and one running for an hour. Handle it by enabling Adaptive Query Execution, which automatically splits skewed partitions; broadcasting the smaller side if the join permits it; salting the key by appending a random suffix to the skewed side and replicating the matching rows on the other side; separating the skewed keys and processing them independently; or filtering out junk keys such as a default -1 or empty string that has absorbed millions of unmatched rows.
Real-time example. A join on customer_id where 30% of rows carried a placeholder ID for guest checkouts. Handling that key separately removed the skew entirely — the fix was data quality, not Spark tuning.
Interview tip. Describe the symptom first — one straggler task. It shows you would recognise skew from the Spark UI rather than only knowing the definition.
Q82. Scenario — Your Spark job fails with an out-of-memory error on the executors. What do you do?
Difficulty: Advanced
Answer. Identify which memory is exhausted before changing anything. Common causes, in order of likelihood: skew concentrating too much data in one partition; too few partitions so each is too large; a collect() or toPandas() pulling a large dataset to the driver — though that manifests as driver OOM; caching too much and starving execution memory; an excessively large broadcast; or a wide aggregation with high-cardinality grouping. Fixes accordingly: increase partition count or repartition on a better key, address skew, remove the collect and write to storage instead, unpersist unnecessary caches, lower the broadcast threshold, or move to memory-optimised instances. Raising executor memory is a valid last step, but doing it first means you never learn the real cause and the problem returns at larger volume.
Real-time example. An OOM traced to toPandas() on a 40-million-row DataFrame added for a “quick check” during development and never removed.
Interview tip. Distinguish driver OOM from executor OOM explicitly. It is the first thing a strong engineer would clarify, and panels are watching for it.
Module 10 — Azure Synapse Analytics
Short explanation
Synapse remains widely deployed even as new projects move toward Fabric, so panels still test it — and increasingly ask how you would decide between the two.
Important concepts
The Synapse workspace, dedicated versus serverless SQL pools, table distribution strategies, PolyBase and COPY INTO, Synapse pipelines, Spark pools, and workload management.
Q83. What is Azure Synapse Analytics?
Difficulty: Beginner
Answer. Synapse is an integrated analytics platform combining several engines under one workspace: dedicated SQL pools for provisioned data warehousing, serverless SQL pools for querying files in the lake on demand, Apache Spark pools for big data processing, and Synapse Pipelines for orchestration — the same technology as Azure Data Factory. Synapse Studio provides a single interface across all of them. It was the strategic warehousing platform on Azure before Microsoft Fabric, and it remains fully supported with a large installed base.
Real-time example. A workspace using serverless SQL to explore raw Parquet in the lake, Spark pools for transformation, and a dedicated SQL pool serving the curated star schema to Power BI.
Interview tip. Have a clear position on Synapse versus Fabric ready — it is almost always the follow-up in 2026 interviews.
Q84. Dedicated SQL pool vs serverless SQL pool — what’s the difference?
Difficulty: Intermediate
Answer. A dedicated SQL pool is provisioned MPP compute measured in Data Warehouse Units, with data stored in its own distributed tables. You pay for the provisioned capacity whether or not you query, so it can and should be paused when idle. It is the right choice for predictable, high-concurrency workloads needing consistent performance. A serverless SQL pool has no provisioned infrastructure: it queries files in the data lake directly using T-SQL, and you pay per terabyte of data processed. It suits ad-hoc exploration and intermittent querying, but it cannot store data and performance depends heavily on file format and partitioning.
Real-time example. Analysts explore new raw datasets in serverless before the team commits to building a curated model; only proven, frequently-used datasets get promoted into the dedicated pool.
Interview tip. The pause-when-idle cost lever for dedicated pools is the practical detail. A pool left running overnight and at weekends wastes roughly two-thirds of its cost.
Q85. What are the table distribution types in a dedicated SQL pool?
Difficulty: Advanced
Answer. Data in a dedicated SQL pool is spread across 60 distributions. Hash distributes rows by a hash of a chosen column — the right choice for large fact tables, because joins and aggregations on the distribution column happen locally without data movement. Round robin distributes evenly at random, fast to load but requiring data movement for most joins; suitable for staging tables. Replicate copies the full table to every compute node, ideal for small dimension tables under roughly 2 GB because it eliminates movement in joins. Choosing the hash column well matters enormously: pick a column with high cardinality, even distribution, no NULLs, and one that is frequently used in joins — but not one used in a WHERE filter on a single value, which would concentrate work on one distribution.
Real-time example. A fact table hash-distributed on order_id and joined to a replicated DimDate, eliminating the data movement that dominated the previous round-robin design.
Interview tip. Naming the 60 distributions and the roughly 2 GB replicate guideline gives the answer real specificity. Also mention avoiding a date column as a hash key — it usually skews.
Q86. How do you load data efficiently into a dedicated SQL pool?
Difficulty: Intermediate
Answer. Use COPY INTO, which is the current recommended method — a single T-SQL statement loading from ADLS or blob with support for multiple formats, error file handling and no need for external table objects. PolyBase, using external tables, is the older approach and still performs well. Both are parallel loads that engage all compute nodes, unlike row-by-row inserts which are dramatically slower. Practical guidance: load into a heap or round-robin staging table first, then insert into the final distributed table; use a larger resource class for the loading user so the load gets more memory; split source files so parallelism is available; and create or update statistics after loading, since the optimiser depends on them.
Real-time example. Replacing a row-by-row ADF sink with staged copy plus COPY INTO reduced a 90-minute load to roughly six minutes.
Interview tip. Mention creating statistics after load. It is skipped constantly and it is why “the load was fine but queries are slow” happens.
Q87. What is the relationship between Synapse Pipelines and Azure Data Factory?
Difficulty: Beginner
Answer. Synapse Pipelines use the same underlying technology as Azure Data Factory — the same activity types, linked services, datasets, triggers and Integration Runtime concepts. The differences are contextual: Synapse Pipelines live inside a Synapse workspace with native access to its SQL and Spark pools, while ADF is a standalone service. ADF has some capabilities that Synapse Pipelines lack, including SSIS Integration Runtime support and certain CI/CD and monitoring features. If your work is entirely inside Synapse, use its pipelines; if you orchestrate across many services, standalone ADF is often the better home.
Real-time example. A team kept ADF as the central orchestrator because it also coordinated Databricks, Logic Apps and on-premises sources, and used Synapse only as a query engine.
Interview tip. Do not describe them as unrelated products. Recognising the shared foundation and naming the specific gaps is the accurate answer.
Q88. What is a Synapse Spark pool and how does it differ from Databricks?
Difficulty: Intermediate
Answer. A Synapse Spark pool is a managed Apache Spark cluster inside the Synapse workspace, integrated with the workspace’s storage, notebooks and pipelines, and billed on vCore-hours with auto-pause. Compared with Azure Databricks, Synapse Spark is more tightly integrated with the Synapse SQL pools and simpler if you are already fully within Synapse. Databricks generally offers more mature Spark capability — Photon, Unity Catalog, Delta Live Tables, Auto Loader, a faster runtime and a deeper feature set — and is usually the choice for heavy or complex Spark workloads. Many organisations run both: Synapse Spark for lighter in-workspace tasks, Databricks for the core transformation platform.
Real-time example. A platform using Synapse serverless SQL for lake exploration and Databricks for all production transformation, with ADF orchestrating both.
Interview tip. Avoid tribal advocacy. A balanced answer naming what each does better reads as senior; declaring one universally superior does not.
Q89. What is workload management in a dedicated SQL pool?
Difficulty: Advanced
Answer. Workload management controls how the pool’s fixed concurrency and memory are allocated. It has three parts. Workload classification assigns incoming requests to a workload group based on the login, role or label. Workload importance determines which queued requests run first when concurrency slots are full, so a critical executive report can pre-empt a long ad-hoc query. Workload isolation reserves a guaranteed percentage of resources for a group, so an ETL load window cannot be starved by reporting traffic. Resource classes are the older mechanism controlling memory per query; a larger class gives more memory but reduces concurrency.
Real-time example. Reserving 40% of resources for the ETL service account during the nightly window guaranteed the load completed before the SLA, regardless of overnight reporting activity.
Interview tip. The memory-versus-concurrency trade-off in resource classes is the underlying concept. A larger resource class is not simply “better” — it means fewer queries can run at once.
Q90. Scenario — Your company runs Synapse today and leadership asks whether to move to Microsoft Fabric. How do you answer?
Difficulty: Advanced
Answer. Give a decision framework rather than a verdict. Fabric is Microsoft’s strategic direction, unifying engineering, warehousing, real-time analytics and BI on OneLake with a SaaS model, Direct Lake for Power BI, and simpler capacity-based licensing — so new greenfield builds have a strong case for starting there. For an existing Synapse estate, migration is not free: dedicated SQL pool features do not all map identically, security and networking models differ, and existing pipelines, CI/CD and monitoring need rework. The honest recommendation is usually staged: build new workloads in Fabric, keep stable Synapse workloads running, migrate them when there is a concrete driver — a Power BI performance need served by Direct Lake, a cost consolidation, or a Synapse capability gap. Set a review point rather than committing to a big-bang cutover.
Real-time example. A team started all new domains in Fabric while leaving a mature, performant dedicated SQL pool untouched for eighteen months, migrating only when its Power BI import refreshes became the bottleneck.
Interview tip. Resist saying “move everything to Fabric”. A migration recommendation without a cost, risk and driver assessment is exactly what a manager-round interviewer is testing you for.
Module 11 — Microsoft Fabric
Short explanation
Fabric is now the fastest-changing area of Azure Data Engineer interviews. DP-203 retired on 31 March 2025 and DP-700 (Fabric Data Engineer Associate) is the current Microsoft credential, so panels increasingly expect at least conceptual Fabric fluency even from candidates who have not shipped a Fabric workload. If you have not built anything on the platform yet, start with how Microsoft Fabric handles data pipelines in Azure.
Important concepts
The Fabric SaaS model, OneLake, Lakehouse and Warehouse, shortcuts and mirroring, Dataflows Gen2 versus Data Pipelines, Direct Lake, capacities and workspaces.
Q91. What is Microsoft Fabric?
Difficulty: Beginner
Answer. Fabric is Microsoft’s unified, SaaS analytics platform that brings data engineering, data integration, data warehousing, data science, real-time intelligence and Power BI into one product on a shared storage foundation called OneLake. Rather than provisioning and connecting separate services, you buy a capacity and use whichever workload you need inside a workspace. Storage is standardised on Delta Parquet, so different engines read the same physical data without copying. It went generally available in 2024 and is Microsoft’s strategic direction for analytics.
Real-time example. A single workspace where a pipeline lands data into a Lakehouse, a notebook transforms it, and a Power BI report reads it in Direct Lake mode — no data movement between three separate services.
Interview tip. The phrase to land is “one copy of data, many engines”. That is the actual architectural claim, and it explains why OneLake matters.
Q92. What is OneLake?
Difficulty: Beginner
Answer. OneLake is the single, tenant-wide logical data lake automatically provisioned with Fabric — often described as OneDrive for data. Every workspace gets a folder within it, and every Fabric item that stores data stores it there. It is built on ADLS Gen2 and exposes the same APIs and Delta Parquet format, so external tools can read it. The design goal is eliminating data duplication: instead of each team copying data into their own lake, everyone references one governed copy.
Real-time example. A finance team and a sales team both querying the same conformed customer table in OneLake through different workspaces, with no synchronisation job between them.
Interview tip. Mention the ADLS Gen2 foundation and Delta Parquet standardisation. It reassures the panel you understand Fabric is not a closed box.
Q93. Lakehouse vs Warehouse in Fabric — when do you use each?
Difficulty: Intermediate
Answer. A Lakehouse stores Delta tables plus unstructured files, is developed primarily through Spark notebooks, and exposes a read-only SQL analytics endpoint. Choose it for data engineering work, unstructured or semi-structured data, and teams comfortable with Spark and PySpark. A Warehouse is a full T-SQL relational engine supporting multi-table transactions and read-write DML through SQL, with no Spark required. Choose it for teams whose skillset is SQL, for workloads needing full transactional T-SQL, or when migrating an existing SQL warehouse. Both store data as Delta in OneLake, so they interoperate and can be queried together through cross-database queries.
Real-time example. A team uses a Lakehouse for bronze and silver where PySpark handles messy semi-structured sources, then a Warehouse for the gold layer where the analytics team writes T-SQL stored procedures.
Interview tip. Lead the recommendation with team skillset. Both are technically capable; the deciding factor in practice is who maintains it.
Q94. What are shortcuts and mirroring in Fabric?
Difficulty: Intermediate
Answer. A shortcut is a pointer to data stored elsewhere — another OneLake location, ADLS Gen2, Amazon S3, Google Cloud Storage or Dataverse — that appears in your Lakehouse as if it were local, with no copy and no scheduled sync. It solves the duplication problem for data you do not own. Mirroring continuously replicates an operational database — such as Azure SQL Database, Cosmos DB or Snowflake — into OneLake in near real time as Delta tables, with no pipeline to build or maintain. Shortcuts reference data in place; mirroring creates and maintains a replicated analytical copy.
Real-time example. A team shortcuts an existing ADLS Gen2 bronze zone into Fabric rather than migrating it, and mirrors the production Azure SQL order database so analysts get near-real-time data without an ETL job.
Interview tip. The one-line contrast — shortcuts point, mirroring replicates — is the answer. Candidates frequently blur them.
Q95. Dataflows Gen2 vs Data Pipelines in Fabric — what’s the difference?
Difficulty: Intermediate
Answer. Dataflows Gen2 are Power Query based: a low-code, visual transformation experience aimed at analysts and citizen developers, with a large connector library, well-suited to moderate volumes and business-user-owned logic. Data Pipelines are the orchestration layer — the same lineage as ADF and Synapse Pipelines — handling activity sequencing, control flow, parameterisation, copy at scale and invoking notebooks, dataflows and stored procedures. They are complementary rather than competing: a pipeline commonly orchestrates a dataflow as one of its activities. For large-scale or complex transformation, notebooks generally outperform Dataflows Gen2.
Real-time example. A pipeline copies raw files into the Lakehouse, invokes a notebook for the heavy transformation, and then triggers a Dataflow Gen2 that a business analyst maintains for a small enrichment step.
Interview tip. Say “complementary, not alternatives” explicitly. The question is often phrased as versus, and correcting the framing is a positive signal.
Q96. What is Direct Lake mode in Power BI?
Difficulty: Advanced
Answer. Direct Lake is a Power BI storage mode that reads Delta Parquet files in OneLake directly into the semantic model engine, without importing data into a refreshed model and without translating queries to a source system as DirectQuery does. It aims to give the query performance of Import mode with the freshness of DirectQuery, since there is no refresh window — new data committed to the Delta table is available to the report. It requires Fabric capacity and data in OneLake in Delta format. If a query exceeds capacity guardrails or uses unsupported features, it can fall back to DirectQuery, at which point performance drops — so understanding the fallback behaviour matters when you promise stakeholders performance.
Real-time example. A report with a two-hour Import refresh cycle moved to Direct Lake and showed data within minutes of the pipeline completing, removing the refresh scheduling problem entirely.
Interview tip. Mention the DirectQuery fallback. It is the caveat that separates someone who has deployed Direct Lake from someone who has read the marketing page.
Q97. How do capacities, workspaces and domains work in Fabric?
Difficulty: Intermediate
Answer. A capacity is the purchased compute, measured in Capacity Units (F2 through F2048 and above), shared across all workloads assigned to it and billable per second with the ability to pause. A workspace is the collaboration container for items — lakehouses, notebooks, pipelines, reports — and is assigned to exactly one capacity. Domains group workspaces by business area for governance and data mesh style organisation. The capacity model has an important operational consequence: because compute is shared, one heavy Spark job can consume units needed by Power BI reports on the same capacity. Fabric smooths and bursts usage over time, but sustained overconsumption causes throttling, so separating production and development onto different capacities is common practice.
Real-time example. A team moved development workloads onto a small separate F8 capacity after an experimental notebook throttled the production capacity mid-morning and slowed executive reports.
Interview tip. Raising the noisy-neighbour risk and the mitigation is a strong practical signal — it is the thing that actually bites teams in production.
Q98. Scenario — You have ADF pipelines and a Synapse dedicated SQL pool. How would you plan a move to Fabric?
Difficulty: Advanced
Answer. Assess first, migrate in phases. Inventory current workloads, their consumers, SLAs and costs, and identify which are stable and which are actively changing — migrate the changing ones first, because they are already being touched. Start by shortcutting existing ADLS Gen2 storage into a Fabric Lakehouse so Fabric can read current data without any movement, which enables parallel running. Rebuild pipelines as Fabric Data Pipelines, which is largely familiar given the shared ADF lineage, and port Synapse Spark notebooks to Fabric notebooks. For the dedicated SQL pool, map objects to a Fabric Warehouse and validate carefully — T-SQL surface area, distribution concepts and workload management do not translate one-to-one. Migrate Power BI models to Direct Lake where it removes a refresh bottleneck. Run both platforms in parallel with reconciliation checks before cutting over, and size the Fabric capacity from measured usage rather than a guess. Plan for CI/CD, security and monitoring rework explicitly — that is where migrations overrun.
Real-time example. A phased migration where shortcuts allowed the Fabric build to proceed for three months without touching the existing lake, so a rollback remained available at every point.
Interview tip. Naming reconciliation and parallel running is what a hiring manager wants to hear. Migration questions test risk management, not enthusiasm for the new platform.
Module 12 — Power BI
Short explanation
Data engineers are not expected to be BI developers, but you own the layer Power BI consumes, so panels check that you understand what a good model looks like from the report side.
Important concepts
Calculated columns versus measures, Power Query and M versus DAX, relationships and cardinality, gateways, and storage modes.
Q99. What is the difference between a calculated column and a measure in DAX?
Difficulty: Beginner
Answer. A calculated column is computed row by row during model refresh and stored in the model, consuming memory and increasing model size; it has row context and is used where you need a physical value to slice, filter or relate on. A measure is computed at query time based on the current filter context, stores nothing, and is used for aggregations that must respond to whatever the user has selected. The default guidance is to prefer measures, and to push calculated column logic upstream into the data pipeline where possible, so the model stays lean.
Real-time example. A profit margin implemented as a calculated column gave wrong results at every aggregation level because it averaged row-level ratios; rewritten as a measure dividing total profit by total revenue, it was correct at all levels.
Interview tip. That averaging-ratios error is a memorable, correct example and it demonstrates you understand filter context rather than just the definitions.
Q100. Power Query (M) vs DAX — what does each do?
Difficulty: Beginner
Answer. Power Query, using the M language, runs at data load and refresh: it connects, shapes, cleans, merges and types the data before it enters the model. DAX runs after the model is loaded: it calculates measures and columns against the loaded data in response to report interaction. The rule of thumb — shape the data as far upstream as possible. Transform in the source or pipeline if you can, in Power Query if you must, and in DAX only for genuinely dynamic calculations.
Real-time example. Splitting a full name into first and last name belongs in the pipeline or Power Query, not a DAX calculated column, because doing it in DAX repeats the work in every model that uses the table.
Interview tip. State the upstream-first principle. It is the answer a data engineering panel most wants from a Power BI question, because it defines the boundary of your own responsibility.
Q101. Explain relationships and cardinality in a Power BI model.
Difficulty: Intermediate
Answer. Relationships connect tables on a key and control how filters propagate. Cardinality types are one-to-many (the standard dimension-to-fact relationship), one-to-one (rare, usually a sign the tables should be merged), and many-to-many (which requires care and often indicates a missing bridge table). Cross-filter direction is single by default, meaning filters flow from the one side to the many side; bi-directional filtering flows both ways and should be used sparingly because it can create ambiguous filter paths and performance problems. A clean star schema with single-direction one-to-many relationships is the target.
Real-time example. A model with bi-directional relationships enabled everywhere produced inconsistent totals depending on which visual was filtered first; reverting to single direction plus explicit CROSSFILTER in the two measures that needed it fixed it.
Interview tip. Connect this back to the star schema from Module 4. Showing the modelling decision and the BI consequence as one continuous chain is a strong senior signal.
Q102. What is an on-premises data gateway and when do you need one?
Difficulty: Intermediate
Answer. A gateway is software installed inside your network that lets the Power BI Service reach data sources that are not publicly accessible — on-premises databases, or resources inside a private VNet. Standard mode supports multiple users and multiple data sources centrally and can be clustered for high availability; personal mode supports a single user and Import only. It is needed for scheduled refresh and DirectQuery against those private sources. It is not needed for sources already in the cloud with public endpoints, or for Direct Lake against OneLake.
Real-time example. A gateway cluster of two nodes on separate servers in the Hyderabad office keeps scheduled refreshes running when one node is patched.
Interview tip. Mentioning clustering for high availability is worth including — a single gateway on someone’s desktop is a common and fragile real-world pattern.
Q103. Import vs DirectQuery vs Direct Lake — how do you choose, and how do you publish safely?
Difficulty: Advanced
Answer. Import loads a compressed copy into the model: fastest queries, full DAX support, but data is only as fresh as the last refresh and model size is bounded by capacity. DirectQuery leaves data at the source and translates visuals into source queries: always current, no size limit, but performance depends entirely on the source and some DAX is restricted. Direct Lake reads Delta files in OneLake directly, targeting Import-like performance with near-real-time freshness, and requires Fabric. Choose on the freshness requirement first, then volume, then source performance. For publishing, use deployment pipelines with dev, test and production workspaces, parameterise data source connections per environment, and apply row-level security in the model rather than duplicating reports per audience.
Real-time example. A 400-million-row fact table exceeded practical Import size and performed poorly on DirectQuery against the source; Direct Lake on a Fabric Lakehouse resolved both constraints.
Interview tip. Lead with the freshness requirement. Choosing a storage mode by technical preference rather than by business requirement is the mistake being tested.
Module 13 — Data Modeling
Short explanation
A short module, but a revealing one. Modelling questions test whether you can translate a business requirement into a schema — the skill that separates a data engineer from a pipeline operator.
Important concepts
Normalisation forms, denormalisation trade-offs, key types, cardinality, and grain.
Q104. What is normalisation? Explain 1NF, 2NF and 3NF.
Difficulty: Beginner
Answer. Normalisation organises data to reduce redundancy and prevent update, insert and delete anomalies. 1NF requires atomic values in every column and no repeating groups — no comma-separated lists in a single field. 2NF requires 1NF plus every non-key attribute depending on the whole primary key, which only matters with composite keys. 3NF requires 2NF plus no transitive dependencies — non-key attributes must not depend on other non-key attributes. Higher forms such as BCNF exist but are rarely a practical concern in analytics work.
Real-time example. An orders table storing customer_city alongside customer_id violates 3NF, because city depends on customer, not order — so a customer relocating requires updating every historical order row.
Interview tip. Have one concrete anomaly example ready like that one. Reciting the definitions without an anomaly is the most common weak answer here.
Q105. When would you deliberately denormalise?
Difficulty: Intermediate
Answer. In analytical models, almost always — a star schema’s dimensions are intentionally denormalised. The reasoning is that normalisation optimises for write consistency, while analytics is read-dominated, so joins become the bottleneck and redundancy is an acceptable price. Denormalise when query performance matters more than storage, when the data is loaded through a controlled pipeline so update anomalies cannot arise organically, and when a simpler model materially improves usability for report authors. Do not denormalise a transactional system where multiple applications write concurrently.
Real-time example. A DimProduct carrying category and subcategory names directly, rather than snowflaked into separate tables, so every product query is one join instead of three.
Interview tip. Frame it as OLTP normalises, OLAP denormalises, and give the reason — write consistency versus read performance. That framing answers the question and the likely follow-up together.
Q106. Explain primary, foreign, composite, surrogate and natural keys.
Difficulty: Beginner
Answer. A primary key uniquely identifies each row and cannot be NULL. A foreign key references a primary key in another table and enforces referential integrity. A composite key is a primary key made of two or more columns together — common in bridge and factless fact tables. A natural key comes from the business, such as an email address or an order number. A surrogate key is a system-generated meaningless integer used as the primary key in dimensions, with the natural key retained as an attribute. Warehouses use surrogate keys because natural keys change and because SCD Type 2 requires multiple rows per business entity.
Real-time example. DimCustomer with customer_sk as the surrogate primary key, customer_id as the natural key attribute, and FactSales.customer_sk as the foreign key — so a Type 2 change adds a new surrogate key without touching historical facts.
Interview tip. Connect surrogate keys back to SCD Type 2 rather than describing them in isolation. Interviewers are checking whether you see the modelling and loading concerns as one system.
Q107. What is cardinality and why does it matter in data modelling?
Difficulty: Intermediate
Answer. Cardinality has two meanings in practice, and knowing both is useful. Between tables, it describes the relationship type — one-to-one, one-to-many, many-to-many — which determines how the model is structured and how filters propagate. Within a column, it describes the number of distinct values, which drives physical decisions: high-cardinality columns are poor partition keys but often good distribution or clustering keys; low-cardinality columns compress well and make sensible partitions. Getting relationship cardinality wrong causes fan-out and duplicated measures; getting column cardinality wrong causes thousands of tiny partitions or severe skew.
Real-time example. Partitioning a fact table by customer_id created hundreds of thousands of tiny partitions; repartitioning by order_date produced a manageable partition count and enabled effective pruning.
Interview tip. Distinguishing the two meanings explicitly is a small thing that reads as precise thinking, and it lets you cover both the modelling and the physical design angles.
Q108. Scenario — Design a data model for a retail chain that needs sales analysis by product, store, customer and time, including tracking when a store changes its region.
Difficulty: Advanced
Answer. Start by declaring the grain: one row per sales transaction line item, which is the finest useful level and supports every aggregation above it. Build FactSales with surrogate foreign keys to DimProduct, DimStore, DimCustomer and DimDate, degenerate dimensions for transaction and line numbers, and additive measures — quantity, gross amount, discount, net amount. Keep the fact table narrow and additive; avoid storing ratios, which do not aggregate correctly. Model DimStore as SCD Type 2 with store_sk, store_id, region, effective_start_date, effective_end_date and is_current, so a region change creates a new row and historical sales stay attributed to the region in force at the time. DimDate is a conformed, pre-populated role-playing dimension. If sales can occur without a known customer, use an “Unknown” member rather than a NULL foreign key. Physically, partition by date, hash-distribute or Z-ORDER on the highest-cardinality join key, and replicate small dimensions.
Real-time example. After a regional reorganisation, the Type 2 store dimension let the business report both “as-was” and “as-is” regional performance from the same fact table — a requirement that a Type 1 dimension would have made impossible to satisfy retrospectively.
Interview tip. Declare the grain in your first sentence. It is the mark of someone who has done dimensional modelling properly, and skipping it is the most common way this question is answered poorly.
Dedicated Question Sections
These sections curate the questions above into the shortlists interviewers actually work from. Where a question appears in a module, the module number is noted so you can jump to the full answer instead of reading it twice.
Top 25 Azure Data Engineer Interview Questions for Freshers
# | Question | Where |
1 | What is cloud computing and how does it differ from on-premises? | Module 1, Q1 |
2 | What is a resource group? | Module 1, Q4 |
3 | Explain IaaS, PaaS and SaaS with examples. | Module 1, Q7 |
4 | What are the types of JOIN? | Module 2, Q11 |
5 | Difference between INNER and LEFT JOIN with NULLs. | Module 2, Q12 |
6 | Explain ROW_NUMBER, RANK and DENSE_RANK. | Module 2, Q15 |
7 | Find the second-highest salary per department. | Module 2, Q16 |
8 | Stored procedure vs function. | Module 2, Q17 |
9 | List vs tuple vs set vs dictionary. | Module 3, Q24 |
10 | How does exception handling work in Python? | Module 3, Q23 |
11 | Series vs DataFrame in Pandas. | Module 3, Q25 |
12 | OLTP vs OLAP. | Module 4, Q31 |
13 | What is a star schema? | Module 4, Q32 |
14 | What are slowly changing dimensions? | Module 4, Q36 |
15 | ETL vs ELT. | Module 5, Q39 |
16 | What is ADF and what are its components? | Module 6, Q47 |
17 | Linked service vs dataset. | Module 6, Q48 |
18 | What are ADF trigger types? | Module 6, Q52 |
19 | ADLS Gen2 vs Blob storage. | Module 7, Q57 |
20 | What is Delta Lake and why use it? | Module 8, Q68 |
21 | RDD vs DataFrame vs Dataset. | Module 9, Q75 |
22 | What is lazy evaluation in Spark? | Module 9, Q76 |
23 | What is Microsoft Fabric? | Module 11, Q91 |
24 | Calculated column vs measure in Power BI. | Module 12, Q99 |
25 | What is normalisation? | Module 13, Q104 |
Fresher-specific questions you will also be asked:
- Walk me through a project you have built. — Have one project you can describe end to end: source, ingestion method, transformation logic, storage format, destination, orchestration, and one thing that went wrong. Rehearse it to under three minutes.
- Why data engineering and not development or testing? — Give a real reason. “It’s in demand” is a weak answer; “I enjoy the problem of making unreliable data trustworthy at scale” is not.
- Which certification do you hold or plan to take? — Know that DP-203 retired on 31 March 2025 and that DP-700 is the current Fabric Data Engineer Associate credential.
- What is the largest dataset you have worked with? — Be honest about the number. Inflating it invites follow-ups you cannot answer.
- How do you learn a new Azure service? — Documentation, a small hands-on build, and a specific example.
- What happens if your pipeline fails at 2am? — They want to hear alerting, logging, retry and idempotency, not “I’d fix it in the morning.”
- Write code to remove duplicates from a list without using set().
- What is the difference between DELETE, TRUNCATE and DROP?
- How would you check whether a data load is correct? — Row counts, control totals, spot checks against the source.
- Where do you want to be in two years? — A specific technical direction beats a generic ambition.
Top 25 Azure Data Engineer Interview Questions for Experienced Professionals
For three-plus years of experience, panels compress the fundamentals and spend the time here.
# | Question | Where |
1 | How do you control cost on an Azure data platform? | Module 1, Q10 |
2 | How do you approach optimising a slow SQL query? | Module 2, Q19 |
3 | A nightly query went from 20 minutes to 3 hours. Diagnose it. | Module 2, Q20 |
4 | CTE vs subquery vs temp table. | Module 2, Q13 |
5 | How do you make an ingestion script idempotent? | Module 3, Q30 |
6 | How do you implement SCD Type 2? | Module 4, Q37 |
7 | How do you handle a late-arriving dimension? | Module 4, Q38 |
8 | How do you implement watermark-based incremental loading? | Module 5, Q42 |
9 | What is CDC and how do you implement it in Azure? | Module 5, Q43 |
10 | How do you handle schema drift? | Module 5, Q46 |
11 | How do you validate data quality in a pipeline? | Module 5, Q44 |
12 | What is a metadata-driven pipeline? | Module 6, Q54 |
13 | How do you handle errors and retries in ADF? | Module 6, Q55 |
14 | Design a 200-table daily incremental load from on-premises. | Module 6, Q56 |
15 | Explain RBAC, ACLs and SAS and how they interact. | Module 7, Q60 |
16 | What is the small file problem and how do you avoid it? | Module 7, Q63 |
17 | How do you optimise a Spark job in Databricks? | Module 8, Q72 |
18 | What is Unity Catalog and what problem does it solve? | Module 8, Q70 |
19 | A Databricks job slowed from 25 minutes to 2 hours. Investigate. | Module 8, Q74 |
20 | What is data skew and how do you handle it? | Module 9, Q81 |
21 | How do you debug an executor out-of-memory error? | Module 9, Q82 |
22 | What are the distribution types in a dedicated SQL pool? | Module 10, Q85 |
23 | Should we move from Synapse to Fabric? | Module 10, Q90 |
24 | How would you plan an ADF and Synapse migration to Fabric? | Module 11, Q98 |
25 | What is Direct Lake mode and what are its limits? | Module 11, Q96 |
Top Scenario-Based Azure Data Engineer Interview Questions
Scenario questions have no single correct answer. Panels score your structure — clarify the requirement, state assumptions, propose an approach, name the trade-off, describe how you would validate it.
- Your pipeline succeeded but the dashboard shows yesterday’s numbers. Where do you look first?
- A source system started sending 10x the usual volume overnight. What breaks and what do you do?
- Finance says revenue in the warehouse doesn’t match the source system by 0.3%. How do you investigate?
- You need to reprocess six months of historical data without disrupting the daily load. How?
- A source removed a column your silver layer depends on. What is your immediate action and your longer-term fix?
- Your Databricks bill doubled this month. Find out why.
- A business user needs data that is currently loaded daily to be available hourly. How do you evaluate the request?
- A pipeline has been failing intermittently for two weeks with no pattern. How do you approach it?
- You inherit a platform with no documentation and 80 pipelines. What are your first two weeks?
- Two teams built separate customer dimensions with conflicting definitions. How do you resolve it?
- Your ADF pipeline shows success but no data landed. What happened? (See Module 6, Q55 — the false-green failure path.)
- A GDPR delete request arrives for a customer whose data is spread across bronze, silver, gold and seven Delta versions. What do you do?
- Your streaming job is falling behind and lag is growing. How do you diagnose it?
- A dashboard that loaded in two seconds now takes ninety. Nothing in the report changed.
- You must migrate 40 SSIS packages to Azure. What is your approach and what do you migrate first?
Top Azure Data Factory Interview Questions
- What is ADF and what are its core components? (Q47)
- Linked service vs dataset. (Q48)
- What are the Integration Runtime types? (Q49)
- How does Copy activity work, and what is staged copy? (Q50)
- Copy activity vs Mapping Data Flow. (Q51)
- What are the trigger types? (Q52)
- Parameters vs variables. (Q53)
- What is a metadata-driven pipeline? (Q54)
- How do you handle errors and retries? (Q55)
- Design a 200-table incremental load. (Q56)
- How do you implement CI/CD for ADF? — Git integration with a collaboration branch, ARM template publish from the adf_publish branch, and parameterised linked services per environment.
- What is the ForEach activity and what is batch count? — Iterates a collection; batch count controls parallelism, capped at 50. Set it based on what the source can tolerate.
- How do you pass data between activities? — Activity output expressions such as @activity(‘Lookup1’).output.firstRow.columnName.
- What is the difference between Lookup and Get Metadata? — Lookup reads data or a query result; Get Metadata reads properties about a file or folder such as existence, size and child items.
- How do you monitor ADF at scale? — Route diagnostic logs to Log Analytics and alert on patterns rather than watching individual pipeline runs.
Top Azure Databricks Interview Questions
- What is Azure Databricks and how is its architecture structured? (Q65)
- Cluster types and when to use each. (Q66)
- Workflows and their relationship to notebooks. (Q67)
- What is Delta Lake and why use it over Parquet? (Q68)
- Explain time travel, MERGE, OPTIMIZE, Z-ORDER and VACUUM. (Q69)
- What is Unity Catalog? (Q70)
- What is Auto Loader? (Q71)
- How do you optimise a Spark job in Databricks? (Q72)
- How do you control Databricks cost? (Q73)
- A job slowed from 25 minutes to 2 hours — investigate. (Q74)
- What is a DBU? — Databricks Unit, the normalised unit of processing consumption used for billing, varying by workload type and tier.
- What is Photon? — A vectorised, C++ execution engine that accelerates SQL and DataFrame operations at a higher DBU rate, usually net cheaper when runtime falls enough.
- Managed vs external tables in Unity Catalog? — Managed tables have their lifecycle and storage controlled by Unity Catalog; dropping one deletes the data. External tables point at a registered location; dropping one leaves the files.
- What are Delta Live Tables / Lakeflow Declarative Pipelines? — A declarative framework for building pipelines where you define the target datasets and expectations, and the platform handles orchestration, dependency resolution and data quality enforcement.
- How do you handle secrets in Databricks? — Databricks secret scopes backed by Key Vault, referenced with dbutils.secrets.get(), never hardcoded.
Top Microsoft Fabric Interview Questions
- What is Microsoft Fabric? (Q91)
- What is OneLake? (Q92)
- Lakehouse vs Warehouse. (Q93)
- Shortcuts vs mirroring. (Q94)
- Dataflows Gen2 vs Data Pipelines. (Q95)
- What is Direct Lake mode? (Q96)
- How do capacities, workspaces and domains work? (Q97)
- How would you migrate from ADF and Synapse to Fabric? (Q98)
- Which certification covers Fabric data engineering? — DP-700, Microsoft Certified: Fabric Data Engineer Associate. It replaced DP-203, which retired on 31 March 2025. There is no automatic upgrade path from the old credential.
- What is the SQL analytics endpoint on a Lakehouse? — An automatically provisioned read-only T-SQL endpoint over the Lakehouse’s Delta tables, allowing SQL querying without Spark.
- What is a semantic model in Fabric? — The Power BI model layer defining relationships, measures and security over the underlying tables; in Fabric it can be built directly over a Lakehouse or Warehouse.
- What is Real-Time Intelligence in Fabric? — The workload covering Eventstreams, Eventhouse and KQL databases for streaming ingestion and low-latency analytical querying.
Azure Data Engineer HR Interview Questions
HR rounds are about retention risk and cultural fit, not technical skill. Keep answers short, specific and non-negative.
- Tell me about yourself. — Two minutes: current role, technical focus, one achievement, why this role.
- Why are you leaving your current company? — Give a forward-looking reason. Never criticise your employer or manager.
- Why do you want to join us? — Reference something specific about their data platform, industry or scale.
- What are your strengths and weaknesses? — A real weakness with the concrete step you are taking about it.
- Describe a time you failed. — Own the mistake, explain what changed in your practice afterwards.
- How do you handle pressure and tight deadlines? — Give a real prioritisation example.
- Tell me about a conflict with a colleague. — Focus on how it was resolved, not who was right.
- What are your salary expectations? — Give a researched range, and ask about the band for the role.
- Are you willing to work in rotational on-call? — Answer honestly. Agreeing insincerely creates a problem in month two.
- Where do you see yourself in five years? — A technical or leadership direction that plausibly exists at their company.
- What is your notice period, and can it be shortened? — Be accurate; this gets verified.
- Do you have any questions for us? — Always have three, at least one about how the team works.
Azure Data Engineer Technical Round Questions
The technical round typically runs 45 to 60 minutes and follows a predictable shape.
Segment | Typical duration | What is asked |
Introduction and project walkthrough | 5–10 min | Your architecture, your specific contribution |
SQL, usually live | 10–15 min | Window functions, joins, a written query |
Python or PySpark | 10 min | A transformation task or a code reading exercise |
Azure services | 10–15 min | ADF, Databricks, ADLS, Synapse or Fabric depth |
Scenario or debugging | 10 min | A failure situation to reason through |
Your questions | 5 min | Judged more than candidates expect |
Questions almost certain to appear: find the Nth highest value per group (Q16); explain your incremental load strategy (Q41, Q42); how you handle pipeline failure (Q55); how you optimise a slow Spark job (Q72); describe your project’s architecture end to end.
How to fail this round despite knowing the material: starting to write SQL before clarifying the requirement; describing an architecture you cannot explain the reasoning behind; claiming ownership of work you observed; and going silent while thinking, which reads as being stuck.
Azure Data Engineer Manager Round Questions
Azure Data Engineer Manager Round Questions
The manager round tests judgement, communication and ownership. Technical depth is assumed by this point.
- Walk me through a technical decision you made that you would make differently now.
- How do you estimate the effort for a data platform project?
- A stakeholder wants a dashboard by Friday and it is technically impossible. What do you do?
- How do you prioritise when three teams all have urgent requests?
- Tell me about a production incident you owned. What was the root cause and what changed afterwards?
- How do you decide between building something in-house and using a managed service?
- How do you handle a disagreement with a senior engineer about architecture?
- How do you keep a platform’s cost under control as it grows?
- What does good documentation look like on your team?
- How do you onboard a new engineer onto a platform you own?
- How do you communicate a data quality problem to a business stakeholder?
- What would you change in the first 90 days if you joined us?
What is being scored: whether you can say no with a reason and an alternative; whether you take ownership without blaming; whether you can explain a technical constraint to a non-technical audience; and whether you think about cost and maintenance, not just delivery.
Azure Data Engineer vs Generic Data Engineer Interview
Dimension | Generic Data Engineer | Azure Data Engineer |
Core focus | Language and framework skill — Python, SQL, Spark, Airflow | Same fundamentals plus depth in the Azure service stack |
Tooling questions | Tool-agnostic: “how would you orchestrate this?” | Service-specific: “how would you configure this ADF trigger?” |
Storage questions | Formats and file layout in general | ADLS Gen2 specifics — HNS, RBAC vs ACL, tiers |
Compute questions | Spark fundamentals | Spark fundamentals plus Databricks, Synapse Spark or Fabric specifics |
Warehouse questions | Dimensional modelling in the abstract | Modelling plus dedicated SQL pool distributions or Fabric Warehouse |
Certification weight | Low; portfolio matters more | Moderate; DP-700 is a recognised filter in Microsoft-heavy shops |
Common failure mode | Weak on production operations | Knows service names but not the underlying distributed concepts |
The takeaway: an Azure-specific interview adds a service layer on top of, not instead of, the fundamentals. Candidates who study only Azure services and skip Spark internals and SQL depth fail in the middle of the interview, not at the start.
Azure Data Engineer vs AWS Data Engineer Interview
Concept | Azure | AWS |
Object storage | ADLS Gen2 | Amazon S3 |
Orchestration / ETL | Azure Data Factory | AWS Glue, Step Functions, MWAA |
Spark platform | Azure Databricks, Synapse Spark, Fabric | AWS Glue, EMR, Databricks on AWS |
Data warehouse | Synapse dedicated SQL pool, Fabric Warehouse | Amazon Redshift |
Serverless lake query | Synapse serverless SQL | Amazon Athena |
Streaming ingestion | Event Hubs | Kinesis Data Streams |
Governance catalog | Microsoft Purview, Unity Catalog | AWS Glue Data Catalog, Lake Formation |
Secrets | Azure Key Vault | AWS Secrets Manager |
Current certification | DP-700 Fabric Data Engineer Associate | AWS Certified Data Engineer – Associate (DEA-C01) |
The takeaway: the underlying concepts are identical — distributed processing, partitioning, incremental loading, dimensional modelling. If you are interviewing across both clouds, prepare the concepts once and learn the service mapping as vocabulary. Panels are generally tolerant of a candidate from the other cloud who reasons well; they are not tolerant of one who cannot explain a shuffle.
Azure (Synapse-era) vs Microsoft Fabric Interview
Dimension | Synapse-era Azure stack | Microsoft Fabric |
Model | Separate PaaS services you compose | Unified SaaS platform |
Storage | ADLS Gen2 accounts you provision | OneLake, provisioned automatically per tenant |
Compute billing | Per-service — DWU, DBU, vCore | Shared capacity units (F-SKUs) |
Warehouse | Synapse dedicated SQL pool with distribution keys | Fabric Warehouse; distribution is abstracted away |
Power BI connection | Import or DirectQuery | Direct Lake, plus Import and DirectQuery |
Typical interview focus | Distribution strategy, PolyBase, resource classes, IR types | OneLake, Lakehouse vs Warehouse, shortcuts, capacity management |
Certification | DP-203 (retired 31 March 2025) | DP-700 (current) |
The takeaway: do not treat these as competing preparation tracks. Most Indian employers in 2026 are running Synapse or a Databricks-plus-ADF stack in production while evaluating or piloting Fabric. You are likely to be asked about both in the same interview, and the strongest answer to “which is better” is a migration-decision framework, not a preference.
Four-Week Azure Data Engineer Interview Preparation Roadmap
This assumes roughly two to three focused hours on weekdays and four to five on weekends. If you have less time, extend the calendar rather than compressing the content — skipping Week 1 to reach Fabric faster is the most common preparation mistake.
Week | Topics | Practice | Outcome |
Week 1 — Fundamentals | Azure basics (Module 1), SQL end to end (Module 2), Python for data (Module 3) | Solve 40–50 SQL problems focused on window functions, joins and aggregation; write 5 Python scripts that read, clean and load a file; explain each answer aloud without notes | You can write a correct windowed query on a shared screen under observation, and explain your Python without hedging |
Week 2 — Modelling and pipelines | Data warehousing (Module 4), ETL/ELT design (Module 5), Azure Data Factory (Module 6), ADLS Gen2 (Module 7) | Build one working ADF pipeline doing metadata-driven incremental load of 3+ tables into a medallion lake; implement SCD Type 2 manually once | You can design a pipeline on a whiteboard and defend the incremental strategy and failure handling |
Week 3 — Spark and Databricks | Databricks (Module 8), Apache Spark internals (Module 9), Synapse (Module 10) | Run a Databricks job on a realistically sized dataset; deliberately create skew and fix it; read the Spark UI for a real job and identify the expensive stage; implement a Delta MERGE | You can explain shuffle, skew and partitioning from experience, and describe a real optimisation you performed |
Week 4 — Fabric, BI and mock rounds | Microsoft Fabric (Module 11), Power BI (Module 12), Data modelling (Module 13), all scenario sections | Build one small end-to-end Fabric Lakehouse; do 3 full mock interviews with a peer, recorded; rehearse your project walkthrough until it runs under 3 minutes cleanly | You can answer scenario questions with structure, and your project story is tight, honest and specific |
Non-negotiable daily habit across all four weeks: 30 minutes of SQL, every day, including Week 4. SQL is the skill that decays fastest and eliminates the most candidates.
The single highest-return activity in this plan is the recorded mock interview. Most candidates discover on playback that they ramble for ninety seconds before answering, or that their project explanation assumes context the interviewer does not have. You cannot detect either of those without hearing yourself.
Common Azure Data Engineer Interview Mistakes
SQL mistakes
Writing before clarifying. Starting to type without asking about duplicates, NULLs or ties. Ask two clarifying questions first — it is scored positively.
Using ROW_NUMBER where DENSE_RANK is correct. The “second-highest distinct value” trap catches many candidates.
Putting a right-table filter in the WHERE clause of a LEFT JOIN, silently converting it to an INNER JOIN.
Ignoring NULL behaviour in aggregates, NOT IN (which returns nothing if the subquery contains a NULL), and comparisons.
Answering “how would you optimise this?” with “add an index” and nothing else. Read the plan first.
Silence while thinking. Narrate your reasoning. An interviewer cannot score a blank screen.
Azure Data Factory mistakes
Describing ADF as a transformation tool. It orchestrates and copies; Data Flows and Databricks transform.
Not knowing why a self-hosted IR is needed, or claiming it needs inbound firewall ports.
Missing the false-green failure path — a pipeline reporting success while an activity failed.
Proposing 200 separate pipelines for 200 tables instead of a metadata-driven design.
Setting ForEach batch count to the maximum without considering source system load.
Updating the watermark before the load succeeds, which silently skips data on failure.
Databricks mistakes
Running production jobs on all-purpose clusters. The most common real-world cost error and a red flag in interviews.
Claiming Delta Lake is “just Parquet with extra files.” It is the transaction log that provides ACID guarantees.
Caching everything rather than only expensive, reused datasets.
Using DBFS mounts with account keys and being unable to explain why Unity Catalog is preferable.
Not knowing the VACUUM and time-travel trade-off.
Answering optimisation questions with a list of config settings instead of “I’d look at the Spark UI first.”
Spark mistakes
Saying “DataFrames are faster than RDDs” without explaining Catalyst. The why is the question.
Confusing repartition and coalesce, or using coalesce(1) on large data to get one output file.
Not being able to describe what a shuffle physically does — disk write, network transfer, deserialisation.
Treating skew as a tuning problem when it is often a data quality problem, such as a placeholder key absorbing millions of rows.
Not distinguishing driver OOM from executor OOM.
Not knowing that a collect() brings all data to the driver.
Fabric mistakes
Presenting DP-203 as a current certification. It retired on 31 March 2025. Saying you are studying for it signals your knowledge is at least a year stale — this is the fastest way to lose credibility in a 2026 interview.
Confusing shortcuts with mirroring.
Describing Fabric as “Synapse rebranded.” It is a SaaS platform with a different storage, licensing and governance model.
Recommending a full migration to Fabric with no cost, risk or driver assessment.
Not knowing the Direct Lake fallback to DirectQuery.
Ignoring the capacity noisy-neighbour problem when asked about Fabric in production.
Cross-cutting mistakes
Inflating your experience. Claiming petabyte scale or ownership of work you observed. Panels probe, and the collapse is worse than the original gap.
No failure stories. A candidate with three years of experience and nothing that ever broke is not believed.
Not asking questions at the end. It is read as low interest, and it is one of the easiest things to fix.
Twenty Rapid Fire Azure Data Engineer Interview Questions
Short questions, one-line answers. Use these as a daily warm-up.
# | Question | Answer |
1 | Which certification replaced DP-203? | DP-700, Fabric Data Engineer Associate; DP-203 retired 31 March 2025. |
2 | How many distributions does a Synapse dedicated SQL pool have? | 60. |
3 | What does the hierarchical namespace enable? | True directories with atomic rename, delete and directory-level ACLs. |
4 | Which activity reads a single row or query result in ADF? | Lookup activity. |
5 | Default cross-filter direction in Power BI? | Single. |
6 | Which Delta command compacts small files? | OPTIMIZE. |
7 | Which Delta command removes unreferenced files? | VACUUM. |
8 | Narrow or wide — is filter narrow? | Narrow. |
9 | Which is cheaper, repartition or coalesce? | Coalesce, because it avoids a full shuffle. |
10 | What does AQE stand for? | Adaptive Query Execution. |
11 | Which IR type is needed for on-premises sources? | Self-hosted Integration Runtime. |
12 | Which SCD type preserves full history? | Type 2. |
13 | What is a factless fact table? | A fact table recording an event with no measure. |
14 | Which Fabric feature points at external data without copying? | Shortcut. |
15 | Which Power BI storage mode reads OneLake Delta files directly? | Direct Lake. |
16 | Which Databricks feature incrementally ingests new files? | Auto Loader. |
17 | What does DBU stand for? | Databricks Unit. |
18 | Which distribution type suits small dimension tables in Synapse? | Replicate. |
19 | Which Python keyword makes a function a generator? | yield. |
20 | What makes a pipeline safe to re-run? | Idempotency. |
Key Takeaways
- SQL decides more outcomes than any Azure service. Thirty minutes daily, every day, including the week of the interview.
- DP-203 is retired. It expired on 31 March 2025. DP-700, the Fabric Data Engineer Associate credential, is current. Mentioning DP-203 as a live certification dates you immediately.
- Prepare module-wise, not randomly. The 108 questions here follow the structure of an actual Azure Data Engineer course, which is also how interview panels structure their rounds.
- Scenario questions score structure, not answers. Clarify, assume, propose, trade off, validate.
- Failure stories are credibility. A candidate with no story about something breaking is not believed. Have two ready with root cause and what changed afterwards.
- Cost awareness signals seniority. Job clusters over all-purpose, pausing dedicated pools, lifecycle policies on storage. Give a specific lever you pulled.
- Idempotency and incremental loading are the two design concepts that come up in almost every experienced-level interview.
- Know both Synapse and Fabric. Most Indian employers in 2026 run one in production while evaluating the other. The best answer to “which is better” is a decision framework.
- Your project walkthrough is the highest-leverage three minutes of the interview. Rehearse it until it is tight, honest and specific.
- Treat salary figures with scepticism, including favourable ones. Glassdoor Hyderabad indicates around ₹9.0 LPA average with a ₹5.85–₹14.85 LPA typical range — indicative, not promised.
Conclusion
Azure Data Engineer interview questions have shifted decisively from definitions to judgement. Panels no longer filter on whether you can describe a pipeline; they filter on whether you can explain why your incremental load is safe to re-run, what you did the night a job started taking four times as long, and how you would decide between Synapse and Fabric for a workload that is already working fine.
That shift is good news for anyone willing to prepare properly, because it means the gap between a prepared candidate and an unprepared one is now visible within minutes — and it can be closed with disciplined work rather than luck.
Four things move the needle more than anything else:
Practise SQL daily. It is the most-tested and fastest-decaying skill in this entire list. Not weekly, not before the interview — daily.
Build something real. One end-to-end project you built yourself, that you can explain from source to dashboard including the parts that went wrong, is worth more than any number of completed courses. Use a real dataset with real messiness in it.
Prepare module by module. Work through Azure fundamentals, SQL, Python, warehousing, ETL design, ADF, ADLS, Databricks, Spark, Synapse, Fabric, Power BI and modelling in that order. Interview panels move in roughly this sequence, and a gap early in the chain stops the conversation before you reach your strengths.
Get the certification path right. AZ-900, then DP-900, then DP-700. Anyone still directing you toward DP-203 is working from information that expired in March 2025.
If you are preparing on your own, work through the 108 questions in this guide, answer each one out loud rather than reading it, and record at least three mock interviews. If you would rather learn this with structured guidance, hands-on lab work and real project experience, an instructor-led Azure Data Engineer course in Hyderabad covering the same modules — Azure fundamentals through Microsoft Fabric, with DP-700 alignment — will get you there faster and with fewer blind spots.
Either way, the candidates who get offers are not the ones who memorised the most answers. They are the ones who can be asked “why did you do it that way?” three times in a row and still have a good answer.
Frequently Asked Questions
1. How many Azure Data Engineer interview questions should I prepare?
Depth beats breadth. Around 100 questions covering every module, with genuine understanding, outperforms 500 memorised answers. The 108 in this guide map to the standard course modules and cover what panels actually ask.
2. Is DP-203 still valid in 2026?
No. Microsoft retired DP-203 on 31 March 2025, and the credential and its renewal assessments expired with it. The current Microsoft certification for this role is DP-700, Microsoft Certified: Fabric Data Engineer Associate. There is no automatic upgrade path from DP-203 — you must pass DP-700. Exam objectives and renewal terms are published on Microsoft’s Fabric Data Engineer Associate credential page.
3. What is the recommended certification path?
AZ-900 (Azure Fundamentals) → DP-900 (Azure Data Fundamentals) → DP-700 (Fabric Data Engineer Associate). DP-600 (Fabric Analytics Engineer) is optional and suits candidates leaning toward the BI and semantic modelling side. Experienced engineers can skip straight to DP-700.
4. Can a fresher get an Azure Data Engineer job?
Yes, though entry usually comes through a junior data engineer, ETL developer or data analyst role rather than a direct senior title. What decides it is a demonstrable end-to-end project you can explain in detail, plus solid SQL. Certification helps you get shortlisted; the project gets you hired.
5. What is the salary for an Azure Data Engineer in Hyderabad?
Glassdoor data for Hyderabad indicates an average of roughly ₹9.0 LPA, with a typical range of about ₹5.85–₹14.85 LPA, based on around 165 reported salaries. Treat these as indicative estimates, not guarantees — actual offers vary substantially by company type, years of experience, interview performance and negotiation. Senior and lead roles report considerably higher figures. Be sceptical of institutes advertising averages well above this range without naming a source.
6. How long does it take to become interview-ready?
For a fresher with SQL and Python basics, typically three to six months of consistent study plus project work. For an experienced ETL or SQL developer transitioning, two to three months is realistic because the fundamentals transfer. The four-week roadmap in this article is a final preparation sprint, not a from-scratch learning plan.
7. Which is more important — SQL or Azure services?
SQL, without close competition. More candidates are rejected on SQL than on any Azure service. Azure services can be learned on the job; weak SQL is visible in the first fifteen minutes and is rarely forgiven.
8. Do I need to know Microsoft Fabric to get hired in 2026?
For most roles, conceptual fluency is sufficient — OneLake, Lakehouse versus Warehouse, shortcuts, Direct Lake, and the fact that DP-700 has replaced DP-203. Hands-on Fabric depth is required where the employer has already committed to it. But do not study Fabric at the expense of Spark and SQL: the majority of production workloads in India today still run on ADF plus Databricks or Synapse.
9. Are Azure Databricks questions always asked?
In most mid and senior interviews, yes, because Databricks is where the heavy transformation work runs in a large share of Azure data platforms. Expect Delta Lake, cluster types, and at least one Spark optimisation question.
10. How many interview rounds are typical?
Usually three to four: a screening round (often telephonic or automated), one or two technical rounds, a manager or architect round, and an HR round. Product companies and GCCs frequently add a live coding assessment or a take-home task.
11. What if I don’t know the answer to a question?
Say so, then show your reasoning: “I haven’t worked with that directly, but based on how X behaves, I’d expect it to work like this.” That earns more than a confident wrong answer and far more than silence. Panels test knowledge boundaries deliberately.
12. Should I mention projects from a training course?
Yes, but describe them accurately as learning projects. Do not present them as production work — the follow-up questions about scale, on-call and stakeholders will expose it immediately. A well-explained training project with a real dataset is respected; a misrepresented one is disqualifying.
13. Do interviewers ask coding questions?
Frequently, but they are data manipulation tasks rather than competitive programming. Expect SQL queries, PySpark transformations, and Python problems involving files, JSON or APIs. Leetcode-style algorithm rounds are uncommon outside large product companies.
14. How should I prepare for scenario-based questions?
Practise structure over content. Clarify the requirement, state your assumptions, propose an approach, name the trade-off, and describe how you would validate the result. Panels score the reasoning method because there is no single correct answer.
15. What is the biggest single mistake candidates make?
Skipping fundamentals to look advanced. Candidates who can discuss Fabric architecture confidently but cannot write a correct window function or explain what a shuffle does are the most common rejections. Panels probe until they find the floor, and the floor is always SQL and distributed systems basics.
