Skip to main content
Preview SparkSQL compatibility mode lets you run Spark SQL against Firebolt without rewriting it, and Databricks compatibility mode does the same for Databricks SQL. When a mode is on, Firebolt parses each statement with that system’s grammar and translates it to native Firebolt SQL, so Spark query syntax, functions, and type names work as written. Use it to point existing Spark or Databricks queries and tools at Firebolt, and to migrate a workload incrementally: run the SQL you already have, then adopt native Firebolt SQL where you want to.

​
What works

  • Query syntax: SELECT, FROM, joins including LEFT SEMI and LEFT ANTI, set operations including MINUS and BY NAME, QUALIFY, GROUP BY ALL, GROUPING SETS, ROLLUP, CUBE, CTEs, SELECT * EXCEPT, SORT BY, TABLE, VALUES, and the |> pipe operator.
  • Table-valued functions: RANGE, EXPLODE, POSEXPLODE, STACK, and IDENTIFIER in a FROM clause.
  • Data types: BOOLEAN, the integer types, FLOAT, DOUBLE, DECIMAL, STRING, BINARY, DATE, TIMESTAMP, TIMESTAMP_NTZ, ARRAY, and STRUCT.
  • Operators: arithmetic, bitwise, comparison including <=>, and logical operators, the :: cast, LIKE ANY and LIKE ALL, RLIKE, and zero-based [] subscripts.
  • Functions:
    • Aggregate: AVG, COLLECT_LIST, COUNT_IF, MAX_BY, MIN_BY, the REGR_ family, and more.
    • Array, collection, and struct: ARRAY, ARRAY_CONTAINS, ARRAY_DISTINCT, ARRAY_MAX, ARRAYS_OVERLAP, GET, and more.
    • Bitwise: SHIFTLEFT, SHIFTRIGHT, and more.
    • Conditional and predicate: COALESCE, IF, NULLIF, NVL, NVL2, EQUAL_NULL, RLIKE, and more.
    • Conversion: CAST and more.
    • Date and timestamp: ADD_MONTHS, DATE_ADD, MAKE_DATE, FROM_UTC_TIMESTAMP, TO_DATE, TO_TIMESTAMP, and more.
    • Hash: MD5 and more.
    • JSON and semi-structured: JSON_OBJECT_KEYS, TO_JSON, and more.
    • Math: EXP, GREATEST, LEAST, LOG2, PMOD, POWER, and more.
    • Miscellaneous: CURRENT_USER, UUID, and more.
    • String: ELT, INSTR, REGEXP_COUNT, REPEAT, SPLIT_PART, URL_ENCODE, and more.
    • Window: RANK, LAG, LEAD, NTH_VALUE, NTILE, and more.
  • Statements: CREATE TABLE, CREATE VIEW, CREATE SCHEMA, DROP, ALTER TABLE, INSERT, UPDATE, DELETE, MERGE, TRUNCATE TABLE, SHOW, DESCRIBE, and transactions.
  • Information schema: the Databricks-shaped information_schema views.
  • Firebolt’s own functions: call them with the firebolt. qualifier when Spark has no equivalent.
Support was checked against open-source Spark 4.1: every example in Spark’s function reference ran on both engines and the results were compared. Databricks has no open-source engine to compare against, so behavior specific to Databricks was checked by hand, and only where this reference describes it. Databricks-only functions, such as the ai_ and h3_ families and read_files, are not available in either mode.

​
Enable a mode

Set the dialect session setting to spark for SparkSQL mode or to databricks for Databricks mode:
Every statement in the session is then read as Spark SQL or Databricks SQL. You can also pass the setting as an HTTP query parameter (?dialect=spark) or in a WITH (dialect='spark') clause. Return to native Firebolt SQL with:
The default is firebolt.

​
Choosing a mode

Both modes translate the same way: the functions, data types, operators, and results described in this reference are identical. They differ only in grammar, where each follows its own system, so pick the mode that matches the SQL you run. Backslash handling also changes regular expressions written as string literals. In Databricks mode '\\d+' reaches the regular expression engine as \d+, as it does in Spark, and '\d+' reaches it as d+. In SparkSQL mode each string arrives as written: '\d+' matches digits, and '\\d+' matches a literal backslash followed by d.

​
Calling Firebolt functions

In either mode you get Spark’s functions, not Firebolt’s. A name Spark does not define is rejected, even if Firebolt has a function by that name:
To call a Firebolt function, qualify it with firebolt:
This works for scalar, aggregate, window, and table functions. A qualified call runs exactly as it would in Firebolt mode: Spark’s translation rules are skipped, so use Firebolt’s name for the function and its argument order. firebolt is a namespace, not a schema: it qualifies function calls only, not tables or views.