Databricks DE · Topic 6.2

Built-in Operators

The drag-and-drop building blocks of a Lakeflow Designer flow — each maps to a familiar SQL/PySpark transform, with SQL/Python escape hatches for the rest. This deep dive shows the generated code per operator.

filter · join · aggregatepivot · combinegenerated SQL/PySparkSQL/Python escape

The one-liner

Operators are transformation steps you wire source → … → output. The common ones — Filter, Aggregate, Join, Pivot, Transform, Combine, Sort, Limit — map to standard SQL/PySpark; SQL and Python operators (and UDFs) cover anything else.

🧱 Analogy: operators are LEGO bricks for data — each brick does one shaped job (filter, join, group), and you snap them together into the pipeline you want.

Operator explorer — click each

▸ see what it does + its SQL equivalent

🔎

Filter

Aggregate

🔗

Join

🔄

Pivot

✏️

Transform

➕

Combine

↕️

Sort / Limit

⌨️

SQL / Python

Filter — keep only rows meeting conditions (equality, range, contains, null checks).

≈ WHERE

Aggregate — group rows and compute SUM/AVG/COUNT/MIN/MAX/MEDIAN/PERCENTILE…

≈ GROUP BY + agg fns

Join — combine two tables on a key (inner / left / right / full, with optional custom expressions).

≈ JOIN

Pivot — reshape rows↔columns with aggregations (pivot / unpivot).

≈ PIVOT

Transform — select, rename, reorder, and add calculated columns (NL or expression).

≈ SELECT / derived columns

Combine — merge tables via union / intersect / except (keep or drop duplicates).

≈ UNION / INTERSECT / EXCEPT

Sort / Limit — order by columns (asc/desc) and cap the row count.

≈ ORDER BY / LIMIT

SQL / Python — escape hatch: custom SQL query or PySpark for transforms the visual operators don't cover. Plus user-defined operators.

≈ raw query / PySpark

Operator → generated code

Filter (≈ WHERE)

SELECT * FROM orders WHERE amount > 0 AND order_date >= current_date() - INTERVAL 90 DAYS;
# PySpark: df.filter("amount > 0 AND order_date >= current_date() - INTERVAL 90 DAYS")

Join (≈ JOIN — inner/left/right/full)

SELECT o.*, c.segment FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;
# PySpark: orders.join(customers, orders.customer_id == customers.id, "left")

Aggregate (≈ GROUP BY + agg fns)

SELECT region, sum(amount) AS revenue, count(*) AS n FROM orders GROUP BY region;
# PySpark: orders.groupBy("region").agg(F.sum("amount").alias("revenue"), F.count("*").alias("n"))

Pivot / Transform / Combine

-- Pivot: rows ↔ columns
SELECT * FROM orders PIVOT (sum(amount) FOR region IN ('US','EU','APAC'));
-- Transform: select / rename / derive
SELECT order_id, amount, amount * 0.1 AS tax, upper(region) AS region FROM orders;
-- Combine: union / intersect / except
SELECT * FROM orders_us UNION ALL SELECT * FROM orders_eu;

Custom (SQL / Python) operator & UDFs

# Python operator: arbitrary PySpark on the upstream DataFrame
from pyspark.sql import functions as F
def transform(df):
    return df.withColumn("clean_email", F.lower(F.trim("email")))

Build a gold flow — click to add operators

▸ assemble a top-10-regions-by-revenue flow

+ Join + Filter + Aggregate + Sort + Limit

orders▸gold

Operator cheat-sheet

Operator	SQL equivalent	Does
Filter	`WHERE`	keep matching rows
Aggregate	`GROUP BY` + agg fns	summarize groups
Join	`JOIN`	combine two tables on a key
Pivot	`PIVOT` / unpivot	rows ↔ columns
Transform	`SELECT` / derived cols	pick/rename/compute columns
Combine	`UNION/INTERSECT/EXCEPT`	merge tables
Sort / Limit	`ORDER BY` / `LIMIT`	order / cap rows
SQL / Python	raw query / PySpark	escape hatch for the rest

Why it matters

Operators map to standard SQL/PySpark transforms — so you can reason about what a visual flow does, and know when to drop to SQL/Python.
Shows Designer is a real transformation tool, not a toy.

🏆 Real-world use case: build a gold table visually — Join orders↔customers, Filter last 90 days, Aggregate revenue by region, Sort desc, Limit top 10 — no code, compiles to a governed pipeline.

Uses, edge cases & limitations

Uses: assembling filter→join→aggregate flows visually; quick reshaping; letting analysts transform without SQL.
Edge case: transforms the visual operators don't cover → use the SQL/Python operator or a user-defined operator.
Limitation: heavy logic can be clearer in code than a long operator chain; operators inherit SDP/Designer constraints. Verify the exact operator list in the docs (it evolves).

Common gotchas

❌ Hunting for a visual operator that doesn't exist — use the SQL/Python operator.
❌ Confusing Pivot (reshape) with Aggregate (group+compute).
❌ Confusing Join (match on key) with Combine/union (stack tables).
❌ A 20-operator chain when a short SQL operator is clearer.