Frequently Asked Questions

Firebolt's FireCache spans three layers: a full result cache that serves repeated queries directly, a join hash table cache that reuses hash tables computed during previous requests, and a pagination input cache. Together they let Firebolt reuse intermediate query artifacts when serving new requests, reducing query processing times significantly while maintaining real-time, fully transactional results. FireCache can use up to 20% of engine RAM and includes built-in automatic cache eviction for efficient memory utilization. You can inspect and manage it with SHOW CACHE, CLEAR CACHE, and the information_schema.engine_caches view.

Firebolt is engineered to handle hundreds of analytical queries per second; without compromising speed. It offers unparalleled cost efficiency with industry-leading price-to-performance ratios and scales seamlessly to handle terabytes of data with minimal performance impact.

The primary index determines how data is sorted, which in turn determines how effectively Firebolt can prune data at query time. Follow these guidelines when selecting one:

Frequently Filtered Columns: Include columns often used in WHERE clauses or joins, ordering the key from most- to least-frequently filtered.

Range Queries: Include columns used in range filters, like dates, to improve performance in range-based queries.

Cardinality Ordering: Place lower-cardinality columns first so they form long constant runs — a key column only prunes effectively while every column before it is constant within a granule. (Note that data distribution across nodes is governed by the FACT vs. DIMENSION table type, not by the primary index.)

For more detailed information, check out Firebolt’s comprehensive guide on primary indexes.

These steps ensure efficient data pruning and faster query execution.

Firebolt's aggregating index pre-calculates and stores partial aggregation state for improved query performance, so a single index can serve many query shapes. The index is stored as an ordinary Firebolt table, partitioned and sorted on its grouping keys. Firebolt selects the best aggregating indexes to optimize queries at runtime, avoiding full table scans. These indexes are automatically updated with new or modified data to remain consistent with the underlying table data. In multi-node engines, Firebolt shards aggregating indexes across nodes, similar to the sharding of the underlying tables.

Warming up an aggregating index preloads the data into the cache, improving query performance. Use the CHECKSUM function on a query matching the index definition to warm up the index, leading to faster execution when it is utilized.

Solution:

Use the CHECKSUM function to preload specific data into the cache. Focus on frequently accessed columns or data ranges to optimize performance and minimize cache usage.

Example:

-- Warm-up the entire table SELECT CHECKSUM(*) FROM playstats;
-- Warm-up specific columnsSELECT CHECKSUM(GameID, PlayerID, CurrentScore) FROM playstats;
-- Warm-up specific data rangeSELECT CHECKSUM(*) FROM playstats WHERE CurrentLevel BETWEEN 1 AND 5;

Warming up tables using CHECKSUM ensures data is stored in the cache, improving performance for large tables or frequently queried datasets. Use filters or column selection to target relevant data efficiently.

For more options — partition-specific warm-up, the Multi-Cluster Engine Warmup setting, and submitting long warm-ups asynchronously — see the Warming Up Engines guide.

Warming up a table can improve query performance by preloading data into the cache. Running warm-up queries (typically SELECT CHECKSUM(...) over the columns you care about) after an engine starts ensures faster execution of subsequent queries. The Warming Up Engines guide covers partition-specific warm-up, the Multi-Cluster Engine Warmup setting, and submitting long warm-ups asynchronously.

Querying cold data, or data not yet cached in Firebolt's local SSD storage, may result in slightly slower performance compared to querying hot (cached) data. However, Firebolt's efficient caching mechanisms ensure that even cold data is accessed quickly, minimizing the performance impact.

The Shuffle operation is the key ingredient to executing queries at scale in distributed systems like Firebolt. Firebolt leverages close to all available network bandwidth and streams intermediate results from one execution state to the next whenever possible. By overlapping the execution of different stages, Firebolt reduces the overall query latency

Firebolt’s engine uses vectorized execution, which processes batches of thousands of rows at a time, leveraging modern CPUs for maximum efficiency. Combined with multi-threading, this approach allows queries to scale across all CPU cores, optimizing performance.*

‍<sub>Boncz, Peter A., Marcin Zukowski, and Niels Nes. "MonetDB/X100: Hyper-Pipelining Query Execution." CIDR. Vol. 5. 2005.
* Nes, Stratos Idreos Fabian Groffen Niels, and Stefan Manegold Sjoerd Mullender Martin Kersten. "MonetDB: Two decades of research in column-oriented database architectures." Data Engineering 40 (2012).</sub>

Data pruning in Firebolt minimizes the amount of data scanned during queries, allowing for tens-of-milliseconds response times by reducing I/O usage. Pruning happens in multiple stages: tablet pruning and sparse (primary) index range pruning skip whole chunks of data before the scan starts, data skipping indexes and inverted/full-text bitmaps eliminate additional granules, and selection vectors with late materialization ensure only the rows that survive filtering are ever fully read.

Firebolt uses advanced query processing techniques such as granular range-level data pruning with sparse indexes, incrementally updated aggregating indexes, vectorized multi-threaded execution, and tiered caching, including sub-plan result caching.These techniques both minimize data being scanned and reduce CPU time by reusing precomputed, enabling query processing times in tens of milliseconds latency on hundreds of TBs of data.

