Databricks SQL Functions: Types, Examples, and Usage

Formulas & functions · Updated November 22, 2026

Databricks SQL functions are built-in or user-defined operations you call from SQL to transform values, summarize rows, work with dates and arrays, or return data from a table-like expression. They follow the Spark SQL foundation, with available functions depending on the Databricks runtime or SQL warehouse and, in some cases, the catalog objects your workspace can access.

What SQL functions do in Databricks

A function takes input and produces a value or a set of rows. In a query, functions can appear in a SELECT list, a filter, a join condition, a grouping expression, or another expression that accepts the relevant data type. For example, lower(email) returns a lowercase version of a string, while sum(amount) calculates a total across rows in a group.

Databricks SQL is built on Apache Spark SQL, so many familiar SQL functions work in Databricks. The platform also adds or adapts functions over time. This means that a function found in a general SQL reference—or in documentation for a different Spark or Databricks version—may not be available in every workspace. Check the documentation for the runtime or SQL warehouse you use when compatibility matters.

It is useful to distinguish a function from a SQL statement. SELECT is a statement; coalesce is a function used inside one. Functions can also be nested, such as lower(trim(email)), where one function cleans whitespace before another changes letter case.

Main types of Databricks SQL functions

Functions are often grouped by what they return and how they process input. The main categories you are likely to meet are:

  • Scalar functions return a value for each input row. Examples include string functions such as upper and substring, numeric functions such as round, and date functions such as date_add.
  • Aggregate functions combine values from multiple rows into a result for a group. Common examples are count, sum, avg, min, and max. They are often used with GROUP BY.
  • Window functions calculate a value across a related set of rows while preserving individual rows in the output. Functions such as row_number, rank, and sum can be used with an OVER clause.
  • Higher-order functions operate on collections such as arrays or maps. For instance, transform applies an expression to each array element, and filter keeps elements that satisfy a condition.
  • Table-valued functions return rows rather than one value. explode, for example, can turn array elements into separate rows for further analysis.

Other useful groups cover conditional logic, type conversion, JSON, hashing, and date or timestamp handling. You do not have to memorize every function. Start with the kind of value you have and the shape of result you need: one value per row, one result per group, or multiple rows.

Practical examples

String and conditional functions can standardize messy fields before analysis. In the following example, trim removes leading and trailing whitespace, lower standardizes letter case, and coalesce supplies a fallback when a value is null:

SELECT lower(trim(email)) AS normalized_email, coalesce(country, 'Unknown') AS country_label FROM customers;

Aggregates answer questions about groups of records. This query produces one row per customer, assuming orders contains customer_id and total columns:

SELECT customer_id, count(*) AS order_count, sum(total) AS revenue FROM orders GROUP BY customer_id;

For a ranking that keeps each order visible, use a window function instead of grouping the rows away. Assuming orders also has order_date, this assigns the newest order rank 1 for each customer:

SELECT customer_id, order_date, row_number() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank FROM orders;

Date functions can make relative filters easier to read. For example, date_sub(current_date(), 30) calculates the date 30 days before the current date. A filter using it can select records from a recent period, but confirm whether the column is a date or timestamp and whether the boundary should be inclusive for your reporting definition.

For arrays, a higher-order function can transform elements without first expanding the array into rows. For example, transform(array(1, 2, 3), x -> x * 2) returns an array containing 2, 4, and 6. By contrast, explode(array(1, 2, 3)) produces separate rows, which is useful when each element needs to be filtered, joined, or counted independently.

How to choose the right function

Choose based on the desired result, not just on a familiar function name. If you need one output value for every record, look for a scalar function. If you need one value for a group, use an aggregate. If you need to compare or number rows without collapsing them, consider a window function. When your input is an array or map, check whether a higher-order function can work with it directly.

Before applying a function, check its input and output types. A date function may expect a date or timestamp, while a string function may require an explicit cast for a numeric value. Implicit conversions can behave differently than expected, especially with malformed text or mixed types. When invalid input is possible, a tolerant conversion such as try_cast may return null instead of raising a conversion error; confirm that this behavior is supported in your environment and handle those nulls deliberately.

Also consider null behavior. count(*) counts rows, whereas count(column_name) counts non-null values in that column. Many aggregate functions ignore null inputs, and coalesce returns the first non-null argument. Treating null as zero or as an empty string can change the meaning of a report, so choose a fallback only when it matches the business definition.

