Ataccama ONE Agentic ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (17.1.0) 17.0.0 16.3.x 16.2.0 16.1.0 16.0.0 15.4.0 15.3.0 15.2.0 15.1.0 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
Downloads

Export Invalid Records: Deep Dive

This page explains the technical details of how exports work, what data is included, and how records are stored and retained. For instructions on how to set up export of invalid records, see Export Invalid Records.

Export granularity

The export contains one row for each rule that a record fails. A record that fails three rules produces three rows.

This differs from post-processing plans, where each input record produces a single row that lists all the rules it failed. Export of invalid records is not a replacement for post-processing plans. It is a complementary option for pushdown processing, which cannot use post-processing plans.

Export table structure

The export table contains the following columns, in this order. All columns are nullable.

Position Column Data type Description

1

TABLE_PATH

STRING

Location of the source data, in the format <catalog>/<schema>/<table>.

2

PROCESSING_TIME

TIMESTAMP

In Snowflake: TIMESTAMP_LTZ

The time the DQ evaluation started. This value comes from the Data Processing Engine (DPE) clock, not from the database clock.

3

PROCESSING_ID

STRING

Unique identifier for the evaluation run. This is the processing ID you select when you browse previous monitoring project runs.

4

CONFIGURATION_ID

STRING

Identifier of the monitoring project that generated the results.

5

RECORD_ID

STRING

Identifier for the record, taken from the primary key of the source table. See Record identifiers.

6

INVALID_VALUE

STRING

The invalid value. For rules with multiple inputs, this is the value of the first input attribute only.

7

RULE_NAME

STRING

Name of the failed rule.

8

ATTRIBUTE

STRING

Name of the attribute that contains the invalid value. For rules with multiple inputs, this is the first input attribute only.

9

EXPLANATION

STRING

Explanation as defined in the rule.

10

SCORE

INT

In BigQuery and Snowflake: INTEGER

Score as defined in the rule. Scores are optional, so if the rule does not set one, the value is 0.

11

ADDITIONAL_ATTRIBUTES

STRING

Any additional attributes you configured, stored as a JSON object serialized to text. See Additional attributes.
Records are written to the export table by column position, not by column name. If you create the export table yourself to use it with the Select from catalog option, create the columns in exactly the order in the preceding table. If the order differs, values are written to the wrong columns and no error is reported.

Record identifiers

The value of RECORD_ID comes from the primary key defined on the source table. The primary key is read from the table metadata in the data source.

If the source table has no primary key defined, RECORD_ID is empty. You cannot select a column to use as the record identifier in the export settings.

The primary key value is stored as a JSON object serialized to text, including when the primary key consists of a single column. For example, a table with the primary key (key STRING, order INTEGER) produces values such as {"key":"abc-123","order":10}.

Additional attributes

Additional attributes are stored in a single column as a JSON object serialized to text. The keys are the attribute names, and the values keep their original data types.

For example, exporting the attributes sell_out_volume and promo_sell_out_volume produces values such as {"sell_out_volume":6.295,"promo_sell_out_volume":2.765}.

To learn which attributes are included and which you need to add yourself, see Additional attributes.

Create the export table manually

If you let Ataccama ONE create the export table for you, you don’t need the following statements. Use them when you want to create the table yourself, for example to use it with the Select from catalog option.

In each statement, replace <catalog>, <schema>, <project>, <dataset>, and <database> with the location in your database where you want to create the table. Replace <name> with the value you enter in the Name field of the Invalid records export settings dialog.

The table name is the fixed ATA_INVALID_RECORDS_ prefix in uppercase, followed by the name you enter, with its casing preserved. Ataccama ONE creates the table using quoted identifiers, so the statements use quoting as well. BigQuery and Snowflake are case-sensitive and store the name as it is created. Databricks is case-insensitive and displays table names in lowercase.

Databricks with Unity Catalog
CREATE TABLE IF NOT EXISTS `<catalog>`.`<schema>`.`ATA_INVALID_RECORDS_<name>` (
  `TABLE_PATH`            STRING,
  `PROCESSING_TIME`       TIMESTAMP,
  `PROCESSING_ID`         STRING,
  `CONFIGURATION_ID`      STRING,
  `RECORD_ID`             STRING,
  `INVALID_VALUE`         STRING,
  `RULE_NAME`             STRING,
  `ATTRIBUTE`             STRING,
  `EXPLANATION`           STRING,
  `SCORE`                 INT,
  `ADDITIONAL_ATTRIBUTES` STRING
)
USING DELTA
CLUSTER BY (`TABLE_PATH`, `PROCESSING_TIME`, `PROCESSING_ID`, `CONFIGURATION_ID`);

