Skip to content

Computed Join Best Practices

Recommendations for configuring Computed Joins that perform well and produce expected results.

Join Key

  • One field per side (pairwise). In pairwise mode you pick a single field on each side. For composite (multi-column) keys, use SQL mode and write the full ON condition, or concatenate the parts on each input as a Computed Field first.
  • Types must match exactly. The validator rejects pairs whose data types differ. See Mismatched join field types.
  • Null keys are dropped. Rows with a null key on either side are excluded regardless of join type, which differs from standard SQL for Left, Right, and Outer joins. If you need to preserve unmatched null-keyed rows, materialize each side as a Computed Table or Computed File that replaces null keys with a placeholder. See How do null keys behave?.

Prefixes

Always set distinct prefixes for the left and right sides (the form defaults to left and right). Without prefixes, columns with the same name on both sides would collide in the result. Changing a prefix later rewrites every matching column reference in the Select Expression automatically.

Select Expression

Project only the columns you actually need. The Select Expression auto-fills with every top-level field from both sides; for wide containers, this can produce a result with hundreds of columns. Trim the Select Expression to the columns you'll use downstream.

The validator rejects DISTINCT in any form: the bare keyword, DISTINCT(col), COUNT(DISTINCT col), SUM(DISTINCT col), APPROX_COUNT_DISTINCT(col), and multi-column variants. To deduplicate results, replace DISTINCT col with explode(collect_set(col)). Add a GROUP BY only if the Select Expression also returns non-aggregated columns alongside the deduplicated one. See FAQ · Why isn't DISTINCT allowed in the Select Expression? for context.

Narrow Each Side Before Joining

In pairwise mode, the join's Filter Clause runs after both sides are loaded and joined. To narrow what each side loads, materialize a filtered Computed Table or Computed File and use it as the input. In SQL mode, use each source's Filter Clause instead, which is applied while that source is read. This is especially valuable for:

  • Cross-datastore joins, where each side comes from a different datastore.
  • Large containers, where loading the full container would exceed the deployment's capacity.

Cross-Datastore Joins

The inputs can live in different datastores. The result lives in the parent datastore (the left container's datastore in pairwise mode, or the first source's datastore in SQL mode), and you need at least Viewer permission on every other datastore an input reads from.

When working across datastores, identify the slower-loading input (larger container or higher-latency datastore) and apply the most aggressive pre-filter there by materializing it as a Computed Table or Computed File, or by adding a per-source Filter Clause in SQL mode. See How Computed Join Works · Cross-Datastore Joins for the execution model.

Container Capacity

Qualytics loads each input of the join in a single pass before applying the join. Very large or very wide containers can exceed the deployment's capacity. If you see a deployment is not large enough message, narrow each input (materialize a filtered Computed Table or Computed File, or add a per-source Filter Clause in SQL mode), reduce the columns you project, or request more capacity from your administrator. See Input container too large for one load pass for the full troubleshooting entry.

Chaining Joins

A pairwise join takes exactly two inputs; a SQL-mode join takes two or more (up to a configurable maximum, 10 by default). To combine more sources than a single join allows, express part of the logic inside a Computed Table whose SQL contains the join, then use that Computed Table as an input to the next Computed Join. See Supported Inputs · Working with Derived Datasets for the worked example.

SQL Mode Tips

  • Filter at the source. Each source's Filter Clause is applied while that source is read, so it bounds how much data loads before the query runs. This is the main lever for large or cross-datastore sources; the query's own WHERE runs only after every source is loaded.
  • Keep aliases short and stable. Aliases must be valid identifiers and unique within the join, and they cannot clash with a CTE name in the query. Renaming an alias means updating every reference in the query.
  • Project only what you need. Select just the columns you will use downstream; wide sources otherwise carry hundreds of columns into the result.
  • DISTINCT is allowed. Unlike the pairwise Select Expression, SQL mode accepts DISTINCT because the whole query is valid Spark SQL.

When to Reach for a Computed Join

  • Computed Field on the source container is cheaper than a Computed Join when the transformation is single-input (cast a column, derive a value).
  • Computed Table fits when the transformation is single-source SQL that benefits from being a persistent definition.
  • Computed Join is the right choice when the join itself is the transformation you want to govern, profile, and run quality checks against.

Naming and Ownership

  • Use a name that describes the joined output, not the source containers (for example, active_customers_with_recent_orders).
  • Fill in the Description so quality-check authors and downstream consumers have context for the joined container.
  • Set an explicit Owner. Authors create joins they own automatically; Editors can reassign ownership to any user.
  • Apply Tags so the join surfaces in tag-scoped searches alongside related containers.