Skip to main content
Skip table of contents

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

Staging Tables

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):

SQL 
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:

SQL 
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.
This view is used to move the data from stg lvl1 to stg lvl2

Dependencies

https://dfakto.atlassian.net/wiki/spaces/C14DF/pages/5081464843/Generated+queries+for+deployment#Staging-table-level-1-creation

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:

SQL 
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:

SQL 
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:

SQL 
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;

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.
There are as many column as hubs attached to the link

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:

SQL 
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.
There are as many column as hubs attached to the link

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:

SQL 
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:

SQL 
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:

SQL 
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

Information Marts - im

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

  • Staging

  • Raw Vault

  • Business Vault

  • Information Mart

  • Meta

  • Referential

VARCHAR(50)
NOT NULL
PRIMARY KEY

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

  • Prefix

  • Suffix

  • Constant

  • None

  • User defined

VARCHAR(10) NULL

convention_value

Prefix or suffix for tables and view of the Schema

VARCHAR(10) NULL

Example:

SQL 
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

  • Prefix

  • Suffix

  • Constant

  • None

VARCHAR(50) NOT NULL

convention_value

 

VARCHAR(50) NOT NULL

SQL 
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

  • Prefix

  • Suffix

  • Constant

  • None

VARCHAR (50) NOT NULL

convention_value

 

VARCHAR (50) NOT NULL

SQL 
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

SQL 
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

data_steward

Data Steward of the source system

VARCHAR (100) NULL

system_administrator

System Administrator of the source system

VARCHAR (100) NULL

SQL 
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,
	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

SQL 
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,
	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

  • 1

  • 2

  • 3

  • 4

VARCHAR (50) NOT NULL

criticality

  • Notification

  • Warning

  • Error

VARCHAR (50) NOT NULL

control_type

  • Completeness

  • Format

  • Freshness

  • Coherence

VARCHAR (50) NOT NULL

expression

SQL Expression of the data quality

VARCHAR (1000) NOT NULL

snapshotname

Name of the Snapshot

VARCHAR (250) NOT NULL

SQL 
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,
	CONSTRAINT pk_meta_data_qualities PRIMARY KEY (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

SQL 
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,
	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

is_nullable

BOOL NOT NULL

expression

Computation on the column

  • Hard rule applied (for staging tables)

VARCHAR(1000) NULL

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

This table will contains all the data flows from one table to another. The data flows are documented here: https://dfakto.atlassian.net/l/cp/CNS0QyKB

SQL 
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,
	CONSTRAINT pk_meta_table_data_flows PRIMARY KEY (input_schema, input_table, input_column, output_schema, output_table, output_column)
);

Source - src

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close