Skip to content

How Computed Join Works

A Computed Join is configured through a form and executed by Qualytics. It has two modes: pairwise (a form that generates the join for you) and SQL mode (a Spark SQL query you write over aliased sources). Whichever mode you use, Qualytics loads each input, applies the join, and returns the result.

The Pairwise Form

In pairwise mode, the Add Computed Join form's join-related fields are:

REF. Field Description
1 Name The unique name for your Computed Join container.
2 Join Type One of: Inner Join (keeps only matching rows from both sides), Left Join (all rows from the left container, matching rows from the right), Right Join (all rows from the right container, matching rows from the left), or Full Outer Join (all rows from both containers).
3 Left Reference The left container, including its parent Datastore, the Container itself, the Field to join on, and an optional Prefix (defaults to left) applied to all columns from this container.
4 Right Reference Mirrors Left Reference (Datastore, Container, Field, Prefix). The Prefix defaults to right, and the right container can live in a different datastore.
5 Select Expression A SQL select clause naming the columns to include in the result, with optional aliases and expressions. Columns are automatically prefixed (for example, left_name, right_name) to avoid name collisions. When you select both containers in create mode, the Select Expression auto-fills with every top-level field from both sides.
6 Filter Clause (WHERE) Optional filter applied to the joined result. Must be a valid SQL predicate.
7 Group By Clause (GROUP BY) Optional grouping when using aggregate functions (COUNT, SUM, AVG, MIN, MAX, and similar) in the Select Expression. All non-aggregated fields in the Select Expression must also appear in the Group By Clause.

Pairwise Execution Flow

Qualytics runs the join during profile and scan operations, and during the synchronous validation step when you click Validate or Save. On every run, Qualytics loads each side independently and computes the join:

  1. Load the left container from its source datastore.
  2. Load the right container from its source datastore.
  3. Compute the join using the chosen join type and the configured join fields.
  4. Apply the Select Expression to project the columns named in the clause.
  5. Apply the Filter Clause (post-join WHERE) to the projected result.
  6. Apply the Group By Clause if grouping is configured.
  7. Return the final result as the Computed Join's output.

Qualytics does not store the joined rows. Each profile or scan re-loads both sides and recomputes the joined result, so the output stays in sync with the underlying containers without a manual refresh step.

SQL Mode

SQL mode replaces the pairwise form with a set of aliased sources and one Spark SQL query over them.

The SQL Mode Form

Field Description
Name The unique name for your Computed Join container.
Sources Two or more input containers, each given an alias. For each source you pick its Datastore and Container, set a unique Alias, and optionally add a Filter Clause. The number of sources ranges from 2 up to a configurable maximum (10 by default).
Query A single Spark SQL query that references the aliases as if they were tables. This is where you express the join, including composite keys, CTEs, window functions, and aggregations.

Each Alias must start with a letter or underscore, contain only letters, digits, and underscores, be unique within the join, and not clash with a CTE name used in the query. Each source's optional Filter Clause is a Spark SQL WHERE expression applied while that source is read, so rows that do not match are never loaded. This is the main lever for keeping large sources manageable.

SQL Mode Execution Flow

  1. Load each source from its own datastore, applying that source's Filter Clause as it reads.
  2. Register each source under its alias so the query can reference it by name.
  3. Run the query as written, computing the join and any CTEs, aggregations, or window functions.
  4. Filter by the incremental field if an incremental identifier is configured (see Incremental Loading).
  5. Return the query result as the Computed Join's output.

The Query Language and Restrictions

The query is written in Spark SQL and references the declared aliases as tables. This differs from a Computed Table, whose query runs inside the source datastore in that datastore's own SQL dialect. A SQL-mode Computed Join always uses Spark SQL, regardless of which datastores its sources come from.

Composite (multi-column) join keys, which the pairwise form cannot express, are written directly in the ON condition:

SELECT o.order_id, o.total, r.status
FROM orders o
JOIN order_revisions r
    ON o.order_id = r.order_id
    AND o.revision = r.revision

CTEs, GROUP BY, window functions, and joins across any number of the declared aliases are supported. The following are rejected at validation:

  • Statements that modify data. Only read queries are accepted.
  • Qualified or catalog table references (for example, analytics.public.orders). Reference sources by their alias only.
  • Functions that execute arbitrary code, such as reflect and java_method.

Unlike the pairwise Select Expression, DISTINCT is allowed in SQL mode because the whole query is valid Spark SQL.

Validation and Fields

Click Validate to dry-run the query against the declared sources. Invalid SQL, unknown columns or aliases, and disallowed statements are reported inline. When you save, the join's fields and their types are taken from the validated query's schema and are available immediately; field statistics arrive after the next profile.

Cross-Datastore Joins

The inputs can live in different datastores (for example, a JDBC table joined with a DFS file). Qualytics loads each input from its own datastore and computes the join. In SQL mode the aliased sources may likewise span datastores. Throughput is bounded by the slowest input; to reduce what an input loads, materialize a filtered Computed Table or Computed File (pairwise), or narrow the source with its Filter Clause (SQL mode).

The Computed Join itself lives under its parent datastore: the left container's datastore in pairwise mode, or the first source's datastore in SQL mode. To read a container that lives in a different datastore, you need at least Viewer permission on that datastore.

Null Keys

In pairwise mode, the join drops rows where either side of the join key is null before matching, regardless of join type. A null on the left does not match a null on the right. This differs from standard SQL for Left, Right, and Outer joins, which would preserve unmatched rows. To preserve unmatched null-keyed rows, materialize each side as a Computed Table or Computed File that replaces null keys with a placeholder value. In SQL mode, null-key handling follows the JOIN ... ON condition you write, using standard Spark SQL semantics.

Output Behavior

The joined output behaves like a regular container in Qualytics:

  • It appears in its parent datastore's container list.
  • It has its own profile. A fresh profile runs on every save, with deeper re-profiling triggered when the fields produced by the join change. A profile also runs on the next scan or profile you trigger.
  • It supports observability features (volumetric tracking, freshness checks).
  • Quality checks can run against the joined result.

Computed Joins are not partitioned. Scans and observability work against the full joined result.

Incremental Loading

A Computed Join always reads its inputs in full on every run; the incremental setting does not prune what the inputs load. Instead, you configure an Incremental Strategy and Incremental Field on the joined output, and Qualytics filters the join result by that field so downstream scans process only rows newer than the last run. The incremental field must be a column the join produces: a column from the Select Expression in pairwise mode, or one of the query's output columns in SQL mode. This is available in both modes.

Sync and Computed Joins

Sync discovers containers from the source system. A Computed Join is produced by Qualytics rather than discovered, so Sync's Inaccessible, Unloadable, and Prune rules do not apply to it.

Field Changes on Inputs

When the join no longer produces a field that has active quality checks or anomalies (because the pairwise Select Expression, or the SQL-mode query, stopped projecting it), the next save or profile lists the dropped fields. Confirm Proceed Anyway in the UI (or set force_drop_fields=true in the API request) to apply the change. The dropped fields are marked missing, and their attached quality checks and anomalies are preserved until the fields reappear in a future edit. See Removed fields warning on Save for the message format and recovery steps.

Permissions

Creating, editing, or deleting a Computed Join requires:

  • Editor on the parent datastore, or Author on the parent datastore plus ownership of the Computed Join. The parent datastore is the left container's datastore in pairwise mode, or the first source's datastore in SQL mode.
  • At least Viewer on every other datastore an input reads from (the right datastore in pairwise mode, or each additional source's datastore in SQL mode).

Reading the joined output requires at least Reporter on the parent datastore. Only an Editor can reassign the owner to another user; Authors cannot transfer ownership.