Generated Tables
This page documents all the tables generated by beVault and their purpose. There are regrouped per schema. The data types and the DDLs listed here are the one generated for PostgreSQL databases and may be different for other types of databases.
Staging Area - stg
Staging table Level 1
Type | Table or View |
|---|---|
Schema | stg |
Table name format | as entered by the user in the interface |
Purpose | Create a staging table where the data arrives |
Note | Can be either a table or a view |
Content
Field name | Description | Type |
|---|---|---|
user defined | Staging table column | user defined |
Example (as entered by the user or generated if he used the wizard):
CREATE TABLE stg.hr_employees (
first_name varchar(255) NULL,
last_name varchar(255) NULL,
id varchar(255) NULL,
email varchar(255) NULL,
department varchar(255) NULL,
country varchar(255) NULL,
city varchar(255) NULL,
address varchar(255) NULL,
shop_id varchar(255) NULL,
manager_id varchar(255) NULL
);Staging table level 2
Type | Table |
|---|---|
Schema | stg |
Table name format | [staging table name]_l2 |
Purpose | Create a staging table level 2 where the computed hashes are stored before loading the data in the dv |
Content
Field name | Description | Type |
|---|---|---|
seq | Row number of the current batch | bigserial |
src | Code of the source system | varchar(255) |
load_dts | Load date for the current batch | timestamp |
[x]_bk | Business key for the hub mapping with the code “x” | varchar |
[x]_hk | Hash key for the hub mapping with the code “x” | uuid |
[y]_lhk | Hash key for the link mapping with the code “y” | uuid |
user defined | Staging table column with the hard rule applied | user defined with the hard rule taken into account |
[z]_hdiff | Hashdiff for the satellite mapping with the code “z” | uuid |
Example:
CREATE TABLE stg.hr_employees_l2 (
seq bigserial NOT NULL,
src varchar(255) NOT NULL,
load_dts timestamp NOT NULL,
a_bk varchar(255) NOT NULL,
a_hk uuid NOT NULL,
b_bk varchar(255) NOT NULL,
b_hk uuid NOT NULL,
c_bk varchar(255) NOT NULL,
c_hk uuid NOT NULL,
d_lhk uuid NOT NULL,
e_lhk uuid NOT NULL,
email varchar(255) NULL,
country varchar(255) NULL,
address varchar(255) NULL,
last_name varchar(255) NULL,
department varchar(255) NULL,
city varchar(255) NULL,
first_name varchar(255) NULL,
f_hdiff uuid NOT NULL,
CONSTRAINT pk_hr_employees_l2 PRIMARY KEY (seq)
);Staging table level 2 loading view
Type | View |
|---|---|
Schema | stg |
Table name format | v_[staging table name]_l2 |
Purpose | Create a view that computes the hashes for the staging table level 2. |
Dependencies |
Content
Field name | Description | Type |
|---|---|---|
src | Code of the source system | varchar(255) |
load_dts | Load date for the current batch. Set to the current date | timestamp |
[x]_bk | Business key for the hub mapping with the code “x” | varchar |
[x]_hk | Computation of the hash key for the hub mapping with the code “x” | uuid |
[y]_lhk | Computation of the hash key for the link mapping with the code “y” | uuid |
user defined | Staging table column with the computation of the hard rule | user defined casted to the hard rule’s output type |
[z]_hdiff | Computation of the hashdiff for the satellite mapping with the code “z” | uuid |
Example:
CREATE OR REPLACE VIEW stg.v_hr_employees_l2
AS SELECT 'hr'::text AS src,
timezone('utc'::text, now()) AS load_dts,
COALESCE(btrim(shop_id::text), '-2'::text) AS a_bk,
md5(upper(COALESCE(btrim(shop_id::text), '-2'::text)))::uuid AS a_hk,
COALESCE(btrim(id::text), '-2'::text) AS b_bk,
md5(upper(COALESCE(btrim(id::text), '-2'::text)))::uuid AS b_hk,
COALESCE(btrim(manager_id::text), '-2'::text) AS c_bk,
md5(upper(COALESCE(btrim(manager_id::text), '-2'::text)))::uuid AS c_hk,
md5(concat(upper(COALESCE(btrim(id::text), '-2'::text)), ';;', upper(COALESCE(btrim(shop_id::text), '-2'::text))))::uuid AS d_lhk,
md5(concat(upper(COALESCE(btrim(id::text), '-2'::text)), ';;', upper(COALESCE(btrim(manager_id::text), '-2'::text))))::uuid AS e_lhk,
email,
country,
address,
last_name,
department,
city,
first_name,
md5(concat(email, ';;', country, ';;', address, ';;', last_name, ';;', department, ';;', city, ';;', first_name))::uuid AS f_hdiff
FROM stg.hr_employees stg1;Raw Vault - dv
Hub
Type | Table |
|---|---|
Schema | dv |
Table name format | h_[hub_name] |
Purpose | Store the business keys of a hub |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the business key | uuid |
bk | Business Key | varchar(x) |
load_dts | Load date when the business key was loaded for the first time | timestamp |
src | Code of the source system and data package where the business key appeared the first time | varchar(255) |
Example:
CREATE TABLE dv.h_employee (
hk uuid NOT NULL,
bk varchar(255) NOT NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
CONSTRAINT pk_h_employee PRIMARY KEY (hk)
);
CREATE UNIQUE INDEX uidx_h_employee ON dv.h_employee USING btree (bk);Hub loading view
Type | View |
|---|---|
Schema | dv |
Table name format | v_h_[ staging table name ]_[ mapping code] |
Purpose | Create a view to load the data from the staging lvl 2 into a hub |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the business key | uuid |
bk | Business Key | varchar(x) |
load_dts | Load date when the business key was loaded for the first time | timestamp |
src | Code of the source system and data package where the business key appeared the first time | varchar(255) |
Example:
CREATE OR REPLACE VIEW dv.v_h_hr_employees_a
AS SELECT DISTINCT stg.a_hk AS hk,
stg.a_bk AS bk,
timezone('utc'::text, now()) AS load_dts,
concat(stg.src, '-', 'hr_employees')::character varying(255) AS src
FROM stg.hr_employees_l2 stg
LEFT JOIN dv.h_shop hub ON stg.a_hk = hub.hk
WHERE hub.hk IS NULL;Link
Type | Table |
|---|---|
Schema | dv |
Table name format | l_[link_name] |
Purpose | Store the relationships between hubs |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the link | uuid |
[hub_reference]_hk | Hash key of the hub referenced. | uuid |
load_dts | Load date when the relationship was loaded for the first time | timestamp |
src | Code of the source system and data package where the business key appeared the first time | varchar(255) |
[dependent child] | Dependent child which is part of the granularity of the link. | User defined at the creation of the link |
[data columns] | Data columns. They are only available for transactionnal links to avoid having to use a satellite for immutable data | User defined at the creation of the link |
Example:
CREATE TABLE dv.l_contract_employee (
hk uuid NOT NULL,
contract_hk uuid NOT NULL,
employee_hk uuid NOT NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
CONSTRAINT pk_l_contract_employee PRIMARY KEY (hk),
CONSTRAINT fk_h_contract_employee_contract FOREIGN KEY (contract_hk) REFERENCES dv.h_contract(hk),
CONSTRAINT fk_h_contract_employee_employee FOREIGN KEY (employee_hk) REFERENCES dv.h_employee(hk)
);Link loading view
Type | View |
|---|---|
Schema | dv |
Table name format | v_l_[ staging table name ]_[ mapping code] |
Purpose | Load the data from the staging lvl2 table into a link |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the link | uuid |
[hub_reference]_hk | Hash key of the hub referenced. | uuid |
load_dts | Load date when the relationship was loaded for the first time | timestamp |
src | Code of the source system and data package where the business key appeared the first time | varchar(255) |
[dependent child] | Dependent child which is part of the granularity of the link. | User defined at the creation of the link |
[data columns] | Data columns. They are only available for transactionnal links to avoid having to use a satellite for immutable data | User defined at the creation of the link |
Example:
CREATE OR REPLACE VIEW dv.v_l_crm_contracts_f
AS SELECT DISTINCT stg.f_lhk AS hk,
timezone('utc'::text, now()) AS load_dts,
concat(stg.src, '-', 'crm_contracts')::character varying(255) AS src,
stg.b_hk AS contract_hk,
stg.d_hk AS employee_hk
FROM stg.crm_contracts_l2 stg
LEFT JOIN dv.l_contract_employee link ON stg.f_lhk = link.hk
WHERE link.hk IS NULL;Satellite
Type | Table |
|---|---|
Schema | dv |
Table name format | s[ “h” or “l” ]_[ parent name]_[ sat name] |
Purpose | Store the descriptive data of a hub or link with its full history |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the satellite. It is also the same hash key as its parent | uuid |
load_dts | Load date when the data appeared or changed for a specific hash key | timestamp |
src | Code of the source system of the satellite | varchar(255) |
hdiff | Hash diff. This column is used to detected changes of descriptive column for a hash key | uuid |
[descriptive columns] | Attributes to describe either the hub or the link | User defined at the creation of the satellite |
Example:
CREATE TABLE dv.sh_employee_crm_info (
hk uuid NOT NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
hdiff uuid NOT NULL,
"location" varchar(255) NULL,
first_name varchar(255) NULL,
last_name varchar(255) NULL,
CONSTRAINT pk_sh_employee_crm_info PRIMARY KEY (hk, load_dts),
CONSTRAINT fk_sh_employee_crm_info FOREIGN KEY (hk) REFERENCES dv.h_employee(hk)
);
Satellite loading view
Type | View |
|---|---|
Schema | dv |
Table name format | s[ “h” or “l” ]_[ parent name]_[ sat name] |
Purpose | Load the data from the staging lvl2 into the satellite’s table |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the satellite. It is also the same hash key as its parent | uuid |
load_dts | Load date when the data appeared or changed for a specific hash key | timestamp |
src | Code of the source system of the satellite | varchar(255) |
hdiff | Hash diff. This column is used to detected changes of descriptive column for a hash key | uuid |
[descriptive columns] | Attributes to describe either the hub or the link | User defined at the creation of the satellite |
Example:
CREATE OR REPLACE VIEW dv.v_sh_crm_employees_b
AS SELECT DISTINCT stg.a_hk AS hk,
timezone('utc'::text, now()) AS load_dts,
stg.src,
stg.b_hdiff AS hdiff,
stg.location,
stg.first_name,
stg.last_name
FROM stg.crm_employees_l2 stg
LEFT JOIN dv.sh_employee_crm_info sat ON stg.a_hk = sat.hk AND sat.load_dts = (( SELECT max(z.load_dts) AS max
FROM dv.sh_employee_crm_info z
WHERE sat.hk = z.hk))
WHERE sat.hk IS NULL OR stg.b_hdiff <> sat.hdiff;Business Vault - bv
Effectivity Satellite
Type | Table |
|---|---|
Schema | bv |
Table name format | [ “h” or “l” ]_[ hub or link name]_[ source name]_effsat |
Purpose | Keeps the history of when each record was valid, using start and end dates. |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the business object | uuid |
load_dts | Load timestamp (when the record was loaded) | timestamp |
src | Source system code | varchar(255) |
start_dts | Effectivity start date | timestamp |
end_dts | Effectivity end date (default "9999-12-31") | timestamp |
is_current | Flag: is the record currently valid? | bool |
Example:
CREATE TABLE bv.h_tree_node_propilot_effsat (
hk uuid NOT NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
start_dts timestamp NOT NULL,
end_dts timestamp DEFAULT '9999-12-31 00:00:00'::timestamp without time zone NOT NULL,
is_current bool DEFAULT true NOT NULL,
CONSTRAINT pk_h_tree_node_propilot_effsat_effsat PRIMARY KEY (hk, load_dts),
CONSTRAINT fk_h_tree_node_propilot_effsat FOREIGN KEY (hk) REFERENCES dv.h_tree_node(hk)
);
Satellite View
Type | View |
|---|---|
Schema | bv |
Table name format | v_[ “sh” or “sl” ]_[ hub or link name]_[ source name]_info |
Purpose | Provides a satellite view including the load_end_dts column, which indicates the end of validity for each version of a record |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the business object (hub or link) | uuid |
load_dts | Load timestamp (when the record was loaded) | timestamp |
load_end_dts | Timestamp when the next version was loaded, or '9999-12-31' if this is the latest | timestamp |
src | Source system code | varchar(255) |
[descriptive columns] | Attributes to describe either the hub or the link | various |
Example:
CREATE OR REPLACE VIEW bv.v_sh_kpi_propilot_info AS
SELECT
hk,
load_dts,
COALESCE(lead(load_dts) OVER (PARTITION BY hk ORDER BY load_dts), '9999-12-31 00:00:00'::timestamp without time zone) AS load_end_dts,
src,
description,
start_date,
display_name,
end_date,
last_update_date
FROM dv.sh_kpi_propilot_info sat;
l_dq_x
Type | Table |
|---|---|
Schema | bv |
Table name format | l_dq_[link name] |
Purpose |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key for the link (unique identifier) | uuid |
[entity1]_hk | Hash key of the first business entity | uuid |
[entity2]_hk | Hash key of the second business entity | uuid |
snapshot_date | Snapshot or effective date for the link | timestamp |
dq_id | Data quality rule identifier | varchar |
load_dts | Load timestamp (when the record was created) | timestamp |
src | Source system code | varchar(255) |
Example:
CREATE TABLE bv.l_dq_tree_node_property (
hk uuid NOT NULL,
tree_node_hk uuid NOT NULL,
property_hk uuid NOT NULL,
snapshot_date timestamp NULL,
dq_id varchar NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
CONSTRAINT pk_l_dq_tree_node_property PRIMARY KEY (hk),
CONSTRAINT fk_h_dq_tree_node_property_property FOREIGN KEY (property_hk) REFERENCES dv.h_property(hk),
CONSTRAINT fk_h_dq_tree_node_property_tree_node FOREIGN KEY (tree_node_hk) REFERENCES dv.h_tree_node(hk)
);sl_dq_x_results
Type | Table |
|---|---|
Schema | bv |
Table name format | sl_dq_[link name]_results |
Purpose | Stores the results and related information for data quality checks performed on links between business entities |
Content
Field name | Description | Type |
|---|---|---|
hk | Hash key of the linked entities | uuid |
load_dts | Load timestamp (when the result was recorded) | timestamp |
src | Source system code | varchar(255) |
hdiff | Hash diff. This column is used to detected changes of descriptive column for a hash key | uuid |
responsible | Responsible person or system for this result | varchar(255) |
object_name | Name of the data object involved | varchar(1000) |
object_id | Identifier of the data object involved | varchar(1000) |
dq_result | Result value of the data quality check | int2 |
Example:
CREATE TABLE bv.sl_dq_tree_node_property_results (
hk uuid NOT NULL,
load_dts timestamp NOT NULL,
src varchar(255) NOT NULL,
hdiff uuid NOT NULL,
responsible varchar(255) NULL,
object_name varchar(1000) NULL,
object_id varchar(1000) NULL,
dq_result int2 NULL,
CONSTRAINT pk_sl_dq_tree_node_property_results PRIMARY KEY (hk, load_dts),
CONSTRAINT fk_sl_dq_tree_node_property_results FOREIGN KEY (hk) REFERENCES bv.l_dq_tree_node_property(hk)
);Information Marts - im
Snapshot tables (ctrl_[snapshot name])
Type | Table |
|---|---|
Schema | im |
Table name format | ctrl_[snapshot name] |
Purpose | Stores metadata and control information about each data snapshot. |
Content
Field name | Description | Type |
|---|---|---|
snapshot_date | Unique date/time of the snapshot | timestamp |
displayName | Display name or label for the snapshot | varchar(200) |
businessDate | Business date the snapshot refers to | timestamp |
creationDate | Date/time when the snapshot was created | timestamp |
deletionDate | Date/time when the snapshot was deleted or expired | timestamp |
Example:
CREATE TABLE im."ctrl_[snapshot name]" (
snapshot_date timestamp NOT NULL,
"displayName" varchar(200) NULL,
"businessDate" timestamp NULL,
"creationDate" timestamp NULL,
"deletionDate" timestamp NULL,
CONSTRAINT "pk_snapshot_[snapshot name]" PRIMARY KEY (snapshot_date)
);dim_dq_controls
Type | Table |
|---|---|
Schema | im |
Table name format | dim_dq_controls |
Purpose | Stores the definitions and metadata for data quality controls |
Content
Field name | Description | Type |
|---|---|---|
dq_id | Unique identifier of the data quality control | varchar |
name | Name of the control | varchar |
description | Description of the control | varchar |
principal_source | Main data source for the control | varchar |
business_concept | Associated business concept | varchar |
quality_level | Level of data quality (1, 2, 3 or 4) | int2 |
quality_level_description | Description of the quality level ( 1 = form, 2 = Intra-source, 3 = Inter-source, 4 = Business plausibility) | varchar |
controls_type | Type/category of the control (Completeness, Format, Freshness, Coherence) | varchar |
criticality | Criticality of the control (Error, Warning, Notification) | varchar |
resolution | Resolution method or rule for the control | varchar |
Example:
CREATE TABLE im.dim_dq_controls (
dq_id varchar NOT NULL,
"name" varchar NULL,
description varchar NULL,
principal_source varchar NULL,
business_concept varchar NULL,
quality_level int2 NULL,
quality_level_description varchar NULL,
controls_type varchar NULL,
criticality varchar NULL,
resolution varchar NULL,
"expression" varchar NULL
);fact_dq_results
Type | View |
|---|---|
Schema | im |
Table name format | fact_dq_results |
Purpose | Provides view of data quality results |
Content
Field name | Description | Type |
|---|---|---|
dq_id | Autogenerated-id of the DQ test. Used to link it to im.dim_dq_controls | varchar |
object_id | Business key of the hub (or concatenation of the business keys of the link’s hub references) on which the test has been executed | varchar |
responsible | Responsible for this DQ test | varchar |
result | Result of the DQ test. 1 for the test that passed, 0 for the one that failed | int2 |
denom | 1 | integer |
date | Date of the snapshot or when the check was performed | timestamp |
snapshot_name | Snapshot_date for which the result is valid | text |
object_name | Value of the column flagged as Ref (CFR creation of DQ test) | varchar |
entity_hk | Hash key of the hub or link on which the test is performed | uuid |
Example:
CREATE OR REPLACE VIEW im.fact_dq_results
AS SELECT l.dq_id,
s.object_id,
s.responsible,
s.dq_result AS result,
1 AS denom,
l.snapshot_date AS date,
'ProPilot'::text AS snapshot_name,
s.object_name,
entity.hk AS entity_hk
FROM bv.l_dq_tree_node_property l
JOIN bv.sl_dq_tree_node_property_results s ON l.hk = s.hk
JOIN dv.l_tree_node_property entity ON l.tree_node_hk = entity.tree_node_hk AND l.property_hk = entity.property_hk
WHERE l.dq_id IS NOT NULL;Metadata - meta
Schema conventions
Type | Table |
|---|---|
Schema | meta |
Table name format | schema_conventions |
Purpose | List of conventions used on the schemas. |
Content
Field name | Description | Type |
|---|---|---|
type | Schema Type
| VARCHAR(50) |
name | Name of the schema. Multiple type can have the same name and use prefix to logically group tables together | VARCHAR(150) NOT NULL |
convention_type |
| VARCHAR(10) NULL |
convention_value | Prefix or suffix for tables and view of the Schema | VARCHAR(10) NULL |
Example:
CREATE TABLE meta.schema_conventions (
"type" varchar(50) NOT NULL,
"name" varchar(100) NOT NULL,
convention_type varchar(10) NULL,
convention_value varchar(10) NULL,
CONSTRAINT pk_meta_schema_conventions PRIMARY KEY (type)
);Table conventions
Type | Table |
|---|---|
Schema | meta |
Table name format | table_conventions |
Purpose | List of naming conventions used for table names. |
Content
Field name | Description | Type |
|---|---|---|
type | Type of table on which to apply the convention | VARCHAR(50) NOT NULL Primary Key |
convention_type | Type of naming convention
| VARCHAR(50) NOT NULL |
convention_value |
| VARCHAR(50) NOT NULL |
CREATE TABLE meta.schema_conventions (
"type" varchar(50) NOT NULL,
"name" varchar(100) NOT NULL,
convention_type varchar(10) NULL,
convention_value varchar(10) NULL,
CONSTRAINT pk_meta_schema_conventions PRIMARY KEY (type)
);Column conventions
Type | Table |
|---|---|
Schema | meta |
Table name format | column_conventions |
Purpose | List of naming conventions used for technical column names. |
Content
Field name | Description | Type |
|---|---|---|
type | Type of technical column | VARCHAR (50) PRIMARY KEY |
convention_type | Type of naming convention
| VARCHAR (50) NOT NULL |
convention_value |
| VARCHAR (50) NOT NULL |
CREATE TABLE meta.column_conventions (
"type" varchar(50) NOT NULL,
convention_type varchar(50) NOT NULL,
convention_value varchar(50) NOT NULL,
CONSTRAINT pk_meta_column_conventions PRIMARY KEY (type)
);Config
Type | Table |
|---|---|
Schema | meta |
Table name format | config |
Purpose | Lists all configuration options. |
Content
Field name | Description | Type |
|---|---|---|
name | Key value of the configuration option. | VARCHAR (250) PRIMARY KEY |
value | Value of the configuration option. | VARCHAR (500) NOT NULL |
CREATE TABLE meta.config (
"name" varchar(255) NOT NULL,
value varchar(255) NOT NULL
);
Source Systems
Type | Table |
|---|---|
Schema | meta |
Table name format | sourcesystems |
Purpose | Lists all source systems and its metadata |
Content
Field name | Description | Type |
|---|---|---|
code | Code of the source system. | VARCHAR (250) PRIMARY KEY |
name | Name of the source system. | VARCHAR (250) NOT NULL |
technical_description | Technical description of the source system | VARCHAR (1000) NULL |
business_description | Business description of the source system | VARCHAR (1000) NULL |
version | version of the source system | VARCHAR (100) NULL |
quality | Expected quality of the source | INT NULL |
data_steward | Data Steward of the source system | VARCHAR (100) NULL |
system_administrator | System Administrator of the source system | VARCHAR (100) NULL |
CREATE TABLE meta.sourcesystems (
code varchar(250) NOT NULL,
name varchar(250) NOT NULL,
technical_description varchar(1000) NULL,
business_description varchar(1000) NULL,
"version" varchar(100) NULL,
quality int4 NULL,
data_steward varchar(100) NULL,
system_administrator varchar(100) NULL,
CONSTRAINT pk_meta_sourcesystems PRIMARY KEY (code)
);Data packages
Type | Table |
|---|---|
Schema | meta |
Table name format | data_packages |
Purpose | Lists all Data Packages and its metadata |
Content
Field name | Description | Type |
|---|---|---|
name | Name of the data package. | VARCHAR (250) PRIMARY KEY |
sourcesystem_code | Code of the data packages’s source system. | VARCHAR (250) PRIMARY KEY, FOREIGN KEY to meta.sourcesystems |
delivery_schedule | Delivery Schedule of the data package. | VARCHAR (250) NULL |
technical_description | Technical description of the data package | VARCHAR (1000) NULL |
business_description | Business description of the data package | VARCHAR (1000) NULL |
refresh_type | RefreshType of the data package | VARCHAR (250) NULL |
format_info | FormatInfo of the data package | VARCHAR (250) NULL |
quality | Expected quality of the data package | INT NULL |
CREATE TABLE meta.data_packages (
"name" varchar(250) NOT NULL,
sourcesystem_code varchar(250) NOT NULL,
delivery_schedule varchar(250) NULL,
technical_description varchar(1000) NULL,
business_description varchar(1000) NULL,
refresh_type varchar(250) NULL,
format_info varchar(250) NULL,
quality int4 NULL,
CONSTRAINT pk_meta_data_packages PRIMARY KEY (name, sourcesystem_code)
);Data Qualities
Type | Table |
|---|---|
Schema | meta |
Table name format | data_qualities |
Purpose | Lists all data quality controls |
Content
Field name | Description | Type |
|---|---|---|
name | Name of the data quality. | VARCHAR (250) PRIMARY KEY |
description | Description of the data quality | VARCHAR (250) NOT NULL |
resolution | Resolution of the data quality | VARCHAR (250) NULL |
sourcesystemcode | Code of the source system. | VARCHAR (50) FOREIGN KEY |
tablename | Table of the Hub or Link parent | VARCHAR (250) NOT NULL |
entity_type | Hub or Link | VARCHAR (50) NOT NULL |
quality_level |
| VARCHAR (50) NOT NULL |
criticality |
| VARCHAR (50) NOT NULL |
control_type |
| VARCHAR (50) NOT NULL |
expression | SQL Expression of the data quality | VARCHAR (1000) NOT NULL |
snapshotname | Name of the Snapshot | VARCHAR (250) NOT NULL |
responsible_type |
| VARCHAR NOT NULL |
responsible_value |
| VARCHAR NOT NULL |
CREATE TABLE meta.data_qualities (
"name" varchar(250) NOT NULL,
description varchar(250) NULL,
resolution varchar(250) NULL,
sourcesystemcode varchar(50) NOT NULL,
tablename varchar(250) NOT NULL,
entity_type varchar(50) NOT NULL,
quality_level varchar(50) NOT NULL,
criticality varchar(50) NOT NULL,
control_type varchar(50) NOT NULL,
"expression" varchar(1000) NOT NULL,
snapshotname varchar(250) NOT NULL,
responsible_type varchar NOT NULL,
responsible_value varchar NOT NULL,
CONSTRAINT pk_meta_data_qualities PRIMARY KEY (name)
);Data Quality columns
Type | Table |
|---|---|
Schema | meta |
Table name format | data_quality_columns |
Purpose | Lists the columns used in the data quality controls |
Content
Field name | Description | Type |
|---|---|---|
name | Name of the data quality. | VARCHAR (250) PRIMARY KEY |
schema_name | Name of the schema of the table of the column | VARCHAR (150) PRIMARY KEY, FOREIGN KEY to meta.schema_conventions |
table_name | Name of the table of the column | VARCHAR (150) PRIMARY KEY, FOREIGN KEY to meta.tables |
column_name | Name of the Column used on the DQ expression | VARCHAR (150) PRIMARY KEY |
is_object_name |
| BOOLEAN |
CREATE TABLE meta.data_quality_columns (
"name" varchar(250) NOT NULL,
schema_name varchar(150) NOT NULL,
table_name varchar(150) NOT NULL,
column_name varchar(150) NOT NULL,
is_object_name bool NOT NULL,
CONSTRAINT pk_meta_data_quality_columns PRIMARY KEY (name, schema_name, table_name, column_name, is_object_name)
);Tables
Type | Table |
|---|---|
Schema | meta |
Table name format | tables |
Purpose | List all tables generated by beVault |
Content
Field name | Description | Type |
|---|---|---|
schema_name | name of the schema | VARCHAR(150) PRIMARY KEY |
name | name of the table | VARCHAR(150) PRIMARY KEY |
sourcesystemcode | Code of the source system (if staging table) | VARCHAR(250) FOREIGN KEY NULL |
data_package_name | Name of the data package (if staging table) | VARCHAR(250) FOREIGN KEY NULL |
type | Type of table (HUB, LINK, …) | VARCHAR(150) |
subtype | Subtype of table (SameAs, Effectivity, …) | VARCHAR NULL |
business_description | Business description of the entity | VARCHAR(1000) NULL |
technical_description | technical description of the entity | VARCHAR(1000) NULL |
CREATE TABLE meta."tables" (
schema_name varchar(150) NOT NULL,
"name" varchar(150) NOT NULL,
sourcesystemcode varchar(250) NOT NULL,
data_package_name varchar(250) NOT NULL,
"type" varchar(150) NOT NULL,
subtype varchar NULL,
business_description varchar(1000) NULL,
technical_description varchar(1000) NULL,
CONSTRAINT pk_meta_tables PRIMARY KEY (schema_name, name, sourcesystemcode, data_package_name)
);Columns
Type | Table |
|---|---|
Schema | meta |
Table name format | table_columns |
Purpose | List all table columns |
Content
Field name | Description | Type |
|---|---|---|
schema_name | VARCHAR(150) PRIMARY KEY | |
table_name | VARCHAR(150) PRIMARY KEY | |
name | VARCHAR(150) PRIMARY KEY | |
type | BusinessKey, DrivingKey, HashDiff, etc | VARCHAR(50) FOREIGN KEY to meta.column_conventions |
data_type | string, bool, etc | VARCHAR(50) NOT NULL |
length | length encoded for the data type (if applicable) | INT NULL |
scale | scale of the data type for the numeric values (if applicable) | INT NULL |
precision | precision of the data type for the numeric values (if applicable) | INT NULL |
is_nullable | BOOL NOT NULL | |
business_name | Business name given within the staging table definition (only for column from staging table) | VARCHAR (250) NULL |
technical_description | Technical_description given within the staging table definition (only for column from staging table) | VARCHAR(1000) NULL |
business_description | Business description given within the staging table definition (only for column from staging table) | VARCHAR(1000) NULL |
ignore_business_key_case |
| BOOL NULL |
CREATE TABLE meta.table_columns (
schema_name varchar(150) NOT NULL,
table_name varchar(150) NOT NULL,
"name" varchar(150) NOT NULL,
"type" varchar(50) NOT NULL,
data_type varchar(50) NOT NULL,
length int4 NULL,
"scale" int4 NULL,
"precision" int4 NULL,
is_nullable bool NOT NULL,
business_name varchar(250) NULL,
technical_description varchar(1000) NULL,
business_description varchar(1000) NULL,
ignore_business_key_case bool NULL,
CONSTRAINT pk_meta_table_columns PRIMARY KEY (schema_name, table_name, name)
);Data Flows
Type | Table |
|---|---|
Schema | meta |
Table name format | table_data_flows |
Purpose | Lists information on the data flows between tables. This table can be used to design a Data Lineage application |
Content
Field name | Description | Type |
|---|---|---|
input_schema | VARCHAR(150) PRIMARY KEY | |
input_table | VARCHAR(150) PRIMARY KEY | |
input_column | VARCHAR(150) PRIMARY KEY | |
output_schema | VARCHAR(150) PRIMARY KEY | |
output_table | VARCHAR(150) PRIMARY KEY | |
output_column | VARCHAR(150) PRIMARY KEY | |
expression | expression of the transformation applied (same as in table_columns) | VARCHAR(1000) NULL |
This table will contain all the data flows from one table to another. The data flows are documented here: https://dfakto.atlassian.net/l/cp/CNS0QyKB
CREATE TABLE meta.table_data_flows (
input_schema varchar(150) NOT NULL,
input_table varchar(150) NOT NULL,
input_column varchar(150) NOT NULL,
output_schema varchar(150) NOT NULL,
output_table varchar(150) NOT NULL,
output_column varchar(150) NOT NULL,
"expression" varchar NULL,
CONSTRAINT pk_meta_table_data_flows PRIMARY KEY (input_schema, input_table, input_column, output_schema, output_table, output_column)
);Source - src
v_dq_[dq_id]
Type | View |
|---|---|
Schema | src |
Table name format | v_dq_[dq_id] |
Purpose | Exposes the detailed results of a specific data quality check |
Content
Field name | Description | Type |
|---|---|---|
snapshot_date | Date of the snapshot | timestamp |
object_id | Unique identifier of the tested object | varchar |
dq_id | Identifier of the data quality rule applied | text |
responsible | Person responsible for the check | text |
object_name | Name or label of the tested object | varchar |
dq_result | Numeric result of the DQ check (1 = pass, 0 = fail) | integer |
Example:
CREATE OR REPLACE VIEW src."v_dq_DQ11141"
AS SELECT p.snapshot_date,
"left"(concat(p.tree_node_bk, '_', p.property_bk)::character varying::text, 1000) AS object_id,
'd9ef3330-657b-425f-b4b5-593910c9689b'::text AS dq_id,
'PCMA'::text AS responsible,
"left"(s_a.current_value::text, 1000) AS object_name,
CASE
WHEN (p.property_bk::text <> ALL (ARRAY['Website'::character varying, 'website_accomodation'::character varying, 'website_venue'::character varying]::text[])) OR s_a.current_value::text ~ '^(https?:\/\/www\.|www\.)[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)+(:[0-9]{1,5})?(\/[^\s]*)?$'::text THEN 1
ELSE 0
END AS dq_result,
p.tree_node_bk AS tree_node,
p.property_bk AS property
FROM bv."l_pit_tree_node_property_ProPilot" p
JOIN dv.sl_tree_node_property_propilot_info s_a ON s_a.hk = p.tree_node_property_propilot_info_lhk AND s_a.load_dts = p.tree_node_property_propilot_info_load_dts;v_dq_[concept]
Type | View |
|---|---|
Schema | src |
Table name format | v_dq_[concept] |
Purpose | Provides the data quality results for a specific business concept |
Content
Field name | Description | Type |
|---|---|---|
snapshot_date | Date of the data extraction | timestamp |
object_id | Unique identifier of the object | varchar |
dq_id | Identifier of the applied data quality rule | varchar |
responsible | Person responsible for the check | varchar |
object_name | Name of the object evaluated | varchar |
dq_result | Numeric result of the DQ check (1 = pass, 0 = fail) | integer |
bk | Business key of the object | varchar |
Example:
CREATE OR REPLACE VIEW src."v_dq_DQ11141"
AS SELECT p.snapshot_date,
"left"(concat(p.tree_node_bk, '_', p.property_bk)::character varying::text, 1000) AS object_id,
'd9ef3330-657b-425f-b4b5-593910c9689b'::text AS dq_id,
'PCMA'::text AS responsible,
"left"(s_a.current_value::text, 1000) AS object_name,
CASE
WHEN (p.property_bk::text <> ALL (ARRAY['Website'::character varying, 'website_accomodation'::character varying, 'website_venue'::character varying]::text[])) OR s_a.current_value::text ~ '^(https?:\/\/www\.|www\.)[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)+(:[0-9]{1,5})?(\/[^\s]*)?$'::text THEN 1
ELSE 0
END AS dq_result,
p.tree_node_bk AS tree_node,
p.property_bk AS property
FROM bv."l_pit_tree_node_property_ProPilot" p
JOIN dv.sl_tree_node_property_propilot_info s_a ON s_a.hk = p.tree_node_property_propilot_info_lhk AND s_a.load_dts = p.tree_node_property_propilot_info_load_dts;