# Databricks Delta on AWS (v1) Destination Reference

Databricks Delta Lake (AWS) is an open source storage layer that sits on top of your existing data lake file storage. Stitch’s Databricks Delta Lake (AWS) destination is compatible with Amazon S3 data lakes.

This guide serves as a reference for version 1 of Stitch’s Databricks Delta Lake (AWS) destination.

## Details and features

* [Stitch features](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#stitch-features)

* [Destination details](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#destination-details)

### Stitch features

High-level details about Stitch’s implementation of Databricks Delta Lake (AWS), such as supported connection methods, availability on Stitch plans, etc.

| **Release status<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | Released |
| --- | --- |
| **Stitch plan availability<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | All Stitch plans |
| **Stitch supported regions<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | <ul><li>North America (AWS us-east-1)</li><li>Europe (AWS eu-central-1)</li></ul><br><br>Operating regions determine the location of the resources Stitch uses to process your data. [Learn more](https://help.qlik.com/en-US/stitch/security/supported-data-pipeline-regions). |
| **Supported versions<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | Databricks Runtime Version 6.3+ |
| **Connect API availability<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Supported<br>the Success iconSupported**<br><br>This version of the Databricks Delta Lake (AWS) destination can be created and managed using Stitch’s Connect API. [Learn more](https://help.qlik.com/en-US/stitch/developers/stitch-connect/api#destination-form-properties). |
| **SSH connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Supported<br>the Success iconSupported**<br><br>Stitch supports using SSH tunnels to connect to Databricks Delta Lake (AWS) destinations. |
| **SSL connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Supported<br>the Success iconSupported**<br><br>Stitch will attempt to use SSL to connect by default. No additional configuration is needed. |
| **VPN connections<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Unsupported<br>the Remove iconNot supported**<br><br>Virtual Private Network (VPN) connections may be implemented as part of a Premium plan. Contact [Stitch Sales](https://www.stitchdata.com/contact/) for more info. |
| **Static IP addresses<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Supported<br>the Success iconSupported**<br><br>This version of the Databricks Delta Lake (AWS) destination has static IP addresses that can be whitelisted. |
| **Default loading behavior<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Upsert**<br><br>**Note**: Append-Only loading will be used if all conditions for Upsert are not met. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior). |
| **Nested structure support<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | **Supported**<br><br>the Success icon<br><br>Supported<br><br><br>Nested data structures (JSON arrays and objects) will be loaded intact into a `STRING` column with a comment specifying that the column contains JSON. [Learn more](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--json-structures). |

### Destination details

Details about the destination, including object names, table and column limits, reserved keywords, etc.

**Note**: Exceeding the limits noted below will result in loading errors or rejected data.

| **Maximum record size<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | 20MB |
| --- | --- |
| **Table name length<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | 78 characters |
| **Column name length<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | 122 characters |
| **Maximum columns per table<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | None |
| **Maximum table size<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | None |
| **Maximum tables per database<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | None |
| **Case sensitivity<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | Insensitive |
| **Reserved keywords<br><br><br>[the Help icon](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference)** | Refer to the [Reserved keywords documentation](https://help.qlik.com/en-US/stitch/replication/loading/reserved-keywords#databricks-delta-v1-reserved-keywords). |

## Replication

* [Replication process overview](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication-process-overview)

* [Loading behavior](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#loading-behavior)

* [Primary Keys](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--primary-keys)

* [Incompatible sources](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--incompatible-sources)

### Replication process overview

A Stitch replication job consists of three stages:

* [Step 1: Data extraction](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--data-extraction)

* [Step 2: Stitch’s internal pipeline](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--stitch-internal-pipeline)

* [Step 3: Amazon S3 bucket](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--amazon-s3-bucket)

* [Step 4: Staging data](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--staging-data)

* [Step 5: Data merge](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#replication--data-merge)

#### Step 1: Data extraction

Stitch requests and extracts data from a data source. Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--extraction) for a more detailed explanation of the Extraction phase.

#### Step 2: Stitch's internal pipeline

The data extracted from sources is processed by Stitch. Stitch’s internal pipeline includes the **Prepare** and **Load** phases of the replication process:

* **Prepare**: During this phase, the extracted data is buffered in Stitch’s durable, highly available internal data pipeline and readied for loading.
* **Load**: During this phase, the prepared data is transformed to be compatible with the destination, and then loaded. Refer to the [Transformations](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations) section for more info about the transformations Stitch performs for Databricks Delta Lake (AWS) destinations.

Refer to the [System overview guide](https://help.qlik.com/en-US/stitch/getting-started/basic-concepts-system-overview#system-architecture--preparing) for a more detailed explanation of these phases.

#### Step 3: Amazon S3 bucket

Data is loaded into S3 files in the Amazon S3 bucket you provide during destination setup.

#### Step 4: Staging data

Data is copied from the Amazon S3 bucket and placed into staging tables in Databricks Delta Lake (AWS).

#### Step 5: Data merge

Data is merged from the staging tables into real tables in Databricks Delta Lake (AWS).

### Loading behavior

By default, Stitch will use **Upsert loading** when loading data into Databricks Delta Lake (AWS).

If the conditions for Upsert loading aren’t met, data will be loaded using Append-Only loading.

Refer to the [Understanding loading behavior guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples.

### Primary Keys

Stitch requires Primary Keys to de-dupe incrementally replicated data. To ensure Primary Key data is available, Stitch creates a `stitch.pks` [table property comment](https://docs.databricks.com/delta/delta-batch.html#table-properties-1) when the table is initially created in Databricks Delta Lake (AWS). The table property comment is an array of strings that contain the names of the Primary Key columns for the table.

For example: A table property comment for a table with a single Primary Key:

```
(stitch.pks="id")
```

And a table property comment for a table with a composite Primary Key:

```
(stitch.pks="id,created_at")
```

**Note**: Removing or incorrectly altering Primary Key table property comments can lead to replication issues.

### Incompatible sources

No compatibility issues have been discovered between Databricks Delta Lake (AWS) and Stitch's integration offerings.

See all [destination and integration incompatibilities.](https://help.qlik.com/en-US/stitch/destinations/destination-integration-compatibility)

## Transformations

* [System tables and columns](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--system-tables-columns)

* [Data typing](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--data-typing)

* [JSON structures](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--json-structures)

* [Column names](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--column-naming)

* [Timezones](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference#transformations--timezones)

### System tables and columns

Stitch will create the following tables in each integration’s dataset:

* [_sdc_rejected](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table)

Additionally, Stitch will insert [system columns](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns) (prepended with `_sdc`) into each table.

### Data typing

Stitch converts data types only where needed to ensure the data is accepted by Databricks Delta Lake (AWS). In the table below are the data types Stitch supports for Databricks Delta Lake (AWS) destinations, and the Stitch types they map to.

* **Stitch type:** The Stitch data type the source type was mapped to. During the Extraction and Preparing phases, Stitch identifies the data type in the source and then maps it to a common Stitch data type.
* **Destination type:** The destination-compatible data type the Stitch type maps to. This is the data type Stitch will use to store data in Databricks Delta Lake (AWS).
* **Notes:** Details about the data type and/or its allowed values in the destination, if available. If a range is available, values that exceed the noted range will be [rejected by Databricks Delta Lake (AWS)](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table).

| **Stitch type** | **Destination type** | **Notes** |
| --- | --- | --- |
| BIGINT | [UNSUPPORTED](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: The <code>BIGINT</code> type is not supported for this destination. If a record contains a <code>BIGINT</code> value, it will be sent to the <code>_sdc_rejected</code> table.</p> </li> </ul> |
| BOOLEAN | [BOOLEAN](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) |  |
| DATE | [TIMESTAMP](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: Stored in UTC</p> </li> <li> <p><strong>Range </strong></p><div><strong><button>the Warning 2 icon</button><div>Values that exceed this range will be rejected by the destination and logged in the &lt;strong&gt;_sdc_rejected&lt;/strong&gt; table. &lt;a href='/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table'&gt;Learn more&lt;/a&gt;.</div></strong></div><strong>:</strong>Timestamps before <code>1900-01-01T00:00:00Z</code> are not supported.<p></p> </li> </ul> |
| DOUBLE | [DECIMAL](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: Stored as <code>decimal(38,6)</code></p> </li> </ul> |
| FLOAT | [FLOAT](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) |  |
| INTEGER | [INT](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) |  |
| JSON ARRAY | [STRING](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: Used to store nested JSON structures (objects and arrays). JSON is loaded intact into the column, which will have a comment (<code>"json"</code>) specifying that the column contains JSON data.</p> </li> </ul> |
| JSON OBJECT | [STRING](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: Used to store nested JSON structures (objects and arrays). JSON is loaded intact into the column, which will have a comment (<code>"json"</code>) specifying that the column contains JSON data.</p> </li> </ul> |
| NUMBER | [DECIMAL](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) | <ul> <li> <p><strong>Description</strong>: Stored as <code>decimal(38,6)</code></p> </li> </ul> |
| STRING | [STRING](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference) |  |

### JSON structures

Databricks Delta Lake (AWS) supports nested records within tables. When JSON objects and arrays are replicated, Stitch will load the JSON intact into a `STRING` column and add a comment (`"json"`) specifying that the column contains JSON data.

Refer to [Databricks’ documentation](https://docs.databricks.com/spark/latest/dataframes-datasets/complex-nested-data.html) for examples and instructions on working with complex data structures.

### Column names

Column names in Databricks Delta Lake (AWS):

* Must contain only letters (a-z, A-Z), numbers (0-9), or underscores (`_`)

* Must begin with a letter or an underscore

* Must be less than the maximum length of 122 characters. Columns that exceed this limit will be [rejected by Databricks Delta Lake (AWS)](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table).

* Must not be prefixed or suffixed with any of [Stitch’s reserved keyword prefixes or suffixes](https://help.qlik.com/en-US/stitch/replication/loading/reserved-keywords#databricks-delta-v1-reserved-keywords)

Stitch will perform the following transformations to ensure column names [adhere to the rules imposed by Databricks Delta Lake (AWS)](https://help.qlik.com/en-US/stitch/destinations/databricks-delta/reference):

| **Transformation** | **Source column** | **Destination column** |
| --- | --- | --- |
| Convert uppercase and mixed case to lowercase | `CUSTOMERID` or `cUsTomErId` | `customerid` |
| Convert spaces to underscores | `customer id` | `customer_id` |
| Convert special characters to underscores | `customer#id` or `!customerid` | `customer_id` and `_customerid` |
| Prepend an underscore to names with leading numeric characters | `4customerid` | `_4customerid` |

### Timezones

Databricks Delta Lake (AWS) will store the value as `TIMESTAMP WITH TIMEZONE`. In Databricks Delta Lake (AWS), this data is stored with timezone information and expressed as UTC.

## Compare destinations

**Not sure if Databricks Delta Lake (AWS) is the destination for you?** Check out the [Choosing a Stitch Destination](https://help.qlik.com/en-US/stitch/destinations/choosing-a-stitch-destination) guide to compare each of Stitch’s destination offerings.