# Build a computed join

Source: https://userguide.qualytics.io/cli/examples/build-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](https://userguide.qualytics.io/cli/examples/build-computed-join/#sql-mode-composite-keys-more-than-two-sources) 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

```mermaid
graph LR
    L[(Left container: orders)] --> J[Computed join]
    R[(Right container: customers)] --> J
    J --> Profile[Profile]
    Profile --> Checks[Add quality checks]
```

### 1. Validate the join shape (optional but recommended)

```bash
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

```bash
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

```bash
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":

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

```bash
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

```python
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

```bash
--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:

```python
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](https://userguide.qualytics.io/container/computed-join/getting-started/) 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. |

## Related

- [Containers command reference](https://userguide.qualytics.io/cli/containers/)
- [Bulk import computed tables from CSV](https://userguide.qualytics.io/cli/examples/bulk-import-computed-tables/): for SQL-defined containers (not joins).
- [Quality Checks command reference](https://userguide.qualytics.io/cli/quality-checks/): write checks against the joined view.

*Last updated: July 24, 2026.*