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. Pick a single field on each side. Composite keys aren't supported directly; 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

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. 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 left and right containers can live in different datastores. The result lives in the left container's datastore, and you need at least Viewer permission on the right datastore to create the join.

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

Container Capacity

Qualytics loads each side 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 side by materializing a filtered Computed Table or Computed File, reduce the columns in the Select Expression, or request more capacity from your administrator. See Input container too large for one load pass for the full troubleshooting entry.

Chaining Joins

A single Computed Join always takes exactly two inputs. To combine three or more sources, express the first join inside a Computed Table whose SQL contains the join logic, then use that Computed Table as one side of the next Computed Join. See Supported Inputs · Working with Derived Datasets for the worked example.

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.