Syncing Datasources

Use the datasource sync functionality to automatically extract and sync metadata from the datasources in your organization. It is also possible to use file imports and manually manage datasources in Katalogue, but these methods are intended as a backup for cases where live connections to the datasources is not possible.

To set this up, you must first create a connection in Katalogue and configure it with connection credentials (see supported connectors in the Connectors section). Then, you must create a datasource sync task for the datasource, and optionally assign the sync task to a Schedule to automatically run the task at a certain interval.

Short vocabulary:

Term	Description
Connection	Connections are used to define how to connect to a datasource. They typically hold connection credentials and optional filter logic.
Task	Tasks are job definitions. Specifically Datasource Sync Tasks uses a Connection to connect to datasources and sync the source data with the data in Katalogue.
Job	A Job is a single run, or instantiation, of a Task.
Schedule	Tasks can be assigned to a Schedule to execute the Task repeatedly on a schedule.

Creating a Connection

1. Login as an admin user.
2. Go to Connections in the Manage section and click +ADD to create a Connection.
3. Set the following properties under the General tab:
   • Name: Unique display name of the connection.
   • Connector: The connector for the datasource type.
   • Description: Optional description of the connection.
   • System: The system the imported metadata will be organized under.
4. Go to the Connection tab and enter the relevant connection credentials for your connector. See the Connectors section for details on each connector.
5. Optionally, go to the Filters tab to set a datasource filter.
6. Optionally, and if supported by the selected connector, go to the Advanced tab to set e.g. custom import queries
7. Click the ADD button to save the connection.

Creating a Datasource Sync Task

A datasource sync task is the job definition for connecting to a datasource. The sync task uses a Connection to actually connect.

1. Login as an admin user.

2. Go to Tasks in the Manage section and click +ADD to create a Task.

3. Set the following properties:

   • Name: Unique display name of the task.
   • Description: Optional description of the task.
   • Task Type: Datasource Sync
   • Connection: Select an existing connection the Task will use, see Creating a Connection
   • Schema Change Notification Email: Optionally, specify one or more email addresses that will receive an email notification if the sync detects schema changes. This property is only available if the email notifications feature under Settings is enabled.

4. Optionally, go to the Schedule tab and assign the task to one or more Schedules to run the Task automatically.

5. Click the ADD button to save the task.

6. To run the datasource sync task, select the task in the list under Tasks and click “Run”.

7. Open the task to see its job log, and click a job to see more details like job steps and a changelog.

Datasource Filters

Datasource filters is a feature to limit the data extracted from the source system. If no filters are provided, all tables, fields etc found in the INFORMATION_SCHEMA (or similar views) are imported - with the exception for some common tables like the INFORMATION_SCHEMA views themselves.

Where the filter is applied depends on the connector. For database connectors the filter is applied in the extract query from the datasource, so data that is filtered out never leaves the datasource. For connectors that fetch their whole source into memory (the API, File and dbt connectors), the source is fetched in full and the filter is applied within Katalogue before import. The connector page for each connector states which applies.

Not the same as a REST API filter

Katalogue has two filter syntaxes, for two different jobs. A datasource filter narrows what is imported into Katalogue from a source system, and is written as the JSON object described below. The REST API’s $filter parameter narrows what you read out of Katalogue, is written as an expression in a query string, and uses different operator names — eq and contains where a datasource filter uses equal and ilike.

Example

The datasource filters are expressed as a JSON object with the following pattern:

{
  "datasource_filter": [
    {
      "concatenator": "and",
      "filters": [
        {
          "field": "dataset_name",
          "operator": "not_equal",
          "value": "field"
        },
        {
          "field": "dataset_group_name",
          "operator": "in",
          "value": ["public", "stage"]
        }
      ]
    }
  ]
}

For a PostgreSQL datasource, the example above translates to the following SQL:

...
WHERE ( c.table_name <> 'field'
  AND c.table_schema = ANY('{"public","stage"}') )

Valid Concatenators

• and
• or

All filters in the “filters” array are enclosed in a paranthesis and concatenated with the selected concatenator. It is possible to nest concatenators like so:

{
  "datasource_filter": [
    {
      "concatenator": "and",
      "filters": [
        {
          "field": "dataset_name",
          "operator": "not_equal",
          "value": "field"
        },
        {
          "concatenator": "or",
          "filters": [
          {
            "field": "dataset_group_name",
            "operator": "equal",
            "value": "public"
          },
          {
            "field": "dataset_group_name",
            "operator": "equal",
            "value": "stage"
          }
        ]
        }
      ]
    }
  ]
}

For a Postgres datasource, the example above results in the following SQL:

...
WHERE ( c.table_name <> 'field'
  AND ( c.table_schema = 'public'
   OR c.table_schema = 'stage' ) )

Valid Fields

Valid fields to filter on:

• datasource_name
• dataset_group_name
• dataset_name
• dataset_type_name
• field_name
• datatype_name
• datasource_source_id
• dataset_group_source_id
• dataset_source_id
• field_source_id
• dataset_technical_definition
• dataset_subtype (the subtype of the dataset that owns the branch — an API endpoint’s HTTP method, e.g. get, post, or a Power BI item’s kind, e.g. semantic_model, report)

Valid Operators

Valid operators and the corresponding SQL map:

Katalogue operator	SQL equivalent
equal	=
not_equal	<>
greater	>
less	<
greater_equal	>=
less_equal	<=
=	=
<>	<>
>	>
<	<
>=	>=
<=	<=
in	IN
not_in	NOT IN
is	IS
is_not	IS NOT
like	LIKE
not_like	NOT LIKE
ilike	ILIKE
not_ilike	NOT ILIKE

ILIKE (i.e. case-insensitive LIKE) and NOT ILIKE are translated to valid SQL in databases that do not support these commands natively, e.g. Oracle and SQL Server. LIKE, NOT LIKE, ILIKE and NOT ILIKE also works with an array as value for Postgres and Snowflake datasources. This is translated to a (<field> LIKE <val1> OR <field> LIKE <val2> ...) clause for datasources that does not support LIKE with arrays natively.

Wildcard Character

The SQL Wildcard character ”%” is supported for any of the LIKE operators. Use it in the “value” part of the filter clause like so:

{
  "datasource_filter": [
    {
      "concatenator": "and",
      "filters": [
        {
          "field": "dataset_name",
          "operator": "ilike",
          "value": ["field", "dataset%"]
        }
      ]
    }
  ]
}

For a Snowflake datasource, the example above results in the following SQL:

...
WHERE ( c.TABLE_NAME ILIKE ANY ('field', 'dataset%') )

Datasource Filters And Custom Import Queries

Datasource filters are supported in the same way as stated above when using your own custom import query

Relationships

A relationship in Katalogue is a connection between two Fields, and therefore shows how datasets can be combined. Understanding this is and having them visualized is often very useful for analysts and other data consumers, as combining data from different datasets is a very common task.

Katalogue imports primary key/foreign key constraints automatically when a datasource declares them. However, plenty of datasources declare none. A constraint may be unsupported between database views, or simply never have been created, and a star schema modelled as views in a data warehouse often end up with no relationships at all. So where the source system is silent, Katalogue comes with two features to supply the relationships instead: relationship map rules, which you write based on naming logic, and virtual relationships, which Katalogue infers from your glossary.

This means Katalogue have three different kinds of relationships, and each relationship is typed to say where it came from. This is a label only, not tied to any behaviour, and is shown on a Field’s Relationship tab.

1. physical: a primary key/foreign key constraint imported automatically from the datasource.
2. map-rule: created by a rule you write — see Relationship Map Rules.
3. virtual: inferred from a shared Attribute — see Virtual Relationships.

Katalogue records relationships with the same priority as the list above, i.e. if there already is a physical relationship between two fields, no duplicate map-rule or virtual relationships will be created.

Where the settings live

Each feature can be configured on the Connection by admin users on the Advanced tab.

Setting	Belongs to	Purpose
Relationship Import Query	Physical relationships	For relational databases, the defeault import query for primary key/foreign key relationships can be modified. See Custom Relationship Import Query.
Relationship Map Rules	Relationship map rules	A JSON object holding the rules themselves, in a relationshipMap section, and optionally a contextFilters section narrowing what they produce. See Map Rule Example and Relationship Context Filters.
Enable Virtual Relationships	Virtual relationships	Whether Katalogue infers relationships for this datasource at all.
Exclusion Filters	Virtual relationships	A JSON object holding an exclusionFilters section, selecting the Fields that never take part in an inferred relationship by column name or by Dataset, Dataset Group or Datasource. See Exclusion filters.

Relationship Map Rules

A map rule states, in terms of Field and Dataset names, which Fields should be related. It is the way to describe a relationship the datasource cannot declare for itself. One example is matching CUSTOMER_DWKEY in any table to the key of D_CUSTOMER.

Rules go in the relationshipMap section of the connection’s Relationship Map field, and are applied in the import query from the Katalogue stage.raw_relationship table to the public.relationship table.

Map Rule Example

The relationship map rules are expressed as a JSON object with the following pattern:

{
  "relationshipMap": [
    {
      "concatenator": "and",
      "mapRules": [
        {
          "left": {
            "regexp": "(.*__)?(.*)_DWKEY",
            "captureGroup": 2,
            "field": "from_field_name"
          },
          "operator": "equal",
          "right": {
            "regexp": "D_(.*)",
            "field": "to_dataset_name"
          }
        },
        {
          "left": {
            "regexp": "(.*__)?(.*)",
            "captureGroup": 2,
            "field": "from_field_name"
          },
          "operator": "equal",
          "right": {
            "field": "to_field_name"
          }
        },
        {
          "left": {
            "field": "from_dataset_name"
          },
          "operator": "not_equal",
          "right": {
            "field": "to_dataset_name"
          }
        },
        {
          "left": {
            "field": "from_dataset_group_name"
          },
          "operator": "equal",
          "right": {
            "field": "to_dataset_group_name"
          }
        }
      ]
    }
  ]
}