Firebolt scales to manage hundreds of terabytes of data without performance bottlenecks. Its distributed architecture allows it to leverage all available network bandwidth and execute queries at scale with efficient cross-node data transfer using streaming data shuffle.

Firebolt engines can scale up and out to handle high-concurrency workloads. Engines scale for concurrency by adding clusters automatically: you set bounds with MIN_CLUSTERS and MAX_CLUSTERS (both default to 1), and when demand exceeds capacity Firebolt adds a cluster; when load drops it removes one, gracefully draining in-flight queries first. Newly added clusters start cold, so Firebolt initially routes less traffic to them while the cache warms. See Understanding Autoscaling for details.

Firebolt offers observability views through information_schema, allowing you to access real-time engine metrics. These insights help you size your engines for optimal performance and cost efficiency. Read more here- https://docs.firebolt.io/reference-sql/information-schema

Firebolt has been benchmarked against several major data warehouses, including Snowflake, BigQuery, and Redshift. These benchmarks highlight Firebolt's superior performance in low-latency, high-concurrency queries, especially for fast aggregations and real-time analytics. See further details in our benchmark Github repo and our benchmark articles about handling concurency,  high-volume ingestion and DML operations.

Firebolt's optimizer draws on automated column statistics and history-based statistics for cardinality estimation, and its runtime applies join pruning, late materialization, and spilling to local SSD for large aggregations and joins. You can steer plans with query hints or the user-guided optimizer mode, and tables and columns can be compressed with LZ4 or ZSTD. Together with sparse and aggregating indexes and tiered caching, these features keep latencies low without manual tuning.

Yes. Firebolt is a high-performance, open source analytical database — open engine, open storage, no lock-in. It runs as a single binary on your laptop and scales to hundreds of nodes and petabytes of data on object storage in your own cloud. Firebolt OSS is currently in preview ahead of its GA launch. See What is Firebolt? in the documentation.

Yes. Firebolt offers four deployment models: a single binary with zero dependencies that runs at laptop scale, a Helm chart, a Kubernetes Operator for self-managed production deployments, and the fully managed service. Self-managed Firebolt is GA on AWS and GCP and in Preview on Azure. See the self-managed documentation.

Firebolt Core is the free-forever self-hosted edition of Firebolt, with no usage limits, shipped as a single Docker image from GitHub. It runs the same engine as the managed service — the same SQL and the same behavior everywhere — so you can develop locally and move to a cluster or to the managed service without changes.

Yes. Self-managed Firebolt deploys into your own environment via the Helm chart, the Kubernetes Operator, or standalone binaries, and managed-table storage can target S3-compatible object stores such as MinIO — so you can run it on your own infrastructure, in any cloud, or on your laptop.

Firebolt ships several generally available AI features. You can call large language models directly from SQL with AI_QUERY for a simple text prompt or AWS_BEDROCK_AI_QUERY for full control over the Bedrock model ID and JSON request body, and generate embeddings with AI_EMBED_TEXT. Firebolt also provides native vector search: create an HNSW index on an embedding column and query it with the VECTOR_SEARCH table function for sub-second top-K similarity search, alongside a full set of vector distance functions. Beyond SQL, the Firebolt MCP Server connects LLM clients such as Claude, Cursor, and GitHub Copilot Chat to your Firebolt data, and the Firebolt Agent (currently in preview) provides in-product SQL generation, error fixing, and optimization help. See Getting started with AI.

Create a LOCATION object pointing at Amazon Bedrock, then call an AI function referencing it — for example:

CREATE LOCATION bedrock_role WITH SOURCE = AMAZON_BEDROCK CREDENTIALS = (AWS_ROLE_ARN = '...');
SELECT AI_QUERY(...);

Credentials can be an access key and secret, temporary credentials with a session token, an IAM role ARN, or an IAM role with an external ID. Note that LLM invocations count against your account's daily token budget, which defaults to zero — an admin must set LLM_TOKEN_BUDGET with ALTER ACCOUNT before AI functions will run. Usage is visible in account_db.information_schema.quotas.

Yes, and it is generally available. Create a vector search index with CREATE INDEX <name> ON <table> USING HNSW (<column> <distance_metric>) WITH (dimension = <n>), then query it with the VECTOR_SEARCH table function. Firebolt builds one HNSW index file per tablet and merges results across tablets at query time, using a semi-join optimization so it retrieves only matching rows rather than scanning the table. The index maintains full ACID consistency with the base table, so there is no separate vector store to keep in sync. See the vector search index overview.

Yes. The Firebolt MCP Server is a lightweight service implementing the Model Context Protocol, letting LLM clients such as Claude, Cursor, and GitHub Copilot Chat query your databases with context-aware prompts and access Firebolt documentation, metadata, and live data. It ships as a Go binary or Docker container and authenticates with a service account client ID and secret. Firebolt also has documented integrations with LangChain via the SQLAlchemy connector, and with the AI analytics tools Dot and TextQL.

The Firebolt Agent is an AI assistant built into the Firebolt Workspace query editor, currently in preview and disabled by default until an organization admin enables it. It answers questions about Firebolt features and SQL, generates SQL from natural-language descriptions using schema awareness, fixes failing queries through a "Fix with AI" button that highlights the changes, and suggests performance optimizations. Firebolt uses enterprise-grade LLMs, encrypts data in transit, and does not use customer data processed by this feature to train the foundation models.

