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 beprofilling
ordata_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