Overview
Microsoft Fabric provides multiple ways to implement transformation logic and operationalize it within Fabric Data Factory pipelines. Two of the most common approaches are Fabric notebooks and SQL stored procedures.
Both are first class tools in Fabric and both can be orchestrated through Data Factory pipelines. The difference is not about which one is better. It is about how the processing is executed, where the logic lives, and what development style best fits the workload.
Notebooks are built on Apache Spark and are designed for distributed, code driven data engineering and analytics workflows. Stored procedures run directly in the SQL engine and are optimized for relational, database centric operations. In real world Fabric architectures, it is common and often recommended to use both together.
Understanding the strengths of each helps teams design pipelines that are scalable, maintainable, and aligned with how their data is structured and governed.
Fabric Notebooks
What they are
Fabric notebooks are interactive, Spark based code assets used primarily for data engineering, data science, and advanced transformation scenarios. They support multiple languages, including PySpark, Spark SQL, Scala, and R, and allow developers to mix code, documentation, and results in a single artifact.
Within Fabric Data Factory, notebooks can be executed using the Notebook activity, making them easy to operationalize as part of scheduled or event driven pipelines.
Strengths of Fabric notebooks
Fabric notebooks are particularly strong when workloads require distributed processing or complex transformation logic.
Key strengths include:
- Spark based distributed compute
Notebooks execute on Apache Spark, making them well suited for large scale transformations, file based processing, and lakehouse centric architectures. - Flexible, code first development
Developers can write imperative logic, loops, conditionals, and complex transformations that are difficult to express purely in SQL. - Multi language support
The ability to combine PySpark, Spark SQL, and other languages in a single notebook enables flexible patterns for ingestion, transformation, and validation. - Interactive and iterative workflow
Notebooks allow engineers to explore data, test transformations, and visualize results before operationalizing the logic in a pipeline.
Typical use cases
- Large scale data transformations in a lakehouse
- Complex business logic that benefits from imperative code
- Data preparation and enrichment across multiple sources
- Scenarios where Spark libraries or advanced processing frameworks are required
Stored Procedures
What they are
Stored procedures are database native units of logic written in T SQL and executed directly by the SQL engine. They encapsulate one or more SQL statements into a reusable, parameterized object that can be invoked by applications, users, or Fabric Data Factory pipelines.
In Fabric, stored procedures are executed using the Stored procedure activity, allowing pipelines to trigger database operations as part of broader orchestration flows.
Strengths of stored procedures
Stored procedures excel in scenarios where logic is tightly coupled to relational data and database semantics.
Key strengths include:
- Database engine execution
Logic runs close to the data, benefiting from the SQL optimizer, indexes, transactions, and set based processing. - Strong encapsulation and reuse
Business rules can be centralized in the database and reused across pipelines, reports, and applications. - Clear security boundaries
Permissions can be granted at the procedure level, allowing users or pipelines to execute logic without direct access to underlying tables. - Predictable operational behavior
Stored procedures provide consistent execution patterns, making them well suited for repeatable, production grade transformations.
Typical use cases
- Relational transformations and aggregations
- Data validation, cleanup, and enrichment within SQL databases
- Applying business rules before publishing curated datasets
- Scenarios requiring tight control over permissions and execution behavior
Final Thoughts
Fabric notebooks and stored procedures are intentionally complementary, not competing, tools.
- Fabric notebooks are ideal for Spark based, large scale, or highly customized transformations where flexibility and distributed processing are required.
- Stored procedures are best suited for database centric logic where relational operations, encapsulation, and governance are the priority.
In practice, many Fabric solutions use both.
A pipeline may land data, run a notebook to perform large scale transformations in the lakehouse, and then invoke stored procedures to apply relational business rules or publish curated tables.
The decision usually comes down to a few simple questions:
- Are you working with very large amounts of data that need heavy processing, or are you mostly updating and shaping data that already lives in a database?
- Do you need flexible, step by step logic written in code, or are your rules naturally expressed as SQL statements like filters, joins, and updates?
- Is this work better handled by a data engineering team using Spark and notebooks, or by a database team managing logic inside SQL?
If you are interested in designing Fabric pipelines, deciding where logic should live, or understanding how notebooks and stored procedures can work together, feel free to reach out and let’s talk.