In Firebolt, an “engine” refers to a virtual compute resource that provides the processing power to execute queries, load data, and perform various SQL driven tasks. Unlike traditional cloud data warehouses, Firebolt engines can be resized, paused, and resumed in a much more granular, and cost effective way to optimize performance and cost.

An engine has three key dimensions:

Type - This refers to the type of nodes used in an engine.

Cluster - A collection of nodes of the same type.

Nodes - The number of nodes in each cluster.

An engine comprises one or more clusters. Every cluster in the engine has the same type and the same number of nodes.

There are four node types available in Firebolt: Small, Medium, Large, and X-Large. Each node type provides a certain amount of CPU, RAM, and SSD. These resources scale linearly with the node type. For example, an "M" type node provides twice as much CPU, RAM, and SSD as a "S" type node.

Nodes also come in two families: storage-optimized (the default) and compute-optimized, which differ substantially in RAM and local disk. Roughly 25% of local disk is reserved for system operations and caches, and compute-optimized nodes may see longer engine start times.

For more information, check out the Engine Fundamentals article in our documentation.

You can use anywhere from 1-128 nodes per cluster in a given engine.

For more information, check out our documentation.

Engines scale concurrency by adding clusters automatically between the MIN_CLUSTERS and MAX_CLUSTERS bounds you set (both default to 1). The maximum number of clusters depends on the engine's node type and your account configuration.

For more information, check out our documentation, or contact support if you need more concurrent clusters.

No. Engines and databases are fully decoupled in Firebolt. A given engine can be used with multiple databases, and conversely, multiple engines can be used with a given database. On Firebolt, all engines can write to the same database. No need to segregate engines as read-write and read-only.

For more information about how Engine access can be limited to certain Databases, check out our Engine Permissions Documentation.

No. While there is no theoretical limit on the number of databases you can use with a given engine, note that the configuration of your engine will determine the performance of your applications. Based on the performance demands of your applications and the needs of your business, you may want to create the appropriate number of engines.

For more information, check out our Engine Permissions Documentation.

Firebolt maintains warmpools of pre-provisioned resources so engines typically start quickly, but start-up time is not guaranteed, as it can be affected by resource availability on AWS. Warmpools currently cover Small and Medium storage-optimized engines on Enterprise accounts; other configurations may take longer to provision.

For more information, check out our Engine Warmpools documentation.

In managed Firebolt, engine lifecycle is part of the SQL/UI control plane. To create an engine, you can use the “CREATE ENGINE” command, specifying a name for the engine, the type of the nodes, the number of nodes in each cluster, and the autoscaling bounds via MIN_CLUSTERS and MAX_CLUSTERS. After the engine is successfully created, users will get an endpoint that they can use to submit their queries. For example, you can create an engine named MyEngine that starts with one cluster and can scale to two, each with two nodes of type “M”, as below:

CREATE ENGINE IF NOT EXISTS MyEngine WITH TYPE = "M" NODES = 2 MIN_CLUSTERS = 1 MAX_CLUSTERS = 2;

For more details, see the documentation.

In self-managed Firebolt, that control plane is Kubernetes: you create a FireboltEngine (YAML or kubectl firebolt), and the Firebolt Operator materializes pods and registers the engine with the instance gateway. SQL is still how you query once an engine exists.

In Firebolt, you can scale an engine across multiple dimensions. All scaling operations in managed and self-managed Firebolt are dynamic, meaning you do not need to stop your engines to scale them.

Scale Up/Down: You can vertically scale an engine by using a different node type that best fits the needs of your workload.

Scaling Out/In: You can horizontally scale an engine by modifying the number of nodes per cluster in the engine. Horizontal scaling can be used when your workload can benefit by distributing your queries across multiple nodes.

Concurrency Scaling: Managed Firebolt offers the capability to automatically add and remove clusters to your Engine. Firebolt adds or removes clusters in an engine automatically within the MIN_CLUSTERS and MAX_CLUSTERS bounds you set, so your workload can absorb a sudden spike in the number of users or number of queries. Note that you can scale along more than one dimension simultaneously. For example, the command below changes both the node type to “L” and the maximum number of clusters to two.

ALTER ENGINE MyEngine SET TYPE = "L" MAX_CLUSTERS = 2;

All Scaling operations can be performed via SQL using the ALTER ENGINE statement or via UI. For more information on how to perform scaling operations in Firebolt, see the Guides section in documentation.

No. Scaling operations in Firebolt are dynamic and do not require stopping the engine, so your applications will not experience downtime.

For more information, check out our Engine Fundamentals Documentation.

Your queries will continue to run uninterrupted during a scaling operation. When you perform horizontal or vertical scaling operations on your engine, Firebolt adds additional compute resources per your new configuration. While new queries will be directed to the new resources, the old compute resources will finish executing any queries currently running, after which they will be removed from the engine.

For more information, check out our Engine Consumption Documentation.

The system engine lets you run metadata queries — information_schema views and administrative commands — without starting a user engine. See the System Engine documentation.

Yes. Engines have release channels: set RELEASE = (CHANNEL = DEFAULT | PREVIEW) on CREATE ENGINE or ALTER ENGINE to choose whether an engine tracks the default release train or receives preview builds early. Engine-level RBAC additionally governs who can operate and modify each engine. See release settings.

