Skip to main content

05.5 SQL Functions & Advanced Expressions

SQL becomes powerful when you stop treating it as only SELECT ... FROM ... WHERE ... and start treating expressions as a typed computation language.

This topic builds that skill from everyday functions to architect-level expression design: scalar functions, conditional logic, strings, dates and timestamps, numeric precision, NULL semantics, arrays/maps/structs, higher-order functions, JSON and VARIANT, regular expressions, aggregates, window expressions, function discovery, and SQL UDFs.

Databricks SQL functions architecture

1. Learning objectives​

By the end of this topic you should be able to:

  • Choose a built-in function before reaching for a UDF.
  • Reason about input types, output types, NULL behavior, and implicit casting.
  • Build robust date/time logic with explicit formats and timezone awareness.
  • Manipulate arrays, maps, and structs without exploding data unnecessarily.
  • Use higher-order functions and lambda expressions for nested data.
  • Parse and generate JSON safely and design around semi-structured data.
  • Use regular expressions deliberately rather than as a substitute for simple string functions.
  • Understand aggregate and window expressions as part of the broader expression model.
  • Discover functions with SHOW FUNCTIONS and inspect them with DESCRIBE FUNCTION.
  • Design reusable SQL UDFs with clear contracts.
  • Explain why native expressions are usually preferable to external UDF execution.

2. The function mental model​

A SQL expression is evaluated for each relevant row, group, or window context. A function is therefore not just a convenience—it becomes part of the query plan.

Column / Literal
      │
      ▼
Typed Expression
      │
      ├── Scalar function ───────────────► one value per row
      ├── Conditional expression ────────► one value per row
      ├── Complex-type expression ───────► array/map/struct/variant
      ├── Aggregate function ────────────► one value per group
      └── Window expression ─────────────► one value per row + window
      │
      ▼
Logical plan → optimized plan → physical execution

Databricks documents a large built-in function surface covering strings, numeric functions, aggregates, windows, arrays, maps, dates/timestamps, casting, JSON, and other categories. Use the built-in catalog before inventing custom logic.

Official reference: Databricks built-in functions.

3. Function categories​

CategoryTypical functionsTypical use
Numericabs, round, ceil, floor, greatestmeasures and calculations
ConditionalCASE, when, coalesce, nullifbusiness rules
Stringtrim, lower, upper, substring, concatcleansing and standardization
Date/timeto_date, to_timestamp, date_add, date_trunccalendar and event logic
NULLcoalesce, nullif, nvl, isnullmissing-value semantics
Arrayarray, element_at, array_contains, transformnested attributes
Mapmap, map_keys, map_values, map_entrieskey/value attributes
Structnamed_struct, field accesshierarchical records
JSONfrom_json, to_json, parse_jsonsemi-structured payloads
Regexregexp_extract, regexp_replace, regexp_likepattern-based parsing
Aggregatesum, avg, count, maxgroup-level metrics
Windowrow_number, lag, lead, rankingrow-relative analytics
UDFSQL/Python UDFsreusable custom logic

4. Scalar expressions and type discipline​

Start with the simplest expression that satisfies the requirement.

SELECT
    net_value,
    tax_value,
    net_value + tax_value AS gross_value,
    round(net_value * 1.18, 2) AS gross_with_tax
FROM sales;

Why type discipline matters​

An expression has input types and a resulting type. Mixed numeric types, string-to-date conversions, decimals, and NULLs can change both correctness and performance.

SELECT
    CAST(amount AS DECIMAL(18,2)) AS amount_decimal,
    CAST(order_id AS STRING) AS order_id_text
FROM bronze_orders;

Architect rule: make important casts explicit at data-contract boundaries instead of relying on accidental coercion.

5. Conditional logic​

CASE​

SELECT
    CASE
        WHEN amount >= 100000 THEN 'HIGH'
        WHEN amount >= 10000  THEN 'MEDIUM'
        ELSE 'LOW'
    END AS value_band
FROM orders;

Use searched CASE when the business rule is predicate-driven.

COALESCE​

