Databricks Built-In Functions: Types and Examples

Formulas & functions · Updated November 22, 2026

Databricks built-in functions are ready-made operations for querying and transforming data with Databricks SQL and Apache Spark SQL. They cover common tasks such as working with text, dates, numbers, arrays, JSON, and grouped or windowed data; you can call them in SQL expressions without first defining a custom function.

What Databricks means by built-in functions

A built-in function is an operation supplied by the Databricks or Spark SQL environment. It takes arguments, performs a defined task, and generally returns a value. For example, lower(email) converts text to lowercase, while round(amount, 2) rounds a number to two decimal places. These functions let you express data logic close to the data instead of implementing every transformation in application code.

Databricks SQL uses Spark SQL syntax, but the available functions and their details can depend on the Databricks Runtime version, SQL warehouse, and language interface. Databricks also adds platform-specific capabilities. Consequently, a function name found in a general Spark reference is not a guarantee that the same behavior is available in every Databricks environment. Check the Databricks SQL function reference and the documentation for the runtime or warehouse you are using.

“Built-in” describes where the operation comes from, not how it must be called. SQL users write functions in expressions such as SELECT abs(-7). In PySpark, a corresponding operation may be available through the pyspark.sql.functions module and used in a DataFrame expression. A Python function that processes ordinary local values is not automatically a Databricks SQL built-in function.

Common categories and examples

Function families make it easier to find the right operation. Names and exact argument rules should still be checked in the reference, especially for less familiar functions. Common examples include:

  • Numeric and mathematical: abs, round, ceil, floor, and sqrt operate on numeric values.
  • String: lower, upper, trim, concat, and regexp_replace help normalize, combine, or modify text.
  • Date and time: current_date, current_timestamp, datediff, and date_trunc support date calculations and time bucketing.
  • Conditional and null handling: coalesce, nullif, and if help choose results based on values or missing data.
  • Aggregate: count, sum, avg, min, and max summarize groups of rows.
  • Complex types and semi-structured data: array, size, explode, from_json, and to_json work with arrays and JSON representations.

Some names refer to related but distinct operations. An aggregate function such as sum reduces a group to a result, while a scalar function such as abs returns a result for each input row. A window function can calculate a value across related rows while retaining the individual rows in the output. Knowing which kind of result you need is often the quickest way to narrow the choices.

Using functions in Databricks SQL

Functions can appear in a query’s select list, filter condition, grouping expression, or other expression positions where the SQL syntax permits them. This example cleans names and computes a total by normalized name:

SELECT lower(trim(customer_name)) AS normalized_name,
       sum(amount) AS total_amount
FROM sales
WHERE amount IS NOT NULL
GROUP BY lower(trim(customer_name));

The expression used for grouping corresponds to the normalized value selected above. The WHERE condition removes rows with a missing amount before the aggregation. If you need to preserve those rows or report their count, do not filter them out casually; decide what a null amount means for the analysis.

Several functions can be nested, as in lower(trim(customer_name)). Read the expression from the innermost operation outward: first trim surrounding whitespace, then convert the remaining text to lowercase. This can be useful when standardizing values for comparison, though it does not make spelling variants or different names equivalent.

Nulls, conditions, and data types

Null handling deserves particular attention because SQL uses a special unknown value rather than treating null as an ordinary empty string or zero. For example, coalesce(phone, 'not provided') returns the first non-null argument. It can make output easier to read, but substituting a label changes the value’s type or meaning and may be unsuitable for later numeric calculations.

Comparisons with null do not work like ordinary equality comparisons. A condition such as phone = NULL does not test reliably for missing values; use phone IS NULL or phone IS NOT NULL. Similarly, functions may propagate nulls, ignore them, or handle them in a documented special way. Check the behavior when the result matters, particularly for aggregates and conditional expressions.

Types also affect results. A date string may need conversion before date arithmetic, and combining values of different types can cause implicit conversions or errors. Use an explicit cast when the intended type is clear, and validate input formats rather than assuming every text value can be parsed. Databricks SQL provides conversion functions, but their failure and malformed-input behavior can differ by function.

Arrays, JSON, and nested data

Databricks is often used with nested and semi-structured records, so functions for complex types can be as important as ordinary string and numeric operations. The from_json function parses a JSON string using a supplied schema; to_json converts a supported value into JSON text. Supplying an explicit schema makes the expected fields and types clearer than treating all content as unstructured text.

Arrays can be created or inspected with functions such as array and size. A generator such as explode can turn array elements into rows, which is useful when each element needs to be filtered, joined, or aggregated separately. For example:

SELECT order_id, explode(items) AS item
FROM orders;

This changes the number of output rows: an order with several array elements can produce several rows. That expansion can affect totals if you later aggregate order-level amounts, so preserve the original grain of the data or aggregate at the correct level. Also consider what should happen when an array is empty or null; generator behavior and available variants should be checked in the applicable function documentation.

