Ataccama ONE Agentic ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (17.2.0) 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

You can configure monitoring projects to automatically export records that fail validation rules to a table. Invalid records are exported with each DQ evaluation, ensuring you always have current results for analysis and remediation. The export contains one row for each rule that a record fails.

Why export invalid records?

Exporting invalid records is useful for sharing results with data owners or stakeholders without access to Ataccama ONE, or for integrating with external remediation workflows.

Invalid record exports vs. samples

Invalid record exports

All invalid records exported to an external table, up to the configured record limit. Useful for comprehensive analysis, sharing, and remediation.

Invalid record samples

A limited sample of invalid data available from the monitoring project Configuration & Results tab. Useful for quick investigation within ONE. See Invalid results samples.

Supported sources

You can export invalid records from catalog items that are evaluated with or without pushdown processing. In both cases, the export table has the same columns.

Pushdown sources

Invalid records can be exported from the following sources, provided pushdown processing is enabled:

  • BigQuery

  • Databricks

  • IOMETE

  • Snowflake (requires Legacy pushdown to be turned off on the connection). For details, see Legacy pushdown.

The export table is created in the same data source as the catalog item.

Non-pushdown sources

For catalog items that are evaluated without pushdown, invalid records are exported to a table in a relational database of your choice.

Before you start, make sure that:

  • The target database has a JDBC connection in ONE with write credentials. Only connections with write credentials are offered as export targets. See Write credentials and data export.

  • The database user of the write credentials has the required privileges. Privileges are not checked when you save the export settings, so if a privilege is missing, the DQ evaluation fails. The following privileges are required:

    • INSERT, for all exports.

    • CREATE TABLE, if ONE creates the export table for you.

    • DELETE, if you use the Replace write strategy.

  • DQ evaluation runs locally on the Data Processing Engine (DPE). If the catalog item is processed on Spark, invalid records are not exported. The DQ evaluation still completes, and the skipped export is recorded only in the DPE log.

If the export fails, the whole DQ evaluation fails and no DQ results are produced for that run.

The job fails with the error Invalid-records export to <source table> failed and the DQ evaluation cannot be completed. The export target may be empty or partially written. To find the cause, check the job log.

Supported target databases

The following databases support automatic table creation and both the Append and Replace write strategies:

  • PostgreSQL, including Amazon Aurora PostgreSQL

  • MySQL, including MariaDB and Amazon Aurora MySQL

  • Microsoft SQL Server, including connections that use the jTDS driver

  • Oracle

  • Amazon Redshift

You can also export to other relational databases that have a JDBC connection, such as Teradata, SAP HANA, Sybase, Informix, Dremio, or Db2 connected through a generic JDBC connection. These databases aren’t natively supported, so only the Select from catalog option with the Append write strategy is available, and you need to create the export table yourself. See Create the export table manually.

In the export settings for these databases, Select in source and Replace are unavailable, and the dialog shows the following messages:

  • Automatic table creation is not supported for this database. Select an existing catalog item.

  • Replace is not supported for this database. Records are appended to the target table.

The same rule is enforced when the export settings are saved. Saving an export that creates a table in such a database fails with the error This database does not support automatic table creation.

The following can’t be used as export targets:

  • Read-only sources, such as Azure Data Explorer or the Microsoft Fabric Lakehouse SQL analytics endpoint.

  • File and object storage sources.

  • Snowflake, BigQuery, Databricks, Azure Synapse, and IOMETE.

Performance impact of exporting invalid records

In addition to the columns used by the evaluated rules, exporting invalid records reads the primary key columns that identify the records and the additional attributes selected for the export. The more additional attributes you select, the more data is read during the monitoring project run. For details and recommendations, see DQ evaluation is slow.

Set up export of invalid records

To configure automatic export:

  1. Open your monitoring project.

  2. On the Configuration & Results tab, select the three-dot menu for the catalog item and then Add pushdown invalid record export.

    Select Add pushdown invalid record export

    For catalog items that are evaluated without pushdown, select Add invalid record export instead.

  3. Configure the export settings and select Save. For details, see Export settings.

  4. Publish the changes.

The next time you run DQ evaluation, invalid records will be exported to your specified table.

Export settings

Configure the following settings in the Invalid records export settings dialog:

Export configuration for pushdown processing

Target table

