Customizing default attributes and transformations

It is possible to customize default attributes and their transformations according to your requirements and practices. For example, you can configure ADE to create attribute stg_create_time by default when creating a staging table. This attribute has a default transformation assigned to it, which sets current timestamp function to the attribute.

When creating a new entity with configured default attributes, they will appear in the new entity automatically. However, changes will not be propagated to old entities automatically.

See also:

ADE default configuration (GitHub)

Customizing default attributes

Staging

Staging tables in this example have metadata attributes stg_source_system, stg_create_time, stg_source_system, stg_source_entity and stg_file_name. It is a good practice to define the needed metadata attributes in staging tables so that they can be easily mapped to further data warehouse zones in loads.

Attribute	Default
stg_batch_id	x
stg_create_time	x
stg_source_system	x
stg_source_entity	x
stg_file_name	x
attribute 1..n

Configure your metadata attributes and other defaults with CONFIG_ENTITY_DEFAULTS. Here is an example configuration for the above metadata attributes:

CONFIG_ENTITY_DEFAULT.json example

JSON

    "entityTypes": [
        ...
        {
            "entityType": "STAGE",
            "description": "Staging Entity",
            "namePrefix": "STG_",
            "dvEntity": false,
            "byDefaultAutoManaged": false,
            "defaultSchema": "staging",
            "defaultZone": "INGESTION",
            "defaultPhysicalType": "TABLE"
        },
        ...
    ],
    "attributeTypes": [
        ...
        {
            "attributeType": "FILE_NAME"
        },
        {
            "attributeType": "SDT_STAGE_CREATE_TIME"
        },
        {
            "attributeType": "SDT_STAGE_SOURCE"
        },
        {
            "attributeType": "SDT_STAGE_SOURCE_TYPE"
        },
        {
            "attributeType": "SOURCE_ENTITY"
        },
        ...
    ],
    "defaultAttributes": [
        {
            "defaultAttributeId": "10",
            "attributeNameTemplate": "stg_batch_id",
            "datatype": "INTEGER8",
            "charUsed": false,
            "description": "Internal Attribute",
            "isNullable": true,
            "isPersistent": true,
            "attributeType": "RUN_ID"
        },
        {
            "defaultAttributeId": "11",
            "attributeNameTemplate": "stg_create_time",
            "datatype": "TIMESTAMP",
            "charUsed": false,
            "description": "Internal Attribute",
            "isNullable": true,
            "isPersistent": true,
            "attributeType": "SDT_STAGE_CREATE_TIME"
        },
        {
            "defaultAttributeId": "13",
            "attributeNameTemplate": "stg_source_system",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 50,
            "description": "Internal Attribute",
            "isNullable": true,
            "isPersistent": true,
            "attributeType": "SDT_STAGE_SOURCE"
        },
        {
            "defaultAttributeId": "20",
            "attributeNameTemplate": "stg_file_name",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Staging file name",
            "isNullable": true,
            "isPersistent": true,
            "attributeType": "FILE_NAME"
        },
        {
            "defaultAttributeId": "21",
            "attributeNameTemplate": "stg_source_entity",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Staging source entity",
            "isNullable": true,
            "isPersistent": true,
            "attributeType": "SOURCE_ENTITY"
        },
    ],
    "entityTypeDefaultAttributes": [
        {
            "entityTypeDefaultAttributeId": "1",
            "entityType": "STAGE",
            "defaultAttributeId": "10",
            "position": "1"
        },
        {
            "entityTypeDefaultAttributeId": "2",
            "entityType": "STAGE",
            "defaultAttributeId": "11",
            "position": "2"
        },
        {
            "entityTypeDefaultAttributeId": "3",
            "entityType": "STAGE",
            "defaultAttributeId": "13",
            "position": "3"
        },
        {
            "entityTypeDefaultAttributeId": "5",
            "entityType": "STAGE",
            "defaultAttributeId": "21",
            "position": "4"
        },
        {
            "entityTypeDefaultAttributeId": "4",
            "entityType": "STAGE",
            "defaultAttributeId": "20",
            "position": "5"
        }
    ]

Data Vault

Data Vault entities in the examples have been configured to include dv_source_system and dv_source_entity metadata attributes. Additionally, the Data Vault entities have been enriched with dv_package_version and dv_load_name default attributes to collect more metadata which can be useful e.g. for auditing and troubleshooting purposes.

Example for Hub table

In this example, Hub tables are created with the following default attributes. For Hub tables, there are default transformation types assigned.

Attribute	Default transformation type	Default
dv_id	DV_HASH	x
dv_load_time	CURRENT_TS	x
dv_run_id	RUN_ID	x
dv_source_system		x
dv_source_entity		x
dv_package_version	PACKAGE_VERSION	x
dv_load_name	LOAD_NAME	x
business_key	DV_BUSINESS_KEY	x