SELECT COALESCE(preferred_name, legal_name, 'UNKNOWN') AS display_name
FROM customer;

COALESCE is especially useful when integrating systems with different completeness rules.

NULLIF​

SELECT revenue / NULLIF(units, 0) AS revenue_per_unit
FROM sales;

This prevents a divide-by-zero condition from turning a metric into an uncontrolled failure.

Greatest / least​

SELECT
    greatest(actual_value, forecast_value, 0) AS max_value,
    least(actual_value, forecast_value) AS min_value
FROM planning;

6. String engineering​

String logic appears everywhere in enterprise ingestion: IDs, descriptions, SAP keys, source-system codes, and semi-structured payloads.

SELECT
    trim(upper(customer_id)) AS customer_id_normalized,
    lower(email) AS email_normalized,
    regexp_replace(phone, '[^0-9]', '') AS phone_digits
FROM bronze_customer;

Prefer simple functions such as trim, replace, lower, upper, substring, split, and concatenation when they solve the problem. Use regex when the rule genuinely requires pattern matching.

Common enterprise pattern​

SELECT
    lpad(cast(material_id AS STRING), 18, '0') AS material_id_18
FROM source_material;

Be careful with identifiers: formatting a business key is a data-contract decision, not merely presentation logic.

7. Date and timestamp engineering​

Temporal logic is one of the most common sources of subtle analytics defects.

Parse explicitly​

SELECT to_date(order_date_text, 'yyyy-MM-dd') AS order_date
FROM raw_orders;

Databricks uses datetime pattern strings for parsing and formatting dates/timestamps. See the datetime pattern reference.

Format​

SELECT date_format(order_ts, 'yyyy-MM-dd HH:mm:ss') AS order_ts_text
FROM events;

Calendar arithmetic​

SELECT
    date_add(order_date, 7) AS plus_7_days,
    add_months(order_date, 1) AS plus_1_month,
    date_trunc('month', order_date) AS month_start
FROM orders;

Extract components​

SELECT
    year(order_date) AS year_no,
    month(order_date) AS month_no,
    day(order_date) AS day_no
FROM orders;

Timezone principle​

Do not treat timestamps as plain strings. Establish what the timestamp represents, what timezone the source uses, and what timezone the analytical contract requires.

Functions such as extract operate on timestamp values and can be affected by the session timezone for timezone-aware timestamps. See the extract reference.

Architect rule: store a consistent temporal representation and localize only at the presentation boundary unless the business process explicitly requires local-time semantics.

8. Numeric and financial expressions​

Financial calculations should be deliberate about precision and scale.

SELECT
    CAST(net_amount AS DECIMAL(18,2)) AS net_amount,
    CAST(tax_rate AS DECIMAL(9,6)) AS tax_rate,
    round(net_amount * tax_rate, 2) AS tax_amount
FROM invoice;

Avoid accidental floating-point financial logic​

Prefer a suitable DECIMAL(p,s) contract for money and rates where exact decimal behavior is required.

Useful functions include round, ceil, floor, abs, greatest, least, nullifzero, and pmod.

9. NULL semantics​

NULL means unknown or missing, not zero and not an empty string.

SQL predicates use three-valued logic: TRUE, FALSE, and UNKNOWN.

SELECT *
FROM orders
WHERE customer_id IS NULL;

Do not write:

WHERE customer_id = NULL

Use:

WHERE customer_id IS NULL

NULL-safe comparisons​

When the business meaning requires treating two NULL values as equal, use the appropriate NULL-safe comparison rather than silently changing NULL into a fake business value.

Architect rule: decide whether NULL means missing, unknown, not applicable, or not yet received. That semantic decision belongs in the data contract.

10. Arrays, maps, and structs​

Modern lakehouse data is often nested rather than perfectly tabular.

Array​

SELECT array('SAP', 'BW', 'Databricks') AS technologies;

Access and transform arrays with functions such as element_at, array_contains, slice, sort_array, and higher-order functions.

Map​

SELECT map('country', 'IN', 'currency', 'INR') AS attributes;

Maps are useful for flexible attributes, but their schema is weaker than a well-defined relational column set.

