Write it in Python.
Run it as SQL.
This demo shows how to 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.
Compare Python results with a compiled SQL function
The same function runs over the same generated rows in Python and in ChalkSQL.
Try this experiment · expected results
1Read the recorded shipping example
Compare the Python body, compiled expression, SQL result, and result-match check.
Expected: The expression preserves the function’s result without calling Python once per row.
2Compare function cost
Switch between one and ten million rows or another function, then inspect the timing breakdown.
Expected: Separate function CPU cost from total query time; Run live executes the selected example 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
| 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
Inspect the SQL and compiled expression
-- 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 expressCAST(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)
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.
Built on Chalk
ChalkSQL reads the source through its connector, pushes supported work to that source, and runs the remaining SQL over the returned rows.
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.
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.
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.
-- 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 1Implementation details
How the example works
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.
SQL behavior and pushdown rules
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.
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.