Azure Synapse Analytics (formerly SQL DW) support

See also: Azure Synapse Pipelines

Dataedo supports both Dedicated and Serverless SQL pools.

Catalog and documentation

Data Dictionary

Dataedo imports the following report objects from Synapse:

• Tables
• Views
• Procedures
• Functions
• Dependencies
• External data sources (as Linked Sources)

Descriptions, aliases, and custom fields

When technical metadata is imported, users will be able to edit descriptions of each object and element, provide meaningful aliases (titles), and document everything with additional custom fields. Dataedo reads extended properties from the following Synapse objects:

• Tables and External Tables
  • Columns
• Views
  • Columns
• Procedures
  • Parameters
• Functions
  • Parameters

Data Profiling

Users will be able to run data profiling for a table or view in the warehouse and then save selected data in the repository. This data will be available from Desktop and Web.

Connection requirements

• Importing database schema requires a certain access level in the documented database. The user used for importing or updating the schema should at least have the "View definition" permission granted on all objects that are to be documented. "Select" also works on tables and views.

Connecting to Synapse

Add new connection

To connect to a Synapse instance, create new documentation by clicking Add and choosing Database connection.

On the connection screen, choose Azure Synapse Analytics (SQL Data Warehouse).

Connection details

Provide connection details:

• Server name - Name of the server you would like to connect to
• Port - Port of the service
• Authentication - Select the way you want to authorize.
  • Username/password
    • Username - Username to your Synapse, e.g., [email protected]
    • Password - Password to your Synapse.
• Connection mode - Select the way you want to encrypt data
• Database - Name of the Synapse database
• Automatic lineage - Disable or enable importing lineage
• Parse SQL - Disable or enable parsing view scripts to get lineage
• Copy history - Disable or enable getting lineage from parsing queries from sys.dm_pdw_exec_request

Connection details in Synapse Serverless

There are no differences in connecting to Synapse Serverless.

Saving password

You can save the password for later connections by checking the Save password option. Passwords are securely stored in the repository database.

Importing schema

When the connection is successful, Dataedo will read objects and show a list of objects found. You can choose which objects to import. You can also use an advanced filter to narrow down the list of objects.

Confirm the list of objects to import by clicking Next.

The next screen allows you to change the default name of the documentation under which your schema will be visible in the Dataedo repository.

Click Import to start the import.

When done, close the import window with the Finish button.

Your database schema has been imported into new documentation in the repository.

Importing changes

To sync any changes in the schema in Synapse and reimport any technical metadata, simply choose the Import changes option. You will be asked to connect to Azure Synapse Analytics again, and changes will be synced from the source.

Scheduling imports

You can also schedule metadata updates using command-line files. To do it, after creating documentation, use the Save update command option. The downloaded file can be run in the command line, which will reimport changes to your documentation.

Specification

Imported metadata

	Imported	Editable
Tables & External Tables	✅	✅
Columns	✅	✅
Data types	✅
Nullability	✅
Description	✅	✅
Identity (is identity on)	✅
Default value	✅	✅
Views	✅	✅
Description	✅	✅
Script	✅	✅
Columns	✅
Data types	✅
Nullability	✅
Description	✅	✅
Identity (is identity on)	✅
Default value	✅	✅
Procedures	✅	✅
Script	✅	✅
Parameters	✅	✅
Functions	✅	✅
Script	✅	✅
Parameters	✅	✅
Returned value	✅	✅
Dependencies	✅	✅

Supported features

Feature	Imported
Import comments	✅
Write comments back
Data profiling	✅
Reference data (import lookups)
Importing from DDL
Generating DDL	✅
FK relationship tester	✅

Comments

Dataedo reads comments from the following Tableau objects:

Object	Read	Write back
Tables comments	✅
External tables comments	✅
Views comments	✅

Data Lineage

Source	Method	Version
Views (column-level)	From SQL parsing	✅
External Tables (object-level)	From sys.external_tables and sys.external_data_sources views	23.2 (2023)
COPY INTO tables (object-level)	Parsing queries from sys.dm_pdw_exec_request views	23.2 (2023)

Column-level data lineage is retrieved using the Dataedo SQL parser. Read more about the capabilities of the Transact-SQL SQL parser.