The example above contain four map rules with the following meaning:

1. Look at all field names that may contain the prefix *__ and contain the suffix _DWKEY and match them with dataset names that has the prefix D_. Example: Fields with name CUSTOMER_DWKEY and for example PREVIOUS__CUSTOMER_DWKEY in any table will match all fields in table D_CUSTOMER.
2. Only match field names with the same name. Example: Field CUSTOMER_DWKEY in any table will only match field CUSTOMER_DWKEY.
3. Do not match fields in the same dataset. This is to prevent self-join relationships. Example: Field CUSTOMER_DWKEY in table D_CUSTOMER will not relate to itself.
4. Only match fields and datasets within the same dataset group. Example: Create relationships within schema MART_CUSTOMER and schema MART_FINANCE etc, but no relationships between them.

As these four rules are combined with an “and” logic, the result of them will be as follows:

“Create a relationship from field CUSTOMER_DWKEY in any table to field CUSTOMER_DWKEY in table D_CUSTOMER as long as the tables are in the same schema.”

The example above results in the following SQL (PostgreSQL), where the map rules get translated to the join conditions for the INNER JOIN at the bottom:

...

"SELECT All PK/FK constraints in the datasource"

UNION

SELECT
      NULL                              AS relationship_name
    , 'map-rule'                        AS relationship_type
    , f_from.datasource_name            AS from_datasource_name
    , f_from.dataset_group_name         AS from_dataset_group_name
    , f_from.dataset_name               AS from_dataset_name
    , f_from.field_name                 AS from_field_name
    , f_from.dataset_type_name          AS from_dataset_type_name
    , f_from.datatype_name              AS from_datatype_name
    , f_to.datasource_name              AS to_datasource_name
    , f_to.dataset_group_name           AS to_dataset_group_name
    , f_to.dataset_name                 AS to_dataset_name
    , f_to.field_name                   AS to_field_name
    , f_to.dataset_type_name            AS to_dataset_type_name
    , f_to.datatype_name                AS to_datatype_name
    , now()                             AS relationship_source_created

FROM fieldsCTE f_from
INNER JOIN fieldsCTE f_to ON (
    (regexp_match("f_from"."field_name",'(.*__)?(.*)_DWKEY','i'))[2] = (regexp_match("f_to"."dataset_name",'D_(.*)','i'))[1]
  AND "f_from"."dataset_name" <> "f_to"."dataset_name"
  AND "f_from"."dataset_group_name" = "f_to"."dataset_group_name"
)

...

Map Rule Concatenators

• and
• or

All rules in the “mapRules” array are enclosed in a paranthesis and concatenated with the selected concatenator. It is possible to nest concatenators.

Map Rule Fields

Valid fields to match on: These placeholders are available in the “field” property in the “left” and “right” part of a map rule.

• from_datasource_name
• from_dataset_group_name
• from_dataset_name
• from_field_name
• from_dataset_type_name
• from_datatype_name
• to_datasource_name
• to_dataset_group_name
• to_dataset_name
• to_field_name
• to_dataset_type_name
• to_datatype_name

Map Rule Operators

Valid operators and the corresponding SQL map:

Katalogue operator	SQL equivalent
equal	=
not_equal	<>
greater	>
less	<
greater_equal	>=
less_equal	<=
=	=
<>	<>
>	>
<	<
>=	>=
<=	<=
in	IN
not_in	NOT IN
is	IS
is_not	IS NOT
like	LIKE
not_like	NOT LIKE
ilike	ILIKE
not_ilike	NOT ILIKE

Regexp Flags and Capture Groups

It is possible to provide flags and use capture groups in the regular expression like so:

{
  "relationshipMap": [
    {
      "concatenator": "and",
      "mapRules": [
        {
          "left": {
            "regexp": "(.*__)?(.*)_DWKEY",
            "captureGroup": 2,
            "flags": "i",
            "field": "from_field_name"
          },
          "operator": "equal",
          "right": {
            "regexp": "D_(.*)",
            "field": "to_dataset_name"
          }
        }
      ]
    }
  ]
}

If no flags or capture group are provided, these are the defaults:

"captureGroup": 1
"flags": "i"

Relationship Context Filters

It is possible to include a filter clause along with the relationship map rules to limit them to certain parts of the datasource. Example:

{
  "contextFilters": [
    {
      "concatenator": "and",
      "filters": [
        {
          "field": "from_dataset_group_name",
          "operator": "not_equal",
          "value": "MY_SCHEMA"
        },
    {
          "field": "to_dataset_group_name",
          "operator": "not_equal",
          "value": "MY_SCHEMA"
        }
      ]
    }
  ],
  "relationshipMap": [
  ...
  ]
}

The example above results in this SQL (PostgreSQL):

...

SELECT *
FROM (
    "SELECT All PK/FK constraints in the datasource"

    UNION

    "SELECT All relationships generated by map rules"
)
WHERE ( from_dataset_group_name <> 'my_schema'
AND to_dataset_group_name <> 'my_schema' )

...

The context filter syntax is exactly the same way as for the datasource filters with these exceptions:

• Valid fields to filter on are the same as the fields available to use in the relationship map rules.
• Context filter values are case-insensitive.

Virtual Relationships

Katalogue can infer relationships between fields that are associated with the same Attribute. An Attribute in Katalogue defines a piece of information, which is materialized in the fields associated with it. Hence, two Fields associated with the same Attribute carry the same information, whatever the datatype or technical format each system happens to use. If any of the fields have existing relationships, or is a primary key, logic dictates that the other fields are identifiers too. Hence, a virtual relationship describes a join path Katalogue believes is possible, not a constraint it found.

Two rules create them.

Rule A — inheritance. A Field with no relationship of its own takes the place of a Field that has one, when the two are associated with the same Attribute. The other end and the direction stay as they were. Standing in on the referenced side additionally requires being a primary key — that is the side a foreign key points at.

Orders.CustomerId references Customers.Id. Returns.CustomerId has no relationship, and both CustomerId Fields are associated with the Attribute Customer Id. Katalogue infers Returns.CustomerId → Customers.Id.

Rule B — primary keys. A Field is a foreign key to every primary key associated with the same Attribute.

Sales.DateKey is associated with Date Key, and so is DimDate.DateKey, which is a primary key. Katalogue infers Sales.DateKey → DimDate.DateKey.

Where several Fields qualify, all of them are related: each is a join path that really is available, and a path into another schema or database is often the most useful thing to know.

What the rules will not do:

• A Field that already has a physical or map-rule relationship gains no virtual ones. Katalogue fills gaps rather than second-guessing a source system that has already spoken.
• A primary key is never the referencing side, and the referenced side is always a primary key. A primary key is what a foreign key points at; it is not itself a foreign key. So a Field that merely shares an Attribute is not pointed at as though it were a key — which is what would otherwise happen to a View’s copy of a column, or to a denormalised duplicate of one. A source that declares no primary keys at all therefore infers nothing on that side.
• A multi-column relationship is inherited only whole. For a Dataset to stand in for a two-column key, it must carry both of the Attributes involved — one Field cannot stand in for a composite key. For the same reason, rule B skips a Dataset whose primary key spans several columns.
• An Attribute whose role is Measure infers nothing. A quantity you aggregate is not a key.
• A virtual relationship never creates another. Only relationships the source system stated, or a map rule produced, are used as the starting point, so repeated syncs cannot compound.
• Relationships are never inferred across Systems. They are inferred freely across Dataset Groups and Datasources within one System.

Virtual relationships are kept up to date as you work: associating a Field with an Attribute, or removing it, immediately adds or removes the relationships the rules justify — including relationships other Fields gain, which is what happens when the Field you associate is a primary key.

Disabling virtual relationships

The virtual relationships feature is enabled by default, but it can be switched off for the entire datasource by unticking the Enable Virtual Relationships checkbox under the Advanced tab in the datasource’s Connection settings.

Exclusion filters

Some columns appear in every table without ever being a useful join — audit and housekeeping columns such as owner_user_id or created_by. Every table carrying one means every table can be joined to every other, which is true, but is mostly noise and of no use to anyone.

Rather than switching the feature off altogether, name those columns in the Connection’s Exclusion Filters field. As with the relationship map, the value is a JSON object with one named section. A filter selects what is left out: a Field it matches takes no part in a virtual relationship at either end. The following example prevents virtual relationships where any of the given fields are part of the relationship.

{
  "exclusionFilters": ["owner_user_id", "created_by"]
}

A plain list of names inside that section is shorthand. It also accepts the datasource filter clause syntax used elsewhere, which allows patterns and lets you filter by Dataset, Dataset Group or Datasource as well as by column name:

{
  "exclusionFilters": [
    { "field": "field_name", "operator": "like", "value": "%_user_id" },
    { "field": "dataset_group_name", "operator": "equal", "value": "staging" }
  ]
}

A clause may name field_name, dataset_name, dataset_group_name or datasource_name, and values are matched case-insensitively, as elsewhere. Clauses are not directional: a column that is never join material is never join material at either end, so it is named once rather than as a from- and a to-side.

The setting belongs to the datasource’s connection and follows its Fields wherever generation happens — so a Field excluded here stays excluded even when the relationship would be created by an Attribute change made in the glossary.

Custom Import Queries

This feature is supported by all DB-related connectors:

• Databricks
• IBM DB2
• Microsoft SQL Server
• ODBC
• Oracle
• PostgreSQL
• Snowflake

Katalogue uses pre-defined and connector-specific SQL queries when connecting to a relational database. It is possible to review and override these default queries with user-defined custom queries. Some use cases for this are:

• Provide custom filters instead of using Katalogue’s datasource filters feature described below.
• Use custom logic to retrieve e.g. primary key definitions. Useful in scenarios where actual primary key constraints are missing but where the primary keys can be derived from unique indexes, naming conventions etc.
• You do not want to, or do not have access to, connect to the actual source system directly and therefore use the datasource as an intermediary.

Katalogue uses three import queries to get all metadata it needs;

1. Base Import Query Imports columns, datasets, schemas and related metadata such as column datatype, primary keys etc.
2. Relationship Import Query Imports relationships (PK/FK constraints) between tables.
3. Lineage Import Query Imports data lineage between tables.

Define a custom query like so:

1. Create a Connection and select a Connection Type that support custom import queries
2. Go to the “ADVANCED” tab and enable “Use Custom Base Import Query”.
3. Click the “Edit Base Import Query” button to open a dialog where you can enter a custom SQL select statement. See requirements below.
4. It is possible to retrieve the default import query for the Connection Type by clicking the “Insert Default Query” button in the dialog
5. Click “Save” to save the query and close the dialog
6. Optionally, repeat the steps above for the “Use Custom Relationship Import Query” and “Use Custom Lineage Import Query” option to provide a custom SQL select statement for PK/FK constraints and dataset lineage.

Note that SELECT-statements are the only supported SQL statement. Katalogue will validate and sanitize the query before execution to prevent SQL injections.

Custom Base Import Query

The custom import query must return all columns with the exact names (case sensitive) listed below. The mandatory columns must be present, cannot be null nor an empty string. Those columns are the minimum data needed for Katalogue to correctly import data.

Caution

The columns suffixed with “_source_id” are strongly recommended. Including them enables Katalogue to handle renames gracefully, as assets are primarily matched by source id, with a fallback to match on name. If e.g. a dataset_group (database schema) is renamed, and Katalogue does not have a stable source id of the schema, Katalogue will perform a delete and insert instead of an update. A delete & insert means that associations with Attributes, Custom Properties etc will be lost for the schema, all tables and their columns.

Column Name	Datatype	Description	Is Mandatory
datasource_name	string	Database name	Yes
dataset_group_name	string	Schema name	Yes
dataset_name	string	Name of the table/view	Yes
dataset_type_name	string	Type of dataset. Normally either “table” or “view”, but can be anything that matches the values in public.dataset_type.dataset_type_code column.	Yes
field_name	string	Column name	Yes
datatype_name	string	Datatype name. Should be the raw name without appended precision and scale.	Yes
datatype_length	integer	Character length
datatype_precision	integer	Numeric precision
datatype_scale	integer	Numeric scale
datasource_hostname	string	If present, this will override the hostname derived from the connector. I.e. it is possible to set this to “//server.mydomain.com” eventhough the hostname in the connector is something else. This is primarily used when deriving data from a proxy source, or by file-based connectors.
datasource_type_name	string	Database type. If present, this will override the datasource type derived from the connector. I.e. it is possible to set this to “Microsoft SQL Server” eventhough the connector is to an Oracle database. The datasource will then show up as a “Microsoft SQL Server” datasource in the GUI instead of an Oracle datasource.
datasource_source_id	string	Unique, internal Id of the database in the source database. If present, Katalogue will use this to handle renames better by doing an update instead of a delete & insert statement.
dataset_group_source_id	string	Unique, internal Id of the schema in the source database. If present, Katalogue will use this to handle renames better by doing an update instead of a delete & insert statement.
dataset_source_id	string	Unique, internal Id of the table/view in the source database. If present, Katalogue will use this to handle renames better by doing an update instead of a delete & insert statement.
field_source_id	string	Unique, internal Id of the column in the source database. If present, Katalogue will use this to handle renames better by doing an update instead of a delete & insert statement.
dataset_row_count	bigint	Number of rows in the dataset. Normaly taken from a statistics table (much faster than a “select count(*)”).
dataset_size_bytes	bigint	Total disk size in bytes of the dataset.
is_primary_key	boolean	True if the column is part of a primary key constraint
is_nullable	boolean	True if the column can be NULL
ordinal_position	integer	Ordinal position of the column
default_value	string	Default value of the column
dataset_url	string	URL to the dataset. If present, this will show up as a clickable link in the GUI.
datasource_source_created	timestamp	Timestamp for when the database was created
dataset_group_source_created	timestamp	Timestamp for when the schema was created
dataset_source_created	timestamp	Timestamp for when the table/view was created
datasource_source_modified	timestamp	Timestamp for when the database was last altered
dataset_group_source_modified	timestamp	Timestamp for when the schema was last altered
dataset_source_modified	timestamp	Timestamp for when the table/view was last altered
dataset_technical_definition	string	Normally the SQL statement for views, but can be any transformation logic to populate a table or view.
dataset_technical_definition_raw	string	This can be used if the SQL statement/transformation logic is dynamically created. This is the case for e.g. dbt datasources, then this column will contain the raw, uncompiled dbt code, and the “dataset_technical_definition” column contains the compiled SQL.
datasource_description	string	Comment or description of the database
dataset_group_description	string	Comment or description of the schema
dataset_description	string	Comment or description of the table/view
field_description	string	Comment or description of the column
dataset_subtype_code	string	Code of the subtype of the dataset that owns this branch: an API endpoint’s method (get, post, put, patch, delete), or a Power BI item’s kind (semantic_model, report, …). Set on the dataset that owns it and inherited by every row below it.
dataset_code_path	string	Used for datasources that support nested datasets, such as APIs. Root-to-node array of dataset_code segments (lower(trim(name))) encoding the dataset hierarchy. Used to match existing datasets (vs a recursive-CTE-built path) and to derive the parent (path minus last element) and depth (cardinality).
field_code_path	string	Used for datasources that support nested fields, such as APIs. Root-to-node array of field_code segments encoding the field hierarchy within a dataset. Same role as dataset_code_path, for fields.