Firebolt is ACID compliant and treats every operation as a transaction. For example, data from a COPY FROM operation is visible only after the entire operation is successful, ensuring data integrity. This eliminates partial updates ensuring data integrity at all times.

Firebolt offers multiple data import options: the COPY FROM SQL command for importing data from S3 buckets with built-in schema inference and automatic table creation; the 'Load data' wizard in the WebUI to explore, set options, infer schema, and load data into Firebolt tables; and a full set of table-valued functions for reading data in place — READ_CSV, READ_PARQUET, READ_JSON, READ_AVRO, READ_TEXT, READ_FILES (format inferred automatically), READ_ICEBERG, READ_DUCKLAKE, and READ_STREAM — plus LIST_OBJECTS and PARQUET_METADATA for inspecting source files. You can also send local files inline with a query using the upload:// scheme (up to 1 GB per request).

COPY FROM supports TYPE = AUTO | CSV | TSV | PARQUET, where AUTO (the default) infers the format automatically. For AVRO, JSON, or ORC data, use the READ_AVRO, READ_JSON, or READ_FILES table-valued functions with INSERT INTO ... SELECT. We also recommend the LOCATION-object form of COPY FROM, which keeps credentials in a managed, securable object rather than embedded in SQL.

Yes, Firebolt’s COPY FROM command can automatically create the destination table using AUTO_CREATE = TRUE, which maps columns and creates the table when it doesn't exist.

Use the CREATE EXTERNAL TABLE command to reference data stored outside Firebolt, like in an S3 bucket, while specifying the file format and schema. Note that table-valued functions (READ_PARQUET, READ_CSV, and friends) and COPY FROM are now the recommended way to work with external data — querying external tables directly is significantly slower — and LOCATION objects should hold credentials rather than embedding them in SQL.

Firebolt allows filtering on file-level information such as name, modified time, and size using metadata fields like $source_file_timestamp, $source_file_name, and $source_file_size.

Yes. Define a stream with CREATE STREAM over a Kafka topic and ingest it into a managed table with the READ_STREAM table-valued function, which advances the offset inside the transaction for exactly-once delivery. For operational databases, Firebolt also offers native change data capture from Postgres and MongoDB via CREATE STREAM plus CREATE CDC TABLE, keeping a continuously updated mirror with freshness measured in seconds (currently in private preview — contact support@firebolt.io to request early access). Ingest health is visible in information_schema.cdc_ingests and information_schema.streams. Micro-batching to S3 remains available, but it is no longer the only option.

Start with a small node type (CREATE ENGINE ingest_engine TYPE=S NODES=1) and monitor CPU and RAM utilization via information_schema.engine_metrics_history. Scale out the engine (e.g., ALTER ingest_engine SET NODES=4) as needed to increase throughput. As a general rule of thumb, most ingestion workloads benefit from paralellism, specifically when importing multiple files. Adding to that, Firebolt will be even more efficient when files are roughly equivalent in size.

Firebolt boosts data ingestion performance through parallel processing, multi-node scaling as the engine grows, and pipelined execution for efficient resource use. Using COPY FROM enables linear scaling with the number of nodes, accelerating ingestion speed with larger engines—ideal for latency-sensitive ELT scenarios.

Firebolt uses transactional semantics and ACID guarantees. Ingestion operations are fully isolated from ongoing reads or queries, ensuring consistency. There are no partial inserts or copies to clean up.

Optimize file sizes (500MB–1GB), use efficient formats (Parquet), and relocate files after ingestion to avoid reprocessing.

Data transformations can be applied directly within INSERT INTO SELECT statements during ingestion. Standard SQL functions can be used to manipulate data types, perform calculations, and format strings.

Yes. COPY TO exports query results or tables to Amazon S3, and CREATE ICEBERG TABLE AS SELECT writes results out as Apache Iceberg tables readable by any Iceberg-compatible engine. Firebolt also publishes a data portability and switching specification for EU Data Act compliance, documenting how to move your data out.

Everything in Firebolt is done through SQL. Firebolt speaks a subset of PostgreSQL-compatible SQL and supports running queries directly on structured and semi-structured data without compromising speed. Its extensions are built for modern data applications: AI and vector functions with HNSW vector search, a GEOGRAPHY type with fifteen spatial (ST_*) functions, SQL pipe syntax (|>), explicit BEGIN/COMMIT/ROLLBACK transactions, user-defined schemas, cross-database queries with three-part identifiers, native JSON and STRUCT types, parametrized queries, and array lambda functions. Python UDFs are available in private preview.

Yes, Firebolt is designed for ease of use, leveraging SQL simplicity and PostgreSQL compliance. It allows data professionals to manage, process, and query data effortlessly using familiar SQL commands.

When deciding between a fact or dimension table in Firebolt, it's important to consider how the data will be used and queried, as this choice impacts performance and how data is handled in multi-node engines.

Fact tables are typically large and contain measurable events, like sales or sensor readings. They usually hold foreign keys to dimension tables and measures that are aggregated (e.g., sums or averages). Fact tables benefit from aggregate indexes, which optimize heavy aggregations.

Dimension tables describe the entities in fact tables, such as product details or customer information. Dimension tables are usually smaller, updated more frequently, and replicated across nodes for faster lookups.

