# Id Graph Canonical Id Size

> Query ID graph to analyze canonical ID group sizes. Use when debugging over-stitching by checking how many individual IDs are mapped to each canonical ID, or when canonical ID groups seem too large.

- Skill: `treasure-data/id-graph-canonical-id-size` (Agent Skill)
- Install (CLI): `npx skillmds add treasure-data/id-graph-canonical-id-size`
- Raw SKILL.md: https://api.skillmd.com/api/skills/treasure-data/id-graph-canonical-id-size/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: treasure-data (https://skillmd.com/u/treasure-data)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/treasure-data/id-graph-canonical-id-size

---


# ID Graph - Canonical ID Size Analysis

Analyze canonical ID group sizes to identify over-stitching patterns where too many individual IDs are mapped to a single canonical ID.

## Requirements

- Parent segment ID with RT 2.0 enabled

## Database

The database name contains the parent segment ID and has this format cdp_audience_273509. This is where you can plug in the parent segment the user gives in the request.

## ids_updated table

The ID graph table is typically called `ids_updated` and is in the Parent Segment real time database. This table contains the canonical ID mappings created by RT 2.0's ID stitching process.

## Schema

The ids_updated table has the following key columns:
- canonical_id, string, the canonical identifier for a group of stitched IDs
- id_set, array(string), array of individual identifiers that belong to this canonical group

## Core Query

The main query to analyze ID counts per canonical ID:

```sql
WITH flattened AS (
    SELECT
        canonical_id,
        id_value
    FROM cdp_audience_<parent_segment_id>.ids_updated
    CROSS JOIN UNNEST(id_set) AS t(id_value)
    WHERE td_interval(time, '-7d')  -- Avoid full table scan
)
SELECT
    canonical_id,
    count(DISTINCT id_value) as unique_id_count
FROM flattened
GROUP BY canonical_id
ORDER BY unique_id_count DESC
LIMIT 100;
```

## Troubleshooting

- Ensure the parent segment has RT 2.0 enabled
- Large result sets may need LIMIT clauses for performance