# Microsoft Azure Synapse Analytics Destination Reference Information note **Stitch only supports connecting to Azure Synapse Analytics instances** Stitch’s Microsoft Azure Synapse Analytics destination only works with Microsoft’s [Microsoft Azure Synapse Analytics](https://azure.microsoft.com/en-us/services/synapse-analytics/). Stitch doesn’t currently support using Azure SQL Server or Azure SQL Database as a destination. Microsoft Azure Synapse Analytics is a fast, fully-managed, petabyte-scale data warehouse. It’s ideal for batch-based data warehouse workloads, and designed with a decoupled storage and compute model that allows it to scale quickly and be maintained in a cost-effective way. This guide serves as a reference for version 1 of Stitch’s Microsoft Azure Synapse Analytics destination. ## Details and features * [Stitch features](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#stitch-features) * [Destination details](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#destination-details) * [Microsoft Azure Synapse Analytics pricing](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#pricing) ### Stitch features High-level details about Stitch’s implementation of Microsoft Azure Synapse Analytics, such as supported connection methods, availability on Stitch plans, etc. | **Release status


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | Released | | --- | --- | | **Stitch plan availability


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | All Stitch plans | | **Stitch supported regions


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** |

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


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | Not applicable | | **Connect API availability


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Supported
the Success iconSupported**

This version of the Microsoft Azure Synapse Analytics 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


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Supported
the Success iconSupported**

Stitch supports using SSH tunnels to connect to Microsoft Azure Synapse Analytics destinations. | | **SSL connections


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Supported
the Success iconSupported**

Stitch will attempt to use SSL to connect by default. No additional configuration is needed. | | **VPN connections


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Unsupported
the Remove iconNot supported**

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


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Supported
the Success iconSupported**

This version of the Microsoft Azure Synapse Analytics destination has static IP addresses that can be whitelisted. | | **Default loading behavior


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Upsert**

**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


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | **Unsupported**

the Remove icon

Unupported


