Microsoft Fabric
Explore Data with PySpark in Microsoft Fabric
Learn PySpark in Microsoft Fabric step by step by reading Lakehouse data, filtering rows, creating columns, aggregating, joining, using Spark SQL and writing a Delta result.
Dans cet article
In the previous step you created a notebook, built a tiny DataFrame by hand and wrote it as a table. That proved the notebook works. This step is about the work you will actually do in notebooks every day: loading real data, looking at it, and shaping it with PySpark until it answers a question.
If you know SQL, most of this will feel familiar in purpose and unfamiliar in syntax. A DataFrame plays the role of a table or a query result, and its methods map to the clauses you already know. The real differences are underneath: Spark builds a plan before it does any work, runs it across many machines, and only executes when you ask for a result. Those differences are what this article prepares you for, before Delta tables in step 5.
By the end you will be able to:
- load data into a DataFrame and inspect its schema and rows
- select, filter and sort, including conditions that combine several tests
- create derived columns and handle nulls deliberately
- aggregate with
groupByand join two DataFrames - run the same logic in Spark SQL
- understand lazy evaluation, and why
count()andcollect()deserve care - write a result to a Lakehouse table
- choose between PySpark and Spark SQL for a task
This is not a full Spark course. It covers the operations you need to explore data confidently; the cheat sheets linked in each section go further.
The data
This step uses the same synthetic sales data as the End-to-End Sales Analytics lab, so what you learn here carries straight into it.
- Download
sales_orders.csvandcustomers.csv. - In the
lh_learningLakehouse from earlier steps, create a folderrawunder Files and upload both files into it. - Create a new notebook from the Lakehouse, so
lh_learningis attached as its default Lakehouse, and run the cells below in order.
sales_orders.csv has 250 orders from January to early October 2026, with columns including sales_order_id, customer_id, order_timestamp, channel, status, currency and grand_total. customers.csv has 50 customers with customer_id, segment, city and state. The data is synthetic; there are no real people in it.
DataFrames in SQL terms
A DataFrame is a table-shaped, typed collection of rows that Spark can process in parallel. Most of its methods correspond to SQL clauses:
| SQL concept | PySpark equivalent |
|---|---|
| Table or result set | DataFrame |
SELECT col1, col2 |
df.select("col1", "col2") |
WHERE |
df.filter(...) or df.where(...) (identical) |
ORDER BY |
df.orderBy(...) |
GROUP BY with aggregates |
df.groupBy(...).agg(...) |
JOIN |
df.join(other, on, how) |
CASE WHEN |
F.when(...).otherwise(...) |
IS NULL / IS NOT NULL |
F.col(...).isNull() / .isNotNull() |
COUNT(*) |
df.count(), or F.count(...) inside agg |
AS (alias) |
.alias("name") |
Two differences matter from the start. A DataFrame is immutable: every operation returns a new DataFrame and leaves the original unchanged. And operations are lazy: they describe work without doing it, which is covered properly later.
Load the data into a DataFrame
In a Fabric notebook, spark is already defined. It is the SparkSession, your entry point to Spark, created for you when the session starts. You do not import or build it.
from pyspark.sql import functions as F
raw_orders = (
spark.read
.option("header", True)
.csv("Files/raw/sales_orders.csv")
)
orders = raw_orders.select(
"sales_order_id",
"customer_id",
F.to_timestamp("order_timestamp").alias("order_timestamp"),
"channel",
"status",
"currency",
F.col("grand_total").cast("decimal(18,2)").alias("grand_total"),
)The relative path Files/raw/... resolves against the notebook’s default Lakehouse. CSV files carry no types, so without casting every column is a string; the select gives order_timestamp and grand_total proper types. from pyspark.sql import functions as F is the conventional import for Spark’s built-in functions, and the rest of this article uses F. throughout.
Once data lives in a Lakehouse table rather than a file, reading it is one line: spark.read.table("bronze_sales"). You will mostly work that way after step 5.
Inspect the data
Five ways to look at a DataFrame, each for a different job:
display(orders) # interactive, rich table in the notebook
orders.show(5) # plain-text first rows (standard Spark)
orders.printSchema() # column names, types, nullability
orders.columns # just the column names, as a Python list
orders.count() # number of rows: an action that scans the datadisplay()is the Fabric notebook’s interactive view: a sortable, filterable table with built-in chart options. It shows a sample of rows, not necessarily all of them. Use it for exploration.show(n)is standard Spark and prints plain text. It is useful in logs and in code that should also run outside Fabric.printSchema()is the first thing to run on any new data. It tells you whether your casts worked and which columns can be null.columnscosts nothing; it reads the schema, not the data.count()reads the data to count it. That is fine for 250 rows and something to think about for a billion. More on this under lazy evaluation.
For this dataset, orders.count() returns 250.
Select columns
select chooses columns, just like the column list in a SQL SELECT:
orders.select("sales_order_id", "order_timestamp", "grand_total").show(5)
orders.select(
F.col("sales_order_id"),
F.col("grand_total"),
(F.col("grand_total") * 0.9).alias("grand_total_after_discount"),
).show(5)F.col("name") refers to a column as an expression you can compute with. Plain strings work for simply naming columns; F.col is needed as soon as you calculate, compare or call a function. Using F.col consistently avoids confusing the two.
Select only the columns you need. A DataFrame with fewer columns is cheaper to process later, and for columnar formats like Parquet and Delta, Spark can avoid reading the others at all. The select and filter cheat sheet collects the variations.
Filter rows
filter keeps the rows that satisfy a condition, like WHERE:
large_orders = orders.filter(F.col("grand_total") > 10000)
delivered = orders.filter(F.col("status") == "Delivered")
q3 = orders.filter(
(F.col("order_timestamp") >= "2026-07-01") &
(F.col("order_timestamp") < "2026-10-01")
)where is an alias for filter; use whichever reads better to you. Note == for equality, not =. In this dataset, 121 orders have a grand_total above 10,000, and Q3 2026 contains 83 orders.
Combining conditions: &, | and parentheses
This is where almost every SQL user trips once:
# Correct: & for AND, | for OR, ~ for NOT, each condition in parentheses
orders.filter((F.col("status") == "Delivered") & (F.col("grand_total") > 10000))
orders.filter((F.col("channel") == "Web") | (F.col("channel") == "Mobile"))
orders.filter(~(F.col("status") == "Cancelled"))
# Also correct, and clearer for lists of values
orders.filter(F.col("channel").isin("Web", "Mobile"))Two rules:
- Use
&,|and~, not Python’sand,orandnot. Spark conditions are column expressions, not Python booleans. Usingandraises an error about converting a column to a boolean. - Put each condition in parentheses. In Python,
&binds more tightly than==and>, soF.col("status") == "Delivered" & F.col("grand_total") > 10000is parsed in the wrong order and fails.
The first query above returns 23 orders.
Sort data
(orders
.orderBy(F.desc("grand_total"))
.select("sales_order_id", "channel", "grand_total")
.show(5))
orders.orderBy("channel", F.desc("grand_total")).show(5)orderBy sorts ascending by default; F.desc() sorts descending. Sorting a large DataFrame is expensive because Spark has to bring related rows together, so sort when you need ordered output, typically at the end, not as an intermediate step.
Create derived columns
withColumn adds a column, or replaces one with the same name:
orders_enriched = (
orders
.withColumn("order_date", F.to_date("order_timestamp"))
.withColumn(
"order_size",
F.when(F.col("grand_total") >= 10000, "large")
.when(F.col("grand_total") >= 2000, "medium")
.otherwise("small"),
)
)
orders_enriched.select("sales_order_id", "order_date", "grand_total", "order_size").show(5)F.when(...).otherwise(...) is Spark’s CASE WHEN. Each withColumn returns a new DataFrame, so chaining them reads top to bottom like a recipe.
Working with dates
A few functions cover most date work:
orders_dated = (
orders_enriched
.withColumn("order_year", F.year("order_date"))
.withColumn("order_month", F.month("order_date"))
)
september = orders_dated.filter(
(F.col("order_date") >= F.lit("2026-09-01")) & (F.col("order_date") < F.lit("2026-10-01"))
)to_date drops the time part of a timestamp; year, month and dayofweek extract parts; F.lit() turns a Python value into a Spark literal. Filter on half-open ranges (>= start, < end) so timestamps late on the last day are not lost.
Handle nulls deliberately
Real data has missing values, and how you treat them is a business decision. This sample file happens to have none, which you can confirm:
orders.filter(F.col("customer_id").isNull()).count() # 0 in this dataset
orders.select([
F.sum(F.col(c).isNull().cast("int")).alias(c) for c in orders.columns
]).show() # null count per columnWhen nulls do appear (and the join below will create some), the tools are:
isNull()/isNotNull()to find or exclude them.fillna()to replace them:df.fillna({"target": 0, "region": "Unassigned"}).dropna()to remove rows with nulls:df.dropna(subset=["customer_id"]).
Do not fill everything with zero or an empty string out of habit. A missing order amount is not a zero amount, and replacing it changes every total that includes it. Decide per column what a null means, and keep the null when “unknown” is the honest answer. The NULL lesson in SQL Server Foundations explains the same thinking in SQL terms.
Aggregate with groupBy
groupBy(...).agg(...) is GROUP BY with aggregate functions:
orders.groupBy("channel").agg(
F.count("*").alias("orders"),
F.round(F.avg("grand_total"), 2).alias("avg_order_value"),
).orderBy("channel").show()
orders.groupBy("status").count().show()The second form, .count() directly after groupBy, is a shortcut for counting rows per group. Use agg when you need several aggregates or want to name the results. The aggregations cheat sheet covers countDistinct, min/max, conditional aggregates and more.
A daily sales summary
Here is something useful: one row per day with the number of orders, total sales and average order value, excluding cancelled orders.
daily_sales = (
orders_enriched
.filter(F.col("status") != "Cancelled")
.groupBy("order_date", "currency")
.agg(
F.count("*").alias("order_count"),
F.sum("grand_total").alias("total_sales"),
F.round(F.avg("grand_total"), 2).alias("avg_order_value"),
)
.orderBy("order_date", "currency")
)
display(daily_sales)Notice currency in the grouping. Orders in this dataset are in USD and CAD, and adding two currencies together produces a number that means nothing. Grouping by currency, or converting first, is the kind of detail that separates a quick exploration from a correct summary. A table like this, one row per day and currency with business measures, is a small preview of a Gold-layer table.
Join DataFrames
Joins need two DataFrames. Load the customers:
customers = (
spark.read.option("header", True).csv("Files/raw/customers.csv")
.select("customer_id", "segment", "city", "state")
)
orders_with_customers = orders_enriched.join(customers, on="customer_id", how="inner")
orders_with_customers.groupBy("segment").agg(F.sum("grand_total").alias("sales")).show()on="customer_id"joins on a column with the same name on both sides, and keeps a singlecustomer_idcolumn in the result.how="inner"keeps only matching rows;"left"keeps every row from the left DataFrame and fills missing matches with nulls.
When the key columns have different names, or other columns share a name, use an explicit condition and drop or rename duplicates:
o = orders_enriched.alias("o")
c = customers.alias("c")
joined = o.join(c, F.col("o.customer_id") == F.col("c.customer_id"), "left").drop(F.col("c.customer_id"))Otherwise the result has two customer_id columns, and selecting it later fails as ambiguous.
A left join that creates nulls
Every order in this dataset has a matching customer, so to see a left join’s nulls, use a small lookup built just for teaching: monthly targets per channel, with no target defined for the Partner channel.
targets = spark.createDataFrame(
[("Store", 60000.0), ("Web", 55000.0), ("Mobile", 45000.0)],
["channel", "monthly_target"],
)
orders_targets = orders_enriched.join(targets, on="channel", how="left")
orders_targets.filter(F.col("monthly_target").isNull()).select("channel").distinct().show() # Partner
orders_targets = orders_targets.fillna({"monthly_target": 0.0}) # only if 0 is the agreed meaningBefore joining real tables, check the key’s uniqueness on the side that should be unique. If customers contained a customer twice, every one of that customer’s orders would appear twice after the join, and every total would be wrong without any error. The joins cheat sheet covers the other join types and the duplicate checks.
Spark SQL: the same engine, in SQL
If you prefer SQL, register a DataFrame as a temporary view and query it:
orders_enriched.createOrReplaceTempView("sales_orders")
SELECT order_date,
currency,
COUNT(*) AS order_count,
SUM(grand_total) AS total_sales,
ROUND(AVG(grand_total), 2) AS avg_order_value
FROM sales_orders
WHERE status <> 'Cancelled'
GROUP BY order_date, currency
ORDER BY order_date, currency;This returns the same result as daily_sales. The %%sql cell magic runs Spark SQL in a notebook cell, and spark.sql("...") does the same from Python and returns a DataFrame. A temporary view lives only for the current Spark session and is not stored anywhere; to keep data, write a table.
PySpark and Spark SQL are two interfaces to the same engine. Both produce the same kind of plan and run the same way. The choice is about readability and who maintains the code, not about one being fundamentally faster.
| Situation | PySpark | Spark SQL |
|---|---|---|
| Step-by-step exploratory transformations | Strong: easy to build up and inspect | Fine for single queries |
| A team that thinks in SQL | Learnable, but less familiar | Natural fit |
| Complex, reusable logic (functions, parameters, tests) | Strong | Harder to modularize |
| Ad hoc aggregations | Either | Either |
| Joins and grouping | Either | Either |
| Needs Python libraries or loops | PySpark | Not possible on its own |
| Analysts sharing queries | Possible | Usually clearer |
Mixing them in one notebook is normal: a SQL cell for a quick aggregate, PySpark for the transformation pipeline.
Lazy evaluation
Everything you have done to orders so far, select, filter, withColumn, groupBy, join, has been a transformation. Transformations do not process any data. They add a step to a plan.
Only an action makes Spark execute the plan: display, show, count, collect, and writing a table.
- Source dataFiles/raw/sales_orders.csv
- DataFramespark.read ... csv()
- transformationfilter
- transformationselect
- transformationgroupBy and agg
- Execution planOptimized as a whole
- Actiondisplay, count, collect, write
- Spark runs tasksDistributed across executors
That is why the cells defining large_orders or daily_sales returned instantly, and why the display() that followed took longer. It also has a practical consequence: each action runs the plan again. A notebook that calls count() after every step reads the data again every time. On 250 rows you will not notice. On a large table it can turn a five-minute job into a thirty-minute one, and it is one of the first things Fabric performance troubleshooting looks for.
Count with care
count() is an action that scans the data to count it. Use it when the number matters, not to get a feel for a table’s size. For large tables, a partial look with display() or limit() tells you what the data looks like far more cheaply, and table metadata (covered in step 5) tells you how big it is.
Avoid collect() on large data
collect() returns every row to the notebook’s driver as a Python list. On a small result, such as an aggregate with a few dozen rows, that is fine. On a large DataFrame it moves the whole dataset into one machine’s memory and can fail or stall the session. To look at data, prefer display(), show(), or limit(n); to get a number, aggregate first and collect the small result.
top_channel = (orders.groupBy("channel").count()
.orderBy(F.desc("count")).limit(1).collect()[0]["channel"]) # one row: fine
See the plan with explain()
daily_sales.explain()
explain() prints the plan Spark intends to run, without running it. You do not need to understand every line yet. It is useful to know that it exists, and that the plan shows things such as which files are read, which filters are pushed down to the read, and where data has to be exchanged between machines. Those become important when something is slow.
A window function, briefly
Sometimes you need a calculation across related rows without collapsing them, like SQL’s ROW_NUMBER() OVER (...). For example, each customer’s largest order:
from pyspark.sql import Window
w = Window.partitionBy("customer_id").orderBy(F.desc("grand_total"))
largest_per_customer = (
orders.withColumn("rank", F.row_number().over(w))
.filter(F.col("rank") == 1)
)
largest_per_customer.count() # 50: one row per customerWindow functions deserve more than a paragraph; the window functions cheat sheet covers ranking, running totals and lag/lead.
Write a result
Exploring is temporary: DataFrames and temporary views disappear when the session ends. To keep a result, write it as a table:
(daily_sales
.write
.format("delta")
.mode("overwrite")
.saveAsTable("gold_daily_sales_demo"))This writes a managed Delta table in lh_learning, visible under Tables and queryable from the SQL analytics endpoint. mode("overwrite") replaces the table each time you run the cell, which is what you want while experimenting. Delta is already the default table format in Fabric Lakehouses; format("delta") just makes it explicit.
What a Delta table really is, and what append, overwrite and MERGE mean for it, is the subject of the next step, Delta Tables in Microsoft Fabric for SQL Server Engineers. The Delta read and write cheat sheet and the PySpark Delta tables cheat sheet are quick references for the write options.
If you come from SQL Server
A few shifts make PySpark click faster:
- A DataFrame is not a stored table. It is a description of a result. Nothing is persisted until you write it.
- Transformations compose. You can build a DataFrame in steps, name intermediate results, and reuse them, without creating temporary tables.
- Execution is lazy. Writing a query and running it are separate events; the action is the “run”.
- Spark distributes the work. A job is split into tasks that run in parallel on several machines, which is why it scales and also why some operations cost more than you expect.
- Joins and aggregations can shuffle data between machines. They are normal, but they are where large jobs spend their time.
- There is no clustered-index mental model. Performance comes later from how data is laid out in files, not from indexes; step 5 and the performance guide explain this.
PySpark or pandas?
You will see pandas in many Python examples. The difference:
- pandas runs on a single machine, in memory. It is excellent for small datasets and quick analysis, and often faster than Spark for them, because there is no distributed overhead.
- PySpark runs on Spark, distributed across a cluster. It is designed for data that is too large for one machine, and it is how most Fabric data engineering is done.
PySpark is not automatically faster; for a few thousand rows, pandas may well win. Converting a large Spark DataFrame to pandas with toPandas() pulls all of it to the driver, with the same risk as collect(). Use pandas for small, already-reduced results, and keep large data in Spark.
Common beginner mistakes
- Using Python
and/orinstead of&/|in filter conditions. - Forgetting parentheses around each condition.
- Calling
collect()on large DataFrames. - Running
count()after every step and re-reading the data each time. - Confusing Python values with Spark columns: comparing a column with a Python variable is fine; using a Python
ifon a column is not. - Joining without checking key uniqueness, and silently multiplying rows.
- Selecting everything when a few columns are needed.
- Filling nulls without deciding what they mean.
- Assuming code runs when the cell finishes: transformations only describe work until an action.
- Converting to pandas too early, before the data has been reduced.
Mini exercise: a daily sales table, end to end
Put it together in one notebook. Starting from the uploaded files:
from pyspark.sql import functions as F
# 1. Read the sales data
orders = (
spark.read.option("header", True).csv("Files/raw/sales_orders.csv")
.select("sales_order_id", "customer_id",
F.to_timestamp("order_timestamp").alias("order_timestamp"),
"status", "currency",
F.col("grand_total").cast("decimal(18,2)").alias("grand_total"))
)
# 2. Inspect the schema
orders.printSchema()
# 3. Filter one period: Q3 2026, without cancelled orders
q3 = orders.filter(
(F.col("order_timestamp") >= "2026-07-01") &
(F.col("order_timestamp") < "2026-10-01") &
(F.col("status") != "Cancelled")
)
# 4. Derive the order date
q3 = q3.withColumn("order_date", F.to_date("order_timestamp"))
# 5. Aggregate daily sales per currency
q3_daily = q3.groupBy("order_date", "currency").agg(
F.count("*").alias("order_count"),
F.sum("grand_total").alias("total_sales"),
F.round(F.avg("grand_total"), 2).alias("avg_order_value"),
)
# 6. Sort and 7. display
q3_daily = q3_daily.orderBy("order_date", "currency")
display(q3_daily)
# 8. Save the result as a Delta table
q3_daily.write.format("delta").mode("overwrite").saveAsTable("gold_q3_daily_sales")What to expect, using the downloadable sample files unchanged:
- Q3 2026 has 83 orders, 62 of them not cancelled.
- The result has 48 rows: one per order date and currency. There are 44 distinct dates, and on a few of them orders were placed in both USD and CAD.
- Columns:
order_date,currency,order_count,total_sales,avg_order_value. - A new table
gold_q3_daily_salesappears under Tables inlh_learning.
If your numbers differ, check the filter boundaries first: a <= instead of < on the end date, or a missing status filter, are the usual causes.
Where to go next
- Step 5: Delta Tables in Microsoft Fabric for SQL Server Engineers explains what the table you just wrote actually is.
- The End-to-End Sales Analytics lab uses this same dataset to build Bronze, Silver and Gold layers and a pipeline.
- When a notebook gets slow, start with How to Make Microsoft Fabric Notebooks Faster and notebook startup time; the PySpark performance cheat sheet collects the common fixes.
- To decide when a notebook is the right tool at all, compared with a pipeline, Dataflow Gen2 or a Copy job, see Pipelines vs Notebooks vs Dataflow Gen2 vs Copy Job. Once your data changes every day, Incremental Loads shows how to process only what changed.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Continuer à apprendre
- Aide-mémoireContenu en anglaisKQL for Microsoft Fabric Cheat SheetThe Kusto Query Language (KQL) a Microsoft Fabric data engineer uses most in Eventhouse and KQL querysets: filter, shape, sort, summarize, time windows with bin(), joins, nulls and dynamic fields, with a SQL-to-KQL mapping and seven practice exercises.Ouvrir l’aide-mémoire
- ArticleContenu en anglaisDelta Tables in Microsoft Fabric for SQL Server EngineersWhat a Delta table really is, how it differs from a SQL Server table, and what to understand about writes, keys, time travel, maintenance and the SQL analytics endpoint before using Fabric Lakehouse tables in production.Lire l’article
Que devrais-je créer ensuite ?
Je développe Javi Builds autour des problèmes concrets rencontrés par les data engineers. Dites-moi quel sujet vous aimeriez voir ensuite.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.