Firebolt supports six index types — primary, aggregating, data skipping, inverted, full-text search, and vector search. Lookup joins are accelerated automatically by join pruning, which pushes join-key values collected from the build side into the probe-side scan at runtime, so there is no separate join index to create.

In general, choose a fact table when you need to aggregate large volumes of data, and a dimension table for smaller, descriptive datasets primarily used for lookups. For multi-node engines, keep in mind that fact tables are sharded, while dimension tables are replicated.

In Firebolt's UI, numeric values are automatically displayed with commas for readability (e.g., 123,456,789). However, this may be undesirable for fields like IDs or other values where commas aren’t needed.

Solution:
To remove commas from numbers in the UI, CAST the numeric field to TEXT using ::TEXT. This ensures that the number is displayed as a plain text string, without commas.

Example:

SELECT 
  playerid AS playerid_default,
  playerid::text AS playerid_text,
  nickname,
  email
FROM players
LIMIT 10;

In this example, playerid_default will display with commas, while playerid_text will display the number without commas.

This method only affects how numbers are displayed in the Firebolt UI and does not alter the underlying data or its formatting in external tools.

Use PARTITION BY when you need to split the table into distinct data segments for better data management or to prune large amounts of data quickly. Partitioning allows for efficient data removal (e.g., ALTER TABLE...DROP PARTITION).

Use the Primary Index when you want to organize the order of data for optimal query performance. The primary index helps Firebolt efficiently prune data during queries based on filter conditions.

Example:

If you often query by playerid but also need to manage data by tournamentid, you could use playerid in the primary index and tournamentid in PARTITION BY. This would allow you to both optimize query performance and manage large data segments.

CREATE TABLE playstats_partition (
  playerid integer,
  tournamentid integer,
  stattime timestampntz
) PRIMARY INDEX playerid
PARTITION BY tournamentid;

To implement LEFT() and RIGHT() string functions in Firebolt, you can use the SUBSTR() function, as Firebolt does not natively support these functions.

LEFT() Alternative
To replicate the LEFT() function, use SUBSTR() to extract characters from the left side of a string. For example:

SELECT SUBSTR(nickname, 1, 6) FROM players WHERE nickname = 'murrayrebecca';
-- This returns "murray"
This extracts the first 6 characters from the string.

RIGHT() Alternative
For the RIGHT() function, combine SUBSTR() with LENGTH() to extract characters from the right side of the string. For example:

SELECT SUBSTR(nickname, LENGTH(nickname) - 6) FROM players WHERE nickname = 'murrayrebecca';
-- This returns "rebecca"

In general, to take the last n characters use SUBSTR(s, LENGTH(s) - n + 1). The hardcoded - 6 in this example works out to the last 7 characters of 'murrayrebecca' — adjust the constant for the length you actually want.

These methods allow you to achieve the same functionality as LEFT() and RIGHT() using SUBSTR() in Firebolt.

This error occurs when Firebolt cannot convert data from a text format (e.g., CSV or TSV) to the expected column data type defined in the external table schema.

Common Scenarios:

Mismatched Data Types: If a column contains a value that doesn’t match the expected type (e.g., a string in a numeric column).

Example: A file contains the value "abc" in a column defined as LONG, which leads to the error.

Header Rows in Files: If a CSV file includes a header row and it's not excluded, Firebolt tries to interpret the header text as data.

Solution: Use SKIP_HEADER_ROWS in the TYPE parameter of the CREATE EXTERNAL TABLE DDL.

Troubleshooting Tip: Use a text editor to inspect the first few rows of the file for mismatches. If the issue isn’t obvious, use SELECT...LIMIT and OFFSET to locate problematic rows and identify the file using the SOURCE_FILE_NAME column.

Example query:

SELECT SOURCE_FILE_NAME, COUNT(*) 
FROM (SELECT *, SOURCE_FILE_NAME FROM my_external_table LIMIT 10000 OFFSET 0)
GROUP BY SOURCE_FILE_NAME;

When using a NOT IN filter, rows where the column value is NULL are excluded from the results, even though NULL is not in the list of values. This is because SQL treats comparisons with NULL as UNKNOWN, which prevents those rows from being returned.

How to include NULL in NOT IN results:

To include rows with NULL values, add an explicit condition checking for NULL using OR column IS NULL.

Example:

SELECT * 
FROM players 
WHERE playerid NOT IN (1, 2, 3) OR playerid IS NULL;

This query will include rows where playerid is either NOT IN the list or is NULL, ensuring that NULL values are part of the result set.

The behavior of quote escaping is controlled by the setting standard_conforming_strings. When this setting is enabled (the default behavior), backslashes are treated literally, and strings are parsed without escaping. This ensures consistent handling of literal strings and avoids unexpected transformations. If standard_conforming_strings is disabled, backslashes can be used as escape characters, altering how strings are interpreted. For more information, check our documentation.

Firebolt exposes several independent cache settings. Use enable_result_cache=FALSE to stop full query results being served from cache, enable_subresult_cache=FALSE to disable the subresult caching layer, and enable_scan_cache=FALSE for the post-scan column cache. For benchmarking, Firebolt recommends disabling only the result cache, which preserves the benefits of cross-query subresult reuse. Set all three only if you need a fully cold measurement. See system settings for details.