Databricks documents that map keys must be non-NULL and duplicate keys can raise DUPLICATED_MAP_KEY. See the map function reference.

Struct​

SELECT named_struct(
    'id', 1001,
    'name', 'Yogesh',
    'country', 'IN'
) AS customer;

Structs are often the best choice when the nested schema is known and governed.

11. Higher-order functions — the advanced SQL skill​

Higher-order functions operate on arrays using lambda expressions. This lets SQL express transformations that would otherwise require exploding and regrouping data.

Databricks documents higher-order functions as primitives for manipulating arrays with lambda functions.

Official reference: Higher-order functions.

TRANSFORM​

SELECT
    transform(scores, score -> score * 1.1) AS adjusted_scores
FROM assessments;

FILTER​

SELECT
    filter(scores, score -> score >= 80) AS passing_scores
FROM assessments;

EXISTS​

SELECT
    exists(scores, score -> score < 40) AS has_failure
FROM assessments;

REDUCE / AGGREGATE​

SELECT
    aggregate(scores, 0, (acc, score) -> acc + score) AS total_score
FROM assessments;

Why this matters​

Consider a record with 20 nested line items. An explode can multiply rows and force later aggregation. A higher-order expression can sometimes keep the computation at the original row grain.

Architect rule: use nested expressions when the business result is still naturally at the parent row grain; explode only when you actually need child rows as independent relational records.

12. JSON and semi-structured data​

Enterprise APIs frequently deliver JSON payloads before the lakehouse has a fully normalized schema.

Parse JSON into a known structure​

SELECT
    from_json(
        payload,
        'order_id STRING, customer_id STRING, amount DECIMAL(18,2)'
    ) AS order_record
FROM raw_api_events;

Access nested fields​

SELECT
    order_record.order_id,
    order_record.customer_id,
    order_record.amount
FROM parsed_events;

Serialize back to JSON​

SELECT to_json(
    named_struct('order_id', order_id, 'amount', amount)
) AS payload
FROM orders;

to_json can serialize STRUCT values and, in supported Databricks runtimes, VARIANT values. See the to_json reference.

Architect pattern​

Raw JSON
   │
   ├── preserve original payload
   │
   ▼
Parse + validate
   │
   ├── accepted → typed Silver
   └── rejected → quarantine / DQ
              │
              ▼
        Gold business model

Do not repeatedly parse the same large JSON string in every downstream report. Parse once at the appropriate layer and expose governed columns.

13. Regular expressions​

Regex is powerful but can become expensive and difficult to maintain.

Useful functions include regexp_like, regexp_extract, regexp_extract_all, regexp_replace, regexp_count, and regexp_substr.

Official reference: Regular expressions.

Validate​

SELECT regexp_like(email, '^[^@]+@[^@]+\\.[^@]+$') AS valid_email
FROM customers;

Extract​

SELECT regexp_extract(material_text, 'MAT-([0-9]+)', 1) AS material_number
FROM source;

Replace​

SELECT regexp_replace(phone, '[^0-9]', '') AS normalized_phone
FROM customer;

Regex decision rule​

Can a simple function solve it?
        │
     yes ─────► use simple string function
        │
       no
        ▼
Does the pattern genuinely require regex?
        │
     yes ─────► use regex + test edge cases
        │
       no
        ▼
Reconsider the data model

14. Aggregate and window expressions​

Functions operate at different grains.

Row grain​

SELECT amount * tax_rate AS tax_amount
FROM invoice;

Group grain​

SELECT customer_id, sum(amount) AS revenue
FROM invoice
GROUP BY customer_id;

Window grain​

