Difficulty: Hard | Concepts: Orchestration, Pipeline, Notebook, Dataflow Gen2, Architecture Decision-Making

Senior-level Fabric interviews often skip small syntax questions and go straight to: "Design the end-to-end architecture for this project — which components would you use and why?" This tests whether you can reason about trade-offs, not just recall definitions.

What Is an Orchestration Layer, and Why Does It Matter Here?

An orchestration layer is the component responsible for deciding when and in what order individual data tasks run, handling dependencies, retries, and failure alerts — as opposed to the components that actually do the data processing. In Fabric, the Pipeline is the orchestration layer: it doesn't do heavy transformation itself, but it calls and sequences Notebooks, Dataflows, Copy Activities, and even other Pipelines. Understanding this distinction — orchestration vs. processing — is what lets you correctly decide which tool owns which responsibility in a larger architecture, instead of treating all three tools as interchangeable.

Scenario

Design a solution that: ingests data nightly from a REST API and an on-prem SQL Server, applies business-user-maintained cleansing rules to one of the sources, runs a heavy PySpark aggregation across both, and finally refreshes a Power BI semantic model — with failure alerts at every stage.

Role of Each Component

Component	Role in this architecture
Pipeline	Top-level orchestrator: sequencing, dependency chains, retries, alerts, scheduling
Copy Activity (inside Pipeline)	Bulk ingestion from SQL Server into bronze Lakehouse files
Dataflow Gen2	Business-user-maintained cleansing rules for the API source (owned by analysts, not engineers)
Notebook	Heavy PySpark aggregation joining both cleaned sources into gold tables
Power BI refresh activity	Final step, triggered only after the Notebook succeeds

End-to-End Design

Pipeline triggers two parallel branches: a Copy Activity for SQL Server, and a Dataflow Gen2 activity for the REST API + cleansing rules.
An If Condition / Wait-for-both pattern ensures both branches complete before continuing.
A Notebook Activity reads both cleaned sources, performs the heavy join/aggregation in PySpark, and writes gold Delta tables.
On Notebook success, the Pipeline triggers a Power BI semantic model refresh activity.
Every activity has a failure path wired to a Teams/email alert activity, so a break at any stage is caught immediately.

Visual Representation

Pipeline (orchestrator, scheduled nightly) | |-- Copy Activity: SQL Server -> bronze Lakehouse ---\ |-- Dataflow Gen2: API + cleansing rules -> silver ---> both must succeed | |-- Notebook: join + aggregate -> gold Delta tables | |-- Power BI Refresh Activity (Direct Lake semantic model) | (failure at any node) --> Alert Activity (Teams/email)

Common Mistake to Avoid

Candidates frequently try to put heavy PySpark-style logic inside a Dataflow Gen2 just because it's already in the picture, or try to do business-friendly cleansing rules in a Notebook where only engineers can maintain them. The stronger answer explicitly assigns ownership: Dataflow Gen2 for logic that business users need to see and adjust, Notebooks for logic that needs engineering-grade performance and complexity, and the Pipeline purely for sequencing — never mixing these responsibilities.

Conclusion

This is the kind of question where interviewers are grading your architectural reasoning, not memorized facts: correctly separating orchestration from processing, and processing further into "business-maintainable" vs. "engineering-grade" logic, is exactly the signal that distinguishes a senior Fabric candidate from someone who's only used each tool in isolation.