Transform Function Reference

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.

Getting Started

Guides

Core Concepts

Pipelines

Connectors

Platform

Support

Transform Function Reference

How to read this reference

Math

Arithmetic

Logarithms

Rounding

String

Case & whitespace

Measurement & manipulation

Search & extract

Date & Time

Extract components

Arithmetic & formatting

Conditional & Null

Branching

Null handling

Null checks

Boolean

Comparison

Aggregate

Statistical aggregates

Window

Regex

JSON

Path syntax

Hashing & Security

Type casting

Literal values

​How to read this reference

​Math

​Arithmetic

​Logarithms

​Rounding

​String

​Case & whitespace

​Measurement & manipulation

​Search & extract

​Date & Time

​Extract components

​Arithmetic & formatting

​Conditional & Null

​Branching

​Null handling

​Null checks

​Boolean

​Comparison

​Aggregate

​Statistical aggregates

​Window

​Regex

​JSON

​Path syntax

​Hashing & Security

​Type casting

​Literal values

How to read this reference

Math

Arithmetic

Logarithms

Rounding

String

Case & whitespace

Measurement & manipulation

Search & extract

Date & Time

Extract components

Arithmetic & formatting

Conditional & Null

Branching

Null handling

Null checks

Boolean

Comparison

Aggregate

Statistical aggregates

Window

Regex

JSON

Path syntax

Hashing & Security

Type casting

Literal values