Data quality rules reporting tables

Check the Postgres, Db2 statements for the tables are related to data quality.

Subject area Table name Description
Data quality rules dq_issues_for_asset_columns The data quality issues for a column.
Data quality rules dq_issues_for_assets The issues observed when assets undergo quality analysis to determine the overall data quality.
Data quality rules dq_checks Information about the data quality checks.
Data quality rules dq_v4_dimensions Information about the quality dimensions for the rule for example wheather there is duplicates.
Data quality rules dq_rule_definitions The data quality rule definition.
Data quality rules dq_rules_defs Data Quality Rule Definitions.
Data quality rules dq_rules The data quality rule information.
Data quality rules dq_rule_bindings The rule bindings for the data quality rule.
Data quality rules dq_rule_execution The scheduled time for the data quality rule job.

dq_issues_for_asset_columns table

This table has the following columns:

  • issue_id - The identifier of the asset issue.
  • asset_id - The identifier of the asset.
  • container_id - The identifier of the project.
  • column_name - The column name for which you run the data qulaity rules and analysis.
  • check_id - The identifier for the data quality check.
  • number_of_occurences - The number of occurences found for the data quality check.
  • number_of_tested_records - The number of tested records found for the data quality check.
  • percent_occurences - The percentage of the occurences.
  • score - Score found after running a check.
  • status - The current status of an issue.
  • ignored - Flag to identify wheather the current issue is participating in the data quality issue.

Postgres

CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS dq_issues_for_asset_columns (
  issue_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  container_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  column_name character varying(256) COLLATE pg_catalog.default NOT NULL,
  asset_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  check_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  number_of_occurrences bigint NOT NULL DEFAULT 0,
  number_of_tested_records bigint NOT NULL DEFAULT 0,
  percent_occurrences double precision,
  score double precision,
  status character varying(16) COLLATE pg_catalog.default NOT NULL,
  ignored boolean NOT NULL,
  tech_start timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tech_end timestamp(6) without time zone NOT NULL DEFAULT to_timestamp('9999-12-30' :: text, 'YYYY-MM-DD' :: text),
  ts_id timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT dq_issues_for_asset_columns_pkey PRIMARY KEY (issue_id, asset_id, container_id, column_name),
  CONSTRAINT fk_dq_issues_for_asset_columns_container_data_asset_columns_6 FOREIGN KEY (container_id, asset_id, column_name) REFERENCES container_data_asset_columns (container_id, asset_id, name) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_dq_issues_for_asset_columns_dq_checks_5 FOREIGN KEY (container_id, check_id) REFERENCES dq_checks (container_id, check_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
)


Db2

CREATE TABLE statement:

CREATE TABLE DQ_ISSUES_FOR_ASSET_COLUMNS (
  ISSUE_ID VARCHAR(36 OCTETS) NOT NULL,
  CONTAINER_ID VARCHAR(36 OCTETS) NOT NULL,
  COLUMN_NAME VARCHAR(256 OCTETS) NOT NULL,
  ASSET_ID VARCHAR(36 OCTETS) NOT NULL,
  CHECK_ID VARCHAR(36 OCTETS) NOT NULL,
  NUMBER_OF_OCCURRENCES BIGINT NOT NULL WITH DEFAULT 0,
  NUMBER_OF_TESTED_RECORDS BIGINT NOT NULL WITH DEFAULT 0,
  PERCENT_OCCURRENCES DOUBLE,
  SCORE DOUBLE,
  STATUS VARCHAR(16 OCTETS) NOT NULL,
  IGNORED BOOLEAN NOT NULL,
  TECH_START TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
  TECH_END TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
  TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME (TECH_START, TECH_END)
) IN USERSPACE1 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE
  DQ_ISSUES_FOR_ASSET_COLUMNS
ADD
  PRIMARY KEY (
    ISSUE_ID,
    ASSET_ID,
    CONTAINER_ID,
    COLUMN_NAME
  ) ENFORCED;
ALTER TABLE
  DQ_ISSUES_FOR_ASSET_COLUMNS
ADD
  CONSTRAINT FK_DQ_ISSUES_FOR_ASSET_COLUMNS_CONTAINER_DATA_ASSET_COLUMNS_6 FOREIGN KEY (CONTAINER_ID, ASSET_ID, COLUMN_NAME) REFERENCES CONTAINER_DATA_ASSET_COLUMNS (CONTAINER_ID, ASSET_ID, NAME) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;
ALTER TABLE
  DQ_ISSUES_FOR_ASSET_COLUMNS
ADD
  CONSTRAINT FK_DQ_ISSUES_FOR_ASSET_COLUMNS_DQ_CHECKS_5 FOREIGN KEY (CHECK_ID, CONTAINER_ID) REFERENCES DQ_CHECKS (CHECK_ID, CONTAINER_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;

dq_issues_for_assets

This table has the following columns:

  • issue_id - The identifier of the asset issue.
  • container_id - The identifier of the project.
  • asset_id - The identifier of the asset.
  • check_id - The identifier for the data quality check.
  • number_of_occurences - The number of occurences found for the data quality check.
  • number_of_tested_records - The number of tested records found for the data quality check.
  • percent_occurences - The percentage of the occurences.
  • score - Score found after running a check.
  • status - The current status of an issue.
  • ignored - Flag to identify if the current issue is participating in the data quality issue.

Postgres

CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS dq_issues_for_assets (
  issue_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  container_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  asset_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  check_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  number_of_occurrences bigint NOT NULL DEFAULT 0,
  number_of_tested_records bigint NOT NULL DEFAULT 0,
  percent_occurrences double precision,
  score double precision,
  status character varying(16) COLLATE pg_catalog.default NOT NULL,
  ignored boolean NOT NULL,
  tech_start timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tech_end timestamp(6) without time zone NOT NULL DEFAULT to_timestamp('9999-12-30' :: text, 'YYYY-MM-DD' :: text),
  ts_id timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT dq_issues_for_assets_pkey PRIMARY KEY (issue_id, asset_id, container_id),
  CONSTRAINT fk_dq_issues_for_assets_container_data_assets_4 FOREIGN KEY (container_id, asset_id) REFERENCES container_data_assets (container_id, asset_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_dq_issues_for_assets_dq_checks_3 FOREIGN KEY (container_id, check_id) REFERENCES dq_checks (container_id, check_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
)


Db2

CREATE TABLE statement:

CREATE TABLE DQ_ISSUES_FOR_ASSETS (
  ISSUE_ID VARCHAR(36 OCTETS) NOT NULL,
  CONTAINER_ID VARCHAR(36 OCTETS) NOT NULL,
  ASSET_ID VARCHAR(36 OCTETS) NOT NULL,
  CHECK_ID VARCHAR(36 OCTETS) NOT NULL,
  NUMBER_OF_OCCURRENCES BIGINT NOT NULL WITH DEFAULT 0,
  NUMBER_OF_TESTED_RECORDS BIGINT NOT NULL WITH DEFAULT 0,
  PERCENT_OCCURRENCES DOUBLE,
  SCORE DOUBLE,
  STATUS VARCHAR(16 OCTETS) NOT NULL,
  IGNORED BOOLEAN NOT NULL,
  TECH_START TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
  TECH_END TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
  TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
  PERIOD SYSTEM_TIME (TECH_START, TECH_END)
) IN USERSPACE1 ORGANIZE BY ROW;

ALTER TABLE statements:

ALTER TABLE
  DQ_ISSUES_FOR_ASSETS
ADD
  PRIMARY KEY (ISSUE_ID, ASSET_ID, CONTAINER_ID) ENFORCED;
ALTER TABLE
  DQ_ISSUES_FOR_ASSETS
ADD
  CONSTRAINT FK_DQ_ISSUES_FOR_ASSETS_CONTAINER_DATA_ASSETS_4 FOREIGN KEY (CONTAINER_ID, ASSET_ID) REFERENCES CONTAINER_DATA_ASSETS (CONTAINER_ID, ASSET_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;
ALTER TABLE
  DQ_ISSUES_FOR_ASSETS
ADD
  CONSTRAINT FK_DQ_ISSUES_FOR_ASSETS_DQ_CHECKS_3 FOREIGN KEY (CHECK_ID, CONTAINER_ID) REFERENCES DQ_CHECKS (CHECK_ID, CONTAINER_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;

dq_checks table

This table has the following columns:

  • check_id - The identifier for the data quality check.
  • container_id - The identifier of the project.
  • dq_check_type - The type of data quality check (could be profilling or data_rule).
  • dq_check_name - The name of the data quality check.
  • dq_dimension_id - The identifier of the data quality dimension.
  • created_by - The user who created this data quality check.
  • created_on - The creation time of the data quality check.

Postgres

CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS dq_checks (
  check_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  container_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  dq_check_type character varying(256) COLLATE pg_catalog.default NOT NULL,
  dq_check_name character varying(256) COLLATE pg_catalog.default NOT NULL,
  dq_dimension_id character varying(36) COLLATE pg_catalog.default NOT NULL,
  created_by character varying(256) COLLATE pg_catalog.default NOT NULL,
  created_on timestamp(6) without time zone NOT NULL,
  tech_start timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tech_end timestamp(6) without time zone NOT NULL DEFAULT to_timestamp('9999-12-30' :: text, 'YYYY-MM-DD' :: text),
  ts_id timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT dq_checks_pkey PRIMARY KEY (check_id, container_id),
  CONSTRAINT fk_dq_checks_containers_1 FOREIGN KEY (container_id) REFERENCES containers (container_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_dq_checks_dq_v4_dimensions_2 FOREIGN KEY (dq_dimension_id) REFERENCES dq_v4_dimensions (dimension_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
)


Db2

CREATE TABLE statement:

 CREATE TABLE   DQ_CHECKS   (
 CHECK_ID  VARCHAR(36 OCTETS) NOT NULL , 
 CONTAINER_ID  VARCHAR(36 OCTETS) NOT NULL , 
 DQ_CHECK_TYPE  VARCHAR(256 OCTETS) NOT NULL , 
 DQ_CHECK_NAME  VARCHAR(256 OCTETS) NOT NULL , 
 DQ_DIMENSION_ID  VARCHAR(36 OCTETS) NOT NULL , 
 CREATED_BY  VARCHAR(256 OCTETS) NOT NULL , 
 CREATED_ON  TIMESTAMP(12) NOT NULL , 
 TECH_START  TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN , 
 TECH_END  TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END , 
 TS_ID  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID , 
 PERIOD SYSTEM_TIME ( TECH_START , TECH_END ) )   
 IN  USERSPACE1   
 ORGANIZE BY ROW;

ALTER TABLE statements:

 ALTER TABLE   DQ_CHECKS  
 ADD PRIMARY KEY
 ( CHECK_ID ,
 CONTAINER_ID )
 ENFORCED;
ALTER TABLE   DQ_CHECKS  
ADD CONSTRAINT  FK_DQ_CHECKS_CONTAINERS_1  FOREIGN KEY
( CONTAINER_ID )
REFERENCES   CONTAINERS 
( CONTAINER_ID )
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE   DQ_CHECKS  
ADD CONSTRAINT  FK_DQ_CHECKS_DQ_V4_DIMENSIONS_2  FOREIGN KEY
( DQ_DIMENSION_ID )
REFERENCES   DQ_V4_DIMENSIONS 
( DIMENSION_ID )
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

dq_v4_dimensions table

This table contains information about the quality dimensions for the rule.

This table has the following columns:

  • dimension_id - The identifier of the data quality dimension.
  • name - The name of the data quality dimension.
  • description - The description of the data quality dimension.

Postgres

CREATE TABLE statement:

create table dq_v4_dimensions(dimension_id varchar(128) not null,
container_id varchar(36) not null,
name varchar(256) not null,
description varchar(256) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(dimension_id,
container_id))

ALTER TABLE statement:

alter table dq_v4_dimensions add constraint fk_dq_v4_dimensions_containers_1 foreign key (container_id) references containers(container_id) on
delete
	cascade on
	update
	no action

ALTER TABLE statement:

ALTER TABLE IF EXISTS  dq_v4_dimensions
    OWNER to postgres;


Db2

CREATE TABLE statement:

create table dq_v4_dimensions(dimension_id varchar(128) not null,
container_id varchar(36) not null,
name varchar(256) not null,
description varchar(256) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(dimension_id,
container_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statement:

alter table dq_v4_dimensions add constraint fk_dq_v4_dimensions_containers_1 foreign key (container_id) references containers(container_id) on
delete
	cascade on
	update
	no action

dq_rule_definitions table

This table contains the actual metadata for data quality rule.

This table has the following columns:

  • rule_definition_id - The identifier of the data quality rule definition.
  • container_id - The identifier of the catalog or project.
  • expression - The expression of the data quality rule definition.
  • dq_dimension_id - The identifier of the data quality dimension.
  • name - The name of the data quality definition.

Postgres

CREATE TABLE statement:

create table dq_rule_definitions(rule_definition_id varchar(128) not null,
container_id varchar(36) not null,
expression text not null,
dq_dimension_id varchar(128),
name varchar(256) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(rule_definition_id))

ALTER TABLE statements:

alter table dq_rule_definitions add constraint fk_dq_rule_definitions_containers_2 foreign key (container_id) references containers(container_id) on
delete
	cascade on
	update
	no action
alter table dq_rule_definitions add constraint fk_dq_rule_definitions_dq_v4_dimensions_3 foreign key (dq_dimension_id,
container_id) references dq_v4_dimensions(dimension_id,
container_id) on
delete
	cascade on
	update
	no action


Db2

CREATE TABLE statement:

create table dq_rule_definitions(rule_definition_id varchar(128) not null,
container_id varchar(36) not null,
expression clob not null,
dq_dimension_id varchar(128),
name varchar(256) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(rule_definition_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statements:

alter table dq_rule_definitions add constraint fk_dq_rule_definitions_containers_2 foreign key (container_id) references containers(container_id) on
delete
	cascade on
	update
	no action
alter table dq_rule_definitions add constraint fk_dq_rule_definitions_dq_v4_dimensions_3 foreign key (dq_dimension_id,
container_id) references dq_v4_dimensions(dimension_id,
container_id) on
delete
	cascade on
	update
	no action

dq_rules_defs table

This table contains details of binding between rule and definition as multiple bindings can be made.

This table has the following columns:

  • rule_id - The identifier of the data quality rule.
  • rule_definition_id - The identifier for the rule definition.
  • disambiguator - The disambiguator number.

Postgres

CREATE TABLE statement:

create table dq_rules_defs(rule_id varchar(128) not null,
rule_definition_id varchar(128) not null,
disambiguator integer,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(rule_id,
rule_definition_id))

ALTER TABLE statements:

alter table dq_rules_defs add constraint fk_dq_rules_defs_dq_rules_1 foreign key (rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action
alter table dq_rules_defs add constraint fk_dq_rules_defs_dq_rule_definitions_2 foreign key (rule_definition_id) references dq_rule_definitions(rule_definition_id) on
delete
	cascade on
	update
	no action


Db2

CREATE TABLE statement:

create table dq_rules_defs(rule_id varchar(128) not null,
rule_definition_id varchar(128) not null,
disambiguator integer,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(rule_id,
rule_definition_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statements:

alter table dq_rules_defs add constraint fk_dq_rules_defs_dq_rules_1 foreign key (rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action
alter table dq_rules_defs add constraint fk_dq_rules_defs_dq_rule_definitions_2 foreign key (rule_definition_id) references dq_rule_definitions(rule_definition_id) on
delete
	cascade on
	update
	no action

dq_rules table

This table contains information about the data quality rules.

This table has the following columns:

  • rule_id - The identifier of the data quality rule.
  • container_id - The identifier of the catalog or project.
  • dq_dimension_id - The identifier of the data quality rule dimension.
  • name - The name of the data quality rule.
  • output_asset_id - The identifier of the output asset.
  • output_asset_container_id - The identifier of the output catalog or project.

Postgres

CREATE TABLE statement:

create table dq_rules(rule_id varchar(128) not null,
container_id varchar(36) not null,
dq_dimension_id varchar(128),
name varchar(256) not null,
output_asset_id varchar(128),
output_asset_container_id varchar(36),
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(rule_id))

ALTER TABLE statement:

alter table dq_rules add constraint fk_dq_rules_dq_v4_dimensions_5 foreign key (dq_dimension_id,
container_id) references dq_v4_dimensions(dimension_id,
container_id) on
delete
	cascade on
	update
	no action


Db2

CREATE TABLE statement:

create table dq_rules(rule_id varchar(128) not null,
container_id varchar(36) not null,
dq_dimension_id varchar(128),
name varchar(256) not null,
output_asset_id varchar(128),
output_asset_container_id varchar(36),
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(rule_id),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statement:

alter table dq_rules add constraint fk_dq_rules_dq_v4_dimensions_5 foreign key (dq_dimension_id,
container_id) references dq_v4_dimensions(dimension_id,
container_id) on
delete
	cascade on
	update
	no action

dq_rule_bindings table

This table contains information about the Data Quality Rule Bindings.

This table has the following columns:

  • rule_id - The identifier of the data quality rule.
  • rule_definition_id - The identifier for the rule definition.
  • variable_name - The identifier of the variable that binds to column for data quality rule.
  • column_name - The name of the column.
  • container_id - The identifier of the catalog or project.
  • asset_id - The identifier of the asset.

Postgres

CREATE TABLE statement:

create table dq_rule_bindings(rule_id varchar(128) not null,
variable_name varchar(256) not null,
column_name varchar(256) not null,
container_id varchar(36) not null,
asset_id varchar(128) not null,
rule_definition_id varchar(128) default 'na' not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(rule_id,
rule_definition_id,
variable_name,
column_name))

ALTER TABLE statements:

alter table dq_rule_bindings add constraint fk_dq_rule_bindings_dq_rules_6 foreign key (rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action
alter table dq_rule_bindings add constraint fk_dq_rule_bindings_container_data_asset_columns_7 foreign key (container_id,
asset_id,
column_name) references container_data_asset_columns(container_id,
asset_id,
name) on
delete
	cascade on
	update
	no action 
alter table dq_rule_bindings add constraint fk_dq_rule_bindings_dq_rules_defs_3 foreign key (rule_id,
rule_definition_id) references dq_rules_defs(rule_id,
rule_definition_id) on
delete
	cascade on
	update
	no action


Db2

CREATE TABLE statement:

create table dq_rule_bindings(rule_id varchar(128) not null,
variable_name varchar(256) not null,
column_name varchar(256) not null,
container_id varchar(36) not null,
asset_id varchar(128) not null,
rule_definition_id varchar(128) default 'na' not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(rule_id,
rule_definition_id,
variable_name,
column_name),
period SYSTEM_TIME (tech_start,
tech_end) ) ORGANIZE by row

ALTER TABLE statements:

alter table dq_rule_bindings add constraint fk_dq_rule_bindings_dq_rules_6 foreign key (rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action
alter table dq_rule_bindings add constraint fk_dq_rule_bindings_container_data_asset_columns_7 foreign key (container_id,
asset_id,
column_name) references container_data_asset_columns(container_id,
asset_id,
name) on
delete
	cascade on
	update
	no action
alter table dq_rule_bindings add constraint fk_dq_rule_bindings_dq_rules_defs_3 foreign key (rule_id,
rule_definition_id) references dq_rules_defs(rule_id,
rule_definition_id) on
delete
	cascade on
	update
	no action

dq_rule_execution table

This table contains information about the rule job for the data quality rule.

This table has the following columns:

  • dq_rule_id - The identifier of the data quality rule.
  • execution_id - The identifier of the job for data quality rule job.
  • start_time - The start time of the job for data quality rule job.
  • end_time - The end time of the job for data quality rule job.
  • nb_tested_rows - The number of tested rows for the data quality rule job.
  • nb_passing_rows - The number of passing rows for the data quality rule job.
  • nb_failing_rows - The number of failing rows for the data quality rule job.
  • percent_passing_rows - The percentage of passing rows for the data quality rule job.
  • percent_failing_rows - The percentage of failing rows for the data quality rule job.
  • sampling_used - Specifies whether the sampling is used for the data quality rule.
  • sample_size - The sampling size for the data quality rule.
  • sample_type - The sampling type for the data quality rule.
  • flow_job_id - The identifier of the DataStage flow job.
  • flow_job_run_id - The identifier of the DataStage flow job run.

Postgres

CREATE TABLE statement:

CREATE TABLE "globalschema".dq_rule_execution(
  dq_rule_id varchar(128) NOT NULL, 
  execution_id varchar(256) NOT NULL, 
  start_time timestamp(6) NOT NULL, 
  end_time timestamp(6) NOT NULL, 
  nb_tested_rows bigint NOT NULL, 
  nb_passing_rows bigint NOT NULL, 
  nb_failing_rows bigint NOT NULL, 
  percent_passing_rows float NOT NULL, 
  percent_failing_rows float NOT NULL, 
  sampling_used decimal(1) CHECK (
    sampling_used in (0, 1)
  ) NOT NULL, 
  sample_size bigint, 
  sample_type varchar(256), 
  flow_job_id varchar(128), 
  flow_job_run_id varchar(128), 
  tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'), 
  ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY(dq_rule_id, execution_id)
)

ALTER TABLE statement:

alter table dq_rule_execution add constraint fk_dq_rule_execution_dq_rules_8 foreign key (dq_rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action


Db2

CREATE TABLE statement:

CREATE TABLE "globalschema".dq_rule_execution(
  dq_rule_id varchar(128) NOT NULL, 
  execution_id varchar(256) NOT NULL, 
  start_time timestamp(6) NOT NULL, 
  end_time timestamp(6) NOT NULL, 
  nb_tested_rows bigint NOT NULL, 
  nb_passing_rows bigint NOT NULL, 
  nb_failing_rows bigint NOT NULL, 
  percent_passing_rows float NOT NULL, 
  percent_failing_rows float NOT NULL, 
  sampling_used decimal(1) CHECK (
    sampling_used in (0, 1)
  ) NOT NULL, 
  sample_size bigint, 
  sample_type varchar(256), 
  flow_job_id varchar(128), 
  flow_job_run_id varchar(128), 
  tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'), 
  ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY(dq_rule_id, execution_id)
)

ALTER TABLE statement:

alter table dq_rule_execution add constraint fk_dq_rule_execution_dq_rules_8 foreign key (dq_rule_id) references dq_rules(rule_id) on
delete
	cascade on
	update
	no action


MS SQL Server

CREATE TABLE statement:

CREATE TABLE "globalschema".dq_rule_execution(
  dq_rule_id varchar(128) NOT NULL, 
  execution_id varchar(256) NOT NULL, 
  start_time DATETIME2 NOT NULL, 
  end_time DATETIME2 NOT NULL, 
  nb_tested_rows bigint NOT NULL, 
  nb_passing_rows bigint NOT NULL, 
  nb_failing_rows bigint NOT NULL, 
  percent_passing_rows float NOT NULL, 
  percent_failing_rows float NOT NULL, 
  sampling_used decimal(1) CHECK (
    sampling_used in (0, 1)
  ) NOT NULL, 
  sample_size bigint, 
  sample_type varchar(256), 
  flow_job_id varchar(128), 
  flow_job_run_id varchar(128), 
  tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
  tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
  ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL, 
  CONSTRAINT PK_dq_rule_execution_globalschema PRIMARY KEY(dq_rule_id, execution_id), 
  PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
  SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = "globalschema".hist_dq_rule_execution
  )
)

Learn more

Parent topic: Reporting tables