Datasource Syncing

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.

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 Field Descriptions, Custom Attributes 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
api_method_code	string	API method code (get, post, put, patch, delete) from the source system. Only relevant if datasource_type_name = ‘api’.
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 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.

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
• api_method (only relevant for API datasources — the endpoint’s HTTP method, e.g. get, post)

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

Relationship Map Rules

Katalogue imports primary key/foreign key constraints automatically when present in the datasource and presents them visually. With the relationship mapping feature, Katalogue can create such relationships between datasets that do not have pk/fk constraints in the datasource. Hence, this feature can be used to show relationships between datasets in the datasource where such constraints are not supported, such as between database views, or between datasets that lack these relationships. Showing such relationships can be very helpful to analysts or other people to understand e.g. a star schema modelled with views.

Relationship map rules are applied in the import query from the Katalogue stage.raw_relationship table to the public.relationship table. Hence, Katalogue always imports all relationships from the datasource to stage and then applies the map rules when moving data from the stage table. If you do not want some relationships to leave the datasource, you must use your own custom relationship view to filter them out.

Relationship Types

Katalogue categorizes all relationships after how they are created. This is a simple label and is not tied to any functionality. The relationship type can be viewed by going to a Field -> Relationship tab.

There are currently three types of relationships:

• physical: pk/fk constraint imported from the datasource
• map-rule: relationship created with the relationship map rule feature
• virtual: relationship created by inheritance through a Field Description. If a Field without a relationship is associated with a Field Description, which in turn is associated with another Field with a relationship, Katalogue will on some occasions automatically create (i.e. “guess”) a relationship for the first Field.

The feature to let Katalogue automatically create virtual relationships can be disabled for the entire datasource by checking the “Disable Virtual Relationships” checkbox in the datasource sync settings.

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 dataset 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, 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"
)

...

Valid 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.

Valid 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

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

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.

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 Field Description if either one of the following criteria are fulfilled:

• There is exactly one Field Description in Katalogue with a matching name* as the Field. If there are multiple Field Descriptions 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 Field Description. The name of that Field Description 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 Field Descriptions, no association will be done.

* The match between Field names and Field Description 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 Field Description with name “_Customer full_NAME” will be converted to “_customerfullname” in the matching.

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.