Lineage for Stored Procedures (Beta)

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

As part of the metadata extraction (MDE) process, Alation extracts stored procedure metadata from the data sources that include stored procedures. Starting in version 2024.3.3 and applicable to Alation Cloud Service, Alation has enhanced MDE to also capture lineage information for stored procedures. This allows users to trace lineage paths from tables to the specific stored procedures that use these tables on lineage charts.

Lineage for stored procedures is supported by the following connectors and data sources:

  • Amazon Redshift

  • Azure SQL Database

  • Azure Synapse Analytics

  • Greenplum

  • IBM Db2

  • Microsoft SQL Server

  • MySQL

  • Oracle

  • OpenText Analytics Database (Vertica)

  • PostgreSQL

  • SAP Adaptive Server Enterprise

  • SAP HANA

  • SAP IQ

  • SingleStore

  • Teradata

Let’s assume you have a stored procedure in your SQL Server data source, where the data from the Books table is joined with the data from the Editors and Translators tables. The data is then transferred to the output of the sp_output_10 table.

To see this stored procedure on the lineage chart, you need to customize the extraction scope to include functions:

  1. On the Settings page of your data source, go to the Metadata Extraction tab.

  2. Select the Extract functions option when you customize the extraction scope.

    Note

    The Extract functions option can be found in various locations within the user interface under the Metadata Extraction tab, depending on the OCF connector and its version. For some connectors, this option is located under Connector Settings > Query Based Extraction. For others, it appears in the Customize Extraction Scope (optional) section.

  3. Run the metadata extraction option to extract the stored procedure.

  4. View the extraction status under MDE Job History tab.

  5. Open the catalog page of the table that is used in the stored procedure you’re interested in.

  6. Open the Lineage tab to view the lineage chart. The stored procedure is displayed on the chart as a lineage node.

You can copy the URL from the dataflow and paste it into the browser to see the corresponding function page. The page stores the specific stored procedure definition (SQL code).

The screenshot below illustrates how a lineage chart incorporates a stored procedure:

../../_images/SPL_Chart.png

The corresponding dataflow object will show the link to the stored procedure object in the Alation catalog. The structure of the URL is <base_url>/<otype>/<oid>, where

  • otype : Function Name

  • oid : Function ID

../../_images/SPL_DFC.png

Known Limitations

Consider the following limitations in stored procedure lineage when you use it:

  • The dataflow object content doesn’t appear with a hyperlink to the function definition. You have to manually open it using the URL structure of <base_url>/<otype>/<oid> , where

    • otype : Function Name

    • oid : Function ID

  • When you use SELECT * in queries, some of the column level information could be missing.

  • When the stored procedure has orphan columns, the lineage graph does not appear as expected as some of the source tables could be missing.