Export Invalid Records: Deep Dive

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, but a complementary option. For pushdown processing, which cannot use post-processing plans, it is the only way to export invalid records.

The export table contains the following columns, in the order in which ONE creates them. All columns are nullable, and no primary key or index is created.

The data types in the following table are those used for pushdown sources. For non-pushdown sources, see Data types for non-pushdown sources.

Records are written to the export table by column name. If you create the export table yourself, the order of the columns doesn’t matter. The table can also contain additional columns, provided that they don’t require a value when a record is inserted, for example, because they are nullable. These columns are left empty.

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.

If the primary key consists of a single column, RECORD_ID contains the value of that column, for example, A003.

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

For non-pushdown sources, date and time values in the primary key are formatted as yyyy-MM-dd HH:mm:ss, in the time zone of the DPE.

Additional attributes are stored in a single column as a JSON object serialized to text. The keys are the attribute names.

For pushdown sources, 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}.

For non-pushdown sources, all values are stored as strings, for example, {"email":"jane@","country":"CZ"}. The object contains the attributes you selected, followed by the remaining inputs of the rule. If there are no additional attributes, the value is NULL.

To learn which attributes are included, see Additional attributes.

If you let 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. 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 the Hive metastore and IOMETE don’t support clustering:

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.