Azure Synapse

Adding Synapse to your decube connections helps your team to find relevant datasets, understand their quality via incident monitoring and apply governance policies via our data catalog.

Supported Capabilities

Supported Capabilities

General

• Metadata — metadata extraction and display of asset information (tables, columns, schemas). Types collected: Schema, Table, Column

• Profiling — data profiling on the Profiler tab

• Preview — sample data preview

• Data Quality — data quality monitoring and observability

• Configurable Collection — selective ingestion of schemas/workspaces in Data Source Management

• External Table — external tables, enabling queries on data in external storage (e.g., S3, ADLS) without loading it into the database

Data Quality Monitors

• Freshness

• Volume

• Field Health

• Custom SQL

• Schema Drift

Lineage

• External Table Lineage — tracks the relationship between raw files in cloud storage (S3/ADLS) and the virtualized relational schema in your data platform

Not Supported

General

• View Table

• Stored Procedure

Data Quality Monitors

• Job Failure

Lineage

• View Table Lineage

• SQL Query Lineage

• Foreign Key Lineage

• Stored Procedure Lineage

Connection Requirements

Connecting to Azure Synapse requires credentials that can be easily found through this guide. These credentials include:

• username

• password

• host

• database

Azure Data Lake Storage

Prerequisite

• Ensure the Azure account you logged into has access to Azure Synapse credentials.\

Getting the Credentials.

Log into your Azure account that has access to manage Synapse and click on the Synapse workspace of your choice. These are the information that you need to be aware of.

• Dedicated SQL server URL (1) or Serverless SQL endpoint (2). Depending on where the database you want to connect with decube resides, we will need to input this information for Host.

These are highlighted and numbered on the relevant image below.

Retrieve the Dedicated SQL Server URL or Serverless SQL endpoint from this screen.

Click on the Synapse workspace of your choice. These are the credentials that we will need from here.

1. Username

2. Password

3. Database name

1. Select the master database on the Use database drop-down and create this login, we recommend calling it decube_login .

Save this login value and password value, the login will be for the Username and Password credentials respectively.

CREATE LOGIN decube_login WITH PASSWORD = 'strong_password_here!';
GO

1. Select the database you wish to connect with decube on with the Connect to and Use database drop-down. Create a user with the mapping for the login. We recommend naming it decube_user .

Save the database name as we will need that as the Database credential.

CREATE USER decube_user
FOR LOGIN decube_login;
GO

1. (a) Grant the user permission to read the data based on your Synapse SQL pool type.

ALTER ROLE db_datareader ADD MEMBER decube_user;
GO

(b) If the database is in a Dedicated SQL pool:

EXEC sp_addrolemember 'db_datareader', 'decube_user';
GO

1. Add the permissions below to use Data Quality and Profiler.

GRANT VIEW DATABASE STATE TO decube_user;
GRANT VIEW DEFINITION TO decube_user;
GO

1. Grant Storage Credential Access (Serverless SQL Pools Only)

Grant the user permission to authenticate against Azure Storage.

Ensure the tables are created with the items below:

• A Database Scoped Credential created in the database (using Managed Identity).

• An External Data Source attached to the Database Scoped Credential.

• The External Tables have been successfully created using the External Data Source.

Run the command below, replacing [Your_Credential_Name] with the actual credential of your External Data Source:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[Your_Credential_Name] TO decube_user;
GO

For more information, refer to Microsoft's official documentation on using external tables and accessing external tables with Serverless Synapse.