Configuring Fact and Dimension Tables

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.

---

Primary Index

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:

• Fact tables:

  • 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.

• Dimension tables:

  • Should include the field/s that are used to join the Dimension table to the Fact table.

---

Partition Key

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 date column, 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 WHERE clause, so that it prunes as much data as possible for each query.

  For example, if your main query’s predicate is the product_type column, 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

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).

---

See Also

• Working with partitions.