> ## Documentation Index
> Fetch the complete documentation index at: https://docs.anomalyarmor.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Referential Checks API

> Verify foreign key relationships and detect orphaned records

<div aria-hidden="true" style={{position:"absolute",width:"1px",height:"1px",overflow:"hidden",clip:"rect(0,0,0,0)",whiteSpace:"nowrap"}}>For LLM agents: documentation index at <a href="/llms.txt" tabIndex={-1}>/llms.txt</a>, full text at <a href="/llms-full.txt" tabIndex={-1}>/llms-full.txt</a>. Append .md to any page URL for plain markdown.</div>
The Referential Checks API enables monitoring of foreign key relationships between tables. Use it to detect orphaned records, validate referential integrity, and integrate relationship checks into your data quality workflows.

## Endpoints

| Method | Endpoint                                                | Description                          |
| ------ | ------------------------------------------------------- | ------------------------------------ |
| GET    | `/api/v1/sdk/referential/{asset_id}/summary`            | Get referential summary for an asset |
| GET    | `/api/v1/sdk/referential/{asset_id}`                    | List referential checks for an asset |
| GET    | `/api/v1/sdk/referential/{asset_id}/{check_id}`         | Get referential check details        |
| POST   | `/api/v1/sdk/referential/{asset_id}`                    | Create a new referential check       |
| PATCH  | `/api/v1/sdk/referential/{asset_id}/{check_id}`         | Update a referential check           |
| DELETE | `/api/v1/sdk/referential/{asset_id}/{check_id}`         | Delete a referential check           |
| POST   | `/api/v1/sdk/referential/{asset_id}/{check_id}/execute` | Execute referential check            |
| GET    | `/api/v1/sdk/referential/{asset_id}/{check_id}/results` | List check results                   |

## Concepts

A referential integrity check validates that values in a **child column** (foreign key) exist in a **parent column** (primary key).

* **Child table**: The table containing the foreign key (e.g., `orders.customer_id`)
* **Parent table**: The table being referenced (e.g., `customers.id`)
* **Orphan**: A record in the child table with no matching parent record

## Get Referential Summary

```
GET /api/v1/sdk/referential/{asset_id}/summary
```

Returns aggregate referential integrity statistics for an asset.

<CodeGroup>
  ```bash cURL theme={null}
  curl -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/summary"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  summary = client.referential.summary("550e8400-e29b-41d4-a716-446655440000")
  print(f"Total checks: {summary.total_checks}")
  print(f"Passing: {summary.passing_checks}")
  print(f"Failing: {summary.failing_checks}")
  ```

  ```bash CLI theme={null}
  armor referential summary 550e8400-e29b-41d4-a716-446655440000
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "total_checks": 8,
    "active_checks": 7,
    "passing_checks": 6,
    "failing_checks": 1,
    "last_check_at": "2024-12-04T10:30:00Z"
  }
}
```

## List Referential Checks

```
GET /api/v1/sdk/referential/{asset_id}
```

### Query Parameters

| Parameter   | Type    | Default | Description             |
| ----------- | ------- | ------- | ----------------------- |
| `is_active` | boolean | -       | Filter by active status |
| `limit`     | integer | 50      | Max results (max: 100)  |
| `offset`    | integer | 0       | Results to skip         |

<CodeGroup>
  ```bash cURL theme={null}
  curl -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000?is_active=true"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()

  # List all checks
  checks = client.referential.list("550e8400-e29b-41d4-a716-446655440000")

  # Filter active only
  active_checks = client.referential.list(
      "550e8400-e29b-41d4-a716-446655440000",
      is_active=True,
  )
  for c in active_checks:
      print(f"{c.child_table_path}.{c.child_column_name} -> {c.parent_table_path}.{c.parent_column_name}")
  ```

  ```bash CLI theme={null}
  armor referential list 550e8400-e29b-41d4-a716-446655440000 --active
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "items": [
      {
        "id": "r_550e8400-e29b-41d4-a716-446655440001",
        "internal_id": 123,
        "asset_id": 456,
        "child_table_path": "snowflake.prod.warehouse.orders",
        "child_column_name": "customer_id",
        "parent_table_path": "snowflake.prod.warehouse.customers",
        "parent_column_name": "id",
        "name": "Orders -> Customers FK",
        "capture_interval": "daily",
        "is_active": true,
        "created_at": "2024-12-01T10:00:00Z"
      }
    ]
  },
  "pagination": {
    "total": 8,
    "limit": 50,
    "offset": 0,
    "has_more": false
  }
}
```

## Get Referential Check Details

```
GET /api/v1/sdk/referential/{asset_id}/{check_id}
```

