Azure Synapse Analytics (formerly SQL DW) support

11th November, 2023
Applies to: Dataedo 23.x (current) versions, Article available also for: 10.x

See also: Azure Synapse Pipelines

Dataedo supports both Dedicated and Serverless SQL pools.

Catalog and documentation

Data Dictionary

Dataedo imports 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 everyting with additional custom fields. Dataedo reads extended properties from 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 certain access level in documented database. The user used for importing or updating schema should at least have "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 Synapse instance create new documentation by clicking Add and choosing Database connection.

Image title

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

Image title

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. eg. john@example.com
      • 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 views scripts to get lineage
  • Copy history - Disable or enable getting lineage from parsing queries from sys.dm_pdw_exec_request

Image title

Connection details in Synapse Serverless

There are no differences in connecting to Synapse Serverless.

Saving password

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

Importing schema

When the connection was 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.

Image title

Confirm list of objects to import by clicking Next.

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

Image title

Click Import to start the import.

When done close import window with Finish button.

Image title

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

Importing changes

To sync any changes in the schema in Synapse and reimport any technical metadata simply choose 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 Save update command option. Downloaded file can be run in command line, what 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
Dependecies

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 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 retived using Dataedo SQL parser. Read more about capabilities of Transact-SQL SQL parser

Found issue with this article? Comment below
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.