Skip to content

Build a computed join

Goal

Join two existing base containers (tables, views, or files) into a new virtual container, without writing the join SQL by hand. Computed joins are how you analyze cross-table relationships in Qualytics: for example, joining orders with customers so a quality check can validate that every order has a non-null customer email.

The CLI creates pairwise joins: exactly two containers on a single key per side. Each side can be a table, view, file, computed table, or computed file. For composite keys or more than two sources, use SQL mode via the UI or API.

Permissions

Step Endpoint Role Team permission
Validate the join shape POST /api/containers/validate Member N/A
Create the join POST /api/containers Member Editor on the datastore's team
Profile the new join POST /api/operations/run (profile) Member Editor

Prerequisites

  • The CLI is installed and authenticated.
  • Both source containers exist in the same datastore.
  • You know the join keys (left field and right field) and the desired join type (inner, left, right, full).

CLI workflow

graph LR
    L[(Left container: orders)] --> J[Computed join]
    R[(Right container: customers)] --> J
    J --> Profile[Profile]
    Profile --> Checks[Add quality checks]
qualytics containers validate \
    --type computed_join \
    --datastore-id 42 \
    --left-container-id 100 \
    --right-container-id 105 \
    --left-key-field "customer_id" \
    --right-key-field "id" \
    --select-clause "ord_id, ord_total, cust_email, cust_name" \
    --left-prefix "ord_" \
    --right-prefix "cust_" \
    --join-type inner

A successful validation confirms the field references resolve and the SQL is parseable.

2. Create the join

qualytics containers create \
    --type computed_join \
    --name "orders_with_customers" \
    --datastore-id 42 \
    --left-container-id 100 \
    --right-container-id 105 \
    --left-key-field "customer_id" \
    --right-key-field "id" \
    --left-prefix "ord_" \
    --right-prefix "cust_" \
    --join-type inner \
    --select-clause "ord_id, ord_total, cust_email, cust_name" \
    --description "Orders joined with their customer records" \
    --tags "joins,orders"

3. Profile the join

qualytics operations profile --datastore-id 42 --container-names "orders_with_customers"

4. Add a quality check on the joined view

For example, "every order must have a non-null customer email":

# checks/orders_with_customers/email_not_null.yaml
rule_type: isNotNull
container: orders_with_customers
fields:
  - cust_email
status: Active

qualytics checks create --datastore-id 42 --file ./checks/orders_with_customers/email_not_null.yaml

Behind the scenes

CLI step Method Path Notes
containers validate POST /api/containers/validate The create payload is sent nested under a container key; the configuration is validated without being persisted.
containers create POST /api/containers The CLI sends the join definition; the create endpoint ignores description and tags and applies them in a follow-up PUT.
containers create (apply description/tags) PUT /api/containers/{container_id} Sets description and tags after creation.
operations profile POST /api/operations/run (profile) Standard async operation.

Python equivalent

import os
import time
import httpx

BASE_URL = os.environ["QUALYTICS_URL"].rstrip("/")
TOKEN    = os.environ["QUALYTICS_TOKEN"]
HEADERS  = {"Authorization": f"Bearer {TOKEN}"}

with httpx.Client(headers=HEADERS, timeout=60.0) as client:
    body = {
        "container_type":        "computed_join",
        "name":                  "orders_with_customers",
        "left_container_id":     100,
        "right_container_id":    105,
        "left_join_field_name":  "customer_id",
        "right_join_field_name": "id",
        "left_prefix":           "ord_",
        "right_prefix":          "cust_",
        "join_type":             "inner",
        "select_clause":         "ord_id, ord_total, cust_email, cust_name",
    }
    # Validate first (the create payload nests under a "container" key here)
    client.post(f"{BASE_URL}/api/containers/validate", json={"container": body}).raise_for_status()
    # Create
    r = client.post(f"{BASE_URL}/api/containers", json=body)
    r.raise_for_status()
    container_id = r.json()["id"]

    # Apply description and tags
    client.put(f"{BASE_URL}/api/containers/{container_id}", json={
        "description": "Orders joined with their customer records",
        "tags":        ["joins", "orders"],
    }).raise_for_status()

    # Profile
    op = client.post(f"{BASE_URL}/api/operations/run", json={
        "type":          "profile",
        "datastore_ids": [42],
        "container_ids": [container_id],
    }).json()
    while True:
        s = client.get(f"{BASE_URL}/api/operations/{op['id']}").json()
        if s["result"] in ("success", "failure", "aborted"):
            print(f"profile: {s['result']}")
            break
        time.sleep(10)

Variations and advanced usage

Outer joins

--join-type left   # all rows from left, matching from right (or NULL)
--join-type right
--join-type full

Useful when you want to find rows in one container that have no match in the other (orphans).

SQL mode: composite keys, more than two sources

The CLI flags create pairwise joins: exactly two containers, one key per side. For a composite key or more than two sources, create the join in SQL mode instead: declare two or more aliased sources (they may span datastores, and each can be a table, view, file, computed table, or computed file, as long as each has been profiled) and write a Spark SQL query that references the aliases as tables. A composite key becomes a natural join condition rather than a workaround:

body = {
    "container_type": "computed_join",
    "name": "orders_with_revisions",
    "sources": [
        {"container_id": 100, "alias": "orders"},
        {"container_id": 105, "alias": "order_revisions",
         "where_clause": "status = 'active'"},
    ],
    "query": (
        "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"
    ),
}
client.post(f"{BASE_URL}/api/containers/validate", json={"container": body}).raise_for_status()
r = client.post(f"{BASE_URL}/api/containers", json=body)
r.raise_for_status()
container_id = r.json()["id"]

# Optional: apply description and tags before the profile.
client.put(f"{BASE_URL}/api/containers/{container_id}", json={
    "description": "Orders joined with their active revisions",
    "tags":        ["joins", "orders"],
}).raise_for_status()

# Profile. A SQL-mode join lives in its first source's datastore,
# so pass that one datastore ID (not every source datastore) to /operations/run.
op = client.post(f"{BASE_URL}/api/operations/run", json={
    "type":          "profile",
    "datastore_ids": [42],
    "container_ids": [container_id],
}).json()
while True:
    s = client.get(f"{BASE_URL}/api/operations/{op['id']}").json()
    if s["result"] in ("success", "failure", "aborted"):
        print(f"profile: {s['result']}")
        break
    time.sleep(10)

The datastore ID passed to /api/operations/run is always the parent datastore's, which for SQL mode is the first source's datastore. Passing a non-parent datastore ID (for example, the datastore of a later source) returns 400 Bad Request.

Each source's optional where_clause is applied while that source is read, which is the main lever for bounding how much data a large source loads. SQL mode is available through the UI and the API; the CLI does not expose it yet. See Computed Join for the full SQL mode reference, including query restrictions and limits.

Troubleshooting

Symptom Likely cause Fix
validate fails with Field not found Field name in --select-clause doesn't exist on either side Use qualytics containers get --id 100 --profiles to confirm field names.
Profile completes but the join has zero rows Inner join misses (no matching keys) Try --join-type left to see how many left rows have no match.
Materialize fails after the join was created Materialization needs the source database to support the SQL Qualytics emits Some warehouses (e.g., older Hive) restrict join syntax; check with materialize against a smaller container first.
Description and tags don't appear in the response The create endpoint ignores those fields; the CLI applies them via a follow-up PUT Confirm the PUT call ran (check verbose output); on Python, send the PUT explicitly.