Write it in Python.
Run it as SQL.
Define a ChalkSQL function in plain Python. Chalk compiles it once into a native expression, so every query that calls it runs vectorized inside the engine, with no Python interpreter evaluating rows one at a time.
Four functions, a million shipments
Tidewater Freight, a fictional carrier, prices and scores its shipments with small Python functions. Each one is registered with CREATE FUNCTION and called from a ChalkSQL query over 1 or 10 million generated shipments. The same function also runs in plain Python over the same rows, to check the answer and to time calling it once per row. What you see first is a recorded run; Run live registers the function and runs the query again.
def shipping_cost(weight_kg: float, distance_km: float, express: bool) -> float:
base = 4.99 + 1.25 * weight_kg + 0.02 * distance_km
if weight_kg > 20:
base = base + 15.0
if express:
return base * 1.8
return base
CAST(if(express, (if((NOT is_nan(weight_kg) and (20.0 < weight_kg)), (((4.99 + (1.25 * weight_kg)) + (0.02 * distance_km)) + 15.0), ((4.99 + (1.25 * weight_kg)) + (0.02 * distance_km))) * 1.8), if((NOT is_nan(weight_kg) and (20.0 < weight_kg)), (((4.99 + (1.25 * weight_kg)) + (0.02 * distance_km)) + 15.0), ((4.99 + (1.25 * weight_kg)) + (0.02 * distance_km)))) AS double)
-- shipments: a CTE that generates the rows, shown under Getting started
SELECT express,
count(*) AS shipments,
avg(cost) AS avg_cost,
max(cost) AS max_cost
FROM (SELECT express, tidewater_shipping_cost(weight_kg, distance_km, express) AS cost FROM shipments)
GROUP BY express
ORDER BY express| express | shipments | avg_cost | max_cost |
|---|---|---|---|
| false | 800,000 | $67.49 | $129.18 |
| true | 200,000 | $121.39 | $232.45 |
The compiled function used 7.1× less CPU than calling it once per row in Python, and added 8 ms to the query's wall time: the engine evaluates it in parallel, inside the same vectorized step that reads the rows.
- Compiled once by CREATE FUNCTION
- 57 ms
- Whole query, engine time
- 477 ms
- Same query without the function
- 469 ms
- Result matches the Python run
- yes
How the timing is measured: ChalkSQL's cost is the CPU its projection steps used, minus the same query with the function call replaced by a plain column expression, both read from the engine's own performance summary. Python's is the time to call the function once per row over the same columns on one core of the demo backend.
From def to a query plan
A Python UDF usually means shipping rows to an interpreter and back, one call at a time. ChalkSQL compiles the function instead, so a query sees an expression it already knows how to run fast.
Write a Python function
Arithmetic, branches, local variables, string methods and the math, datetime, re, json, hashlib and statistics modules. Type hints give the argument and return types.
CREATE compiles it once
Chalk's symbolic interpreter traces the function's bytecode and turns it into one native expression. The catalog stores that expression, not the Python.
Queries run it vectorized
Calling the function inlines the expression into the query plan, so it runs on whole column batches, across the engine's threads, in the same step that reads the rows.
One statement to register a function
Run CREATE FUNCTION from the SQL editor in the dashboard, the chalk CLI or run_sql in Python, then call the function from any ChalkSQL query. Not sure a function will compile? The resolver interpreter shows you line by line, using the same Rust compiler.
Try the resolver interpreter-- The body is plain Python. CREATE compiles it once into a native expression;
-- a body that cannot be compiled is rejected with the reason.
CREATE OR REPLACE FUNCTION delivery_risk(distance_km DOUBLE, weight_kg DOUBLE, express BOOLEAN)
RETURNS VARCHAR
LANGUAGE PYTHON AS $$
def delivery_risk(distance_km: float, weight_kg: float, express: bool) -> str:
score = math.sqrt(distance_km) / 40 + weight_kg / 100
if express:
score = score * 1.5
if score > 1.0:
return "high"
if score > 0.6:
return "medium"
return "low"
$$;
-- Then use it like any built-in function.
SELECT delivery_risk(distance_km, weight_kg, express) AS risk_band, count(*) AS shipments
FROM shipments
GROUP BY 1What compiles, and what happens when it doesn't
The compiler only accepts what it can reproduce exactly, so a function you can create is a function you can trust in a query.
Python semantics, kept
The expression reproduces what the Python would do, edge cases included: a NULL argument returns NULL, NaN comparisons follow Python, and math.sqrt of a negative raises math's own domain error.
Compiled, or rejected
There is no silent fallback to running Python per row. A body that reads files, imports a library or calls something the interpreter cannot trace fails at CREATE time, with the reason.
Works anywhere SQL does
Use a function in SELECT, WHERE, GROUP BY, joins and other functions' arguments. Aggregate functions work with GROUP BY, HAVING and FILTER.
No interpreter at query time
Nothing runs row by row and no Python process sits in the query path, so a function costs about as much as the built-in functions it compiles to.
Two compilers
The default compiler is the static accelerator Chalk uses for Python resolvers. COMPILER RUST uses its Rust port, with no Python at all.
Check before you create
Paste a function into the resolver interpreter demo to see which lines compile and what stops the rest, before you register it.
Keep your logic in Python
Bring the scoring rules, pricing formulas and cleanup code your team already writes in Python. Talk to an engineer about running them natively in ChalkSQL.