Schema Item Grounding

Skill: schema item grounding

1. Capability Definition & Real Case

• Professional Definition: The ability to map natural-language intent to specific relational schema elements (tables, columns, and join keys) under high structural entropy, dynamic table materialization, and large-scale semantic overlap. This specifically encompasses the resolution of schema ambiguity—where multiple tables or columns are semantically viable candidates—by utilizing iterative schema masking, confidence-based selection (Selector/Generator paradigms), and preference-aware grounding to distinguish between raw, pre-aggregated, or historically-versioned data components.
• Dimension Hierarchy: Environment Grounding->Retrieval and Alignment->schema item grounding

Real Case

[Case 1]

• Initial Environment: An enterprise retail database contains two tables: 'customers' and 'orders'. The 'orders' table contains a row-level 'revenue' column, while the 'customers' table includes a pre-calculated 'total_revenue' column for each client. The agent must decide whether a join/aggregation or a flat retrieval is intended.
• Real Question: What is the revenue per customer?
• Real Trajectory: 1. Search schema for the entity 'revenue' and find matches in both 'orders.revenue' and 'customers.total_revenue'. 2. Identify the 'per customer' constraint, which is compatible with both a JOIN (orders to customers) and the pre-aggregated column in the customers table. 3. Evaluate the structural ambiguity: a join over millions of order rows vs. a direct lookup of the pre-summed field. 4. Since the intent is generalized, generate both possibilities or prioritize the pre-aggregated column if runtime performance is implicitly prioritized in the organizational context.
• Real Answer: SELECT customer_id, total_revenue FROM customers;
• Why this demonstrates the capability: This case illustrates 'Join Ambiguity' or structural ambiguity. The agent must recognize that the same analytical intent can be satisfied by two radically different SQL structures (a JOIN + SUM vs. a simple SELECT) depending on which part of the schema is grounded, requiring a choice between raw and derived data sources.

[Case 2]

• Initial Environment: A corporate database tracks sales performance. The 'sales' table contains two highly similar columns: 'gross_sales' and 'net_sales'. There is no explicit clarification in the user's prompt about which metric to use.
• Real Question: What were the total sales last quarter?
• Real Trajectory: 1. Ground the term 'total sales' to the schema. 2. Identify a 'Column Ambiguity' where both 'gross_sales' and 'net_sales' are valid semantic interpretations. 3. Analyze systemic hints or historical user feedback to see if 'total' usually correlates to gross figures. 4. Construct the query using 'gross_sales' but flag the 'net_sales' alternative as a potential recommendation.
• Real Answer: SELECT SUM(gross_sales) FROM sales WHERE date >= '2024-01-01' AND date <= '2024-03-31';
• Why this demonstrates the capability: This demonstrates 'Column-Level Semantic Overlap'. The agent must navigate a scenario where the natural language is underspecified relative to the schema's precision, forcing a decision between two technically valid relational paths that produce different results.

[Case 3]

• Initial Environment: A university database uses multiple tables for department records, including 'curr_dept' (current active departments) and 'dept_2022' (a historical snapshot).
• Real Question: What are the average salaries by department?
• Real Trajectory: 1. Identify 'department' as the grouping entity. 2. Find two candidates: 'curr_dept' and 'dept_2022'. 3. Apply the 'Recency Heuristic': in the absence of a specific year in the prompt, the agent should ground the intent to the 'active' rather than the 'snapshot' table. 4. Perform the join with the 'employees' table to calculate the averages across the current department roster.
• Real Answer: SELECT T2.dept_name, AVG(T1.salary) FROM employees AS T1 JOIN curr_dept AS T2 ON T1.dept_id = T2.dept_id GROUP BY T2.dept_name;
• Why this demonstrates the capability: This verifies 'Table Ambiguity' resolution. The agent must distinguish between a static core relation and a transient historical view, ensuring the SQL remains relevant to the user's implied 'current' state without being misled by similarly named archival tables.

Pipeline Execution Instructions

To synthesize data for this capability, you must strictly follow a 3-phase pipeline. Do not hallucinate steps. Read the corresponding reference file for each phase sequentially:

1. Phase 1: Environment Exploration Read the exploration guidelines to discover raw knowledge seeds: references/EXPLORATION.md

2. Phase 2: Trajectory Selection Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: references/SELECTION.md

3. Phase 3: Data Synthesis Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: references/SYNTHESIS.md