"
},
"Action": "sts:AssumeRole"
}
]
}
```
## Step 3: Invoke a Bedrock model from SQL
After you create the location and configure access, call a model using `AWS_BEDROCK_AI_QUERY` and pass the location name.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT AWS_BEDROCK_AI_QUERY(
'amazon.nova-micro-v1:0',
$${"schemaVersion":"messages-v1","messages":[{"role":"user","content":[{"text":"Hello"}]}]}$$,
'bedrock_role'
) AS result;
```
For details on inputs and responses, see [`AWS_BEDROCK_AI_QUERY`](/reference-sql/functions-reference/ai/aws-bedrock-ai-query).
## Related resources
* [CREATE LOCATION (Amazon Bedrock)](/reference-sql/commands/data-definition/create-location-bedrock)
* [LOCATION objects](/security/guides/location)
* [`AWS_BEDROCK_AI_QUERY`](/reference-sql/functions-reference/ai/aws-bedrock-ai-query)
# Change data capture
Source: https://docs.firebolt.io/guides/change-data-capture
Stream changes from Postgres and MongoDB directly into Firebolt tables, with exactly-once ingestion and freshness measured in seconds.
Nightly Feature
This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
Change data capture (CDC) keeps a Firebolt table continuously in sync with a table in your operational database. Firebolt connects directly to Postgres or MongoDB, reads their native change feeds, and applies every insert, update, and delete to a managed table that you query like any other. There is no connector, message queue, or pipeline to run in between.
A conventional CDC pipeline chains a log reader, a message bus, a sink connector, and a scheduled merge job; each hop adds latency and a delivery boundary the next hop must reconcile. Reading the source directly removes all of that: changes are visible in Firebolt within seconds, with exactly-once guarantees end to end.
## The three objects
CDC ingestion is built from three SQL objects, each created once:
1. A **location** stores the connection details and credentials for the source database. See [CREATE LOCATION (Postgres)](/reference-sql/commands/data-definition/create-location-postgres) and [CREATE LOCATION (MongoDB)](/reference-sql/commands/data-definition/create-location-mongodb).
2. A **stream** binds to one source table or collection and tracks a consumed position in its change feed: a replication slot for Postgres, a change-stream position for MongoDB. You can read a stream directly with [`READ_STREAM`](/reference-sql/functions-reference/table-valued/read_stream) to inspect raw change events. See [CREATE STREAM](/reference-sql/commands/data-definition/create-stream).
3. A **CDC table** consumes a Postgres or MongoDB stream and maintains the current state of the source table: one live row per key, with inserts, updates, and deletes applied in source order. See [CREATE CDC TABLE](/reference-sql/commands/data-definition/create-cdc-table).
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
┌──────────────────────┐
│ SOURCE DATABASE │
├──────────────────────┤
│ Postgres / MongoDB │
│ (WAL / change stream)│
└──────────┬───────────┘
▼
┌──────────────────────┐ ┌────────────────────────┐
│ FIREBOLT ENGINE │ │ FIREBOLT ENGINE │
├──────────────────────┤ ├────────────────────────┤
│ runs the ingest │ │ any other engine │
│ worker: decodes and │ │ SELECT ... FROM orders │
│ appends changes, │ │ │
│ commits data + stream│ │ │
│ position atomically │ │ │
└──────────┬───────────┘ └───────────▲────────────┘
▼ │
┌─────────────────────────────────────────┴────────────┐
│ OBJECT STORAGE │
├──────────────────────────────────────────────────────┤
│ the CDC table's tablets │
└──────────────────────────────────────────────────────┘
```
A minimal end-to-end setup is three statements plus one to start ingestion:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION pg_prod WITH (
SOURCE = 'POSTGRES',
HOST = 'pg.example.internal',
DATABASE = 'app',
USER = 'firebolt_cdc',
PASSWORD = '********',
PUBLICATION = 'firebolt_cdc'
);
CREATE STREAM orders_changes (order_id BIGINT, customer_id BIGINT, amount NUMERIC(12, 2))
TABLE = 'public.orders'
LOCATION = 'pg_prod';
CREATE CDC TABLE orders (
order_id BIGINT,
customer_id BIGINT,
amount NUMERIC(12, 2),
PRIMARY KEY (order_id) NOT ENFORCED
) FROM STREAM orders_changes;
ALTER CDC TABLE orders RESUME;
```
The table backfills from a snapshot of the source, switches to the live change feed, and stays current from then on. `SELECT * FROM orders` returns the mirror at your query's snapshot, on any engine in the account.
Both phases, and the ingest's health, are visible in [`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests):
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT table_name, state, phase, apply_count, live_lag_ms
FROM information_schema.cdc_ingests;
```
`phase` distinguishes the initial backfill from live streaming, and `live_lag_ms` shows how far behind the worker is.
## What you can rely on
* **Exactly once.** The stream's position and the rows it produced commit in the same transaction, so no change is lost or applied twice. There is no deduplication step to build or monitor.
* **Always consistent.** A query sees each change fully applied or not at all, at its own snapshot, on every engine.
* **Fresh within seconds.** A change is visible as soon as its batch commits. Background consolidation never delays it.
* **Fast queries at high change rates, on your terms.** You choose, per table, where the merge work runs. The default merges on read: ingestion stays append-only, superseded rows are excluded through deletion masks, and queries keep running as a single sorted, indexed scan while changes pour in. `merge_mode = 'write'` merges on the write side instead: reads carry no merge work at all, which buys the last bit of analytical query performance, and the table stays open to direct DML. [CDC tables](/guides/change-data-capture/cdc-tables) covers the trade-off.
* **Isolated ingestion.** The ingest worker runs on the engine where you execute `ALTER CDC TABLE ... RESUME`. Every other engine reads the same continuously updated table from object storage without sharing compute with it.
* **Scale-out for hot tables.** A partitioned Postgres source is ingested with one replication slot per partition, in parallel. See [Postgres CDC](/guides/change-data-capture/postgres#partitioned-source-tables).
## Guides
* [Postgres CDC](/guides/change-data-capture/postgres): prerequisites, setup, partitioned sources, TOAST handling, operations.
* [MongoDB CDC](/guides/change-data-capture/mongodb): prerequisites, schema inference, querying undeclared fields, operations.
* [CDC tables](/guides/change-data-capture/cdc-tables): semantics, merge modes and their performance characteristics, lifecycle, monitoring.
# CDC tables
Source: https://docs.firebolt.io/guides/change-data-capture/cdc-tables
How CDC tables keep one live row per key, what the two merge modes cost, and how to operate them.
Nightly Feature
This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
A CDC table is a managed table that Firebolt maintains from a change stream: one live row per merge key, with source inserts, updates, and deletes applied continuously and in order. You query it like any other table; ingestion, staging storage, and background consolidation belong to the engine.
For source-specific setup, see [Postgres CDC](/guides/change-data-capture/postgres) and [MongoDB CDC](/guides/change-data-capture/mongodb); for exact DDL syntax, see [CREATE CDC TABLE](/reference-sql/commands/data-definition/create-cdc-table).
## Creating a CDC table
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE CDC TABLE orders (
order_id BIGINT,
customer_id BIGINT,
amount NUMERIC(12, 2),
PRIMARY KEY (order_id) NOT ENFORCED
) FROM STREAM orders_changes;
ALTER CDC TABLE orders RESUME;
```
`PRIMARY KEY ... NOT ENFORCED` names the **merge key**: the column set that identifies a row across its lifetime, normally the source table's primary key. Composite keys are supported. The key is trusted, not checked; one live row per key follows from applying the source's changes in order, not from a uniqueness scan.
You can also omit the column list, and the columns and key are inferred from the stream: `CREATE CDC TABLE orders FROM STREAM orders_changes`. Tables over MongoDB streams are usually created this way.
An optional `PRIMARY INDEX` clause sets the table's sort order independently of the merge key, for example on an event-time column your dashboards filter by. By default the table is sorted and indexed on the merge key.
`CREATE CDC TABLE` only records metadata. Ingestion starts with `ALTER CDC TABLE ... RESUME`: the worker backfills from a snapshot of the source, then switches to the change feed. Both phases are visible in [`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests).
## Semantics
**The latest change wins, per key.** Every change carries a source sequence: the WAL position for Postgres, the cluster timestamp for MongoDB. The row with the highest sequence is the live row for its key, and that is decided by the sequence, not by arrival order: a redelivered or late event with an older sequence never overwrites newer state. A delete is a change like any other: when it is the latest event for a key, the row is gone until a later insert re-creates it.
**Ordered per key at the source.** Each key's changes flow through a single feed in commit order (for partitioned Postgres sources, the partition-key-in-merge-key rule guarantees this). Sequence-based resolution already makes inserts and updates safe against redelivery; deletes are what rely on this ordering, because a deleted row leaves nothing behind for an even older event to compare against.
**Reads are always current and consistent.** A `SELECT` returns the latest-per-key state as of its transaction snapshot, including changes not yet consolidated into base storage. Freshness equals the ingest worker's append cadence, typically a few seconds behind the source commit. A query pins one snapshot across the table's base and staging storage, so no reader, on any engine, ever observes a half-applied batch or a mid-consolidation state.
**Exactly-once effect.** The stream position advances only with the transaction that applied the batch, and every apply is guarded by the source sequence. Crashes and restarts can only redeliver changes that then apply as no-ops.
## Merge modes
Keeping one live row per key means superseded row versions must be excluded somewhere. The `merge_mode` option chooses where: on the read side (`'read'`, the default) or eagerly on the write path (`'write'`). The mode is set at creation and cannot be changed later.
### merge\_mode = 'read' (default)
Changes are appended to hidden staging storage, an O(batch) operation that never touches the base table. Superseded rows are excluded positionally: when a query plans against the table, the engine resolves which physical row positions are superseded, by merging the staged changes per key and probing an index on the merge key for each older version's position. The result is a per-tablet **deletion mask**, the same Roaring-bitmap primitive that serves [deletes on managed tables](/performance-and-observability/storage-and-indexing#writes%2C-updates%2C-and-deletes). The scan then reads base and staging as one plain table with masks applied.
* **Read cost: mask resolution, then a plain scan.** Before the scan, the engine resolves which row positions are superseded; the result depends only on the current set of tablets, so it is computed once, cached, and shared by concurrent queries. The scan then applies the masks exactly as row-level deletes are applied on any managed table, and is otherwise ordinary: the `WHERE` clause prunes tablets and granules through the same min/max metadata, and predicate pushdown and index-served reads keep their full effect. Masks never pile up, either: tablets where masked rows accumulate are vacuumed automatically in the background, so the overhead stays bounded no matter how long the table has been ingesting.
* **Write cost: appends plus a background cascade.** On a cadence (`cascade_interval`, default 60 seconds), the cascade folds the current masks into committed deletion masks and adopts the staging tablets into the base as a metadata-only re-parent; no row data is rewritten to move a change from staging to base. The vacuum of heavily masked tablets runs within a per-cascade budget so it never delays adoption, and the staging window is consolidated into well-sized tablets before adoption. For a partitioned Postgres source, each source partition consolidates independently and in parallel.
Merge-key columns must have an index-supported type: `BOOLEAN`, `INTEGER`, `BIGINT`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`, `NUMERIC`, or `TEXT`.
### merge\_mode = 'write'
Each batch of changes is merged directly into the table: matched keys updated or deleted, new keys inserted, guarded by the source sequence. The table is always fully consolidated, and it stays an ordinary table operationally: direct DML and schema changes remain available.
* **Read cost: none.** Queries scan a plain, fully consolidated table.
* **Write cost: a per-batch merge that probes the table by key.** On a large table with uniformly distributed keys, each batch touches most of the table, so merge cost is driven by table size rather than batch size. At sustained high change rates this becomes the bottleneck: at 10,000 random-key changes per second against a multi-billion-row table, ingestion settles into a steady-state lag of several minutes that more compute does not remove.
### Choosing
Stay with the default `read` mode unless you have a reason not to: it sustains high change rates and gives queries a plain scan. Choose `write` when the change volume is modest and you want the table to remain open to direct DML and schema changes, or when the merge key's type is not supported by `read` mode.
## Lifecycle and operations
* `ALTER CDC TABLE RESUME` starts ingestion and binds the worker to the engine you run it on. The enabled state is persisted: after an engine restart, ingestion resumes automatically from the committed stream position, including a restart mid-backfill.
* `ALTER CDC TABLE SUSPEND` stops the worker. The table stays queryable at its last state. For Postgres sources, the idle replication slot retains WAL on the source while suspended.
* `ALTER CDC TABLE CASCADE` runs a consolidation immediately instead of waiting for the next interval.
* `DROP TABLE ` drops the table together with its hidden staging storage and stops the worker.
* **Workload isolation.** Ingestion consumes compute only on the engine where it was resumed. Every other engine queries the same table from object storage at full freshness, so a dedicated ingest engine isolates CDC work from serving entirely.
### Monitoring
[`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests) has one row per CDC table with an active ingest worker; query it on the engine where ingestion runs:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT table_name, stream_name, state, phase,
apply_count, last_commit_time, live_lag_ms, last_error, error_count
FROM information_schema.cdc_ingests;
```
`state` and `phase` distinguish the initial backfill from steady-state streaming, `apply_count` counts applied batches (source transactions for a Postgres stream; the initial load is not counted), `live_lag_ms` measures time since the worker last advanced (a growing value while `streaming` signals a stalled feed), and `last_error` carries the most recent failure.
## Restrictions
In `read` mode, the table's state belongs to the ingest worker:
* No direct `INSERT`, `UPDATE`, `DELETE`, `MERGE`, or `TRUNCATE`.
* No column-level `ALTER TABLE` and no `ALTER TABLE ... SET PRIMARY INDEX`; renaming the table is allowed.
* No aggregating indexes.
`write` tables accept direct DML and schema changes like ordinary tables.
General limits:
* `merge_mode` is fixed at creation.
* One stream feeds one CDC table. This is not a scaling limit for partitioned Postgres sources: the stream detects the partitioning at creation and ingests the leaf partitions in parallel, still as one stream into one table. See [partitioned source tables](/guides/change-data-capture/postgres#partitioned-source-tables).
* The change feed must be in order per key, which the supported sources guarantee; there is no reordering buffer for late events.
* Column names beginning with `__`, plus `last_seq`, are reserved for CDC bookkeeping.
# MongoDB CDC
Source: https://docs.firebolt.io/guides/change-data-capture/mongodb
Ingest changes from MongoDB into Firebolt through change streams, with inferred schemas and every document field queryable.
Nightly Feature
This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
Firebolt connects to your MongoDB deployment and keeps a [CDC table](/guides/change-data-capture/cdc-tables) continuously in sync with a collection: every insert, update, replace, and delete arrives within seconds, with nothing to deploy in between. Under the hood, the connection is a native MongoDB change stream.
Documents do not need a fixed schema to become queryable. Fields with stable types become typed columns, and every other field stays reachable by name through a JSON fallback.
## Prepare the source deployment
On the MongoDB side you need:
1. **A replica set or sharded cluster.** Change streams read the oplog; a standalone `mongod` has none.
2. **MongoDB 6.0 or later**, with document pre- and post-images enabled on the collection. Firebolt reads exact point-in-time images rather than re-fetching documents, so updates are never observed out of order or half-applied:
```javascript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
db.runCommand({
collMod: "orders",
changeStreamPreAndPostImages: { enabled: true }
})
```
3. **A role with `find` and `changeStream` privileges** on the collection.
4. **Oplog and pre-image retention** sized so the feed's history outlives any planned ingestion pause. If the stored position ages out of the oplog, ingestion cannot resume from it and the table must be re-bootstrapped.
5. **Network reachability** from the Firebolt engine to the cluster (VPC peering, PrivateLink, or an Atlas access list).
## Create the location
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION mongo_prod WITH
SOURCE = MONGODB
URI = 'mongodb+srv://cluster0.example.mongodb.net/'
USER = 'firebolt_cdc'
PASSWORD = '********';
```
TLS follows the URI: `mongodb+srv://` connections use TLS, and plain `mongodb://` URIs can request it with `tls=true`. Credentials can also be embedded in the URI instead of `USER` and `PASSWORD`. See [CREATE LOCATION (MongoDB)](/reference-sql/commands/data-definition/create-location-mongodb).
## Create the stream
A stream binds to one collection, named as `.`. You can declare the columns explicitly:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE STREAM orders_changes (
_id TEXT,
status TEXT,
amount BIGINT
)
LOCATION = 'mongo_prod'
COLLECTION = 'app.orders'
KEY = '_id';
```
or omit the column list and let Firebolt infer it:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE STREAM orders_changes
LOCATION = 'mongo_prod'
COLLECTION = 'app.orders';
```
`KEY` names the column used as the merge key downstream and defaults to `'_id'`.
### Schema inference
Inference reads the collection's `$jsonSchema` validator. Every top-level field the validator declares with a single scalar type (or a homogeneous array of scalars) becomes a typed column:
| BSON type | Firebolt type |
| :----------------------- | :-------------- |
| `bool` | `BOOLEAN` |
| `int` | `INT` |
| `long` | `BIGINT` |
| `double` | `DOUBLE` |
| `string`, `objectId` | `TEXT` |
| `date` | `TIMESTAMPTZ` |
| array of one scalar type | `ARRAY()` |
Fields with polymorphic types, nested objects, `decimal128`, and anything the validator does not declare are deliberately left out of the typed schema; they stay fully queryable through the overflow fallback described below. Inference runs once, at `CREATE STREAM`; a collection without a `$jsonSchema` validator requires an explicit column list.
## Read raw change events
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT "$op", "$wall_time", _id, status, amount
FROM READ_STREAM(STREAM orders_changes, consume => false);
```
Change rows carry the declared columns plus pseudo-columns (reference them double-quoted):
| Pseudo-column | Type | Description |
| :------------------- | :---------- | :-------------------------------------------------------------------------------------------------------- |
| `$op` | TEXT | Operation: `I` insert, `U` update, `R` replace, `D` delete, `T` collection drop, rename, or invalidation. |
| `$doc` | JSON | Exact post-image of the document; `NULL` for deletes. |
| `$before` | JSON | Exact pre-image; `NULL` for inserts. |
| `$update_desc` | JSON | Updated fields, removed fields, and truncated arrays, for updates. |
| `$key` | JSON | The document key (`_id`, plus the shard key on sharded clusters). |
| `$resume_token` | TEXT | The change stream position of this event. |
| `$cluster_time` | BIGINT | Cluster timestamp, packed into a 64-bit integer; the ordering authority for the feed. |
| `$wall_time` | TIMESTAMPTZ | Wall-clock time of the operation on the source. |
| `$ns` | TEXT | Namespace, as `database.collection`. |
| `$stream_row_number` | BIGINT | 1-based position of the row within this statement's read, in delivery order. |
The first consuming read with no stored position starts at the current end of the oplog; it does not replay history. With `snapshot => true`, `READ_STREAM` instead scans the collection and returns every document as a synthetic insert, pinning the change-stream position first so the subsequent feed continues with no gap. The CDC table's initial backfill uses this path.
Consuming reads advance the stream's stored position in the same Firebolt transaction that writes the data, so ingestion is exactly-once with no deduplication step.
## Create the CDC table and start ingestion
The inference form mirrors the stream's schema, so a complete continuously updated mirror of a collection is two statements:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE CDC TABLE orders FROM STREAM orders_changes;
ALTER CDC TABLE orders RESUME;
```
The worker backfills from a collection snapshot, then applies the change feed continuously, ordered by `$cluster_time`. Semantics, merge modes, and lifecycle commands are covered in [CDC tables](/guides/change-data-capture/cdc-tables); progress is visible in [`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests).
### Query any field, declared or not
A CDC table created through inference carries a hidden JSON overflow column holding each document's fields that are not typed columns. Name resolution falls back to it automatically, so undeclared fields and nested paths work in ordinary SQL without schema changes:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- status and amount are typed columns; coupon and customer.city live in the overflow
SELECT _id, status, coupon
FROM orders
WHERE CAST(customer.city AS TEXT) = 'Berlin';
```
New fields that start appearing in documents after the stream was created land in the overflow as well, so source schema drift never breaks ingestion or requires DDL. When a field stabilizes and earns a typed column (for query performance or for use in the merge key), recreate the stream and table with the field declared.
## Operations
* **Delivery and recovery.** The resume token commits atomically with the data it produced. After a crash or engine restart, ingestion resumes from the last committed token; the apply is additionally guarded by `$cluster_time`, so a replayed event is a no-op.
* **Feed expiry.** If ingestion is paused long enough for the stored token to age out of the oplog (or the needed pre-images to expire), the stream reports that its history is lost. Nothing on the source is harmed; recreate the CDC table to re-baseline from a fresh snapshot.
* **Collection lifecycle events.** A collection drop, rename, or stream invalidation emits a terminal `$op = 'T'` and ends the feed; recreate the stream and table against the new collection.
* **`DROP STREAM`** removes only Firebolt metadata. MongoDB change streams hold no server-side resource comparable to a replication slot, so there is nothing to clean up on the source.
## Limitations
* One collection per stream.
* Inference requires a `$jsonSchema` validator; without one, declare columns explicitly.
* `ALTER STREAM` is not supported; schema changes are drop-and-recreate.
* `decimal128` and nested-object fields are not inferred as typed columns; they are available through the overflow fallback.
# Postgres CDC
Source: https://docs.firebolt.io/guides/change-data-capture/postgres
Ingest changes from Postgres into Firebolt through logical replication, from source setup to a continuously updated table.
Nightly Feature
This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
Firebolt connects to your Postgres database and keeps a [CDC table](/guides/change-data-capture/cdc-tables) continuously up to date with it: every insert, update, and delete on the source arrives within seconds, with nothing to deploy in between. Under the hood, the connection is native Postgres logical replication.
This page walks through preparing the source, setting up the stream and table, and day-to-day operations, with the advanced details at the end.
## Prepare the source database
On the Postgres side you need:
1. **Logical WAL.** Set `wal_level = logical` (requires a restart).
2. **A replication role.** The connecting user needs the `REPLICATION` attribute and `SELECT` on the tables you capture:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE ROLE firebolt_cdc WITH LOGIN REPLICATION PASSWORD '********';
GRANT SELECT ON public.orders TO firebolt_cdc;
```
3. **A publication** covering the table:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE PUBLICATION firebolt_cdc FOR TABLE public.orders;
```
4. **Slot headroom.** Each stream takes one replication slot (one per leaf partition for [partitioned sources](#partitioned-source-tables)). Size `max_replication_slots` and `max_wal_senders` accordingly.
5. **Replica identity.** The default (primary key) identity is sufficient: deletes and updates carry the key. Set `REPLICA IDENTITY FULL` on the table if updates can leave large out-of-line values unchanged (see [TOAST columns](#toast-columns)).
If something is missing, `CREATE STREAM` fails with the Postgres server's original error message.
A replication slot pins WAL on the source until its consumer acknowledges it. While ingestion is running this is a bounded window; if you suspend ingestion for a long period or stop querying a stream, the source retains WAL for the slot and its disk usage grows. Drop streams you no longer consume.
## Create the location
The location stores the connection details, credentials, and the publication name.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION pg_prod WITH (
SOURCE = 'POSTGRES',
HOST = 'pg.example.internal',
PORT = '5432',
DATABASE = 'app',
USER = 'firebolt_cdc',
PASSWORD = '********',
PUBLICATION = 'firebolt_cdc'
);
```
Connections to any host other than `localhost` use TLS, verified against the operating system trust store of the Firebolt nodes. There is no connection-level option to disable verification or point at a CA file; to use a private certificate authority, install its certificate into the nodes' system trust store. See [CREATE LOCATION (Postgres)](/reference-sql/commands/data-definition/create-location-postgres) for the full parameter reference.
## Create the stream
A stream binds to one source table and declares the columns to capture, mapped to Firebolt types. Column definitions are required for Postgres streams; there is no schema inference from the source.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE STREAM orders_changes (
order_id BIGINT,
customer_id BIGINT,
amount NUMERIC(12, 2)
)
TABLE = 'public.orders'
LOCATION = 'pg_prod';
```
If the table stores large `TEXT`, `BYTEA`, or `JSON` values, read [TOAST columns](#toast-columns) before creating the stream.
`CREATE STREAM` connects to the source, discovers whether the table is partitioned, and creates the replication slot (or one slot per leaf partition). The slot's starting position is recorded so that a later snapshot backfill and the change feed line up with no gap. No data is copied at create time.
Renaming or retyping a captured column on the source requires dropping and recreating the stream; there is no `ALTER STREAM` for Postgres streams.
Support for upstream schema evolution, so that source column changes flow through without recreating the stream, is planned for a future release.
## Create the CDC table and start ingestion
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE CDC TABLE orders (
order_id BIGINT,
customer_id BIGINT,
amount NUMERIC(12, 2),
PRIMARY KEY (order_id) NOT ENFORCED
) FROM STREAM orders_changes;
ALTER CDC TABLE orders RESUME;
```
The `PRIMARY KEY ... NOT ENFORCED` clause names the merge key: the columns that identify a row across its lifetime, normally the source table's primary key. On `RESUME`, the ingest worker backfills the table from a source snapshot and then applies the change feed continuously. Semantics, merge modes, and lifecycle commands are covered in [CDC tables](/guides/change-data-capture/cdc-tables).
Monitor progress through [`information_schema.cdc_ingests`](/reference-sql/information-schema/cdc-ingests):
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT table_name, state, phase, apply_count, live_lag_ms, last_error
FROM information_schema.cdc_ingests;
```
## Operations
* **Delivery and recovery.** The replication slot's consumed position advances only after the Firebolt transaction that read the batch commits. After a crash or engine restart, the worker resumes from the slot's confirmed position; a batch that was read but not committed is redelivered, and the sequence-guarded apply makes the redelivery a no-op. The net effect is exactly-once application.
* **Engine restart.** Ingestion resumes automatically for every CDC table whose ingest is enabled; no manual `RESUME` is needed after a restart.
* **Source `TRUNCATE` halts ingestion.** A truncate on the source is not applied. Ingestion stops at that point in the feed and the table keeps its pre-truncate state, so an operational mistake on the source does not silently empty the analytical mirror. Recreate the CDC table to re-baseline from a fresh snapshot.
* **`DROP STREAM` drops the replication slot(s)** on the source. If the source is unreachable, the stream is still dropped and the error message names the leftover slot so you can remove it with `SELECT pg_drop_replication_slot('')`.
* **One consumer per slot.** A stream's change feed has a single consuming reader. The CDC table's ingest worker is that consumer; use `consume => false` for ad-hoc reads alongside it.
## Limitations
* Captured column types must be primitive (no `STRUCT`).
* Source DDL is not replicated. Adding a column on the source is invisible until you recreate the stream with the new column; dropping a captured column breaks ingestion.
* `ALTER STREAM` is not supported for Postgres streams.
## Advanced
The sections below cover details that most setups never need: inspecting the raw change feed, scaling ingestion for partitioned sources, and how large out-of-line values behave.
### Read raw change events
`READ_STREAM` returns the decoded change feed. Use `consume => false` to peek without advancing the slot; peeks are isolated and repeatable, so they are safe for inspection at any time.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT "$op", "$commit_ts", order_id, customer_id, amount
FROM READ_STREAM(STREAM orders_changes, consume => false);
```
Each change row carries the declared columns plus pseudo-columns (reference them double-quoted):
| Pseudo-column | Type | Description |
| :------------------- | :---------- | :----------------------------------------------------------------------------- |
| `$op` | TEXT | Operation: `I` insert, `U` update, `D` delete, `T` truncate, `R` snapshot row. |
| `$lsn` | BIGINT | WAL position of the change, packed into a 64-bit integer. |
| `$commit_lsn` | BIGINT | WAL position of the enclosing transaction's commit. |
| `$commit_ts` | TIMESTAMPTZ | Commit timestamp of the enclosing transaction. |
| `$xid` | BIGINT | Source transaction ID. |
| `$source_partition` | BIGINT | Leaf-partition ordinal for partitioned sources; `0` otherwise. |
| `$stream_row_number` | BIGINT | 1-based position of the row within this statement's read, in delivery order. |
Changes appear in commit order, and uncommitted transactions are never surfaced. Under the default replica identity, a delete carries only the key columns (other columns are `NULL`), and a primary-key update decomposes into a delete of the old key and an update with the new one.
With `snapshot => true`, `READ_STREAM` returns a full snapshot of the source table (rows tagged `$op = 'R'`) instead of the change feed. The CDC table's initial backfill uses this path; you rarely call it directly.
### Partitioned source tables
A single replication slot is decoded by one `walsender` process on the source, which caps a slot's throughput at a few hundred thousand row changes per second regardless of how large the source machine is. For hot tables, Postgres declarative partitioning removes that ceiling.
When the source table is partitioned, `CREATE STREAM` discovers the leaf partitions automatically and creates one replication slot per leaf. The engine then snapshots and tails all leaves in parallel, and the decode work spreads across one `walsender` per leaf on the source. No extra syntax is involved; partitioning is detected from the table itself.
Two requirements come with this:
* **One dedicated publication per leaf.** Each leaf partition needs a publication that covers exactly that leaf (`CREATE PUBLICATION orders_p0 FOR TABLE public.orders_p0;`). `CREATE STREAM` verifies this and names the missing publication in its error message.
* **The partition key must be part of the CDC table's merge key.** Postgres routes a row's changes to the leaf its partition key selects, so requiring the partition key inside the merge key guarantees all changes for a given key flow through one slot, in order. `CREATE CDC TABLE` enforces this and rejects a key that could split across slots.
Rows from each leaf carry their leaf's ordinal in `$source_partition`, and the CDC table keeps each source partition in its own storage partition so background consolidation runs per partition, in parallel.
### TOAST columns
Postgres stores large values (roughly 2 KB and up, in `TEXT`, `BYTEA`, `JSON`, and similar columns) out of line in TOAST storage, and logical replication omits such a value from an update's row image when the update leaves it unchanged. The value is then simply not in the change feed. Inline-sized values are unaffected; declaring `TEXT` columns is fine, and most tables never hit this.
The `SUPPORT_TOASTED_VALUES` stream option controls what happens when an omitted value is encountered:
* **`FALSE` (default).** The source keeps its default replica identity. If an update omits an unchanged out-of-line value, ingestion stops with an error naming the column, rather than writing a wrong value into the mirror. The table keeps its last consistent state, and the error message carries the remediation.
* **`TRUE`.** The source table must use `REPLICA IDENTITY FULL`, which makes every update carry the full old row; Firebolt recovers unchanged values from it. If the identity is not actually `FULL`, ingestion stops with an error pointing at the missing `ALTER TABLE`.
For a table whose large columns are updated in place (so unchanged out-of-line values occur), configure both sides together. On the source Postgres database:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER TABLE public.orders REPLICA IDENTITY FULL;
```
And in Firebolt:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE STREAM orders_changes (
order_id BIGINT,
status TEXT,
payload TEXT
)
TABLE = 'public.orders'
SUPPORT_TOASTED_VALUES = TRUE
LOCATION = 'pg_prod';
```
`REPLICA IDENTITY FULL` is a property of the source table, not of the stream: it controls what Postgres writes into the WAL at update time, which every replication consumer then shares, so it cannot be set per slot. It increases WAL volume on the source because every update and delete logs the full old row; that is the cost of exact mirroring for out-of-line values. Leave the option off when your updates always rewrite the large columns or the values stay inline.
# Go
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-go
Learn about using the Go SDK for Firebolt.
## Overview
The Firebolt Go SDK is an implementation of Go's `database/sql/driver` interface, enabling Go developers to connect to and interact with Firebolt databases seamlessly.
## Prerequisites
You must have the following prerequisites before you can connect your Firebolt account to Go:
* **Go installed and configured** on your system. The minimum supported version is 1.18 or higher. If you do not have Go installed, you can download the [latest version](https://go.dev/dl/). After installing, if you don't have a Go module yet, you'll need to initialize one. See the [Go documentation on modules](https://go.dev/doc/tutorial/create-module) for detailed instructions on how to create and initialize a Go module.
* **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
* **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
* **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
* **Firebolt database and engine (optional)** – You can optionally connect to a Firebolt database and/or engine. If you do not have one yet, you can [create a database](/overview/quickstart#create-a-database) and also [create an engine](/overview/quickstart#create-an-engine). You would need a database if want to access stored data in Firebolt and an engine if you want to load and query stored data.
## Installation
To install the Firebolt Go SDK, run the following `go get` command from inside your Go module:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
go get github.com/firebolt-db/firebolt-go-sdk
```
## DSN Parameters
Go passes a data source name (DSN) to Firebolt's Go SDK to connect to Firebolt. The SDK parses the DSN string for parameters to authenticate and connect to a Firebolt account, database, and engine.
The DSN string supports the following parameters:
* `client_id`: client ID of your [service account](/managed-service/organization/service-accounts).
* `client_secret`: client secret of your [service account](/managed-service/organization/service-accounts).
* `account_name`: The name of your Firebolt [account](/guides/managing-your-organization/managing-accounts).
* `database`: (Optional) The name of the [database](/security/rbac/database-permissions) to connect to.
* `engine`: (Optional) The name of the [engine](/security/rbac/engine-permissions) to run SQL queries on.
The following is an example DSN string:
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
firebolt://[/]?account_name=&client_id=&client_secret=&engine=
```
## Connect to Firebolt
To establish a connection to a Firebolt database, construct a DSN string with your credentials and database details. The following example contains a script to connect to Firebolt that you can place in a file (e.g `main.go`) and run using `go run main.go` inside your Go module:
```go theme={"theme":{"light":"css-variables","dark":"css-variables"}}
package main
import (
"database/sql"
"fmt"
// Import the Firebolt Go SDK
_ "github.com/firebolt-db/firebolt-go-sdk"
)
func main() {
// Replace with your Firebolt credentials and database details
clientId := "your_client_id"
clientSecret := "your_client_secret"
accountName := "your_account_name"
databaseName := "your_database_name" // Optional parameter
engineName := "your_engine_name" // Optional parameter
dsn := fmt.Sprintf("firebolt:///%s?account_name=%s&client_id=%s&client_secret=%s&engine=%s", databaseName, accountName, clientId, clientSecret, engineName)
// Open a connection to the Firebolt database
db, err := sql.Open("firebolt", dsn)
if err != nil {
log.Fatalf("Error opening database connection: %v\n", err)
return
}
defer db.Close()
// Your database operations go here
}
```
## Run queries
Once connected, you can run SQL queries. The following examples show you how to create a table, insert data, and retrieve data. You can place them inside the previous script under \`// Your database operations go here\`\`:
```go theme={"theme":{"light":"css-variables","dark":"css-variables"}}
// Create a table
_, err = db.Exec("CREATE TABLE IF NOT EXISTS test_table (id INT, value TEXT)")
if err != nil {
log.Fatalf("Error creating table: %v\n", err)
return
}
// Insert data into the table
_, err = db.Exec("INSERT INTO test_table (id, value) VALUES (?, ?)", 1, "sample value")
if err != nil {
log.Fatalf("Error inserting data: %v\n", err)
return
}
// Query data from the table
rows, err := db.Query("SELECT id, value FROM test_table")
if err != nil {
log.Fatalf("Error querying data: %v\n", err)
return
}
defer rows.Close()
// Iterate over the result set
for rows.Next() {
var id int
var value string
if err := rows.Scan(&id, &value); err != nil {
log.Fatalf("Error scanning row: %v\n", err)
return
}
log.Print("Row: id=%d, value=%s\n", id, value)
}
```
## Streaming Queries
Firebolt supports streaming large query results using `rows.Next()`, allowing efficient processing of large datasets.
If you enable result streaming, the query execution might finish successfully, but the actual error might be returned while iterating the rows.
To enable streaming, use the `firebolt-go-sdk/context` package to create a context with streaming enabled:
```go theme={"theme":{"light":"css-variables","dark":"css-variables"}}
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/firebolt-db/firebolt-go-sdk"
fireboltContext "github.com/firebolt-db/firebolt-go-sdk/context"
)
func main() {
dsn := "firebolt:///your_database_name?account_name=your_account_name&client_id=your_client_id&client_secret=your_client_secret"
db, err := sql.Open("firebolt", dsn)
if err != nil {
log.Fatalf("Failed to open database: %v", err)
}
defer db.Close()
streamingCtx := fireboltContext.WithStreaming(context.Background())
// Execute a query with streaming enabled. Imitate large query result
rows, err := db.QueryContext(ctx, "SELECT 123, 'data' FROM generate_series(1, 100000000)")
if err != nil {
log.Fatalf("Query execution failed: %v", err)
}
defer rows.Close()
for rows.Next() {
var col1 string
var col2 int
if err := rows.Scan(&col1, &col2); err != nil {
log.Fatalf("Error scanning row: %v", err)
}
log.Print("Row: col1=%s, col2=%d\n", col1, col2)
}
if err := rows.Err(); err != nil {
log.Fatalf("Row iteration error: %v", err)
}
}
```
Streaming queries are particularly useful when dealing with large datasets, as they avoid loading the entire result set into memory at once.
## Troubleshooting
When building a DSN to connect with Firebolt using the Go SDK, follow these best practices to ensure correct connection string formatting and avoid parsing errors. The DSN must follow this structure:
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
firebolt:///?account_name=&client_id=&client_secret=&engine=
```
**Guidelines**
* Place the database name in the URI path after `firebolt:///`.
* Use only letters, numbers, and underscores (\_) in the database name. Avoid hyphens (-), as they may cause parsing errors.
* Ensure the `account_name` matches the name shown in the Firebolt Console URL, which is usually lowercase with no special characters.
* Use the exact engine name as shown in the Firebolt Workspace.
* Do not pass the database name as a query parameter. The SDK does not support `&database=` in the DSN.
### Common errors and solutions
| Error message | Likely cause | Solution |
| ----------------------------------- | ------------------------------------------------------------------ | ---------------------------------------------------------------------------- |
| `invalid connection string format` | URI format is invalid or it contains illegal characters (like `-`) | Double check the URI format and remove illegal characters. |
| `unknown parameter name database` | Attempted to pass `database` as a query parameter. | Move the database name into the URI path. |
| `error opening database connection` | Incorrect connection credentials. | Verify connection parameters values in the Firebolt UI and use exact values. |
## Additional Resources
* [Firebolt Go SDK GitHub Repository](https://github.com/firebolt-db/firebolt-go-sdk)
* [Firebolt Documentation: Connecting with Go](/guides/developing-with-firebolt/connecting-with-go)
# JDBC
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-jdbc
How to use the Firebolt JDBC driver
Firebolt's [type 4](https://en.wikipedia.org/wiki/JDBC_driver#Type_4_driver_%E2%80%93_Database-Protocol_driver/Thin_Driver\(Pure_Java_driver\)) JDBC driver lets Java applications connect to Firebolt. The JDBC driver is open-source software released under an Apache 2 license. You can browse, fork, download, and contribute to its development on [GitHub](https://github.com/firebolt-db/jdbc).
## Download the JAR file
The Firebolt JDBC driver is provided as a JAR file and requires [Java 11](https://java.com/en/download/manual.jsp) or later.
Download the driver from [GitHub JDBC releases](https://github.com/firebolt-db/jdbc/releases).
## Adding the Firebolt JDBC driver as a Maven dependency
To connect your project to Firebolt using [Apache Maven](https://maven.apache.org/), add the Firebolt JDBC driver as a dependency in your **pom.xml** configuration file. Link to the [Firebolt Maven repository](https://central.sonatype.com/artifact/io.firebolt/firebolt-jdbc), so that Maven can download and include the JDBC driver in your project, as shown in the following code example:
```xml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
io.firebolt
firebolt-jdbc
3.3.0
```
In the previous code example, replace `3.3.0` with the latest version available in the [Firebolt Maven Central repository](https://central.sonatype.com/artifact/io.firebolt/firebolt-jdbc).
## Adding the Firebolt JDBC driver as a Gradle dependency
If you are using the [Gradle Build Tool](https://gradle.org/), you can configure your Gradle project to use the Firebolt JDBC driver by specifying Apache's [Maven Central](https://maven.apache.org/repository/index.html) as a repository and adding the Firebolt JDBC driver as a dependency as follows:
```gradle theme={"theme":{"light":"css-variables","dark":"css-variables"}}
/* build.gradle */
repositories {
mavenCentral()
}
dependencies {
implementation 'io.firebolt:firebolt-jdbc:3.3.0'
}
```
In the previous code example, replace `3.3.0` with the latest version available in the [Firebolt Maven Central repository](https://central.sonatype.com/artifact/io.firebolt/firebolt-jdbc).
## Connecting to Firebolt with the JDBC driver
Provide connection details to the Firebolt JDBC driver using a connection string in the following format:
```
jdbc:firebolt:?
```
In the previous connection example, the following apply:
* `` - Specifies the name of the Firebolt database to connect to.
* `` - A list of connection parameters formatted as a standard [URL query string](https://en.wikipedia.org/wiki/Query_string#Structure).
## Authentication
To authenticate with managed Firebolt, use a [service account ID and secret](/managed-service/organization/service-accounts).
A service account, which is used for programmatic access to Firebolt, uses a `client_id` and a `client_secret` for identification.
To ensure compatibility with tools external to Firebolt, you can specify the service account's `client_id` as `user` and `client_secret` as `password`.
The following are examples of how to specify connection strings for authentication and configuration:
**Example**
The following example connection string configures the Firebolt JDBC driver to connect to `my_database` using a specified `client_id` and `secret_id` for authentication:
```
jdbc:firebolt:my_database?client_id=&client_secret=&account=my_account&engine=my_engine&buffer_size=1000000&connection_timeout_millis=10000
```
The previous example string also specifies an account name `my_account`, an engine name `my_engine`, a buffer size of `1000000` bytes, and a connection timeout of `10000` milliseconds, or `10` seconds.
**Example**
The following example provides `client_id` and `client_secret` as separate properties, rather than embedding them directly in the connection string, as shown in the previous example.
Connection string:
```
jdbc:firebolt:my_database?account=my_account&engine=my_engine&buffer_size=1000000&connection_timeout_millis=10000`
```
Connection properties:
```
client_id=
client_secret=
```
**Example**
The following example connects to `my_database` using only connection properties for authentication and parameters, without including any parameters directly in the string.
Connection string:
```
jdbc:firebolt:my_database
```
Connection properties:
```
client_id=
client_secret=
account=my_account
engine=my_engine
buffer_size=1000000
connection_timeout_millis=10000
```
**Example**
The following example is a minimal URL that connects to `my_database` using `client_id` and `client_secret` as connection properties for authentication, omitting the engine name and therefore connects to default engine and relying on default values for all other parameters:
Connection string:
```
jdbc:firebolt:my_database
```
Connection properties:
```
client_id=
client_secret=
account=my_account
```
Because the previous configuration example omits specifying the engine name, `my_database` connects to the default engine.
Since the connection string is a URI, make sure to [percent-encode](https://en.wikipedia.org/wiki/Percent-encoding) any reserved characters or special characters used in parameter keys or parameter values.
### Available connection parameters
The following table lists the available parameters that can be added to a Firebolt JDBC connection string. All parameter keys are case-sensitive.
| Parameter key | Data type | Default value | Range | Description |
| -------------------------------------- | --------- | ------------------------------------------------------ | ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `client_id` | TEXT | No default value. | | (**Required**) The Firebolt service account ID. |
| `client_secret` | TEXT | No default value. | | (**Required**) The secret generated for the Firebolt service account. |
| `account` | TEXT | No default value. | | (**Required**) Your Firebolt account name. |
| `database` | TEXT | No default value. | | The name of the database to connect to. Takes precedence over the database name provided as a path parameter. |
| `engine` | TEXT | The default engine attached to the specified database. | | The name of the engine to connect to. |
| `buffer_size` | INTEGER | `65536` | `1` to `2147483647` | The buffer size, in bytes, that the driver uses to read the responses from the Firebolt API. |
| `connection_timeout_millis` | INTEGER | `60000` | `0` to `2147483647` | The wait time in milliseconds before a connection to the server is considered failed. A timeout value of zero means that the connection will wait indefinitely. |
| `max_connections_total` | INTEGER | `300` | `1` to `2147483647` | The maximum total number of connections. |
| `socket_timeout_millis` | INTEGER | `0` | `0` to `2147483647` | The socket timeout, in milliseconds, which specifies the maximum wait time for data, defining the longest allowed inactivity between consecutive data packets. A value of zero means that there is no timeout limit. |
| `connection_keep_alive_timeout_millis` | INTEGER | `300000` | `1` to `2147483647` | Defines the duration to keep a server connection open in the connection pool before it is closed. |
| `ssl_mode` | TEXT | `strict` | `strict` or `none` | When set to `strict`, the SSL or TLS certificate is validated for accuracy and authenticity. If set to `none`, certificate verification is omitted. |
| `ssl_certificate_path` | TEXT | No default value. | | The absolute file path for the SSL root certificate. |
| `cache_connection` | BOOLEAN | `TRUE` | `TRUE` or `FALSE` | Keep this enabled for better performance when interacting with Firebolt. If you experience connection issues that might be related to stale cache set this to FALSE. Available only with JDBC driver version 3.6.1 and above. |
### System settings as connection parameters
In addition to the parameters specified in the previous table, any [system setting](/reference-sql/system-settings) can be passed as a connection string parameter. For example, to set a custom time zone, use the following format:
```
jdbc:firebolt:my_database?time_zone=UTC&
```
## Applying system settings using SET
In addition to passing system settings as connection string parameters, any [system setting](/reference-sql/system-settings) can be passed using the SQL `SET` command. Multiple `SET` statements can be run consecutively, separated by semicolons, as shown below:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SET time_zone = 'UTC';
SET standard_conforming_strings = false;
```
## Connection validation
The Firebolt JDBC driver validates the connection by sending a `SELECT 1` query on the connected engine URL. If this query fails, the driver throws an exception.
## Full reference documentation
The complete documentation for classes and methods in the Firebolt JDBC driver is available in the [Firebolt JDBC API reference guide](https://jdbc.docs.firebolt.io/javadoc/).
# .NET SDK
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-net-sdk
Learn about using the .NET SDK for Firebolt.
## Overview
The Firebolt .NET SDK is a software development kit designed to facilitate the integration of Firebolt's high-performance database capabilities into .NET applications. This SDK provides developers with the tools and interfaces needed to interact with Firebolt databases efficiently, enabling effective data manipulation and query execution.
## Installation
Install the Firebolt .NET SDK by adding the NuGet package to your project. You can do this in several ways:
### Via Package Manager Console
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
Install-Package FireboltNetSdk
```
### Via .NET CLI
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dotnet add package FireboltNetSdk
```
### Via PackageReference
Add the following line to your project file:
```xml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
```
Make sure to replace `x.x.x` with the specific version you want to use.
### Via **Visual Studio UI**
`Tools` > `NuGet Package Manager` > `Manage NuGet Packages for Solution` and search for `Firebolt`
For more details and versioning information, please visit the [NuGet Gallery](https://www.nuget.org/packages/FireboltNetSdk/).
## Quick Start
Here's a simple example to get started with the Firebolt .NET SDK:
```cs theme={"theme":{"light":"css-variables","dark":"css-variables"}}
using System.Data.Common;
using FireboltDotNetSdk.Client;
public class Program
{
public static async Task Main(string[] args)
{
// Name of your Firebolt account
string account = "my_firebolt_account";
// Client credentials, that you want to use to connect
string clientId = "my_client_id";
string clientSecret = "my_client_secret";
// Name of database and engine to connect to (Optional)
string database = "my_database_name";
string engine = "my_engine_name";
// Construct a connection string using defined parameter
string conn_string = $"account={account};clientid={clientId};clientsecret={clientSecret};database={database};engine={engine}";
// Create a new connection using generated connection string
using var conn = new FireboltConnection(conn_string);
// Open a connection
conn.Open();
// First you would need to create a command
var command = conn.CreateCommand();
// ... and set the SQL query
command.CommandText = "SELECT * FROM my_table";
// Execute a SQL query and get a DB reader
DbDataReader reader = command.ExecuteReader();
// Optionally you can check whether the result set has rows
Console.WriteLine($"Has rows: {reader.HasRows}");
// Close the connection after all operations are done
conn.Close();
}
}
```
## Documentation
For more detailed documentation, including API references and advanced usage, please refer to the [README](https://github.com/firebolt-db/firebolt-net-sdk/blob/main/README.md) file in the repository.
## Support
For support, issues, or contributions, please refer to the repository's issue tracker and contributing guidelines.
## License
This SDK is released under **Apache License 2.0**. Please see the [LICENSE](https://github.com/firebolt-db/firebolt-net-sdk/blob/main/LICENSE) file for more details.
# Node.js
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-nodejs
Learn about using the Node.js SDK for Firebolt.
## Overview
The Firebolt Node SDK is a software development kit designed to facilitate the integration of Firebolt's high-performance database capabilities into Node.js applications. This SDK provides a set of tools and interfaces for developers to interact with Firebolt databases, enabling efficient data manipulation and query execution. For more detailed documentation, including API references and advanced usage, refer to the [README](https://github.com/firebolt-db/firebolt-node-sdk/blob/main/README.md) file in the Firebolt Node SDK repository.
## Installation
To install the Firebolt Node SDK, run the following command in your project directory:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
npm install firebolt-sdk
```
## Authentication
After installation, you must authenticate before you can use the SDK to establish connections, run queries, and manage database resources. The following code example sets up a connection using your Firebolt [service account](/managed-service/organization/service-accounts) credentials:
```typescript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
const connection = await firebolt.connect({
auth: {
client_id: '12345678-90123-4567-8901-234567890123',
client_secret: 'secret',
},
engineName: 'engine_name',
account: 'account_name',
database: 'database',
});
```
In the previous code example, the following details apply:
* `client_id` and `client_secret`: These are your service account credentials. Refer to Firebolt's guide to learn how to [create a service account](/managed-service/organization/service-accounts#create-a-service-account) and obtain its [ID](/managed-service/organization/service-accounts#get-a-service-account-id) and [secret](/managed-service/organization/service-accounts#generate-a-secret).
* `engineName`: The name of the engine used to run your queries on.
* `database`: The target database where your tables will be stored.
* `account`: The object within your organization that encapsulates resources for storing, querying, and managing data. In the Node.js SDK, the [account](/managed-service/organizations-accounts#accounts) parameter specifies which organizational environment the connection will use.
## Quick start
In the following code example, credentials are stored in environment variables.
```javascript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import { Firebolt } from 'firebolt-sdk'
// Initialize client
const firebolt = Firebolt();
// Establish connection to Firebolt using environment variables for credentials and configuration
const connection = await firebolt.connect({
auth: {
client_id: process.env.FIREBOLT_CLIENT_ID,
client_secret: process.env.FIREBOLT_CLIENT_SECRET,
},
account: process.env.FIREBOLT_ACCOUNT,
database: process.env.FIREBOLT_DATABASE,
engineName: process.env.FIREBOLT_ENGINE_NAME
});
// Create a "users" table
await connection.execute(`
CREATE TABLE IF NOT EXISTS users (
id INT,
name STRING,
age INT
)
`);
// Insert sample data
await connection.execute(`
INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 30),
(2, 'Bob', 25)
`);
// Update rows
await connection.execute(`
UPDATE users SET age = 31 WHERE id = 1
`);
// Fetch data with a query
const statement = await connection.execute("SELECT * FROM users");
// Fetch the complete result set
const { data, meta } = await statement.fetchResult();
// Log metadata describing the columns of the result set
console.log(meta)
// Outputs:
// [
// Meta { type: 'int null', name: 'id' },
// Meta { type: 'text null', name: 'name' },
// Meta { type: 'int null', name: 'age' }
// ]
// Alternatively, stream the result set row by row
const { data } = await statement.streamResult();
data.on("metadata", metadata => {
console.log(metadata);
});
// Handle metadata event
data.on("error", error => {
console.log(error);
});
const rows = []
for await (const row of data) {
rows.push(row);
}
// Log the collected rows
console.log(rows)
// Outputs:
// [ [ 1, 'Alice', 31 ], [ 2, 'Bob', 25 ] ]
```
## Contribution
To receive support, report issues, or contribute, please refer to the Firebolt Node SDK repository [issue tracker](https://github.com/firebolt-db/firebolt-node-sdk/issues).
## License
This SDK is released under **Apache License 2.0**. See the [LICENSE](https://github.com/firebolt-db/firebolt-node-sdk/blob/main/LICENSE) file for more details.
# Python
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-python
Learn about using the Python SDK for Firebolt.
You can use the [Python SDK](https://github.com/firebolt-db/firebolt-python-sdk/) to work with Firebolt.
## Prerequisites
* Python >=3.10
* `firebolt-sdk` version 1.18.3 or higher
## Installation
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
pip install "firebolt-sdk>=1.18.3"
```
## Connect to managed Firebolt
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
from firebolt.db import connect
from firebolt.client.auth import ClientCredentials
with connect(
auth=ClientCredentials(client_id, client_secret),
account_name="your_account",
database="your_database",
engine_name="your_engine"
) as connection:
cursor = connection.cursor()
cursor.execute("SELECT 1")
```
## Further reading
* [Firebolt Python SDK documentation](https://python.docs.firebolt.io/sdk_documentation/latest/)
* The [firebolt-python-sdk repository on GitHub](https://github.com/firebolt-db/firebolt-python-sdk/)
* Code examples (in Jupyter notebooks) in the SDK repository that demonstrate common [data tasks](https://github.com/firebolt-db/firebolt-python-sdk/blob/main/examples/dbapi.ipynb) and [management tasks](https://github.com/firebolt-db/firebolt-python-sdk/blob/main/examples/management.ipynb)
# Rust
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-rust
Learn about using the Rust SDK for Firebolt.
## Overview
The Firebolt Rust SDK enables Rust developers to connect to and interact with Firebolt databases seamlessly. It provides an async-first interface with comprehensive type safety, OAuth2 authentication, and structured error handling for all Firebolt data types.
## Prerequisites
You must have the following prerequisites before you can connect your Firebolt account to Rust:
* **Rust installed and configured** on your system. The minimum supported version is 1.70 or higher. If you do not have Rust installed, you can download it from [rustup.rs](https://rustup.rs/).
* **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
* **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
* **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
* **Firebolt database and engine (optional)** – You can optionally connect to a Firebolt database and/or engine. If you do not have one yet, you can [create a database](/overview/quickstart#create-a-database) and also [create an engine](/overview/quickstart#create-an-engine). You would need a database if you want to access stored data in Firebolt and an engine if you want to load and query stored data.
## Installation
Add the Firebolt SDK to your `Cargo.toml` dependencies:
```toml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
[dependencies]
firebolt-sdk = ">=0.1.0"
tokio = { version = "1.0", features = ["full"] }
```
## Connection Parameters
The Rust SDK uses a builder pattern to configure connections to Firebolt. The SDK supports the following parameters:
* `client_id`: Client ID of your [service account](/managed-service/organization/service-accounts).
* `client_secret`: Client secret of your [service account](/managed-service/organization/service-accounts).
* `account_name`: The name of your Firebolt [account](/guides/managing-your-organization/managing-accounts).
* `database`: (Optional) The name of the [database](/security/rbac/database-permissions) to connect to.
* `engine`: (Optional) The name of the [engine](/security/rbac/engine-permissions) to run SQL queries on.
## Connect to Firebolt
To establish a connection to a Firebolt database, use the builder pattern with your credentials and database details. The following example shows how to connect to Firebolt:
```rust theme={"theme":{"light":"css-variables","dark":"css-variables"}}
use firebolt_sdk::FireboltClient;
#[tokio::main]
async fn main() -> Result<(), Box> {
// Replace with your Firebolt credentials and database details
let client_id = "your_client_id";
let client_secret = "your_client_secret";
let account_name = "your_account_name";
let database_name = "your_database_name"; // Optional parameter
let engine_name = "your_engine_name"; // Optional parameter
let mut client = FireboltClient::builder()
.with_credentials(client_id.to_string(), client_secret.to_string())
.with_account(account_name.to_string())
.with_database(database_name.to_string())
.with_engine(engine_name.to_string())
.build()
.await?;
println!("Connected to Firebolt successfully!");
Ok(())
}
```
## Run Queries
Once connected, you can execute SQL queries using the `query` method. The SDK returns results with type-safe parsing for all Firebolt data types. The following examples show you how to create a table, insert data, and retrieve data:
```rust theme={"theme":{"light":"css-variables","dark":"css-variables"}}
use firebolt_sdk::FireboltClient;
#[tokio::main]
async fn main() -> Result<(), Box> {
let mut client = FireboltClient::builder()
.with_credentials("your_client_id".to_string(), "your_client_secret".to_string())
.with_account("your_account_name".to_string())
.with_database("your_database_name".to_string())
.with_engine("your_engine_name".to_string())
.build()
.await?;
// Create a table
client.query("CREATE TABLE IF NOT EXISTS test_table (id INT, value TEXT)").await?;
// Insert data into the table
client.query("INSERT INTO test_table (id, value) VALUES (1, 'sample value')").await?;
// Query data from the table
let result = client.query("SELECT id, value FROM test_table").await?;
println!("Columns: {}", result.columns.len());
println!("Rows: {}", result.rows.len());
// Iterate over the result set
for row in &result.rows {
let id: i32 = row.get("id")?;
let value: String = row.get("value")?;
println!("Row: id={}, value={}", id, value);
}
Ok(())
}
```
## Type-Safe Result Parsing
The SDK provides comprehensive type conversion for all Firebolt data types. You can access column values by name or index with automatic type conversion:
```rust theme={"theme":{"light":"css-variables","dark":"css-variables"}}
use firebolt_sdk::FireboltClient;
use num_bigint::BigInt;
use rust_decimal::Decimal;
#[tokio::main]
async fn main() -> Result<(), Box> {
let mut client = FireboltClient::builder()
.with_credentials("your_client_id".to_string(), "your_client_secret".to_string())
.with_account("your_account_name".to_string())
.with_database("your_database_name".to_string())
.with_engine("your_engine_name".to_string())
.build()
.await?;
let result = client.query(r#"
SELECT
42 as int_col,
30000000000 as long_col,
3.14::float4 as float_col,
3.14159265359 as double_col,
'123.456'::decimal(10,3) as decimal_col,
'hello world' as text_col,
true as bool_col,
[1,2,3] as array_col,
NULL as nullable_col
"#).await?;
let row = &result.rows[0];
// Type-safe column access by name
let int_val: i32 = row.get("int_col")?;
let long_val: BigInt = row.get("long_col")?;
let float_val: f32 = row.get("float_col")?;
let double_val: f64 = row.get("double_col")?;
let decimal_val: Decimal = row.get("decimal_col")?;
let text_val: String = row.get("text_col")?;
let bool_val: bool = row.get("bool_col")?;
let array_val: serde_json::Value = row.get("array_col")?;
// For nullable types use Option
let nullable_val: Option = row.get("nullable_col")?;
// Access by index
let first_column: i32 = row.get(0)?;
let second_column: BigInt = row.get(1)?;
println!("Integer: {}", int_val);
println!("Long: {}", long_val);
println!("Float: {}", float_val);
println!("Double: {}", double_val);
println!("Decimal: {}", decimal_val);
println!("Text: {}", text_val);
println!("Boolean: {}", bool_val);
println!("Array: {}", array_val);
println!("First column by index: {}", first_column);
println!("Second column by index: {}", second_column);
// Handle nullable value
match nullable_val {
Some(value) => println!("Nullable Value: {}", value),
None => println!("Nullable Value is NULL"),
}
Ok(())
}
```
The Rust SDK does not currently support streaming queries for processing large result sets. All query results are loaded into memory at once. For large datasets, consider using LIMIT clauses or pagination techniques to manage memory usage.
## Error Handling
The SDK provides comprehensive error handling through the `FireboltError` enum:
```rust theme={"theme":{"light":"css-variables","dark":"css-variables"}}
use firebolt_sdk::{FireboltClient, FireboltError};
#[tokio::main]
async fn main() -> Result<(), Box> {
let mut client = FireboltClient::builder()
.with_credentials("your_client_id".to_string(), "your_client_secret".to_string())
.with_account("your_account_name".to_string())
.build()
.await?;
match client.query("SELECT * FROM non_existent_table").await {
Ok(result) => {
println!("Query succeeded with {} rows", result.rows.len());
}
Err(FireboltError::Query(msg)) => {
println!("Query error: {}", msg);
}
Err(FireboltError::Authentication(msg)) => {
println!("Authentication error: {}", msg);
}
Err(FireboltError::Network(msg)) => {
println!("Network error: {}", msg);
}
Err(FireboltError::Configuration(msg)) => {
println!("Configuration error: {}", msg);
}
Err(e) => {
println!("Other error: {}", e);
}
}
Ok(())
}
```
## Troubleshooting
When building a connection to Firebolt using the Rust SDK, follow these best practices to ensure correct configuration and avoid common errors:
**Guidelines**
* Ensure all required parameters (`client_id`, `client_secret`, `account_name`) are provided to the builder.
* Use the exact account name as shown in the Firebolt Console URL, which is usually lowercase with no special characters.
* Use the exact engine and database names as shown in the Firebolt Workspace.
* Verify your service account has the necessary permissions for the database and engine you're trying to access.
### Common errors and solutions
| Error message | Likely cause | Solution |
| --------------------------------------------------- | ------------------------------------- | --------------------------------------------------------------- |
| `Configuration error: client_id is required` | Missing required parameter in builder | Ensure all required parameters are provided to the builder |
| `Authentication error: Invalid credentials` | Incorrect client ID or secret | Verify your service account credentials in the Firebolt console |
| `Network error: Failed to get engine URL` | Network connectivity issues | Check your internet connection and firewall settings |
| `Query error: relation "table_name" does not exist` | Invalid SQL query or missing table | Verify your SQL query uses valid table and column names |
The Rust SDK does not currently support connecting to Firebolt Core. For Firebolt Core connections, use the [Go SDK](/guides/developing-with-firebolt/connecting-with-go) or other supported SDKs.
## Additional Resources
* [Firebolt Rust SDK GitHub Repository](https://github.com/firebolt-db/firebolt-rust-sdk)
* [Firebolt Documentation](https://docs.firebolt.io/)
* [Rust Documentation](https://doc.rust-lang.org/)
# SQLAlchemy
Source: https://docs.firebolt.io/guides/developing-with-firebolt/connecting-with-sqlalchemy
Learn about using the Firebolt adapter for the SQLAlchemy Python SQL toolkit.
SQLAlchemy is an open-source SQL toolkit and object-relational mapper for the Python programming language.
Firebolt’s adapter for SQLAlchemy acts as an interface for other supported third-party applications including Superset and Preset. When the SQLAlchemy adapter is successfully connected, these applications are able to communicate with Firebolt databases through the REST API.
The adapter is written in Python using the SQLAlchemy toolkit.
### Get started
Follow the guidelines for SQLAlchemy integration in the Firebolt-SQLAlchemy [Github repository](https://github.com/firebolt-db/firebolt-sqlalchemy/).
# Parametrized queries
Source: https://docs.firebolt.io/guides/developing-with-firebolt/parametrized-queries
Learn how to use parametrized queries across Firebolt SDKs and drivers to safely execute queries with dynamic values.
Parametrized queries allow you to write SQL statements with `$1`, `$2`, … placeholders instead of hard-coded values. The actual values are supplied separately at execution time and substituted on the server side. This approach provides two key benefits:
* **SQL injection protection** – Parameter values are validated and escaped by Firebolt before being applied to the query, preventing malicious input from altering query logic.
* **Code clarity** – Queries remain readable and reusable regardless of the values being substituted.
***
## .NET SDK
**Repository:** [firebolt-db/firebolt-net-sdk](https://github.com/firebolt-db/firebolt-net-sdk)
Add `preparedStatementParamStyle=FbNumeric` to your connection string to enable server-side parametrized queries, then use `$1`, `$2`, … as placeholders.
```plaintext theme={"theme":{"light":"css-variables","dark":"css-variables"}}
account=my_account;clientid=...;clientsecret=...;database=my_db;preparedStatementParamStyle=FbNumeric
```
```csharp theme={"theme":{"light":"css-variables","dark":"css-variables"}}
var command = (FireboltCommand)conn.CreateCommand();
command.CommandText = "SELECT * FROM my_table WHERE id = $1 AND name = $2";
command.Parameters.AddWithValue("$1", 123);
command.Parameters.AddWithValue("$2", "Alice");
command.Prepare();
using var reader = command.ExecuteReader();
```
**Supported types:** `bool`, `byte`, `short`, `int`, `long`, `float`, `double`, `decimal`, `string`, `Guid`, `DateTime`, `DateOnly`, `DateTimeOffset`, `TimeOnly`, `byte[]`, and `IList` (arrays).
For more details, see the [.NET SDK README](https://github.com/firebolt-db/firebolt-net-sdk/blob/main/README.md#server-side-prepared-statement-execution).
***
## Go SDK
**Repository:** [firebolt-db/firebolt-go-sdk](https://github.com/firebolt-db/firebolt-go-sdk)
Pass a context with the `FbNumeric` style enabled when preparing or executing statements. Use `$1`, `$2`, … as placeholders.
```go theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import (
"context"
"database/sql"
_ "github.com/firebolt-db/firebolt-go-sdk"
fireboltContext "github.com/firebolt-db/firebolt-go-sdk/context"
)
db, _ := sql.Open("firebolt", dsn)
serverSideCtx := fireboltContext.WithPreparedStatementsStyle(
context.Background(),
fireboltContext.PreparedStatementsStyleFbNumeric,
)
// With an explicit prepared statement
stmt, _ := db.PrepareContext(serverSideCtx, "INSERT INTO my_table VALUES ($1, $2)")
stmt.Exec(1, "value")
// Or directly without preparing first
db.ExecContext(serverSideCtx, "INSERT INTO my_table VALUES ($1, $2)", 2, "another value")
```
For more details, see the [Go SDK README](https://github.com/firebolt-db/firebolt-go-sdk/blob/main/README.md#prepared-statements).
***
## JDBC driver
**Repository:** [firebolt-db/jdbc](https://github.com/firebolt-db/jdbc)\
**Documentation:** [Connecting with JDBC](/guides/developing-with-firebolt/connecting-with-jdbc)
Add `preparedStatementParamStyle=FbNumeric` to your JDBC connection properties to enable server-side parametrized queries, then use `$1`, `$2`, … as placeholders.
```java theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import java.sql.*;
Properties props = new Properties();
props.setProperty("preparedStatementParamStyle", "FbNumeric");
Connection conn = DriverManager.getConnection(jdbcUrl, props);
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM my_table WHERE id = $1 AND name = $2"
);
stmt.setInt(1, 123);
stmt.setString(2, "Alice");
ResultSet rs = stmt.executeQuery();
```
**Batch execution** is also supported using `addBatch()` and `executeBatch()`.
**Supported types:** `boolean`, `byte`, `short`, `int`, `long`, `float`, `double`, `BigDecimal`, `String`, `Date`, `Timestamp`, `byte[]`, and `Array`.
For more details, see the [JDBC driver documentation](/guides/developing-with-firebolt/connecting-with-jdbc).
***
## Node.js SDK
**Repository:** [firebolt-db/firebolt-node-sdk](https://github.com/firebolt-db/firebolt-node-sdk)\
**Documentation:** [Connecting with Node.js](/guides/developing-with-firebolt/connecting-with-nodejs)
Set `preparedStatementParamStyle: 'fb_numeric'` in the connection options to enable server-side parametrized queries, then use `$1`, `$2`, … as placeholders.
```typescript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
const connection = await firebolt.connect({
auth: { client_id: "...", client_secret: "..." },
account: "my_account",
database: "my_database",
engineName: "my_engine",
preparedStatementParamStyle: "fb_numeric",
});
const statement = await connection.execute(
"SELECT * FROM my_table WHERE id = $1 AND name = $2",
{ parameters: [123, "Alice"] }
);
```
You can also reference parameters by name using `namedParameters`:
```typescript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
const statement = await connection.execute(
"SELECT * FROM my_table WHERE id = $1 AND name = $2",
{ namedParameters: { $1: 123, $2: "Alice" } }
);
```
For more details, see the [Node.js SDK README](https://github.com/firebolt-db/firebolt-node-sdk/blob/main/README.md#server-side-prepared-statement).
***
## REST API
When calling the Firebolt query API directly (without an SDK), pass `query_parameters` as a URL query string parameter containing a JSON array that maps each `$number` placeholder to its value.
**Format:**
```json theme={"theme":{"light":"css-variables","dark":"css-variables"}}
[
{ "name": "$1", "value": },
{ "name": "$2", "value": }
]
```
**Example:**
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl --location \
'https://?database=my_db&query_parameters=[{"name":"$1","value":123},{"name":"$2","value":"Alice"}]' \
--header 'Authorization: Bearer ' \
--data 'SELECT * FROM my_table WHERE id = $1 AND name = $2'
```
The `query_parameters` value must be URL-encoded when passed as a query string. The example above shows it unencoded for readability.
***
## Summary
| SDK / Driver | Placeholder syntax | How to enable |
| --------------------------------------------------------------- | ------------------ | ------------------------------------------------------------------------------------ |
| [REST API](/guides/developing-with-firebolt/using-the-api) | `$1`, `$2`, … | `query_parameters` URL query string parameter |
| [.NET SDK](https://github.com/firebolt-db/firebolt-net-sdk) | `$1`, `$2`, … | Connection string: `preparedStatementParamStyle=FbNumeric` |
| [Go SDK](https://github.com/firebolt-db/firebolt-go-sdk) | `$1`, `$2`, … | `fireboltContext.WithPreparedStatementsStyle(..., PreparedStatementsStyleFbNumeric)` |
| [JDBC driver](https://github.com/firebolt-db/jdbc) | `$1`, `$2`, … | Connection property: `preparedStatementParamStyle=FbNumeric` |
| [Node.js SDK](https://github.com/firebolt-db/firebolt-node-sdk) | `$1`, `$2`, … | Connection option: `preparedStatementParamStyle: 'fb_numeric'` |
# REST API
Source: https://docs.firebolt.io/guides/developing-with-firebolt/using-the-api
Learn about using the Firebolt API to interact with Firebolt.
Use the Firebolt REST API to execute queries on engines programmatically. Learn how to use the API, including authentication, working with engines and executing queries. A service account is required to access the API. Learn about [managing programmatic access to Firebolt](/managed-service/organization/service-accounts).
## Create a service account and associate it with a user
Create a service account with organization administrator privilege,
i.e., the service account property\_is\_organization\_admin\_ must be *true*.
Next, create a user with role privileges you would like to have the service account
and associate the service account with the user.
## Use tokens for authentication
To authenticate Firebolt using the service accounts with the properties
as described above via Firebolt’s REST API, send the following request
to receive an authentication token:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl -X POST --location 'https://id.app.firebolt.io/oauth/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'audience=https://api.firebolt.io' \
--data-urlencode "client_id=${service_account_id}" \
--data-urlencode "client_secret=${service_account_secret}"
```
where:
| Property | Data type | Description |
| :------------- | :-------- | :------------------------------------------------------------------------------------------------- |
| client\_id | TEXT | The service [account ID](/managed-service/organization/service-accounts#get-a-service-account-id). |
| client\_secret | TEXT | The service [account secret](/managed-service/organization/service-accounts#generate-a-secret). |
**Response**
```json theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{
"access_token":"access_token_value",
"token_type":"Bearer",
"expires_in":86400
}
```
In the previous example response, the following apply:
* The `access_token` is a unique token that authorizes your API requests that acts as a temporary key to access resources or perform actions. You can use this token to authenticate with Firebolt’s platform until it expires.
* The `token_type` is `Bearer`, which means that the access token must be included in an authorization header of your API requests using the format: `Authorization: Bearer `.
* The token `expires_in` indicates the number of seconds until the token expires.
Use the returned access\_token to authenticate with Firebolt.
To run a query using the API, you must first obtain the URL of the engine you want to run on.
## Get the account gateway URL
Use the following endpoint to return the account gateway URL for ``.
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl https://api.app.firebolt.io/web/v3/account//engineUrl \
-H 'Accept: application/json' \
-H 'Authorization: Bearer '
```
**Example:** `https://api.app.firebolt.io/web/v3/account/my-account/engineUrl`
**Response**
```json theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{
"engineUrl":".api.us-east-1.app.firebolt.io"
}
```
You can use this URL for metadata queries (for example, looking up engine URLs in `information_schema.engines`) and for DDL that does not target a specific user engine.
## Get a user engine URL
Get a user engine URL by running the following query against the `information_schema.engines` table:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT url
FROM information_schema.engines
WHERE engine_name=''
```
You can run the query using the account gateway URL with the following request:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl --location 'https:///query' \
--header 'Authorization: Bearer ' \
--data 'SELECT url FROM information_schema.engines WHERE engine_name='\''my_engine'\'''
```
## Execute a query on a user engine
Use the following endpoint to run a query on a user engine:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl --location 'https://&database=' \
--header 'Authorization: Bearer ' \
--data ''
```
where:
| Property | Data type | Description |
| :-------------- | :-------- | :------------------------------------------------------------- |
| user engine URL | TEXT | The user engine URL ([retrieved here](#get-a-user-engine-url)) |
| database name | TEXT | The database to run the query |
| SQL query | TEXT | Any valid SQL query |
Queries are per request. To run multiple statement queries, separate queries each into one request.
## API limits
### Request Size Limits
By default, Firebolt enforces a request size limit of 2 MiB per query on user engines. If you exceed this limit, you will get an error like:
```
413: Request body is larger than configured limit of 20971520 bytes. Please contact support if you need to send larger queries to support your workload
```
This limit is configurable, so please contact our support team if it is insufficient for your use-case.
**Note**: overriding this limit will disable the following features for large requests:
* Requests above 2 MiB will not be retried internally after transient network errors.
* Requests above 2 MiB will not be included in online upgrade verification.
# Export data
Source: https://docs.firebolt.io/guides/exporting-data
Export query results from Firebolt to Amazon S3 using COPY TO.
You can export data from a `SELECT` query directly to an Amazon S3 location using [COPY TO](/reference-sql/commands/data-management/copy-to). This method is more flexible and efficient than downloading query results manually from the **Firebolt Workspace**, making it ideal for data sharing, integration, and archival.
## How to export data
The following code example uses `COPY TO` to export the result of a `SELECT` query from `my_table` to a specified Amazon S3 bucket in CSV format using the provided [AWS credentials](/reference-sql/commands/data-management/copy-to#credentials):
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
SELECT column1, column2 FROM my_table WHERE condition
)
TO 's3://your-bucket/path/'
WITH (FORMAT = 'CSV')
CREDENTIALS = ('aws_key_id'='your-key' 'aws_secret_key'='your-secret');
```
## Choose the right export format
| Format | Best For | Characteristics | Recommended Use |
| ------------------------- | ----------------------------------------- | --------------------------------------------------- | ------------------------------------------------------------------------- |
| **CSV (Comma-Separated)** | General data exchange, spreadsheets, SQL. | Simple, widely supported, and easy to read. | Best for spreadsheets, databases, or general data exchange. |
| **TSV (Tab-Separated)** | Structured text data. | Like CSV, but uses tabs instead of commas. | Best for Excel, databases, or general data exchange. |
| **JSON** | APIs, web applications, NoSQL databases. | Flexible, human-readable, and supports nested data. | Best for web apps, APIs, or NoSQL integrations. |
| **PARQUET** | Big data processing, analytics workloads. | Compressed, columnar, and optimized for querying. | Ideal for analytics, performance-sensitive workloads, and large datasets. |
## Examples
**Export data in CSV format**
Use CSV when you need a simple, widely supported format for spreadsheets, relational databases, or data exchange.
The following code example exports `user_id`, `event_type`, and `timestamp` data and headers from the `user_events` table to a CSV file in an Amazon S3 bucket:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (SELECT user_id, event_type, timestamp FROM user_events)
TO 's3://my-export-bucket/user_events.csv'
WITH (FORMAT = 'CSV', HEADER = TRUE)
CREDENTIALS = ('aws_key_id'='your-key' 'aws_secret_key'='your-secret');
```
**Export data in Parquet format**
Parquet is best for big data workloads, as it offers compressed, columnar storage optimized for analytics and query performance.
The following code example exports all data from the `sales_data` table to an Amazon S3 bucket in Parquet format using the provided AWS credentials:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (SELECT * FROM sales_data)
TO 's3://my-export-bucket/sales_data.parquet'
WITH (FORMAT = 'PARQUET')
CREDENTIALS = ('aws_key_id'='your-key' 'aws_secret_key'='your-secret');
```
**Export data in JSON format**
JSON is ideal for APIs, web applications, and NoSQL databases, as it supports nested and flexible data structures.
The following code example exports `order_id` and `order_details` from the `orders` table to an Amazon S3 bucket in JSON format using the provided AWS credentials:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (SELECT order_id, order_details FROM orders)
TO 's3://my-export-bucket/orders.json'
WITH (FORMAT = 'JSON')
CREDENTIALS = ('aws_key_id'='your-key' 'aws_secret_key'='your-secret');
```
**Export data in TSV format**
TSV is similar to CSV but uses tab delimiters, making it useful for structured text data that may contain commas.
The following code example exports `name`, `age`, and `city` from the `customers` table to an Amazon S3 bucket in TSV format using the provided AWS credentials:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (SELECT name, age, city FROM customers)
TO 's3://my-export-bucket/customers.tsv'
WITH (FORMAT = 'TSV')
CREDENTIALS = ('aws_key_id'='your-key' 'aws_secret_key'='your-secret');
```
## Additional Considerations
**Performance tips**
* Export only required columns and use filters to reduce data volume
* Ensure proper permissions are set on your S3 bucket
**Security and credentials**
* Always use **secure AWS credentials**.
* Use **IAM roles** instead setting credentials directly in the code for better security.
## Next Steps
For more information about advanced options including **compression**, **partitioning**, and **null handling**, see [COPY TO](/reference-sql/commands/data-management/copy-to).
# Query DuckLake tables with Firebolt
Source: https://docs.firebolt.io/guides/iceberg-and-data-lake/ducklake
Set up a DuckLake catalog on PostgreSQL with DuckDB, then read its Parquet data from Firebolt using READ_DUCKLAKE and LIST_DUCKLAKE_FILES.
DuckLake support is **experimental** and may change. Only DuckLake catalogs hosted on **PostgreSQL** are supported.
This guide walks you through setting up a [DuckLake](https://ducklake.select/) catalog on PostgreSQL with Parquet data files on local disk, and then reading that data from Firebolt. You'll create a table with DuckDB and query it from Firebolt using [`READ_DUCKLAKE`](/reference-sql/functions-reference/ducklake/read_ducklake) and [`LIST_DUCKLAKE_FILES`](/reference-sql/functions-reference/ducklake/list_ducklake_files).
## Prerequisites
* Firebolt
* PostgreSQL — this guide runs it as a Docker container
* [DuckDB](https://duckdb.org/) — tested with v1.5.2
## Step 1: Start PostgreSQL
DuckLake stores its catalog metadata in a SQL database. Start a PostgreSQL container, setting the user, password, and database name:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
docker rm -f ducklake-postgres
docker run -d \
--name ducklake-postgres \
-e POSTGRES_USER=dl_user \
-e POSTGRES_PASSWORD=dl_pw \
-e POSTGRES_DB=dl_db \
-p 5432:5432 \
postgres:16
```
## Step 2: Create a DuckLake table with DuckDB
1. Install DuckDB and start the DuckDB shell:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
curl https://install.duckdb.org | sh
duckdb
```
The remaining commands in this step run inside the DuckDB shell.
2. Install and load the DuckLake extension:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
INSTALL ducklake;
LOAD ducklake;
```
3. Create the DuckLake catalog in PostgreSQL and attach it. Use the same credentials you set for the PostgreSQL container. The `DATA_PATH` option determines where the Parquet data files are written on local disk:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ATTACH 'ducklake:postgres:host=localhost port=5432 user=dl_user password=dl_pw dbname=dl_db'
AS pg_ducklake (DATA_PATH '/tmp/ducklake/', OVERRIDE_DATA_PATH true);
```
4. Create a table and insert some data:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
-- Use the DuckLake catalog
USE pg_ducklake;
-- Create a table in the DuckLake catalog
CREATE TABLE my_first_ducklake_table (a INT, r FLOAT);
-- Insert sample data
INSERT INTO my_first_ducklake_table SELECT x, random() FROM generate_series(1, 10000) g(x);
```
5. Confirm the table exists, with its Parquet files written to local disk:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
.timer on -- optional: show query latency
SELECT * FROM my_first_ducklake_table ORDER BY r DESC LIMIT 5;
```
## Step 3: Start Firebolt
Start the Firebolt container. Two options matter for DuckLake:
* `--network host` lets the Firebolt binary inside the container reach the PostgreSQL container.
* `-v /tmp/ducklake:/tmp/ducklake` mounts the local directory where DuckDB wrote the Parquet files into the Firebolt container at the same path.
The `--ulimit memlock` and `--security-opt seccomp=unconfined` flags let Firebolt use `io_uring`. Without them Firebolt still starts, but queries that need `io_uring`, including queries that spill to disk, fail. See [`execution.io_uring`](/self-managed/engine-configuration#param-execution-io-uring).
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
docker run -it \
--name firebolt \
--rm \
-e FIREBOLT_CORE_MODE=1 \
--ulimit memlock=8589934592:8589934592 \
--security-opt seccomp=unconfined \
--network host \
-v /tmp/ducklake:/tmp/ducklake \
ghcr.io/firebolt-db/engine:dev
```
## Step 4: Query the DuckLake table from Firebolt
The container from Step 3 runs in the foreground, so open a **new terminal** to connect to Firebolt. Any [supported client](/self-managed/connecting-over-http) works — connect to the query endpoint on port `3473`, then run the SQL below.
1. Create a location object that points to the DuckLake catalog. Use the same credentials you set for the PostgreSQL container. Because the data files are on local disk, no endpoint or storage credentials are needed:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION my_ducklake_loc WITH
SOURCE = DUCKLAKE
CATALOG = 'postgresql://dl_user:dl_pw@127.0.0.1:5432/dl_db';
```
For the full syntax, see [CREATE LOCATION (DuckLake)](/reference-sql/commands/data-definition/create-location-ducklake).
2. List the Parquet files that make up your table:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT *
FROM LIST_DUCKLAKE_FILES(
LOCATION => 'my_ducklake_loc',
SCHEMA => 'main', -- optional: 'main' is the default
TABLE => 'my_first_ducklake_table'
);
```
3. Read the data, the same query you ran in DuckDB:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT *
FROM READ_DUCKLAKE(
LOCATION => 'my_ducklake_loc',
TABLE => 'my_first_ducklake_table'
)
ORDER BY r DESC
LIMIT 5;
```
4. Inspect the query plan to see how caching behaves across repeated runs. Run it twice and compare the two plans, the second run reads cached metadata and data:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
EXPLAIN (ANALYZE)
SELECT *
FROM READ_DUCKLAKE(
LOCATION => 'my_ducklake_loc',
TABLE => 'my_first_ducklake_table'
)
ORDER BY r DESC
LIMIT 5;
```
## Next steps
* [READ\_DUCKLAKE](/reference-sql/functions-reference/ducklake/read_ducklake) — Full reference, including reading from S3-compatible object storage, pinning a snapshot, and the supported data types.
* [LIST\_DUCKLAKE\_FILES](/reference-sql/functions-reference/ducklake/list_ducklake_files) — Inspect a table's data files and per-file statistics.
* [CREATE LOCATION (DuckLake)](/reference-sql/commands/data-definition/create-location-ducklake) — Store catalog connection details and credentials in a reusable location object.
# Iceberg
Source: https://docs.firebolt.io/guides/iceberg-and-data-lake/iceberg
How to query, tune, and export Apache Iceberg tables in Firebolt
Firebolt reads [Apache Iceberg](https://iceberg.apache.org/) tables natively, and can export query results into a new Iceberg table so your data stays open to other query engines.
## Quickstart
A [`LOCATION`](/reference-sql/commands/data-definition/create-location-iceberg) stores an Iceberg catalog's or table's connection and credentials once, so you don't repeat them in every query. Mounting a whole catalog as a database is the quickest way to make its tables queryable.
### Attach a catalog as a database
Mount an external Iceberg catalog with [`CREATE ICEBERG DATABASE`](/reference-sql/commands/data-definition/create-iceberg-database) so every table in it is queryable by name, with no per-table setup. The database holds only a pointer to the [`LOCATION`](/reference-sql/commands/data-definition/create-location-iceberg) and an optional freshness setting, so tables added to the catalog become visible on the next query:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION my_catalog WITH
SOURCE = ICEBERG
CATALOG = REST
CATALOG_OPTIONS = ( URL = 'https://catalog.example.com/v1' WAREHOUSE = 'analytics' )
CREDENTIALS = (
OAUTH_CLIENT_ID = ''
OAUTH_CLIENT_SECRET = ''
);
CREATE ICEBERG DATABASE lake WITH
LOCATION = 'my_catalog'
MAX_STALENESS = '30 seconds';
-- Equivalent to: SELECT * FROM READ_ICEBERG(LOCATION => 'my_catalog', NAMESPACE => 'sales', TABLE => 'orders') LIMIT 10;
SELECT * FROM lake.sales.orders LIMIT 10;
```
Supported catalog types are `FILE_BASED`, `REST`, `SNOWFLAKE_OPEN_CATALOG`, `DATABRICKS_UNITY`, `AWS_GLUE`, and `S3_TABLES` Nightly Feature. For details and limitations, see the [`CREATE ICEBERG DATABASE`](/reference-sql/commands/data-definition/create-iceberg-database) reference.
### Register a single table
To expose one table instead of a whole catalog, point a [`LOCATION`](/reference-sql/commands/data-definition/create-location-iceberg) at that table and register it with [`CREATE ICEBERG TABLE`](/reference-sql/commands/data-definition/create-iceberg-table). Firebolt infers the schema from the Iceberg metadata, and you can then query the table like any managed table:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE LOCATION my_table_location WITH
SOURCE = ICEBERG
CATALOG = FILE_BASED
CATALOG_OPTIONS = ( URL = 's3://my-bucket/path/to/iceberg/table' )
CREDENTIALS = ( AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/IcebergAccess' AWS_ROLE_EXTERNAL_ID = 'my-external-id' );
CREATE ICEBERG TABLE lineitem LOCATION = 'my_table_location';
-- Equivalent to: SELECT * FROM READ_ICEBERG(LOCATION => 'my_table_location') LIMIT 10;
SELECT * FROM lineitem LIMIT 10;
```
For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account's unique IAM principal. Configuring one is a recommended best practice. See [IAM roles](/security#iam-roles).
### Query a table ad hoc
For a one-off read with no setup, use the [`READ_ICEBERG`](/reference-sql/functions-reference/iceberg/read_iceberg) table-valued function:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT * FROM READ_ICEBERG(URL => 's3://my-bucket/path/to/iceberg/table') LIMIT 10;
```
To inspect the underlying data and delete files of an Iceberg table without reading its rows, use the [`LIST_ICEBERG_FILES`](/reference-sql/functions-reference/iceberg/list_iceberg_files) TVF:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT * FROM LIST_ICEBERG_FILES(URL => 's3://my-bucket/path/to/iceberg/table') LIMIT 10;
```
### Import / Export
You can also ingest Iceberg data into Firebolt-managed storage using any of these methods or the [COPY FROM statement](/reference-sql/commands/data-management/copy-from). Managed tables are built for low-latency, high-concurrency real-time analytics and usually offer better price/performance, because they support additional index types and compaction; see [Storage and indexing](/performance-and-observability/storage-and-indexing). To go the other way, export query results from Firebolt into a new Iceberg table using [`CREATE ICEBERG TABLE AS SELECT`](/reference-sql/commands/data-definition/create-iceberg-table-as-select).
For the full set of Iceberg functions, including the partition transform functions used in `PARTITION BY` clauses, see the [Iceberg functions reference](/reference-sql/functions-reference/iceberg).
## Best practices
* **Register tables you query often.** Use [`CREATE ICEBERG TABLE`](/reference-sql/commands/data-definition/create-iceberg-table) to register a table in Firebolt's catalog so you can query it with regular `SELECT` statements, or [`CREATE ICEBERG DATABASE`](/reference-sql/commands/data-definition/create-iceberg-database) to mount an entire catalog and query its tables by name. Reserve [`READ_ICEBERG`](/reference-sql/functions-reference/iceberg/read_iceberg) for ad hoc reads of tables you do not want to register.
* **Store credentials in a `LOCATION` object.** A [`LOCATION`](/reference-sql/commands/data-definition/create-location-iceberg) centralizes credential management and avoids specifying individual credentials in each query.
* **Set `MAX_STALENESS` for tables that tolerate slightly stale reads.** This caches catalog metadata and vended credentials and typically cuts query latency by tens to hundreds of milliseconds. See [Configurable data freshness with `MAX_STALENESS`](/performance-and-observability/runtime/iceberg-performance#configurable-data-freshness-with-max_staleness).
* **Require a partition filter if you have large tables you always query by partition.** Setting [`require_iceberg_partition_filter`](/reference-sql/system-settings#requiring-a-partition-filter-on-iceberg-scans) to `TRUE` rejects a query that derives no partition pruning for such a table, so a missing or ineffective predicate fails fast; a query meant to read the table in full can override the check, see [Requiring a partition filter](/performance-and-observability/runtime/iceberg-performance#requiring-a-partition-filter).
## Supported features and limitations
At a glance:
| Capability | Support |
| :-------------------- | :---------------------------------------------------------------------------------------------------------------------------------- |
| Catalogs | `FILE_BASED`, `REST`, `SNOWFLAKE_OPEN_CATALOG`, `DATABRICKS_UNITY`, `AWS_GLUE`, `S3_TABLES`, Snowflake Horizon Catalog (via `REST`) |
| Data files | Apache Parquet on Amazon S3 |
| Spec versions | Iceberg v1 and v2 |
| Writes | Export only, via [`CREATE ICEBERG TABLE AS SELECT`](/reference-sql/commands/data-definition/create-iceberg-table-as-select); no DML |
| Positional deletes | Supported |
| Equality deletes | Supported, except on dropped columns and `REAL` or `DOUBLE PRECISION` columns |
| Deletion vectors (v3) | Not supported |
| Schema evolution | Supported, except type promotion and non-null `initial-default` |
| Partition evolution | Supported |
| Time travel | Not supported |
A few details and exceptions apply on top of the table above:
* See [`CREATE LOCATION (Iceberg)`](/reference-sql/commands/data-definition/create-location-iceberg) for the parameters and credentials each catalog type takes.
* Cross-region reads from S3 are disabled by default because they can incur additional cost. Enable them per query with the [`cross_region_request_mode`](/reference-sql/system-settings#access-cross-region-data) setting.
* When a partitioned table contains equality delete files, all data and equality delete files must be written under the table's current partition spec.
* The data types `variant`, `geometry`, and `geography` are not supported.
* Nested complex types (`struct`, `list`, `map`) nested inside another complex type are read as nullable even when Iceberg defines the field as non-nullable.
* [Returning partition values for identity transforms from partition metadata](https://iceberg.apache.org/spec/#column-projection) is not supported.
## Performance
Choosing Iceberg over managed tables is a performance trade-off, and Firebolt accelerates Iceberg queries with caching, pruning, co-located joins, and writer tuning. For when to choose Iceberg over Firebolt-managed tables and the full tuning guidance, see the [Iceberg performance guide](/performance-and-observability/runtime/iceberg-performance).
# Integrate with Firebolt
Source: https://docs.firebolt.io/guides/integrations
Connect Firebolt to your favorite tools for data ingestion, transformation, visualization, and AI workflows.
## Data Ingestion & Integration
* [Airbyte](/guides/integrations/airbyte) – Open-source data integration platform for loading data into Firebolt.
* [Airflow](/guides/integrations/airflow) – Orchestrate data pipelines with the Apache Airflow provider package.
* [AWS Glue](/guides/integrations/aws-glue) – Build data pipelines using AWS Glue with the Firebolt JDBC driver.
* [Estuary](/guides/integrations/estuary) – Real-time data integration using Estuary Flow.
* [Kafka Sink Connector](/guides/integrations/kafka-sink-connector) – Move data from Apache Kafka to Firebolt using Kafka Connect.
* [Wirekite](/guides/integrations/wirekite) – High-throughput bulk migration and continuous CDC from OLTP databases to Firebolt.
## Transformation & Modeling
* [dbt - Firebolt adapter (core only)](/guides/integrations/dbt-firebolt-adaptor) – Connect dbt Core to Firebolt using the native Firebolt adapter.
* [dbt - PostgreSQL adapter (cloud and core)](/guides/integrations/dbt-postgres-adaptor) – Connect dbt Cloud or dbt Core to Firebolt using the PostgreSQL protocol.
## Business Intelligence (BI) & Visualization
* [Apache Superset](/guides/integrations/connecting-to-apache-superset) – Connect Apache Superset to Firebolt for interactive data exploration.
* [DataBrain](/guides/integrations/connecting-to-databrain) – Embed interactive dashboards and self-service BI powered by Firebolt.
* [AWS QuickSight](/guides/integrations/quicksight) – Connect AWS QuickSight to Firebolt via the PostgreSQL interface.
* [Embeddable](/guides/integrations/embeddable) – Build and embed custom analytics in your product powered by Firebolt.
* [Lightdash](/guides/integrations/lightdash) – Connect Lightdash to Firebolt for dbt-powered analytics.
* [Looker Cloud](/guides/integrations/looker-cloud) – Connect Looker Cloud to Firebolt using the PostgreSQL dialect.
* [Looker On-Prem](/guides/integrations/looker-on-prem) – Connect self-hosted Looker to Firebolt using the PostgreSQL dialect.
* [Metabase Cloud](/guides/integrations/metabase-cloud) – Connect Metabase Cloud to Firebolt.
* [Metabase On-Prem](/guides/integrations/metabase-on-prem) – Connect a self-hosted Metabase instance to Firebolt.
* [Omni](/guides/integrations/omni) – Connect Omni to Firebolt using the PostgreSQL protocol.
* [Power BI](/guides/integrations/power-bi) – Connect Power BI to Firebolt for reporting and dashboards.
* [Preset](/guides/integrations/connecting-to-preset) – Connect Preset to Firebolt.
* [Tableau](/guides/integrations/tableau) – Connect Tableau to Firebolt for data visualization.
* [ThoughtSpot](/guides/integrations/thoughtspot) – Connect ThoughtSpot to Firebolt for AI-driven analytics.
## Data Science & Advanced Analytics
* [Pandas](/guides/integrations/pandas) – Analyze data in Firebolt using Pandas DataFrames.
* [Cube.js](/guides/integrations/cube-js) – Build analytics APIs on top of Firebolt with Cube.js.
* [Hex](/guides/integrations/hex) – Connect Hex to Firebolt using the PostgreSQL protocol.
* [Paradime](/guides/integrations/connecting-to-paradime) – Connect Paradime to Firebolt.
## AI & LLM Tools
* [Dot](/guides/integrations/dot) – AI Data Analyst that lets your team query Firebolt using natural language in Slack, Teams, or the web.
* [LangChain](/guides/integrations/langchain) – Use Firebolt as a data source in LangChain workflows.
* [MCP Server](/guides/integrations/mcp) – Enable AI-powered workflows with LLMs using the Firebolt MCP Server.
* [TextQL](/guides/integrations/textql) – Query and explore Firebolt data using natural language powered by AI.
## Developer Tools & Observability
* [DBeaver](/guides/integrations/dbeaver) – Connect DBeaver to Firebolt using the JDBC driver.
* [OpenTelemetry Exporter](/guides/integrations/otel-exporter) – Export Firebolt telemetry data using OpenTelemetry.
# Airbyte
Source: https://docs.firebolt.io/guides/integrations/airbyte
Connecting Airbyte and Firebolt.
![Airbyte icon]()
Airbyte is an open-source data integration platform that significantly simplifies the ETL (Extract, Transform, Load) process, making it easier for users to manage and migrate their data across various sources. By providing a user-friendly interface and robust functionality, Airbyte enables seamless data movement and transformation, catering to a wide range of data integration needs. One of the key features of Airbyte is its extensive range of connectors, which allow it to integrate with numerous data sources and destinations.
Using Airbyte's Firebolt connector, users can efficiently and effortlessly load large amounts of data to and from Firebolt. This capability extends to integration with a wide array of data sources, thanks to Airbyte's extensive library of connectors. Whether your data resides in cloud storage, on-premises databases, SaaS applications, or other data warehouses, Airbyte facilitates smooth and reliable data transfer between these sources and Firebolt.
## Quickstart
There are several [ways](https://docs.airbyte.com/platform/deploying-airbyte) to deploy Airbyte. In this tutorial we will the easiest way to start prototyping by using a [Docker Compose](https://docs.docker.com/compose/) deployment locally.
If you already have an airbyte deployment skip to the [configuration section](#step-2-configure-firebolt-connection-via-ui).
#### Prerequisites
1. **Docker**: Ensure you have Docker installed. You can download it from [here](https://www.docker.com/products/docker-desktop).
2. **Firebolt Account**: You need an active Firebolt account. Sign up [here](https://www.firebolt.io/) if you don’t have one.
3. **Firebolt Database and Table**: Make sure you have a Firebolt database and table with data ready for querying.
4. **Firebolt Service Account**: Create a [service account](/managed-service/organization/service-accounts) in Firebolt and note its id and secret.
#### Step 1: Deploy Airbyte Locally with Docker
1. Create a new directory for your Airbyte setup:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
git clone --depth=1 https://github.com/airbytehq/airbyte.git
```
2. Switch to the Airbyte directory:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
cd airbyte
```
3. Start Airbyte by running the following command in the terminal:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
./run-ab-platform.sh
```
4. Open your browser and navigate to `http://localhost:8000` to access the Airbyte UI.
5. You will be asked for a username and password. By default the username is `airbyte` and the password is `password`. Before you deploy Airbyte in production make sure to change the password.
#### Step 2: Configure Firebolt Connection via UI
1. In the Airbyte UI, click on the **"Connections"** tab and select **"Create your first connection"**.
2. Click on **"New Destination"** and select **"Firebolt"** as the destination type.
3. Enter your Firebolt connection details:
* Client ID: Your service account id.
* Client Secret: Your service account secret.
* Database: Your database name.
* Account: Your firebolt [account](/guides/managing-your-organization/managing-accounts).
* Engine: Firebolt engine which will run the ingestion.
* Host (Optional): For non-standard use cases. Should be left blank.
4. Select replication strategy. SQL is easier to setup but S3 is more performant on production loads. See the [Airbyte doc](https://docs.airbyte.com/integrations/destinations/firebolt) for more information.
5. Save.
![Connection parameters]()
#### Step 3: Create a Connection in Airbyte
1. In the Airbyte UI, click on the **"Connections"** tab and select **"Create your first connection"** (**"New Connection"** if you already have a connection defined).
2. Choose a source from which you want to extract data. We'll be using **Faker** to generate some sample data.
3. Leave fields as is and click **"Set up source"**.
4. Next in the destination screen select the Firebolt destination you configured earlier.
5. Select the streams you want to replicate and sync mode (Full refresh or Incremental). To save time select only "products" stream.
6. Finally specify the frequency of your data repication or manual if you want to trigger the job in UI or via an API call.
7. Click **"Set up connection"** to start syncing data from your source to Firebolt!
![Streams]()
#### Step 4: Monitor and Manage Data Syncs
1. Use the Airbyte UI to monitor your data syncs and ensure that data is being transferred accurately and efficiently.
2. Adjust sync settings and transformations as needed to optimize your ETL process. You can leverage DBT to
![Sync monitoring]()
### Output schema
The Firebolt Destination connector is a V1 connector, meaning it works with raw data. Refer to Airbyte’s [Destination V2 document](https://docs.airbyte.com/platform/using-airbyte/core-concepts/typing-deduping#what-is-destinations-v2) to learn about the differences. Each stream is written into its own [Fact table](/overview/data-management#firebolt-managed-tables) in Firebolt, containing three columns:
\*`_airbyte_ab_id`: a UUID assigned by Airbyte to each processed event. The column type is TEXT.
* `_airbyte_emitted_at`: a TIMESTAMP indicating when the event was pulled from the source.
* `_airbyte_data`: a JSON blob representing event data, stored as TEXT, but can be parsed using [JSON functions](/reference-sql/functions-reference/json).
### Further Reading
After setting up Airbyte with Firebolt, explore these resources to leverage additional features and enhance your data integration capabilities:
1. Learn how to use [Firebolt Source](https://docs.airbyte.com/integrations/sources/firebolt).
2. Ensure you're following [security guidelines](https://docs.airbyte.com/platform/operating-airbyte/security).
3. Explore other [deployment options](https://docs.airbyte.com/platform/deploying-airbyte).
4. Configure your [connections](https://docs.airbyte.com/platform/cloud/managing-airbyte-cloud/configuring-connections).
# Airflow
Source: https://docs.firebolt.io/guides/integrations/airflow
Learn how to use the Apache Airflow provider package to connect Airflow to Firebolt.
[Apache Airflow](https://airflow.apache.org/) is a data orchestration tool that allows you to programmatically create, schedule, and monitor workflows. You can connect a Firebolt database into your data pipeline using the Airflow provider package for Firebolt. For example, you can schedule automatic incremental data ingestion into Firebolt.
This guide explains how to install the [Airflow provider package](https://pypi.org/project/airflow-provider-firebolt/) for Firebolt, set up a connection to Firebolt resources using the Airflow user interface (UI), and create an example Directed Acyclic Graph (DAG) for common Firebolt tasks. The source code for the Airflow provider package for Firebolt is available in the [airflow-provider-firebolt](https://github.com/firebolt-db/airflow-provider-firebolt) repository on GitHub.
## Prerequisites
Make sure that you have:
* A Firebolt account. [Create a new account](/guides/managing-your-organization/managing-accounts#create-a-new-account).
* A Firebolt database and engine.
* [Python](https://www.python.org/downloads/) version 3.8 or later.
* An installation of Airflow version 2.x. See the [Airflow installation guide](https://airflow.apache.org/docs/apache-airflow/stable/installation/index.html).
The Firebolt Airflow provider package currently supports Apache Airflow 2.x only. Airflow 3.x is not yet supported.
## Install the Airflow provider package for Firebolt
You need to install the Airflow provider package for Firebolt. This package enables Firebolt as a **Connection type** in the Airflow UI.
1. Install the package.
Run the following command to install the package:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
pip install airflow-provider-firebolt
```
2. Upgrade to the latest version.
Run the latest version of the provider package. [Release history](https://pypi.org/project/airflow-provider-firebolt/#history) is available on PyPI.
Use the following command to upgrade:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
pip install airflow-provider-firebolt --upgrade
```
Restart Airflow after upgrading to apply the new changes.
3. Install a specific version.
If a specific version is required, replace `1.0.0` with the desired version:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
pip install airflow-provider-firebolt==1.0.0
```
4. Install the provider for AWS Managed Airflow (MWAA).
Ensure you are using version 2 of AWS Managed Airflow (MWAA) when working with the Firebolt Airflow provider. Add `airflow-provider-firebolt` to the `requirements.txt` file following the instructions in the [MWAA Documentation.](https://docs.aws.amazon.com/mwaa/latest/userguide/working-dags-dependencies.html)
## Connect Airflow to Firebolt
Create a connection object in the Airflow UI to integrate Firebolt with Airflow.
### Steps to configure a connection
1. Open the Airflow UI and log in.
2. Select the **Admin** menu.
3. Choose **Connections**.
4. Select the **+** button to add a new connection.
5. Choose Firebolt from the **Connection Type** list
6. Provide the details in the following table. These connection parameters correspond to built-in Airflow variables.
| Parameter | Description | Example value |
| :------------ | :--------------------------------------------------------------------------------------------------- | :-------------------------------------------------------------------------------------------- |
| Connection id | The name of the connection for the UI. | `My_Firebolt_Connection` |
| Description | Information about the connection. | `Connection to Firebolt database MyDatabase using engine MyFireboltDatabase_general_purpose.` |
| Database | The name of the Firebolt database to connect to. | `MyFireboltDatabase` |
| Engine | The name of the engine to run queries | `MyFireboltEngine` |
| Client ID | The ID of your service account. | `XyZ83JSuhsua82hs` |
| Client Secret | The [secret](/guides/loading-data/creating-access-keys-aws) for your service account authentication. | `yy7h&993))29&%j` |
| Account | The name of your account. | `developer` |
| Extra | The additional properties that you may need to set (optional). | `{"property1": "value1", "property2": "value2"}` |
Client ID and secret credentials can be obtained by registering a [service account](/managed-service/organization/service-accounts).
7. Choose **Test** to verify the connection.
8. Once the test succeeds, select **Save**.
## Create a DAG for data processing with Firebolt
A DAG file in Airflow is a Python script that defines tasks and their execution order for a data workflow. The following example is an example DAG for performing the following tasks:
* Start a Firebolt [engine](/managed-service/engine-fundamentals).
* Create an [external table](/guides/loading-data/working-with-external-tables) linked to an Amazon S3 data source.
* Create a fact table for ingested data. For more information, see [Firebolt-managed tables](/overview/data-management#firebolt-managed-tables).
* Insert data into the fact table.
* Stop the Firebolt engine. This task is not required if your engine has `AUTO_STOP` configured
### DAG script example
The following DAG script creates a DAG named `firebolt_provider_trip_data`. It uses an Airflow connection to Firebolt named `my_firebolt_connection`. For the contents of the SQL scripts that the DAG runs, see the following [SQL script examples](#sql-script-examples). You can run this example with your own database and engine by updating the connector values in Airflow, setting the `FIREBOLT_CONN_ID ` to match your connector, and creating the necessary custom variables in Airflow.
```python Airflow 3.* theme={"theme":{"light":"css-variables","dark":"css-variables"}}
from datetime import datetime
from airflow.sdk import dag, task, Variable
from firebolt_provider.hooks.firebolt import FireboltHook
from firebolt_provider.operators.firebolt import FireboltStartEngineOperator, FireboltStopEngineOperator
# Set up the Firebolt connection ID
firebolt_conn_id = 'firebolt'
# Function to get Firebolt connection parameters
def get_connection_params(conn_id, field):
hook = FireboltHook(firebolt_conn_id=conn_id)
conn_parameters = hook._get_conn_params()
return getattr(conn_parameters, field)
# Function to open query files saved locally
def get_query(query_file):
return open(query_file, "r").read()
@dag(
dag_id='firebolt_provider_startstop_trip_data',
start_date=datetime(2023, 1, 1),
schedule=None,
catchup=False,
tags=["firebolt"]
)
def firebolt_trip_data_dag():
firebolt_engine_name = get_connection_params(firebolt_conn_id, 'engine_name')
tmpl_search_path = Variable.get("firebolt_sql_path")
@task
def start_engine():
start_op = FireboltStartEngineOperator(
task_id="START_ENGINE",
firebolt_conn_id=firebolt_conn_id,
engine_name=firebolt_engine_name
)
return start_op.execute({})
@task
def create_external_table():
hook = FireboltHook(firebolt_conn_id=firebolt_conn_id)
sql = get_query(f'{tmpl_search_path}/trip_data__create_external_table.sql')
hook.run(sql)
return "External table created"
@task
def create_fact_table():
hook = FireboltHook(firebolt_conn_id=firebolt_conn_id)
sql = get_query(f'{tmpl_search_path}/trip_data__create_table.sql')
hook.run(sql)
return "Fact table created"
@task
def process_data():
hook = FireboltHook(firebolt_conn_id=firebolt_conn_id)
sql = get_query(f'{tmpl_search_path}/trip_data__process.sql')
hook.run(sql)
return "Data processed"
@task
def stop_engine():
stop_op = FireboltStopEngineOperator(
task_id="STOP_ENGINE",
firebolt_conn_id=firebolt_conn_id,
engine_name=firebolt_engine_name
)
return stop_op.execute({})
# Define task dependencies
start_task = start_engine()
external_table_task = create_external_table()
fact_table_task = create_fact_table()
process_task = process_data()
stop_task = stop_engine()
start_task >> external_table_task >> fact_table_task >> process_task >> stop_task
# Instantiate the DAG
dag_instance = firebolt_trip_data_dag()
```
```python Airflow 2.* theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import time
import airflow
from airflow.models import DAG, Variable
from firebolt_provider.operators.firebolt \
import FireboltOperator, FireboltStartEngineOperator, FireboltStopEngineOperator
# Define function to get Firebolt connection parameters
def connection_params(conn_opp, field):
connector = FireboltOperator(
firebolt_conn_id=conn_opp, sql="", task_id="CONNECT")
conn_parameters = connector.get_db_hook()._get_conn_params()
return getattr(conn_parameters, field)
# Set up the Firebolt connection ID
firebolt_conn_id = 'firebolt'
firebolt_engine_name = connection_params(firebolt_conn_id, 'engine_name')
tmpl_search_path = Variable.get("firebolt_sql_path")
default_args = {
'owner': 'airflow',
'start_date': airflow.utils.dates.days_ago(1)
}
# Function to open query files saved locally.
def get_query(query_file):
return open(query_file, "r").read()
# Define a variable based on an Airflow DAG class.
# For class parameters, see https://airflow.apache.org/docs/apache-airflow/2.11.0/_api/airflow/models/dag/index.html#module-airflow.models.dag.
with DAG('firebolt_provider_startstop_trip_data',
default_args=default_args,
template_searchpath=tmpl_search_path,
schedule_interval=None,
catchup=False,
tags=["firebolt"]) as dag:
# Define DAG tasks and task sequence.
# Where necessary, read local sql files using the Airflow variable.
task_start_engine = FireboltStartEngineOperator(
dag=dag,
task_id="START_ENGINE",
firebolt_conn_id=firebolt_conn_id,
engine_name=firebolt_engine_name)
task_trip_data__external_table = FireboltOperator(
dag=dag,
task_id="task_trip_data__external_table",
sql=get_query(f'{tmpl_search_path}/trip_data__create_external_table.sql'),
firebolt_conn_id=firebolt_conn_id
)
task_trip_data__create_table = FireboltOperator(
dag=dag,
task_id="task_trip_data__create_table",
sql=get_query(f'{tmpl_search_path}/trip_data__create_table.sql'),
firebolt_conn_id=firebolt_conn_id
)
task_trip_data__create_table.post_execute = lambda **x: time.sleep(10)
task_trip_data__process_data = FireboltOperator(
dag=dag,
task_id="task_trip_data__process_data",
sql=get_query(f'{tmpl_search_path}/trip_data__process.sql'),
firebolt_conn_id=firebolt_conn_id
)
task_stop_engine = FireboltStopEngineOperator(
dag=dag,
task_id="STOP_ENGINE",
firebolt_conn_id=firebolt_conn_id,
engine_name=firebolt_engine_name)
(task_start_engine >> task_trip_data__external_table >>
task_trip_data__create_table >> task_trip_data__process_data >> task_stop_engine)
```
This DAG showcases various Firebolt tasks as an example and is not intended to represent a typical real-world workflow or pipeline.
### Define Airflow variables
Airflow variables store-key value pairs that DAGs can use during execution. You can create and manage variables through the Airflow user interface (UI) or JSON documents. For detailed instructions, check out Airflow's [Variables](https://airflow.apache.org/docs/apache-airflow/stable/concepts/variables.html) and [Managing Variables](https://airflow.apache.org/docs/apache-airflow/stable/howto/variable.html) documentation pages.
**Example variable for SQL files**\
The DAG example uses the custom variable `firebolt_sql_path` to define the directory within your Airflow home directory where SQL files are stored. The DAG reads these files to execute tasks in Firebolt.
* **Key**: `firebolt_sql_path`
* **Value**: Path to the directory containing SQL scripts. For example, `~/airflow/sql_store`.
**Using the variable in the DAG**\
A python function in the DAG reads the SQL scripts stored in the directory defined by `firebolt_sql_path`. This allows the DAG to dynamically execute the SQL files as tasks in Firebolt.
The following example demonstrates how the variable is accessed in the DAG script:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
tmpl_search_path = Variable.get("firebolt_sql_path")
def get_query(query_file):
with open(query_file, "r") as file:
return file.read()
```
### SQL script examples
Save the following SQL scripts to your `tmpl_search_path`.
#### trip\_data\_\_create\_external\_table.sql
This example creates the `ex_trip_data` fact table to connect to a public Amazon S3 data store.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE EXTERNAL TABLE IF NOT EXISTS ex_trip_data(
vendorid INTEGER,
lpep_pickup_datetime TIMESTAMP,
lpep_dropoff_datetime TIMESTAMP,
passenger_count INTEGER,
trip_distance REAL,
ratecodeid INTEGER,
store_and_fwd_flag TEXT,
pu_location_id INTEGER,
do_location_id INTEGER,
payment_type INTEGER,
fare_amount REAL,
extra REAL,
mta_tax REAL,
tip_amount REAL,
tolls_amount REAL,
improvement_surcharge REAL,
total_amount REAL,
congestion_surcharge REAL
)
url = 's3://firebolt-publishing-public/samples/taxi/'
object_pattern = '*yellow*2020*.csv'
type = (CSV SKIP_HEADER_ROWS = true);
```
#### trip\_data\_\_create\_table.sql
This example creates the `my_taxi_trip_data` fact table, to receive ingested data.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
DROP TABLE IF EXISTS my_taxi_trip_data;
CREATE FACT TABLE IF NOT EXISTS my_taxi_trip_data(
vendorid INTEGER,
lpep_pickup_datetime TIMESTAMP,
lpep_dropoff_datetime TIMESTAMP,
passenger_count INTEGER,
trip_distance REAL,
ratecodeid INTEGER,
store_and_fwd_flag TEXT,
pu_location_id INTEGER,
do_location_id INTEGER,
payment_type INTEGER,
fare_amount REAL,
extra REAL,
mta_tax REAL,
tip_amount REAL,
tolls_amount REAL,
improvement_surcharge REAL,
total_amount REAL,
congestion_surcharge REAL,
SOURCE_FILE_NAME TEXT,
SOURCE_FILE_TIMESTAMP TIMESTAMP
) PRIMARY INDEX vendorid;
```
#### trip\_data\_\_process.sql
An `INSERT INTO` operation ingests data into the `my_taxi_trip_data` fact table using the `ex_trip_data`
external table. This example uses the external table metadata column, `$source_file_timestamp`, to retrieve records exclusively from the latest file.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
INSERT INTO my_taxi_trip_data
SELECT
vendorid,
lpep_pickup_datetime,
lpep_dropoff_datetime,
passenger_count,
trip_distance,
ratecodeid,
store_and_fwd_flag,
pu_location_id,
do_location_id,
payment_type,
fare_amount,
extra,
mta_tax,
tip_amount,
tolls_amount,
improvement_surcharge,
total_amount,
congestion_surcharge,
$source_file_name,
$source_file_timestamp
FROM ex_trip_data
WHERE coalesce($source_file_timestamp > (SELECT MAX(source_file_timestamp) FROM my_taxi_trip_data), true);
```
## Example: Working with query results
The `FireboltOperator` is designed to execute SQL queries but does not return query results. To retrieve query results, use the `FireboltHook` class. The following example demonstrates how to use `FireboltHook` to execute a query and log the row count in the `my_taxi_trip_data` table.
### Python code example: Retrieiving query results
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import logging
import airflow
from airflow import DAG
from airflow.operators.python import PythonOperator
from firebolt_provider.hooks.firebolt import FireboltHook
from airflow.providers.common.sql.hooks.sql import fetch_one_handler
# Set up the Firebolt connection ID
firebolt_conn_id = 'firebolt'
default_args = {
'owner': 'airflow',
'start_date': airflow.utils.dates.days_ago(1)
}
# Function to notify the team about the data
def notify(message: str):
logging.info(message)
# Function to fetch data from Firebolt and notify the team
def fetch_firebolt_data():
hook = FireboltHook(firebolt_conn_id=firebolt_conn_id)
results = hook.run(
"SELECT count(*) FROM my_taxi_trip_data",
handler=fetch_one_handler
)
count = results[0]
notify("Amount of data in Firebolt: " + str(count))
with DAG(
'return_result_dag',
default_args=default_args,
schedule_interval=None, # Run manually
catchup=False
) as dag:
# Define a Python operator to fetch data from Firebolt and notify the team
monitor_firebolt_data = PythonOperator(
task_id='monitor_firebolt_data',
python_callable=fetch_firebolt_data
)
monitor_firebolt_data
```
## Example: Controlling query execution timeout
The Firebolt provider includes parameters to control query execution time and behavior when a timeout occurs:
* `query_timeout`: Sets the maximum duration (in seconds) that a query can run
* `fail_on_query_timeout` - If `True`, a timeout raises a `QueryTimeoutError`. If `False`, the task terminates quietly, and the task proceeds without raising an error.
### Python code example: Using timeout settings
In this example, the `FireboltOperator` task stops execution after one second and proceeds without error. The `PythonOperator` task fetches data from Firebolt with a timeout of 0.5 seconds and raises an error if the query times out.
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import airflow
from airflow.models import DAG, Variable
from airflow.operators.python import PythonOperator
from firebolt_provider.hooks.firebolt import FireboltHook
from airflow.providers.common.sql.hooks.sql import fetch_one_handler
from firebolt_provider.operators.firebolt import FireboltOperator
# Set up the Firebolt connection ID
firebolt_conn_id = 'firebolt'
default_args = {
'owner': 'airflow',
'start_date': airflow.utils.dates.days_ago(1)
}
tmpl_search_path = Variable.get("firebolt_sql_path")
def get_query(query_file):
return open(query_file, "r").read()
# Function to fetch data with a timeout
def fetch_with_timeout():
hook = FireboltHook(
firebolt_conn_id=firebolt_conn_id,
query_timeout=0.5,
fail_on_query_timeout=True,
)
results = hook.run(
"SELECT count(*) FROM my_taxi_trip_data",
handler=fetch_one_handler,
)
print(f"Results: {results}")
# Define the DAG
with DAG(
'timeout_dag',
default_args=default_args,
schedule_interval=None, # Run manually
catchup=False
) as dag:
# Firebolt operator with a timeout
firebolt_operator_with_timeout = FireboltOperator(
dag=dag,
task_id="insert_with_timeout",
sql=get_query(f'{tmpl_search_path}/trip_data__process.sql'),
firebolt_conn_id=firebolt_conn_id,
query_timeout=1,
# Task will not fail if query times out, and will proceed to the next task
fail_on_query_timeout=False,
)
# Python operator to fetch data with a timeout
operator_with_hook_timeout = PythonOperator(
dag=dag,
task_id='select_with_hook_timeout',
python_callable=fetch_with_timeout,
)
firebolt_operator_with_timeout >> operator_with_hook_timeout
```
## Additional resources
For more information about connecting to Airflow, refer to the following resources:
* [Managing Connections in Airflow](https://airflow.apache.org/docs/apache-airflow/stable/howto/connection.html)
* [Firebolt Airflow provider on Pypi](https://pypi.org/project/airflow-provider-firebolt/)
* [DAGs](https://airflow.apache.org/docs/apache-airflow/stable/core-concepts/dags.html)
* [airflow.models.dag](https://airflow.apache.org/docs/apache-airflow/2.11.0/_api/airflow/models/dag/index.html#module-airflow.models.dag)
# AWS Glue
Source: https://docs.firebolt.io/guides/integrations/aws-glue
Learn how to use AWS Glue with Firebolt to build data pipelines using the JDBC driver.
![AWS Glue logo]()
[AWS Glue](https://aws.amazon.com/glue/) is a fully managed extract, transform, and load (ETL) service that makes it easy to prepare and transform data for analytics. AWS Glue automatically discovers your data and stores the associated metadata in the AWS Glue Data Catalog, making your data immediately searchable, queryable, and available for ETL operations.
You can connect AWS Glue to Firebolt using the [Firebolt JDBC driver](/guides/developing-with-firebolt/connecting-with-jdbc) to build powerful data pipelines that can extract data from various sources, transform it using Spark, and load it into your Firebolt database for high-performance analytics.
## Prerequisites
Before connecting AWS Glue to Firebolt, ensure you have:
* **AWS Account** – An active AWS account with appropriate permissions to create and manage AWS Glue resources.
* **Firebolt account** – An active Firebolt account. If you don't have one, you can [sign up](https://go.firebolt.io/signup).
* **Firebolt database and engine** – Access to a Firebolt database and engine. If you need to create these, see [Create a database](/overview/quickstart#create-a-database) and [Create an engine](/overview/quickstart#create-an-engine).
* **Firebolt service account** – A [service account](/managed-service/organization/service-accounts) for programmatic access with its ID and secret.
* **Appropriate permissions** – Your service account must be associated with a user that has the appropriate permissions to query the database and operate the engine. Specifically, the user should have [USAGE](/security/rbac/database-permissions) permission on the database and [OPERATE](/security/rbac/engine-permissions) permission on the engine. In short, a user should make sure that any operation they wish to perform on the Firebolt database or engine is allowed by the permissions granted to their service account.
* **IAM permissions** – AWS IAM permissions to create and manage Glue jobs, connections, and access S3 buckets.
## Set up the JDBC connection in AWS Glue
1. Download the [Firebolt JDBC driver JAR](https://github.com/firebolt-db/jdbc/releases) from GitHub.
2. Upload the JAR file to an S3 bucket that your AWS Glue job can access.
3. Note the S3 path (e.g., `s3://your-bucket/jars/firebolt-jdbc-3.x.x.jar`).
## Create an ETL job with Firebolt
### Read data from Firebolt
You can create an AWS Glue job that reads data from Firebolt using the JDBC driver. Below is a sample Glue script that connects to Firebolt to read data and then transforms it.
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
# Initialize Glue context
args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)
# Firebolt connection details
url = "jdbc:firebolt:?engine=&account="
user = "your_client_id"
password = "your_client_secret"
driver = "com.firebolt.FireboltDriver"
source_table = "your_source_table"
# Read data from Firebolt using JDBC
source_df = spark.read \
.format("jdbc") \
.option("url", url) \
.option("user", user) \
.option("password", password) \
.option("driver", driver) \
.option("dbtable", source_table) \
.load()
# Apply basic transformations
filtered_df = source_df.filter(source_df.status == "active")
renamed_df = filtered_df.withColumnRenamed("customer_id", "id")
# Use Spark SQL for further filtering and selection
renamed_df.createOrReplaceTempView("temp_view")
processed_df = spark.sql("""
SELECT
id,
name,
email,
CURRENT_TIMESTAMP() as load_timestamp
FROM temp_view
WHERE email IS NOT NULL
""")
```
### Write data to Firebolt
You can also write data back to Firebolt using the JDBC driver. Below is an example of how to write the processed data into a Firebolt table.
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# Target table to write to in Firebolt
target_table = "your_target_table"
# Write processed data to Firebolt
processed_df.write \
.format("jdbc") \
.option("url", url) \
.option("user", user) \
.option("password", password) \
.option("driver", driver) \
.option("dbtable", target_table) \
.mode("overwrite") \
.save()
# Commit the job
job.commit()
```
### Configure job parameters
When creating your AWS Glue job, configure these important parameters:
1. **Job properties**:
* **Type**: Spark
* **Glue version**: Choose the latest version (5.0 recommended)
* **Language**: Python 3
2. **Advanced properties**:
* **Dependent JARs path**: `s3://your-bucket/jars/firebolt-jdbc-3.x.x.jar`
* **Job parameters** (optional, can be used to pass dynamic values/creds)
3. **Security configuration**: Choose appropriate IAM roles and encryption settings.
4. **Resource allocation**: Configure the number of workers and worker type based on your data volume.
## DMS-S3-Glue-Firebolt data pipeline
A common use case for AWS Glue with Firebolt is building a data pipeline that processes change data capture (CDC) files from AWS Database Migration Service (DMS). This section covers how to set up an automated pipeline that:
1. **AWS DMS** captures changes from source databases and writes them to S3 as CSV files
2. **AWS Glue** processes these files incrementally and loads them into Firebolt
3. **Firebolt** provides high-performance analytics on the replicated data
### Architecture overview
```
Source → AWS DMS → S3 → AWS Glue → Firebolt
```
The pipeline handles incremental processing by tracking which files have been processed, ensuring data integrity and preventing duplicate processing.
### Prerequisites for DMS integration
In addition to the [general prerequisites](#prerequisites), you'll need:
* **AWS DMS replication instance** configured to write CDC data to S3
* **S3 bucket** where DMS writes the CSV files
* **Glue Data Catalog** database and table to track processed files
* **EventBridge or Glue triggers** to trigger the job when new files arrive
### Set up the processed files tracking table
Create a Glue Data Catalog table to track which files have been processed:
1. In the AWS Glue console, create a new database (e.g., `processed_files_db`)
2. Create a table with the following schema:
| Column | Type | Description |
| -------------- | --------- | ----------------------------- |
| `file_path` | string | S3 path of the processed file |
| `processed_at` | timestamp | When the file was processed |
### DMS-Glue integration script
Here's a complete Glue script that handles DMS CDC files:
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import sys
import boto3
from datetime import datetime
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.dynamicframe import DynamicFrame
from pyspark.sql.types import StructType, StructField, StringType, TimestampType
from pyspark.sql.functions import lit, current_timestamp
# Get job parameters
args = getResolvedOptions(sys.argv, [
"JOB_NAME", "driver", "url", "user", "password",
"bucket_name", "staging_table", "merge_query"
])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)
# Parameters from job configuration
driver = args["driver"]
url = args["url"]
user = args["user"]
password = args["password"]
bucket_name = args["bucket_name"]
staging_table = args["staging_table"]
merge_query = args["merge_query"]
# Optional parameters
prefix = args.get("prefix", "") # S3 folder prefix
suffix = args.get("suffix", ".csv") # File extension filter
# Step 1: Get all files from S3 bucket
s3 = boto3.client('s3')
response = s3.list_objects_v2(Bucket=bucket_name, Prefix=prefix)
all_files = [obj['Key'] for obj in response.get('Contents', []) if obj['Key'].endswith(suffix)]
# Step 2: Read already processed files from Glue catalog
try:
processed_df = glueContext.create_dynamic_frame.from_catalog(
database="processed_files_db",
table_name="processed_files_table",
transformation_ctx="load_processed"
).toDF()
processed_files = [row['file_path'] for row in processed_df.collect()]
except Exception as e:
print(f"No processed files found or error reading catalog: {e}")
processed_files = []
# Step 3: Determine files to process
to_process = list(set(all_files) - set(processed_files))
print(f"Found {len(to_process)} files to process")
# Step 4: Process each file
for file_path in to_process:
try:
print(f"Processing file: {file_path}")
s3_path = f"s3://{bucket_name}/{file_path}"
# Read CSV file from S3
df = spark.read \
.option("header", "false") \
.option("inferSchema", "true") \
.csv(s3_path)
# Define column names based on your DMS output format
# Adjust these column names according to your actual DMS configuration
column_names = [
"operation", # I, U, D for Insert, Update, Delete
"id",
"name",
"email",
"updated_at",
# Add more columns as needed
]
df = df.toDF(*column_names)
# Add metadata columns
df = df.withColumn("batch_id", lit(file_path)) \
.withColumn("load_timestamp", current_timestamp())
# Step 5: Write to Firebolt staging table
df.write \
.format("jdbc") \
.option("url", url) \
.option("user", user) \
.option("password", password) \
.option("driver", driver) \
.option("dbtable", staging_table) \
.option("batchsize", 10000) \ # Adjust batch size as needed
.mode("append") \
.save()
# Step 6: Execute merge query via JDBC
print(f"Executing merge query for batch: {file_path}")
jvm = spark._sc._jvm
jvm.java.lang.Class.forName(driver)
conn = jvm.java.sql.DriverManager.getConnection(url, user, password)
stmt = conn.createStatement()
# Replace placeholder in merge query with actual batch_id
batch_merge_query = merge_query.replace("${batch_id}", file_path) #if using batch_id in merge query
stmt.execute(batch_merge_query)
stmt.close()
conn.close()
# Step 7: Record processed file
schema = StructType([
StructField("file_path", StringType(), True),
StructField("processed_at", TimestampType(), True)
])
now = datetime.utcnow()
row = [(file_path, now)]
processed_file_df = spark.createDataFrame(row, schema=schema)
processed_file_dyf = DynamicFrame.fromDF(processed_file_df, glueContext, "new_files")
# Write to processed files table
glueContext.write_dynamic_frame.from_options(
frame=processed_file_dyf,
connection_type="s3",
connection_options={"path": "s3://glue-table-catalog-bucket/processed_files_table/"},
format="parquet"
)
print(f"Successfully processed file: {file_path}")
except Exception as e:
print(f"Error processing file {file_path}: {str(e)}")
# Optionally, continue with next file or fail the job
continue
job.commit()
```
### Configure DMS job parameters
When creating your DMS-Glue job, set these job parameters:
| Parameter | Description | Example |
| ----------------- | --------------------------- | --------------------------------------------------------------- |
| `--driver` | JDBC driver class | `com.firebolt.FireboltDriver` |
| `--url` | Firebolt JDBC URL | `jdbc:firebolt:your_db?engine=your_engine&account=your_account` |
| `--user` | Firebolt client ID | `your_client_id` |
| `--password` | Firebolt client secret | `your_client_secret` |
| `--bucket_name` | S3 bucket with DMS files | `your-dms-bucket` |
| `--staging_table` | Staging table in Firebolt | `staging.cdc_data` |
| `--merge_query` | SQL merge statement | See [merge query example](#merge-query-example) |
| `--prefix` | S3 folder prefix (optional) | `dms-output/` |
### Merge query example
Create a merge query that handles CDC operations (Insert, Update, Delete):
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
MERGE INTO {target_table} AS target
USING (select * from {staging_table} where file_path = '${batch_id}') AS source
ON target.{merge_key} = source.{merge_key}
WHEN matched and source.operation='D' then delete
WHEN matched and source.operation='U' then UPDATE set name=source.name, email=source.email, updated_at=source.updated_at
WHEN NOT MATCHED BY TARGET and source.operation='I' then INSERT (id, name, email, updated_at) VALUES (source.id, source.name, source.email, source.updated_at);
```
### Data integrity strategies
To maintain data integrity at scale (50,000+ records per hour), consider these approaches:
#### Strategy 1: Batch processing with staging
* Configure DMS to produce a regular number of files every 3-5 minutes
* Process multiple files in each Glue job run
* Use batch IDs to group related changes
* Execute MERGE statements after all files in a batch are loaded
This approach is the one used in the provided Glue script, where files are processed in batches and a merge query is executed after loading all files.
#### Strategy 2: Single file processing with controlled frequency
* Configure DMS to produce larger files (every 3+ minutes)
* Process one file at a time to avoid concurrent job conflicts
* Use Glue job queuing to handle multiple triggers
This could moves more work on DMS side, but it can simplify the Glue job logic and reduce the risk of concurrent processing issues.
### Trigger configuration
Set up EventBridge or S3 event notifications to trigger the Glue job:
#### Option 1: S3 Event Notifications
```json theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{
"source": ["aws.s3"],
"detail-type": ["Object Created"],
"detail": {
"bucket": {
"name": ["your_bucket_name"]
}
}
}
```
This configuration uses the Glue workflow EventBridge trigger to start the Glue job whenever one or more new files are created in the specified S3 bucket.
#### Option 2: Scheduled triggers
Use a scheduled trigger to run the Glue job at regular intervals (e.g., every 5 minutes). This is useful if you expect DMS to produce files at a consistent rate.
### Monitoring and troubleshooting
#### Key metrics to monitor
* **Files processed per hour**: Track throughput
* **Processing latency**: Time from file creation to Firebolt load
* **Error rate**: Failed file processing percentage
* **Data freshness**: Age of the oldest unprocessed file
#### Common issues
| Issue | Cause | Solution |
| ---------------------------- | --------------------------------------- | ------------------------------------------------ |
| **Concurrent job execution** | Multiple triggers firing simultaneously | Use job queuing or implement file locking |
| **Schema evolution** | DMS source schema changes | Add schema validation and dynamic column mapping |
### Best practices for DMS integration
1. **File size optimization**: Configure DMS to produce files of 10-100MB for optimal processing
2. **Monitoring**: Set up CloudWatch alarms for job failures and processing delays
3. **Schema management**: Handle schema evolution gracefully with dynamic column mapping
4. **Cost optimization**: Use appropriate Glue worker types and auto-scaling
5. **Data validation**: Add data quality checks before and after merge operations
## Additional resources
* [AWS Glue Developer Guide](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html)
* [Firebolt JDBC driver documentation](/guides/developing-with-firebolt/connecting-with-jdbc)
* [AWS Glue PySpark Transforms Reference](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-python-transforms.html)
* [Firebolt indexes overview](/overview/data-management)
* [Managing Firebolt service accounts](/managed-service/organization/service-accounts)
# Apache Superset
Source: https://docs.firebolt.io/guides/integrations/connecting-to-apache-superset
Learn about connecting Apache Superset to Firebolt.
[Apache Superset](https://superset.apache.org) is an open-source data exploration and visualization platform that empowers users to create interactive, shareable dashboards and charts for analyzing and presenting data. It supports a wide range of data sources and provides an intuitive, web-based interface for data exploration, slicing, and dicing, with features like dynamic filtering, pivot tables, and drag-and-drop functionality. Superset also offers a rich set of visualization options and can be extended through custom plugins, making it a versatile tool for data analysts and business users to gain insights from their data and collaborate effectively.
With its exceptional speed and scalability, Firebolt allows users to handle vast amounts of data with minimal query latency, ensuring that Superset dashboards and visualizations load quickly, even when dealing with massive datasets. This integration between Firebolt and Superset creates a powerful combination for data professionals, offering them a streamlined and efficient workflow for extracting maximum value from their data.
Firebolt is also supported in [Preset](/guides/integrations/connecting-to-preset), a fully managed cloud Superset solution.
## Prerequisites
Superset can be installed in several ways, including using a pre-built [Docker container](https://superset.apache.org/docs/installation/docker-compose), building it from [source](https://superset.apache.org/docs/installation/pypi) or deploying via [Kubernetes Helm chart](https://superset.apache.org/docs/installation/kubernetes).
The easiest way to get started is to run Superset via Docker.
You will need:
* [Docker](https://www.docker.com/) and [Docker Compose](https://docs.docker.com/compose/).
* [VirtualBox](https://www.virtualbox.org/) (Windows only).
* [Git](https://git-scm.com/).
## Quickstart
Follow this guide to setup Superset and get your first chart ready.
### Setup Superset
1. Clone Superset's GitHub [repository](https://github.com/apache/superset)
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
git clone https://github.com/apache/superset.git
```
2. Change directory to the root of the newly cloned repository and add the Firebolt driver
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
cd superset
touch ./docker/requirements-local.txt
echo "firebolt-sqlalchemy" >> ./docker/requirements-local.txt
```
3. Run Superset via Docker Compose
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
docker compose -f docker-compose-non-dev.yml up
```
4. (Optional) Verify firebolt driver is present in Superset container
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
docker exec -it bash
pip freeze | grep firebolt
```
You should see `firebolt-sqlalchemy` in the output.
Once your Superset is booted up you should be able to access it in `http://localhost:8088/`
For more installation details, refer to [Adding New Database Drivers in Docker](https://superset.apache.org/user-docs/6.0.0/configuration/databases/#installing-drivers-in-docker-images) in the Superset documentation.
### Setup Firebolt connection
After the initial setup in Superset User Interface head to the `Settings -> Database connections` in the top right corner.
![Database Connections]()
On the next screen, press the `+ Database` button and select Firebolt from the dropdown. If you don't see Firebolt in the list, please refer to the [Setup Superset](#setup-superset) section for instructions on how to install the Firebolt driver and verify that the driver is present.
![Connect database]()
The connection expects a SQLAlchemy connection string of the form:
```
firebolt://{client_id}:{client_secret}@{database}/{engine_name}?account_name={account_name}
```
To authenticate, use a service account ID and secret.
A service account is identified by a `client_id` and a `client_secret`.
Learn how to generate an ID and secret [here](/managed-service/organization/service-accounts).
Account name must be provided, you can learn about accounts in [Manage accounts](/guides/managing-your-organization/managing-accounts) section.
![Credentials]()
Click the **Test Connection** button to confirm things work end to end. If the connection looks good, save the configuration by clicking the **Connect** button in the bottom right corner of the modal window.
Now you're ready to start using Superset!
### Build your first chart
This section assumes you have followed Firebolt [tutorial](/overview/quickstart) and loaded a sample data set into your database.
Now that you’ve configured Firebolt as a data source, you can select specific tables (Datasets) that you want to see in Superset.
Go to Data -> Datasets and select `+ Dataset`. There you can select your sample table by specifying Firebolt as your Database, your schema and the table name you chose.
![Dataset]()
Press "Create Dataset and Create Chart". On the next screen you can select your desired chart type. For this tutorial we will go with a simple Bar Chart.
![Chart type]()
In the next screen you can drag and drop your table columns into Metrics and Dimensions, specify filters or sorting orders. We will plot max play time per level grouping it by level type and sorting the x-axis in ascending order.
![Chart]()
Your first chart is ready! You can now save it, add more data to it or change its type. You can also start building a dashboard with different charts telling a story. Learn more about this functionality and more by following the links below.
## Further reading
* [Creating your first Dashboard](https://superset.apache.org/docs/creating-charts-dashboards/creating-your-first-dashboard).
* [Exploring data](https://superset.apache.org/docs/creating-charts-dashboards/exploring-data).
* [Preset](https://preset.io/) - managed Superset.
# DataBrain
Source: https://docs.firebolt.io/guides/integrations/connecting-to-databrain
Learn about connecting DataBrain to Firebolt.
[DataBrain](https://www.usedatabrain.com/) is a modern embedded analytics platform that enables you to build and embed interactive dashboards, visualizations, and self-service business intelligence directly into your applications. It provides a comprehensive suite of tools for data visualization, AI-powered analytics, and customer-facing analytics, empowering teams to deliver data-driven insights to their end users.
With its exceptional speed and scalability, Firebolt allows you to handle vast amounts of data with minimal query latency, ensuring that DataBrain dashboards and visualizations load quickly, even when dealing with massive datasets. This integration creates a powerful combination for product teams and developers, offering a streamlined and efficient workflow for building embedded analytics solutions.
## Benefits of using Firebolt with DataBrain
Firebolt's ultra-fast query performance and scalable architecture make it an ideal data source for DataBrain's embedded analytics platform:
* **Ultra-fast query performance:** Firebolt's proprietary indexing technology and in-memory capabilities enable lightning-quick queries, ensuring sub-second dashboard load times.
* **Scalable architecture:** Pay-as-you-go model with independent scaling of storage and compute for cost-effective resource management.
* **Developer-friendly:** Firebolt offers ANSI SQL support, making it easy for data teams and analysts to adopt and integrate with existing workflows.
* **Optimized for analytics:** Ideal for complex analytical workloads, BI dashboards, and real-time reporting.
* **Cost efficiency:** Right-size your compute clusters and enable auto-scaling to manage costs effectively.
## Prerequisites
Before connecting DataBrain to Firebolt, ensure you have:
* An active [DataBrain account](https://www.usedatabrain.com/).
* A Firebolt account with a configured database and engine.
* Service account [credentials](/managed-service/organization/service-accounts) (Client ID and Client Secret).
* Your Firebolt account name, database name, engine name, and schema name.
* [Data loaded](/guides/loading-data) into your Firebolt database that you want to visualize.
## Setup guide
Follow these steps to connect Firebolt to DataBrain and start building your analytics dashboards.
### Create a Firebolt service account
To authenticate DataBrain with Firebolt, you need to create a service account with the appropriate permissions.
1. Log in to your Firebolt account.
2. Navigate to the **Configure** tab and select the **Service Accounts** section.
3. Click **CREATE** to establish a new service account.
4. Copy the **Client ID** and **Client Secret** for later use.
For detailed instructions on creating and managing service accounts, see [Service accounts](/managed-service/organization/service-accounts).
### Assign roles and permissions
Create a role with the necessary permissions and assign it to your service account.
1. Navigate to the **Govern** tab in your Firebolt account.
2. Create a new role with permissions to **use any database** and **use any engine**.
3. Create a user within Firebolt and assign the newly created role to the user.
For more information on role-based access control, see [RBAC](/security/rbac).
### Configure the connection in DataBrain
Now that you have your Firebolt credentials, you can configure the connection in DataBrain.
1. Log in to your DataBrain account.
2. Navigate to **Data Studio** → **Data Sources**.
3. Click **Add New Source** and select **Firebolt** from the list of available connectors.
4. Enter the following connection details:
* **Integration Name**: Choose a descriptive name to identify this data source in DataBrain.
* **Client ID**: Paste the Client ID from your Firebolt service account.
* **Client Secret**: Paste the Client Secret from your Firebolt service account.
* **Account Name**: Enter your Firebolt account name.
* **Database Name**: Specify the name of your Firebolt database.
* **Engine Name**: Provide the name of your Firebolt engine (e.g., `my_engine`).
* **Schema**: Enter the schema name you want to use.
5. Click **Test Connection** to verify that DataBrain can successfully connect to Firebolt.
6. Once the connection test succeeds, click **Save** to complete the setup.
Make sure your service account has the appropriate permissions to access the specified database, engine, and schema. If the connection test fails, verify your credentials and permissions.
### Build your first dashboard
Once your Firebolt data source is connected, you can start building interactive dashboards in DataBrain.
1. Navigate to **Workspaces** in DataBrain and create a new workspace or select an existing one.
2. Click **Create Dashboard** to start building your first dashboard.
3. Add visualizations by selecting your Firebolt data source and choosing the tables or views you want to analyze.
4. Use DataBrain's drag-and-drop interface to create charts, graphs, and other visualizations.
5. Apply filters, aggregations, and transformations to your data as needed.
6. Customize the dashboard layout and appearance using DataBrain's theming and customization options.
7. Share your dashboard with team members or embed it directly into your application.
DataBrain supports automated data refreshes to keep your dashboards up to date. Configure refresh schedules in the **Data Sources** settings.
## Real-world use cases
DataBrain and Firebolt integration is ideal for a variety of analytics scenarios:
* **E-commerce analytics:** Track millions of transactions in near real-time to spot trends and optimize pricing strategies. Build customer-facing dashboards that display order history, inventory levels, and sales performance.
* **IoT data monitoring:** Ingest high-volume sensor data and gain instant insights to drive rapid decision-making. Visualize device metrics, anomaly detection, and predictive maintenance indicators.
* **SaaS product analytics:** Embed usage analytics, feature adoption metrics, and user behavior dashboards directly into your SaaS application, empowering your customers with self-service analytics.
* **Financial reporting:** Deliver real-time financial dashboards with sub-second query performance, enabling stakeholders to make informed decisions based on the latest data.
## Best practices
To get the most out of your DataBrain and Firebolt integration, follow these best practices:
* **Leverage indexing strategies:** Use Firebolt's unique primary and aggregating indexes to reduce query times and improve dashboard performance.
* **Optimize data models:** Create efficient data models in DataBrain that align with your Firebolt table structures and indexes.
* **Cost optimization:** Right-size your Firebolt compute clusters and enable auto-scaling to manage costs effectively while maintaining performance.
* **Performance monitoring:** Regularly check Firebolt's usage metrics and query performance to identify bottlenecks or potential improvements.
* **Schedule data refreshes:** Configure automated data refresh schedules in DataBrain to ensure your dashboards always display the latest information.
## Further reading
* [DataBrain documentation](https://docs.usedatabrain.com/) - comprehensive guides for using DataBrain.
* [DataBrain blog: Firebolt integration announcement](https://www.usedatabrain.com/blog/databrain-firebolt-integration) - learn more about the integration and its benefits.
* [Firebolt service accounts](/managed-service/organization/service-accounts) - detailed guide on creating and managing service accounts.
* [Loading data into Firebolt](/guides/loading-data) - learn how to ingest data into Firebolt.
* [Firebolt indexing strategies](/overview/data-management) - optimize query performance with primary and aggregating indexes.
# Paradime
Source: https://docs.firebolt.io/guides/integrations/connecting-to-paradime
Connect Paradime to Firebolt.
![Paradime logo]()
[Paradime](https://www.paradime.io/) is a unified platform for data science and analytics that streamlines workflows for data teams. It offers a collaborative workspace where data scientists and analysts can explore, analyze, and visualize data across multiple tools and environments. Paradime integrates with tools including Jupyter notebooks, SQL editors, and Tableau. You can use the [Paradime connector](https://docs.paradime.io/app-help/documentation/settings/connections/scheduler-environment/firebolt) to link the Paradime platform directly to Firebolt's cloud data warehouse. This connection allows you to run SQL queries, visualize results, and collaborate with team members all within the Paradime workspace.
This guide shows you how to connect Paradime to Firebolt using the Paradime user interface (UI). You must have a Firebolt account, a Firebolt service account, access to a Firebolt database, and an account with Paradime. These instructions build on the steps in Paradime's [Getting Started with your Paradime Workspace](https://docs.paradime.io/app-help/guides/paradime-101/getting-started-with-your-paradime-workspace) guide, providing Firebolt-specific configuration details.
## Prerequisites
Before you can connect Paradime to Firebolt, you must have the following:
1. **Firebolt Account**: Ensure that you have access to an active Firebolt account. If you don't have access, you can [sign up for an account](https://go.firebolt.io/signup). For more information about how to register with Firebolt, see [Get started with Firebolt](/overview/quickstart).
2. **Service Account**: You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt.
3. **Firebolt Database**: You must have access to a Firebolt database. If you don't have access, you can [create a database](/overview/quickstart#create-a-database).
4. **Paradime Account**: You must have access to an active Paradime account. If you don't have access, you can [sign up](https://app.paradime.io) for one.
## Create a Paradime workspace
Create a Paradime workspace to connect to Firebolt as follows:
1. In the Paradime UI, navigate to your account profile in the upper-right corner of the page.
2. Select **Profile Settings**.
3. In the **Workspaces** window, select the **New Workspace** button.
4. Enter a descriptive name for your workspace in the text box under **Name**.
5. Select **Create Workspace**.
6. Select **Continue**.
7. Select the most recent dbt-core version from the drop-down list.
8. Select **Continue**.
9. Select a dbt repository. You can either use an existing data build tool ([dbt](https://www.getdbt.com/blog/what-exactly-is-dbt)) repository or fork Firebolt's sample [Jaffle Shop](https://github.com/firebolt-db/jaffle_shop_firebolt) repository from GitHub. Paradime supports the following providers: Azure DevOps, Bitbucket, GitHub, and GitLab.
10. Select **Next**.
11. Enter the SSH URI for your repository in the text box under **Repository URI**. Copy the key that appears under the **Deploy Key**.
12. Add the new deploy key to your dbt repository and allow write access. The following are resources for providers supported by Paradime:
* [Add the deploy key](https://docs.github.com/en/authentication/connecting-to-github-with-ssh/managing-deploy-keys#set-up-deploy-keys) in **github**.
* [Add a deployment key](https://www.atlassian.com/blog/bitbucket/deployment-keys) in **Bitbucket**.
* Use [deploy keys](https://docs.gitlab.com/ee/user/project/deploy_keys/) in **gitlab**.
* [Use SSH key authentication](https://learn.microsoft.com/en-us/azure/devops/repos/git/use-ssh-keys-to-authenticate?view=azure-devops) to connect with **Azure DevOps**.
13. Select **Continue**.
14. If your repository connected successfully, select **Continue**.
15. Select **Firebolt** from the choices under **Warehouse connection**.
16. Under **Connection Settings**, enter the following:
1. **Profile Name** – The name of a [connection profile](https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles) that is defined in `dbt_project.yaml` by a workspace administrator, and contains configurations including credentials to connect to a data warehouse. For more information, see Paradime's [Setting up your profile](https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles#setting-up-your-profile) guide.
2. **Target** – Specify the [target variable](https://docs.getdbt.com/reference/dbt-jinja-functions/target) that contains information about your data warehouse connection including its name, schema, and type.
3. **Host Name** – Enter `api.app.firebolt.io`.
17. Under **Development Credentials**, enter the following:
1. **Client Id** – Enter your Firebolt [service account ID](/managed-service/organization/service-accounts#get-a-service-account-id). Do not enter your Firebolt login email.
2. **Client Secret** – Enter your Firebolt [service account secret](/managed-service/organization/service-accounts#generate-a-secret). Do not enter your Firebolt password.
3. **Account Name** – Enter your Firebolt [account name](/guides/managing-your-organization/managing-accounts).
4. **Engine Name** – Enter the name of the engine where you want to run your queries.
5. **Database Name** – Specify the Firebolt database name.
6. Select **Test Connection** to connect to Firebolt and authenticate.
7. Select **Next**.
For more information about the previous connection settings, see Paradime's documentation to [add a development connection](https://docs.paradime.io/app-help/documentation/settings/connections/development-environment/firebolt).
## Create a schedule (Optional)
Paradime offers a scheduling feature using a [Bolt user interface](https://docs.paradime.io/app-help/documentation/bolt) to automatically run dbt commands on a specified interval or event. You can use Bolt to run a dbt job in a production environment, in a test environment prior to merging changes to production, or in an environment that runs jobs only on changed models.
To create a new schedule:
1. Login to your [Paradime account](https://app.paradime.io/?target=main-app).
2. Select **Bolt** from the left navigation bar.
3. Select **+ New Schedule**.
4. Select a pre-configured template from a list of popular Bolt templates or create a new schedule using a blank template. For information about how to configure settings in a Paradime schedule, see [Schedule Fields](https://docs.paradime.io/app-help/guides/paradime-101/running-dbt-in-production-with-bolt/creating-bolt-schedules#ui-based-schedule-fields).
5. Select **Publish**.
6. To view the new schedule, select **Bolt** from the left navigation pane.
## Additional resources
* Learn about the [Paradime integrated development Environment](https://docs.paradime.io/app-help/guides/paradime-101/getting-started-with-the-paradime-ide).
* Learn to use the [Bolt scheduler](https://docs.paradime.io/app-help/guides/paradime-101/running-dbt-in-production-with-bolt/creating-bolt-schedules) to run your dbt jobs.
* Learn how to [manage your Bolt schedule](https://docs.paradime.io/app-help/documentation/bolt/managing-schedules).
# Preset
Source: https://docs.firebolt.io/guides/integrations/connecting-to-preset
Learn about connecting Preset to Firebolt.
[Preset](https://preset.io/) is a cloud-hosted data exploration and visualization platform built on top of the popular open-source project, [Apache Superset](https://superset.apache.org/). This fully managed service makes it easy to run Superset at scale with enterprise-ready security, reliability, and governance.
Boasting exceptional speed and scalability, Firebolt enables users to adeptly manage substantial data volumes with minimal query latency. The integration with Preset establishes a strong partnership for data professionals, presenting them with a streamlined and efficient workflow. This collaboration ensures prompt loading of Preset dashboards and visualizations, even when confronted with extensive datasets, thereby facilitating the extraction of maximum value from their data.
## Prerequisites
Preset is a managed service so most of the deployment requirements are handled by them.
You will only need:
* To [register](https://manage.app.preset.io/starter-registration/) a Preset account.
* To have a Firebolt account and service account [credentials](/managed-service/organization/service-accounts).
* [Load data](/guides/loading-data) you want to visualise.
Make sure that your [service account's network policy](https://docs.firebolt.io/Guides/managing-your-organization/service-accounts.html#edit-your-service-account-using-the-ui) allows connections from [Preset IPs](https://docs.preset.io/docs/connecting-your-data).
## Quickstart
### Create a workspace
A workspace is an organizational unit, accessible by team members, that is created for a specific purpose. You can read Preset's [guidance](https://docs.preset.io/docs/about-workspaces) on workspaces to learn more.
1. To Create a Workspace, navigate to the empty card and select + Workspace.
![Create Workspace]()
2. Define Workspace name and settings
![Add new Workspace]()
3. Save the workspace and enter it by clicking the card.
### Setup Firebolt connection
After the initial setup in Preset User Interface head to the `Settings -> Database connections` in the top right corner.
![Database Connections]()
On the next screen, press the `+ Database` button and select Firebolt from the dropdown.
![Connect database]()
The connection expects a SQLAlchemy connection string of the form:
```
firebolt://{client_id}:{client_secret}@{database}/{engine_name}?account_name={account_name}
```
To authenticate, use a service account ID and secret.
A service account is identified by a `client_id` and a `client_secret`.
Learn how to generate an ID and secret [here](/managed-service/organization/service-accounts).
Account name must be provided, you can learn about accounts in [Manage accounts](/guides/managing-your-organization/managing-accounts) section.
![Credentials]()
Click the Test Connection button to confirm things work end to end. If the connection looks good, save the configuration by clicking the Connect button in the bottom right corner of the modal window.
Now you're ready to start using Preset!
### Build your first chart
To build a chart you can follow our guide in the [Superset section](/guides/integrations/connecting-to-apache-superset#build-your-first-chart), as the Preset works identically.
## Further reading
* [Creating a chart](https://docs.preset.io/docs/creating-a-chart) walkthrough.
* [Creating a Dashboard](https://docs.preset.io/docs/creating-a-dashboard).
* [Collaboration features of Preset](https://docs.preset.io/docs/sharing-and-collaboration).
* [Storytelling in charts](https://docs.preset.io/docs/storytelling-with-charts-and-dashboards-mini-guide).
# Cube.js
Source: https://docs.firebolt.io/guides/integrations/cube-js
Learn how to connect Cube.js to Firebolt.
![Cube]()
Cube.js is an open-source analytical API platform that empowers developers to build custom and scalable analytics solutions. By acting as an intermediary between your data sources and front-end applications, Cube.js simplifies the process of querying large datasets and ensures efficient data management and visualization.
Integrating Cube.js with Firebolt significantly enhances the data processing capabilities of your analytics stack. Firebolt’s ability to execute complex queries with minimal latency aligns perfectly with Cube.js’s goal of delivering fast and responsive analytics. As a result, users benefit from a seamless and highly performant analytics experience, making it an ideal solution for businesses looking to scale their data operations without compromising on speed or efficiency.
## Quickstart: Connecting Cube.js to Firebolt
Follow these steps to quickly connect Cube.js to Firebolt and start building powerful analytics solutions using Docker. For this demo we'll be using [Cube Core](https://cube.dev/docs/product/getting-started/core). For other deployment options follow the Cube [documentation](https://cube.dev/docs/product/deployment).
#### Prerequisites
1. **Docker**: Ensure you have Docker installed. You can download it from [here](https://www.docker.com/products/docker-desktop).
2. **Firebolt Account**: You need an active Firebolt account. Sign up [here](https://www.firebolt.io/) if you don’t have one.
3. **Firebolt Database and Table**: Make sure you have a Firebolt database and table with data ready for querying. Follow our [Getting started tutorial](/overview/quickstart) to set up some sample data.
4. **Firebolt Service Account**: Create a [service account](/managed-service/organization/service-accounts) in Firebolt and note its id and secret.
#### Step 1: Create a Cube.js Project with Docker
1. Create a new directory for your Cube.js project:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
mkdir cubejs-firebolt
cd cubejs-firebolt
touch docker-compose.yml
```
2. Create a `docker-compose.yml` file with the following content:
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
version: "2.2"
services:
cube:
image: cubejs/cube:latest
ports:
- 4000:4000
- 15432:15432
environment:
CUBEJS_DEV_MODE: "true"
volumes:
- .:/cube/conf
```
#### Step 2: Start Cube.js
1. Run the Cube.js development server using Docker Compose:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
docker compose up -d
```
2. Open your browser and navigate to `http://localhost:4000`. You should see the Cube.js [playground](https://cube.dev/docs/product/workspace/playground).
#### Step 3: Configure Firebolt Connection via UI
The Playground has a database connection wizard that loads when Cube is first started up and no .env file is found. After database credentials have been set up, an .env file will automatically be created and populated with credentials.
1. Select **Firebolt** as the database type.
2. Enter your Firebolt credentials:
* **Client ID**: Your service account ID
* **Client Secret**: Your service account secret
* **Database**: Your Firebolt database name
* **Account**: Your [account](/guides/managing-your-organization/managing-accounts) name
* **Engine Name**: Your Firebolt engine name
3. Click "Apply" to set up the connection
#### Step 4: Generate Schema Using UI
You should see tables available to you from the configured database
1. Select the `levels` table.
2. After selecting the table, click Generate Data Model and pick either YAML (recommended) or JavaScript format.
3. Click build.
![Data Model]()
You can start exploring your data!
#### Step 5: Query data in Playground
Select measures, dimensions and filters to explore your data!
![Playground]()
Congratulations! You have successfully connected Cube.js to Firebolt and can now start building high-performance analytics solutions. For more detailed configuration and advanced features, refer to the [Cube.js documentation](https://cube.dev/docs) and [Firebolt documentation](https://docs.firebolt.io/).
### Further Reading
After setting up Cube.js with Firebolt, you can explore and leverage several powerful features to enhance your analytics capabilities. Here are some resources to help you get started:
1. **Cube.js Data Blending**: Understand how to combine data from different sources for more comprehensive analysis.
[Cube.js Data Blending Documentation](https://cube.dev/docs/product/data-modeling/concepts/data-blending)
2. **Cube.js Security**: Implement row-level security to ensure your data is accessed appropriately.
[Cube.js Security Documentation](https://cube.dev/docs/security)
3. **Cube.js API**: Explore the Cube.js REST API to programmatically access your data and build custom integrations.
[Cube.js API Reference](https://cube.dev/docs/rest-api)
4. **Cube.js Visualization Tools**: Build and deploy powerful dashboards using Cube.js and your favorite front-end frameworks.
[Cube.js Visualization Tools](https://cube.dev/docs/product/configuration/visualization-tools)
These resources will help you unlock the full potential of Cube.js and create robust, high-performance analytics solutions.
# DBeaver
Source: https://docs.firebolt.io/guides/integrations/dbeaver
Configure DBeaver to connect to Firebolt using the JDBC driver.
![DBeaver logo]()
DBeaver is a free, open-source database administration tool that supports multiple database types. It provides a graphical interface for managing databases, running queries, and analyzing data. DBeaver is widely used for database development, troubleshooting, and administration, making it a versatile choice for both developers and database administrators. You can connect DBeaver to Firebolt using the [Firebolt JDBC driver](/guides/developing-with-firebolt/connecting-with-jdbc).
## Prerequisites
You must have the following prerequisites before you can connect your Firebolt account to DBeaver:
* **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
* **Firebolt database and engine** – You must have access to a Firebolt database. If you do not have access, you can [create a database](/overview/quickstart#create-a-database) and then [create an engine](/overview/quickstart#create-an-engine).
* **Firebolt service account** – You must have an active Firebolt [service account](/managed-service/organization/service-accounts) for programmatic access, along with its ID and secret.
* **Sufficient permissions** – Your service account must be [associated](/managed-service/organization/service-accounts#create-a-user) with a user. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started. It should also have at least USAGE and SELECT [permissions](/security/rbac/database-permissions/schema-permissions) on the schema you are planning to query.
* **DBeaver installed** – You must have downloaded and installed [DBeaver](https://dbeaver.io/download/).
## Add the Firebolt JDBC Driver in DBeaver
To connect to Firebolt, you must add the Firebolt JDBC driver to DBeaver as follows:
1. Download the [Firebolt JDBC driver](/guides/developing-with-firebolt/connecting-with-jdbc#download-the-jar-file).
2. In the DBeaver user interface (UI), under **Database**, select **Driver Manager**.
3. In **Driver Manager**, select **New** and enter the following parameters:
* **Driver Name**: `Firebolt`
* **Class Name**: `com.firebolt.FireboltDriver`
4. Select the **Libraries** tab.
5. Select **Add File**, and then select the JDBC driver you downloaded in the first step.
6. Select **Close**.
## Connect to Firebolt in DBeaver
To connect to Firebolt, you must configure a new database connection in DBeaver as follows:
1. In DBeaver, select **Database**, then **New Database Connection**.
2. Enter `Firebolt` in the search box, then select it from the list.
3. Select **Next>**.
4. Enter the connection parameters in the **Main** tab as follows:
| Parameter | Description |
| ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **JDBC URL** | Use `jdbc:firebolt:?engine=&account=` replacing `` with your Firebolt [database name](/overview/data-management#databases), `` with your [engine name](/overview/quickstart#create-an-engine) and `` with your [account name](/guides/managing-your-organization/managing-accounts). |
| **Username** | Your Firebolt [service account](/managed-service/organization/service-accounts#get-a-service-account-id) ID. |
| **Password** | Your Firebolt [service account](/managed-service/organization/service-accounts#generate-a-secret) secret. |
5. Select **Test Connection** to verify the connection. Ensure your Firebolt database is running before testing.
6. If the connection is successful, select **Finish**.
## Query Firebolt in DBeaver
1. In the database navigator, right-click or open the context menu of your Firebolt connection, select **SQL Editor**, then select **New SQL Script**.
2. Enter SQL queries into the SQL editor to interact with your Firebolt database.
## Additional Resources
* Learn more about the [Firebolt JDBC driver](/guides/developing-with-firebolt/connecting-with-jdbc).
* Explore [DBeaver's documentation](https://dbeaver.com/docs/dbeaver/) for details on its UI, integrations, tools, and features.
* Discover other tools that [Firebolt integrates](/guides/integrations) with.
# dbt - Firebolt adapter
Source: https://docs.firebolt.io/guides/integrations/dbt-firebolt-adaptor
Learn how to connect dbt to Firebolt using the Firebolt adapter.
![DBT]()
[DBT](https://www.getdbt.com), or Data Build Tool, is a framework designed for managing and executing data transformations within modern data warehousing architectures. It facilitates the development and deployment of SQL-based transformations in a version-controlled environment, enabling collaboration and ensuring reproducibility of data pipelines. DBT streamlines the process of transforming raw data into analytics-ready datasets, accelerating the delivery of insights.
The Firebolt adapter for dbt brings together dbt's state-of-the-art development tools and Firebolt's next-generation analytics performance. On top of dbt's core features, the adapter offers native support for all of Firebolt's index types and has been specifically enhanced to support ingestion from S3 using Firebolt's external tables mechanics.
## Prerequisites
The following steps install [dbt Core](https://docs.getdbt.com/docs/introduction#dbt-core) with Python's `pip`. For other installation methods, see [dbt's installation guide](https://docs.getdbt.com/docs/local/install-dbt).
You will need the following:
* A GitHub account.
* Python 3.8+.
## Quickstart
This guide shows you how to set up DBT with Firebolt and run your first DBT [model](https://docs.getdbt.com/docs/build/models).
### Setup DBT Core
1. Create a new Python [virtual environment](https://docs.python.org/3/library/venv.html), as shown in the following script example:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
python3 -m venv dbt-env
```
2. Activate your `venv`, as shown in the following script example:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
source dbt-env/bin/activate
```
3. Install Firebolt's [adapter](https://github.com/firebolt-db/dbt-firebolt) for DBT, as shown in the following script example:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
python -m pip install dbt-firebolt
```
4. (Optional) Check that both dbt packages are installed:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
python -m pip list | grep dbt
```
This command should return `dbt-core` and `dbt-firebolt` and their respective versions.
### Setup connection to Firebolt
DBT uses a `profiles.yml` file to store the connection information. This file generally lives outside of your dbt project to avoid checking in sensitive information in to version control.
The usual place to create this file on Mac and Linux is `~/.dbt/profiles.yml`.
1. Open `~/.dbt/profiles.yml` with your preferred text editor.
2. Paste the following sample configuration:
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
jaffle-shop:
target: dev
outputs:
dev:
type: firebolt
client_id: ""
client_secret: ""
database: ""
engine_name: ""
account_name: ""
schema: ""
```
3. Replace the placeholders with your account's information.
`` and `` are key and secret of your service account. If you don't have one, follow the steps in the [Manage service accounts](/managed-service/organization/service-accounts) page to learn how to set one up.
`` and `` are the Firebolt's database and engine that you want your queries to run.
`` is a Firebolt account that you're connected to. Learn more [here](/guides/managing-your-organization/managing-accounts).
`` is a prefix prepended to your table names. Since Firebolt does not support custom schemas, this prefix serves as a [workaround](https://docs.getdbt.com/docs/core/connect-data-platform/firebolt-setup#supporting-concurrent-development) to prevent table name conflicts during concurrent development.
### Setup Jaffle Shop, a sample dbt project
`jaffle_shop` is a fictional ecommerce store. This dbt project transforms raw data from an app database into a customers and orders model ready for analytics. [This version](https://github.com/firebolt-db/jaffle_shop_firebolt) is designed to showcase Firebolt's integration with DBT.
1. Clone `jaffle-shop-firebolt` repository and change to the newly created directory, as follows:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
git clone https://github.com/firebolt-db/jaffle_shop_firebolt.git
cd jaffle_shop_firebolt
```
2. Ensure your profile is setup correctly:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dbt debug
```
If you're seeing an error here, check that your `profiles.yml` is [set up correctly](#setup-connection-to-firebolt), is in the right directory on your system, and that the [engine](/managed-service/operate-engines). is running.
Also check that you're still in `dbt-env` virtual Python environment that we've [setup earlier](#setup-dbt-core) and that both packages are present.
3. Install dependent packages:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dbt deps
```
4. Run the external table model. If your database is not in `us-east-1` AWS region then refer to the [Readme](https://github.com/firebolt-db/jaffle_shop_firebolt) on how to copy the files.
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dbt run-operation stage_external_sources
```
5. Load sample CSV in your database:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dbt seed
```
6. Run the models:
```shell theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dbt run
```
You should now see the `customers` and `orders` tables in your database, created using dbt models. From here you can explore more of DBT's capabilities, including incremental models, documentation generation, and more, by following the official guides in the section below.
## External table loading strategy
In the previous Jaffle Shop example we used a public Amazon S3 bucket to load data. If your bucket contains sensitive data, you will want to restrict access. Follow our [guide](/guides/loading-data/creating-access-keys-aws) to set up AWS authentication using an ID and secret key.
In your `dbt_project.yml`, you can specify the credentials for your external table in fields `aws_key_id` and `aws_secret_key`, as shown in the following code example:
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
sources:
- name: firebolt_external
schema: "{{ target.schema }}"
loader: S3
tables:
- name:
external:
url: 's3:///'
object_pattern: ''
type: ''
credentials:
aws_key_id:
aws_secret_key:
object_pattern: ''
compression: ''
partitions:
- name:
data_type:
regex: ''
columns:
- name:
data_type:
```
To use external tables, you must define a table as external in your `dbt_project.yml` file. Every external table must contain the fields: `url`, `type`, and `object_pattern`. The Firebolt external table [specification](/reference-sql/commands/data-definition/create-external-table) requires fewer fields than those specified in the dbt documentation.
## "Copy" loading strategy
You can also use [COPY FROM](/reference-sql/commands/data-management/copy-from) to load data from Amazon S3 into Firebolt. It has a simple syntax and doesn't require an exact match with your source data. `COPY_FROM` does not create an intermediate table and writes your data straight into Firebolt so you can start working with it right away.
The copy syntax in dbt closely adheres to the [syntax](/reference-sql/commands/data-management/copy-from#syntax) in Firebolt's `COPY_FROM`.
To use `COPY FROM` instead of creating an external table, set `strategy: copy` in your external source definition. For backwards compatibility, if no strategy is specified, the external table strategy is used by default.
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
sources:
- name: s3
tables:
- name:
external:
strategy: copy
url: 's3:///'
credentials:
aws_key_id:
aws_secret_key:
options:
object_pattern: ''
type: 'CSV'
auto_create: true
```
You can also include the following options:
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
options:
object_pattern: ''
type: 'CSV'
auto_create: true
allow_column_mismatch: false
max_errors_per_file: 10
csv_options:
header: true
delimiter: ','
quote: DOUBLE_QUOTE
escape: '\'
null_string: '\\N'
empty_field_as_null: true
skip_blank_lines: true
date_format: 'YYYY-MM-DD'
timestamp_format: 'YYYY-MM-DD HH24:MI:SS'
```
In the previous code example, `csv_options` are indented. For detailed descriptions of these options and their allowed values, refer to the [parameter specification](/reference-sql/commands/data-management/copy-from#parameters).
## Limitations
Not every feature of DBT is supported in Firebolt. You can find an up-to-date list of features in the [adapter documentation](https://github.com/firebolt-db/dbt-firebolt?tab=readme-ov-file#feature-support).
[DBT Cloud](https://docs.getdbt.com/docs/cloud/about-cloud/dbt-cloud-features) is not supported at the moment.
## Further reading
* [Configuring Firebolt-specific features](https://docs.getdbt.com/reference/resource-configs/firebolt-configs).
* [Incremental models](https://docs.getdbt.com/docs/build/incremental-models).
* [Data tests](https://docs.getdbt.com/docs/build/data-tests).
* [Documenting your models](https://docs.getdbt.com/docs/collaborate/documentation).
# dbt - PostgreSQL adapter (cloud and core)
Source: https://docs.firebolt.io/guides/integrations/dbt-postgres-adaptor
Learn how to connect dbt to Firebolt using PostgreSQL adapter.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![DBT]()
[DBT](https://www.getdbt.com), or Data Build Tool, is a framework designed for managing and executing data transformations within modern data warehousing architectures. It facilitates the development and deployment of SQL-based transformations in a version-controlled environment, enabling collaboration and ensuring reproducibility of data pipelines. DBT streamlines the process of transforming raw data into analytics-ready datasets, accelerating the delivery of insights.
# Connect dbt to Firebolt using the PostgreSQL protocol
This guide explains how to connect **dbt** to **Firebolt** using the **PostgreSQL protocol**.
Firebolt supports two dbt connection paths:
* **dbt Cloud** using the PostgreSQL adapter
* **dbt Core** using the PostgreSQL adapter with mutual TLS (mTLS)
> Note
> Firebolt also provides a native dbt adapter, but it is currently limited to **Firebolt Core only**.
> This guide focuses on the PostgreSQL protocol, which works for both **Cloud** and **Core**.
***
## Overview
When using dbt with Firebolt over the PostgreSQL protocol:
* Firebolt is exposed as a PostgreSQL-compatible endpoint
* Authentication is done using **service accounts**
* Account and engine are provided via the username field
* Username use a **triple identifier** format: `::`
### Prerequisites
Before starting, make sure you have:
1. **dbt Cloud or dbt Core** – Depending on your choice, have access to either a dbt Cloud account or a local installation of dbt Core.
2. **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
3. **Firebolt database and table** – You must have access to a Firebolt database that contains a table with data ready for transformation. If you don't have access, you can [create a database](/overview/quickstart#create-a-database) and then [load data](/guides/loading-data) into it.
4. **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
5. **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
***
## Connect dbt Cloud to Firebolt
ℹ️ **Network access requirement**
dbt Cloud does not support mutual TLS (mTLS). In most cases, connections work without additional setup.
If you encounter connectivity issues, your dbt Cloud IP address may need to be allowlisted by Firebolt. See [Allowlisting dbt Cloud IP Addresses](#allowlisting-dbt-cloud-ip-addresses).
⚠️ dbt Cloud limitation (important)
dbt Cloud enforces a PostgreSQL-style limit on the username length.
When using the new Firebolt PostgreSQL connection model, the full username value: `::` must be 63 characters or fewer.
If this limit is exceeded:
- The connection test may succeed
- But the connection cannot be saved in dbt Cloud
- dbt Cloud displays a generic “Something went wrong” error
Service account ID requirement
Firebolt service accounts with a 57-character client ID cannot be used with dbt Cloud.
To connect dbt Cloud to Firebolt, you must use a service account with a shorter client ID (26 characters).
If the service account you are using has a 57-character client ID,
[create a new service account](/managed-service/organization/commands/create-service-account) with a shorter ID and use it for the connection.
Engine name length
If the combined username exceeds 63 characters due to a long engine name,
rename the engine to a shorter name for use with dbt Cloud.
Note
This limitation is specific to dbt Cloud and PostgreSQL compatibility.
It does not apply to dbt Core.
### Step 1: Create a PostgreSQL connection
1. Go to **Settings → Connections**
2. Click **New connection**
3. Select **PostgreSQL**
* Fill in the required fields:
* **Connection name:** Any descriptive name (for example: `Firebolt PostgreSQL`)
* **Server hostname:** `pg..app.firebolt.io`
* Replace `` with your Firebolt account region (for example: `us-east-1`)
* **Port:** `5432`
* Expand **Optional settings** and set:
* **Database name:** ``
* Replace the placeholders with your account's information:
* Click **Save**.
### Step 2: Create a dbt Cloud project
1. Go to **Projects**
2. Click **+ New project**
3. Enter a **Project name**
4. Continue to **Configure your development environment**
#### Configure development credentials
When setting up the project, dbt Cloud will request **development credentials**.
Firebolt requires **service account credentials**.
Fill the fields as follows:
* **Connection:** Select the PostgreSQL connection created earlier
* **Username:** `::`
* **Password:**` `
* Replace the placeholders with your service account's information:
* ``: Your Firebolt account name
* ``: Your Firebolt engine name
* ``: Client ID of your service account
* ``: Client secret of your service account
Click **Test connection**, then continue with the project setup.
### Allowlisting dbt Cloud IP Addresses
dbt Cloud does not support mutual TLS (mTLS).
If you experience connectivity issues when connecting dbt Cloud to Firebolt, your dbt Cloud IP address may need to be allowlisted by Firebolt.
#### Get your dbt Cloud IP address
dbt Cloud displays the outbound IP addresses directly in the PostgreSQL connection setup screen.
1. While creating or editing the **PostgreSQL** connection in dbt Cloud, scroll to the **Settings** section
2. Locate the message indicating the IP addresses dbt Cloud will connect from.
3. Copy **all IP addresses** listed in that message
#### Request allowlisting
Contact Firebolt Support and request to allowlist the ThoughtSpot IP address for your account.
See more about how to contact [Firebolt Support and the severity guidelines](/support/severity-guidelines).
Include the following information in your request:
* Your name and email address
* Your organization name
* Name of the tool you want to connect (dbt Cloud in this case)
* The dbt Cloud IPs addresses to allowlist
After allowlisting is completed, return to dbt Cloud and retry the connection.
***
## Connect dbt Core to Firebolt (mTLS)
dbt Core connects to Firebolt using:
* PostgreSQL adapter
* Service account credentials
* Mutual TLS (mTLS)
* Full server certificate verification (verify-full)
### Step 1: Generate certificates (client cert + Let’s Encrypt root)
Run the following script to generate all certificates required by dbt Core and Firebolt.
This script:
* Generates a client private key and certificate
* Downloads the Let’s Encrypt root CA used by Firebolt servers
* Derives a public key to attach to the Firebolt service account
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
#!/bin/bash
set -euo pipefail
CLIENT_CN="${CLIENT_CN:-firebolt-dbt}"
DAYS_VALID="${DAYS_VALID:-730}"
OUT_DIR="${OUT_DIR:-./out}"
LE_ROOT_URL="https://letsencrypt.org/certs/isrgrootx1.pem.txt"
mkdir -p "$OUT_DIR"
echo "==> Client CN : $CLIENT_CN"
echo "==> Validity (days) : $DAYS_VALID"
echo "==> Output directory : $OUT_DIR"
# ---- 1) Server root CA (verify Firebolt server cert) ----
echo "==> Downloading Let's Encrypt root (ISRG Root X1)"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"
# Sanity check
openssl x509 -in "$OUT_DIR/isrgrootx1.pem" -noout -subject >/dev/null
# ---- 2) Client private key ----
echo "==> Generating client private key"
openssl genrsa -out "$OUT_DIR/fb-client.key" 2048
# ---- 3) CSR ----
echo "==> Creating CSR"
openssl req -new \
-key "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.csr" \
-subj "/CN=$CLIENT_CN"
# ---- 4) Self-sign client cert with clientAuth extensions ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF
echo "==> Creating self-signed client certificate (clientAuth)"
openssl x509 -req \
-in "$OUT_DIR/fb-client.csr" \
-signkey "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.crt" \
-days "$DAYS_VALID" \
-sha256 \
-extfile "$OUT_DIR/client.ext"
rm -f "$OUT_DIR/fb-client.csr" "$OUT_DIR/client.ext"
# ---- 5) Public key for Firebolt service account ----
echo "==> Deriving public key for Firebolt service account"
openssl pkey \
-in "$OUT_DIR/fb-client.key" \
-pubout \
-out "$OUT_DIR/fb-public.pem"
echo ""
echo "✅ Done. Generated files:"
echo ""
echo "dbt Core / libpq SSL:"
echo " sslrootcert : $OUT_DIR/isrgrootx1.pem"
echo " sslcert : $OUT_DIR/fb-client.crt"
echo " sslkey : $OUT_DIR/fb-client.key"
echo ""
echo "Firebolt service account:"
echo " Public key : $OUT_DIR/fb-public.pem"
```
### Step 2: Configure the Firebolt service account
Create or update a service account with the generated public key:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER SERVICE ACCOUNT ""
SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----';
```
### Step 3: Configure profiles.yml
Example profiles.yml for dbt Core:
```yaml theme={"theme":{"light":"css-variables","dark":"css-variables"}}
firebolt_mtls:
target: dev
outputs:
dev:
type: postgres
host: pg..app.firebolt.io
port: 5432
# Firebolt service account credentials (triple identifier)
user: ::
password:
dbname:
schema: public
threads: 1
# --- SSL / mTLS ---
sslmode: verify-full
sslrootcert: /out/isrgrootx1.pem
sslcert: /out/fb-client.crt
sslkey: /out/fb-client.key
```
Replace the placeholders with your account's information:
* ``: Your Firebolt region (for example: `us-east-1`)
* ``: Client ID of your service account
* ``: Client secret of your service account
* ``: Your Firebolt account name
* ``: Your Firebolt database name
* ``: Your Firebolt engine name
* ``: Path to the directory where the certificates were generated
***
# Troubleshooting, known limitations and workarounds
This section lists known limitations when using Firebolt with dbt via the PostgreSQL protocol, along with recommended workarounds.
These limitations apply to **both dbt Cloud and dbt Core** when using the PostgreSQL adapter.
***
## Dependent views and model rebuild failures
Firebolt does not allow altering or replacing tables that have dependent views.
In dbt, this can cause failures during:
* `dbt run`
* `dbt build`
* `dbt run --full-refresh`
Typical error messages include:
* references to **dependent views**
* failures to **ALTER TABLE** or **RENAME** objects
* messages indicating the object does not exist, even though dbt expects it to
### Why this happens
dbt commonly rebuilds models using the following pattern:
1. Create a temporary relation (`__dbt_tmp`)
2. Rename the existing relation to a backup (`__dbt_backup`)
3. Rename the temporary relation to the target name
If a **view depends on the target table**, Firebolt blocks the rename operation.
A common workaround is to drop the dependent view before rebuilding the table.
However, **dropping views using raw SQL (`DROP VIEW`) in hooks is unsafe**.
Why?
* dbt maintains an internal **relation cache**
* Executing raw SQL does **not update dbt’s cache**
* dbt may still think the view exists and attempt to rename it
* This leads to flaky behavior (for example: every second run fails)
### Recommended workaround (safe and stable)
Use an **adapter-aware macro** to drop dependent views.
This ensures dbt updates its internal cache correctly.
#### 1. Define a helper macro
Create the following macro (for example: `macros/firebolt_drop_view.sql`):
```jinja theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{% macro firebolt_drop_view_if_exists(schema_name, view_name) %}
{% if execute %}
{% set rel = adapter.get_relation(
database=this.database,
schema=schema_name,
identifier=view_name
) %}
{% if rel is not none %}
{% do adapter.drop_relation(rel) %}
{% endif %}
{% endif %}
{% endmacro %}
```
This macro:
* Checks whether the view exists
* Drops it using dbt’s adapter API
* Keeps dbt’s internal state consistent
#### 1. Call the macro from a model pre-hook
Apply the pre-hook to the model that owns the base table (for example, orders).
Using model config in SQL:
```jinja theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{{
config(
pre_hook=[
"{{ firebolt_drop_view_if_exists('public', 'dependent_view_name') }}"
]
)
}}
```
Or using YAML configuration:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
models:
- name: orders
pre_hook:
- "{{ firebolt_drop_view_if_exists('public', 'dependent_view_name') }}"
```
Replace `dependent_view_name` with the actual name of the view that depends on this table.
### Note and limitations
* Do not use raw DROP VIEW statements in hooks
* Avoid recreating views manually inside hooks, as this can introduce cycles
* The drop must run on the upstream table model, not on the view itself
***
## Additional resources
For more information about dbt Core and PostgreSQL-based integrations, see the official dbt documentation:
* [dbt documentation home](https://docs.getdbt.com/)
* [Introduction to dbt](https://docs.getdbt.com/docs/introduction)
* [Installing dbt Core](https://docs.getdbt.com/docs/core/installation-overview)
* [Connecting dbt to PostgreSQL](https://docs.getdbt.com/docs/core/connect-data-platform/postgres-setup)
* [Configuring profiles.yml](https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml)
* [Building models in dbt](https://docs.getdbt.com/docs/build/models)
* [dbt materializations](https://docs.getdbt.com/docs/build/materializations)
* [Hooks in dbt (pre-hook and post-hook)](https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook)
These resources cover core dbt concepts, PostgreSQL connections, hooks, and the Semantic Layer, and are useful when running dbt against Firebolt using the PostgreSQL protocol.
# Dot
Source: https://docs.firebolt.io/guides/integrations/dot
Connect Dot AI Data Analyst to Firebolt to query your data using natural language.
# Dot integration with Firebolt
[**Dot**](https://getdot.ai) is an AI Data Analyst that connects directly to your database and lets anyone on your team ask questions in plain English. Dot translates natural language into SQL, executes it against your database, and returns answers as charts, tables, or summaries — in Slack, Microsoft Teams, or the Dot web app.
This guide explains how to connect **Dot** to **Firebolt** so your team can query Firebolt data using natural language.
***
## Overview
Dot connects to Firebolt using the [firebolt-sdk](https://github.com/firebolt-db/firebolt-python-sdk) Python driver with service account authentication. All query computation is pushed down to Firebolt — Dot provides the AI layer that translates questions into SQL and presents the results.
Key characteristics of this integration:
* Dot uses the native Firebolt Python SDK (`firebolt-sdk`)
* Authentication uses Firebolt service account credentials (client ID and client secret)
* All SQL execution happens on your Firebolt engine
* Dot syncs your database schema (tables, columns, types) to generate accurate SQL
* Supports natural language queries, automated visualizations, and scheduled reports
***
## Prerequisites
Before starting, make sure you have:
1. **Dot account**
* Sign up at [app.getdot.ai](https://app.getdot.ai)
* Admin access to configure data connections
2. **Firebolt account**
* With access to a database and engine
3. **Firebolt [service account](/managed-service/organization/service-accounts)**
* Client ID and client secret
* A user [associated](/managed-service/organization/service-accounts#create-a-user) with the service account
4. **Permissions**
* [USAGE](/security/rbac/database-permissions) on the database
* [OPERATE](/security/rbac/engine-permissions) on the engine
* SELECT access on the tables you want to query
***
## Connect Dot to Firebolt
### Step 1: Open the connections page in Dot
1. Log in to [Dot](https://app.getdot.ai)
2. Go to **Settings** (gear icon)
3. Click **Connections**
4. Click **Add Connection**
5. Select **Firebolt**
### Step 2: Enter your Firebolt connection details
Fill in the connection form with the following parameters:
| Parameter | Description |
| ----------------- | ------------------------------------------------------------------------------------------------------------ |
| **Account name** | Your Firebolt account name |
| **Client ID** | The client ID of your Firebolt [service account](/managed-service/organization/service-accounts) |
| **Client secret** | The client secret of your Firebolt service account |
| **Database** | The name of the Firebolt database to query |
| **Engine name** | *(Optional)* The name of the Firebolt engine to use. If omitted, the default engine for the database is used |
### Step 3: Connect and sync
1. Click **Connect**
2. Dot will test the connection and sync your database schema
3. Once the sync completes, select which tables and columns to make available for querying
After connecting, Dot automatically discovers your tables, columns, and data types so it can generate accurate SQL for your Firebolt database.
***
## Query pushdown
All SQL queries are executed directly on your Firebolt engine. Dot does not copy or extract your data — it sends SQL to Firebolt and receives only the query results. This means:
* You get the full performance of Firebolt's columnar engine
* Data never leaves your Firebolt environment
* Row-level security and access controls in Firebolt are respected
***
## What you can do with Dot and Firebolt
Once connected, your team can:
* **Ask questions in natural language** — "What were the top 10 customers by revenue last month?" Dot translates it into SQL and runs it on Firebolt.
* **Get automatic visualizations** — Dot generates charts and tables based on query results.
* **Query from Slack or Microsoft Teams** — Ask questions directly in your team's messaging tools without switching to a SQL editor.
* **Schedule recurring reports** — Set up automated queries that run on a schedule and deliver results to Slack or email.
* **Explore data iteratively** — Ask follow-up questions that build on previous results.
***
## Additional resources
* [Dot website](https://getdot.ai)
* [Dot documentation](https://docs.getdot.ai)
* [Sign up for Dot](https://app.getdot.ai)
* [Firebolt Python SDK](https://github.com/firebolt-db/firebolt-python-sdk)
# Embeddable
Source: https://docs.firebolt.io/guides/integrations/embeddable
Learn about connecting Embeddable to Firebolt.
# Embeddable integration with Firebolt
[Embeddable](https://embeddable.com) is a developer-first toolkit for building lightning-fast, fully-custom analytics directly inside your product. Its headless architecture lets you craft analytics that look and feel native, while product, data, and customer-facing teams can iterate on dashboards without engineering bottlenecks.
When paired with **Firebolt**, Embeddable lets you serve interactive dashboards over huge datasets while keeping millisecond-level response times for your end-users.
***
## Prerequisites
Embeddable is a managed SaaS platform, so most deployment tasks are handled for you.
You'll need:
* **Embeddable workspace**: [Set up an account](https://embeddable.com) and create a workspace.
* **Firebolt account**: You need an active [Firebolt account](https://www.firebolt.io/).
* **Firebolt service account** client ID and client secret (see [Service accounts](/managed-service/organization/service-accounts)).
* A Firebolt **database**, **engine**, and **loaded data** you want to visualize (see [Loading data](/guides/loading-data)).
**Network policy**
Ensure the service account's network policy allows ingress from the fixed [Embeddable IP ranges](https://docs.embeddable.com/data/connect-your-database#ip-whitelisting).
***
## Quick-start
### 1. Set up your Embeddable account
Follow the [Quickstart Guide](https://docs.embeddable.com/getting-started/quick-start-guide) to set up your Embeddable workspace and local environment.
### 2. Connect Firebolt
Use the [Connections API](https://docs.embeddable.com/data/connect-your-database) to connect Firebolt, obtaining your API key from the homepage of your Embeddable workspace.
```javascript theme={"theme":{"light":"css-variables","dark":"css-variables"}}
// Example only — store Embeddable API keys and Firebolt credentials securely (env vars or a secrets manager), not in source control.
const apiKey = '';
const connectionName = 'firebolt-prod';
const baseUrl = 'https://api..embeddable.com';
const resp = await fetch(`${baseUrl}/api/v1/connections`, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': `Bearer ${apiKey}`,
},
body: JSON.stringify({
name: connectionName,
type: 'firebolt',
credentials: {
database: '',
account: '',
engine_name: '',
id: '',
secret: '',
},
}),
});
```
### 3. Define your data models
You can now [define your data models](https://docs.embeddable.com/data-modeling/defining-models) in preparation for building customer-facing dashboards, and test these in the Data Playground tab of your Embeddable workspace.
***
## Next steps
Congratulations! You're now ready to model, visualize, and embed.
**Further reading**
* Implementing [row-level security](https://docs.embeddable.com/data-modeling/row-level-security/introduction).
* [Building dashboards](https://docs.embeddable.com/dashboards/building-dashboards).
* [Embedding with JWT](https://docs.embeddable.com/deployment/embedding).
# Estuary
Source: https://docs.firebolt.io/guides/integrations/estuary
Using Estuary to transfer data to Firebolt
![Estuary logo]()
Estuary Flow is a real-time data integration platform designed to streamline the movement and transformation of data between diverse sources and destinations. It provides an event-driven architecture and a user-friendly interface for building pipelines with minimal effort. You can use Flow to set up pipelines to load data from various sources, such as cloud storage and databases, into Firebolt’s cloud data warehouse for low-latency analytics.
This guide shows you how to set up a Flow pipeline that automatically moves data from your Amazon S3 bucket to your Firebolt database using the Estuary Flow user interface (UI). You must have access to an Estuary Flow account, an Amazon S3 bucket, and a Firebolt service account.
## Prerequisites
1. **Estuary Flow account** – You must have access to an active Estuary Flow account. If you do not have access, you can [sign up](https://www.estuary.dev) with Estuary.
2. **Amazon S3 bucket** – you must have access to the following:
* An [AWS Access Key ID and AWS Secret Access Key](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html) for an Amazon S3 bucket.
* The name and path to an [Amazon S3 bucket](https://aws.amazon.com/s3/) that contains your data.
3. **Firebolt service account** –
* Access to an organization in Firebolt. If you don't have access, you can [create an organization](/guides/managing-your-organization/creating-an-organization).
* Access to a Firebolt database and engine. If you don't have access, you can [create a database](/overview/quickstart#create-a-database) and [create an engine](/overview/quickstart#create-an-engine).
* Access to a Firebolt service account, which is used for programmatic access, its [service account ID](/managed-service/organization/service-accounts#get-a-service-account-id) and [secret](/managed-service/organization/service-accounts#generate-a-secret-using-the-ui). If you don't have access, you can [create a service account](/managed-service/organization/service-accounts#create-a-service-account).
## Configure your Estuary Flow source
To set up an Estuary Flow pipeline that automatically moves data from your Amazon S3 bucket, you must create a capture that defines how and where data should be collected. Create a capture for the Estuary Flow source as follows:
1. Sign in to your [Estuary Flow Dashboard](https://dashboard.estuary.dev).
2. Select **Sources** from the left navigation pane.
3. In the **Sources** window, select **+ NEW CAPTURE**.
4. From the list of available connectors, navigate to **Amazon S3**, and select **Capture**.
5. Under **Capture Details**, enter a descriptive name for your capture in the text box under **Name**.
6. Under **Endpoint Config**, enter the following:
1. **AWS Access Key ID** – The AWS account ID associated with the Amazon S3 bucket containing your data.
2. **AWS Secret Access Key** – The AWS secret access key associated with the Amazon S3 bucket containing your data.
3. **AWS Region** – The [AWS region](https://aws.amazon.com/about-aws/global-infrastructure/regions_az/) that contains your Amazon S3 bucket. For example: `us-east-1`.
4. **Bucket** – The name of your Amazon S3 bucket. For example, `firebolt-publishing-public`.
5. **Prefix** (Optional) – A folder or key prefix to restrict the data to a specific path within the bucket. An example prefix structure follows: `/help_center_assets/firebolt_sample_dataset/levels.csv`.
6. **Match Keys** (Optional) – Use a filter to include only specific object keys under the prefix, narrowing the capture's scope.
7. Select the **NEXT** button in the upper-right corner of the page.
8. Test and save your connection as follows:
1. Select **TEST** in the upper-right corner of the page. Estuary will run a test for your capture and display **Success** if it completes successfully.
2. Select **CLOSE** in the bottom-right corner of the page.
3. Select the **SAVE AND PUBLISH** button in the upper-right corner of the page. Estuary will test, save, and publish your capture and display **Success** if it completes successfully.
4. Select **CLOSE** in the bottom-right corner of the page.
## Configure your Estuary Flow destination
To set up an Estuary Flow pipeline that automatically moves data from your Amazon S3 bucket, you must create a materialization that defines how the data should appear in the destination system, including any schema or transformation logic. Create a materialization for the Estuary Flow destination as follows:
1. Select **Destinations** from the left navigation pane.
2. Select the **+ NEW MATERIALIZATION** button in the upper-left corner of the page.
3. Navigate to the **Firebolt** connector and select **Materialization**.
4. Under **Materialization Details**, enter a descriptive name for your materialization in the text box under **Name**.
5. Under **Endpoint Config**, enter the following:
1. **Client ID** – The service account ID for your Firebolt service account.
2. **Client Secret** – The secret for your Firebolt service account.
3. **Account Name** – The name of your service account.
4. **Database** – The name of the Firebolt database where you want to put your data. For example, `my-database`.
5. **Engine Name** – The name of the Firebolt engine to run the queries. For example: `my-engine-name`.
6. **S3 Bucket** – The name of the Amazon S3 bucket to store temporary intermediate files related to the operation of the external table. For example, `my-bucket`.
7. **S3 Prefix** – (Optional) A folder or key prefix to restrict the data to a specific path within the bucket. An example prefix structure follows the format in: `temp_files/`.
8. **AWS Key ID** – The access key ID for the AWS account linked to the Amazon S3 bucket for temporary file storage.
9. **AWS Secret Key** – The AWS secret key associated with the Amazon S3 bucket to store temporary files.
10. **AWS Region** – The [AWS region](https://aws.amazon.com/about-aws/global-infrastructure/regions_az/) of your Amazon S3 bucket. For example: `us-east-1`.
6. Select the **NEXT** button in the upper-right corner of the page.
7. Under **Source Collections**, do the following:
1. Select **Source From Capture**.
2. In the **Captures** window, select the checkbox next to the Amazon S3 source you specified when you configured your Estuary Flow source.
3. Select the **CONTINUE** button in the bottom-right corner of the page.
4. Verify that the **Table** name and type in the **CONFIG** tab under **Resource Configuration** are correct, and update if necessary.
5. (Optional) Choose **Refresh** next to **Field Selection** to preview the fields, their types, and actions that will be written to Firebolt.
8. Test and save your materialization as follows:
1. Select the **TEST** button in the upper-right corner of the page.
Estuary will run a test for your materialization and display **Success** if it completes successfully.
2. Select **CLOSE** in the bottom-right corner of the page.
3. Select the **SAVE AND PUBLISH** button in the upper-right corner of the page. Estuary will test, save, and publish your materialization and display **Success** if it completes successfully.
4. Select **CLOSE** in the bottom-right corner of the page.
## Monitor your materialization
You can monitor your new data pipeline in Estuary Flow's dashboard as follows:
1. Select **Destinations** from the left navigation pane.
2. Select your newly created materialization to view a dashboard with the following tabs:
1. **OVERVIEW** – Provides a high-level summary of the materialization that includes throughput over time.
2. **SPEC** – Displays the configurations and specifications of the materialization that includes schema mapping from the source to destination, the configuration of the destination, and any filters or constrains on the materialized data.
3. **LOGS** – Provides records of materialization activity including success and failure events, messages, and errors.
Ensure that your data is being ingested and transferred as expected.
## Validate your materialization
You can validate that your data has arrived at Firebolt as follows:
1. Log in to the [Firebolt Workspace](https://firebolt.go.firebolt.io/signup).
2. Select the **Develop** icon (**\**) from the left navigation pane.
3. In the **Script Editor**, run a query on the table that you specified as an Estuary Flow destination to confirm the transfer of data as follows:
1. Select the name of the database that you specified as your Estuary Flow destination from the drop-down list next to **Databases**.
2. Enter a script in the script editor to query the table that you specified as an Estuary Flow destination. The following code example returns the contents of all rows and all columns from the `games` table:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT * FROM games
```
You've successfully set up an Estuary Flow pipeline to move data from an Amazon S3 source to a Firebolt destination. Next, explore the following resources to continue expanding your knowledge base.
## Additional resources
* Explore the [core concepts](https://docs.estuary.dev/concepts/) of Estuary Flow.
* Access [tutorials](https://docs.estuary.dev/getting-started/tutorials/) for Estuary Flow including a tutorial on [data transformation](https://docs.estuary.dev/guides/derivation_tutorial_sql/).
* Learn more about Estuary Flow's [command line interface](https://docs.estuary.dev/concepts/flowctl/).
# Hex
Source: https://docs.firebolt.io/guides/integrations/hex
Learn how to connect Hex to Firebolt using the PostgreSQL protocol.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![Hex]()
# Hex integration with Firebolt
[**Hex**](https://hex.tech) is a modern analytics and BI platform that combines SQL, notebooks, dashboards, and AI-powered data exploration. Hex allows teams to explore data, build dashboards, and ask natural-language questions that are automatically translated into SQL queries.
This guide explains how to connect **Hex** to **Firebolt** using the **PostgreSQL protocol** with **mutual TLS (mTLS)** authentication.
***
## Overview
Hex connects to Firebolt through the PostgreSQL-compatible endpoint exposed by Firebolt.
Key characteristics of this integration:
* Hex uses the PostgreSQL protocol
* Authentication is done using Firebolt service accounts
* Connections use mutual TLS (mTLS)
* Account and engine are provided via the username field
* Username use a **triple identifier** format: `::`
* Hex executes SQL directly against Firebolt
* Dashboards, SQL exploration, and Hex AI are supported
* Tables should be located in the `public` schema
***
## Prerequisites
Before starting, make sure you have:
1. **Hex**
* Hex Cloud workspace
* Admin access to configure data connections
2. **Firebolt account**
* With access to a database and engine
3. **Firebolt [service account](/managed-service/organization/service-accounts)**
* Client ID and client secret
* A user [associated](/managed-service/organization/service-accounts#create-a-user) with the service account
4. **Permissions**
* [USAGE](/security/rbac/database-permissions) on the database
* [OPERATE](/security/rbac/engine-permissions) on the engine
***
## Authentication and security
Hex connects to Firebolt using:
* PostgreSQL protocol
* Firebolt service account credentials
* Mutual TLS (mTLS)
This setup ensures:
* Encrypted connections
* Strong client authentication
* Compatibility with standard PostgreSQL drivers used by Hex
***
## Generate mTLS certificates for Hex
### Step 1: Generate certificates
Run the following script to generate the certificates required by **Hex** and Firebolt.
This script:
* Generates a client certificate and private key
* Downloads the **Let’s Encrypt root CA** used by Firebolt servers
* Derives a public key to attach to the Firebolt service account
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
#!/bin/bash
set -euo pipefail
CLIENT_CN="${CLIENT_CN:-firebolt-hex}"
DAYS_VALID="${DAYS_VALID:-730}"
OUT_DIR="${OUT_DIR:-./out}"
LE_ROOT_URL="https://letsencrypt.org/certs/isrgrootx1.pem.txt"
mkdir -p "$OUT_DIR"
echo "==> Client CN : $CLIENT_CN"
echo "==> Validity (days) : $DAYS_VALID"
echo "==> Output directory : $OUT_DIR"
# ---- 1) Server root (verify Firebolt server cert) ----
echo "==> Downloading Let's Encrypt root"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"
# Sanity check
openssl x509 -in "$OUT_DIR/isrgrootx1.pem" -noout -subject >/dev/null
# ---- 2) Client private key ----
echo "==> Generating client private key"
openssl genrsa -out "$OUT_DIR/fb-client.key" 2048
# ---- 3) CSR ----
echo "==> Creating CSR"
openssl req -new \
-key "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.csr" \
-subj "/CN=$CLIENT_CN"
# ---- 4) Self-sign client cert with extensions ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF
echo "==> Creating self-signed client certificate (clientAuth)"
openssl x509 -req \
-in "$OUT_DIR/fb-client.csr" \
-signkey "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.crt" \
-days "$DAYS_VALID" \
-sha256 \
-extfile "$OUT_DIR/client.ext"
rm -f "$OUT_DIR/fb-client.csr" "$OUT_DIR/client.ext"
# ---- 5) Public key for Firebolt service account ----
echo "==> Deriving public key for Firebolt service account"
openssl pkey \
-in "$OUT_DIR/fb-client.key" \
-pubout \
-out "$OUT_DIR/fb-public.pem"
echo ""
echo "✅ Done. Generated files:"
echo ""
echo "Postgres/libpq:"
echo " sslrootcert : $OUT_DIR/isrgrootx1.pem"
echo " sslcert : $OUT_DIR/fb-client.crt"
echo " sslkey : $OUT_DIR/fb-client.key"
echo ""
echo "Firebolt service account:"
echo " Public key : $OUT_DIR/fb-public.pem"
```
### Step 2: Configure the Firebolt service account
Attach the generated public key to your Firebolt service account:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER SERVICE ACCOUNT ""
SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----';
```
## Connect Hex to Firebolt
### Step 1: Create a data connection in Hex
1. Open **Hex**
2. Go to **Settings → Data sources**
3. Click **+ Connection**
4. Select **PostgreSQL**
### Step 2: Configure the PostgreSQL connection
Fill in the connection form as follows.
**Name** A friendly name, for example: `Firebolt`
**Host & port**
* Host: `pg..app.firebolt.io`
* Replace `` with your Firebolt region (for example: `us-east-1`)
* Port: `5432`
**Database**: ``
### Step 3: Authentication (mTLS)
#### Authentication settings
**Type**: Select **Certificate**
**Username**: `::`
Where:
* `` is your Firebolt account name
* `` is your Firebolt engine name
* `` is the client ID of your Firebolt service account
**Password**: ``
#### TLS configuration
Paste the values generated by the certificate script:
* **SSL certificate**
Paste the contents of `fb-client.crt`
* **SSL root certificate**
Paste the contents of `isrgrootx1.pem`
* **SSL key**
Paste the contents of `fb-client.key`
* **SSL password**
Leave empty (the private key is not encrypted)
***
## Test and use the connection
1. Click **Create connection**
2. Hex will automatically test the connection
3. Once the test succeeds, the connection is ready to use
After connecting, you can:
* Run SQL queries directly against Firebolt
* Build SQL-based charts and dashboards
* Create UI dashboards on top of saved queries
* Use **Hex AI** to ask natural-language questions such as:
* “What is the city with the most orders?”
* “What is the average number of order items per order?”
Hex translates these questions into SQL, executes them on Firebolt, and evaluates the results.
***
## Known limitations and workarounds
Hex provides a rich UI for building calculated fields, measures, and formulas on top of connected data sources.
However, not all Hex-generated calculation formulas are currently supported by Firebolt’s PostgreSQL-compatible SQL dialect.
Because Hex dynamically generates SQL for these calculations, some functions may fail at query execution time even though the connection itself is working correctly.
### Calculation functions that may not work
Based on current testing, the following categories of Hex calculation formulas are **not fully supported**:
#### Date & time calculations
* `Diff*` functions (for example: DiffHours, DiffMinutes, DiffSeconds)
* `Second()` and `Millisecond()` extraction
* Some `Trunc*` functions on older Firebolt versions (for example, Firebolt 4.29)
* These work correctly on newer versions (for example, 4.31)
#### String functions
* `Left()`
* `Right()`
* `StartsWith()`
* `EndsWith()` (may be generated using `RIGHT()` internally)
> ⚠️ This list is not guaranteed to be exhaustive.
> Hex may generate additional SQL expressions depending on the UI feature used.
### Recommended workaround
If a calculation or formula does not work in Hex UI:
👉 **Write plain SQL instead of using the Hex formula builder**
Hex allows you to:
* Write raw SQL queries
* Build charts and dashboards on top of SQL results
* Use Firebolt-supported SQL functions directly
This approach avoids unsupported generated SQL and provides full control over query logic.
### Ongoing improvements
Firebolt continues to expand PostgreSQL SQL compatibility.
Additional functions may become supported over time, reducing the need for manual SQL workarounds.
***
## Additional resources
For more details on using **Hex** and configuring PostgreSQL connections, refer to the official Hex documentation:
* [Hex documentation home](https://learn.hex.tech/docs)
* [Connect to PostgreSQL in Hex](https://learn.hex.tech/docs/connect-to-data/data-connections/setup-guides/connect-to-postgres)
These resources provide up-to-date guidance on connecting Hex to databases, managing connections, and working with SQL on top of Firebolt.
# Kafka Sink Connector
Source: https://docs.firebolt.io/guides/integrations/kafka-sink-connector
Learn about moving data from Apache Kafka to Firebolt using Kafka Connect framework.
Firebolt Kafka Connect Sink is a Kafka Connect connector that delivers data from Kafka topics to Firebolt tables.
## Prerequisites
* Apache Kafka 3.2 or later installed in your environment
* (Optional) Confluent Cloud account if deploying on Confluent Cloud
## Features
* Append-only writes with at-least-once delivery semantics
* Schema Registry support for Kafka message values
* Developed and maintained by Firebolt; verified by Confluent
* Supports all Firebolt data types except STRUCT and GEOGRAPHY
## Quickstart
Follow this guide to set up Firebolt Kafka Connect Sink on Confluent Cloud.
### Firebolt details
To connect to Firebolt you need the following information:
* Service account client ID and client secret
* Database name — the database that will contain the tables populated from Kafka topics
* Engine name — the engine that will run INSERT queries
* Account name — the Firebolt account that has access to the database
### Kafka details
* Topic names — the topics that will be synced to Firebolt tables
* Kafka API key and secret — when deployed on Confluent Cloud, used to authenticate to Kafka
* Schema Registry API key and secret — if using Schema Registry on Confluent Cloud, used to authenticate to Schema Registry
### Firebolt connector configuration
1. **Mandatory attributes**
* `firebolt.clientId` — client ID used to authenticate to Firebolt
* `firebolt.clientSecret` — client secret corresponding to the client ID
* `jdbc.connection.url` — JDBC connection URL used to connect to Firebolt. It must include the database name, account name, and engine name.
Do not put the client ID and client secret in the JDBC connection URL; this attribute is not obfuscated when the connector definition is displayed.
* `topics` — comma-delimited list of topics the connector listens to (for example: `mytopic1,mytopic2,mytopic3`)
* `value.converter` — set to `io.confluent.connect.json.JsonSchemaConverter`
* `key.converter` — set to `org.apache.kafka.connect.storage.StringConverter`
2. **Optional attributes**
* `topic.to.table.mapping` — if your topic names do not match your table names, use this property to map topics to tables. It is a comma-separated list of `topic_name:table_name` pairs (for example: `mytopic1:mytable1,mytopic2:mytable2`).
* `value.converter.schema.registry.url` — URL of your Schema Registry if used for the value schema
* `value.converter.basic.auth.credentials.source` — set to `USER_INFO` if using API key/secret to communicate with Schema Registry
* `value.converter.schema.registry.basic.auth.user.info` — credentials in the format `api_key:api_secret`
* `errors.deadletterqueue.topic.name` — dead-letter queue topic for messages that cannot be processed
* `errors.deadletterqueue.context.headers.enable` — set to `true` to include failure context headers in the dead-letter queue
* `errors.tolerance` — set to `all` so that Kafka messages that cannot be processed are sent to the dead-letter queue
### Install Firebolt connector on Confluent Cloud
1. In Confluent Cloud, navigate to the target cluster. Select **Connectors** in the left navigation and search for "Firebolt".
![]()
2. The connector is verified by Confluent but is not managed by Confluent, so you need to download the archive.
![]()
3. Create a new Custom Connector using the downloaded artifact.
![]()
4. Configure the Firebolt connector.
![]()
* Connector plugin name — choose a name for your connector
* Connector class — `com.firebolt.kafka.connect.FireboltSinkConnector` (the custom connector class for Firebolt)
* Type — select `Sink` (Firebolt implements the Sink functionality)
* Connector archive — select the JAR file you downloaded in step 2
* Sensitive properties — Firebolt Connect Sink has two sensitive properties (they are not shown in the UI or via REST):
* `firebolt.clientId` — client ID used to authenticate to Firebolt
* `firebolt.clientSecret` — client secret corresponding to the client ID
4.1 Set up the credentials used to connect to the Kafka cluster
![]()
4.2 Configure the Firebolt connector definition
![]()
Here is sample definition:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{
"firebolt.clientId": "****************",
"firebolt.clientSecret": "****************",
"jdbc.connection.url": "jdbc:firebolt:?account=&engine=",
"topic.to.table.mapping": "mytopic:mytable",
"topics": "mytopic",
"value.converter": "io.confluent.connect.json.JsonSchemaConverter",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"value.converter.basic.auth.credentials.source": "USER_INFO",
"value.converter.schema.registry.basic.auth.user.info": "",
"value.converter.schema.registry.url": "",
"errors.deadletterqueue.context.headers.enable": "true",
"errors.deadletterqueue.topic.name": "",
"errors.tolerance": "all",
"consumer.override.fetch.max.bytes": "20971520",
"consumer.override.max.partition.fetch.bytes": "10485760",
"consumer.override.max.poll.records": "6000",
"fetch.max.bytes": "15000000",
"max.partition.fetch.bytes": "10000000",
"poll.interval.ms": "1000",
"producer.override.max.request.size": "10485760"
}
```
4.3 Configure the outgoing networking endpoints
![]()
4.4 Size your connector workers
![]()
4.5 On the last page of the wizard, review all details from the previous steps and complete the workflow.
5. You should now see the connector running on the Connectors page.
![]()
### Troubleshoot installing Firebolt connector on Confluent Cloud
1. **Networking endpoints troubleshooting** — The Kafka connector needs to know in advance the egress endpoints it will call so it can allowlist those IP addresses.
Set endpoints for Firebolt authentication (`id.app.firebolt.io`) and Firebolt backend API calls (`api.app.firebolt.io`). Some endpoints are dynamic (the account URL is specific to the account in your JDBC URL).
Each endpoint may be served by multiple IPs because a reverse proxy is used in front of the services.
In case you see the status of the connector as failed, then check the Settings page
![]()
Go to the networking section, and you should see an error message. Click on Fix.
![]()
Then click on the Add to allow-list
![]()
Make sure you click then Save Changes on the Networking section and then Apply Changes on the bottom of the Settings page so the changes to be applied.
2. **Log messages with request being too large**
```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
Cannot process the firebolt record from partition xxx at offset yyy, as it is too large and exceeds the Firebolt request entity size
```
Firebolt has a maximum payload size that can be set on an http request. If your Kafka message is, let's say 20KB, and you are ingesting 1000 messages from the same topic, then your payload size would be 20MB.
You have few options to fix this:
* reduce the number of messages that you ingest in a kafka batch by setting this property `consumer.override.max.poll.records` to a smaller value
* contact [support@firebolt.io](mailto:support@firebolt.io) to request an increase in maximum payload request size
## Kafka Sink connector is under development so we will be adding new features in the following versions:
* Change data capture (CDC): not currently supported
* Schema evolution: not currently supported
* Avro format and Kafka message keys with Schema Registry: not currently supported
# LangChain
Source: https://docs.firebolt.io/guides/integrations/langchain
Connect LangChain to Firebolt using the SQLAlchemy connector for natural language interactions with your database.
[LangChain](https://python.langchain.com/) is a framework for developing applications powered by language models. It enables developers to build context-aware applications that can reason about data and take actions. LangChain provides tools for connecting LLMs to various data sources, including databases, making it possible to query and analyze data using natural language.
This guide shows you how to connect LangChain to Firebolt using the SQLAlchemy connector, enabling natural language interactions with your Firebolt database.
## Prerequisites
Before you begin, ensure you have the following prerequisites:
1. **Python installation**: You need Python 3.8 or higher installed on your machine. You can download it from [python.org](https://www.python.org/downloads/).
2. **Firebolt account**: You need an active Firebolt account with a configured database and engine. If you don't have one, you can [sign up](https://go.firebolt.io/signup) for free.
3. **Firebolt credentials**: Create a [service account](/managed-service/organization/service-accounts) in Firebolt and note its client ID and secret.
4. **LLM API key**: You'll need an API key from a supported LLM provider to power the natural language processing. This guide uses OpenAI as an example, but LangChain supports [many other chat model providers](https://python.langchain.com/docs/integrations/chat/). You can obtain an OpenAI API key from the [OpenAI website](https://platform.openai.com/settings/organization/api-keys).
## Connecting to Firebolt with LangChain
### 1. Install Required Packages
Install the necessary Python packages. This example uses OpenAI, but you can install packages for [any LangChain-supported chat model](https://python.langchain.com/docs/integrations/chat/):
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# For OpenAI (example used in this guide)
pip install langchain langchain-openai firebolt-sqlalchemy
# For other providers, install the appropriate package:
# pip install langchain langchain-anthropic firebolt-sqlalchemy # For Anthropic Claude
# pip install langchain langchain-google-genai firebolt-sqlalchemy # For Google Gemini
# pip install langchain langchain-cohere firebolt-sqlalchemy # For Cohere
```
### 2. Set Up Environment Variables
For security best practices, store your credentials as environment variables. This example uses OpenAI, but you can use [any LangChain-supported model](https://python.langchain.com/docs/integrations/chat/):
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
export FIREBOLT_CLIENT_ID="your_client_id"
export FIREBOLT_CLIENT_SECRET="your_client_secret"
export FIREBOLT_ACCOUNT_NAME="your_account_name"
export FIREBOLT_DATABASE="your_database_name"
export FIREBOLT_ENGINE_NAME="your_engine_name"
# For OpenAI (example used in this guide)
export OPENAI_API_KEY="your_openai_api_key"
# For other providers, set the appropriate environment variable:
# export ANTHROPIC_API_KEY="your_anthropic_api_key" # For Anthropic Claude
# export GOOGLE_API_KEY="your_google_api_key" # For Google Gemini
# export COHERE_API_KEY="your_cohere_api_key" # For Cohere
```
### 3. Configure the Connection
Connect to Firebolt using a SQLAlchemy engine. You'll need to provide your Firebolt credentials and the database connection details:
* `client_id`: client ID of your [service account](/managed-service/organization/service-accounts).
* `client_secret`: client secret of your [service account](/managed-service/organization/service-accounts).
* `account_name`: The name of your Firebolt [account](/guides/managing-your-organization/managing-accounts).
* `database`: The name of the [database](/security/rbac/database-permissions) to connect to.
* `engine`: The name of the [engine](/security/rbac/engine-permissions) to run SQL queries on.
The SQLAlchemy connection string is the key component that enables LangChain integration with Firebolt. This example uses OpenAI, but you can substitute with [any LangChain-supported chat model](https://python.langchain.com/docs/integrations/chat/):
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import os
from langchain_community.utilities import SQLDatabase
from langchain_openai import ChatOpenAI # Replace with your preferred provider
from langchain_community.agent_toolkits import create_sql_agent
# 🔐 Credentials - Make sure these are securely stored
FIREBOLT_CLIENT_ID = os.getenv("FIREBOLT_CLIENT_ID")
FIREBOLT_CLIENT_SECRET = os.getenv("FIREBOLT_CLIENT_SECRET")
FIREBOLT_ACCOUNT_NAME = os.getenv("FIREBOLT_ACCOUNT_NAME")
FIREBOLT_DATABASE = os.getenv("FIREBOLT_DATABASE")
FIREBOLT_ENGINE_NAME = os.getenv("FIREBOLT_ENGINE_NAME")
OPENAI_API_KEY = os.getenv("OPENAI_API_KEY")
# 🔗 SQLAlchemy connection string for Firebolt
connection_url = (f"firebolt://{FIREBOLT_CLIENT_ID}:{FIREBOLT_CLIENT_SECRET}@{FIREBOLT_DATABASE}/"
f"{FIREBOLT_ENGINE_NAME}?account_name={FIREBOLT_ACCOUNT_NAME}")
llm = ChatOpenAI(temperature=0, openai_api_key=OPENAI_API_KEY, model_name="gpt-4o")
# For other providers, use the appropriate class:
# from langchain_anthropic import ChatAnthropic
# llm = ChatAnthropic(model="claude-3-sonnet-20240229", anthropic_api_key=os.getenv("ANTHROPIC_API_KEY"))
#
# from langchain_google_genai import ChatGoogleGenerativeAI
# llm = ChatGoogleGenerativeAI(model="gemini-pro", google_api_key=os.getenv("GOOGLE_API_KEY"))
db = SQLDatabase.from_uri(connection_url, schema="public")
agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)
```
## Usage Examples
### Basic Database Analysis
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# Analyze your dataset structure
prompt = "Analyze the dataset I have in the database. What tables do I have and what data do they contain?"
response = agent_executor.invoke(prompt)
print(response["output"])
```
### Natural Language Queries
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# Query data using natural language
prompt = "What are the top 5 countries by ad revenue?"
response = agent_executor.invoke(prompt)
print(response["output"])
# More complex analytical queries
prompt = "Show me the monthly trend of sales for the last 6 months, grouped by product category"
response = agent_executor.invoke(prompt)
print(response["output"])
```
### Advanced Analytics
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# Statistical analysis
prompt = "Calculate the correlation between marketing spend and revenue by region"
response = agent_executor.invoke(prompt)
print(response["output"])
# Data quality checks
prompt = "Identify any data quality issues in the customer table, such as missing values or duplicates"
response = agent_executor.invoke(prompt)
print(response["output"])
```
## Compatibility and Limitations
* **LangChain Versions**: This integration is compatible with LangChain 0.1.0 and later versions.
* **Performance**: For large datasets, consider using appropriate filters and limits in your natural language prompts to optimize query performance.
* **Token Limits**: Be aware of LLM token limits when working with large schema descriptions or query results.
## Further Reading
* Learn more about [LangChain SQL agents](https://python.langchain.com/docs/integrations/tools/sql_database/) and their capabilities
* Explore [LangChain chat model integrations](https://python.langchain.com/docs/integrations/chat/) to see all supported LLM providers
* Review the [Firebolt SQLAlchemy documentation](https://github.com/firebolt-db/firebolt-sqlalchemy/blob/main/README.md) for advanced connection options
* Visit [LangChain's documentation](https://python.langchain.com/) for more advanced use cases and integrations
# Lightdash
Source: https://docs.firebolt.io/guides/integrations/lightdash
Learn how to connect dbt to Firebolt using PostgreSQL adapter.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![lightdash]()
# Lightdash integration with Firebolt
[**Lightdash**](https://www.lightdash.com) is an open-source BI platform that enables teams to explore, visualize, and share insights on top of dbt projects. Lightdash relies entirely on dbt metadata and executes queries using standard database connectors.
This guide explains how to connect **Lightdash** to **Firebolt** using the **PostgreSQL protocol** with **mutual TLS (mTLS)** authentication.
***
## Overview
Lightdash connects to Firebolt through the PostgreSQL-compatible endpoint exposed by Firebolt.
Key characteristics of this integration:
* Lightdash uses the PostgreSQL protocol
* Authentication is done using Firebolt service accounts
* Connections use mutual TLS (mTLS)
* Account and engine are provided via the username field
* Username use a **triple identifier** format: `::`
* Lightdash relies on dbt to compile models and generate SQL
**Note**
Because Lightdash fully relies on dbt to execute queries, the prerequisites and constraints are very similar to connecting **dbt Cloud or dbt Core** to Firebolt using the PostgreSQL adapter.
***
## Prerequisites
Before starting, make sure you have:
1. **Lightdash**
* Cloud or self-hosted deployment
2. **Firebolt account**
* With access to a database and engine
3. **Firebolt [service account](/managed-service/organization/service-accounts)**
* Client ID and client secret
* A user [associated](/managed-service/organization/service-accounts#create-a-user) with the service account
4. **Permissions**
* [USAGE](/security/rbac/database-permissions) on the database
* [OPERATE](/security/rbac/engine-permissions) on the engine
5. **dbt project**
* Models already built in Firebolt
***
## Authentication and security
Lightdash connects to Firebolt using:
* PostgreSQL protocol
* Firebolt service account credentials
* Mutual TLS (mTLS)
* Full server certificate verification (verify-full)
Firebolt server certificates are issued by Let’s Encrypt, so the Let’s Encrypt root CA must be provided to Lightdash.
From a security perspective, this setup is equivalent to **dbt Core over PostgreSQL**, with Lightdash acting as the client.
***
## Generate mTLS certificates for Lightdash
### Step 1: Generate certificates
Run the following script to generate all certificates required by Lightdash and Firebolt.
This script:
* Generates a client private key and certificate
* Downloads the Let’s Encrypt root CA used by Firebolt servers
* Derives a public key to attach to the Firebolt service account
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
#!/bin/bash
set -euo pipefail
CLIENT_CN="${CLIENT_CN:-firebolt-lightdash}"
DAYS_VALID="${DAYS_VALID:-730}"
OUT_DIR="${OUT_DIR:-./out}"
LE_ROOT_URL="https://letsencrypt.org/certs/isrgrootx1.pem.txt"
mkdir -p "$OUT_DIR"
echo "==> Client CN : $CLIENT_CN"
echo "==> Validity (days) : $DAYS_VALID"
echo "==> Output directory : $OUT_DIR"
# ---- 1) Download Firebolt server root CA (Let's Encrypt) ----
echo "==> Downloading Let's Encrypt root CA"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"
openssl x509 -in "$OUT_DIR/isrgrootx1.pem" -noout -subject >/dev/null
# ---- 2) Client private key ----
echo "==> Generating client private key"
openssl genrsa -out "$OUT_DIR/fb-client.key" 2048
# ---- 3) CSR ----
echo "==> Creating CSR"
openssl req -new \
-key "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.csr" \
-subj "/CN=$CLIENT_CN"
# ---- 4) Self-signed client certificate ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF
echo "==> Creating client certificate"
openssl x509 -req \
-in "$OUT_DIR/fb-client.csr" \
-signkey "$OUT_DIR/fb-client.key" \
-out "$OUT_DIR/fb-client.crt" \
-days "$DAYS_VALID" \
-sha256 \
-extfile "$OUT_DIR/client.ext"
rm -f "$OUT_DIR/fb-client.csr" "$OUT_DIR/client.ext"
# ---- 5) Public key for Firebolt service account ----
echo "==> Deriving public key for Firebolt service account"
openssl pkey \
-in "$OUT_DIR/fb-client.key" \
-pubout \
-out "$OUT_DIR/fb-public.pem"
echo ""
echo "Done. Generated files:"
echo ""
echo "Lightdash:"
echo " sslrootcert : $OUT_DIR/isrgrootx1.pem"
echo " sslcert : $OUT_DIR/fb-client.crt"
echo " sslkey : $OUT_DIR/fb-client.key"
echo ""
echo "Firebolt service account:"
echo " Public key : $OUT_DIR/fb-public.pem"
```
### Step 2: Configure the Firebolt service account
Attach the generated public key to your Firebolt service account:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER SERVICE ACCOUNT ""
SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----';
```
***
## Connect Lightdash to Firebolt
### Step 1: Create a Lightdash project
1. Open **Lightdash**
2. Click **Create project**
3. Select **PostgreSQL** as the warehouse
4. Choose **Manual setup**
### Step 2: Configure the PostgreSQL connection
Fill in the connection details as follows.
**Host:** `pg..app.firebolt.io`
* Replace `` with your Firebolt region (for example: `us-east-1`)
**User:** `::`
* Where:
* `` is your Firebolt account name
* `` is your Firebolt engine name
* `` is the client ID of your Firebolt service account
**Password:** ``
**DB name:** ``
### Step 3: Advanced connection options (mTLS)
Expand **Advanced connection options** and configure:
* **Port:** `5432`
* **SSL mode:** `verify-full`
* **SSL certificate:** upload `fb-client.crt`
* **SSL private key:** upload `fb-client.key`
* **SSL root certificate:** upload `isrgrootx1.pem`
***
## Integrate with your dbt project
When prompted to integrate Lightdash with dbt:
* Select your dbt project
***
## Test and deploy
1. Click **Test connection**
2. Verify the connection succeeds
3. Deploy the project
Once deployed, Lightdash will:
* Read dbt metadata
* Generate SQL queries
* Execute them against Firebolt via pg\_fire
***
## Additional resources
For more details on using **Lightdash**, see:
* [Lightdash documentation home](https://docs.lightdash.com)
* [Lightdash + dbt projects reference](https://docs.lightdash.com/references/dbt-projects)
* [Exploring data in Lightdash](https://docs.lightdash.com/explore)
These resources provide additional guidance on working with Lightdash and dbt-based analytics on top of Firebolt.
# Looker Cloud
Source: https://docs.firebolt.io/guides/integrations/looker-cloud
Connect Looker Cloud to Firebolt using the PostgreSQL dialect.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![Looker Cloud]()
Looker Cloud is a cloud-native business intelligence platform that enables teams to model, explore, and visualize their data. Using Looker’s PostgreSQL dialect, you can connect Looker Cloud directly to Firebolt. This integration allows analysts and business users to query Firebolt’s high-performance data warehouse from within Looker dashboards and Explores.
This guide walks through prerequisites, permissions, and connection setup. By the end, Looker Cloud will be connected to Firebolt and ready to run queries.
ℹ️ **Network access requirement**
Looker cloud does not support mutual TLS (mTLS). In most cases, connections work without additional setup.
If you encounter connectivity issues, your Looker cloud IP address may need to be allowlisted by Firebolt. See [Allowlisting Looker cloud IP Addresses](#allowlisting-looker-cloud-ip-addresses).
### Prerequisites
Before starting, make sure you have:
1. **Looker Cloud Account** – With admin access to configure database connections.
2. **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
3. **Firebolt database and table** – You must have access to a Firebolt database that contains a table with data ready for visualization. If you don't have access, you can [create a database](/overview/quickstart#create-a-database) and then [load data](/guides/loading-data) into it.
4. **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
5. **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
### Configure the Connection in Looker Cloud
#### Navigation path
1. In Looker, go to **Admin → Database → Connections**.
2. Click **Add connection** (top-right).
3. Follow the connection wizard, which is split into four steps.
#### Step 1: General settings
* **Name** – A name for the connection (for example: `firebolt_connection`).
* **SQL Dialect** – Select **PostgreSQL 9.5+**.
Click **Next**.
#### Step 2: Database settings
Fill in the following fields:
| Field | Value |
| ----------------- | --------------------------------------------------- |
| **Host** | `pg..app.firebolt.io` |
| **Port** | `5432` |
| **Database name** | `` |
| **Username** | `::` |
| **Password** | `` |
Click **Next**.
##### **Host details**
The host is based on your Firebolt region. Example:
```
pg.us-east-1.app.firebolt.io
```
Confirm your region with:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT region
FROM information_schema.accounts
WHERE account_name = '';
```
##### **Database name**
The Database name field must contain only the Firebolt database name.
##### **Username format**
The Username field must use the following format:
```
::
```
Where:
* `` is your Firebolt account name
* `` is your Firebolt engine name
* `` is the ID of your Firebolt service account
> **Deprecated**
> Older connection setups encoded the account and engine in the database field using:
> `@@`
> This format is deprecated and should not be used for new connections. While SQL execution may still work, metadata-driven features may behave incorrectly.
#### Step 3: Optional settings
No additional configuration is required.
Click **Next**.
#### Step 4: Review
* Optionally click Test connection to validate the setup.
* Click **Save** to create the connection.
## Allowlisting Looker Cloud IP Addresses
Looker Cloud does not support mutual TLS (mTLS).
Looker Cloud runs on managed infrastructure and sends outbound database traffic from a fixed set of public IP addresses.
If your Firebolt account restricts inbound network access, these IP addresses must be allowlisted.
If you experience connectivity issues when connecting Looker Cloud to Firebolt, your Looker Cloud IP address may need to be allowlisted by Firebolt.
### Get your Looker Cloud IP address
1. In Looker, go to **Admin → Database → Connections**.
2. In the top-right corner, click **Public IP Addresses**.
3. Copy the list of IP addresses displayed.
### Request allowlisting
Contact Firebolt Support and request to allowlist the Looker Cloud IP address for your account.
See more about how to contact [Firebolt Support and the severity guidelines](/support/severity-guidelines).
Include the following information in your request:
* Your name and email address
* Your organization name
* Name of the tool you want to connect (Looker Cloud in this case)
* The Looker Cloud IP address to allowlist
After allowlisting is completed, return to Looker Cloud and retry the connection.
## Performance and Limits
Firebolt enforces soft rate limits to ensure fair usage:
| Limit type | Threshold | Scope |
| :-------------- | :------------- | :----------------------- |
| New connections | 600 per minute | Per IP address |
| Queries | 600 per minute | Per organization/account |
These limits are not hard blocks. Contact Support if you need them raised (provide org name, workload, and requested threshold).
## Compatibility Notes
Some Looker SQL and LookML features are not fully supported through Firebolt’s PostgreSQL adapter.
* **Unsupported functions**: `diff_days()`, `diff_hours()`, `contains()`, `exp()`.
* **Partially supported functions**: `extract_minutes()`, `trunc_months()` (only work with TIMESTAMP/TIMESTAMPTZ, not DATE).
* **Unsupported metrics**: Median, list of unique values.
* **Unsupported dimension types**: Any `date_...` or `duration_...` types. Use custom SQL dimensions in `.view.lkml` instead like:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dimension: diff_days_now {
sql: CAST(DATE_DIFF('day', DATE_TRUNC('day', table."column"), CURRENT_TIMESTAMP) AS BIGINT);;
}
```
# Looker On-Prem
Source: https://docs.firebolt.io/guides/integrations/looker-on-prem
Connect Looker On-Prem to Firebolt using the PostgreSQL dialect.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![Looker On-Prem]()
# Connect Looker On‑Prem to Firebolt with mTLS (PostgreSQL Wire)
This guide covers **mutual TLS (mTLS)** setup for **Looker On‑Prem** connecting to **Firebolt** over the PostgreSQL‑compatible interface.
***
## Prerequisites
1. **Looker On-Prem** – With admin access to configure database connections.
2. **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
3. **Firebolt database and table** – You must have access to a Firebolt database that contains a table with data ready for visualization. If you don't have access, you can [create a database](/overview/quickstart#create-a-database) and then [load data](/guides/loading-data) into it.
4. **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
5. **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
***
## Step 1: Prepare SSL Certificate Files (mTLS)
You need a **client certificate** and a **client private key in PKCS#8 DER** format. You will also generate a **public key** from the private key and attach it to the Firebolt service account.
### If you need to generate certificates
The script below creates a local CA (Certificate Authority), generates a PKCS#8 private key, a public key for Firebolt, and a signed client certificate.
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
#!/bin/bash
set -e
CLIENT_CN="firebolt.looker"
DAYS_VALID=730
CA_DIR="./fb-ca"
# Create CA (optional if using company CA)
mkdir -p "$CA_DIR"
openssl genrsa -out "$CA_DIR/ca.key" 4096
openssl req -x509 -new -nodes -key "$CA_DIR/ca.key" -sha256 -days "$DAYS_VALID" \
-out "$CA_DIR/ca.crt" -subj "/CN=$CLIENT_CN"
# Create RSA key and convert to PKCS#8 DER
openssl genrsa -out fb-rsa.key 2048
openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in fb-rsa.key -out fb.pk8
# Public key for Firebolt service account
openssl pkey -in fb.pk8 -inform DER -pubout -out fb-public.pem
# CSR and client certificate signed by the CA
openssl req -new -key fb-rsa.key -out fb.csr -subj "/CN=$CLIENT_CN"
openssl x509 -req -in fb.csr -CA "$CA_DIR/ca.crt" -CAkey "$CA_DIR/ca.key" -CAcreateserial \
-out fb.crt -days "$DAYS_VALID" -sha256
# Cleanup
rm -rf fb.csr fb-rsa.key $CA_DIR
```
Generated files:
* `fb.pk8` – private key (PKCS#8 DER)
* `fb.crt` – client certificate
* `fb-public.pem` – public key to attach to your Firebolt service account
### If you already have the certificates
Expected files:
* `client-cert.pem` — client certificate
* `client-key.pk8` — client private key (**PKCS#8 DER**)
**Convert a PEM key to PKCS#8 DER (if needed):**
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
openssl pkcs8 -topk8 -inform PEM -outform DER \
-in client-key.pem -out client-key.pk8 -nocrypt
```
**Generate a public key from the PKCS#8 key (attach to Firebolt):**
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
openssl pkey -inform DER -in client-key.pk8 -pubout -out client-public.pem
```
## Step 2: Place the certificate files on the Looker host
> Upload **only the public key** to Firebolt. Do not share the private key.
**Place files on the Looker server and set permissions:**
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
# Paths
/path/to/client-cert.pem
/path/to/client-key.pk8
# Permissions
chmod 600 /path/to/client-key.pk8
chmod 644 /path/to/client-cert.pem
# Optional ownership if Looker runs as user "looker"
chown looker:looker /path/to/client-cert.pem /path/to/client-key.pk8
```
***
## Step 3: Attach the Public Key to the Firebolt Service Account
Attach the generated public key to the Firebolt service account you will use from Looker.
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER SERVICE ACCOUNT "your_account" SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----';
```
> Ensure the service account is attached to a user in the Firebolt account and has privileges to access the target database/engine.
***
## Step 4: Create the Looker On‑Prem Connection (mTLS)
In Looker: **Admin → Connections → New Connection**
| Key | Value |
| -------------------------- | ----------------------------------------------------------------------------------------------------------------- |
| Name | `firebolt_connection` (or preferred name) |
| Dialect | PostgreSQL 9.5+ |
| Host | `pg..app.firebolt.io` |
| Port | `5432` |
| Database | `` |
| Username | `::` |
| Password | `` |
| SSL | Enabled |
| Verify SSL | Disabled |
| Additional JDBC parameters | `sslmode=require&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslcert=/path/to/fb.crt&sslkey=/path/to/fb.pk8` |
### Determine the correct Firebolt host (region)
Example: `pg.us-east-1.app.firebolt.io`.
Find your region:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT region FROM information_schema.accounts WHERE account_name = '';
```
### Username field format
The Username field must use the following format:
```
::
```
Where:
* `` is your Firebolt account name
* `` is your Firebolt engine name
* `` is the ID of your Firebolt service account
> **Deprecated**
> Older connection setups encoded the account and engine in the database field using:
> `@@`
> This format is deprecated and should not be used for new connections. While SQL execution may still work, metadata-driven features may behave incorrectly.
***
## Final Steps & Troubleshooting
1. Click **Test**; if successful, click **Connect**.
2. If the test fails, verify:
* Absolute paths to `sslcert` and `sslkey` exist and are readable by the Looker process.
* The private key is **PKCS#8 DER** (`*.pk8`).
* The service account’s **public key** is attached in Firebolt.
* The host region, account, database, and engine are correct.
* The service account has the necessary permissions and the ID/secret are correct.
***
## Performance and Limits
Firebolt enforces soft rate limits to ensure fair usage:
| Limit type | Threshold | Scope |
| :-------------- | :------------- | :----------------------- |
| New connections | 600 per minute | Per IP address |
| Queries | 600 per minute | Per organization/account |
These limits are not hard blocks. Contact Support if you need them raised (provide org name, workload, and requested threshold).
## Compatibility Notes
Some Looker SQL and LookML features are not fully supported through Firebolt’s PostgreSQL adapter.
* **Unsupported functions**: `diff_days()`, `diff_hours()`, `contains()`, `exp()`.
* **Partially supported functions**: `extract_minutes()`, `trunc_months()` (only work with TIMESTAMP/TIMESTAMPTZ, not DATE).
* **Unsupported metrics**: Median, list of unique values.
* **Unsupported dimension types**: Any `date_...` or `duration_...` types. Use custom SQL dimensions in `.view.lkml` instead like:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
dimension: diff_days_now {
sql: CAST(DATE_DIFF('day', DATE_TRUNC('day', table."column"), CURRENT_TIMESTAMP) AS BIGINT);;
}
```
# MCP Server
Source: https://docs.firebolt.io/guides/integrations/mcp
Use Firebolt MCP Server to enable AI-powered workflows with LLMs.
Firebolt MCP Server is a lightweight service that enables **large language models (LLMs)** like Claude, GitHub Copilot Chat, and Cursor to connect to Firebolt in a secure, context-aware way. It acts as a bridge between your data warehouse and AI assistants, allowing them to:
* Understand Firebolt-specific SQL
* Query your databases with context-aware prompts
* Access technical documentation, metadata, and live data
It's designed for developers, analysts, and teams who want to integrate Firebolt into AI-driven workflows or copilots with minimal setup.
Full setup instructions, environment variables, and integration guides are available on the [Firebolt MCP GitHub repository](https://github.com/firebolt-db/mcp-server).
## When to Use MCP Server
Use Firebolt MCP Server if you want to:
* Enable LLMs to write and execute SQL against your Firebolt environment
* Automate documentation lookups and metadata extraction
* Create advanced AI agents that can explore, troubleshoot, and analyze Firebolt data
Typical use cases include:
* Querying Firebolt using natural language
* Custom copilots in VSCode or Cursor with deep Firebolt context
* AI workflows that require real-time access to Firebolt SQL features
## Getting Started
MCP Server is available as both a binary and a Docker container. To run it, you’ll need:
* A [Firebolt service account](/managed-service/organization/service-accounts) (Client ID and Secret)
* Either Docker or a supported OS for running Go binaries
* An LLM client that supports [Model Context Protocol (MCP)](https://modelcontextprotocol.io)
# Metabase Cloud
Source: https://docs.firebolt.io/guides/integrations/metabase-cloud
Connecting Metabase Cloud and Firebolt.
![Metabase]()
[Metabase](https://www.metabase.com/) is an open-source business intelligence platform. You can use Metabase's user interface to explore, analyze, and visualize data, query databases, generate reports, and create dashboards.
This guide shows you how to [set up a Firebolt connector](#set-up-a-connector-to-metabase) for a the managed or a cloud-hosted version of [**Metabase Cloud**](https://www.metabase.com/docs/latest/cloud/start). If you are using a self-hosted Metabase instance, you can refer to the [Metabase On-Prem guide](/guides/integrations/metabase-on-prem) for setup instructions.
## Step 1: Prepare SSL Certificate Files
In order to connect Metabase Cloud to Firebolt you need certificate-based authentication, which requires SSL certificate files. You can either use existing certificates or generate new ones.
### If You Already Have the Certificates
If you already have the following files:
* `client-cert.pem` — your client certificate
* `client-key.pk8` — your private key (must be in PKCS#8 DER format)
Follow these steps:
#### Ensure your private key is in PKCS#8 DER format (if needed)
If your private key is in traditional PEM format, you must convert it to PKCS#8 DER (required by PostgreSQL JDBC and Firebolt):
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.pk8 -nocrypt
```
#### Generate a public key from the PKCS#8 private key
To access Firebolt using certificate-based auth, you must attach the public key to the Firebolt service account. Generate the public key as follows:
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
openssl pkey -in client-key.pk8 -pubout -out client-public.pem
```
🔐 This public key must be uploaded to the Firebolt UI or API and linked to the appropriate service account to enable access in [step 2](#step-2-set-up-the-firebolt-service-account--attach-the-public-key).
⚠️ **Never share the private key. Only the public key (client-public.pem) should be attached to Firebolt.**
### If You Need to Generate the Certificates
If you don’t have an existing certificate and key pair, you can generate them using the Bash script below. This will:
Create a local Certificate Authority (CA)
Generate a private key in PKCS#8 DER format (fb.pk8)
Generate a public key required by Firebolt
Sign a client certificate (fb.crt) using the CA
⚠️ If you already have a company CA, you can skip the CA generation step and use your organization’s ca.crt and ca.key files instead. Just update the CA\_DIR variable in the script accordingly and make sure the filenames are exactly:
ca.crt
ca.key
Certificate Generation Script
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
#!/bin/bash
set -e # Exit on error
# === CONFIG ===
CLIENT_CN="jdbc.firebolt.pg-fire"
DAYS_VALID=730
CA_DIR="./fb-ca"
# === Create CA (optional) ===
echo "📜 Creating Certificate Authority (CA)..."
mkdir -p "$CA_DIR"
openssl genrsa -out "$CA_DIR/ca.key" 4096
openssl req -x509 -new -nodes -key "$CA_DIR/ca.key" -sha256 -days "$DAYS_VALID" -out "$CA_DIR/ca.crt" -subj "/CN=$CLIENT_CN"
# === Create RSA Key ===
echo "🔐 Generating raw RSA key..."
openssl genrsa -out fb-rsa.key 2048
# === Convert to PKCS#8 DER (.pk8) ===
echo "📦 Converting to PKCS#8 DER format..."
openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in fb-rsa.key -out fb.pk8
# === Create public Key ===
echo "🔐 Generating public key..."
openssl pkey -in fb.pk8 -inform DER -pubout -out fb-public.pem
# === Create CSR using original RSA key (matches .pk8) ===
openssl req -new -key fb-rsa.key -out fb.csr -subj "/CN=$CLIENT_CN"
# === Sign Client Certificate ===
openssl x509 -req -in fb.csr -CA "$CA_DIR/ca.crt" -CAkey "$CA_DIR/ca.key" -CAcreateserial -out fb.crt -days "$DAYS_VALID" -sha256
# === Cleanup ===
rm -rf fb.csr fb-rsa.key $CA_DIR
echo "✅ Done."
```
#### Files Generated
* `fb.pk8` – Private key (PKCS#8 format)
* `fb.crt` – Client certificate
* `fb-public.pem` – Public key to attach to your Firebolt service account
## Step 2: Set Up the Firebolt Service Account & Attach the Public Key
For Metabase to connect to Firebolt using certificate authentication, the public key generated in Step 1 must be attached to a Firebolt service account. You can either use an existing service account or create a new one.
### Optional: Create a New Service Account
If you prefer to create a dedicated service account for Metabase access, follow the steps from the official documentation: [Create a Service Account](https://docs.firebolt.io/Guides/managing-your-organization/service-accounts.html#create-a-service-account)
⚠️ **NOTE:** Ensure the service account is attached to a user to access the Firebolt account.
### Associate the public key with the Firebolt service account
1. Copy the contents of your public key file (`client-public.pem` or `fb-public.pem`).
2. In Firebolt, run the following SQL to attach the public key:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
ALTER SERVICE ACCOUNT "your_service_account_name" SET public_key='-----BEGIN PUBLIC KEY-----
-----END PUBLIC KEY-----';
```
## Step 3: Create a Connection to Metabase
After setting up the Firebolt connector, use the following steps to create a connection between Metabase and your Firebolt database:
1. Open your Metabase instance's home page in a web browser.
2. Select **Settings** from the top-right menu of the Metabase interface.
3. Select **Admin** from the dropdown menu.
4. On the **Admin** page, select **Databases** in the top navigation bar.
5. Select the **Add Database** button.
6. From the **Database Type** dropdown list, select **PostgreSQL**.
7. Fill out the required connection details using the descriptions provided in the following table:
| Field | Description |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| **Display Name** | A name to identify your database in Metabase. Use the same name as your Firebolt database for simplicity. |
| **Host** | `pg..app.firebolt.io` |
| **Port** | `5432` |
| **Database name** | `@@` |
| **Username** | The [service account ID](/managed-service/organization/service-accounts#get-a-service-account-id) associated with your Firebolt database. |
| **Password** | The [secret for the service account](/managed-service/organization/service-accounts#generate-a-secret) associated with your Firebolt database. |
#### Host details
The host is based on your Firebolt region. Example:
```
pg.us-east-1.app.firebolt.io
```
Confirm your region with:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT region
FROM information_schema.accounts
WHERE account_name = '';
```
8. Enable SSL by checking the **Use a secure connection (SSL)** option and make sure SSL mode is set to **Require**.
9. Enable **Authenticate client certificate?** and select the following files from Step 1:
* **SSL Client Certificate (PEM)**: `fb.crt` you generated or a certificate you already had
* **SSL Client Key (PKCS-8/DER)**: `client-key.pem` you generated or a key you already had
10. Select **Save** to store your database configuration.
11. Verify the connection by confirming that Metabase displays a success message indicating that your Firebolt database has been added successfully. If the connection fails, double-check your settings and ensure all required fields are correct.
## Additional Resources
For more information about Metabase configuration and troubleshooting, refer to the following resources:
* [**Adding and Managing Databases**](https://www.metabase.com/docs/latest/databases/connecting) — Official Metabase documentation on connecting to data sources and managing database connections.
* [**Troubleshooting Database Connections**](https://www.metabase.com/docs/latest/troubleshooting-guide/db-connection) — Guidance on resolving issues when connecting [Metabase](https://www.metabase.com/docs/latest/databases/connecting) to your databases.
* [**Troubleshooting Database Performance**](https://www.metabase.com/docs/latest/troubleshooting-guide/db-performance) — Tips for identifying and addressing performance issues with connected databases.
# Metabase On Prem
Source: https://docs.firebolt.io/guides/integrations/metabase-on-prem
Connecting Metabase and Firebolt.
![Metabase On-Prem]()
[Metabase](https://www.metabase.com/) is an open-source business intelligence platform. You can use Metabase's user interface to explore, analyze, and visualize data, query databases, generate reports, and create dashboards.
This guide shows you how to [set up a Firebolt connector](#set-up-a-connector-to-metabase) for a self-hosted Metabase instance and how to [create a connection](#create-a-connection-to-metabase).
You can also watch a short video on how to connect Metabase to Firebolt:
### Set up a connector to metabase
For self-hosted deployments on-premises, the Firebolt connector must be installed manually using the following steps:
1. **Download the Firebolt Metabase driver**
* Go to the [GitHub Releases page for Firebolt](https://github.com/firebolt-db/metabase-firebolt-driver/releases).
* Locate the most recent version of the Firebolt driver, and download it.
2. **Move the driver file to the plugins directory**
* Save the downloaded driver file in the `/plugins` directory on your Metabase host system.
* By default, the `/plugins` directory is located in the same folder where the `metabase.jar` file runs.
After completing these steps, the Firebolt connector will be available for configuration within Metabase.
### Create a connection to metabase
After setting up the Firebolt connector, use the following steps to create a connection between Metabase and your Firebolt database:
1. Open your Metabase instance's home page in a web browser.
2. Select **Settings** from the top-right menu of the Metabase interface.
3. Select **Admin** from the dropdown menu.
4. On the **Admin** page, select **Databases** in the top navigation bar.
5. Select the **Add Database** button.
6. From the **Database Type** dropdown list, select **Firebolt**.
Fill out the required connection details using the descriptions provided in the following table:
| Field | Description |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Display Name** | A name to identify your database in Metabase. Use the same name as your Firebolt database for simplicity. |
| **Client ID** | The [service account ID](/managed-service/organization/service-accounts#get-a-service-account-id) associated with your Firebolt database. |
| **Client Secret** | The [secret for the service account](/managed-service/organization/service-accounts#generate-a-secret) associated with your Firebolt database. |
| **Database name** | Specify the name of the Firebolt database you want to connect to. |
| **Account name** | The name of your Firebolt account, which is required to log in and authenticate your database connection. |
| **Engine name** | Provide the name of the Firebolt engine that will be used to run queries against the database. |
| **Additional JDBC options** | Add any extra parameters needed for the connection, such as `connection_timeout_millis=10000`. For more options, access the [JDBC connection parameters guide](/guides/developing-with-firebolt/connecting-with-jdbc#available-connection-parameters). |
7. Select **Save** to store your database configuration.
Verify the connection by confirming that Metabase displays a success message indicating that your Firebolt database has been added successfully. If the connection fails, double-check your settings and ensure all required fields are correct.
### Additional Resources
For more information about Metabase configuration and troubleshooting, refer to the following resources:
* [**Adding and Managing Databases**](https://www.metabase.com/docs/latest/databases/connecting) — Official Metabase documentation on connecting to data sources and managing database connections.
* [**Troubleshooting Database Connections**](https://www.metabase.com/docs/latest/troubleshooting-guide/db-connection) — Guidance on resolving issues when connecting [Metabase](https://www.metabase.com/docs/latest/databases/connecting) to your databases.
* [**Troubleshooting Database Performance**](https://www.metabase.com/docs/latest/troubleshooting-guide/db-performance) — Tips for identifying and addressing performance issues with connected databases.
# Omni
Source: https://docs.firebolt.io/guides/integrations/omni
Learn how to connect Omni to Firebolt using the PostgreSQL protocol.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![Omni]()
[Omni](https://www.omni.co) is a modern business intelligence and analytics platform that enables teams to explore data, define semantic models, and build interactive dashboards using a SQL-first workflow.
***
# Connect Omni to Firebolt using the PostgreSQL protocol
This guide explains how to connect **Omni** to **Firebolt** using Firebolt’s **PostgreSQL-compatible (pgwire) interface**.
Firebolt is exposed as a PostgreSQL endpoint, which allows Omni to query Firebolt databases using its built-in **Postgres connector**.
## Overview
When using Omni with Firebolt over the PostgreSQL protocol:
* Firebolt is exposed as a PostgreSQL-compatible endpoint
* Authentication is done using **Firebolt service accounts**
* Account and engine are provided via the username field
* Username use a **triple identifier** format: `::`
## Prerequisites
Before starting, make sure you have:
1. **Omni account**
Access to an Omni workspace with permissions to create connections, views, topics, and dashboards.
2. **Firebolt account**
An active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup).
3. **Firebolt database and tables**
Access to a Firebolt database that contains data ready for analysis.
If needed, you can [create a database](/overview/quickstart#create-a-database) and then [load data](/guides/loading-data) into it.
4. **Firebolt service account**
An active Firebolt [service account](/managed-service/organization/service-accounts), including:
* Service account ID
* Service account secret
5. **Firebolt user**
A Firebolt user [associated](/managed-service/organization/service-accounts#create-a-user) with the service account, with:
* [USAGE](/security/rbac/database-permissions) permission on the database
* [OPERATE](/security/rbac/engine-permissions) permission on the engine (if it is not already running)
***
## Connect Omni to Firebolt
ℹ️ **Network access requirement**
Omni does not support mutual TLS (mTLS). In most cases, connections work without additional setup.
If you encounter connectivity issues, your Omni IP address may need to be allowlisted by Firebolt. See [Allowlisting Omni IP Addresses](#allowlisting-omni-ip-addresses).
### Step 1: Create a PostgreSQL connection
1. In Omni, go to **Settings → Connections**
2. Click **Add connection**
3. Select **Postgres**
### Step 2: Configure connection details
Fill in the connection form with the following values.
#### Basic settings
* **Display Name**: Any descriptive name (for example: `Firebolt Production`)
* **Host**: `pg..app.firebolt.io`
* Replace `` with your Firebolt account region (for example: `us-east-1`)
* **Port**: `5432`
* **Database**: ``
#### Authentication
* **Username**: `::`
* Replace `` with your Firebolt account name
* Replace `` with the name of the Firebolt engine to use
* Replace `` with your Firebolt service account ID
* **Password**: Firebolt service account secret
#### Schema configuration
Firebolt does **not support custom schemas**.
All objects in Firebolt are created and queried in the default `public` schema.
As a result, schema configuration in Omni must be kept minimal.
Use the following settings:
* **Include schemas**: Leave empty or set to `public`
* **Offloaded schemas**: Leave empty or set to `public`
* **Schema for table uploads**: Leave empty or set to `public`
Do not specify custom schemas in the Omni connection configuration.
Firebolt currently supports only the public schema, and configuring other schemas may result in query errors.
#### Timezone configuration
Firebolt supports timezone-aware timestamps (`TIMESTAMPTZ`).
However, Omni timezone conversion features are **not currently compatible** with Firebolt.
Use the following configuration:
* **Database timezone** `UTC`
* **Query timezone** Do not set
* **Allow user-specific timezones** Disabled
Do not enable Query Timezone or Allow user-specific timezones in Omni.
Enabling these options may result in query execution errors when running queries against Firebolt.
***
## Allowlisting Omni IP addresses
Omni connects to Firebolt from a **fixed, stable set of outbound IP addresses**.
If you experience connectivity issues when connecting Omni to Firebolt, your Omni IP address may need to be allowlisted by Firebolt.
### How to allowlist Omni IPs
1. In Omni, open the Firebolt connection configuration
2. Locate the outbound IP addresses displayed in the connection screen
3. Copy all listed IP addresses
4. Contact **Firebolt Support** and request allowlisting
* See more about how to contact [Firebolt Support and the severity guidelines](/support/severity-guidelines).
Include the following information in your request:
* Your name and email address
* Your organization name
* The tool name (**Omni**)
* The Omni IP addresses to allowlist
***
## Additional resources
* [Omni documentation](https://docs.omni.co)
* [Omni Postgres database connection](https://docs.omni.co/connect-data/setup/postgres)
# OpenTelemetry Exporter
Source: https://docs.firebolt.io/guides/integrations/otel-exporter
Learn how to enable Firebolt OpenTelemetry Exporter.
[OpenTelemetry](https://opentelemetry.io/) is a [CNCF](https://www.cncf.io/) project that provides a collection of APIs, SDKs,
and tools to instrument, generate, collect, and export telemetry data (metrics, logs, and traces).
In the past few years, this project become the accepted standard for telemetry, with native support by all major vendors.
As such, Firebolt provides an OpenTelemetry exporter which gives and compatibility with minimal effort.
Firebolt OpenTelemetry Exporter is provided as a docker image, which allows exporting engine metrics to any [OTLP](https://opentelemetry.io/docs/specs/otel/protocol/)
compatible collector. This makes possible to integrate Firebolt runtime metrics into customer's monitoring and alerting systems and
be able to use homogeneous infrastructure for observability of the entire data stack.
## Enabling Firebolt OpenTelemetry Exporter
For installation and usage instructions, see the [otel-exporter](https://github.com/firebolt-db/otel-exporter) repository on GitHub.
# Pandas
Source: https://docs.firebolt.io/guides/integrations/pandas
Use Pandas to analyze data in Firebolt.
![Pandas]()
[Pandas](https://pandas.pydata.org) is a powerful open-source data analysis and manipulation library for Python. It provides data structures like DataFrames and Series, which make it easy to work with structured data. Pandas is widely used in data science, machine learning, and statistical analysis due to its flexibility and ease of use.
This guide will show you how to connect Pandas to Firebolt, allowing you to perform data analysis and manipulation on your Firebolt data.
## Prerequisites
Before you begin, ensure you have the following prerequisites:
1. **Python**: You need to have Python installed on your machine. You can download it from [python.org](https://www.python.org/downloads/).
2. **Firebolt account**: You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for free.
3. **Firebolt Database and Table**: Make sure you have a Firebolt database and table with data ready for querying.
4. **Firebolt Service Account**: Create a [service account](/managed-service/organization/service-accounts) in Firebolt and note its id and secret.
## Connecting Pandas to Firebolt
1. Install the required libraries:
Pandas 2.2+ is only compatible with SQLAlchemy 2.0+. In case you're using different versions of those packages, please ensure their compatibility
```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
pip install "pandas>=2.2" "SQLAlchemy>=2.0" firebolt-sqlalchemy
```
2. Connect to Firebolt using a SQLAlchemy engine:
* `client_id`: client ID of your [service account](/managed-service/organization/service-accounts).
* `client_secret`: client secret of your [service account](/managed-service/organization/service-accounts).
* `account_name`: The name of your Firebolt [account](/guides/managing-your-organization/managing-accounts).
* `database`: The name of the [database](/security/rbac/database-permissions) to connect to.
* `engine`: The name of the [engine](/security/rbac/engine-permissions) to run SQL queries on.
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
from sqlalchemy import create_engine
# Fill out your Firebolt credentials
client_id = ""
client_secret = ""
account_name = ""
database = ""
engine_name = ""
# Create a SQLAlchemy engine for Firebolt
connection_url = f"firebolt://{client_id}:{client_secret}@{database}/{engine_name}?account_name={account_name}"
engine = create_engine(connection_url)
```
3. Load data into Pandas using a SQLAlchemy engine:
```python theme={"theme":{"light":"css-variables","dark":"css-variables"}}
import pandas as pd
# Read table content into a DataFrame
table_name = "my_table_name"
df = pd.read_sql_table(table_name, engine)
print(df.head())
# Or, execute a custom SQL query
sql = "SELECT * FROM my_table_name WHERE some_column = 'some_value' LIMIT 10000"
df = pd.read_sql(sql, engine)
print(df.head())
```
Done! You can now use Pandas to analyze and manipulate data from Firebolt. You can perform various operations like filtering, aggregating, and visualizing data using Pandas' powerful features.
## Further reading
* Learn more about [Pandas](https://pandas.pydata.org/docs/) and its capabilities.
* Explore the [Firebolt SQLAlchemy documentation](https://github.com/firebolt-db/firebolt-sqlalchemy/blob/main/README.md) for more details on using Firebolt with SQLAlchemy.
# Power BI
Source: https://docs.firebolt.io/guides/integrations/power-bi
Connecting Power BI and Firebolt.
![Power BI icon]()
[Power BI](https://powerbi.microsoft.com/) is a business analytics platform by Microsoft that enables users to visualize data, create interactive reports, and share insights across their organization. By integrating Power BI with Firebolt, you can leverage Firebolt's high-performance analytics capabilities to visualize and analyze large datasets efficiently.
This guide shows you how to set up your Firebolt account to integrate with Power BI Desktop using a custom ODBC driver.
## Prerequisites
Before connecting Power BI to Firebolt, ensure you have:
* **Power BI Desktop** – Download and install from the [Power BI Desktop download page](https://powerbi.microsoft.com/desktop/).
* **Firebolt account** – An active Firebolt account. If you don't have one, [sign up](https://go.firebolt.io/signup).
* **Firebolt database and table** – A Firebolt database with data ready for visualization. If needed, [create a database](/overview/quickstart#create-a-database) and [load data](/guides/loading-data) into it.
* **Firebolt service account** – An active Firebolt [service account](/managed-service/organization/service-accounts) with its ID and secret.
* **Firebolt user** – A user [associated](/managed-service/organization/service-accounts#create-a-user) with your service account, having [USAGE](/security/rbac/database-permissions) permission to query your database and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine.
## Install the Firebolt ODBC driver
1. Download the ODBC driver for your system architecture [Windows **x64**](https://firebolt-publishing-public.s3.us-east-1.amazonaws.com/repo/odbc/releases/latest/firebolt-odbc-latest-win64.msi) or [Windows **x32/x86**](https://firebolt-publishing-public.s3.us-east-1.amazonaws.com/repo/odbc/releases/latest/firebolt-odbc-latest-win32.msi).
2. Run the installer and follow the prompts to complete the installation.
## Install the Firebolt Power BI Connector
### Option 1: Using the signed connector (Recommended)
Follow these instructions if you have administrator access to your computer or are able to modify the registry. This is the most secure way. Otherwise, follow the unsigned guide below.
1. Download the latest Firebolt Power BI connector (`Firebolt.pqx`) from the [Firebolt Power BI Connector GitHub repository](https://github.com/firebolt-db/power-bi-firebolt/releases).
2. Place the downloaded `.pqx` file into the `Documents\Power BI Desktop\Custom Connectors` directory in your Windows operating system. If the folder does not exist - create it.
3. Open the registry editor from the Start menu or by pressing Windows + R, typing `regedit` and pressing enter.
4. Navigate to `HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Power BI Desktop`, creating the path if it does not exist.
5. Add a new value under this path. The type should be `Multi-String Value: REG_MULTI_SZ`. It should be called `TrustedCertificateThumbprints`.
6. Right-click the key, then select Modify and add the following value - `C838876B92BB2C62AB228A6DFDEF24F72CA46193`.
7. Save the value.
You can now proceed to Firebolt connection configuration step.
### Option 2: Using the unsigned connector
Suitable if you don't have access to the Windows registry. Requires Power BI security settings modification. Only use if following the steps above is not possible.
1. Download the latest Firebolt Power BI connector (`Firebolt.mez`) from the [Firebolt Power BI Connector GitHub repository](https://github.com/firebolt-db/power-bi-firebolt/releases).
2. Place the downloaded `.mez` file into the `Documents\Power BI Desktop\Custom Connectors` directory in your Windows operating system. If the folder does not exist - create it.
3. Restart Power BI Desktop to ensure the connector is loaded correctly.
4. In Power BI Desktop, go to **File** > **Options and settings** > **Options** > **Security**, and under **Data Extensions**, select **Allow any extension to load without validation or warning**. Click **OK** to apply the changes.
(This step is necessary because custom connectors are not signed by Microsoft and Power BI Desktop blocks unsigned connectors by default for security reasons.)
## Configure the Firebolt connection in Power BI
1. Open Power BI Desktop.
2. Select **Get data** from the Home ribbon.
3. Search for and select **Firebolt** from the list of available data sources.
4. In the first stage of the connection setup, enter the following parameters:
* **Account**: The name of your Firebolt account.
* **Database**: The name of the Firebolt database you want to connect to.
* **Engine Name**: The name of the Firebolt engine to run queries.
* **Data Connectivity Mode**: Choose between **Import** or **DirectQuery** mode based on your use case:
* **Import Mode**: Data is imported into Power BI and stored in-memory for faster performance. This mode is ideal for smaller datasets or scenarios where real-time updates are not required.
* **DirectQuery Mode**: Queries are sent directly to Firebolt in real-time, ensuring that the latest data is always used. This mode is suitable for large datasets or when up-to-date data is critical.
Click **Next** to proceed.
5. In the second stage, enter your Firebolt credentials:
* **Client ID**: The ID of your Firebolt service account.
* **Client Secret**: The secret for your Firebolt service account.
Click **Connect** to establish the connection.
6. Once connected, you will see a list of available tables in the Navigator pane. Select the tables you want to visualize and click **Load** to import the data into Power BI.
## Select and visualize data in Power BI
After connecting successfully, you can select and visualize data from Firebolt:
1. In the Navigator pane, select the tables you want to visualize.
2. Click **Load** to import data into Power BI.
3. Once loaded, use Power BI's visualization tools to create charts and dashboards. For more information, see Power BI's [Create reports](https://learn.microsoft.com/power-bi/create-reports/) documentation.
## Limitations
* The Firebolt ODBC driver currently supports Power BI Desktop only. Power BI Service (cloud) is not supported.
* Changing the database or engine requires creating a new ODBC connection.
## Additional resources
* [Power BI documentation](https://learn.microsoft.com/power-bi/).
* [Power BI Community](https://community.powerbi.com/) for support and best practices.
# AWS QuickSight
Source: https://docs.firebolt.io/guides/integrations/quicksight
Connecting AWS QuickSight and Firebolt via PostgreSQL interface.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![AWS QuickSight]()
[AWS QuickSight](https://aws.amazon.com/quicksight/) is a fast, cloud-powered business intelligence service that makes it easy to deliver insights to everyone in your organization. You can use QuickSight to create and publish interactive dashboards, perform ad-hoc analysis, and get business insights from your data.
This guide shows you how to connect AWS QuickSight to Firebolt using the PostgreSQL-compatible interface.
ℹ️ **Network access requirement**
QuickSight does not support mutual TLS (mTLS). In most cases, connections work without additional setup.
If you encounter connectivity issues, your QuickSight IP address may need to be allowlisted by Firebolt. See [Allowlisting QuickSight IP Addresses](#allowlisting-quicksight-ip-addresses).
## Step 1: Create a New Data Source
⚠️ QuickSight limitations (important)
AWS QuickSight enforces a PostgreSQL-style limit on the username length.
The full username value: `::` must be 63 characters or fewer.
If this limit is exceeded, QuickSight fails with a generic
“Something went wrong” error and the connection request does not reach Firebolt.
Service account ID requirement
Firebolt service accounts with a 57-character client ID cannot be used with QuickSight.
To connect QuickSight to Firebolt, you must use a service account with a shorter client ID (26 characters).
If the service account you are using has a 57-character client ID,
[create a new service account](/managed-service/organization/commands/create-service-account) with a shorter ID and use it for the connection.
Engine name length
If the combined username exceeds 63 characters due to a long engine name,
rename the engine to a shorter name for use with QuickSight.
These constraints are specific to QuickSight and PostgreSQL compatibility,
not Firebolt itself.
1. From the left navigation pane, choose **Datasets**.
2. Select the **Data sources** tab.
3. Choose **Create data source** (top right).
4. Choose the **PostgreSQL** data source card.
![QuickSight Postgres]()
You should see the following data source creation modal.
![QuickSight Data Source Modal]()
5. For **Data source name**, enter a descriptive name for your Firebolt data source connection. Because you can create many datasets from a connection to Firebolt, it's best to keep the name simple.
6. For **Connection type**, only Public is available so leave it as is.
7. Fill out the connection parameters using the values specified in the following table:
| Field | Value |
| ----------------- | --------------------------------------------------- |
| **Host** | `pg..app.firebolt.io` |
| **Port** | `5432` |
| **Database name** | `` |
| **Username** | `::` |
| **Password** | Service account secret |
#### Host details
When using Firebolt's **PostgreSQL-compatible interface**, the **host URL is based only on the region**.
Host URL Format: `pg..app.firebolt.io`
* `` — The region your account is hosted in (e.g., `us-east-1`)
* How to Find Your Region: Use this SQL query in Firebolt to get the region for your account:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT region FROM information_schema.accounts WHERE account_name = '';
```
#### User field details
Unlike traditional PostgreSQL databases, Firebolt uses a composite string in the User field to specify:
* Account name
* Engine name
* Service account ID
These are joined using `:` separators in a single string:
```
::
```
For example, if your account name is `my_account`, your engine name is `analytics_engine`, and your service account id is `fbcid_xxx` you would enter `my_account:analytics_engine:fbcid_xxx`.
8. To verify the connection is working, choose **Validate** connection.
9. To finish and create the data source, choose **Create** data source.
## Step 2: Create a QuickSight Dataset
After you create a Firebolt data source, you can use it to create a dataset for analysis.
To create a dataset using a Firebolt data source, follow these steps:
1. From the left navigation pane, choose **Datasets**.
2. Choose **Create dataset** (top right).
3. From the list of existing data sources, select the Firebolt data source you created.
4. Choose **Select**.
5. To specify the table you want to connect to, first select the **Schema** you want to use.
6. For **Tables**, choose the table that you want to use.
If you prefer to use your own SQL statement, select **Use custom SQL**.
7. When prompted to choose a dataset creation mode, select **Directly query your data**.
Firebolt is designed to provide high-performance query execution, so Direct Query is recommended in most cases.
(Optional) You may choose **Import to SPICE** if you specifically require SPICE-based features or caching behavior.
8. Choose **Edit/Preview**.
9. (Optional) To add more data, use the following steps:
* Choose **Add data** in the top right.
* To connect to different data, choose **Switch data source**, and choose a different dataset.
* Follow the prompts to finish adding data.
* After adding new data to the same dataset, choose **Configure this join** (the two red dots). Set up a join for each additional table.
10. If you want to add calculated fields, choose **Add calculated field**.
11. Clear the check box for any fields that you want to omit.
12. Update any data types that you want to change.
13. When you are done, choose **Save** to save and close the dataset.
## Allowlisting QuickSight IP Addresses
Amazon QuickSight does not support mutual TLS (mTLS).
If you experience connectivity issues when connecting QuickSight to Firebolt, QuickSight network access for your AWS region may need to be allowlisted by Firebolt.
### Request allowlisting from Firebolt Support
Contact Firebolt Support and request allowlisting, including the following information:
See how to contact [Firebolt Support and severity guidelines](/support/severity-guidelines)
* Your name and email address
* Your organization name
* Tool: **Amazon QuickSight**
* QuickSight AWS region (for example: `us-east-1`)
After allowlisting is completed, return to QuickSight and retry the connection.
## Calculated fields compatibility
QuickSight supports creating calculated fields using built-in functions.
Most calculated field functions work as expected with Firebolt.
However, in rare cases, a calculated field function may generate SQL that is not supported by Firebolt’s PostgreSQL-compatible interface.
If you encounter an error with a calculated field, use **Custom SQL** in QuickSight and implement the logic directly as SQL instead of relying on the calculated field function.
## Additional Resources
For more information about AWS QuickSight configuration and visualisation, refer to the AWS [documentaion](https://docs.aws.amazon.com/quicksuite/latest/userguide/working-with-visuals.html).
# Tableau
Source: https://docs.firebolt.io/guides/integrations/tableau
Connecting Tableau and Firebolt.
![Tableau icon]()
[Tableau](https://www.tableau.com/) is a visual analytics platform that empowers users to explore, analyze, and present data through interactive visualizations. It supports diverse use cases such as data exploration, reporting, and collaboration, and helps users gain insights and make informed decisions. This guide shows you how to set up your Firebolt account to integrate with [Tableau Desktop](https://www.tableau.com/products/desktop) or [Tableau Prep](https://www.tableau.com/products/prep).
## Prerequisites
You must have the following prerequisites before you can connect your Firebolt account to Tableau:
* **Tableau account** – You must have access to an active Tableau account. If you do not have access, you can [sign up](https://www.tableau.com/products/trial) for one.
* **Firebolt account** – You need an active Firebolt account. If you do not have one, you can [sign up](https://go.firebolt.io/signup) for one.
* **Firebolt database and table** – You must have access to a Firebolt database that contains a table with data ready for visualization. If you don't have access, you can [create a database](/overview/quickstart#create-a-database) and then [load data](/guides/loading-data) into it.
* **Firebolt service account** – You must have access to an active Firebolt [service account](/managed-service/organization/service-accounts), which facilitates programmatic access to Firebolt, its ID and secret.
* **Firebolt user** – You must have a user that is [associated](/managed-service/organization/service-accounts#create-a-user) with your service account. The user should have [USAGE](/security/rbac/database-permissions) permission to query your database, and [OPERATE](/security/rbac/engine-permissions) permission to start and stop an engine if it is not already started.
## Set up Tableau Cloud
1. Navigate to Tableau Cloud's [login page](https://online.tableau.com/) and log in to your account.
2. On the home screen select **New** and from the drop-down menu select **Workbook**.
3. You will be redirected to a new page where a **Connect to Data** pane appears in the middle. Select **Connectors** to open a list of all the connectors available.
4. Find "Firebolt Connector by Firebolt" and select it.
5. Follow the steps in [Connect to Firebolt](#connect-to-firebolt) to connect to your Firebolt account and select a database and schema to query.
## Set up Tableau Desktop or Tableau Prep
To connect Tableau Desktop or Tableau Prep to Firebolt, you must install a Firebolt connector, a [JDBC driver](/guides/developing-with-firebolt/connecting-with-jdbc#jdbc-driver), connect to Firebolt, and select a database and schema to query.
1. **Download and install Tableau**
1. Navigate to Tableau's [download page](https://www.tableau.com/products/desktop/download) for Desktop or [download page](https://www.tableau.com/products/prep/download) for Prep.
2. Follow the prompts to install Tableau Desktop or Tableau Prep.
2. **Download the latest JDBC driver**
Download a JDBC driver, which will allow Tableau to interact with Firebolt databases using Java, from Firebolt's GitHub [repository](https://github.com/firebolt-db/jdbc/releases). The name of the file has the following format: `firebolt-jdbc-.jar`, and should be saved in a specific directory that depends on the operating system as follows:
* Windows: `C:\Program Files\Tableau\Drivers`
* Mac: `/Users//Library/Tableau/Drivers`
* Linux: `/opt/tableau/tableau_driver/jdbc`
3. **Download the latest Firebolt connector**
1. **Tableau Desktop**
1. Open Tableau and on the home screen in the Connect section on the left find **To a Server** option.
2. Select **More...** to open a list of all the connectors available.
3. Find "Firebolt by Firebolt Analytics Inc" in the **Additional Connectors** section or use the search bar to quickly navigate to it.
4. Select this connector and in the following window select **Install and restart Tableau**.
2. **Tableau Prep**
1. Open Tableau and on the home screen in the top section press the **Connect to Data** button.
2. In the newly opened **Connect** section scroll down to find "Firebolt by Firebolt Analytics Inc" in the **Additional Connectors** section or use the search bar to quickly navigate to it.
3. Select this connector and in the following window select **Install and restart Tableau**.
4. **Start Tableau and verify Firebolt connector availability**
1. Start your Tableau Desktop or Tableau Prep. If you selected "Install and restart" in the previous step it should restart automatically.
2. Following the same steps as in **3a** or **3b** find the Firebolt Connector.
5. Follow the steps in [Connect to Firebolt](#connect-to-firebolt) to connect to your Firebolt account and select a database and schema to query.
## Connect to Firebolt
1. Select "Firebolt Connector by Firebolt" from the list of available connectors.
2. Enter the following parameters:
| **Field** | **Required** | **Description** |
| ----------------- | ------------ | ----------------------------------------------------------------------------------------------------------------------- |
| **Host** | No | Most users should not enter a value in the text box under `Host`. |
| **Database** | Yes | The name of the Firebolt [database](/overview/data-management#databases) to connect to. |
| **Client ID** | Yes | The [ID of your service account](/managed-service/organization/service-accounts#get-a-service-account-id). |
| **Client Secret** | Yes | The [secret](/managed-service/organization/service-accounts#generate-a-secret) for your service account authentication. |
| **Engine Name** | Yes | The name of the [engine](/managed-service/engine-fundamentals) to run queries. |
| **Account** | Yes | The name of your Firebolt account within your organization. |
3. Select **Sign in**.
4. **Choose the database and the schema to query**
After successful authentication, **Database** and **Schema** drop-down lists appear in the left navigation pane under **Connections**. The database name from the previous step appears in the database drop-down list. To change the database, you must repeat the previous step and set up a new connector.
Choose the schema and tables as follows:
1. Select the drop-down list under **Schema** to select a [schema](/overview/data-management#schema). Most users should choose `public`. For more information about schema permissions and privileges, see [Schema permissions](/security/rbac/database-permissions/schema-permissions).
2. Drag and drop tables from the list of available tables in your schema to use them in Tableau.
5. **Prepare or visualize your data**
Once your data source is selected you can begin preparing or visualizing the data as follows:
* **For Tableau Desktop/Cloud**: Select `Sheet 1` tab from the bottom-left corner of your Tableau window next to **Data Source**. In the left navigation panel under **Sheets**, drag and drop any available columns or pre-defined aggregation from your table into the Tableau workspace to start building charts. See Tableau's [Build a view from scratch](https://help.tableau.com/current/pro/desktop/en-us/getstarted_buildmanual_ex1basic.htm) documentation for more information.
* **For Tableau Prep**: Use Tableau Prep's tools to clean and shape your data. See Tableau Prep's [Getting Started](https://help.tableau.com/current/prep/en-us/prep_get_started.htm) documentation for more information. Combine data from multiple sources if needed and save the prepared data for analysis in Tableau Desktop or Server.
## Limitations
* Once you have set up a connection to Firebolt, you cannot change the database that you specified during setup. In order to change the database, you must repeat step 4 to **Start Tableau and verify Firebolt connector availability** in [Connect to Tableau](#connect-to-tableau-desktop-or-tableau-prep) to set up a new connection.
## Additional resources
* Watch Tableau's [free training videos](https://www.tableau.com/en-gb/learn/training) on getting started, preparing data, and geographical analysis.
* Read Tableau's data visualization [articles](https://www.tableau.com/en-gb/learn/articles) about creating effective, engaging, and interactive examples.
* Follow Tableau's [blog](https://www.tableau.com/en-gb/blog) for new features and tips.
# TextQL
Source: https://docs.firebolt.io/guides/integrations/textql
Use TextQL to query Firebolt with natural language powered by AI.
[TextQL](https://www.textql.com) is an AI-powered analytics platform that lets teams query and explore data using natural language. TextQL connects to Firebolt as a data source, enabling your organization to ask questions in plain English and get results backed by Firebolt's high-performance query engine.
For setup instructions, configuration details, and the latest connection steps, see the [TextQL Firebolt connector documentation](https://docs.textql.com/core/datasources/databases/firebolt).
## Use cases
* Ask analytical questions in natural language against data stored in Firebolt.
* Enable non-technical stakeholders to explore Firebolt data without writing SQL.
* Combine TextQL's AI capabilities with Firebolt's sub-second query performance.
# ThoughtSpot
Source: https://docs.firebolt.io/guides/integrations/thoughtspot
Connecting ThoughtSpot and Firebolt.
🧪 **Preview (Beta)**
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
![ThoughtSpot]()
[ThoughtSpot](https://www.thoughtspot.com/) is a modern analytics platform that enables users to search and analyze data using natural language queries. You can use ThoughtSpot's interface to explore, visualize, and create interactive dashboards from your data.
This guide shows you how to create a connection between ThoughtSpot and your Firebolt database.
ℹ️ **Network access requirement**
ThoughtSpot does not support mutual TLS (mTLS). In most cases, connections work without additional setup.
If you encounter connectivity issues, your ThoughtSpot IP address may need to be allowlisted by Firebolt. See [Allowlisting ThoughtSpot IP Addresses](#allowlisting-thoughtspot-ip-addresses).
## Step-by-Step Guide
1. Select **Data workspace** tab.
2. Select the **Connections** section in the left navigation bar, and then press the **Create connection** button.
3. Find and select the **PostgreSQL** tile as the connection type and click **Next**.
![ThoughtSpot Connection Selection]()
4. Create a name for your connection, a description (optional) then enter the connection details for your Firebolt data source:
| Field | Value |
| ------------ | --------------------------------------------------- |
| **Host** | `pg..app.firebolt.io` |
| **Port** | `5432` |
| **User** | `::` |
| **Password** | Service account secret |
| **Database** | Database name |
#### Host details
When using Firebolt's PostgreSQL-compatible interface, the host URL is based only on the region.
**Host URL Format:** `pg..app.firebolt.io`
`` — The region your account is hosted in (e.g., us-east-1)
**How to Find Your Region:** Use this SQL query in Firebolt to get the region for your account:
```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT region FROM information_schema.accounts WHERE account_name = '