Use ARRAY_SORT to sort one array and apply the same order to the other. For example, if you have array1 and array2:
array1: [4, 1, 3, 2]
array2: [Z, X, Y, R]

SELECT 
    ARRAY_SORT(x, y -> y, ARRAY_AGG(array2), ARRAY_AGG(array1)) AS sorted_array2
FROM your_table;

This ensures the order in array1 is applied to both arrays, maintaining their alignment.

Output:
sorted_array1: [1, 2, 3, 4]
sorted_array2: [X, R, Y, Z]

For more information, check our documentation.

Use the source_file_name virtual column to filter rows based on the Parquet file name. For example:

SELECT $source_file_name, *
FROM external_table
WHERE $source_file_name ILIKE '%filename.parquet%';

This query retrieves rows where the source_file_name contains the specified file name. %filename.parquet% can be replaced with any pattern to match your file name.

For more information, see Using metadata virtual columns docs

There is no limit to the number of regex expressions you can use with REGEXP_LIKE_ANY.

Yes, and there are two paths. The native dbt-firebolt adapter allows you to model, transform, and manage your data workflows using dbt — note that it supports dbt Core only; dbt Cloud is not supported with the native adapter. Alternatively, you can connect through the dbt PostgreSQL adapter via Firebolt's Postgres-compatible endpoint, which works with both dbt Core and dbt Cloud. One dbt Cloud gotcha: its 63-character username limit means you should authenticate with a service account, whose 26-character client ID fits within it. For more details, visit Firebolt's blog on ELT with dbt.

At present, Firebolt does not have a direct API connection to external data sources like Google Sheets. However, you can leverage third-party tools or custom ETL pipelines to load data from sources like Google Sheets into Firebolt for analysis.

Yes, Firebolt integrates with a wide range of popular BI and data tools, including Looker, Tableau, and Power BI, among others. Firebolt's PostgreSQL-compatible wire protocol endpoint (currently in preview) lets any tool that speaks Postgres — such as Looker, Hex, Omni, QuickSight, and Lightdash — connect with a generic Postgres driver. Firebolt also ships SDKs and drivers for Go, JDBC, .NET, Node.js, Python, Rust, and SQLAlchemy, plus a REST API, to facilitate connectivity with other tools.

Yes. Firebolt natively supports geospatial data through the GEOGRAPHY data type and a full set of spatial SQL functions. Construct geographies with ST_GEOGPOINT, ST_GEOGFROMTEXT, ST_GEOGFROMGEOJSON, and ST_GEOGFROMWKB; run predicates with ST_CONTAINS, ST_COVERS, and ST_INTERSECTS; measure with ST_DISTANCE; and serialize with ST_ASTEXT, ST_ASBINARY, ST_ASGEOJSON, and ST_ASEWKB. No external tooling or string/numeric workaround is required.

If you encounter errors due to missing credentials when accessing AWS S3 from Firebolt, ensure that you have the correct IAM roles and policies assigned. Alternatively, you can provide AWS keys directly within your external table definition using the CREDENTIALS parameter. Check your AWS permissions and Firebolt’s documentation for troubleshooting credential errors.

Firebolt can be integrated with Coralogix through OpenTelemetry. Firebolt’s OTel Exporter allows you to export Firebolt engine metrics, query logs, and other telemetry data to any OpenTelemetry-compatible platform, including Coralogix. This integration enables real-time monitoring and troubleshooting, giving you better insights into engine performance, query execution, and resource usage. You can refer to Firebolt's GitHub repository for additional setup details and code samples.

System settings in Firebolt allow you to control query execution behavior and performance, providing flexibility when needed. This is particularly useful when you want to override default settings for specific queries via the REST API.

To adjust settings such as the time_zone, you can embed them directly in the URL of your API call. For example, if you need to set the time_zone to UTC, include the parameter in the API call URL.

Example API call:

curl --location 'https://<user engine URL>?engine=<engine_name>&database=<database_name>&time_zone=UTC' \
--header 'Authorization: Bearer <authentication_token>' \
--data "SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin'"

This query sets the time_zone system setting to UTC for the duration of the query. Each new API call requires you to include the necessary system settings again if you want to apply specific overrides.

Firebolt natively supports Apache Iceberg: query tables in place with READ_ICEBERG, register them with CREATE ICEBERG TABLE or CREATE ICEBERG DATABASE, inspect files with LIST_ICEBERG_FILES, and export results with CREATE ICEBERG TABLE AS SELECT (export only — no DML on Iceberg tables). Supported catalogs include file-based, REST, AWS Glue, Snowflake Open Catalog, and Databricks Unity Catalog, so Databricks-managed Iceberg tables are reachable today. DuckLake catalogs hosted on PostgreSQL are also readable via READ_DUCKLAKE (experimental). Delta Lake is not natively supported. See the Iceberg guide for details.

Firebolt is continuously expanding its integration ecosystem to support a wide range of data sources and connectors. If your preferred connector isn't listed in the current documentation, don’t worry! Firebolt’s development team is actively working on adding new integrations, and you can expect ongoing enhancements to its capabilities.

In the meantime, you can reach out to Firebolt support to inquire about upcoming connectors or even request a specific integration. Firebolt also supports custom connectors through its API and can integrate with many systems using standard protocols like JDBC and ODBC, giving you the flexibility to connect to external sources in various ways.

