{"id":111,"date":"2026-03-05T13:51:31","date_gmt":"2026-03-05T13:51:31","guid":{"rendered":"https:\/\/joeltan.me\/?p=111"},"modified":"2026-03-05T13:51:34","modified_gmt":"2026-03-05T13:51:34","slug":"microsoft-fabric-etl-escape-the-notebook-monolith","status":"publish","type":"post","link":"https:\/\/joeltan.me\/?p=111","title":{"rendered":"Microsoft Fabric ETL: Escape the Notebook Monolith"},"content":{"rendered":"\n

We\u2019ve all been there: it starts as a simple proof-of-concept. You open up a new Notebook in Microsoft Fabric, write a few cells to ingest data, add some transformations, and maybe append a couple of data quality checks. Fast forward three months, and that “quick script” has evolved into a monolithic monster sprawling across 100+ cells. <\/p>\n\n\n\n

Suddenly, your notebook contains utility functions, control flow logic, documentation, environment parameters, and inline unit tests – all tangled together in a single execution flow.<\/p>\n\n\n\n

When data engineering code lives entirely inside monolithic notebooks, it becomes notoriously difficult to maintain. You end up violating the DRY (Don’t Repeat Yourself) principle, unit testing becomes effectively impossible, and when a scheduled job fails on cell 87 out of 112, you’re faced with operational blindness while trying to dig through execution states.<\/p>\n\n\n\n

In this post, we’re going to fix that. We’ll look at how to leverage Microsoft Fabric’s built-in workspace resources to escape the notebook monolith by adopting an Object-Oriented (OO) Python approach alongside Fabric Pipelines. <\/p>\n\n\n\n

The Solution: Separation of Concerns<\/h2>\n\n\n\n

To solve the monolith problem, we need to enforce a rigid separation of concerns. Instead of letting a notebook do everything from defining functions to managing execution order, we split the responsibilities:<\/p>\n\n\n\n

    \n
  1. Reusable Logic:<\/strong> We use Fabric’s built-in resources (Environment\/Workspace files) to store pure Python modules. This allows us to write repeatable ETL code in an Object-Oriented style.<\/li>\n\n\n\n
  2. Execution Context:<\/strong> The Notebook itself becomes incredibly thin, acting only as the entry point that retrieves parameters and instantiates our Python classes.<\/li>\n\n\n\n
  3. Control Flow:<\/strong> We use Fabric Pipelines<\/strong> to orchestrate the dependency chains and looping, moving that responsibility out of the notebook entirely.<\/li>\n<\/ol>\n\n\n\n

    Deep Dive: The Lakehouseloader <\/em>Base Class (Template Method + Strategy Pattern)<\/h2>\n\n\n\n

    To eliminate the repetition of writing create table<\/code> and upsert<\/code> queries across our Medallion Architecture (Bronze, Silver, Gold), we combine two complementary design patterns: the Template Method Pattern<\/strong> and the Strategy Pattern<\/strong>.<\/p>\n\n\n\n

    The Template Method<\/strong> gives us the skeleton<\/em>: a private _table_load<\/code> method that enforces the strict, non-negotiable sequence of steps every table load must follow – drop (if recreating), create, truncate (if needed), then insert. That order never changes.<\/p>\n\n\n\n

    The Strategy Pattern<\/strong> gives us the flexibility<\/em>: instead of locking the create<\/code> and insert<\/code> logic into abstract methods on the class, _table_load<\/code> accepts them as plain callables (create_fn<\/code> and insert_fn<\/code>). This means every individual load_*<\/code> method can supply its own completely independent implementation – one might use the Spark DataFrame API, another a raw Spark SQL MERGE<\/code> statement – without any of those choices bleeding into each other.<\/p>\n\n\n\n

    We start by creating this base class (e.g., in a workspace file named lakehouse_utils.py<\/code>):<\/p>\n\n\n

    \nfrom abc import ABC\nfrom typing import Callable\nfrom pyspark.sql import SparkSession, DataFrame\n\nclass LakehouseLoader(ABC):\n    def __init__(self, spark: SparkSession, schema: str = "dbo"):\n        self.spark = spark\n        self.schema = schema\n\n    def _table_load(\n        self,\n        df: DataFrame,\n        table_name: str,\n        create_fn: Callable,\n        insert_fn: Callable,\n        recreate: bool = False,\n        truncate: bool = False\n    ):\n        """\n        The Template Method.\n        Accepts create_fn and insert_fn callables so each load method\n        can supply its own independent implementation.\n        """\n        print(f"Starting load process for {self.schema}.{table_name} (recreate={recreate}, truncate={truncate})...")\n\n        if recreate:\n            print(f"Dropping table {self.schema}.{table_name} to recreate...")\n            self.spark.sql(f"DROP TABLE IF EXISTS {self.schema}.{table_name}")\n\n        create_fn(df, table_name)\n\n        if truncate and not recreate:\n            print(f"Truncating table {self.schema}.{table_name}...")\n            self.spark.sql(f"TRUNCATE TABLE {self.schema}.{table_name}")\n\n        insert_fn(df, table_name)\n        print("Load complete.")\n        return True\n<\/pre><\/div>\n\n\n
    Deep Dive: The Medallion Subclasses<\/h2>\n\n\n\n
    With the callable-injection approach, each load_*<\/code> method in a subclass is entirely self-contained. It defines its own create<\/code> and insert<\/code> as inner functions (closures), which naturally capture self<\/code> from the enclosing method scope \u2014 giving them full access to self.spark<\/code> and self.database_name<\/code> without any extra wiring. These two functions are then passed directly into _table_load<\/code> as the strategy for that specific table.<\/p>\n\n\n\n
    This means there is no shared create<\/code> or insert<\/code> logic on the class itself to accidentally inherit or override incorrectly. A Bronze<\/strong> load_dim_date<\/code> can use explicit Spark SQL DDL to define a strict schema, while a Bronze<\/strong> load_dim_customer<\/code> can infer the schema from the DataFrame. Meanwhile, a Silver<\/strong> load_dim_customer<\/code> can run a full Delta Lake MERGE<\/code> statement \u2014 all living independently in the same codebase without any coupling between them.<\/p>\n\n\n
    \nclass BronzeLoader(LakehouseLoader):\n\n    def load_dim_date(self, df: DataFrame, recreate: bool = False, truncate: bool = False):\n\n        def create(df: DataFrame, table_name: str):\n            # Use Spark SQL DDL for an explicit schema definition\n            self.spark.sql(f"""\n                CREATE TABLE IF NOT EXISTS {self.schema}.{table_name} (\n                    date_key INT,\n                    full_date DATE,\n                    year INT,\n                    month INT,\n                    day INT\n                ) USING DELTA\n            """)\n\n        def insert(df: DataFrame, table_name: str):\n            # Bronze logic: straight append via DataFrame API\n            print("Appending records to Bronze dim_date...")\n            df.write.format("delta").mode("append").saveAsTable(f"{self.schema}.{table_name}")\n\n        self._table_load(df, "dim_date", create, insert, recreate=recreate, truncate=truncate)\n\n    def load_dim_customer(self, df: DataFrame, recreate: bool = False, truncate: bool = False):\n\n        def create(df: DataFrame, table_name: str):\n            # Infer schema from the DataFrame itself\n            df.limit(0).write.format("delta").mode("ignore").saveAsTable(f"{self.schema}.{table_name}")\n\n        def insert(df: DataFrame, table_name: str):\n            # Bronze logic: straight append via DataFrame API\n            print("Appending records to Bronze dim_customer...")\n            df.write.format("delta").mode("append").saveAsTable(f"{self.schema}.{table_name}")\n\n        self._table_load(df, "dim_customer", create, insert, recreate=recreate, truncate=truncate)\n\n\nclass SilverLoader(LakehouseLoader):\n\n    def load_dim_customer(self, df: DataFrame, recreate: bool = False, truncate: bool = False):\n\n        def create(df: DataFrame, table_name: str):\n            # Infer schema from the DataFrame itself\n            df.limit(0).write.format("delta").mode("ignore").saveAsTable(f"{self.schema}.{table_name}")\n\n        def insert(df: DataFrame, table_name: str):\n            # Silver logic: Deduplicate and MERGE via Spark SQL\n            print("Deduplicating and merging into Silver dim_customer...")\n            df.createOrReplaceTempView("silver_dim_customer_staging")\n            self.spark.sql(f"""\n                MERGE INTO {self.schema}.{table_name} AS target\n                USING silver_dim_customer_staging AS source\n                ON target.customer_key = source.customer_key\n                WHEN MATCHED THEN UPDATE SET *\n                WHEN NOT MATCHED THEN INSERT *\n            """)\n\n        self._table_load(df, "dim_customer", create, insert, recreate=recreate, truncate=truncate)\n\n<\/pre><\/div>\n\n\n
    (You could easily follow this same pattern for a GoldLoader<\/code> \u2014 its load_*<\/code> methods would define insert<\/code> inner functions that perform aggregation and write to summary tables, while create<\/code> could provision the Gold schema via DDL.)<\/em><\/p>\n\n\n\n
    The “Skinny Notebook” Paradigm<\/h2>\n\n\n\n
    Because all the complex, repeatable logic is abstracted away into our Python module, the actual Fabric Notebook becomes incredibly clean. A 100-cell monolith can often be reduced to just 2 or 3 cells.<\/p>\n\n\n\n
    One of the conveniences that makes this even cleaner is Microsoft Fabric’s default Lakehouse<\/strong> feature. When you attach a default Lakehouse to a notebook, Spark resolves table references using the short schema.table<\/code> two-part name \u2014 there is no need to qualify the full three-part lakehouse.schema.table<\/code> path. Our LakehouseLoader<\/code> is built around this: the schema<\/code> argument (typically \"dbo\"<\/code> for the Lakehouse default schema) is the only context needed to resolve any table, keeping the instantiation simple and portable across environments.<\/p>\n\n\n\n
    The notebook’s only<\/em> job is to retrieve parameters and call the loader:<\/p>\n\n\n
    \n# Cell 1: Retrieve Parameters (passed in by Fabric Pipeline)\nsource_table = mssparkutils.env.get("SOURCE_TABLE")\n# The framework knows the target based on the specific load method\n\n# Cell 2: Execute Load via our OO Classes\n# Requires a default Lakehouse to be attached to this notebook.\n# Tables are resolved as schema.table (e.g. dbo.dim_customer).\nfrom lakehouse_utils import SilverLoader\n\ndf_bronze = spark.read.table(source_table)\nloader = SilverLoader(spark, schema="dbo")\nloader.load_dim_customer(df_bronze, recreate=False, truncate=False)\n\n<\/pre><\/div>\n\n\n
    Orchestration with Microsoft Fabric Pipelines<\/h2>\n\n\n\n
    The final piece of the puzzle is moving the control flow out of the notebook. If you have Python if\/else<\/code> statements determining which tables to load, or for<\/code> loops iterating over dependencies, those should be ripped out and placed into Fabric Pipelines<\/strong>.<\/p>\n\n\n\n
    A typical pipeline structure looks much cleaner:<\/p>\n\n\n\n
      \n
    1. Copy Data Activity<\/strong>: Ingests files into the Lakehouse.<\/li>\n\n\n\n
    2. Notebook Activity (Bronze)<\/strong>: Triggers the heavily structured Bronze load.<\/li>\n\n\n\n
    3. Notebook Activity (Silver)<\/strong>: Triggers only upon the success of the Bronze activity.<\/li>\n<\/ol>\n\n\n\n
      The Pipeline handles retries, concurrency, and dependencies. The Notebook handles Spark execution. The .py<\/code> modules handle the repeatable business logic. <\/p>\n\n\n\n
      Benefits & Results<\/h2>\n\n\n\n
      Making this architectural shift usually yields massive improvements:<\/p>\n\n\n\n