Skip to main content
Firebolt supports loading data using external tables, which are different from fact and dimension tables. External tables store metadata objects that reference files stored in an Amazon S3 bucket, rather than actual data. To create an external table, run the CREATE EXTERNAL TABLE command. Firebolt recommends using LOCATION objects to securely manage credentials and connection details for external data sources. After you create an external table, use the INSERT command to load the data from the external table into a fact or dimension table. Data that you ingest must be in the same AWS Region as the target Firebolt database.
Although you can run a query over an external table to return query results, we don’t recommend it. Such a query will be significantly slower than the same query run over the same data in a fact or dimension table because of the data transfer between Firebolt and your data store. We strongly recommend that you use external tables only for ingestion, specifying the table and its columns only in the FROM clause of an INSERT statement.

​
Workflows

For a simple end-to-end workflow that demonstrates loading data into Firebolt, see the Getting started tutorial.

​
Supported file formats

Firebolt supports loading the following source file formats from S3: PARQUET, CSV, TSV, AVRO, JSON (JSON Lines), and ORC. We are quick to add support for more types, so make sure to let us know if you need it.

​
Using metadata virtual columns

Firebolt external tables include metadata virtual columns that Firebolt populates with useful system data during ingestion. Firebolt includes these columns automatically. You don’t need to specify them in the CREATE EXTERNAL TABLE statement. When you use an external table to ingest data, you can explicitly reference these columns to ingest the metadata. First, you define the columns in a CREATE FACT|DIMENSION TABLE statement. Next, you specify the virtual column names to select in the INSERT INTO statement, with the fact or dimension table as the target. You can then query the columns in the fact or dimension table for analysis, troubleshooting, and to implement logic. For more information, see the example below. The metadata virtual columns listed below are available in external tables.

​
Example–querying metadata virtual column values

The example below creates an external table that references an AWS S3 bucket that contains Parquet files from which Firebolt will ingest values for c_id and c_name. First, create a LOCATION object to securely store credentials:
Then create the external table using the LOCATION object:
The query example below creates a dimension table, which will be the target for the data to be ingested. The statement defines two additional columns, $source_file_name and $source_file_timestamp, to contain metadata values that Firebolt creates automatically for the external table.
Finally, the INSERT query below ingests the data from my_external_table into my_dim_table_with_metadata. The SELECT clause explicitly specifies the metadata virtual columns, which is a requirement.
An example SELECT query over my_dim_table_with_metadata shows that the source data file (minus the s3://my_bucket portion of the file path) and file timestamp are included in the dimension table for each row.