<CodeGroup>
  ```bash cURL theme={null}
  curl -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/r_550e8400-e29b-41d4-a716-446655440001"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  check = client.referential.get(
      "550e8400-e29b-41d4-a716-446655440000",
      "r_550e8400-e29b-41d4-a716-446655440001",
  )
  print(f"Check: {check.name}")
  print(f"FK: {check.child_table_path}.{check.child_column_name}")
  print(f"PK: {check.parent_table_path}.{check.parent_column_name}")
  ```

  ```bash CLI theme={null}
  armor referential get 550e8400-e29b-41d4-a716-446655440000 \
    r_550e8400-e29b-41d4-a716-446655440001
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "id": "r_550e8400-e29b-41d4-a716-446655440001",
    "internal_id": 123,
    "asset_id": 456,
    "child_table_path": "snowflake.prod.warehouse.orders",
    "child_column_name": "customer_id",
    "parent_table_path": "snowflake.prod.warehouse.customers",
    "parent_column_name": "id",
    "name": "Orders -> Customers FK",
    "description": "Validates orders reference existing customers",
    "capture_interval": "daily",
    "max_orphan_count": 0,
    "max_orphan_percent": 0.1,
    "min_child_count": null,
    "max_child_count": null,
    "is_active": true
  }
}
```

## Create Referential Check

```
POST /api/v1/sdk/referential/{asset_id}
```

<Note>
  Requires `read-write` or `admin` scope.
</Note>

### Request Body

| Field                | Type    | Required | Description                                    |
| -------------------- | ------- | -------- | ---------------------------------------------- |
| `child_table_path`   | string  | Yes      | Full path to child table (with FK column)      |
| `child_column_name`  | string  | Yes      | Column name in child table (FK)                |
| `parent_table_path`  | string  | Yes      | Full path to parent table (with PK column)     |
| `parent_column_name` | string  | Yes      | Column name in parent table (PK)               |
| `name`               | string  | No       | Human-readable check name                      |
| `description`        | string  | No       | Check description                              |
| `capture_interval`   | string  | No       | `hourly`, `daily`, `weekly` (default: `daily`) |
| `max_orphan_count`   | integer | No       | Alert if orphan count exceeds this             |
| `max_orphan_percent` | float   | No       | Alert if orphan % exceeds this                 |
| `min_child_count`    | integer | No       | Min expected children per parent               |
| `max_child_count`    | integer | No       | Max expected children per parent               |

<CodeGroup>
  ```bash cURL theme={null}
  curl -X POST -H "Authorization: Bearer aa_live_xxx" \
    -H "Content-Type: application/json" \
    -d '{
      "child_table_path": "snowflake.prod.warehouse.orders",
      "child_column_name": "customer_id",
      "parent_table_path": "snowflake.prod.warehouse.customers",
      "parent_column_name": "id",
      "name": "Orders -> Customers FK",
      "max_orphan_count": 0
    }' \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  asset_id = "550e8400-e29b-41d4-a716-446655440000"

  # Create basic FK check
  check = client.referential.create(
      asset_id,
      child_table_path="snowflake.prod.warehouse.orders",
      child_column_name="customer_id",
      parent_table_path="snowflake.prod.warehouse.customers",
      parent_column_name="id",
      name="Orders -> Customers FK",
      max_orphan_count=0,  # Fail if any orphans
  )
  print(f"Created check: {check.id}")

  # Create check with cardinality constraints
  payments_check = client.referential.create(
      asset_id,
      child_table_path="snowflake.prod.warehouse.payments",
      child_column_name="order_id",
      parent_table_path="snowflake.prod.warehouse.orders",
      parent_column_name="id",
      name="Payments -> Orders FK",
      max_orphan_percent=0.1,  # Allow up to 0.1% orphans
      min_child_count=1,  # Each order should have at least 1 payment
      max_child_count=10,  # Each order should have at most 10 payments
  )
  ```

  ```bash CLI theme={null}
  armor referential create 550e8400-e29b-41d4-a716-446655440000 \
    --child-table snowflake.prod.warehouse.orders \
    --child-column customer_id \
    --parent-table snowflake.prod.warehouse.customers \
    --parent-column id \
    --name "Orders -> Customers FK"
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "id": "r_550e8400-e29b-41d4-a716-446655440002",
    "internal_id": 124,
    "asset_id": 456,
    "child_table_path": "snowflake.prod.warehouse.orders",
    "child_column_name": "customer_id",
    "parent_table_path": "snowflake.prod.warehouse.customers",
    "parent_column_name": "id",
    "name": "Orders -> Customers FK",
    "capture_interval": "daily",
    "max_orphan_count": 0,
    "is_active": true,
    "created_at": "2024-12-04T10:30:00Z"
  }
}
```

