Beyond Syntax: The Universal Patterns Behind Modern Data Tools

"Show me the average salary for each city." A manager can ask this without knowing what a database is. An analyst answers it in SQL, a data scientist in pandas, a data engineer in PySpark. The three pieces of code look nothing alike. The question is the same, and so is the work the computer has to do: split the employees by city, then average each group.

Behind the syntax of every data tool sits a short list of operations. Once you can name the operation a question needs, a new tool stops being a new language: it becomes a new way of writing something you already know.

These operations are older than any of the tools. In 1970, Edgar Codd described a relational algebra for querying tables: keep some rows, keep some columns, combine two tables (Codd, 1970). Grouping, windows and ranking came later, but they follow the same logic: an operation on a set of rows, defined independently of how you type it.

That is only half of the story. The operation is shared; the details are not. Tools that look interchangeable make different choices about missing values, ordering and types, and the last part of this article shows five places where code that looks the same gives a different answer.

One dataset for every example

Every example below asks questions of the same eight employees and three departments. The data is small on purpose, and three details are there to cause trouble later: Ines has no recorded salary, Sara and Lea earn exactly the same, and Tom belongs to department 40, which does not exist in the departments table.

departments: 10 is Data, 20 is Web, 30 is Design.

CREATE TABLE employees (id INTEGER, name VARCHAR, city VARCHAR, salary INTEGER, dept_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Ana', 'Paris', 52000, 10), (2, 'Marc', 'Lyon', 47000, 20),
  (3, 'Sara', 'Paris', 61000, 10), (4, 'Adam', 'Lyon', 55000, 20),
  (5, 'Lea', 'Paris', 61000, 30), (6, 'Hugo', 'Lille', 49000, 10),
  (7, 'Ines', 'Lyon', NULL, 20), (8, 'Tom', 'Lille', 58000, 40);
CREATE TABLE departments (dept_id INTEGER, department VARCHAR);
INSERT INTO departments VALUES (10, 'Data'), (20, 'Web'), (30, 'Design');

Every result in this article was produced by running the code in all four tools and checking that they agree: SQL on DuckDB 1.5.5, pandas 3.0.6, Polars 1.44.2 and PySpark 4.2.0 in local mode.

Same questions, different engines

The tools overlap, but they are not interchangeable. The main difference is not syntax; it is where and when the work happens.

Two consequences for everyday work. pandas needs the data to fit in memory, which is why its documentation has a page on datasets that do not (pandas docs). And with lazy engines (Polars lazy, Spark), an error in step two may only show up when step ten asks for a result, because nothing ran before.

Filter and project: keep what matters

Names and salaries of the employees in Paris.

This question makes two separate decisions: which rows (employees in Paris) and which columns (name and salary). Relational algebra has a name for each: selection keeps rows, projection keeps columns.

The names hide a trap. In SQL, the keyword SELECT does the projection; the selection is the WHERE clause. Keeping the two decisions apart makes pandas easier to read too: .loc[rows, columns] takes both at once, rows first.

SELECT name, salary
FROM employees
WHERE city = 'Paris';

Four syntaxes, one operation. (pandas prints 52000.0; the reason is in the section on answers that differ.)

Derive: compute a new value from existing ones

Salaries in thousands.

A derivation reads values from a row and computes a new one, row by row. The number of rows does not change. A large share of what gets called cleaning or feature engineering is this operation: a unit conversion, a ratio, a date part, a normalised string.

SELECT name, salary / 1000 AS salary_k
FROM employees;

Ana gets 52.0, Marc 47.0, and so on. Ines gets a missing value in all four tools: arithmetic on a missing value produces a missing value, not zero.

Group and aggregate: many rows become one

Headcount and average salary in each city.

Grouping is two operations in a row. First group: split the rows by the value of city. Then aggregate: reduce each group to one row with a count and an average. Hadley Wickham named this pattern split-apply-combine (Wickham, 2011).

SELECT city, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY city;

Look at Lyon: three employees, but the average is (47000 + 55000) / 2. Averages skip missing values in all four tools; Ines is not counted as a zero salary. COUNT(*) counts rows, so she is in the headcount; COUNT(salary) would say 2.

Window: look around without losing the row

Each employee's salary, next to the average of their city.

GROUP BY cannot answer this: it turns eight employees into three cities, and the individual rows are gone. A window computes the same aggregate over a group of related rows, called the partition, and writes the result back on every row of that group.

The word "window" has nothing to do with time. It is the set of rows a calculation can see from the current row. Time series use windows a lot (running totals, moving averages, adding an ORDER BY and a frame), which is where the confusion comes from, but the partition alone is already a window.

SELECT name, city, salary,
       AVG(salary) OVER (PARTITION BY city) AS city_avg
FROM employees;

The name changes the most here: OVER (PARTITION BY …) in SQL, groupby().transform() in pandas, .over() in Polars, Window.partitionBy() in PySpark. Once you know you need "an aggregate without losing the rows", you know what to look for in each.

Join: match rows through a relationship

Each employee with the name of their department.

A join does not glue two tables together. For each row on one side, it looks for the rows on the other side whose key matches (dept_id here) and outputs one combined row per match. Two consequences follow from that definition.

Rows without a match. Tom's department, 40, does not exist. An inner join, the default in all four tools, drops him: 7 rows. A left join keeps every employee and fills the missing department with a missing value: 8 rows. (A right join does the same from the other side; a full join keeps unmatched rows from both.)

Rows with several matches. If departments contained department 10 twice, say under an old and a new name, each of the three Data employees would come out twice, and the inner join would return 10 rows instead of 7. When a join returns more rows than expected, a duplicated key is almost always the reason.

SELECT e.name, d.department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Rank: order within a group, then pick

The highest-paid employee in each city.

There is no single "top per group" operation. It is a window (partition by city), an order inside each partition (salary, descending), a number given to each row, and a filter that keeps number 1. The only real decision is which number, because Sara and Lea are tied:

RANK keeps both winners. ROW_NUMBER keeps exactly one, and which one is arbitrary unless the ordering has a tie-breaker (the table above adds id). Neither is right in general; it depends on whether the question means "the best-paid people" or "exactly one person per city".

pandas and Polars add a trap of their own: their rank() gives tied rows the average of their positions by default. Sara and Lea both get 1.5, so a filter on rank == 1 returns nobody in Paris. That is why the code below asks for method="min", which behaves like SQL's RANK.

SELECT name, city, salary
FROM (
  SELECT *, RANK() OVER (PARTITION BY city ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk = 1;

Composition: real questions use several operations

In each department, who earns the most, and how far above the department average?

No single operation answers this. Reading the question phrase by phrase gives the plan before any code:

WITH staff AS (
  SELECT d.department, e.name, e.salary,
         AVG(e.salary) OVER (PARTITION BY d.department) AS dept_avg,
         RANK() OVER (PARTITION BY d.department ORDER BY e.salary DESC) AS rnk
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WHERE e.salary IS NOT NULL
)
SELECT department, name, salary, salary - dept_avg AS above_avg
FROM staff
WHERE rnk = 1;

The four programs have different shapes: SQL nests a query inside a WITH, the dataframe libraries chain one step after another. The list of operations is the same in all of them, and it is the part worth writing first. The plan also forces the decisions the code would otherwise make silently: the inner join drops Tom, whose department does not exist, and the filter removes Ines. Both are choices, not accidents, and the next section shows why the filter matters more than it looks.

Same words, different answers

Translating the operation is half the work. The other half is checking what each engine does at the edges. Here are five cases where code that looks the same gives a different result. Each was reproduced on the dataset above, except the two PostgreSQL behaviours, which come from its documentation.

1. Dividing integers. SELECT salary / 12 returns 4333.33 for Ana in DuckDB, but 4333 in PostgreSQL and SQLite, where dividing two integers truncates the result (PostgreSQL docs). The query text is identical. Write salary / 12.0 when you want a decimal.

2. Missing values in a sort. ORDER BY salary DESC puts Ines last in DuckDB and Spark. PostgreSQL treats a missing value as larger than any number and puts it first in a descending sort (PostgreSQL docs). Without the filter in the previous section, PostgreSQL would name Ines the best-paid employee in the Web department. Write NULLS LAST, or filter the missing values out.

3. Missing group keys. After the left join, group the employees by department. DuckDB, Polars and PySpark return four groups, including one with a missing department that holds Tom. pandas returns three: groupby drops missing keys by default, so Tom disappears from the totals without a warning. Pass dropna=False to keep him.

4. Order of the groups. pandas sorts group keys by default (Lille, Lyon, Paris). Polars makes no promise: running the same group_by("city") thirty times returned the three cities in six different orders. SQL and Spark promise no order either. If the order matters, ask for it with ORDER BY, a sort, or maintain_order=True in Polars.

5. Missing values and types. pandas stores Ines's missing salary as NaN, a floating-point value, so the whole salary column becomes float64; that is why it printed 52000.0 earlier. Polars, DuckDB and Spark keep integers and mark the value as missing (null). Converting text is another source of surprises. Turning "n/a" into a number raises an error in DuckDB and in PySpark 4, which enables ANSI SQL mode by default since Spark 4.0 (release notes); Spark 3 quietly returned a missing value instead. Each tool has an explicit way to say "convert what you can, mark the rest as missing":

SELECT TRY_CAST(salary_text AS INTEGER) AS salary
FROM raw_salaries;

On ["52000", "n/a", "49000"], all four return 52000, a missing value, and 49000 (pandas as floats). TRY_CAST is DuckDB and Spark SQL syntax. PostgreSQL has no TRY_CAST; since version 16, pg_input_is_valid() can test a value before you cast it.

NumPy: when rows and columns stop being the point

Everything so far thinks in records: rows with named, typed columns, grouped and joined by key. NumPy asks a different question. Its unit is the array, a block of numbers of a single type in one or more dimensions, and an operation applies to the whole array at once. There are no column names and no joins. It is the right model when the data is numbers laid out in space: images, matrices, embeddings, simulations.

Two ideas carry most of the work. A mask is an array of booleans that selects elements. Broadcasting lets arrays of different shapes combine: NumPy compares the shapes from the last dimension backwards, and two dimensions are compatible when they are equal or one of them is 1 (NumPy docs).

import numpy as np
 
readings = np.array([[12.0, -0.5, 3.0],
                     [-2.0,  5.0, 7.0]])   # shape (2, 3)
 
# A mask: replace negative values, in a new array
np.where(readings < 0, 0, readings)
# [[12.  0.  3.]
#  [ 0.  5.  7.]]
 
# Broadcasting: (2, 3) minus the column means, shape (3,)
readings - readings.mean(axis=0)
# [[ 7.   -2.75 -2.  ]
#  [-7.    2.75  2.  ]]
 
# Same mask, but this time readings itself is modified
readings[readings < 0] = 0

The second line has no loop and no group: subtracting the column means from every row is one expression, because the shape (3,) is stretched to (2, 3). In dataframe terms it resembles a window over columns, but the mental model is geometry, not records.

A method for the next tool

1. State the intent in plain words

   "One number per city", "every employee with their department", "exactly one person per group". If you cannot say it, no syntax will help.

2. Name the operations

   Filter, project, derive, group and aggregate, window, join, rank. Most questions are a short sequence of these; write the sequence before any code.

3. Learn the execution model

   In memory or in a database? Eager or lazy? One machine or a cluster? It decides what is cheap, what is expensive, and when errors appear.

4. Translate one operation at a time

   Look up how the tool spells each operation, and check the result after each step on a small sample.

5. Test the edges

   Missing values, ties, duplicate keys, types, ordering. A tiny dataset with one of each, like the eight employees above, finds most surprises in minutes.

Once you can name the operation, the syntax is the easy part.

Three common operations are left for another article: sorting on its own, removing duplicates (DISTINCT), and reshaping between long and wide formats (pivot and unpivot). For reshaping, the reference is Wickham's Tidy Data (2014).

References

1. Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377–387.
2. Wickham, H. (2011). The Split-Apply-Combine Strategy for Data Analysis. Journal of Statistical Software, 40(1).
3. Wickham, H. (2014). Tidy Data. Journal of Statistical Software, 59(10).
4. PostgreSQL documentation: mathematical operators, sorting rows, window functions.
5. Apache Spark: Spark 4.0.0 release notes and ANSI compliance.
6. pandas: DataFrame.groupby (defaults sort=True, dropna=True). Polars: DataFrame.group_by (default maintain_order=False).