SQL Pushdown Processing

With standard processing, catalog item data is fetched from the data source to Ataccama DPEs and processed there. For large datasets, this means transferring significant volumes of data and scaling DPE capacity to match. Pushdown processing turns this around, with three key benefits:

Pushdown processing is enabled on the connection level. To enable it, follow the setup instructions for your data source:

Pushdown processing applies to the following job types:

Very small datasets are better suited for non-pushdown processing because of SQL overhead.

Standard profiling fetches all catalog item data to Ataccama DPE, iterates rows, and executes the profiling locally. Pushdown profiling instead creates specialized database-specific SQL queries that are executed inside the data source, and only the profiling results are returned to Ataccama DPE.

By default, when you start profiling, a single SQL query is generated per column that combines all the statistics Ataccama requires for that column. On Snowflake, a single query covers up to 20 columns of a table, or 50 columns of a view that isn’t cached, by default. The total number of queries for the table is determined by the number of columns in the table: you can minimize the number of queries by merging queries for multiple columns into a single, bigger SQL query. See Pushdown profiling configuration.

Each query reads the source, so a wide catalog item is read once per group of columns. For a view, an SQL catalog item, or a sliced source, every read also re-executes the underlying query or filter. To avoid repeating that work when the columns don’t fit in one query, DPE materializes the source once into a profiling cache table (ata_profiling_cache_*) and runs the profiling queries against that copy. The copy is still read once per group of columns, but as a plain table, without recomputing the view, query, or slice. This applies to Snowflake, Databricks, BigQuery, and IOMETE. Azure Synapse always reads the source directly.

The cache table is created in the operational database configured on the connection. The operational database is optional for profiling: without it, profiling returns the same results but takes longer for views, SQL catalog items, and sliced sources. The cache table is removed as soon as profiling no longer needs it; if the removal was interrupted, the table is typically deleted with the next profiling.

On Snowflake, if the operational database is not configured, a temporary table is used instead, so profiling speed remains intact. If the connection credentials can’t create temporary tables, the source is read without the cache. Legacy pushdown doesn’t use caching at all.

To optimize performance, some of the most infrequently used profiling statistics, such as frequency groups and quantiles for non-numeric attributes, aren’t calculated in pushdown.

Pushdown for DQ evaluation translates the rules applied to a catalog item into native SQL queries that run in your data source. You still need at least one Ataccama DPE: it is the only component that connects to your data source, so it generates the queries, runs them, and collects the results, while the data processing itself happens in the source.

Which queries run depends on the configuration, for example, whether invalid samples are collected or invalid records exported. The complexity of the queries depends on the DQ rules assigned to the catalog item.

DQ evaluation in pushdown requires an operational database, that is, a dedicated schema, dataset, or database in your data source. Without it, DQ jobs fail with the error Schema is not specified for lookup location. The setup is described in the instructions for each source (see Supported data sources).

If you use lookup data in the rules, Ataccama DPE uploads the lookups into the operational database and joins them with the processed catalog item to match the values. For small lookups (where the lookup key column is less than 100 kB after values have been normalized), the lookup data is embedded directly into the SQL instead.

To avoid reading the source again for each step, DQ jobs that collect invalid record samples or export invalid records store intermediate results in the operational database. Azure Synapse doesn’t store intermediate results and always reads the source directly.

The following limitations apply to all supported data sources:

Source-specific limitations are described in the setup instructions for each data source (see Supported data sources).