## Update Referential Check

```
PATCH /api/v1/sdk/referential/{asset_id}/{check_id}
```

<Note>
  Requires `read-write` or `admin` scope.
</Note>

### Request Body

| Field                | Type    | Description                  |
| -------------------- | ------- | ---------------------------- |
| `is_active`          | boolean | Whether check is active      |
| `name`               | string  | Check name                   |
| `description`        | string  | Check description            |
| `capture_interval`   | string  | Capture interval             |
| `max_orphan_count`   | integer | Max orphan count threshold   |
| `max_orphan_percent` | float   | Max orphan percent threshold |
| `min_child_count`    | integer | Min child count cardinality  |
| `max_child_count`    | integer | Max child count cardinality  |

<CodeGroup>
  ```bash cURL theme={null}
  curl -X PATCH -H "Authorization: Bearer aa_live_xxx" \
    -H "Content-Type: application/json" \
    -d '{"max_orphan_percent": 0.5, "capture_interval": "hourly"}' \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/r_550e8400-e29b-41d4-a716-446655440001"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  check = client.referential.update(
      "550e8400-e29b-41d4-a716-446655440000",
      "r_550e8400-e29b-41d4-a716-446655440001",
      max_orphan_percent=0.5,
      capture_interval="hourly",
  )
  ```
</CodeGroup>

## Delete Referential Check

```
DELETE /api/v1/sdk/referential/{asset_id}/{check_id}
```

<Note>
  Requires `read-write` or `admin` scope.
</Note>

<CodeGroup>
  ```bash cURL theme={null}
  curl -X DELETE -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/r_550e8400-e29b-41d4-a716-446655440001"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  client.referential.delete(
      "550e8400-e29b-41d4-a716-446655440000",
      "r_550e8400-e29b-41d4-a716-446655440001",
  )
  ```

  ```bash CLI theme={null}
  armor referential delete 550e8400-e29b-41d4-a716-446655440000 \
    r_550e8400-e29b-41d4-a716-446655440001 --yes
  ```
</CodeGroup>

## Execute Referential Check

```
POST /api/v1/sdk/referential/{asset_id}/{check_id}/execute
```

<Note>
  Requires `read-write` or `admin` scope.
</Note>

Triggers an immediate execution of the referential integrity check.

<CodeGroup>
  ```bash cURL theme={null}
  curl -X POST -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/r_550e8400-e29b-41d4-a716-446655440001/execute"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  result = client.referential.execute(
      "550e8400-e29b-41d4-a716-446655440000",
      "r_550e8400-e29b-41d4-a716-446655440001",
  )

  if result.status == "fail":
      print(f"Found {result.orphan_count} orphaned records ({result.orphan_percent:.2f}%)")
      for orphan in (result.orphan_sample or [])[:5]:
          print(f"  Orphan: {orphan}")
  else:
      print("Referential integrity check passed!")
  ```

  ```bash CLI theme={null}
  armor referential execute 550e8400-e29b-41d4-a716-446655440000 \
    r_550e8400-e29b-41d4-a716-446655440001
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "id": 789,
    "referential_check_id": 123,
    "status": "fail",
    "orphan_count": 5,
    "orphan_percent": 0.05,
    "total_child_rows": 10000,
    "orphan_sample": [
      {"customer_id": 99999},
      {"customer_id": 99998}
    ],
    "parents_below_min": null,
    "parents_above_max": null,
    "query_duration_ms": 2500,
    "created_at": "2024-12-04T10:35:00Z"
  }
}
```

## List Check Results

```
GET /api/v1/sdk/referential/{asset_id}/{check_id}/results
```

### Query Parameters

| Parameter | Type    | Default | Description     |
| --------- | ------- | ------- | --------------- |
| `limit`   | integer | 100     | Max results     |
| `offset`  | integer | 0       | Results to skip |

<CodeGroup>
  ```bash cURL theme={null}
  curl -H "Authorization: Bearer aa_live_xxx" \
    "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/r_550e8400-e29b-41d4-a716-446655440001/results?limit=30"
  ```

  ```python Python SDK theme={null}
  from anomalyarmor import Client

  client = Client()
  results = client.referential.results(
      "550e8400-e29b-41d4-a716-446655440000",
      "r_550e8400-e29b-41d4-a716-446655440001",
      limit=30,
  )
  for r in results:
      print(f"{r.created_at}: {r.status} ({r.orphan_count} orphans)")
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": {
    "items": [
      {
        "id": 789,
        "referential_check_id": 123,
        "status": "fail",
        "orphan_count": 5,
        "orphan_percent": 0.05,
        "total_child_rows": 10000,
        "query_duration_ms": 2500,
        "created_at": "2024-12-04T10:35:00Z"
      },
      {
        "id": 788,
        "referential_check_id": 123,
        "status": "pass",
        "orphan_count": 0,
        "orphan_percent": 0.0,
        "total_child_rows": 9995,
        "query_duration_ms": 2300,
        "created_at": "2024-12-03T10:35:00Z"
      }
    ]
  },
  "pagination": {
    "total": 60,
    "limit": 30,
    "offset": 0,
    "has_more": true
  }
}
```

