smart-data-query

Goal

Generate executable final SQL based on the "requirements text + data warehouse catalog", satisfying readability, executability, and acceptance.

Default strategy (reduce questions, produce results first)

Core principle: do not ask too many questions; produce the SQL first, and adjust based on user feedback after they review it.

Item	Default behavior
Data warehouse catalog	Current catalog (ask only if not found)
Output fields	Decide on your own (do not ask the user)
Time range	Latest partition `MAX(dt)`(do not ask)
Layered table selection	ADS → DWS → DWT (do not ask)
Table name format	With database name prefix, prod environment

Process

Build catalog index: Run scripts/build_catalog.py to generate the catalog
Select tables: By ADS → DWS → DWT priority, use scripts/search_catalog.py to search
Read table documentation: Confirm granularity, partition fields, and metric definitions
Assemble SQL：
- For array fields, by default use LATERAL VIEW EXPLODE to fully expand
- For multi-condition filtering, clarify intersection/union logic
- For partition filtering, use SELECT MAX(dt) for dynamic retrieval
- CTE structure:base → agg → dim → final
Self-check: Fields exist, join key types are consistent, group by matches select, partition pushdown
Output: Executable SQL with database names + Chinese aliases

Guardrails

Prohibit generating destructive statements (DROP/TRUNCATE/DELETE/INSERT OVERWRITE）
Prohibit TRANSFORM functions and subquery expressions in SELECT/ON (Hive compatibility)
When definitions are uncertain, provide multi-branch SQL or explain assumptions in comments

Wrap-up

After each delivery:

Resources

Purpose	Path
Generate index	`scripts/build_catalog.py`
Search candidate tables	`scripts/search_catalog.py`
SQL static check	`scripts/check_query.py`
Record logs	`scripts/log_qa.py`
Requirements questionnaire	`references/问数需求问卷模板.md`
Dialect compatibility	`references/方言与兼容性.md`
Usage examples	`references/调用示例.md`

About this skill