# Field Selection and Compatibility Rules

To ensure compatibility and that the fields Stitch requires for replication are included in selected streams, Stitch enforces field selection and compatibility rules. Learn about the metadata types that control field inclusion in the Connect API.

## Field types

Stitch requires two types of fields for stream replication: [Primary Keys](https://help.qlik.com/en-US/stitch/developers/stitch-connect/guides/field-selection-compatibility-rules#primary-key-fields) and, when applicable, [Replication Keys](https://help.qlik.com/en-US/stitch/developers/stitch-connect/guides/field-selection-compatibility-rules#replication-key-fields).

### Primary Key fields

To accurately replicate data for a stream, Stitch requires the Primary Key information for each stream. A Primary Key is a column or set of columns that uniquely define a record.

Depending on the source and stream type, this is handled one of several ways.

#### Database sources

For database sources, Stitch will typically query the database’s information schema to determine the Primary Key fields and then store the list of Primary Key field names as a list in the [stream’s metadata](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#stream-level-metadata-object) `table-key-properties` property:

```
Primary Keys in a database stream
```

```
{
  "selected": null,
  "stream_id": 2289176,
  "tap_stream_id": "demni2mf59dt10-heroku-orders",
  "stream_name": "orders",
  "metadata": {
    "database-name": "demni2mf59dt10",
    "selected": null,
    "replication-method": null,
    "is-view": false,
    "row-count": 447,
    "schema-name": "heroku",
    "table-key-properties": [
      "id"
    ]
  }
}
```

#### Database views

For database views, the [stream’s metadata](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#stream-level-metadata-object) will contain an `is-view` property with a value of `true`:

```
Primary Keys in a database view (stream)
```

```
{
  "selected": true,
  "stream_id": 2375830,
  "tap_stream_id": "demni2mf59dt10-public-customer_view",
  "stream_name": "customer_view",
  "metadata": {
    "database-name": "demni2mf59dt10",
    "selected": true,
    "is-view": true,
    "replication-key": "updated_at",
    "replication-method": "updated_at",
    "row-count": 56,
    "schema-name": "public",
    "table-key-properties": [],
    "view-key-properties": [
      "id"
    ]
  }
}
```

Primary Key information must be provided in the `view-key-properties` metadata property when the stream is selected for replication.

#### SaaS sources

For SaaS sources, Primary Keys are typically hard-coded in the Singer tap backing the source. The list of Primary Key field names will be stored as a list in the [stream’s metadata](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#stream-level-metadata-object) `table-key-properties` property:

```
Primary Keys in a SaaS stream
```

```
{
  "selected": null,
  "stream_id": 2288758,
  "tap_stream_id": "custom_collections",
  "stream_name": "custom_collections",
  "metadata": {
    "forced-replication-method": "INCREMENTAL",
    "selected": null,
    "table-key-properties": [
      "id"
    ],
    "valid-replication-keys": [
      "updated_at"
    ]
  }
}
```

### Replication Key fields

If a stream’s `replication-method` is `INCREMENTAL`, an appropriate field must be set as the stream’s [Replication Key](https://help.qlik.com/en-US/stitch/replication/replication-keys). Replication Keys are columns used to identify new and updated data for replication. These are typically integer, datetime, or timestamp columns and are required to use [Key-based Incremental Replication](https://help.qlik.com/en-US/stitch/replication/replication-methods/key-based-incremental).

Like Primary Keys, this is handled in one of several ways depending on the source type.

#### Database sources

For database sources, a valid Replication Key must be provided using the `replication-key` metadata property when the stream is selected.

```
Replication Keys in a database stream
```

```
{
  "selected": null,
  "stream_id": 2289176,
  "tap_stream_id": "demni2mf59dt10-heroku-orders",
  "stream_name": "orders",
  "metadata": {
    "database-name": "demni2mf59dt10",
    "selected": null,
    "replication-method": null,
    "is-view": false,
    "row-count": 447,
    "schema-name": "heroku",
    "table-key-properties": [
      "id"
    ]
  }
}
```

**Note**: This is also applicable to database views if the stream’s `replication-method` is set to `INCREMENTAL`.

#### SaaS sources

For SaaS sources, Replication Keys are hard-coded in the Singer tap backing the source. The list of Replication Key field names will be stored as a list in the [stream’s metadata](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#stream-level-metadata-object) `valid-replication-keys` property:

```
Replication Keys in a SaaS stream
```

```
{
  "selected": null,
  "stream_id": 2288758,
  "tap_stream_id": "custom_collections",
  "stream_name": "custom_collections",
  "metadata": {
    "forced-replication-method": "INCREMENTAL",
    "selected": null,
    "table-key-properties": [
      "id"
    ],
    "valid-replication-keys": [
      "updated_at"
    ]
  }
}
```

**Note**: When selecting fields in SaaS streams with a `valid-replication-keys` property, you must explicitly set the stream’s `replication-key` to a field in the `valid-replication-keys` property. Selecting this field for replication won’t automatically set the field as the stream’s Replication Key.

## Field selection rules

Stitch requires Primary Key and Replication Key fields in streams to be selected in order to successfully and accurately replicate data.

To ensure the required fields are included in a stream’s field inclusion list, Stitch enforces field selection rules.

### Metadata in field selection

Field selection rules are shaped by three `metadata` fields in a [Field-level Metadata object](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#field-level-metadata-object):

| **inclusion**<br><br>STRING<br><br>READ-ONLY | Indicates when a field will be included. Possible values are:<br><br><ul> <li><code>automatic</code> - The field is included all the time, regardless of <code>selected-by-default</code> and <code>selected</code> values</li> <li><code>available</code> - The field is available for selection. The field will be included if <code>selected-by-default</code> or <code>selected</code> is <code>true</code>.</li> <li><code>unsupported</code> - The field is unsupported and will not be included, regardless of <code>selected-by-default</code> and <code>selected</code> values</li></ul> |
| --- | --- |
| **selected-by-default**<br><br>BOOLEAN<br><br>READ-ONLY | Indicates if a field will be selected by default. Possible values are:<br><br><ul> <li><code>null</code> - The value has not been set</li> <li><code>true</code> - The field is selected by default and is included regardless of the <code>selected</code> value</li> <li><code>false</code> - The field is not selected by default. The field will be included if the <code>selected</code> value is <code>true</code>.</li></ul> |
| **selected**<br><br>BOOLEAN | Indicates whether a field should be selected. Possible values are:<br><br><ul> <li><code>null</code> - The value has not been set</li> <li><code>true</code> - The field is selected</li> <li><code>false</code> - The field is not selected</li></ul> |

### Field selection metadata combinations

Below are the possible combinations of `metadata` field values and whether a field will be selected with the listed settings.

**Note**: A `*` in the table indicates any possible value (`null`, `true`, or `false`) for the `metadata` field.

| **inclusion** | **selected** | **selected-by-default** | **replicated?** |
| --- | --- | --- | --- |
| automatic | \* | \* | the Success icon<br><br>The field will be replicated |
| unsupported | \* | \* | the Remove icon<br><br>The field will not be replicated |
| available | true | null | the Success icon<br><br>The field will be replicated |
| available | true | true | the Success icon<br><br>The field will be replicated |
| available | true | false | the Success icon<br><br>The field will be replicated |
| available | false | null | the Remove icon<br><br>The field will not be replicated |
| available | false | true | the Success icon<br><br>The field will be replicated |
| available | false | false | the Remove icon<br><br>The field will not be replicated |
| available | null | true | the Success icon<br><br>The field will be replicated |
| available | null | false | the Remove icon<br><br>The field will not be replicated |
| available | null | null | the Remove icon<br><br>The field will not be replicated |

## Field compatibility rules

While all fields are subject to field selection rules, some fields are also subject to field compatibility rules. This means that certain combinations of fields are not able to be selected together in a single stream.

These restrictions primarily affect SaaS sources like [Microsoft Advertising (formerly Bing Ads)](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#form-properties-bing-ads-object), [Google Analytics](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#form-properties-google-analytics-object), or [Google AdWords](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#form-properties-google-adwords-object), and are set by the source.

### Field exclusion metadata

Information note **Note**: This section isn’t applicable to Google Analytics sources. Refer to the [Google Analytics field compatibility](https://help.qlik.com/en-US/stitch/developers/stitch-connect/guides/field-selection-compatibility-rules#google-analytics-field-compatibility) section.

If a field is subject to compatibility rules, its [Field-level Metadata object](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#field-level-metadata-object) will contain a `fieldExclusion` property. This property contains a list of arrays that correspond to the `breadcrumb` of an incompatible field.

For example: Below is the field-level metadata for the `DeviceOS` field in the Microsoft Advertising (formerly Bing Ads) `ad_group_performance_report` stream:

```
Example field-level metadata for a Microsoft Advertising field
```

```
{
  "metadata": {
    "fieldExclusions": [
      [
        "properties",
        "ExactMatchImpressionSharePercent"
      ],
      [
        "properties",
        "ImpressionLostToAdRelevancePercent"
      ],
      [
        "properties",
        "ImpressionLostToBidPercent"
      ],
      [
        "properties",
        "ImpressionLostToBudgetPercent"
      ],
      [
        "properties",
        "ImpressionLostToExpectedCtrPercent"
      ],
      [
        "properties",
        "ImpressionLostToRankPercent"
      ],
      [
        "properties",
        "ImpressionSharePercent"
      ]
    ],
    "inclusion": "available"
  }
}
```

This indicates that when the `DeviceOS` field is selected, the fields listed in the `fieldExclusions` property cannot also be selected.

### Google Analytics field compatibility

Google Analytics sources are the exception to the previous section. Fields in this source are still subject to compatibility rules, but field-level metadata won’t contain a `fieldExclusion` property.

To determine what fields are compatible, we recommend using [Google’s Dimensions and Metrics Explorer](https://developers.google.com/analytics/devguides/reporting/core/dimsmets) before sending field selection requests to the API.

### Field exclusion violations

The Connect API may allow you to select fields that violate field exclusion/compatibility rules, but doing so will likely result in extraction job failures.

To avoid this scenario, Stitch recommends considering `fieldExclusions`, if available, when building your own application. For Google Analytics sources, we recommend using [Google’s Dimensions and Metrics Explorer](https://developers.google.com/analytics/devguides/reporting/core/dimsmets) to determine field compatibility.