Configure your metadata attributes and other defaults with CONFIG_ENTITY_DEFAULTS. Default transformation types are references to values defined in CONFIG_TRANSFORMATION_FORMULAS package. More of that in section Customizing default transformations.

Example about configuring metadata attributes for hub table:

CONFIG_ENTITY_DEFAULT.json example

JSON

    "entityTypes": [
        ...
        {
            "entityType": "HUB",
            "description": "Hub Entity",
            "namePrefix": "H_",
            "dvEntity": true,
            "byDefaultAutoManaged": false,
            "defaultSchema": "rdv",
            "defaultZone": "RAW",
            "defaultPhysicalType": "TABLE"
        },
        ...
    ],
    "attributeTypes": [
        ...
        {
            "attributeType": "DV_BUSINESS_KEY"
        },
        {
            "attributeType": "DV_HASHKEY"
        },
        {
            "attributeType": "DV_LOAD_NAME"
        },
        {
            "attributeType": "DV_LOAD_TIME"
        },
        {
            "attributeType": "DV_PACKAGE_VERSION"
        },
        {
            "attributeType": "DV_SOURCESYSTEM"
        },
        {
            "attributeType": "DV_SOURCE_ENTITY"
        },
        {
            "attributeType": "RUN_ID"
        },
        ...
    ],
    "defaultAttributes": [
        ...
        {
            "defaultAttributeId": "1",
            "attributeNameTemplate": "dv_id",
            "datatype": "CHAR",
            "charUsed": false,
            "dataLength": 32,
            "description": "Data Vault Hash Key",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_HASHKEY"
        },
        {
            "defaultAttributeId": "3",
            "attributeNameTemplate": "dv_load_time",
            "datatype": "TIMESTAMP",
            "charUsed": false,
            "description": "Data Vault Load Time",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_LOAD_TIME"
        },
        {
            "defaultAttributeId": "4",
            "attributeNameTemplate": "dv_run_id",
            "datatype": "INTEGER8",
            "charUsed": false,
            "description": "Data Vault Run Identifier",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "RUN_ID"
        },
        {
            "defaultAttributeId": "5",
            "attributeNameTemplate": "dv_source_system",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 50,
            "description": "Data Vault Source System",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_SOURCESYSTEM"
        },
        {
            "defaultAttributeId": "22",
            "attributeNameTemplate": "dv_source_entity",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Data Vault Source Entity",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_SOURCE_ENTITY"
        },
        {
            "defaultAttributeId": "23",
            "attributeNameTemplate": "dv_package_version",
            "datatype": "INTEGER8",
            "charUsed": false,
            "description": "Data Vault Package Version",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_PACKAGE_VERSION"
        },
        {
            "defaultAttributeId": "24",
            "attributeNameTemplate": "dv_load_name",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Data Vault Load Name",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_LOAD_NAME"
        },
        {
            "defaultAttributeId": "36",
            "attributeNameTemplate": "business_key",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 200,
            "description": "Business key",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "BUSINESS_KEY"
        },
        ...
    ],
    "entityTypeDefaultAttributes": [
        {
            "entityTypeDefaultAttributeId": "8",
            "entityType": "HUB",
            "defaultAttributeId": "1",
            "position": "1",
            "transformationType": "DV_HASH"
        },
        {
            "entityTypeDefaultAttributeId": "9",
            "entityType": "HUB",
            "defaultAttributeId": "3",
            "position": "2",
            "transformationType": "CURRENT_TS"
        },
        {
            "entityTypeDefaultAttributeId": "10",
            "entityType": "HUB",
            "defaultAttributeId": "4",
            "position": "3",
            "transformationType": "RUN_ID"
        },
        {
            "entityTypeDefaultAttributeId": "11",
            "entityType": "HUB",
            "defaultAttributeId": "5",
            "position": "4"
        },
        {
            "entityTypeDefaultAttributeId": "38",
            "entityType": "HUB",
            "defaultAttributeId": "22",
            "position": "5"
        },
        {
            "entityTypeDefaultAttributeId": "39",
            "entityType": "HUB",
            "defaultAttributeId": "23",
            "position": "6",
            "transformationType": "PACKAGE_VERSION"
        },
        {
            "entityTypeDefaultAttributeId": "40",
            "entityType": "HUB",
            "defaultAttributeId": "24",
            "position": "7",
            "transformationType": "LOAD_NAME"
        },
        {
            "entityTypeDefaultAttributeId": "41",
            "entityType": "HUB",
            "defaultAttributeId": "36",
            "position": "8",
            "transformationType": "DV_BUSINESS_KEY"
        },
        ...
    ]

Example for Link table

In this example, Link tables are created with the following default attributes.

