Skip to main content

Using SQL DW

This section describes how you interact through the Designer Cloud Powered by Trifacta platform with your Azure® Synapse Analytics (Formerly Microsoft® SQL DW)® data warehouse.

Limitations

  • Azure Synapse Analytics (Formerly Microsoft SQL DW) connections are available only if you have deployed the Designer Cloud Powered by Trifacta platform onto Azure.

  • The defined length of a table row cannot exceed 1 MB.

    Note

    In this release, this connection cannot be created through the APIs. Please create connections of this type through the application.

Uses of Azure Synapse Analytics (Formerly Microsoft SQL DW)

The Designer Cloud Powered by Trifacta platform can use Azure Synapse Analytics (Formerly Microsoft SQL DW) for the following tasks:

  1. Create datasets by reading from Azure Synapse Analytics (Formerly Microsoft SQL DW) tables.

  2. Write to Azure Synapse Analytics (Formerly Microsoft SQL DW) tables with your job results.

  3. Ad-hoc publication of data to Azure Synapse Analytics (Formerly Microsoft SQL DW).

Before You Begin Using Azure Synapse Analytics (Formerly Microsoft SQL DW)

  • Enable Access: Integration requires the following:

    • Installation of the Designer Cloud Powered by Trifacta platform on Microsoft Azure.

    • Either ADL or WASB is supported as the base storage layer. For more information, see Set Base Storage Layer.

  • Read Access: Your administrator must configure read permissions. Your administrator should provide a database for upload.

  • Write Access: You can write and publish jobs results to Azure Synapse Analytics (Formerly Microsoft SQL DW).

Secure Access

These connections require SSL access.

Storing Data

Your Azure Synapse Analytics (Formerly Microsoft SQL DW) administrator should provide database access for storing datasets. Users should know where shared data is located and where personal data can be saved without interfering with or confusing other users.

Note

The Designer Cloud Powered by Trifacta platform does not modify source data. Datasets sourced from Azure Synapse Analytics (Formerly Microsoft SQL DW) connections are read without modification from their source locations.

Reading Data

You can create a Alteryx dataset from a managed or external table through Azure Synapse Analytics (Formerly Microsoft SQL DW).

For more information, see Database Browser.

Writing to Azure Synapse Analytics (Formerly Microsoft SQL DW)

You can write back data to Azure Synapse Analytics (Formerly Microsoft SQL DW) using one of the following methods:

Note

Writing and publishing to Azure Synapse Analytics (Formerly Microsoft SQL DW) is not supported if Azure AD SSO has been enabled.

  • Job results can be written directly to Azure Synapse Analytics (Formerly Microsoft SQL DW) as part of the normal job execution. Create a new publishing action to write to Azure Synapse Analytics (Formerly Microsoft SQL DW). See Microsoft SQL Data Warehouse Table Settings.

  • As needed, you can publish results to Azure Synapse Analytics (Formerly Microsoft SQL DW) for previously executed jobs.

  • For more information on how data is converted to Azure Synapse Analytics (Formerly Microsoft SQL DW), see SQL DW Data Type Conversions.

Data Validation issues:

  • No validation is performed for the connection and any required permissions during job execution. So, you can be permitted to launch your job even if you do not have sufficient connectivity or permissions to access the data. The corresponding publish job fails at runtime.

  • No data validation is performed during writing and publication to Azure Synapse Analytics (Formerly Microsoft SQL DW). Your job fails if the schema for theAlteryx dataset varies from the target schema.

  • Prior to publication, no validation is performed on whether a target is a table or a view, so the job that was launched fails at runtime.

In this section: