Configuring Fact and Dimension Tables
On This Page
As a part of creating the tables in Firebolt, you must configure the following keys. These facilitate the efficient loading of data to your Firebolt database.
The Primary Index (PI) is applicable for both Fact and Dimension tables.
Firebolt tables are aimed to optimize speed and efficiency. To achieve this, the records in tables are sorted, compressed, and indexed. The sort order of the records is the PI defined on the table.
Note: You can sort by one or more fields.
A PI is different from the primary key (PK) used in traditional database design. Unlike a primary key, the primary index is not unique.
The primary index for:
Should contain the fields that are most typically used to filter or group the records.
Need not be identical to the field/s by which data is partitioned at the Source.
- Should include the field/s that are used to join the Dimension table to the Fact table.
Partitions are smaller, physical parts of large tables created for data maintenance and performance. A Partition Key (PRK) and Partitions are only supported on Fact tables. A PRK is used to determine the partition of the table in which a record must be stored. Therefore, it cannot be composed of nullable columns.
You can choose to partition your Fact table in one of the following cases:
Delete or update data by key: In this case, the PRK should be the column by which you intend to perform update or delete operations.
For example, if you need to store one month’s data, set the partition key based on the
datecolumn, so you can drop partitions that are older than one month.
Boost performance: If your Fact table is very large (contains more than 100 million rows), consider using partitions to prune the data for running the queries. Use the PRK as the main predicate in your
WHEREclause, so that it prunes as much data as possible for each query.
For example, if your main query’s predicate is the
product_typecolumn, set the partition key by this column.
In both cases, the recommendation is not to use long text columns as your partition key.
Nullable column (NC) is applicable for both Fact and Dimension tables.
Nullable columns are columns that can have
NULL as a value.
Note: Nullable columns cannot be used in Firebolt indexes (Primary, Aggregating, or Join indexes).
Refer to the following table for the list of key updates made to this page:
|Date||Release||Description of Change|