Attribute	Default
dv_id	x
dv_load_time	x
dv_run_id	x
dv_source_system	x
dv_source_entity	x
dv_package_version	x
dv_load_name	x
dv_id_hub_1
dv_id_hub_2

Persistent staging

Configuring new entity types and custom attributes for persistent stage is described in persistent staging section.

Dimensional

Entities in dimensional model can have default attributes and transformation types depending on the use case.

Attribute	Default
id	x
load_time	x
source_system	x
attribute 1..n

CONFIG_ENTITY_DEFAULT.json example

CODE


    "entityTypes": [
        {
            "entityType": "DIM",
            "description": "Dimension Entity",
            "namePrefix": "D_",
            "dvEntity": false,
            "byDefaultAutoManaged": false,
            "defaultSchema": "publish",
            "defaultZone": "INFORMATION_DELIVERY",
            "defaultPhysicalType": "TABLE"
        },
        {
            "entityType": "FACT",
            "description": "Fact Entity",
            "namePrefix": "F_",
            "dvEntity": false,
            "byDefaultAutoManaged": false,
            "defaultSchema": "publish",
            "defaultZone": "INFORMATION_DELIVERY",
            "defaultPhysicalType": "TABLE"
        },
        ...
    ],
    "defaultAttributes": [
        {
            "defaultAttributeId": "110",
            "attributeNameTemplate": "id",
            "datatype": "CHAR",
            "charUsed": false,
            "dataLength": 32,
            "description": "Publish primary key",
            "isNullable": false,
            "isPersistent": true
        },
        {
            "defaultAttributeId": "111",
            "attributeNameTemplate": "load_time",
            "datatype": "TIMESTAMP",
            "charUsed": false,
            "description": "Publish Load Time",
            "isNullable": false,
            "isPersistent": true
        },
        {
            "defaultAttributeId": "112",
            "attributeNameTemplate": "source_system",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 50,
            "description": "Publish Source System",
            "isNullable": false,
            "isPersistent": true
        },
        ...
    ],
    "entityTypeDefaultAttributes": [
        {
            "entityTypeDefaultAttributeId": "201",
            "entityType": "DIM",
            "defaultAttributeId": "110",
            "position": "1"
        },
        {
            "entityTypeDefaultAttributeId": "202",
            "entityType": "DIM",
            "defaultAttributeId": "111",
            "position": "2"
        },
        {
            "entityTypeDefaultAttributeId": "203",
            "entityType": "DIM",
            "defaultAttributeId": "112",
            "position": "3"
        },
        {
            "entityTypeDefaultAttributeId": "204",
            "entityType": "FACT",
            "defaultAttributeId": "110",
            "position": "1"
        },
        {
            "entityTypeDefaultAttributeId": "205",
            "entityType": "FACT",
            "defaultAttributeId": "111",
            "position": "2"
        },
        {
            "entityTypeDefaultAttributeId": "206",
            "entityType": "FACT",
            "defaultAttributeId": "112",
            "position": "3"
        },
    ],
    "entityTypeDefaultLoadTypes": [
        {
            "entityPhysicalType": "METADATA_ONLY",
            "entityType": "DIM",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "TABLE",
            "entityType": "DIM",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "VIEW",
            "entityType": "DIM",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        {
            "entityPhysicalType": "MATERIALIZED_VIEW",
            "entityType": "DIM",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        {
            "entityPhysicalType": "METADATA_ONLY",
            "entityType": "FACT",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "TABLE",
            "entityType": "FACT",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "VIEW",
            "entityType": "FACT",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        {
            "entityPhysicalType": "MATERIALIZED_VIEW",
            "entityType": "FACT",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        ...
    ]

Customizing default transformations

Default transformation types for attributes can be configured in CONFIG_LOAD_TRANSFORMATIONS.

Example for configuring transformation types PACKAGE_VERSION and LOAD_NAME, which were used in Hub examples:

CONFIG_LOAD_TRANSFORMATIONS.json

JSON

   "attrTransformationTypes": [
        ...
        {
            "transformationType": "PACKAGE_VERSION",
            "shortDescription": "Package version",
            "description": "Inserts version number of deployed package.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 18
        },
        {
            "transformationType": "LOAD_NAME",
            "shortDescription": "Load name",
            "description": "Inserts load name.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 19
        },
        ...
    ],
    "dbmsTransformationFormulas": [
        ...
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "BIGQUERY",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "MS_SQL",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "BIGQUERY",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "MS_SQL",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        }
    ]

Tutorials

See the below videos for quick tutorials on how to create an entity type, a transformation type, and an entity type with transformation types and load steps.

https://youtu.be/dLFEaSaags0

https://youtu.be/P9x3ynT5jMk

https://youtu.be/hh9-jzCpkxc