Yes, Firebolt supports data migration from Redshift through standard ETL tools. You can move data from Redshift to Firebolt by exporting Redshift data to S3 and then using Firebolt’s COPY FROM command to ingest data into Firebolt tables.

Firebolt provides a custom Airflow provider, airflow-provider-firebolt, that allows you to orchestrate and automate your Firebolt data workflows directly from Airflow. This integration helps in managing ETL processes, scheduling queries, and handling data pipelines efficiently. Note that the provider supports Airflow 2.x; Airflow 3.x is not yet supported.

Firebolt has a Confluent Kafka connector available in Confluent cloud to ensure real-time ingestion from Kafka. Additionally, you can ingest Kafka data into Firebolt using intermediate storage systems like S3.

Yes, Apache Iceberg is a first-class citizen. Query Iceberg tables in place with READ_ICEBERG, register them with CREATE ICEBERG TABLE or CREATE ICEBERG DATABASE, inspect physical layout with LIST_ICEBERG_FILES, and export results with CREATE ICEBERG TABLE AS SELECT. Supported catalogs include file-based, REST, AWS Glue, Snowflake Open Catalog, and Databricks Unity Catalog. Firebolt accelerates Iceberg queries with metadata caching (tunable via MAX_STALENESS), file- and row-group-level pruning, and co-located joins — see the Iceberg performance guide.

Yes, transferring data between different AWS regions incurs cross-region data transfer costs according to AWS pricing. Firebolt itself does not add additional fees for cross-regional data transfers, but users should consider AWS network charges when moving data across regions.

Firebolt runs on all three major clouds. Self-managed Firebolt is GA on AWS and GCP and in Preview on Azure, with managed-table storage on Amazon S3, Google Cloud Storage, Azure Blob Storage, or S3-compatible stores such Nebius Object Storage, and CoreWeave AI Object Storage. You deploy it into your own environment via the Helm chart, the Kubernetes Operator, or standalone binaries. Firebolt's fully managed service is currently offered on AWS only — if you want Firebolt on GCP or Azure today, use the self-managed deployment. For more details, see the architecture overview.

Firebolt supports deployment in multiple AWS regions, allowing you to choose the most appropriate region for your data and workloads. However, Firebolt does not currently offer seamless, cross-region deployments within a single account. To deploy across multiple regions, you need to create separate accounts in each region.

On Firebolt, data is stored in object storage like Amazon S3, Google Cloud Storage or Azure Blob Storage, which inherently offer durability and availability features leveraging copies of data stored across multiple zones (locations) per region. However, Firebolt does not natively provide cross-region disaster recovery (DR) at this time, so manual processes would need to be in place for cross-region DR setups. For current options around compute high availability across Availability Zones, contact support@firebolt.io.

Firebolt does not yet support automatic cross-region replication. If you need to replicate data across regions, you will need to handle the data replication process manually using external tools or services like AWS DataSync or S3 cross-region replication.

There is a soft limit of 100 databases per account, that can be increased if needed.

Databases and user-defined schemas together form the logical grouping hierarchy — every database has a default public schema, and you can add more with CREATE SCHEMA to organize objects by function, team, or access level.

Splitting into separate databases is mainly a governance decision: different databases can have different owners and permissions, which matters when different teams or departments manage their own data. Role-Based Access Control (RBAC) can also be applied at the schema, table, and column level to restrict access to specific users.

Cross-database queries are supported via three-part identifiers (database.schema.table), so a split does not prevent joins, set operations, MERGE, INSERT INTO ... SELECT, CTAS, or zero-copy cloning across databases. Two structural limits apply: a view's referenced tables must all live in the view's own database, and an index must be defined in the same database as its source table.

The information_schema.engine_query_history view retains the most recent 10,000 queries. High-volume workloads can reach that limit quickly, so we recommend regularly exporting or archiving query history to durable storage. A companion view, engine_user_query_history, filters the same data to user-submitted queries only, excluding queries generated by connectors, drivers, and SDKs.

Engine consumption data is available in the information_schema.engine_metering_history view, which provides hourly usage details at the account and engine level, including resource consumption and cost metrics. This data can also be retrieved via an API request. The view retains roughly 30 days of history by default, so export it regularly if you need longer-term records.

Firebolt provides comprehensive billing view that break down both compute (engine) consumption and storage usage. You can access detailed information on engine usage through the information_schema.engines_billing table and storage usage through the information_schema.storage_billing table. These tables and UI view offer granular insights into usage by specific engines, storage by table, and usage patterns, allowing for better cost tracking and resource optimization. The billing details can be viewed by hour, day, or month in the Firebolt UI, helping users stay informed about their resource consumption.

For finer-grained analysis, information_schema also exposes engine_metering_history, engine_metrics_history, engine_query_history, engine_running_queries, and quotas.

Firebolt's billing is generally sent monthly, aligning with the AWS billing cycle. The bill email provides a breakdown of engine usage and storage consumption, giving you visibility into your total cost. Because Firebolt runs on AWS infrastructure, its billing is influenced by the resources consumed in AWS, and the timing of Firebolt’s billing is closely aligned with AWS bills for the same period.

Consumption is measured only while an engine is in a running state — consumption stops when the engine stops.

