Learn how to build a real PySpark notebook in Microsoft Fabric from scratch — reading CSV files from Lakehouse storage, cleaning and transforming data, and writing Delta tables that are immediately queryable via SQL. This hands-on lesson teaches Spark fundamentals in the context you'll actually use them.
Imagine you've just landed a raw CSV export from your company's sales system — 500,000 rows, no data types enforced, column names like Amt and Dt that only the person who wrote the export knows the meaning of. You need to clean it up, give it proper structure, and land it somewhere your analysts can query reliably. A Dataflow Gen2 might get you part of the way there, but the moment you need to write conditional logic, loop over nested structures, or run complex transformations that go beyond Power Query's click-and-point interface, you want a notebook.
Microsoft Fabric's Spark notebooks give you a full Python environment — with PySpark, pandas, and dozens of other libraries — running on a distributed compute engine, connected directly to your Lakehouse. That last part is what makes Fabric notebooks different from a regular Jupyter notebook: the integration is native. You can read a file from your Lakehouse's Files section with a single line of code, transform it, and write it as a Delta table that immediately shows up in the Tables section — no connection strings, no credentials, no upload step. It just works.
By the end of this lesson, you'll have run a real PySpark notebook from scratch. You'll understand how Spark reads data, how DataFrames work, and how to write a Delta table that lives permanently in your Lakehouse and powers downstream reporting.
What you'll learn:
Before you start this lesson, you should have:
You do not need any prior Spark experience. We'll explain everything as we go.
Apache Spark is a distributed computing engine — meaning it can split a large dataset across many machines and process each chunk in parallel. PySpark is the Python API for Spark. When you write PySpark code, you're writing Python that sends instructions to the Spark engine, which then handles the actual heavy lifting.
In Microsoft Fabric, every notebook runs on a Spark cluster that Fabric manages for you. When you run your first cell, Fabric spins up that cluster (this is called the session startup, and it takes 30–60 seconds the first time). After that, your code executes against the live cluster until you close the session.
The central object you'll work with is the DataFrame — think of it like a table with named columns and typed data, similar to a pandas DataFrame or a SQL table. Unlike a pandas DataFrame, a Spark DataFrame is lazy: when you write a transformation, Spark doesn't actually execute it immediately. It builds up a plan and runs everything when you explicitly ask for results. This is important to understand because it means you can chain many transformations together and Spark will optimize the whole plan at once.
Key insight
Spark DataFrames are lazy. Calling .filter() or .select() doesn't run any computation — it just refines the plan. Computation happens when you call an action like .show(), .count(), or .write. This is why your first transformation often feels instant, but the next .show() takes a few seconds.
In your Fabric workspace, navigate to your Lakehouse item and open it. Inside the Lakehouse editor, you'll see two sections in the left-hand explorer panel: Files and Tables. At the top of the screen, look for the Open notebook button or use the New dropdown and select Notebook. This creates a new notebook and attaches it to your Lakehouse automatically — that attachment is what gives you the seamless file access you're about to use.
Alternatively, go to your workspace home, click New item, and choose Notebook. If you create a notebook this way, you'll need to attach your Lakehouse manually: in the left panel of the notebook, click Add lakehouse and select the one you created.
Give your notebook a meaningful name. Click the default name at the top (usually "Notebook 1") and rename it to something like sales_ingest_notebook. This matters more than it seems — when you have a dozen notebooks in a workspace, vague names become painful.
The notebook interface consists of:
Each cell can be either Code (PySpark, Python, SQL, or Scala) or Markdown (for documentation). By default, cells run Python/PySpark. You can add cells using the + Code or + Markdown buttons that appear between cells when you hover.
Tip
Get into the habit of using Markdown cells between your code cells to document what each block does. A notebook you wrote three months ago with no comments is nearly unreadable. Treat it like a report you'll hand to a colleague.
Before we can read anything, we need something to read. For this lesson, we'll use a realistic sales CSV. In a real project, this file might arrive via a pipeline or a shortcut to blob storage — but for learning purposes, we'll upload it manually.
Create a file on your computer called sales_2024.csv with this content:
order_id,customer_id,product_name,quantity,unit_price,order_date,region
1001,C441,Wireless Keyboard,2,49.99,2024-01-15,North
1002,C882,USB-C Hub,1,34.99,2024-01-15,South
1003,C441,Monitor Stand,1,79.99,2024-01-16,North
1004,C119,Wireless Keyboard,3,49.99,2024-01-17,West
1005,C882,Laptop Bag,2,59.99,2024-01-18,South
1006,C330,USB-C Hub,4,34.99,2024-01-20,East
1007,C119,Monitor Stand,2,79.99,2024-01-22,West
1008,C441,Laptop Bag,1,59.99,2024-01-23,North
In the Lakehouse explorer (the left panel of either the Lakehouse editor or your notebook), right-click on Files and choose Upload → Upload files. Select your sales_2024.csv file. Within a few seconds, it appears under Files. You can expand the Files section to confirm it's there.
Note
The Files section of a Lakehouse is raw object storage — it's part of OneLake, Microsoft's unified storage layer. Files here are not queryable by SQL automatically; they're just files. The Tables section is where Delta tables live, and those are queryable. Our job in this notebook is to promote a file into a table.
Now let's write some code. Click into the first cell of your notebook and type the following:
# Read the CSV file from the Lakehouse Files section
df_raw = spark.read.option("header", True).option("inferSchema", True).csv("Files/sales_2024.csv")
# Show the first 10 rows
df_raw.show(10)
Click the Run cell button (the triangle/play icon on the left of the cell) or press Shift+Enter.
The first time you run any cell in a new session, you'll see a message like "Starting Spark session..." — this is normal. After 30–60 seconds, the session starts and your code runs. You'll see output like a formatted table with your 8 rows.
Let's break down what that spark.read line does:
spark is the SparkSession object — your entry point to all Spark functionality. Fabric creates and injects this automatically; you never have to initialize it yourself..read gives you a DataFrameReader object.option("header", True) tells Spark to use the first row as column names.option("inferSchema", True) tells Spark to look at the data and guess the right data types (integer, string, double, etc.) rather than making everything a string.csv("Files/sales_2024.csv") specifies the path — relative to the root of your attached LakehouseThe path "Files/sales_2024.csv" is the key piece of magic here. When a Lakehouse is attached to a notebook, Fabric maps the Lakehouse's OneLake path so that "Files/" refers to the Files section and "Tables/" refers to the Tables section. You never need to write a full ABFS path like abfss://....
Now add a second cell to inspect the schema:
# Inspect the inferred schema
df_raw.printSchema()
You should see something like:
root
|-- order_id: integer (nullable = true)
|-- customer_id: string (nullable = true)
|-- product_name: string (nullable = true)
|-- quantity: integer (nullable = true)
|-- unit_price: double (nullable = true)
|-- order_date: string (nullable = true)
|-- region: string (nullable = true)
Notice that order_date was read as a string, not a date. inferSchema is convenient but imperfect — it rarely guesses dates correctly. We'll fix this in the next section.
Warning
Avoid using inferSchema on large production files. Spark has to make a full pass through the data to infer types, which doubles your read time. On files with millions of rows, define the schema explicitly using a StructType. For this tutorial, inferSchema is fine.
Real data work always involves cleaning and shaping. Let's do three things: cast the date column to a proper date type, calculate a total_amount column, and rename a column to be more descriptive.
Add a new code cell:
from pyspark.sql.functions import col, to_date, round as spark_round
df_clean = (
df_raw
# Cast order_date from string to a proper DateType
.withColumn("order_date", to_date(col("order_date"), "yyyy-MM-dd"))
# Add a calculated column: total sale amount
.withColumn("total_amount", spark_round(col("quantity") * col("unit_price"), 2))
# Rename customer_id to be more explicit
.withColumnRenamed("customer_id", "customer_code")
# Drop rows where order_id is null (defensive cleaning)
.filter(col("order_id").isNotNull())
)
df_clean.show(10)
Let's walk through what's happening:
withColumn("order_date", to_date(...)) replaces the order_date column with a properly typed date. to_date parses a string using the format you provide — "yyyy-MM-dd" matches our data's format like 2024-01-15.withColumn("total_amount", ...) creates a brand new column. We multiply quantity by unit price and round to 2 decimal places.withColumnRenamed renames a column without touching its data.filter(col("order_id").isNotNull()) removes any rows where order_id is missing — a basic but important defensive step.Notice how we chain all these operations together inside parentheses. This is idiomatic PySpark: each transformation returns a new DataFrame (Spark DataFrames are immutable — you never modify one in place), so you chain them fluently. The parentheses just let us break the chain across multiple lines for readability.
Run this cell. You should see your cleaned DataFrame with the new total_amount column and properly formatted dates.
Here's where things get powerful. A Delta table is a Parquet-based file format with an added transaction log — that log is what enables ACID transactions, time travel, and schema enforcement. When you save to Delta format in a Fabric Lakehouse, the table immediately becomes queryable via the SQL Analytics Endpoint without any additional configuration. This is the foundation of how Direct Lake mode in Power BI works — it reads Delta files directly from OneLake at query time.
Add a new cell to write your cleaned DataFrame as a Delta table:
# Write the cleaned DataFrame as a Delta table in the Lakehouse Tables section
(
df_clean
.write
.format("delta")
.mode("overwrite")
.option("overwriteSchema", True)
.saveAsTable("sales_orders_clean")
)
print("Table written successfully.")
Run this cell. It will take a few seconds. When it completes, look at the left panel of your notebook — expand the Tables section under your Lakehouse. You should see sales_orders_clean listed there. If it doesn't appear immediately, click the refresh icon next to Tables.
Let's break down the write options:
.format("delta") — write as Delta format, not plain Parquet or CSV.mode("overwrite") — if the table already exists, replace it. Other options are "append" (add rows), "ignore" (skip if exists), and "error" (the default, which throws an error if the table exists).option("overwriteSchema", True) — allows overwriting the table even if the schema has changed. Useful during development when you're iterating on column definitions.saveAsTable("sales_orders_clean") — registers the table in the Lakehouse's metastore with this name, which is how it becomes discoverableTip
Use saveAsTable("name") rather than .save("Tables/name") when working in Fabric notebooks. Both write to the same physical location, but saveAsTable also registers the table in the metastore, making it immediately queryable via SQL and visible in the Lakehouse explorer. Using .save() writes the files but doesn't register the table — you'd have to register it manually.
One of the nicest features of Fabric notebooks is that you can switch languages per cell. Add a new cell, and at the top of the cell, change the language to SparkSQL by clicking the language dropdown on the right side of the cell and selecting SparkSQL (or just type %%sql as the very first line of the cell).
%%sql
SELECT
region,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value
FROM sales_orders_clean
GROUP BY region
ORDER BY total_revenue DESC
Run this cell. You'll get a formatted result table showing revenue by region — directly querying the Delta table you just created. No connection string. No export. It's just there.
This is the real value of the Lakehouse model: you write data once, and it's immediately accessible to SQL queries, notebooks, and Power BI reports.
Every Fabric Lakehouse comes with a SQL Analytics Endpoint — a read-only SQL interface that lets you query your Delta tables using T-SQL from any SQL client or from Power BI. To verify your table is there:
SELECT TOP 5 * FROM sales_orders_cleanIf you see your data, everything worked. Your notebook wrote a Delta table that is now queryable from a T-SQL interface without any extra work. This is exactly how the Lakehouse fits into the broader Fabric architecture — it gives you the flexibility of file storage with the queryability of a warehouse.
Now it's your turn to extend what you've built. Complete the following tasks using what you've learned:
Add a new transformation: In your df_clean pipeline, add a withColumn step that creates a column called price_tier. Use pyspark.sql.functions.when to label orders as "Budget" if unit_price < 40, "Mid-range" if between 40 and 70, and "Premium" if above 70. (Hint: look up when().when().otherwise() in PySpark.)
Filter to a specific region: Create a new DataFrame df_north that contains only orders from the "North" region. Use .filter().
Write a second table: Write df_north as a Delta table named sales_orders_north using .mode("append") instead of "overwrite". Run the cell twice and check how many rows are in the table — this will demonstrate why "overwrite" vs "append" matters.
Query with SQL: Write a %%sql cell that joins sales_orders_clean and sales_orders_north to verify they share the same schema.
Key insight
Completing exercise 3 intentionally twice is meant to teach you a lesson about "append" mode. After two runs, you'll have duplicate rows — which is what happens in real pipelines when they re-run without a deduplication strategy. This is one reason medallion architecture patterns deduplicate at the Silver layer. See Implementing the Medallion Architecture in Microsoft Fabric for how teams handle this at scale.
This almost always means either the file name is wrong (check capitalization — OneLake paths are case-sensitive) or the Lakehouse isn't attached to the notebook. Open the left panel, look for your Lakehouse under "Lakehouses," and verify it shows the file you uploaded. If no Lakehouse is listed, click Add lakehouse.
Fabric starts a Spark cluster fresh for each session. On Trial capacities or small F SKUs, this can take 60–90 seconds. This is normal. Once started, subsequent cells run much faster. If it's been more than 3 minutes, try refreshing the page and running again.
This is expected behavior, not a bug. inferSchema samples the data and makes educated guesses. For columns you know the type of (especially dates), always cast explicitly using to_date, cast("integer"), or similar functions after reading. On large files, define the full schema upfront using StructType.
Click the refresh icon next to the Tables section in the left panel. The table is written to storage immediately, but the UI doesn't always auto-refresh. If it still doesn't appear after refreshing, verify the write cell completed without errors.
This happens when you run a write cell with .mode("error") (the default) on a table that already exists. Add .mode("overwrite") to your write call, or drop the table first with spark.sql("DROP TABLE IF EXISTS sales_orders_clean").
Warning
Be careful with DROP TABLE in production environments. Dropping a managed Delta table also deletes the underlying Parquet files. If you only want to clear the registered table metadata without deleting data, use DROP TABLE on external tables only — or better yet, use mode("overwrite") in your write call so Spark handles the replacement safely.
Check your filter conditions. A common mistake is filtering on a string column with the wrong case: filter(col("region") == "north") returns no rows if the data stores it as "North". Use .show() at intermediate steps to inspect the DataFrame before and after each transformation.
You've just written a complete PySpark notebook that reads a raw CSV file from Lakehouse storage, cleans and enriches the data with typed columns and calculated fields, and writes a queryable Delta table — all in under 20 lines of code. More importantly, you understand why each piece works: the SparkSession, lazy evaluation, the read/write API, and the relationship between the Files and Tables sections of a Lakehouse.
Here's what you built:
withColumn, to_date, withColumnRenamed, and filtersaveAsTable, immediately queryable via SparkSQL and T-SQLoverwrite vs append) and their implicationsWhere to go from here:
If you want to go deeper on what PySpark can do for complex data transformations — including joins, window functions, and working with nested JSON — Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables is the natural next step.
Once you're comfortable writing notebooks manually, you'll want to automate them — running on a schedule or triggered by upstream pipeline events. Orchestrating Loads with Fabric Data Pipelines covers how to call a notebook from a pipeline and pass parameters into it.
And if your data is landing in the Lakehouse from external cloud sources like Azure Data Lake Storage or S3 — and you want to avoid copying it — read about OneLake shortcuts, which let your notebook read remote data as if it were local.