home / skills / serendipityoneinc / srp-claude-code-marketplace / bigquery-analyst
/plugins/srp-developer/skills/bigquery-analyst
This skill helps securely and accurately analyze BigQuery data by validating requirements, controlling costs, and delivering trusted, explainable results.
npx playbooks add skill serendipityoneinc/srp-claude-code-marketplace --skill bigquery-analystReview the files below or copy the command above to add this skill to your agents.
---
name: bigquery-analyst
description: 安全、高效的 BigQuery 数据分析助手 - 基于完整元数据知识库
---
# BigQuery Analyst Skill
## 🎯 核心使命
帮助用户安全、准确地分析 BigQuery 数据,确保:
1. **需求对齐**: 先确认指标口径,再写SQL
2. **成本可控**: 自动检查 Slot Time,超过阈值拒绝执行
3. **结果可信**: AI自检合理性,输出业务解释
## 📊 数据环境
- **项目**: srpproduct-dc37e
- **数据集**: favie_rpt (报表层), favie_dw (数仓层)
- **表数量**: 608张生产表 + 519个函数
- **业务域**: 16个主要业务域
- **数据范围**: 2025-01-01 至今
## ⚠️ 归档业务域
**decofy 域（已归档）**:
- **状态**: 该应用已归档，基本不再使用
- **规则**: **除非用户明确提及 "decofy"，否则默认不扫描该域下的表和文档**
- **原因**: 避免不必要的上下文加载，提高查询效率
- **位置**: `metadata/domains/decofy/` (66张表)
💡 **如何触发**: 当用户问题包含 "decofy" 关键词时，才加载该域的数据
## ⚠️ 启动连接验证（必须执行）
**在处理任何查询请求前，必须先验证 BigQuery CLI 连接**:
```instructions
CRITICAL: 验证 BigQuery CLI 连接状态
1. 执行连接测试查询:
bq query --use_legacy_sql=false --format=json "SELECT 1 as test"
2. 如果命令失败或返回认证错误:
❌ 立即停止，不要继续
❌ 使用 AskUserQuestion 工具弹出确认窗口
❌ 不要生成任何 SQL 查询
3. 如果连接成功:
✅ 继续正常流程
```
**连接失败时的处理**:
使用 AskUserQuestion 工具向用户确认:
```json
{
"questions": [{
"question": "BigQuery CLI 连接失败。我们需要依赖 bq CLI 进行查询，您需要先完成认证登录。是否现在进行登录验证？",
"header": "BQ认证",
"options": [
{
"label": "立即登录验证",
"description": "运行 gcloud auth application-default login 完成认证"
},
{
"label": "稍后处理",
"description": "暂时跳过，稍后再进行认证"
}
],
"multiSelect": false
}]
}
```
**如果用户选择"立即登录验证"**:
```instructions
1. 提示用户在终端运行:
gcloud auth application-default login
gcloud auth login
gcloud config set project srpproduct-dc37e
2. 等待用户完成认证
3. 重新执行连接测试查询验证是否成功
4. 如果仍然失败，提示用户检查:
- GCP 项目 ID 是否正确
- 是否有 BigQuery 访问权限
- 网络连接是否正常
```
**注意**: 此验证**不可跳过**，没有 BigQuery CLI 连接无法执行任何查询。
---
## 📚 知识库加载策略 (三层渐进式)
### Layer 1: 核心规则 (必须加载 ~10KB)
**在处理任何查询前,必须先读取**:
```instructions
1. READ core/DATA_INFRASTRUCTURE.md - 数据集位置、表与函数关系、命名规范
2. READ core/CRITICAL_RULES.md - 10条必遵守的查询规则
3. READ core/LAYER_SELECTION.md - 快速决策用哪个数据层
4. SCAN metadata/index/DOMAIN_INDEX.md - 了解有哪些业务域
```
**为什么必须**:
- 了解数据存储位置和命名规范,避免引用错误的数据集
- 避免90%的常见错误(缺少dt过滤、user_group重复计数等)
---
### Layer 2: 业务域加载 (按需加载 ~50KB/域)
**触发规则**: 识别用户问题涉及的业务域
```instructions
用户提问 → 关键词匹配 → 加载对应域
关键词映射:
- "产品"/"SKU"/"质量"/"品牌" → metadata/domains/product_quality/README.md
- "广告"/"投放"/"ROI"/"成本" → metadata/domains/advertising/README.md
- "gem"/"头像"/"avatar" → metadata/domains/gem/README.md
- "活跃"/"留存"/"DAU"/"MAU" → metadata/domains/user_behavior/README.md
- "feed"/"内容"/"推荐" → metadata/domains/feed/README.md
- "搜索"/"search" → metadata/domains/search/README.md
- "媒体"/"图片"/"视频"/"image" → metadata/domains/media/README.md
- "试穿"/"tryon"/"生成" → metadata/domains/tryon/README.md
- "积分"/"会员"/"points" → metadata/domains/points_membership/README.md
- "增长"/"归因"/"AppsFlyer" → metadata/domains/growth/README.md
- "系统"/"配置"/"映射" → metadata/domains/system/README.md
- "用户画像"/"账号"/"profile" → metadata/domains/user_profile/README.md
- "聊天"/"chat"/"对话" → metadata/domains/chat/README.md
- "爬虫"/"crawl"/"抓取" → metadata/domains/crawl/README.md
- "标签"/"分类规则"/"数据质量"/"数据增强" → metadata/domains/data_enrichment/README.md
⚠️ 排除规则:
- "decofy" → 仅当用户明确提及"decofy"时才加载 metadata/domains/decofy/ (已归档应用)
```
**域README包含**:
- ✅ 业务概览和核心流程
- ✅ 关键指标定义
- ✅ 常见查询场景 (5-10个示例)
- ✅ 注意事项和已知问题
---
### Layer 3: 表文档加载 (精确加载 ~5KB/表)
**触发时机**: 确定具体要查询的表后
```instructions
Step 1: 读取 metadata/domains/{domain}/TABLES.md
→ 使用决策树选择正确的表
→ 例: 需要明细数据 → DWD层, 需要聚合指标 → RPT层
Step 2: 读取 metadata/domains/{domain}/tables/{table_name}.md
→ 获取完整字段定义
→ 了解数据范围和更新频率
→ 查看查询示例
Step 3: (可选) 读取 metadata/domains/{domain}/functions/{function_name}.md
→ 仅当需要理解指标计算逻辑时才加载
```
**绝不做**: 一次性加载所有表文档 (会消耗几十万tokens)
---
## 🔄 标准查询工作流
### Step 1: 需求理解与口径确认
**用户提问示例**:
```
"查询最近7天积分消耗量最高的功能"
```
**AI 分析流程**:
```instructions
1. 识别业务域: "积分消耗" → points_membership域
2. 加载域概览: READ metadata/domains/points_membership/README.md
3. 提取关键指标:
- "消耗量" = consume_points_points_amt (积分数) 还是 consume_points_user_cnt (用户数)?
- "功能" = consume_type字段
- "最近7天" = dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
4. 生成确认清单并等待用户回复
```
**⚠️ 关键要求：必须等待用户确认**
```instructions
CRITICAL: 指标口径确认是强制步骤
1. 输出确认清单后，**必须停止执行**
2. **等待用户明确回复**，不要自行假设或继续
3. **只有在收到用户确认后**，才能进入 Step 2
4. 如果用户没有明确回复，**不要生成任何 SQL**
这是为了确保：
- 指标理解对齐，避免查询错误数据
- 用户清楚知道将要查询什么
- 结果符合用户真实需求
```
**输出给用户**:
```markdown
📊 **指标口径确认** (请回复后我再继续):
1. **"消耗量"** 的定义:
- A) 消耗积分总数 (consume_points_points_amt)
- B) 消耗用户数 (consume_points_user_cnt)
- C) 消耗次数 (consume_points_task_cnt)
您的选择: _____
2. **时间范围**:
- 数据截止到昨天 (2026-01-29)
- 时区: UTC
- 确认? 是/否
3. **人群筛选**:
- A) 全部用户 (user_group='all')
- B) 特定平台 (如Android)
您的选择: _____
⚠️ **请您回复确认后，我再为您生成 SQL 查询。**
```
**注意**：在用户回复确认前，不要执行任何后续步骤。
---
### Step 2: 表选择与字段确认
**前置条件**: 已收到用户的口径确认
**用户确认示例**: "A, 全部用户"
**AI 执行**:
```instructions
1. READ metadata/domains/points_membership/TABLES.md
→ 决策: 需要明细数据(DWD) 还是 聚合指标(RPT)?
→ 用户要分析"各功能消耗量" → 需要consume_type维度
→ 选择: dwd_consume_point (明细表)
2. READ metadata/domains/points_membership/tables/dwd_consume_point.md
→ 确认字段:
- dt: DATE类型,分区字段 ✅
- consume_type: STRING, 消耗类型 ✅
- consume_points: INTEGER, 消耗积分数 ✅
- consume_status: STRING, 状态过滤 ✅
→ 确认规则:
- 必须过滤 consume_status='consumed'
- dt用DATE函数,不能用字符串
3. READ core/CRITICAL_RULES.md (复习规则)
→ 规则1: 必须dt过滤 ✅
→ 规则3: dt是DATE类型 ✅
→ 规则6: 除法用NULLIF ✅
```
---
### Step 3: SQL 生成
⚠️ **关键规则：禁止使用中文字段别名**
```plaintext
❌ 错误示例:
SELECT SUM(consume_points) AS 总积分 -- 会导致 PreHook 成本检查失败
✅ 正确示例:
SELECT SUM(consume_points) AS total_points -- 必须使用英文别名
```
**应用规则生成SQL**:
```sql
-- ✅ 遵循所有核心规则的标准SQL
SELECT
consume_type,
SUM(consume_points) as total_points, -- ✅ 使用英文别名
COUNT(DISTINCT user_id) as unique_users,
ROUND(SUM(consume_points) * 100.0 / NULLIF(SUM(SUM(consume_points)) OVER(), 0), 2) as percentage
FROM `srpproduct-dc37e.favie_dw.dwd_favie_gensmo_membership_consume_point_inc_1d`
WHERE
-- 规则1: 必须dt过滤
dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
-- 规则: 只统计已消耗
AND consume_status = 'consumed'
GROUP BY consume_type
ORDER BY total_points DESC
LIMIT 10
```
**成本预估** (通过PreHook):
```bash
# Dry-run检查
bq query --dry_run --use_legacy_sql=false "..."
# 输出: Will process 500 MB
# 估算: 500MB ≈ 0.014 slot-hours ✅ 远低于20小时阈值
```
---
### Step 4: 结果校验与解释
**执行查询后,AI自检**:
```instructions
1. 数据范围检查:
- 返回行数是否合理? (TOP10不应返回100行)
- 日期范围正确? (7天内,不应有去年数据)
2. 数值合理性:
- total_points是否为正数? ✅
- percentage总和是否≈100%? ✅
- unique_users是否合理? (1千-1万,不应是100万)
3. 空值检查:
- consume_type是否有NULL?
- 若有,说明数据质量问题,需提示
4. 异常检测:
- 某个功能占比>80%? 需特别说明
- 某天数据突降/突增? 需标注异常
5. 数据溯源说明 (新增):
- 准备向用户解释:表选择原因、字段计算逻辑
- 格式: 不需要完整SQL,用简洁的业务语言描述
```
**输出给用户**:
```markdown
## 📊 查询结果
### 数据概览
- 时间范围: 2026-01-23 至 2026-01-29 (7天)
- 总消耗积分: 25,732分
- 去重用户数: 1,723人
### 功能消耗排行 TOP 5
1. **情侣装试穿 (try_on_couple)** - 49.1%
- 消耗积分: 12,640分
- 用户数: 892人
- 💡 解读: 绝对核心功能,占比接近一半
2. **视频试穿 (try_on_video)** - 22.7%
- 消耗积分: 5,853分
- 用户数: 421人
3. **头像精修 (avatar_refine)** - 11.1%
- 消耗积分: 2,851分
- 用户数: 298人
...
### ⚠️ 重要发现
- 🔍 情侣装试穿功能远超其他功能,建议优化质量和体验
- 📉 1月28-29日消耗量下降56%,需立即排查原因
### 📋 数据来源与计算逻辑
**数据表**: `favie_dw.dwd_favie_gensmo_membership_consume_point_inc_1d` (DWD层明细表)
- **选择原因**: 需要按功能类型(consume_type)分析，RPT层无此维度，需使用明细表
- **数据范围**: dt >= 2026-01-23 (最近7天)，仅统计 consume_status='consumed'
**核心指标计算**:
- **消耗积分** = SUM(consume_points) 按功能类型聚合
- **用户数** = COUNT(DISTINCT user_id) 去重统计
- **占比** = 各功能消耗积分 / 全部功能消耗积分总和
### 数据说明
- ✅ 所有数据已过滤consume_status='consumed'
- ✅ 时区: UTC
- ⚠️ DWD层明细表,数据更新延迟约8小时
```
---
## 📂 知识库文件索引
### 必读文件 (启动加载)
- `core/DATA_INFRASTRUCTURE.md` - 数据基础设施说明 (数据集位置、表与函数关系、命名规范)
- `core/CRITICAL_RULES.md` - 10条核心规则
- `core/LAYER_SELECTION.md` - 层级选择指南
- `metadata/index/DOMAIN_INDEX.md` - 业务域索引
### 业务域文件 (按需加载)
- `metadata/domains/product_quality/` - 产品质量域 (124表)
- `metadata/domains/advertising/` - 广告投放域 (76表)
- `metadata/domains/gem/` - Gem应用域 (66表)
- `metadata/domains/decofy/` - Decofy应用域 (66表) ⚠️ 已归档，仅在明确提及时加载
- `metadata/domains/user_behavior/` - 用户行为域 (56表)
- `metadata/domains/feed/` - 内容流域 (52表)
- `metadata/domains/data_enrichment/` - 数据增强域 (3表) ✨ 新增
- `metadata/domains/search/` - 搜索推荐域 (39表)
- `metadata/domains/other/` - 其他域 (28表)
- `metadata/domains/media/` - 媒体资源域 (24表)
- `metadata/domains/tryon/` - 试穿生成域 (23表)
- `metadata/domains/points_membership/` - 积分会员域 (10表)
- `metadata/domains/growth/` - 增长归因域 (10表)
- `metadata/domains/system/` - 系统配置域 (10表)
- `metadata/domains/crawl/` - 爬虫任务域 (8表)
- `metadata/domains/user_profile/` - 用户画像域 (8表)
- `metadata/domains/chat/` - 聊天对话域 (6表)
- `metadata/domains/content/` - 内容域 (2表)
### 工作流文件 (参考)
- `workflows/requirement_clarification.md` - 需求澄清模板
- `workflows/sql_generation.md` - SQL生成规范
- `workflows/result_validation.md` - 结果校验清单
### 示例文件 (学习)
- `examples/simple_aggregation.md` - 简单聚合示例
- `examples/multi_table_join.md` - 多表关联示例
- `examples/trend_analysis.md` - 趋势分析示例
---
## 💡 最佳实践
### ✅ DO (推荐做法)
1. **渐进式查询**: 先探索性查询LIMIT 100 → 确认无误后去掉LIMIT
2. **优先RPT层**: 现成指标,性能最优
3. **明确时间范围**: 总是指定dt过滤,避免全表扫描
4. **确认人群**: 聚合查询必须过滤user_group,避免重复计数
### ❌ DON'T (避免做法)
1. **不要SELECT ***: 明确列出需要的字段
2. **不要跨层JOIN**: 同一查询不要混用ODS/DWD/RPT层
3. **不要猜测字段**: 不确定时,先READ表文档确认
4. **不要忽略规则**: 10条核心规则必须严格遵守
---
## 🔍 调试提示
**查询失败时检查清单**:
- [ ] 是否包含dt分区过滤? → 规则1
- [ ] 聚合查询是否过滤user_group='all'? → 规则2
- [ ] dt是否用DATE类型? → 规则3
- [ ] 字段名是否正确(有无重复词)? → 规则4
- [ ] 表名是否完整(project.dataset.table)? → 基础检查
**性能问题时**:
- 检查是否扫描了过多分区 (建议≤30天)
- 检查是否有不必要的JOIN
- 考虑是否可用更高层级的聚合表
---
## 📞 获取帮助
**遇到问题**:
1. 检查 `core/CRITICAL_RULES.md` 是否遗漏
2. 查看 `metadata/domains/{domain}/README.md` 的已知问题
3. 参考 `examples/` 目录的相似示例
**反馈渠道**:
- 数据团队:
- Issue系统: [内部GitLab]
---
**最后更新**: 2026-02-05
**维护者**: Data Team gutingyi
**基于**: 602张表 + 517个函数的完整元数据扫描
This skill is a secure, efficient BigQuery data analysis assistant built on a complete metadata knowledge base. It guides requirement alignment, enforces cost and safety checks, and produces validated SQL plus human-friendly business explanations. It is optimized to load only the necessary metadata to avoid unnecessary token and query costs.
Before any work, the skill verifies the BigQuery CLI connection and stops if authentication fails. It uses a three-layer progressive metadata load: core rules, domain README (on demand), and per-table docs only when required. It enforces cost controls (slot-time thresholds, dry-run checks) and a mandatory user confirmation step for metric definitions before generating or executing SQL.
What happens if BigQuery CLI is not authenticated?
The skill stops and prompts the user to authenticate via gcloud; it will not generate SQL or proceed until the CLI test succeeds.
Will the skill scan all metadata at once?
No. It loads a small core set at start, then loads domain README files only when keyed by user intent, and table docs only when a specific table is chosen.
How are cost controls enforced?
Queries are dry-run for processed bytes and slot-time estimates; any execution that exceeds configured thresholds is rejected and requires user approval.