data-context-extractor 数据分析 Skill 模板实战用 skill-template 从占位符生成可交付的企业级数据技能【免费下载链接】knowledge-work-pluginsOpen source repository of plugins primarily intended for knowledge workers to use in Claude Cowork项目地址: https://gitcode.com/GitHub_Trending/kn/knowledge-work-plugins导读skill-template.md 是 knowledge-work-plugins 仓库中data-context-extractor元技能meta-skill的核心产出模板它把分析师头脑里的公司数据知识表结构、指标口径、过滤规则、查询套路沉淀为一份结构化的SKILL.md让 Claude 在回答公司数据问题时具备与业务一致的上下文。本文以该模板为骨架逐节讲解每个[PLACEHOLDER]的填写方法与底层逻辑并结合仓库内 sql-dialects.md、domain-template.md、example-output.md 与 package_data_skill.py 给出可复制、可运行、可校验的完整实战方案。读完本文你将掌握一套访谈分析师 → 生成技能 → 打包交付 → 迭代完善的标准化方法论能独立为公司数据仓库产出高质量的数据分析 Skill。一、模板在># Check for placeholder text that wasnt filled in if [PLACEHOLDER] in content or [COMPANY] in content: return False, SKILL.md contains unfilled placeholder text也就是说只要SKILL.md里还残留[PLACEHOLDER]或[COMPANY]打包就会失败并提示SKILL.md contains unfilled placeholder text。这把填满占位符从口头约定变成了构建期阻断是保证交付质量的机制设计。模板中出现的占位符主要有四类占位符类别示例含义主体标识[company]、[COMPANY]公司名用于 skill 命名与 description环境标识[WAREHOUSE_TYPE]数仓类型BigQuery / Snowflake / PostgreSQL / Redshift / Databricks / MySQL 等业务内容[PRIMARY_USE_CASE_1]、[TERM_1]、[METRIC_1_NAME]通过分析师访谈 数仓探查获得的具体业务事实数据对象[TABLE_NAME]、[COLUMN_NAME]、[ID_FIELD]真实表名与列名必须来自实际 schema三、Frontmatter让技能可被发现、可被触发模板的 Frontmatter 结构如下--- name: [company]-data-analyst description: [COMPANY] data analysis skill. Provides context for querying [WAREHOUSE_TYPE] including entity definitions, metric calculations, and common query patterns. Use when analyzing [COMPANY] data for: (1) [PRIMARY_USE_CASE_1], (2) [PRIMARY_USE_CASE_2], (3) [PRIMARY_USE_CASE_3], or any data questions requiring [COMPANY]-specific context. ---这段元数据决定了技能的两个关键能力1. name 与 description 是触发匹配的依据。参考># Check SKILL.md exists if not skill_md.exists(): return False, Missing SKILL.md # Check SKILL.md has frontmatter if not content.startswith(---): return False, SKILL.md missing YAML frontmatter # Check for required frontmatter fields if name: not in content[:500]: return False, SKILL.md missing name in frontmatter if description: not in content[:1000]: return False, SKILL.md missing description in frontmatter从源码结构看脚本要求文件必须命名为SKILL.md、必须以---开头、前 500 字符内出现name:、前 1000 字符内出现description:。因此 Frontmatter 必须放在文件最顶部且紧邻文件开头任何先写正文再补元数据的做法都会导致校验失败。四、SQL Dialect 段落按数仓类型插入方言要点模板中该节是一个占位指令SQL Dialect: [WAREHOUSE_TYPE][INSERT APPROPRIATE DIALECT SECTION FROM sql-dialects.md]生成时需从 sql-dialects.md 选取与用户数仓匹配的整段内容。该参考文件覆盖五种主流方言以下逐一列出其完整要点4.1 BigQuery表引用使用反引号project.dataset.table安全除法SAFE_DIVIDE(a, b)除零时返回 NULL 而非报错日期函数DATE_TRUNC(date_col, MONTH)、DATE_SUB(date_col, INTERVAL 1 DAY)、DATE_DIFF(end_date, start_date, DAY)列排除SELECT * EXCEPT(column_to_exclude)数组用UNNEST(array_column)展开结构体点号访问struct_col.field_name时间戳TIMESTAMP_TRUNC()默认按 UTC 存储字符串匹配LIKE、REGEXP_CONTAINS(col, rpattern)聚合中的 NULL多数聚合函数忽略 NULL需要时用IFNULL()或COALESCE()4.2 Snowflake表引用DATABASE.SCHEMA.TABLE大小写敏感列名需加引号Column_Name安全除法DIV0(a, b)返回 0DIV0NULL(a, b)返回 NULL日期函数DATE_TRUNC(MONTH, date_col)、DATEADD(DAY, -1, date_col)、DATEDIFF(DAY, start_date, end_date)列排除SELECT * EXCLUDE (column_to_exclude)数组FLATTEN(array_column)展开通过value访问Variant/JSON冒号记法variant_col:field_name时间戳TIMESTAMP_NTZ无时区、TIMESTAMP_TZ带时区字符串匹配LIKE、REGEXP_LIKE(col, pattern)大小写标识符默认大写除非加引号4.3 PostgreSQL / Redshift表引用schema.table惯例小写安全除法NULLIF模式a / NULLIF(b, 0)日期函数DATE_TRUNC(month, date_col)、date_col - INTERVAL 1 day、DATE_PART(day, end_date - start_date)列选择无 EXCEPT 语法必须显式列出列名数组UNNEST(array_column)PostgreSQLRedshift 支持有限JSONjson_col-field_name取文本、json_col-field_name取 JSON时间戳用AT TIME ZONE UTC做时区转换字符串匹配LIKE、col ~ pattern正则布尔原生 BOOLEAN 类型用TRUE/FALSE4.4 Databricks / Spark SQL表引用catalog.schema.tableUnity Catalog或schema.table安全除法NULLIF模式a / NULLIF(b, 0)或TRY_DIVIDE(a, b)日期函数DATE_TRUNC(MONTH, date_col)、DATE_SUB(date_col, 1)、DATEDIFF(end_date, start_date)列排除SELECT * EXCEPT (column_to_exclude)Databricks SQL数组EXPLODE(array_column)展开结构体点号访问struct_col.field_nameJSONjson_col:field_name或GET_JSON_OBJECT()字符串匹配LIKE、RLIKE正则Delta 特性DESCRIBE HISTORY、VERSION AS OF时间旅行4.5 MySQL表引用反引号database.table安全除法手写IF(b 0, NULL, a / b)或a / NULLIF(b, 0)日期函数DATE_FORMAT(date_col, %Y-%m-01)截断到月初、DATE_SUB(date_col, INTERVAL 1 DAY)、DATEDIFF(end_date, start_date)列选择无 EXCEPT必须显式列出列名数组原生支持有限常以 JSON 存储JSONJSON_EXTRACT(col, $.field)或col-$.field时间戳CONVERT_TZ()做时区转换字符串匹配LIKE、REGEXP正则大小写表名在 Linux 上区分大小写、Windows 上不区分4.6 跨方言公共模式速查操作BigQuerySnowflakePostgreSQLDatabricks当前日期CURRENT_DATE()CURRENT_DATE()CURRENT_DATECURRENT_DATE()当前时间戳CURRENT_TIMESTAMP()CURRENT_TIMESTAMP()NOW()CURRENT_TIMESTAMP()字符串拼接CONCAT()或\|\|CONCAT()或\|\|CONCAT()或\|\|CONCAT()或\|\|空值合并COALESCE()COALESCE()COALESCE()COALESCE()条件分支CASE WHENCASE WHENCASE WHENCASE WHEN去重计数COUNT(DISTINCT x)COUNT(DISTINCT x)COUNT(DISTINCT x)COUNT(DISTINCT x)填写建议不要只粘贴一个方言名而应把该方言整段要点原样放入保证后续生成的所有示例查询语法正确并随后用该方言的语法编写 Common Query Patterns 中的示例 SQL二者必须一致。五、实体消歧Entity Disambiguation数据建模的第一道关卡模板给出的结构如下Entity DisambiguationWhen users mention these terms, clarify which entity they mean:User can mean:Account: An individual login/profile ([PRIMARY_TABLE]: [ID_FIELD])Organization: A billing entity that can have multiple accounts ([ORG_TABLE]: [ORG_ID])[OTHER_TYPE]: [DEFINITION] ([TABLE]: [ID])Relationships:[ENTITY_1] → [ENTITY_2]: [RELATIONSHIP_TYPE] (join on [JOIN_KEY])这是模板中标注Critical关键的一节其依据来自 SKILL.md Bootstrap 模式 Phase 2 的访谈问题When people here say user or customer, what exactly do they mean? Are there different types?访谈时重点捕捉三类信息多实体类型同一个口语词user / customer / account是否指向多个不同实体实体间关系是 1:1、1:many 还是 many:many连接字段哪对 ID 字段把它们关联起来join key。模板给出的典型填法即每个含义一行格式为- **含义名**: 定义 (表名: ID字段)关系行用箭头表达方向并给出 join key。该节的实战价值在于它能直接消灭分析师说 user、工程师写 customer_id这类最常见的信息错位。六、业务术语表Business Terminology统一全公司的语言模板要求用三列表格定义术语TermDefinitionNotes[TERM_1][DEFINITION][CONTEXT/GOTCHA][TERM_2][DEFINITION][CONTEXT/GOTCHA][ACRONYM][FULL_NAME] - [EXPLANATION]从 example-output.md 的 ShopCo 示例可以看到这一节的成熟形态电商指标族TermDefinitionNotesGMVGross Merchandise Value - total order value before returns/discountsUse for top-line reportingNMVNet Merchandise Value - GMV minus returns and discountsUse for actual revenueAOVAverage Order Value - NMV / order countExclude $0 ordersLTVLifetime Value - total NMV per customer since first orderRolling calc, updates dailyCACCustomer Acquisition Cost - marketing spend / new customersBy cohort month注意示例的写法每个条目同时给出全称解释与使用注意Notes 列例如 GMV 用于顶层报告、NMV 才是真实收入、AOV 要排除 $0 订单、CAC 按 cohort 月计算。这些 Notes 正是分析师访谈中最容易流失的隐性知识务必逐条记录。七、标准过滤器Standard Filters把默认正确写进技能模板给出可直接复制的 SQL 骨架-- Exclude test/internal data WHERE [TEST_FLAG_COLUMN] FALSE AND [INTERNAL_FLAG_COLUMN] FALSE -- Exclude invalid/fraud AND [STATUS_COLUMN] ! [EXCLUDED_STATUS] -- [OTHER STANDARD EXCLUSIONS]并配套一个何时覆盖默认过滤的说明When to override:[SCENARIO_1]: Include [NORMALLY_EXCLUDED] when [CONDITION]这一节对应 SKILL.md 中的Data Hygiene访谈What should ALWAYS be filtered out of queries? (test data, fraud, internal users, etc.)访谈需确认标准 WHERE 子句、排除标记列is_test、is_internal、is_fraud、需要排除的特定值如status deleted。ShopCo 示例的成品如下-- Exclude test and internal orders WHERE order_status ! TEST AND customer_type ! INTERNAL AND is_employee_order FALSE -- Exclude cancelled orders for revenue metrics AND order_status NOT IN (CANCELLED, FRAUDULENT)设计要点标准过滤节的作用是把每个分析师都在查询开头复制的那段 WHERE沉淀为技能级默认行为避免每个查询重复犯忘了排除测试数据的错误同时必须写明例外场景否则可能误伤合法的管理报表需求。八、核心指标Key Metrics口径即真相模板要求每个指标按五个维度完整描述[METRIC_1_NAME]Definition: [PLAIN_ENGLISH_EXPLANATION]Formula:[EXACT_CALCULATION]Source:[TABLE_NAME].[COLUMN_NAME]Time grain: [DAILY/WEEKLY/MONTHLY]Caveats: [EDGE_CASES_OR_GOTCHAS]对应 SKILL.md 的Key Metrics访谈What are the 2-3 metrics people ask about most? How is each one calculated?需要捕捉精确公式如 ARR monthly_revenue × 12、指标来自哪些表/列、时间口径约定trailing 7 days、日历月等。ShopCo 示例的两个指标给出了教科书级示范Gross Merchandise Value (GMV)Definition: Total value of all orders placedFormula:SUM(order_total_gross)Source:CORE.FCT_ORDERS.order_total_grossTime grain: Daily, aggregated to weekly/monthlyCaveats: Includes orders that may later be cancelled or returnedNet RevenueDefinition: Actual revenue after returns and discountsFormula:SUM(order_total_gross - return_amount - discount_amount)Source:CORE.FCT_ORDERSCaveats: Returns can occur up to 90 days post-order; use settled_revenue for finalized numbersWhy it matters指标口径差异GMV 是否含取消订单、退货窗口 90 天、何时使用 settled_revenue是跨部门争论的头号来源。把公式、来源列、时间粒度和边界情况Caveats写死是技能可信度的根基。九、数据新鲜度Data Freshness避免基于过期数据的错误结论模板要求一张表级新鲜度登记表TableUpdate FrequencyTypical Lag[TABLE_1][FREQUENCY][LAG][TABLE_2][FREQUENCY][LAG]并提供标准检查 SQLSELECT MAX([DATE_COLUMN]) as latest_data FROM [TABLE]配合 domain-template.md 中每个表都要写 Update Frequency如 Daily/Hourly/Real-time附带 lag与 Partitioned By的要求可以在技能层面建立先验新鲜度、再谈数据的纪律。ShopCo 的 FCT_ORDERS 示例即注明Update Frequency: Hourly (15 min lag)、Partitioned By:order_date——这类信息直接决定了早上的报表能否覆盖昨晚的数据。十、知识库导航Knowledge Base Navigation让 SKILL.md 成为入口而非孤岛模板要求用表格把领域参考文件挂载到 SKILL.md 上DomainReference FileUse For[DOMAIN_1]references/[domain1].md[BRIEF_DESCRIPTION][DOMAIN_2]references/[domain2].md[BRIEF_DESCRIPTION]Entitiesreferences/entities.mdEntity definitions and relationshipsMetricsreferences/metrics.mdKPI calculations and formulas该表对应 Bootstrap 模式生成的目录结构references/entities.md、references/metrics.md、references/tables/[domain].md。ShopCo 示例的成品DomainReference FileUse ForOrdersreferences/orders.mdOrder tables, GMV/NMV calculationsCustomersreferences/customers.mdUser/customer entities, LTV, cohortsProductsreferences/products.mdCatalog, inventory, categories导航的两种用法一是让 LLM 在面对某个领域的深入问题时能定位到对应参考文件避免 SKILL.md 无限膨胀二是 Iteration Mode 中给 SKILL.md 的 Knowledge Base Navigation 表追加新领域行正是迭代更新的标准动作见 SKILL.md Step 4。十一、常用查询模式Common Query Patterns用真实 SQL 证明一切模板要求至少给出两个命名查询模式[PATTERN_1_NAME][SAMPLE_QUERY][PATTERN_2_NAME][SAMPLE_QUERY]example-output.md 的 ShopCo 示例展示了两类高价值模式① 按渠道的每日 GMV聚合 过滤 排序SELECT DATE_TRUNC(DAY, order_timestamp) AS order_date, channel, SUM(order_total_gross) AS gmv, COUNT(DISTINCT order_id) AS order_count FROM SHOPCO_DW.CORE.FCT_ORDERS WHERE order_status NOT IN (TEST, CANCELLED, FRAUDULENT) AND order_timestamp DATEADD(DAY, -30, CURRENT_DATE()) GROUP BY 1, 2 ORDER BY 1 DESC, 3 DESC② 客户 cohort 留存CTE JOIN 月份差WITH cohorts AS ( SELECT customer_id, DATE_TRUNC(MONTH, first_order_date) AS cohort_month FROM SHOPCO_DW.CORE.DIM_CUSTOMERS ) SELECT c.cohort_month, DATEDIFF(MONTH, c.cohort_month, DATE_TRUNC(MONTH, o.order_timestamp)) AS months_since_first, COUNT(DISTINCT c.customer_id) AS active_customers FROM cohorts c JOIN SHOPCO_DW.CORE.FCT_ORDERS o ON c.customer_id o.customer_id WHERE o.order_status NOT IN (TEST, CANCELLED) GROUP BY 1, 2 ORDER BY 1, 2注意两段查询都使用了真实表名/列名 与标准过滤节一致的 WHERE 条件 正确的方言语法Snowflake 的DATE_TRUNC/DATEADD/DATEDIFF。这正是 Customization Notes 第 4 条用真实示例而非抽象描述的落地形态——示例查询必须是可直接复制运行的。十二、故障排查Troubleshooting把踩过的坑写进文档模板给出三小节Common Mistakes常见错误[MISTAKE_1]: [EXPLANATION] → [CORRECT_APPROACH][MISTAKE_2]: [EXPLANATION] → [CORRECT_APPROACH]Access Issues权限问题对[TABLE]遇到权限错误时的替代方案workaround对 PII 受限列的处理策略ALTERNATIVE_APPROACHPerformance Tips性能提示先用[PARTITION_COLUMN]过滤以缩小扫描范围大表探索时使用LIMIT尽量用[AGGREGATED_TABLE]而非[RAW_TABLE]这三小节对应 SKILL.md 中的Common Gotchas访谈What mistakes do new analysts typically make with this data?需要捕捉易混淆的列名、时区问题、NULL 处理怪癖、历史表 vs 当前状态表的区分。模板的撰写方式要求错误 → 解释 → 正确做法三件套让后来者不必重新踩坑。十三、Customization Notes模板使用的五条纪律模板末尾给出生成技能的 5 条自定义规则这是整个模板的使用说明书Fill all placeholders- Dont leave any[PLACEHOLDER]text填满所有占位符一个不留Remove unused sections- If they dont have dashboards, remove that section删除用不到的章节比如没有仪表盘就删掉相关部分Add specificity- Generic advice is less useful than specific column names and values增加具体性具体的列名和值远比泛泛建议有用Include real examples- Sample queries should use actual table/column names示例必须使用真实表名/列名Keep it scannable- Use tables and code blocks liberally保持可扫读多用表格和代码块第 1 条已被 package_data_skill.py 机械执行第 3、4 条共同指向一个核心理念技能的价值密度取决于公司特异性——WHERE user_type ! INTERNAL比WHERE exclude_internal true更有用references/orders.md比一份通用 SQL 教程更有用。十四、参考文件模板领域细节的落点SKILL.md 生成的技能中领域细节存放在references/tables/[domain].md其模板为 domain-template.md结构包含Quick Reference业务背景2-3 句、实体澄清歧义词多义列举、标准过滤Key Tables每张表给出 Location / Description / Primary Key / Update Frequency / Partitioned By配列名-类型-描述-注意四列表以及 Relationships、Nested/Struct 字段Key Metrics指标表Metric / Definition / Table / Formula / NotesSample Queries至少 3 类——基础查询、常见查询、含 CTE 的复杂查询Common Gotchas错误/正确写法对比Related Dashboards可选example-output.md 给出了references/orders.md的成品示例其中 FCT_ORDERS 表的列级文档极具参考价值order_status枚举值、customer_id对 guest 为 NULL、return_amount异步更新等并附示例查询SELECT DATE_TRUNC(WEEK, order_date) AS week, COUNT(*) AS total_orders, SUM(CASE WHEN return_amount 0 THEN 1 ELSE 0 END) AS orders_with_returns, DIV0(SUM(CASE WHEN return_amount 0 THEN 1 ELSE 0 END), COUNT(*)) AS return_rate FROM SHOPCO_DW.CORE.FCT_ORDERS WHERE order_status NOT IN (TEST, CANCELLED) AND order_date DATEADD(MONTH, -3, CURRENT_DATE()) GROUP BY 1 ORDER BY 1同时该参考文件给出了建议的领域拆分清单可据此为每个领域建立独立文件revenue.md- Billing, subscriptions, ARR, transactionsusers.md- Accounts, authentication, user attributesproduct.md- Feature usage, events, sessionsgrowth.md- DAU/WAU/MAU, retention, activationsales.md- CRM, pipeline, opportunitiesmarketing.md- Campaigns, attribution, leadssupport.md- Tickets, CSAT, response times十五、打包与交付从技能目录到 .skill 文件Bootstrap 模式 Phase 4 与 Iteration 模式的收尾动作都是打包交付。仓库提供了标准打包工具 package_data_skill.py# 打包到当前目录 python package_data_skill.py /path/to/[company]-data-analyst # 打包到指定输出目录 python package_data_skill.py /path/to/[company]-data-analyst /tmp/outputs脚本的核心逻辑对应前文各处引用分为校验与打包两阶段validate_skill()依次检查SKILL.md存在、YAML Frontmatter 存在、name:与description:字段齐全、无[PLACEHOLDER]/[COMPANY]残留package_skill()将技能目录压缩为skill_name.zipZIP_DEFLATED跳过隐藏文件.开头的文件/目录、__pycache__、.DS_Store、Thumbs.db并以技能目录父目录为基准计算 zip 内相对路径。校验失败时脚本打印原因如Missing SKILL.md、SKILL.md missing YAML frontmatter、SKILL.md contains unfilled placeholder text并以非零退出码结束成功则打印Validation passed、逐个列出Added: path并输出最终 zip 路径。这意味着生成技能 → 打包 → 交付整条链路可完全脚本化、可回归验证。十六、交付前自检清单SKILL.md 的 Quality Checklist 定义了交付前的最终验收标准与本文模板逐节对应SKILL.md 具有完整的 Frontmattername, description实体消歧Entity disambiguation一节清晰明确关键术语Key terminology已定义标准过滤/排除Standard filters/exclusions已记录每个领域至少 2-3 个示例查询At least 2-3 sample queries per domainSQL 使用正确的方言语法SQL uses correct dialect syntax参考文件已从 SKILL.md 的导航表链接Reference files are linked from SKILL.md navigation section结合打包脚本的硬校验可以认为满足此清单 通过validate_skill()即达到可交付标准。实践中的最佳路径是按本文第 213 节逐项填写skill-template.md占位符 → 用 domain-template.md 生成领域参考文件 → 按 example-output.md 的成品校准质量 → 运行 package_data_skill.py 校验并打包交付。整套流程下来产出的是一个实体清晰、口径统一、过滤默认正确、查询即拷即用的企业级数据分析技能而这正是data-context-extractor元技能存在的意义。【免费下载链接】knowledge-work-pluginsOpen source repository of plugins primarily intended for knowledge workers to use in Claude Cowork项目地址: https://gitcode.com/GitHub_Trending/kn/knowledge-work-plugins创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考