## Use Case: Detect Orphaned Orders

Monitor orders that reference non-existent customers:

```python theme={null}
from anomalyarmor import Client

client = Client()
asset_id = "550e8400-e29b-41d4-a716-446655440000"

# Create a referential check
check = client.referential.create(
    asset_id,
    child_table_path="snowflake.prod.warehouse.orders",
    child_column_name="customer_id",
    parent_table_path="snowflake.prod.warehouse.customers",
    parent_column_name="id",
    name="Orders -> Customers FK",
    description="Ensures all orders reference valid customers",
    max_orphan_count=0,  # Zero tolerance for orphans
)

# Execute check immediately
result = client.referential.execute(asset_id, check.id)

if result.status == "fail":
    print(f"Data quality issue: {result.orphan_count} orphaned orders found")
    print(f"Orphan rate: {result.orphan_percent:.3f}%")

    # Log sample orphans for investigation
    for orphan in (result.orphan_sample or []):
        print(f"  Orphaned customer_id: {orphan}")

    # This could trigger an alert or fail a pipeline
    raise Exception("Referential integrity violation detected")
else:
    print("All orders reference valid customers!")
```

## Use Case: Cardinality Validation

Ensure each order has a reasonable number of line items:

```python theme={null}
from anomalyarmor import Client

client = Client()
asset_id = "550e8400-e29b-41d4-a716-446655440000"

# Create check with cardinality constraints
check = client.referential.create(
    asset_id,
    child_table_path="snowflake.prod.warehouse.order_items",
    child_column_name="order_id",
    parent_table_path="snowflake.prod.warehouse.orders",
    parent_column_name="id",
    name="Order Items -> Orders FK",
    min_child_count=1,   # Each order must have at least 1 item
    max_child_count=100, # No order should have more than 100 items
)

result = client.referential.execute(asset_id, check.id)

if result.parents_below_min is not None and result.parents_below_min > 0:
    print(f"Found {result.parents_below_min} orders with no items!")

if result.parents_above_max is not None and result.parents_above_max > 0:
    print(f"Found {result.parents_above_max} orders with too many items!")
```

## Error Responses

### Check Not Found (404)

```json theme={null}
{
  "error": {
    "code": "CHECK_NOT_FOUND",
    "message": "Referential check not found",
    "details": {"check_id": "r_invalid-uuid"}
  }
}
```

### Invalid Table Path (400)

```json theme={null}
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Table not found: snowflake.prod.warehouse.invalid_table",
    "details": {"field": "child_table_path"}
  }
}
```

### Check Inactive (400)

```json theme={null}
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Cannot execute inactive check",
    "details": {"check_id": "r_550e8400-e29b-41d4-a716-446655440001"}
  }
}
```

### Forbidden (403)

```json theme={null}
{
  "error": {
    "code": "FORBIDDEN",
    "message": "Insufficient permissions. Required scope: read-write",
    "details": {"current_scope": "read-only", "required_scope": "read-write"}
  }
}
```

## Common Questions

### Do my warehouse tables need declared foreign keys for this to work?

No. Referential checks work against any two columns you name, regardless of whether the underlying warehouse enforces or even supports FK constraints. Snowflake and BigQuery, for instance, don't enforce FKs, which is exactly why this API exists. Just provide the child and parent `table_path` and column names.

### When should I use max\_orphan\_count vs max\_orphan\_percent?

Use `max_orphan_count=0` for hard-invariant relationships (every order must reference a real customer). Use `max_orphan_percent` when some drift is acceptable (e.g. late-arriving dimension rows) and you care about the rate, not the absolute count. You can set both; either threshold breach marks the check as failed.

### How do min\_child\_count and max\_child\_count work?

These are cardinality constraints on the parent->children relationship. `min_child_count=1` flags parents with zero children (e.g. orders with no line items), and `max_child_count=100` flags parents with too many. The result returns `parents_below_min` and `parents_above_max` counts separate from the orphan count.

### How expensive are referential checks on large tables?

The query joins child and parent tables, so cost scales with the warehouse's join performance on the key columns. `query_duration_ms` is returned on every result for observability. For very large tables, prefer `daily` or `weekly` `capture_interval` and rely on `execute` for on-demand checks after loads rather than running them hourly.