0 / 0
Data quality rules reporting tables

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

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more