SELECT
    customer_id,
    order_date,
    amount,
    lag(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_amount
FROM orders;

Your most important question is therefore not just which function? but at what grain is this function evaluated?

That question prevents many enterprise SQL defects.

15. Function discovery​

Do not memorize hundreds of functions. Learn how to discover them.

SHOW FUNCTIONS;

Filter by pattern:

SHOW FUNCTIONS LIKE 'date_*';

Inspect a function:

DESCRIBE FUNCTION EXTENDED date_add;

SHOW FUNCTIONS can search system and user-defined functions and can be filtered with a pattern. See the SHOW FUNCTIONS reference.

Professional habit: search → inspect signature → test NULLs/types → inspect plan when relevant → standardize the expression.

16. SQL UDFs — when built-ins are not enough​

Use a UDF when the business logic is genuinely reusable and cannot be expressed cleanly with existing native functions.

Conceptually:

CREATE FUNCTION normalize_customer_status(status STRING)
RETURNS STRING
RETURN CASE
    WHEN upper(trim(status)) IN ('A', 'ACTIVE') THEN 'ACTIVE'
    WHEN upper(trim(status)) IN ('I', 'INACTIVE') THEN 'INACTIVE'
    ELSE 'UNKNOWN'
END;

Then:

SELECT normalize_customer_status(status)
FROM customer;

Databricks supports built-in functions as well as SQL and Python UDFs. See the Functions overview.

UDF design checklist​

  • Define input and output types explicitly.
  • Make NULL behavior intentional.
  • Give the function a stable semantic name.
  • Keep business logic small and testable.
  • Document assumptions.
  • Govern ownership and privileges.
  • Avoid hiding expensive joins or scans behind a function abstraction.
  • Prefer native built-in expressions when they provide the same behavior.

17. Native expression vs UDF​

RequirementPreferred approach
Trim / case conversionBuilt-in
Date parsingBuilt-in
JSON parsingBuilt-in
Array transformationHigher-order built-in
Simple business classificationCASE
Reusable small business ruleSQL UDF
Complex Python-only algorithmPython UDF only when justified
External service callSeparate integration pattern

Why native first?​

Native SQL expressions are visible to the optimizer and execution engine. A custom UDF can introduce additional execution complexity and may reduce opportunities for optimization.

Do not use a UDF simply because the logic is unfamiliar. First search the built-in function catalog.

18. Enterprise example — SAP-style procurement​

Suppose Bronze contains:

PO_NUMBER       STRING
VENDOR_ID       STRING
ORDER_DATE      STRING
NET_VALUE       STRING
CURRENCY        STRING
ITEM_ATTRIBUTES ARRAY<STRUCT<code:STRING,value:STRING>>

Build a governed Silver projection:

SELECT
    lpad(trim(po_number), 10, '0') AS po_number,
    upper(trim(vendor_id)) AS vendor_id,
    to_date(order_date, 'yyyy-MM-dd') AS order_date,
    CAST(net_value AS DECIMAL(18,2)) AS net_value,
    upper(currency) AS currency,
    transform(
        item_attributes,
        item -> named_struct(
            'code', upper(trim(item.code)),
            'value', trim(item.value)
        )
    ) AS item_attributes
FROM bronze_purchase_orders;

Notice what happened:

  1. Identifier normalization.
  2. Explicit date parsing.
  3. Explicit financial typing.
  4. Currency normalization.
  5. Nested-array transformation without flattening the parent row.

That is the difference between merely knowing functions and designing a reliable expression layer.

19. Performance engineering for expressions​

Function choice can affect performance even when two expressions produce the same result.

Prefer​

  • Native functions over external UDFs when equivalent.
  • Projection pruning so only required columns enter expensive expressions.
  • Filtering before expensive parsing where semantics allow it.
  • Parse-once patterns for JSON.
  • Parent-grain higher-order functions when exploding is unnecessary.
  • Explicit schemas for important semi-structured data.

Be careful with​

  • Regex over very large text columns.
  • Repeated JSON parsing.
  • Large collect_list/collect_set results.
  • Unbounded window expressions.
  • High-cardinality expressions followed by unnecessary shuffles.
  • Python UDFs for logic already available natively.

Performance is still a distributed-systems problem: a function executes inside a larger scan, projection, aggregation, join, or window plan.

20. Hands-on lab​

Lab A — Function discovery​

  1. Run SHOW FUNCTIONS.
  2. Search for date_*.
  3. Inspect date_add.
  4. Test date_add with NULL input.
  5. Record input and output data types.

Lab B — Nested data​

Create a table with an array of product prices and use:

  • transform to apply a discount.
  • filter to retain prices above a threshold.
  • aggregate to calculate a total.

Lab C — JSON​

Create a raw JSON payload containing an order header and two items. Parse it into a typed structure, transform the items, and serialize a selected structure back to JSON.

Lab D — Regex​

Create intentionally dirty invoice identifiers such as INV-2026-0001, invoice 2026/0002, and invalid values. Build a validation and extraction expression.

Lab E — Architect challenge​

Compare two designs:

Design A: explode JSON line items → transform → group back to the order.

Design B: parse once → use higher-order functions at order grain.

Explain which design you would choose, why, and under what downstream requirement Design A becomes necessary.

21. Troubleshooting checklist​

SymptomCheck first
Unexpected NULLinput NULLs, cast failures, function semantics
Wrong dateinput format, timezone, parsing pattern
Decimal mismatchprecision/scale and implicit casts
Regex misses valuespattern, escaping, test cases
Duplicate nested valuesarray/map semantics and explode behavior
Slow queryrepeated parsing, UDFs, large expressions, shuffle/window plan
UDF not foundcatalog/schema qualification and privileges
Ambiguous functionoverload/signature and argument types

22. Architect decision framework​

Before writing a complex expression, ask:

  1. What is the data type?
  2. What is the row/group/window grain?
  3. What should NULL mean?
  4. Is there a native function?
  5. Can the operation stay at the current grain?
  6. Will parsing be repeated downstream?
  7. Does the expression introduce a costly execution boundary?
  8. Is the business rule reusable enough for a UDF?
  9. Is the result part of a governed data contract?
  10. How will I test edge cases?

The architect mental model​

Business requirement
       │
       ▼
Data type + semantic grain
       │
       ▼
Native expression?
   ┌───┴────┐
  yes      no
   │        │
   ▼        ▼
Use it   Re-model / UDF
   │        │
   └───┬────┘
       ▼
Validate NULL + edge cases
       │
       ▼
Inspect plan / cost
       │
       ▼
Govern + document

23. Interview questions​

Foundation​

  1. What is the difference between a scalar, aggregate, and window function?
  2. Why is = NULL incorrect?
  3. When would you use COALESCE?
  4. Why should financial values use suitable decimal types?

Advanced​

  1. What are higher-order functions?
  2. When is transform preferable to explode?
  3. How would you parse nested JSON without repeatedly reparsing it?
  4. When should you use regex versus simple string functions?
  5. Why prefer native expressions over Python UDFs?
  6. How do timestamp and timezone semantics affect analytics?

Architect​

  1. You receive a 2 TB JSON event stream. Where would you parse and normalize it?
  2. A report contains a complex UDF and is slow. How would you investigate?
  3. A nested array has 500 elements per parent row. When would you preserve it versus explode it?
  4. How would you govern reusable SQL UDFs across multiple workspaces?
  5. How would you design a reusable expression layer for an enterprise data product?

24. Cheat sheet​

NeedStart with
Missing value fallbackcoalesce
Conditional rulecase
Safe divisionnullif
String cleanuptrim, lower, upper, replace
Pattern matchingregexp_like
Pattern extractionregexp_extract
Date parsingto_date
Timestamp parsingto_timestamp
Date formattingdate_format
Nested array mappingtransform
Nested array filteringfilter
Nested array reductionaggregate
JSON parsingfrom_json / parse_json
JSON generationto_json
Map inspectionmap_keys, map_values, map_entries
Function discoverySHOW FUNCTIONS
Function documentationDESCRIBE FUNCTION
Reusable custom logicSQL UDF

25. Official references​

26. Key takeaway​

Do not memorize functions. Master the expression model.

At enterprise scale, the important skill is deciding which function, at which grain, with which data type and NULL semantics, inside which execution plan.

Once you can reason that way, Databricks SQL becomes a composable data-processing language rather than a collection of syntax tricks.

Next: 05.6 SQL Window Functions & Advanced Analytics.