Finding and checking functions

Databricks provides SQL commands that help you inspect the functions available in your current context. You can list functions with:

SHOW FUNCTIONS;

You can inspect a particular function with:

DESCRIBE FUNCTION EXTENDED date_add;

The description can provide information about the function and its usage. The Databricks SQL function reference is another useful source: look up the exact name, argument types, return type, examples, and any version or environment notes. If you are working with a function from a shared catalog, use its qualified name where needed, such as catalog_name.schema_name.function_name.

If a query reports that a function cannot be resolved, check for spelling errors, the active catalog and schema, and whether the function is built in or user defined. If it reports an argument-type mismatch, compare the supplied arguments with the documented signature and cast values explicitly where appropriate. A query that works in one warehouse may also fail in another if the environments have different capabilities or access permissions.

Built-in functions and user-defined functions

Built-in functions are generally the simplest option: Databricks supplies them, and you can call them directly in SQL. Prefer a built-in when it clearly expresses the operation you need. Built-ins are familiar to other users, have documented behavior, and avoid maintaining a separate implementation for a common task.

If a reusable operation is not available as a built-in, you may be able to define a SQL user-defined function (UDF). A SQL UDF gives a named operation to a SQL expression so that it can be called from queries. In Unity Catalog, a function can be stored as a catalog object and made available to users who have the required privileges. The exact creation syntax, supported function body, and permissions depend on the function type and workspace configuration, so consult the current Databricks documentation before deploying one.

Some workloads also use UDFs written in languages such as Python. These can be useful when logic is difficult to express in SQL, but they introduce additional code and operational considerations. For straightforward transformations, native SQL functions are often easier to review and maintain. Keep custom logic small, document its assumptions, and test how it handles nulls, unexpected values, and changes to input types.

Performance and common pitfalls

Function choice can affect both query clarity and execution. A built-in expression is usually a good starting point because the SQL engine can plan it as part of the query. Avoid wrapping a simple built-in operation in custom code without a clear need. For large tables, inspect the full query plan and measure the workload rather than assuming that a shorter expression is automatically faster.

  • Watch for nulls: Comparisons involving null do not behave like ordinary true-or-false comparisons. In particular, check the logic of NOT IN when its values may contain nulls.
  • Use the intended time zone: Date and timestamp results can depend on the data type and session settings. Confirm the time-zone assumptions before using dates for daily reporting.
  • Be careful with grouping: An aggregate query changes the level of detail. Add every required grouping key, and use a window function if the original rows must remain in the result.
  • Check array expansion: Applying explode can multiply rows. If a record has several array elements, it can produce several output rows, affecting counts and joins.
  • Validate parsing: JSON and string-to-number functions need input in the expected format. Check malformed records rather than silently assuming every value can be parsed.

One common misconception is that every function with a familiar SQL name behaves identically across database products. Names and broad purposes may overlap, but argument order, supported types, null handling, and available versions can differ. When migrating a query, verify each function in the Databricks reference and test representative edge cases.

Frequently Asked Questions

Are Databricks SQL functions the same as Spark SQL functions?
Databricks SQL builds on Spark SQL, and many functions are shared. Databricks may add functions or support them differently across environments, so check the documentation for the specific warehouse or runtime you use.

How do I see which SQL functions are available?
Run SHOW FUNCTIONS; in your SQL context, then inspect a function with DESCRIBE FUNCTION EXTENDED function_name;. Also consult the Databricks function reference for current support and argument details.

What is the difference between an aggregate and a window function?
An aggregate such as sum normally returns a result for each group and reduces the number of rows. A window expression such as sum(amount) OVER (PARTITION BY customer_id) can calculate a group-level value while retaining each input row.

Can I create my own SQL function in Databricks?
Yes. Databricks supports user-defined functions, including SQL functions, subject to the relevant function type, workspace configuration, and permissions. For a shared function, check Unity Catalog requirements and use the current creation syntax in the official documentation.

Why does a function work in one Databricks environment but not another?
Different runtimes and SQL warehouses may support different functions or capabilities. A missing-function error can also come from an incorrect name, an unqualified user-defined function, the wrong active schema, or insufficient permissions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.