Supported SQL syntax

The SQL syntax used in your data sources has an impact on the technical lineage.

Technical lineage supports SQL syntax that is relevant to process data for all supported data sources. This includes:

  • DML (Data Manipulation Language) statements that are used to move and transform data. For example, INSERT, UPDATE and MERGE.
    Note Technical lineage supports the extraction of DML statements from supported procedures, but it does not support all SQL syntax.
  • DDL (Data Definition Language) statements:
    • that impact the technical lineage. For example, ALTER TABLE, which you use to add or rename columns.
    • that are used to transform data. For example, CREATE A TABLE AS SELECT.
  • Relevant syntax constructs. For example, nested subselects, aliases, different join methods, synonyms and cross-database references.
Example You want to create a technical lineage for a Teradata source that has the following SQL syntax:
  • ALTER TYPE
  • ALTER PROCEDURE
  • CREATE/REPLACE AUTHORIZATION
  • MLOAD (MultiLoad)
  • RECORD (FastLoad)
  • BEGIN/END QUERY LOGGING
  • Functions with schema, for example schema_name.function.name(args...)
  • Functions with conversation, for example function_name(args...) RETURNS VARCHAR(<number>) CHARACTER SET LATIN
  • Macro argument attributes

Collibra Data Lineage will successfully parse this SQL syntax.

Not supported SQL syntax

Technical lineage does not support the following SQL syntax:

  • DML statements that you use to access data in complex structures such as JSON objects or structs.
  • Triggers, foreign keys and indexes.
  • Cursors and dynamic queries.
  • Streams queries.

Tip This is not an exhaustive list. If the SQL syntax that you use is not supported, you can add an idea in the Collibra Integrations Ideation Portal. We will evaluate the SQL syntax for inclusion.

Tip Dynamic SQL statements yield limited results. For example, SSRS uses the columns defined by the first SELECT statement in a stored procedure to determine the columns in the result set. Therefore, if you want a full ingestion, you need a static SQL statement. Fortunately, you can transform dynamic SQL statements into static statements. If the dynamic SQL can be logged at the runtime of a table, the dynamic query is transformed into a static query that can be extracted by Collibra Data Lineage and processed without limitations.