Aggregates and window functions

Aggregate functions calculate summaries, usually with a GROUP BY. For example, count(*) counts rows, whereas count(column_name) counts non-null values in that column. This distinction is useful when measuring missing data. approx_count_distinct can estimate the number of distinct values when an approximation is acceptable; it should not be substituted for an exact count without considering the required accuracy.

Window functions answer a different kind of question. They calculate across a defined set of related rows while keeping each row in the result. For instance, row_number() can number records within each customer’s history:

SELECT customer_id, order_date, order_id,
       row_number() OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
       ) AS order_number
FROM orders;

The partition defines which rows belong together, and the ordering defines their sequence. Including a tie-breaker such as order_id makes the ordering more specific when dates match. Without a meaningful ordering, row numbers may not represent a stable business sequence. Related window functions include rank, lag, and lead, each with its own rules for ties or neighboring rows.

SQL functions and PySpark functions

Databricks notebooks and jobs may use SQL, Python, Scala, or other supported interfaces. SQL built-ins are written directly in SQL statements. In PySpark, many comparable operations are exposed as column expressions in pyspark.sql.functions, commonly imported under an alias such as F. For example, a PySpark transformation might use F.lower(F.trim('customer_name')) inside select.

That shared purpose does not mean every interface has identical names, arguments, types, or availability. A SQL expression can be used in a DataFrame workflow through supported expression facilities, but code written for a Python library may not be a built-in SQL expression. When translating between languages, consult the documentation for the specific interface and verify that the returned type and null behavior fit the query.

For repeated logic, decide whether a built-in expression is enough before creating a user-defined function (UDF). A UDF can express custom behavior, but it introduces code to maintain and may limit optimization or portability compared with built-in expressions. If the same transformation can be represented with existing functions, that is often simpler for others to understand and for the query engine to optimize.

How to find and choose the right function

Start with the intended input and output: a string cleanup, a date difference, a summary per group, or a row-by-row calculation across a partition. Then search the Databricks SQL function reference by category or task. Read the function’s signature, argument types, return type, examples, and notes about nulls or version availability. A familiar name is not enough if the argument order differs from another SQL dialect.

  • Confirm that the function is available in the SQL warehouse or Databricks Runtime you will run.
  • Check whether it is scalar, aggregate, windowed, or a generator that can change row counts.
  • Test representative inputs, including nulls, empty values, boundaries, and unexpected formats.
  • Prefer clear expressions and explicit types over clever nesting that obscures the intended result.
  • Compare exact and approximate alternatives when accuracy, speed, or output size matters.

For exploration, Databricks SQL supports function-inspection statements such as SHOW FUNCTIONS and DESCRIBE FUNCTION. Their precise options and the functions visible in a given context can vary, so use the SQL reference for valid syntax. These commands can help identify a function, but the detailed documentation remains the best source for its semantics.

Common mistakes and practical considerations

A frequent mistake is assuming that a function behaves exactly as it does in another database. SQL dialects differ in date arithmetic, string indexing, regular-expression syntax, implicit casting, and error handling. Ported queries should be checked function by function rather than treated as interchangeable just because the names look familiar.

Another mistake is overlooking row multiplication. A generator that expands an array changes the dataset’s grain, and a join performed after that expansion can multiply rows again. Before adding a sum or count, identify what one row represents at each stage. If the desired metric is per order, for example, expanding line items and then summing an order-level amount can count that amount once per item.

Performance depends on the whole query, the amount and layout of data, and the execution environment—not just on whether a function is built in. Prefer built-in expressions when they represent the logic clearly, but avoid applying expensive transformations unnecessarily to large columns. For repeated queries, inspect the query plan and measure the workload in its real context. Function choice alone cannot guarantee a faster job.

Frequently Asked Questions

Are Databricks built-in functions the same as Spark SQL functions?

Databricks SQL is based on Spark SQL and supports many Spark SQL built-ins, but availability and behavior can depend on the Databricks product and version. Check the Databricks documentation for the environment where the query will run.

How can I see which functions are available?

Use the Databricks SQL function reference, and where appropriate use SQL inspection commands such as SHOW FUNCTIONS or DESCRIBE FUNCTION. Verify details against the documentation for your warehouse or runtime.

Can I call SQL built-in functions from PySpark?

Many equivalent operations are available as PySpark column expressions through pyspark.sql.functions. The interfaces are related, but names, signatures, and behavior are not guaranteed to match exactly.

What is the difference between an aggregate and a window function?

An aggregate usually reduces rows into grouped results. A window function calculates across related rows while retaining the individual rows, which is useful for ranking, running calculations, or comparing neighboring records.

Should I write a UDF instead of using a built-in function?

Use a built-in expression when it clearly covers the task. Consider a UDF only when the required logic cannot be expressed suitably with available functions, and assess its maintenance, compatibility, and execution implications.

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.