Skip to main content
Formula table calculations are in Beta (Early Access).The feature is enabled by default on supported warehouses, with no flag required, and we’re actively expanding the function catalog. We’d love your feedback while we iterate. See feature maturity levels for details.
Formula table calculations let you write table calculations in a spreadsheet-style syntax, the way you would in Google Sheets or Excel, instead of raw SQL. When you create a new table calculation on a supported warehouse, Formula is the default input mode. You can switch to the SQL editor any time.
Creating a formula table calculation

​
Why use formulas?

  • Faster to write for common calculations. No OVER (…) or CASE WHEN boilerplate to remember.
  • Familiar if you’ve used Google Sheets, Excel, or Airtable.
  • Portable across warehouses. The same formula compiles to the correct SQL for whichever warehouse your project is connected to.
If you need something the formula syntax doesn’t cover yet, the SQL editor is always one click away.

​
Supported warehouses

WarehouseFormula support
BigQuery✅
ClickHouse✅
Databricks✅
DuckDB✅
PostgreSQL✅
Redshift✅
Snowflake✅
Athena🚧 Not yet
Trino🚧 Not yet
On warehouses where formulas aren’t supported yet, the Formula toggle is hidden and the SQL editor works exactly as it always has.

​
Writing your first formula

Every formula starts with =. Reference a field by its column name (the same name you see in the results table header): 
=orders_total_order_amount * 1.2
The result appears as a new green column in your results table:
Formula table calculation in the results table
You can use:
  • Numbers: 42, 3.14, -1.5
  • Strings: "hello" or 'hello'
  • Booleans: TRUE, FALSE
  • Column references: any field present in your results table
  • Arithmetic operators: +, -, *, /, % (modulo)
  • Comparison operators: =, <>, >, <, >=, <=
  • Boolean operators: AND, OR, NOT

​
Function reference

​
Math

FunctionDescription
ABS(x)Absolute value
ROUND(x, [digits])Round to N decimal places
CEIL(x) / CEILING(x)Round up to nearest integer
FLOOR(x)Round down to nearest integer
MIN(x, [y])Minimum (scalar or aggregate)
MAX(x, [y])Maximum (scalar or aggregate)

​
Logical

FunctionDescription
IF(condition, then, [else])Conditional expression
AND, OR, NOTBoolean operators
=, <>, >, <, >=, <=Comparison operators

​
String

FunctionDescription
CONCAT(a, b, …)Concatenate strings
LEN(s) / LENGTH(s)String length
TRIM(s)Remove whitespace
LOWER(s)Convert to lowercase
UPPER(s)Convert to uppercase

​
Date

FunctionDescription
TODAY()Current date
NOW()Current timestamp
YEAR(d)Extract year
MONTH(d)Extract month
DAY(d)Extract day

​
Aggregation

FunctionDescription
SUM(x)Sum values
AVG(x) / AVERAGE(x)Average values
COUNT([x])Count rows (or non-null x)
SUMIF(condition, x)Sum where condition is true
AVERAGEIF(condition, x)Avg where condition is true
COUNTIF(condition)Count where condition true

​
Window

FunctionDescription
RUNNING_TOTAL(x)Running (cumulative) total
ROW_NUMBER()Sequential row number
RANK()Rank with gaps
DENSE_RANK()Rank without gaps
LAG(x, [offset], [default])Previous row’s value
LEAD(x, [offset], [default])Next row’s value
FIRST(x)First value in window
LAST(x)Last value in window
NTILE(n)Distribute rows into N buckets
MOVING_SUM(x, n)Sum of the last N rows
MOVING_AVG(x, n)Average of the last N rows

​
Null handling

FunctionDescription
COALESCE(a, b, …)First non-null argument
ISNULL(x)TRUE if x is null

​
Examples

Gross margin as a percentage 
=ROUND((orders_revenue - orders_cost) / orders_revenue * 100, 2)
Flag high-value orders 
=IF(orders_total_amount > 1000, "VIP", "Standard")
Running total of revenue 
=RUNNING_TOTAL(orders_revenue)
Period-over-period growth % 
=(orders_revenue - LAG(orders_revenue, 1, 0)) / LAG(orders_revenue, 1, 0) * 100
Bucket customers by spend 
=IF(customers_lifetime_value > 10000, "Platinum",
   IF(customers_lifetime_value > 5000, "Gold",
   IF(customers_lifetime_value > 1000, "Silver", "Bronze")))
Percent of total 
=orders_revenue / SUM(orders_revenue) * 100

​
FAQ

No. The input mode is chosen when you create the table calculation and can’t be changed afterwards, because formulas and SQL aren’t always losslessly interconvertible. If you need to move a SQL calc to a formula (or vice versa), delete the old one and create a new one in the mode you want.
The Formula toggle is hidden and the SQL editor is the only option. Existing SQL table calculations keep working on every warehouse. The beta is strictly additive.
Post in the Lightdash Community Slack or open a GitHub issue. See Contact Us for more options.