Data quality rules reporting tables

Last updated: Dec 13, 2024

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

Was the topic helpful?

0/1000