Nested data structures will be flattened into relational objects. [Learn more](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/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


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 1MB

This limit is imposed by PolyBase, which is required to load data into Microsoft Azure Synapse Analytics destinations. | | --- | --- | | **Table name length


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 112 characters | | **Column name length


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 128 characters | | **Maximum columns per table


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 1,024 | | **Maximum table size


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 60 TB compressed on disk | | **Maximum tables per database


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | 10,000 | | **Case sensitivity


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | Insensitive | | **Reserved keywords


[the Help icon](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference)** | Refer to the [Reserved keywords documentation](https://help.qlik.com/en-US/stitch/). | ### Microsoft Azure Synapse Analytics pricing Microsoft Azure Synapse Analytics bases their pricing on your compute and storage usage. Compute usage is charged using an hourly rate, meaning you’ll only be billed for the hours your data warehouse is active. Compute usage is billed in one hour increments. Storage charges include the size of your primary database and seven days of incremental snapshots. Microsoft Azure rounds charges to the nearest terabyte (TB). For example: If the data warehouse is 1.5 TB and you have 100 GB of snapshots, your bill will be for 2 TB of data. Refer to [Microsoft’s documentation](https://azure.microsoft.com/en-us/pricing/details/synapse-analytics/) for more info and examples. ## Replication * [Replication process overview](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#replication-process) * [Loading behavior](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#loading-behavior) * [Primary Keys](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#replication--primary-keys) * [Incompatible sources](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#replication--incompatible-sources) ### Replication process overview * [Step 1: Data extraction](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#replication--data-extraction) * [Step 2: Stitch’s internal pipeline](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#replication--stitch-internal-pipeline) * [Step 3: Load data into Azure Blob Storage](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#load-into-blob-storage) * [Step 4: Prep data using Polybase](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#prep-data-polybase) * [Step 5: Insert data into the data warehouse](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#bulk-load-from-polybase) #### 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/microsoft-azure-sql-data-warehouse/reference#transformations) section for more info about the transformations Stitch performs for Microsoft Azure Synapse Analytics 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: Load data into Azure Blob Storage Stitch loads the extracted data into [Azure Blob Storage](https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction). Blob storage is intended for storing massive amounts of unstructured data. In the next step, Stitch will use Polybase to retrieve the data from Blob Storage and prepare it for loading into Microsoft Azure Synapse Analytics. #### Step 4: Prep data using Polybase Information note **Note**: Polybase has its own set of limitations that may make it impossible to load certain data. Refer to the [Limitations](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#limitations) section for more info. [Polybase](https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-2017) is a Microsoft offering that integrates Microsoft SQL products with Hadoop. Polybase is needed to query data from Azure Blob Storage. In this step, Stitch will perform the following: 1. **Create an external data source**. This creates an [external data source](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-2017) for the Polybase queries Stitch will run. 2. **Create an external file format**. This creates [an object that defines the external (extracted) data](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-2017) Stitch will load. This is used in the next step to create an external table. 3. **Create an external table**. Using the external file format, this will [create an external table](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017). The external table is used to stage the data from Azure blob storage and load it into your Microsoft Azure Synapse Analytics data warehouse. #### Step 5: Insert data into the data warehouse Lastly, Stitch will insert the data from the external table in Polybase into your Microsoft Azure Synapse Analytics data warehouse. ### Loading behavior By default, Stitch will use **Upsert loading** when loading data into Microsoft Azure Synapse Analytics. 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 an `_sdc_primary_keys`table in every integration dataset. This table contains a list of all tables in an integration’s dataset and the columns those tables use as Primary Keys. Refer to the [_sdc_primary_keys table documentation](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/primary-keys-system-table) for more info. **Note**: Removing or altering this table can lead to replication issues. ### Incompatible sources No compatibility issues have been discovered between Microsoft Azure Synapse Analytics 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/microsoft-azure-sql-data-warehouse/reference#transformations--system-tables-columns) * [Data typing](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#transformations--data-typing) * [JSON structures](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#transformations--json-structures) * [Column names](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#transformations--column-naming) * [Timezones](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference#transformations--timezones) ### System tables and columns Stitch will create the following tables in each integration’s dataset: * [_sdc_primary_keys](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/primary-keys-system-table) * [_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 Microsoft Azure Synapse Analytics. In the table below are the data types Stitch supports for Microsoft Azure Synapse Analytics 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 Microsoft Azure Synapse Analytics. * **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 Microsoft Azure Synapse Analytics](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). | **Stitch type** | **Destination type** | **Notes** | | --- | --- | --- | | BIGINT | [BIGINT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15) | | | BOOLEAN | [BOOLEAN](https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15) | | | DATE | [DATETIMEOFFSET](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=aps-pdw-2016-au7) | | | DOUBLE | [FLOAT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15) | | | FLOAT | [FLOAT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15) | | | INTEGER | [BIGINT](https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15) | | | NUMBER | [DECIMAL](https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver15) | | | STRING | [NVARCHAR](https://help.qlik.com/en-US/stitch/destinations/microsoft-azure-sql-data-warehouse/reference) | | ### JSON structures Microsoft Azure Synapse Analytics destinations don’t have native support for nested data structures. To ensure nested data can be loaded, Stitch will flatten objects and arrays into columns and subtables, respectively. For more info and examples, refer to the [Handling nested data structures guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-row-usage-for-nested-data-structures). ### Column names Column names in Microsoft Azure Synapse Analytics: * Must be less than the maximum length of 128 characters. Columns that exceed this limit will be [rejected by Microsoft Azure Synapse Analytics](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 [Microsoft Azure Synapse Analytics’s or Stitch’s reserved keyword prefixes or suffixes](https://help.qlik.com/en-US/stitch/) Stitch will perform the following transformations to ensure column names [adhere to the rules imposed by Microsoft Azure Synapse Analytics](https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15#rules-for-regular-identifiers): | **Transformation** | **Source column** | **Destination column** | | --- | --- | --- | | Convert spaces to underscores | `customer id` | `customer_id` | | Convert special characters to underscores | `customer#id` or `!CuStOMerID` | `customer_id` and `_CuStOMerID` | | Convert leading numbers to underscores | `4customerid` | `_customerid` | ### Timezones Microsoft Azure Synapse Analytics will store the value in UTC with the specified offset. ## Compare destinations **Not sure if Microsoft Azure Synapse Analytics is the destinationfor 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.