Writing Your First PySpark Notebook in Microsoft Fabric: Reading CSV Files from OneLake, Exploring Data with DataFrames, and Writing a Delta Table to the Lakehouse | Wicked Smart Data
Writing Your First PySpark Notebook in Microsoft Fabric: Reading CSV Files from OneLake, Exploring Data with DataFrames, and Writing a Delta Table to the Lakehouse
Learn how to build a complete PySpark workflow in Microsoft Fabric from scratch. This hands-on lesson walks you through reading a CSV file from OneLake, exploring and cleaning data with Spark DataFrames, and writing a production-ready Delta table to your Lakehouse — with real code you can run immediately.
🌱 Foundation17 min readSep 22, 2026Updated Sep 22, 2026
Writing Your First PySpark Notebook in Microsoft Fabric: Reading CSV Files from OneLake, Exploring Data with DataFrames, and Writing a Delta Table to the Lakehouse
Introduction
Imagine you've just landed raw sales data in your Fabric Lakehouse — a CSV file dropped into the Files section by an upstream process. It has 200,000 rows, mixed date formats, a few suspicious nulls in the revenue column, and column names full of spaces and inconsistent casing. You need to clean it, explore it, and write it out as a proper Delta table so that downstream analysts can query it with SQL and Power BI can connect to it in Direct Lake mode. You could try to do all of this with point-and-click tools, but for jobs like this — where you need precision, repeatability, and code you can version-control — a PySpark notebook is the right tool.
PySpark is the Python interface to Apache Spark, the distributed computing engine that Microsoft Fabric uses under the hood for large-scale data processing. A Spark notebook in Fabric gives you an interactive, cell-by-cell coding environment where you write Python code, run it against Spark's engine, and see results immediately. Unlike a Python script running on your laptop, PySpark can handle hundreds of millions of rows by distributing work across multiple machines — but the code you write looks almost like regular Python, which makes it approachable even if you're new to big data.
By the end of this lesson, you'll have hands-on experience with the full basic workflow: uploading a CSV to your Lakehouse, attaching the Lakehouse to a notebook, reading the file into a Spark DataFrame, exploring and transforming it with PySpark, and writing the result out as a Delta table. That Delta table becomes a first-class citizen in your Fabric environment — queryable with SQL, usable in pipelines, and ready for reporting.
What you'll learn:
How to upload a CSV file to the Files section of a Fabric Lakehouse and reference it in a notebook
How to read a CSV file into a PySpark DataFrame with schema inference and custom options
How to explore a DataFrame: inspecting schema, viewing data, checking nulls, and computing summary statistics
How to perform basic transformations: renaming columns, filtering rows, and casting data types
How to write a cleaned DataFrame to the Lakehouse as a Delta table and verify it in the Lakehouse Explorer
Basic Python familiarity — you should be comfortable reading a for loop and understanding what a variable is. You do not need prior Spark experience.
Understanding the Landscape Before You Write a Line of Code
Before diving into the notebook, it's worth understanding what's happening behind the scenes, because Fabric's architecture shapes how you work.
Your Lakehouse lives in OneLake, which is Fabric's unified storage layer — think of it as a single intelligent data lake that spans your entire Fabric tenant. Every Lakehouse has two storage areas you'll interact with constantly: Files and Tables. Files is a raw file system zone — you can drop CSVs, Parquet files, JSON, images, anything — and Fabric won't try to interpret them. Tables is the managed zone where Delta tables live. When you write a Delta table, Fabric registers it and makes it queryable through the SQL Analytics Endpoint automatically.
A Spark notebook in Fabric runs on a Spark cluster that Fabric provisions and manages for you. When you first run a cell, Fabric starts a Spark session (this takes 30–60 seconds the first time). That session has a default Lakehouse attached to it, which means you can reference files and tables using simple paths rather than long storage account URLs. That's a significant quality-of-life improvement over working with raw Azure Data Lake Storage directly.
Note
The Spark cluster spins up when you run your first cell and stays alive while your session is active. If you leave it idle for a while, the session will time out and you'll need to restart it. You don't lose your code — just the in-memory state of your DataFrames.
Step 1: Get a CSV File into Your Lakehouse
For this lesson, we'll work with a realistic dataset: monthly regional sales transactions. Create a file on your local machine named sales_2024.csv with this content (you can copy and paste it into a text editor and save it):
Notice row 1006 has a missing revenue value — that's intentional. We'll catch and handle it later.
To upload this file to your Lakehouse, navigate to your Lakehouse in the Fabric workspace. In the left pane of the Lakehouse Explorer, you'll see two nodes: Files and Tables. Right-click on Files and choose New subfolder, then name it raw. This is a good practice — keeping raw source files in a dedicated subfolder makes it obvious what has and hasn't been processed yet.
Now right-click on the raw folder and choose Upload → Upload files. Select your sales_2024.csv file from your local machine and confirm the upload. After a moment you'll see it appear in the folder. That file is now sitting in OneLake, addressable and ready for Spark to read.
Step 2: Create a Notebook and Attach Your Lakehouse
In the Fabric workspace, click the + New button and choose Notebook. A new notebook opens with a single empty cell. Give it a meaningful name right away — click on the default name at the top (usually "Notebook 1") and rename it to something like Sales Data - Load and Transform.
Now you need to attach your Lakehouse to this notebook so Spark knows where to read from and write to. In the left sidebar of the notebook, click on Add Lakehouse. A panel will appear letting you choose an existing Lakehouse from your workspace. Select the Lakehouse where you uploaded the CSV. Once attached, you'll see the Lakehouse Explorer appear in the left pane — you can expand Files → raw and see your sales_2024.csv right there without leaving the notebook.
Tip
You can attach multiple Lakehouses to a single notebook, which is useful when you're reading from a bronze Lakehouse and writing to a silver one. The first Lakehouse you attach becomes the "default" Lakehouse, and its paths are used when you write tables without specifying a full path.
Step 3: Read the CSV File into a DataFrame
Now for the actual code. Click into the first cell and start typing. In PySpark, you read files using spark.read, which is the entry point to Spark's data reading API. The spark object is pre-created for you in every Fabric notebook — you don't need to import or initialize anything.
# Read the CSV file from the raw folder in the Lakehouse Files section
df_raw = spark.read \
.option("header", "true") \
.option("inferSchema", "true") \
.option("nullValue", "") \
.csv("Files/raw/sales_2024.csv")
Let's break down each option, because understanding them prevents a lot of future confusion:
header: true tells Spark that the first row of the file contains column names, not data. Without this, Spark would read the header as a data row and name your columns _c0, _c1, etc.
inferSchema: true tells Spark to scan the file and automatically determine the data type of each column — whether it's a string, integer, double, or date. This is convenient but has a cost: Spark has to read the file twice (once to infer, once to actually load).
nullValue: "" tells Spark to treat empty strings as null values. This is how our missing revenue on row 1006 will be handled correctly.
The path "Files/raw/sales_2024.csv" works because you attached your Lakehouse as the default — Fabric resolves this relative path to the full OneLake path automatically.
Press Shift + Enter to run the cell. Fabric will start the Spark session (watch for the status indicator at the bottom of the screen — it will say "Starting" for about 30–60 seconds), then execute the read. No output appears yet, because reading a DataFrame is lazy — Spark hasn't actually processed the file until you ask it to do something with the data.
Step 4: Explore the DataFrame
Now let's actually look at what we have. Add a new cell below the first one using the + Code button or pressing Ctrl + Alt + N.
Schema inspection should be your first move with any new dataset. Notice that inferSchema correctly identified sale_date as a date type and revenue as a double — that's the inference engine doing its job well. If you had skipped inferSchema, everything would be a string.
Preview the Data
# Show the first 10 rows
df_raw.show(10, truncate=False)
The truncate=False argument prevents Spark from truncating long string values in the display. With it, you see the full content of every cell.
Count Rows and Check for Nulls
# Total row count
print(f"Total rows: {df_raw.count()}")
# Count nulls in each column
from pyspark.sql.functions import col, sum as spark_sum, isnan, when
null_counts = df_raw.select([
spark_sum(when(col(c).isNull(), 1).otherwise(0)).alias(c)
for c in df_raw.columns
])
null_counts.show()
This is a practical pattern you'll use constantly. The list comprehension builds one expression per column that counts null values, and select assembles them into a single-row summary. When you run it, you should see revenue has a count of 1 — confirming the missing value we deliberately included.
Key insight
In PySpark, isNull() catches true null values, while isnan() catches IEEE floating-point NaN values (which are different from null). For numeric columns coming from CSVs, it's a good habit to check both. In practice, most CSV nulls become proper SQL nulls when you use .option("nullValue", ""), so isNull() is usually sufficient.
Summary Statistics
# Descriptive statistics for numeric columns
df_raw.describe("revenue", "units_sold").show()
describe() computes count, mean, standard deviation, min, and max for the columns you specify. It's a fast way to sanity-check your numeric ranges — if your min revenue is negative or your max is astronomical, you know something is wrong before you ever write the data anywhere.
Step 5: Transform the DataFrame
Exploration revealed one real problem (the null revenue) and one opportunity for improvement (column names with underscores are fine, but let's practice the rename pattern). Let's do a small but realistic set of transformations.
from pyspark.sql.functions import col, to_date, upper, coalesce, lit
df_clean = (
df_raw
# Rename columns to a consistent snake_case style (already good, but let's add a prefix)
.withColumnRenamed("transaction_id", "txn_id")
# Ensure sale_date is properly cast (it already is, but this is defensive)
.withColumn("sale_date", to_date(col("sale_date"), "yyyy-MM-dd"))
# Standardize region to uppercase
.withColumn("region", upper(col("region")))
# Fill null revenue with 0.0 and flag those rows
.withColumn("revenue", coalesce(col("revenue"), lit(0.0)))
.withColumn("revenue_imputed", col("revenue") == 0.0)
# Filter out any rows with no transaction ID (defensive check)
.filter(col("txn_id").isNotNull())
)
df_clean.show(truncate=False)
Walk through what each transformation does:
withColumnRenamed creates a new DataFrame with a column renamed. Remember: DataFrames in Spark are immutable — every transformation returns a new DataFrame rather than modifying the existing one. That's why we chain them and assign the result to df_clean.
withColumn either replaces an existing column with a new expression or adds a new column if the name doesn't exist yet.
upper(col("region")) standardizes all region values to uppercase so "Northeast" and "NORTHEAST" won't be treated as different groups in downstream aggregations.
coalesce(col("revenue"), lit(0.0)) returns the first non-null value in a list — so if revenue is null, it returns 0.0. The lit() function wraps a Python literal value so Spark can treat it as a column expression.
The revenue_imputed boolean flag is good practice: you fill the null so downstream queries don't break, but you preserve the information that the original value was missing. Silently dropping or imputing data without flagging it causes confusion later.
Warning
Chaining many .withColumn() calls on large DataFrames can sometimes cause performance issues in older versions of Spark due to how the query plan is generated. For DataFrames with dozens of transformations, consider using select() with a list of expressions instead, which tends to produce cleaner query plans.
Step 6: Write the DataFrame as a Delta Table
This is the moment where your in-memory DataFrame becomes a permanent, queryable Delta table in the Lakehouse. Delta is the default (and strongly preferred) table format in Fabric — it gives you ACID transactions, time travel, and the metadata that makes Direct Lake mode in Power BI possible.
# Write the cleaned DataFrame as a Delta table in the default Lakehouse
df_clean.write \
.format("delta") \
.mode("overwrite") \
.saveAsTable("sales_2024_clean")
The mode("overwrite") means that if a table named sales_2024_clean already exists, it will be replaced entirely. Other options are "append" (add rows without removing existing ones) and "error" (fail if the table already exists, which is the default).
saveAsTable("sales_2024_clean") writes the data and registers the table in the Lakehouse's metadata catalog. This is different from save("Tables/sales_2024_clean"), which would write the files but not register them, leaving you with an unmanaged table you'd have to manually add to the schema.
After the cell runs, switch to the Lakehouse Explorer on the left. Click the refresh button (the circular arrow icon) next to Tables. You should see sales_2024_clean appear. Click on it, and you'll see the column names and a preview of the data — all without leaving the notebook. This immediate feedback is one of the nicest aspects of the Fabric notebook environment.
Tip
After writing a Delta table, you can query it immediately using Spark SQL syntax in another cell: spark.sql("SELECT region, SUM(revenue) FROM sales_2024_clean GROUP BY region ORDER BY 2 DESC").show(). This is a great sanity check to confirm your write succeeded and the data looks correct.
Step 7: Verify with a Quick SQL Cell
Fabric notebooks support both Python and SQL cells. Add a new cell, then click the dropdown arrow next to the language indicator at the top left of the cell (it will say "PySpark") and switch it to SparkSQL. Now type:
SELECT
region,
product_category,
COUNT(*) AS transaction_count,
ROUND(SUM(revenue), 2) AS total_revenue,
SUM(units_sold) AS total_units
FROM sales_2024_clean
GROUP BY region, product_category
ORDER BY total_revenue DESC
Run it. You'll get a formatted table output showing revenue by region and category. This confirms your Delta table is live, queryable, and holding the right aggregated results. Once this table exists, you could also query it through the SQL Analytics Endpoint — the auto-provisioned T-SQL interface for your Lakehouse — without writing any more Spark code. See Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse for how that works.
Hands-On Exercise
Complete these tasks to reinforce what you've learned:
Add a derived column. Add a revenue_per_unit column to df_clean before writing the table. Calculate it as revenue / units_sold. Handle the potential division-by-zero case using a when/otherwise expression: return null when units_sold is 0, and the ratio otherwise.
Filter and write a regional subset. Create a second DataFrame called df_northeast that filters df_clean to only Northeast transactions. Write it as a separate Delta table called sales_2024_northeast. Verify it appears in the Lakehouse Explorer.
Use describe() on the clean DataFrame. Run df_clean.describe("revenue", "units_sold", "revenue_per_unit").show() and compare the statistics before and after your transformations. Specifically, check whether the null in revenue affected the mean calculation in df_raw versus df_clean.
Explore with a SQL cell. Write a SparkSQL query against sales_2024_clean that finds the salesperson with the highest total revenue. Try to do it with a single SQL statement using GROUP BY and ORDER BY with LIMIT.
Common Mistakes & Troubleshooting
"File not found" when reading the CSV
This almost always means the path is wrong or the Lakehouse isn't attached as the default. Double-check that your CSV is in Files/raw/ (not Tables/raw/), that the filename matches exactly including case, and that the Lakehouse appears in the left sidebar of the notebook. If you attached it after the session started, try restarting the kernel and re-running.
The schema shows all columns as strings
You forgot .option("inferSchema", "true") or the file had unexpected characters that confused the inference engine. As a fallback, you can define the schema manually using StructType and StructField from pyspark.sql.types — this is actually preferred in production because it's deterministic and doesn't require Spark to read the file twice.
df.show() displays only 20 rows
That's the default. Pass a number: df.show(100). For very large DataFrames, show() will still only pull the first N rows without scanning the entire dataset.
Writing the table fails with "Table already exists"
You probably used the default write mode ("error") or forgot to specify a mode at all. Add .mode("overwrite") or .mode("append") to your write call depending on what you intended.
The Spark session is slow to start
This is normal for the first cell you run in a new session. Fabric provisions a Spark cluster on demand — the 30–60 second wait is the cluster starting up, not your code being slow. Subsequent cells in the same session run much faster.
Warning
Avoid calling df.count() or df.show() repeatedly on large DataFrames during development — each call triggers a full scan of the data. Instead, call them once, capture the result, or use df.cache() to keep the DataFrame in memory if you'll be referencing it multiple times in quick succession.
Column name has spaces and causes errors
When column names contain spaces (common in raw CSVs), you need to use backtick quoting in Spark SQL: SELECT `sale date` FROM my_table. In PySpark DataFrame API code, wrap the name in backticks inside a col() call: col("`sale date`"). The cleaner fix is to rename all columns at load time using withColumnRenamed or a loop that replaces spaces with underscores.
Summary & Next Steps
You've just completed the core Spark notebook workflow in Microsoft Fabric. You uploaded raw CSV data to OneLake, read it into a PySpark DataFrame with proper options, explored its schema and quality, applied meaningful transformations, and wrote a clean Delta table back to the Lakehouse. That Delta table is now a permanent, versioned, queryable asset — visible in the Lakehouse Explorer, queryable via T-SQL, and ready for reporting.
This is the foundation everything else is built on. The patterns you practiced here — spark.read, df.printSchema(), df.show(), withColumn, and df.write.saveAsTable() — appear in virtually every PySpark notebook you'll ever write in Fabric, whether the file is 10 rows or 10 billion.
