Skip to content

Bulk import computed tables from CSV

Goal

Register many computed tables (SQL-defined virtual tables) at once from a CSV or XLSX file. Common scenario: the customer has a library of dozens of metric definitions, business rules, or anomaly-detection queries that they currently run as ad-hoc SQL, and they want each of them registered as a Qualytics computed table with an associated quality check.

Permissions

Step Endpoint Role Team permission
Create computed container POST /api/containers Member Editor on the datastore's team
Create satisfiesExpression check (per row) POST /api/quality-checks Member Author
Profile the new container POST /api/operations/run (profile) Member Editor

Profile must complete before checks become useful

The CLI waits for the post-import profile to finish by default. If you skip the wait with --skip-profile-wait, the checks are still created but will fail until profiling completes.

Prerequisites

  • The CLI is installed and authenticated.
  • A datastore exists where the computed tables will live.
  • A CSV, XLSX, or TXT file with three columns: name, description, query. Header row required.
name,description,query
high_value_orders,Orders over $10k,SELECT * FROM orders WHERE total > 10000
recent_signups,Customers from the last 30 days,SELECT * FROM customers WHERE created_at > NOW() - INTERVAL '30 days'
churned_customers,Customers inactive for 90+ days,SELECT * FROM customers WHERE last_seen < NOW() - INTERVAL '90 days'

CLI workflow

graph LR
    File[CSV / XLSX] --> Preview[containers preview]
    Preview --> Import[containers import]
    Import --> CT[(Computed tables)]
    Import --> Profile[Auto profile]
    Profile --> Checks[(satisfiesExpression checks)]

1. Preview without writing

qualytics containers preview --input ./computed_tables.csv

The CLI parses the file, applies the prefix, normalizes the queries, and shows what it would create, without making any API calls.

2. Import

qualytics containers import \
    --datastore 42 \
    --input ./computed_tables.csv \
    --prefix "metric_" \
    --as-active \
    --tags "metrics,dashboard"

Each row becomes: - A computed table named metric_<name> in datastore 42. - A satisfiesExpression check that fails when the query returns any rows (so query is meant to express "rows that should not exist").

3. (Optional) Skip checks

If you want the computed tables but not the auto-generated checks:

qualytics containers import --datastore 42 --input ./computed_tables.csv --skip-checks

Behind the scenes

CLI step Method Path Notes
Parse the file (local) — Validates header row, applies prefix, adds aliases to SELECT columns missing them.
Skip existing GET /api/containers?datastore_id={id} The CLI lists existing containers and skips rows whose name (after prefix) already exists.
Create computed table POST /api/containers One per new row. Type is computed_table.
Trigger profile POST /api/operations/run (profile) One profile call covering the new containers.
Wait for profile GET /api/operations/{id} Polls until the profile finishes (unless --skip-profile-wait).
Create check (per row) POST /api/quality-checks One satisfiesExpression check per new computed table.

Python equivalent

import csv
import os
import time
import httpx

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

with httpx.Client(headers=HEADERS, timeout=60.0) as client:
    # Read the CSV
    with open("./computed_tables.csv") as f:
        reader = csv.DictReader(f)
        rows = list(reader)

    # Create computed tables
    created_ids = []
    for row in rows:
        body = {
            "type":         "computed_table",
            "name":         f"{PREFIX}{row['name']}",
            "datastore_id": DATASTORE_ID,
            "query":        row["query"],
            "description":  row.get("description"),
            "tags":         ["metrics", "dashboard"],
        }
        r = client.post(f"{BASE_URL}/api/containers", json=body)
        r.raise_for_status()
        created_ids.append(r.json()["id"])

    # Profile the new containers and wait
    r = client.post(f"{BASE_URL}/api/operations/run", json={
        "type":          "profile",
        "datastore_ids": [DATASTORE_ID],
        "container_ids": created_ids,
    })
    r.raise_for_status()
    op_id = r.json()["id"]
    while True:
        op = client.get(f"{BASE_URL}/api/operations/{op_id}").json()
        if op["result"] in ("success", "failure", "aborted"):
            break
        time.sleep(10)

    # Create one satisfiesExpression check per container
    for row, cid in zip(rows, created_ids):
        client.post(f"{BASE_URL}/api/quality-checks", json={
            "container_id": cid,
            "rule_type":    "satisfiesExpression",
            "fields":       [],
            "properties":   {"expression": "1=0"},  # fail if any rows present
            "description":  row.get("description"),
            "status":       "Active",
        }).raise_for_status()

Variations and advanced usage

Use TXT or XLSX

qualytics containers import --datastore 42 --input ./computed_tables.tsv --delimiter $'\t'
qualytics containers import --datastore 42 --input ./computed_tables.xlsx

Create as drafts, review later

qualytics containers import --datastore 42 --input ./computed_tables.csv --as-draft
# After review:
qualytics checks list --datastore-id 42 --status Draft
qualytics checks activate --ids "501,502,503"

Debug logs for stuck imports

qualytics containers import --datastore 42 --input ./computed_tables.csv --debug
# Logs land in ~/.qualytics/logs/

Re-run safely

The CLI skips computed tables whose name (after prefix) already exists. Re-running on the same CSV won't duplicate.

Troubleshooting

Symptom Likely cause Fix
columns missing aliases warning The SELECT has unnamed expressions The CLI adds expr_1, expr_2, ... aliases automatically; the warning is informational.
Profile never finishes Source database is slow or query is heavy Run with --skip-profile-wait and poll qualytics operations list separately.
Some checks fail to create The corresponding computed table was skipped (already existed), so the check check would be a duplicate Check the import summary; the CLI logs what was skipped.
Wrong delimiter detected TXT files default to \t; CSV defaults to , Pass --delimiter explicitly.
Header row is missing or in wrong position The CLI assumes first row is a header Add the header row (name,description,query).