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.

Por JaviPublicado 14 min de lecturaPrincipiante

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 groupBy and join two DataFrames
  • run the same logic in Spark SQL
  • understand lazy evaluation, and why count() and collect() 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.

  1. Download sales_orders.csv and customers.csv.
  2. In the lh_learning Lakehouse from earlier steps, create a folder raw under Files and upload both files into it.
  3. Create a new notebook from the Lakehouse, so lh_learning is 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.

PySpark
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"),
)
PySpark17 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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 data
PySpark5 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.
  • display() 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.
  • columns costs 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:

PySpark
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)
PySpark7 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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")
)
PySpark6 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
# 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"))
PySpark9 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Two rules:

  • Use &, | and ~, not Python’s and, or and not. Spark conditions are column expressions, not Python booleans. Using and raises an error about converting a column to a boolean.
  • Put each condition in parentheses. In Python, & binds more tightly than == and >, so F.col("status") == "Delivered" & F.col("grand_total") > 10000 is parsed in the wrong order and fails.

The first query above returns 23 orders.

Sort data

PySpark
(orders
    .orderBy(F.desc("grand_total"))
    .select("sales_order_id", "channel", "grand_total")
    .show(5))

orders.orderBy("channel", F.desc("grand_total")).show(5)
PySpark6 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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)
PySpark12 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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"))
)
PySpark9 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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 column
PySpark5 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

When 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:

PySpark
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()
PySpark6 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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.

PySpark
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)
PySpark13 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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:

PySpark
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()
PySpark7 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.
  • on="customer_id" joins on a column with the same name on both sides, and keeps a single customer_id column 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:

PySpark
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"))
PySpark3 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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.

PySpark
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 meaning
PySpark9 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Before 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")
%%sql
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;
Spark SQL10 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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.

  1. Source dataFiles/raw/sales_orders.csv
  2. DataFramespark.read ... csv()
  3. transformation
    filter
  4. transformation
    select
  5. transformation
    groupBy and agg
  6. Execution planOptimized as a whole
  7. Actiondisplay, count, collect, write
  8. Spark runs tasksDistributed across executors
Transformations build a plan; actions run it. Spark records each transformation and optimizes the whole chain into one plan. Work only happens when an action asks for a result, and each action runs the plan again unless something is cached or written.

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:

PySpark
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 customer
PySpark8 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

Window 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:

PySpark
(daily_sales
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("gold_daily_sales_demo"))
PySpark5 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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/or instead 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 if on 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:

PySpark
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")
PySpark37 líneasCelda de ejemplo de solo lectura. Nada en esta página ejecuta código.

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_sales appears under Tables in lh_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

Etiquetas

  • Microsoft Fabric
  • Fabric Notebook
  • PySpark
  • DataFrames
  • Spark SQL