Specify where invalid records should be exported.

Ensure your database role has the required permissions. For details, see BigQuery permissions, Databricks permissions, or Snowflake permissions.

  • Target connection: Available only for non-pushdown sources. Select the connection to the database where you want to export the table. Connections are listed in the format <source> / <connection>, and only connections with write credentials are included. For the privileges that the write credentials need, see Non-pushdown sources.

  • Schema: Select the schema where you want to export the table. For databases without schemas, such as MySQL and MariaDB, select Connection default.

  • Select in source: Create a new table or use an existing table in your database.

    • Name: Enter the table name. Use only letters, digits, and underscores. If the table does not exist, it will be created automatically with the required structure.

      There can be a maximum of one export setting per monitoring project catalog item.

    • Prefix: The prefix is fixed as ATA_INVALID_RECORDS_ for easy identification of export tables.

  • Select from catalog: Select an existing catalog item as the export destination.

    The catalog item name must start with ATA_INVALID_RECORDS and must have all required columns. The prefix isn’t case-sensitive, because some databases store table names in lowercase. To create such a catalog item, first create a table with these specifications in your database, and then import the table to the catalog. For the required columns, see Export table structure.

    For non-pushdown sources, this is the only option for databases that don’t support automatic table creation, and the catalog item must be imported through the selected target connection. See Supported target databases.

Write credentials

Available only for non-pushdown sources. The write credentials of the target connection are used to write the export table.

Enabled

Enable export of invalid records using these settings.

Record limit

Set the maximum number of records to export for each catalog item in a DQ evaluation.

To be sure that your exports are complete, set the limit comfortably above the number of invalid records you expect. If the limit is reached, the DQ evaluation completes successfully and the export contains records up to the limit.

The limit counts rows in the export table, not source records, and the export contains one row for each rule that a record fails. The default is 1,000,000,000. You can set any value from 1 to 100,000,000,000.

If you consistently see a high number of invalid records, this might indicate misconfigured DQ rules or monitoring settings that require adjustment. Review your rules logic to make sure it is correct.

Write strategy

Choose how new export results interact with existing data:

  • Replace: Adds the invalid records from the latest DQ evaluation and removes those exported by previous runs of the same export configuration. Records exported from other catalog items or by other monitoring projects are kept, even when they are stored in the same table.

  • Append: Appends new invalid records while keeping the previous ones.

For the exact conditions under which records are removed, see Replace.

For non-pushdown sources, Replace is available only for the databases listed in Supported target databases. For other databases, records are always appended.

Additional attributes

The export reports the first input attribute of each rule in the ATTRIBUTE and INVALID_VALUE columns. The additional attributes contain:

  • Remaining rule inputs: For rules with multiple inputs, the remaining input attributes are added automatically.

  • User-selected attributes: You can select additional attributes to provide context for investigating DQ issues. For example, if an email address fails validation, adding the country code helps you identify whether all issues originate from the same country.

Additional attributes appear in a single column in the export table as a JSON object serialized to text.

View exported records

After DQ evaluation completes, all invalid records are written to your specified table.

To view the exported records:

  1. In the monitoring project, go to the Export tab.

  2. Select the link to your export table.

    Select export table from the Export tab

    This opens the table in your database interface where you can view, query, and download the data.

    View export table in your database interface

For IOMETE sources, a direct link to the export table is not available. The Export tab instead displays the full table name in the format <catalog_name>.<schema_name>.<table_name>, which you can use to locate the table in IOMETE. See Limitations of IOMETE pushdown.

For non-pushdown sources, the Export tab doesn’t link to the export table and doesn’t show export errors. To view the exported records, query the export table directly in your database. If the export fails, the DQ evaluation fails as well, so check the job log for details.

Manage export settings

On the monitoring project Export tab, you can:

  • Modify the export configuration: Select Configure to update settings. Changes take effect after you publish and run the next evaluation.

  • Disable the export: Select Configure and in the settings dialog, uncheck Enable to temporarily stop exporting without removing the configuration.

  • Delete the export settings: Select the Delete icon to remove the configuration.

    Deleting the export configuration does not delete the export table or its data from your database.

Export invalid records: Deep dive

To learn more about the technical details of how exports work, what data is included, and how records are stored and retained, see Export Invalid Records: Deep Dive.

Was this page useful?