Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2026-10-16
Best Practices for Subquery Column Performance in Data Sources

Best Practices for Subquery Column Performance in Data Sources

Subquery columns enable you to filter rows in a staging table based on conditions in a related table. Example: You want to check whether related records exist, or to apply Exists, Count, or Sum rules against those records.
Subquery columns are useful for deduplication and conditional filtering, but they can significantly slow data source queries and downstream loaders. In complex integrations, a single subquery column can delay an entire integration task.
When performance matters:
  • Prefer join tables over subquery columns.
  • Filter data early.
  • Prepare data in the source system before you use subquery columns.
  • Keep join tables simple.
  • Split complex integrations across stages.
  • Index staging tables when needed.
In
Design Integrations
, subquery columns are available in the
Data Components
 pane of your data source: expand
Custom Column
 and select
Subquery Column
.

When to Use Subquery Columns

Use subquery columns when:
  • You need an existence check or aggregate against a related table.
  • The same result can't be achieved with a join table and SQL column.
  • The driving and related tables are small and stable.
  • You've validated performance with realistic data volumes.
Avoid subquery columns when:
  • A join table can produce the same result.
  • The related table is large or growing over time.
  • The subquery references a SQL column, especially one built from concatenation or other expressions.
  • Multiple downstream loaders depend on the same complex staging table.

Performance Implications of Subquery Column Usage

Subquery columns often generate correlated SQL that runs repeatedly for each row in the staging table. Runtime increases when:
  • More rows match the subquery condition.
  • Related tables grow in size.
  • Subqueries depend on SQL columns that can't be indexed.
  • Downstream join tables build on a slow base table.
Investigate performance when:
  • The Preview Data process takes more than about 10 seconds for the first 100 rows.
  • A data loader spends a long time on Discover new mappings or Fetching data to import.
  • The
    Data Mapping
     tab is slow to load or times out.
  • A small expression change causes a large increase in runtime.
Preview filters affect only the preview window. A fast preview doesn't guarantee acceptable performance at full data volume.

Best Practices

Best Practice
Description
Prefer join tables over subquery columns.
Create a join table between the primary and all related tables.
  • Apply filters in the join expression or in the data import filter of the table settings.
  • If you need a derived value, add a SQL column on the join result.
Filter data early.
Filter data early to reduce row volume before joins or subqueries run:
  • Apply the data import filter in the table settings of your base tables.
  • Filter by date range, status, or business unit on the source table.
Don't use subquery columns to discard large volumes of data that you can exclude earlier.
Prepare data in the source system before you use subquery columns.
When the source data format changes, normalize that data in the warehouse, database, or ETL pipeline instead of compensating with complex SQL columns and subqueries in the data source. Example: Values change from 9 to 09.
Import prepared views or tables with join keys and deduplication already applied.
Keep join tables simple.
To keep join tables simple:
  • Join on native source columns.
  • Use simple equality conditions.
  • Keep join chains shallow.
  • Test each join table separately.
Avoid:
  • Joining on SQL columns or concatenated expressions.
  • OR conditions in join expressions.
  • Long chains of join-on-join tables.
  • Assuming downstream speed if an upstream table is slow.
Split complex integrations across stages.
For metadata-heavy pipelines, split work across stages:
  • Import raw source tables.
  • Join and deduplicate in join tables.
  • Load to Planning through focused loaders.
Avoid one primary staging table that combines many joins, SQL columns, and subquery columns.
Index staging tables when needed.
Design Integrations doesn't automatically index columns used in subquery columns and join tables. Add indexes to base staging tables to improve performance when you use:
  • Subquery join keys on related tables.
  • Columns used in join or filter expressions.
  • Large base tables where the Preview Data process is slow and keys are simple column references.

Test Before Production

Validate these items in your implementation environment before you promote to production:
  • The Preview Data process completes in under about 10 seconds on each staging table used by the loader.
  • Preview Loader Output with
    Bypass Data Import
     enabled completes within the expected window.
  • Full loader and integration task runs succeed with production-scale data.
  • Row counts in staging tables are documented and monitored.
  • All loaders that share a data source are tested together.