Firebolt's managed service is available in six AWS regions: US East (N. Virginia, us-east-1), US West (Oregon, us-west-2), Europe (Frankfurt, eu-central-1), Europe (Ireland, eu-west-1), Asia Pacific (Singapore, ap-southeast-1), and Asia Pacific (Mumbai, ap-south-1). To request an additional region, contact support@firebolt.io. Self-managed Firebolt has no such restriction — deploy it wherever your own infrastructure runs. See available regions.

Yes, commitment based discounts are available. Contact our sales team for more information.

Firebolt provides multidimensional scaling to help right-size workloads. Autostop and Autostart are features that help reduce costs by eliminating idle time. Firebolt also provides global visibility of consumption and costs through built-in organizational governance and account-level consumption breakdown.

Firebolt provides engine consumption and spend information in the Web UI. Additionally, granular engine-level consumption can be found via the information_schema.engine_metering_history view that details the hourly consumption of all the engines within an account. Users can also drill down into how the topology of their engines (node type, number of nodes and number of clusters) was modified over time, providing visibility into the consumption of their engines.

Yes, during our POC process, Firebolt's team will provide you with fast and accurate cost estimates based on real usage data. During the POC, our team will closely support you, analyzing engine usage, query patterns, and resource consumption to deliver a precise cost breakdown. With our efficient benchmarking and expert guidance, you’ll quickly understand your projected costs, ensuring transparency and confidence in scaling with Firebolt.

Yes, an AWS account is linked to an organization. However, it is not possible to link accounts within an organization to different AWS accounts, billing is on the Organization level. For more information, check our documentation.

Yes, more details in our End User License Agreement (EULA) and Data Processing Addendum (DPA).

Yes, our SOC 2 Type-2 + HIPAA report is available subject to a Non-Disclosure Agreement (NDA).

Firebolt is certified for ISO 27001 and ISO 27018. Certification reports are available here.

Yes. As a business associate under HIPAA, we support business associate agreements (BAAs) to ensure healthcare data protection. Our SOC 2 Type-2 + HIPAA report is available subject to a Non-Disclosure Agreement (NDA)

A separate BAA with Firebolt is required since our service includes proprietary technology and other sub-processors not covered under the standard AWS HIPAA Eligible Services.

Firebolt is not PCI-DSS compliant and does not permit credit card data storage on its platform.

While Firebolt adheres to NIST SP 800-53, NIST 800-171, and NIST CSF guidelines, we are not currently FedRAMP compliant.

Firebolt processes customer data in compliance with both GDPR and CCPA regulations. We securely collect, store, and manage data according to the highest standards, ensuring that all GDPR and CCPA requirements are met.

For Data Subject Access Requests (DSARs) or any privacy-related inquiries, please reach out to us at privacy@firebolt.io

Yes, our policies, including Disaster Recovery (DR) and Business Continuity Plans (BCP), are tested regularly to ensure effectiveness.

Yes. Firebolt supports single sign-on via SAML 2.0 with Okta, Auth0, OneLogin, PingFederate, Salesforce, or a custom identity provider, plus multi-factor authentication using time-based one-time codes. All access is logged and monitored, with alerts in place for any unauthorized configuration changes across our systems. See Configure SSO.

We use tools like SCA, SAST for code analysis, along with practices such as Fuzzing, scanning for pipeline weaknesses (like the use of unverified external sources), and secret scans as part of our secure software development lifecycle.

Firebolt provides role-based access control (RBAC) at two tiers — organization and account — letting you grant privileges on databases, schemas, tables, views, engines, and locations to roles and assign those roles to users or service accounts. Column-level security restricts which columns of a table a role can read, and secure views execute with owner rights to implement row-level filtering and data masking. Object ownership and default privileges round out the model. See the RBAC documentation.

Network policies let you allow or block IP ranges at both the organization and login level. For private connectivity, Firebolt supports AWS PrivateLink (public preview), so traffic between your VPC and Firebolt never traverses the public internet.

Firebolt supports single sign-on via SAML 2.0 with Okta, Auth0, OneLogin, PingFederate, Salesforce, or a custom identity provider, multi-factor authentication with time-based one-time codes, and service accounts with client ID/secret pairs for programmatic access. See Configure SSO.

LOCATION objects store connection details and credentials for external systems — Amazon S3 buckets, Iceberg catalogs, Amazon Bedrock, and more — as securable, reusable objects. Statements reference the location instead of embedding secrets in SQL, and access to locations is governed by RBAC like any other object.

Firebolt stores unsaved scripts in your browser’s local storage, which has a limit of around 5 MB. If multiple websites use local storage, it can get full, causing unsaved scripts in the Firebolt SQL editor to be erased.

To avoid this:
Save your scripts regularly.
Clear your browser cache/cookies to free up local storage and prevent data loss.
Remember, clearing your cache will also remove other saved data, so use this solution carefully.

Firebolt values transparency and customer feedback when planning its roadmap. To view the current roadmap or see open feature requests, reach out to Firebolt’s support or your customer success manager. Additionally, Firebolt’s team actively gathers feedback from users and considers feature requests as part of ongoing development efforts. Regular updates are communicated through newsletters and user forums. Stay connected to get insights into upcoming releases and features tailored to your needs.