Databricks with the Hive metastore and IOMETE don’t support clustering:

Databricks with the Hive metastore and IOMETE
CREATE TABLE IF NOT EXISTS `hive_metastore`.`<schema>`.`ATA_INVALID_RECORDS_<name>` (
  `TABLE_PATH`            STRING,
  `PROCESSING_TIME`       TIMESTAMP,
  `PROCESSING_ID`         STRING,
  `CONFIGURATION_ID`      STRING,
  `RECORD_ID`             STRING,
  `INVALID_VALUE`         STRING,
  `RULE_NAME`             STRING,
  `ATTRIBUTE`             STRING,
  `EXPLANATION`           STRING,
  `SCORE`                 INT,
  `ADDITIONAL_ATTRIBUTES` STRING
)
USING DELTA;
BigQuery
CREATE TABLE IF NOT EXISTS `<project>.<dataset>.ATA_INVALID_RECORDS_<name>` (
  `TABLE_PATH`            STRING,
  `PROCESSING_TIME`       TIMESTAMP,
  `PROCESSING_ID`         STRING,
  `CONFIGURATION_ID`      STRING,
  `RECORD_ID`             STRING,
  `INVALID_VALUE`         STRING,
  `RULE_NAME`             STRING,
  `ATTRIBUTE`             STRING,
  `EXPLANATION`           STRING,
  `SCORE`                 INTEGER,
  `ADDITIONAL_ATTRIBUTES` STRING
)
CLUSTER BY `TABLE_PATH`, `PROCESSING_TIME`, `PROCESSING_ID`, `CONFIGURATION_ID`;
Snowflake
CREATE TABLE IF NOT EXISTS "<database>"."<schema>"."ATA_INVALID_RECORDS_<name>" (
  "TABLE_PATH"            STRING,
  "PROCESSING_TIME"       TIMESTAMP_LTZ,
  "PROCESSING_ID"         STRING,
  "CONFIGURATION_ID"      STRING,
  "RECORD_ID"             STRING,
  "INVALID_VALUE"         STRING,
  "RULE_NAME"             STRING,
  "ATTRIBUTE"             STRING,
  "EXPLANATION"           STRING,
  "SCORE"                 INTEGER,
  "ADDITIONAL_ATTRIBUTES" STRING
)
CLUSTER BY ("TABLE_PATH", "PROCESSING_TIME", "PROCESSING_ID", "CONFIGURATION_ID");

Storage and retention

Records are stored in the table you specify and never leave your database environment.

New records are appended with timestamps. Existing records are then managed according to the Write strategy you selected.

Consider cleaning up old export records when:

  • Table size impacts storage costs.

  • Historical records are no longer needed for audit.

  • Data quality issues have been resolved.

Replace

New records are appended, and records from previous runs of the same export configuration are deleted.

A record is deleted only if all of the following are true:

  • TABLE_PATH is the same as for the current run.

  • PROCESSING_TIME is older than the start time of the current run.

  • PROCESSING_ID is different from the processing ID of the current run.

  • CONFIGURATION_ID is the same as for the current run.

Records exported from other catalog items, or by other monitoring projects, are therefore not affected, even when they are stored in the same table.

The deletion runs only if the preceding insert succeeded, including when the insert added no records. If the insert fails, records from previous runs are kept.

Append

All previously exported records are kept. The table grows with every DQ evaluation.

Limitations and known issues

  • The export table uses generalized data types, such as STRING and JSON serialized to text, instead of the native types of the source data. This is because a single export table can store data from several source tables.

  • The export table has no indexing and it replicates source data, so it can become large. Querying it efficiently can be a challenge, and the more additional attributes you export, the more storage the table uses.

  • For rules with multiple inputs, only the first input attribute is reported in ATTRIBUTE and INVALID_VALUE. You need to add the remaining input attributes as additional attributes yourself.

  • Rules are referenced by name and the output contains no DQ check identifier. If the same rule is applied more than once to a catalog item, you cannot tell the results apart.

  • RECORD_ID is empty for source tables that have no primary key defined.

Was this page useful?