Categories
Fabric

Thinking in SQL, Working in Spark

I’ve spent years shaping data with SQL Server, however after pulling at the threads of Fabric I’m opening notebooks and finding PySpark.

At first glance the difference is stark, but it’s not quite the dramatic shift it appears. If you’re not familiar, let’s look at what’s very similar, and where the true differences are.

Same data, different language

Spark is the engine which powers our notebooks, and the most common flavours for interacting with it are: the SQL interface using Spark SQL, and the Python approach of PySpark.

A SQL developer may default to Spark SQL due to syntax familiarity, however with the prominence of Python in engineering, it’s worth understanding how familiar PySpark really is. I’m sure SQL developers could easily interpret this example:

result = (
    sales
    .filter(year(col("OrderDate")) == 2026)
    .withColumn("LineTotal", col("UnitPrice") * col("Quantity"))
    .select("CustomerName", "SalesOrderNumber", "LineTotal")
    .groupBy("CustomerName")
    .agg(
        count("*").alias("SalesQty"),
        sum("LineTotal").alias("SalesTotal")
    )
    .orderBy(col("SalesTotal").desc())
    .limit(10)
)

Common SQL syntax is easily identifiable:

  • .filter replaces the WHERE clause
  • .withColumn used to add calculated columns
  • .select to define column list like SELECT
  • .groupBy like GROUP BY for grouping prior to aggregation
  • .agg defines aggregates to be applied to grouped data
  • .orderBy sorts data as ORDER BY would
  • .limit looks like TOP but can limit results at any point in the transformation sequence

We’re now using a programming language to define our query. Consider each function applying logic and passing the result to the next function. This is function chaining and means that we don’t write queries in the same order.

Instead of SELECT / FROM / WHERE / GROUP BY / ORDER BY we now chain them in a logical order of operations, for example FROM / WHERE / SELECT / GROUP BY / ORDER BY. I find this a much more logical way to approach querying data, but it can be a mental shift.

Applying the same functions in a different order can change results dramatically, similar to Power Query transformations. For example if the .limit was moved to after the .select(), only 10 records would be presented for grouping.

Order is absolutely critical.

What we’re used to doing with SQL is fundamentally transferable to PySpark, and because of that, I’ve found the language to be very approachable. It’s the same intent – with different syntax.

Foundational differences

Whilst we see similarities in SQL and Spark syntax on the surface, under the covers it’s a different beast. Here are the two key distinctions which jumped out for me:

Firstly, both platforms have the ability to divide execution. Where SQL Server supports parallelism across multiple cores on a single node, Spark goes a step further and distributes work across multiple nodes in a cluster. This means execution isn’t limited by a single node, but by the availability of nodes to scale to.

Secondly – and more interestingly – Spark uses lazy evaluation by default. That means when declaring a query, nothing may be executed until the results are needed. It allows us to build a query in multiple steps, unlike the monolith SQL queries we’ve no doubt seen. From our example above, we could write the equivalent of:

interim = (
    sales
    .filter(year(col("OrderDate")) == 2026)
    .withColumn("LineTotal", col("UnitPrice") * col("Quantity"))
    .select("CustomerName", "SalesOrderNumber", "LineTotal")
)

result = (
    interim
    .groupBy("CustomerName")
    .agg(
        count("*").alias("SalesQty"),
        sum("LineTotal").alias("SalesTotal")
    )
    .orderBy(col("SalesTotal").desc())
    .limit(10)
)

The interim DataFrame looks like an intermediary dataset, but it’s only a set of transformations. Nothing will be executed until an action requires output, such as the .show() or .count() functions.

When actions require output from the result DataFrame, Spark will combine and optimise all transformations from both the interim and result frames before executing them. That’s a drastic departure from breaking a SQL query into temp tables, where each stage is materialised, regardless of whether it’s used later.

The key takeaways:

  • Scalability is across multiple nodes for Spark rather than being constrained to a single server
  • Lazy evaluation lets us break transformations into readable steps without materialising results

Wrap up

Moving from SQL- to Spark-based transformations can feel daunting. PySpark changes how we express queries, but the approach is surprisingly familiar.

Underneath, Spark is a very different engine, bringing with it distributed execution and lazy evaluation. They’re new concepts, but so were set-based operations once.

With a familiar way of thinking about transformations, Spark is already less like starting over and more about learning a new way to express what we already know.

Leave a comment