Agent Skills › walkthru-earth/geocoding-playground › investigate-data

investigate-data

GitHub

用于调查地理编码器数据质量问题，通过查询S3上的Parquet文件排查自动补全、重复或缺失数据等Bug。

.claude/skills/investigate-data/SKILL.md walkthru-earth/geocoding-playground

Trigger Scenarios

报告自动补全结果错误发现重复条目或数据缺失用户要求检查特定国家的数据执行'investigate'或'query S3'指令

Install

npx skills add walkthru-earth/geocoding-playground --skill investigate-data -g -y

More Options

Non-standard path

npx skills add https://github.com/walkthru-earth/geocoding-playground/tree/main/.claude/skills/investigate-data -g -y

Use without installing

npx skills use walkthru-earth/geocoding-playground@investigate-data

指定 Agent (Claude Code)

npx skills add walkthru-earth/geocoding-playground --skill investigate-data -a claude-code -g -y

安装 repo 全部 skill

npx skills add walkthru-earth/geocoding-playground --all -g -y

预览 repo 内 skill

npx skills add walkthru-earth/geocoding-playground --list

SKILL.md

Frontmatter

{
    "name": "investigate-data",
    "description": "Investigates geocoder data quality issues by querying live S3 parquet files via MotherDuck MCP. Use this skill when the user reports a bug in autocomplete results, duplicate entries, missing data, wrong city\/street\/postcode results, or asks to check data for a specific country. Also use when the user says 'check the data', 'investigate', 'query S3', 'what does the index look like', 'how many streets\/cities\/postcodes', or 'verify the data'. Do NOT use for frontend UI bugs, build issues, or code refactoring.\n",
    "allowed-tools": "Read, Grep, Glob, mcp__motherduck__execute_query"
}

Data Investigation Skill

Investigate geocoder data issues by querying live parquet files on S3.

S3 Base URL

https://s3.us-west-2.amazonaws.com/us-west-2.opendata.source.coop/walkthru-earth/indices/addresses-index/v4/release=<RELEASE>/

Substitute the current release, newest is 2026-04-15.0 as of 2026-04-18. The app auto-discovers releases, so never hard-code an old one.

Workflow

Understand the issue: What country, what index, what unexpected behavior?

Check the schema first if unsure about columns:

DESCRIBE SELECT * FROM read_parquet('...BASE_URL.../city_index/country=XX/data_0.parquet') LIMIT 1;

Query the relevant index using MotherDuck MCP (mcp__motherduck__execute_query):
- City issues: city_index/country=XX/data_0.parquet
- Street issues: street_index/country=XX/data_0.parquet
- Postcode issues: postcode_index/country=XX/data_0.parquet
- Number issues: number_index/country=XX/data_0.parquet
- Tile data: geocoder/country=XX/h3_res4=HEXHASH/bucket=NN/data_0.parquet
- Global stats: manifest.parquet, tile_index.parquet
Diagnose the root cause using these patterns:
- Duplicates: GROUP BY key_cols HAVING count(*) > 1
- Missing data: SELECT count(*) FILTER (col IS NULL)
- Tile overlap: list_has_any(tiles, [expected_tiles])
- Merged rows: Check bbox span (bbox_max - bbox_min) for unreasonably large values
- Ambiguity: Check len(tiles) distribution for a street name
Simulate the fix by running the corrected query and verifying output.
Cross-validate by checking 2-3 other countries for the same pattern.
Report with:
- Root cause (which pipeline step or data source)
- SQL evidence (the queries and results)
- Recommended fix (upstream pipeline SQL or frontend query-time dedup)

Known Gotchas

IT, JP, TW, CO have no postcode_index (404)
FR has no region (depth-1 address_levels only)
JP numbers in raw Overture are "banchi-coordZone", pipeline strips the zone suffix
City index uses per-country flat files, not global
Multi-unit buildings (apartments) emit one row per unit with identical full_address, the only differentiator is the unit column. 195 Clearview AVE in Ottawa = 328 rows. Always SELECT unit when investigating apparent duplicates

Parquet Verification Queries

After pipeline rebuilds, verify Parquet optimization with:

-- number_index: should have many small row groups + bloom filters
SELECT num_rows, num_row_groups, file_size_bytes
FROM parquet_file_metadata('...BASE_URL.../number_index/country=NL/data_0.parquet');

-- Bloom filters present?
SELECT path_in_schema, bloom_filter_offset, bloom_filter_length
FROM parquet_metadata('...BASE_URL.../number_index/country=NL/data_0.parquet')
WHERE path_in_schema = 'street_lower' LIMIT 5;

-- Geocoder tile bloom filters on street?
SELECT path_in_schema, bloom_filter_offset
FROM parquet_metadata('...BASE_URL.../geocoder/country=NL/h3_res4=841969dffffffff/bucket=_/data_0.parquet')
WHERE path_in_schema = 'street' LIMIT 3;