Referential Checks API

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.

curl -H "Authorization: Bearer aa_live_xxx" \
  "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000/summary"

Response

{
  "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

curl -H "Authorization: Bearer aa_live_xxx" \
  "https://api.anomalyarmor.ai/api/v1/sdk/referential/550e8400-e29b-41d4-a716-446655440000?is_active=true"

Response

{
  "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}

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"

Response

{
  "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}

Requires read-write or admin scope.

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

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"

Response

{
  "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}

Requires read-write or admin scope.

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

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"

Delete Referential Check

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

Requires read-write or admin scope.

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"

Execute Referential Check

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

Requires read-write or admin scope.

Triggers an immediate execution of the referential integrity check.

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"

Response

{
  "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

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"

Response

{
  "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:

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:

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)

{
  "error": {
    "code": "CHECK_NOT_FOUND",
    "message": "Referential check not found",
    "details": {"check_id": "r_invalid-uuid"}
  }
}

Invalid Table Path (400)

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

Check Inactive (400)

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

Forbidden (403)

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

API Overview

API Reference

API Guides

Python SDK

CLI

Integrations

AI Agents

Endpoints

Concepts

Get Referential Summary

Response

List Referential Checks

Query Parameters

Response

Get Referential Check Details

Response

Create Referential Check

Request Body

Response

Update Referential Check

Request Body

Delete Referential Check

Execute Referential Check

Response

List Check Results

Query Parameters

Response

Use Case: Detect Orphaned Orders

Use Case: Cardinality Validation

Error Responses

Check Not Found (404)

Invalid Table Path (400)

Check Inactive (400)

Forbidden (403)

API Overview

API Reference

API Guides

Python SDK

CLI

Integrations

AI Agents

​Endpoints

​Concepts

​Get Referential Summary

​Response

​List Referential Checks

​Query Parameters

​Response

​Get Referential Check Details

​Response

​Create Referential Check

​Request Body

​Response

​Update Referential Check

​Request Body

​Delete Referential Check

​Execute Referential Check

​Response

​List Check Results

​Query Parameters

​Response

​Use Case: Detect Orphaned Orders

​Use Case: Cardinality Validation

​Error Responses

​Check Not Found (404)

​Invalid Table Path (400)

​Check Inactive (400)

​Forbidden (403)

Endpoints

Concepts

Get Referential Summary

Response

List Referential Checks

Query Parameters

Response

Get Referential Check Details

Response

Create Referential Check

Request Body

Response

Update Referential Check

Request Body

Delete Referential Check

Execute Referential Check

Response

List Check Results

Query Parameters

Response

Use Case: Detect Orphaned Orders

Use Case: Cardinality Validation

Error Responses

Check Not Found (404)

Invalid Table Path (400)

Check Inactive (400)

Forbidden (403)