Keys

In a database management system, a key is an attribute or a set of attributes that help to uniquely identify a row in a table. This section describes the types of keys and their roles in Fivetran Local Data Processing.

Primary Keys

Primary Key is an ID unique for each specific row in a database table. It consists of a single mandatory column or a set of mandatory columns. Primary keys are typically validated by transaction processing databases. However, analytical databases can consider the primary key to be declarative and not enforce it.

Replication Keys

Replication Key is a unique key that Local Data Processing uses to identify a row in a target table. Local Data Processing requires every table in a channel to have a replication key. When a channel is created, Local Data Processing inspects a source database for a "good" replication key. If it sees a primary key, then that is used for its replication key. If there is no primary key, Local Data Processing will look for a unique index as a replication key. If none of the above exists, then Local Data Processing will define the replication key using all non-LOB columns. This last case is called an implicit replication key.

If there are multiple uniqueness constraints on the capture table (e.g., a primary key and several unique indices), Local Data Processing uses a certain hierarchy rule to decide which one to use as a replication key (e.g. a primary key would 'win' and be considered).

The hierarchy rule is based on:

• the source dictionary;

• a unique key with mandatory columns;

• or all non-LOB columns in the table.

You can add a column to a replication key (assuming that the implicit key has no explicitly-defined columns). This can be achieved using action with parameter or .

NOTE: Local Data Processing ignores a table's non-unique indexes. On Oracle, it also ignores unique indexes if that index only contains a single nullable column.

Distribution Keys

For distributed DBMSes, such as Greenplum, Local Data Processing has a concept of Distribution Key for each table. A distribution key is a column or group of columns used to determine the distribution of portions of the table across logical slices or nodes. These are the column(s) that Local Data Processing uses for hashing the storage when Local Data Processing creates its tables in a target database.

Some databases do not support distribution keys, and some have their own limitations. For example, Redshift only supports a single column as a distribution key, while Greenplum and Teradata support multi-column distribution keys. For good distribution of data across a database cluster, the distribution key values should ideally be unique, or otherwise, be well-distributed and almost unique.

There are two types of distribution keys in Local Data Processing:

• Explicit Distribution Key means that the distribution key has been explicitly defined by a user in Local Data Processing using action with parameter .
• Implicit Distribution Key means that the distribution key has not been explicitly defined by a user in Local Data Processing. In this case, Local Data Processing will derive the distribution key from the first column of the replication key. To influence the selection of the key, refer to the action. For example, you can eliminate unsuitable columns () or set a maximum number of columns () to be considered for the distribution key.

If action with parameter =Burst is defined, Local Data Processing creates target tables in a distributed DBMS, but they can also be created directly. When Local Data Processing creates the target tables, it will use a distribution key for the distributed DBMS. Additionally, the table will have the Local Data Processing replication key as an index (either with or without uniqueness depending on whether action with parameter is defined).

Parameter only affects tables that have an implicit distribution key. It has no effect on tables with an explicit distribution key. The parameter should be used if a table can actually contain multiple identical rows. If no replication key column(s) are defined on the table and this parameter is not set, then all updates are treated as key updates and are replicated as a delete and an insert. Additionally, each delete is integrated using a special SQL subselect which ensures only a single row is deleted, not multiple rows. Unnecessary use of can lead to errors if there are duplicates, but if you set , then performance and accuracy are guaranteed.

Local Data Processing also creates staging tables (ending with '__b') for delivering bursts of changes into its target tables. They do not get an index (not needed), but Local Data Processing will use for them the same distribution key as in the target tables. It is essential that the distribution key of the burst table matches the distribution key of the target table, otherwise the burst updates will be misaligned and the integrate job will either fail or be too slow.