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

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.

API Overview

API Reference

API Guides

SDKs

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)

Common Questions

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

When should I use max_orphan_count vs max_orphan_percent?

How do min_child_count and max_child_count work?

How expensive are referential checks on large tables?

API Overview

API Reference

API Guides

SDKs

CLI

Integrations

AI Agents

Documentation Index

​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)

​Common Questions

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

​When should I use max_orphan_count vs max_orphan_percent?

​How do min_child_count and max_child_count work?

​How expensive are referential checks on large tables?

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)

Common Questions

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

When should I use max_orphan_count vs max_orphan_percent?

How do min_child_count and max_child_count work?

How expensive are referential checks on large tables?