> ## Documentation Index
> Fetch the complete documentation index at: https://docs.zwiron.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Transform Function Reference
> Complete reference for Zwiron computed-column functions — math, string, date, conditional, and aggregation.
Computed columns let you derive new values from existing data as it moves through a pipeline. You pick a function, point it at one or more source columns, and Zwiron produces a new column at the destination — without touching your source.
This page is the complete reference for every available function.
***
## How to read this reference
Each function entry shows:
* **What it does** — one sentence
* **Signature** — `function_name(input, ...)` where quoted strings are literals and bare names are columns
* **Example** — a realistic before/after
***
## Math
### Arithmetic
| Function | What it does | Example |
| ------------------ | --------------------- | --------------------------------------------------- |
| `add(a, b)` | a + b | `add(price, tax)` → total |
| `subtract(a, b)` | a − b | `subtract(revenue, cost)` → profit |
| `multiply(a, b)` | a × b | `multiply(quantity, unit_price)` → line\_total |
| `divide(a, b)` | a ÷ b | `divide(revenue, sessions)` → revenue\_per\_session |
| `power(base, exp)` | base raised to exp | `power(growth_rate, "12")` → annual\_growth |
| `abs(x)` | Absolute value | `abs(balance)` → balance\_unsigned |
| `negate(x)` | Flip the sign | `negate(discount)` → negative\_discount |
| `sign(x)` | Returns −1, 0, or 1 | `sign(net_change)` → direction |
| `sqrt(x)` | Square root | `sqrt(variance)` → stddev\_approx |
| `modulo(a, b)` | Remainder of a ÷ b | `modulo(row_id, "10")` → bucket |
| `greatest(a, b)` | Larger of two values | `greatest(bid, reserve_price)` → floor\_price |
| `least(a, b)` | Smaller of two values | `least(quota, capacity)` → effective\_limit |
### Logarithms
Use these when normalizing skewed distributions or working with ML feature engineering.
| Function | What it does | Example |
| ---------- | -------------------- | ------------------------------------ |
| `ln(x)` | Natural log (base e) | `ln(page_views)` → log\_views |
| `log2(x)` | Log base 2 | `log2(file_size_bytes)` → size\_bits |
| `log10(x)` | Log base 10 | `log10(revenue)` → log\_revenue |
### Rounding
| Function | What it does | Example |
| --------------------------- | ------------------------------ | ----------------------------------------- |
| `round(x)` | Round to nearest integer | `round(avg_rating)` → star\_rating |
| `round(x, "N")` | Round to N decimal places | `round(price, "2")` → price\_cents |
| `round_to_multiple(x, "N")` | Round to nearest multiple of N | `round_to_multiple(age, "5")` → age\_band |
| `floor(x)` | Round down to integer | `floor(score)` → score\_floor |
| `ceil(x)` | Round up to integer | `ceil(duration_mins)` → billed\_minutes |
| `trunc(x)` | Truncate decimal part | `trunc(amount)` → whole\_dollars |
***
## String
### Case & whitespace
| Function | What it does | Example |
| ----------------- | -------------------------------------- | --------------------------------------------- |
| `string_upper(s)` | ALL CAPS | `string_upper(country_code)` → country\_upper |
| `string_lower(s)` | all lowercase | `string_lower(email)` → email\_normalized |
| `initcap(s)` | Title Case | `initcap(full_name)` → display\_name |
| `string_trim(s)` | Remove leading and trailing whitespace | `string_trim(username)` → username\_clean |
| `string_ltrim(s)` | Remove leading whitespace only | `string_ltrim(raw_value)` → ltrimmed |
| `string_rtrim(s)` | Remove trailing whitespace only | `string_rtrim(raw_value)` → rtrimmed |
### Measurement & manipulation
| Function | What it does | Example |
| ----------------------------------------- | ------------------------- | --------------------------------------------------- |
| `string_length(s)` | Number of characters | `string_length(description)` → desc\_length |
| `string_reverse(s)` | Reverse the string | `string_reverse(code)` → reversed\_code |
| `string_concat(a, b)` | Join two columns | `string_concat(first_name, last_name)` → full\_name |
| `string_repeat(s, "N")` | Repeat the string N times | `string_repeat(separator, "3")` → divider |
| `string_replace(s, pattern, replacement)` | Replace first match | `string_replace(phone, "-", "")` → phone\_clean |
### Search & extract
| Function | What it does | Example |
| --------------------------------- | ---------------------------------- | ------------------------------------------------------- |
| `string_contains(s, "substr")` | True if string contains substring | `string_contains(email, "@company.com")` → is\_internal |
| `string_starts_with(s, "prefix")` | True if string starts with prefix | `string_starts_with(sku, "PROD-")` → is\_product |
| `string_ends_with(s, "suffix")` | True if string ends with suffix | `string_ends_with(path, ".pdf")` → is\_pdf |
| `substring(s, start, length)` | Extract N characters from position | `substring(account_id, "1", "8")` → account\_prefix |
| `string_left(s, "N")` | First N characters | `string_left(zip_code, "5")` → zip5 |
| `string_right(s, "N")` | Last N characters | `string_right(order_id, "6")` → short\_order\_id |
***
## Date & Time
### Extract components
All functions take a timestamp column and return an integer.
| Function | What it returns | Example |
| ----------------- | ----------------------------- | --------------------------------------- |
| `year(ts)` | Year (e.g. 2026) | `year(created_at)` → signup\_year |
| `month(ts)` | Month 1–12 | `month(event_time)` → event\_month |
| `day(ts)` | Day of month 1–31 | `day(order_date)` → order\_day |
| `hour(ts)` | Hour 0–23 | `hour(login_at)` → login\_hour |
| `minute(ts)` | Minute 0–59 | `minute(event_time)` → event\_minute |
| `second(ts)` | Second 0–59 | `second(event_time)` → event\_second |
| `quarter(ts)` | Quarter 1–4 | `quarter(sale_date)` → fiscal\_quarter |
| `day_of_week(ts)` | ISO weekday 1 (Mon) – 7 (Sun) | `day_of_week(event_time)` → weekday |
| `day_of_year(ts)` | Day of year 1–366 | `day_of_year(event_time)` → julian\_day |
| `iso_week(ts)` | ISO week number 1–53 | `iso_week(sale_date)` → week\_number |
| `iso_year(ts)` | ISO calendar year | `iso_year(sale_date)` → iso\_year |
### Arithmetic & formatting
| Function | What it does | Example |
| ----------------------------- | --------------------------- | ---------------------------------------------------------------- |
| `date_add(ts, amount, unit)` | Add time to a timestamp | `date_add(created_at, "30", "day")` → expires\_at |
| `date_diff(start, end, unit)` | Time between two timestamps | `date_diff(created_at, resolved_at, "hour")` → resolution\_hours |
| `date_trunc(ts, unit)` | Truncate to unit boundary | `date_trunc(event_time, "month")` → event\_month\_start |
| `date_format(ts, "fmt")` | Format timestamp as string | `date_format(created_at, "%Y-%m-%d")` → date\_label |
| `to_timestamp(s, "fmt")` | Parse string into timestamp | `to_timestamp(date_str, "%d/%m/%Y")` → parsed\_date |
**Supported units for `date_add` / `date_diff`:** `microsecond`, `millisecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`
***
## Conditional & Null
### Branching
| Function | What it does | Example |
| ---------------------------------------------- | ---------------------------------------------------- | ------------------------------------------------------------ |
| `if_else(cond, then, else)` | Return `then` if condition is true, otherwise `else` | `if_else(is_trial, "trial", "paid")` → plan\_type |
| `case_when(cond1, val1, cond2, val2, default)` | Multi-branch — first matching condition wins | `case_when(score >= 90, "A", score >= 70, "B", "C")` → grade |
### Null handling
| Function | What it does | Example |
| ----------------------------- | ---------------------------------- | ------------------------------------------------ |
| `coalesce(a, b, ...)` | First non-null value from the list | `coalesce(phone, mobile, "unknown")` → contact |
| `fill_null(col, replacement)` | Replace nulls with a fallback | `fill_null(country, "US")` → country\_normalized |
### Null checks
These return a boolean column, useful for downstream filtering or flagging.
| Function | What it does | Example |
| ------------------ | -------------------------------------- | ----------------------------------------- |
| `is_null(col)` | True if the value is null | `is_null(deleted_at)` → is\_active |
| `is_not_null(col)` | True if the value is not null | `is_not_null(verified_at)` → is\_verified |
| `is_nan(col)` | True if the value is NaN (floats only) | `is_nan(score)` → score\_invalid |
***
## Boolean
Combine boolean columns or invert them. Inputs must be boolean type.
| Function | What it does | Example |
| --------------- | ------------------------- | --------------------------------------------------------- |
| `and(a, b)` | Both must be true | `and(is_active, is_verified)` → eligible |
| `or(a, b)` | At least one must be true | `or(is_vip, is_staff)` → has\_access |
| `not(a)` | Invert the value | `not(is_deleted)` → is\_live |
| `xor(a, b)` | Exactly one must be true | `xor(flag_a, flag_b)` → exclusive\_flag |
| `and_not(a, b)` | a is true and b is false | `and_not(is_subscribed, is_churned)` → active\_subscriber |
***
## Comparison
These produce a new boolean column. Useful for creating flag columns at the destination.
| Function | What it does | Example |
| --------------------- | ----------------- | ---------------------------------------------------- |
| `equal(a, b)` | `a == b` | `equal(status, "=active")` → is\_active |
| `not_equal(a, b)` | `a != b` | `not_equal(tier, "=free")` → is\_paid |
| `greater(a, b)` | `a > b` | `greater(balance, "=0")` → has\_balance |
| `greater_equal(a, b)` | `a >= b` | `greater_equal(score, "=50")` → passed |
| `less(a, b)` | `a < b` | `less(age, "=18")` → is\_minor |
| `less_equal(a, b)` | `a <= b` | `less_equal(usage, quota)` → within\_quota |
| `is_in(col, ...)` | Value is in a set | `is_in(status, "=active", "=trial")` → is\_onboarded |
***
## Aggregate
These collapse an entire column into a single value, then broadcast it across all rows. Best used when you need to include a summary alongside row-level data (e.g. add a `total_orders` column to every row in the orders table).
| Function | What it does | Example |
| ------------------------ | -------------------------------- | ------------------------------------------- |
| `sum(col)` | Total of all values | `sum(amount)` → grand\_total |
| `mean(col)` | Average | `mean(score)` → avg\_score |
| `count(col)` | Count of non-null values | `count(user_id)` → total\_users |
| `count_distinct(col)` | Count of unique values | `count_distinct(user_id)` → unique\_users |
| `min_agg(col)` | Minimum value | `min_agg(price)` → min\_price |
| `max_agg(col)` | Maximum value | `max_agg(price)` → max\_price |
| `min_element_wise(a, b)` | Row-level minimum of two columns | `min_element_wise(bid, ask)` → lower\_bound |
| `max_element_wise(a, b)` | Row-level maximum of two columns | `max_element_wise(bid, ask)` → upper\_bound |
### Statistical aggregates
| Function | What it does | Example |
| ---------------------- | ------------------------------------- | ------------------------------------------- |
| `stddev(col)` | Sample standard deviation (broadcast) | `stddev(response_ms)` → latency\_stddev |
| `variance(col)` | Sample variance (broadcast) | `variance(daily_sales)` → sales\_variance |
| `median(col)` | Median value (broadcast) | `median(session_length)` → median\_session |
| `percentile(col, "p")` | p-th percentile, 0.0–1.0 (broadcast) | `percentile(load_time, "0.95")` → p95\_load |
Aggregate functions run over the entire batch. For grouping and partitioned aggregations, use your destination's SQL or a transformation tool like dbt.
***
## Window
Window functions assign a positional value to each row based on ordering. Commonly used for ranking, deduplication, and running totals.
| Function | What it does | Example |
| ------------------ | -------------------------------------- | ------------------------------------------ |
| `row_number(col)` | Sequential row number ordered by `col` | `row_number(created_at)` → row\_num |
| `rank(col)` | Rank with gaps for ties | `rank(score)` → score\_rank |
| `dense_rank(col)` | Rank without gaps for ties | `dense_rank(score)` → dense\_score\_rank |
| `running_sum(col)` | Cumulative sum ordered by row position | `running_sum(amount)` → cumulative\_amount |
***
## Regex
| Function | What it does | Example |
| -------------------------------------------- | ----------------------- | --------------------------------------------------------------- |
| `regex_extract(s, "pattern")` | Return the first match | `regex_extract(url, "utm_source=([^&]+)")` → utm\_source |
| `regex_replace(s, "pattern", "replacement")` | Replace all matches | `regex_replace(phone, "[^0-9]", "")` → phone\_digits |
| `regex_match(s, "pattern")` | True if pattern matches | `regex_match(email, "^[^@]+@[^@]+\.[^@]+$")` → is\_valid\_email |
***
## JSON
| Function | What it does | Example |
| ----------------------------- | ----------------------------------------- | ----------------------------------------- |
| `json_extract(col, "$.path")` | Extract a string value from a JSON column | `json_extract(metadata, "$.plan")` → plan |
### Path syntax
* `$.key` — top-level key
* `$.address.city` — nested key
* `$.tags[0]` — first array element
```
Source column: metadata
Value: {"plan":"growth","seats":10,"address":{"city":"Austin"}}
json_extract(metadata, "$.plan") → "growth"
json_extract(metadata, "$.seats") → "10"
json_extract(metadata, "$.address.city") → "Austin"
```
***
## Hashing & Security
| Function | What it does | Example |
| ------------- | -------------------------------------- | ------------------------------- |
| `sha256(col)` | SHA-256 hex digest of the column value | `sha256(email)` → hashed\_email |
Use `sha256` to pseudonymize PII before it reaches the destination. The same source value always produces the same hash, so foreign key relationships are preserved.
```
email = "alice@example.com" → sha256 → "2c624232cdd221771294dfbb310acbc..."
```
SHA-256 is a one-way hash — you cannot reverse it to recover the original value. Make sure to keep the original in your source or in a secured vault if you need it later.
***
## Type casting
| Function | What it does | Example |
| ------------------- | ------------------------------------------ | --------------------------------------- |
| `cast(col, "type")` | Convert a column to a different Arrow type | `cast(user_id, "utf8")` → user\_id\_str |
**Common target types:** `int8`, `int16`, `int32`, `int64`, `float32`, `float64`, `utf8`, `bool`, `timestamp[us, UTC]`, `date32`
***
## Literal values
When an argument to a function should be a fixed value rather than a column name, prefix it with `=`:
```
string_left(sku, "=5") # 5 is a literal
date_add(created_at, "=30", "=day") # both 30 and "day" are literals
fill_null(country, "=US") # "US" is a literal
```
Without the `=` prefix, the engine treats the argument as a column name.