Example query (Microsoft SQL Server connector default query):

SELECT
    c.TABLE_CATALOG                AS "datasource_name"
  , c.TABLE_SCHEMA                 AS "dataset_group_name"
  , c.TABLE_NAME                   AS "dataset_name"
  , CASE
      WHEN o.type = 'V'
      THEN 'view'
      ELSE 'table'
    END                            AS "dataset_type_name"
  , p.num_rows                     AS "dataset_row_count"
  , ts.bytes                       AS "dataset_size_bytes"
  , c.COLUMN_NAME                  AS "field_name"
  , c.DATA_TYPE                    AS "datatype_name"
  , c.CHARACTER_MAXIMUM_LENGTH     AS "datatype_length"
  , CASE
     WHEN c.NUMERIC_PRECISION_RADIX = 2 THEN c.NUMERIC_PRECISION/8
     ELSE coalesce(c.NUMERIC_PRECISION, c.DATETIME_PRECISION)
    END                            AS "datatype_precision"
  , CASE
     WHEN c.NUMERIC_PRECISION_RADIX = 2 THEN c.NUMERIC_SCALE/8
     ELSE c.NUMERIC_SCALE
    END                            AS "datatype_scale"
  , null                           AS "field_source_id" -- Not supported by this datasource
  , CASE
      WHEN pk.COLUMN_NAME IS null
      THEN 0
      ELSE 1
    END                            AS "is_primary_key"
  , CASE
      WHEN c.IS_NULLABLE = 'YES' THEN 1
      ELSE 0
    END                            AS "is_nullable"
  , c.COLUMN_DEFAULT               AS "default_value"
  , c.ORDINAL_POSITION             AS "ordinal_position"
  , d.create_date                  AS "datasource_source_created"
  , null                           AS "dataset_group_source_created" -- Not supported by this datasource
  , t.create_date                  AS "dataset_source_created"
  , null                           AS "datasource_source_modified" -- Not supported by this datasource
  , null                           AS "dataset_group_source_modified" -- Not supported by this datasource
  , t.modify_date                  AS "dataset_source_modified"
  , d.database_id                  AS "datasource_source_id"
  , s.schema_id                    AS "dataset_group_source_id"
  , t.object_id                    AS "dataset_source_id"
  , OBJECT_DEFINITION(o.object_id) AS "dataset_technical_definition"
  , null                           AS "datasource_description" -- Not supported by this datasource
  , null                           AS "dataset_group_description" -- Not supported by this datasource
  , ep_t.value                     AS "dataset_description"
  , ep_c.value                     AS "field_description"

FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN sys.databases d
          ON c.TABLE_CATALOG
     COLLATE DATABASE_DEFAULT = d.name
  INNER JOIN sys.schemas s
          ON c.TABLE_SCHEMA = s.name
  INNER JOIN sys.objects o
          ON o.name = c.TABLE_NAME
         AND o.schema_id = s.schema_id
         AND o.type in ('U', 'V')
  INNER JOIN sys.columns c2
          ON o.object_id = c2.object_id
         AND c.COLUMN_NAME = c2.name
   LEFT JOIN sys.tables t
          ON s.schema_id = t.schema_id
         AND c.TABLE_NAME = t.name
   LEFT JOIN sys.extended_properties ep_c
          ON ep_c.name = 'MS_Description'
         AND ep_c.minor_id = c2.column_id
         AND ep_c.major_id = o.object_id
         AND ep_c.class = 1 -- Objects or columns
   LEFT JOIN sys.extended_properties ep_t
          ON ep_t.name = 'MS_Description'
         AND ep_t.minor_id = 0
         AND ep_t.major_id = o.object_id
         AND ep_t.class = 1 -- Objects or columns
  -- Row count per table; index_id < 2 = heap (0) or clustered index (1). Aggregated once per object_id.
  LEFT JOIN (
    select
        object_id
      , sum(rows) as num_rows
    from sys.partitions
    where index_id < 2
    group by object_id
  ) p ON t.object_id = p.object_id
  -- On-disk size per table (used pages -> bytes), aggregated once per object_id.
  LEFT JOIN (
    select
        t.object_id
      , sum(a.used_pages) * 8 * 1024 as bytes
    from sys.tables t
    inner join sys.indexes i
            on t.object_id = i.object_id
    inner join sys.partitions p
            on i.object_id = p.object_id
           and i.index_id = p.index_id
    inner join sys.allocation_units a
            on p.partition_id = a.container_id
    where i.object_id > 255
    group by
        t.object_id
  ) ts ON t.object_id = ts.object_id
  -- Primary-key columns straight from the sys catalog (faster than INFORMATION_SCHEMA constraint views).
  LEFT JOIN (
    select
        s.name   as TABLE_SCHEMA
      , o.name   as TABLE_NAME
      , col.name as COLUMN_NAME
    from sys.key_constraints kc
    inner join sys.objects       o   on o.object_id  = kc.parent_object_id
    inner join sys.schemas       s   on s.schema_id  = o.schema_id
    inner join sys.index_columns ic  on ic.object_id = kc.parent_object_id
                                    and ic.index_id  = kc.unique_index_id
                                    and ic.is_included_column = 0
    inner join sys.columns       col on col.object_id = ic.object_id
                                    and col.column_id = ic.column_id
    where kc.type = 'PK'
  ) pk ON c.TABLE_NAME = pk.TABLE_NAME
      AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
      AND c.COLUMN_NAME = pk.COLUMN_NAME

WHERE c.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'sys')
  AND c.TABLE_NAME NOT IN ('sysdiagrams')

Custom Relationship Import Query

The custom relationship import query is optional, but must return columns with these exact names (case sensitive) if provided. The mandatory columns must be present, cannot be null nor an empty string. They are the minimum data needed for Katalogue to correctly import relationships.

Column Name	Datatype	Description	Is Mandatory
relationship_name	string	Name of the FK-constraint	Yes
relationship_ordinal_position	integer	Ordinal position of the column in the FK-constraint. Should be 1 even if there is only one column in the constraint.	Yes
from_datasource_name	string	Name of the database with the FK constraint	Yes
from_dataset_group_name	string	Name of the schema with the FK constraint	Yes
from_dataset_name	string	Name of the table with the FK constraint	Yes
from_field_name	string	Name of the column in the FK constraint	Yes
to_datasource_name	string	Name of the database with the target/referenced column of the FK constraint	Yes
to_dataset_group_name	string	Name of the schema with the target/referenced column of the FK constraint	Yes
to_dataset_name	string	Name of the table with the target/referenced column of the FK constraint	Yes
to_field_name	string	Name of the target/referenced column of the FK constraint	Yes
relationship_source_created	timestamp	Timestamp for when the FK-constraint was created

Example query (Microsoft SQL Server connector default query):

SELECT
    fk.name                  AS "relationship_name"
  , fkc.constraint_column_id AS "relationship_ordinal_position"
  , DB_NAME()                AS "from_datasource_name"
  , ps.name                  AS "from_dataset_group_name"
  , pt.name                  AS "from_dataset_name"
  , pc.name                  AS "from_field_name"
  , DB_NAME()                AS "to_datasource_name"
  , rs.name                  AS "to_dataset_group_name"
  , rt.name                  AS "to_dataset_name"
  , rc.name                  AS "to_field_name"
  , NULL                     AS "relationship_source_created"

FROM sys.foreign_keys fk
  INNER JOIN sys.foreign_key_columns fkc
          ON fkc.constraint_object_id = fk.object_id
  -- referencing (FK) side
  INNER JOIN sys.objects pt
          ON pt.object_id = fkc.parent_object_id
  INNER JOIN sys.schemas ps
          ON ps.schema_id = pt.schema_id
  INNER JOIN sys.columns pc
          ON pc.object_id = fkc.parent_object_id
         AND pc.column_id = fkc.parent_column_id
  -- referenced (PK/unique) side
  INNER JOIN sys.objects rt
          ON rt.object_id = fkc.referenced_object_id
  INNER JOIN sys.schemas rs
          ON rs.schema_id = rt.schema_id
  INNER JOIN sys.columns rc
          ON rc.object_id = fkc.referenced_object_id
         AND rc.column_id = fkc.referenced_column_id

Custom Lineage Import Query

The custom lineage import query is optional, but must return columns with these exact names (case sensitive) if provided. The ones marked with an asterisk (*) cannot be null nor an empty string. They are the minimum data needed for Katalogue to correctly import lineage data.

Column Name	Datatype	Description	Is Mandatory
lineage_name	string	Name of the procedure/job/transformation/… that moves the data
lineage_type	string	Type that describes what kind of procedure/job/transformation/… that moves the data
from_datasource_name	string	Name of the upstream database, i.e. source of the procedure/job/transformation/…	Yes
from_dataset_group_name	string	Name of the upstream schema, i.e. source of the procedure/job/transformation/…	Yes
from_dataset_name	string	Name of the upstream table, i.e. source of the procedure/job/transformation/…	Yes
to_datasource_name	string	Name of the downstream database, i.e. target of the procedure/job/transformation/…	Yes
to_dataset_group_name	string	Name of the downstream schema, i.e. target of the procedure/job/transformation/…	Yes
to_dataset_name	string	Name of the downstream table, i.e. target of the procedure/job/transformation/…	Yes

Example query (Databricks connector default query):

select distinct
      null                  as lineage_name
    , null                  as lineage_type
    , source_table_catalog  as from_datasource_name
    , source_table_schema   as from_dataset_group_name
    , source_table_name     as from_dataset_name
    , target_table_catalog  as to_datasource_name
    , target_table_schema   as to_dataset_group_name
    , target_table_name     as to_dataset_name
from system.access.table_lineage

where source_table_name is not null
  and target_table_name is not null

Datasource Sync Task Logic

This section explains the sync logic when syncing datasources.

Datasources

Datasources in the source system and Katalogue are matched on its datasource_source_id, and falls back to datasource_name if empty (case-insensitive match).

Insert: If there is no matching Datasource in Katalogue, it will be added.

Delete: If there is a Datasource in the same System in Katalogue but not in the source system, the Datasource and its related Dataset Groups, Datasets and Fields will be deleted from Katalogue (hard delete).

Update: If there is a matching Datasource in Katalogue, the its properties in Katalogue will be overwritten.

Dataset Groups

Dataset Groups in the source system and Katalogue are matched on its dataset_group_source_id, and falls back to dataset_group_name if empty (case-insensitive match).

Insert: If there is no matching Dataset Group in Katalogue, it will be added.

Delete: If there is a Dataset Group in the same Datasource in Katalogue but not in the source system, the Dataset Group and its related Datasets and Fields will be deleted from Katalogue (hard delete).

Update: If there is a matching Dataset Group in Katalogue, the its properties in Katalogue will be overwritten.

Datasets

Datasets in the source system and Katalogue are matched on its dataset_source_id, and falls back to dataset_name (case-insensitive match) if empty.

Insert: If there is no matching Dataset in Katalogue, it will be added.

Delete: If there is a Dataset in the same Dataset Group in Katalogue but not in the source system, the Dataset and its related Fields will be deleted from Katalogue (hard delete).

Update: If there is a matching Dataset in Katalogue, the its properties in Katalogue will be overwritten.

Fields

Fields in the source system and Katalogue are matched on its field_source_id, and falls back to field_name if empty (case-insensitive match).

Insert: If there is no matching Field in Katalogue, it will be added. When a Field is added to Katalogue for the first time, it will be associated with an existing Attribute if either one of the following criteria are fulfilled:

• There is exactly one Attribute in Katalogue with a matching name* as the Field. If there are multiple Attributes with a matching name, no association will be done.
• There is one or more existing Fields in Katalogue with exactly the same name as the Field in the source system and that are already associated with exactly one Attribute. The name of that Attribute does not have to be the same as the Field name. If there are existing Fields with the same name that are associated with different Attributes, no association will be done.

* The match between Field names and Attribute names are done after a conversion to lowercase, removing whitespaces and removing underscores that are not at the beginning of the name. E.g. a Field or Attribute with name “_Customer full_NAME” will be converted to “_customerfullname” in the matching.

Attribute names must be unique across all glossaries for auto-association to work

An Attribute name only has to be unique within its own glossary, so the same name can exist in several glossaries. Auto-association requires exactly one match across the whole Katalogue, so an Attribute name that is used in more than one glossary will silently never be associated with a Field automatically. Keep the names of Attributes you rely on for auto-association unique across glossaries, or associate those Fields manually.

Delete: If there is a Field in the same Dataset in Katalogue but not in the source system, the Field will be deleted from Katalogue (hard delete).

Update: If there is a matching Field in Katalogue, the its properties in Katalogue will be overwritten.