- Build the Foundation
- Define Complex Metrics
- Refine and Harden Your Logic
- Test and Measure Your Impact
Step 1: Build the Foundation with a Semantic Layer
Before you can tackle complex analysis, you must establish a solid foundation. This involves creating a business-friendly semantic layer over your raw database structures and enriching it with basic, domain-specific language.- What you’ll do: Document tables and columns, define business entities, establish relationships, and add simple rules using natural language context.
- How to do it: For a detailed walkthrough, refer to our guide on Advanced Data Modeling: Creating Knowledge.
Step 2: Define Complex Metrics with Validated Queries
For specific business calculations where a very precise SQL structure is required, you should provide the AI with pre-vetted examples. This is ideal for complex metrics that might need window functions or nested queries.- What you’ll do: Create a library of Validated Queries by pairing a natural language question with its ideal SQL response.
- How to do it: The creation process is covered in our guide on Advanced Data Modeling: Creating Knowledge.
Step 3: Refine and Harden Your Logic
Once your foundational context is in place, you can apply advanced strategies to make your logic more robust, maintainable, and accurate.Optimize Your SQL for Validated Queries
The structure of your SQL has a significant impact on clarity and reusability. Follow these best practices to write more effective queries.- Use Common Table Expressions (CTEs) for clarity. For complex logic, breaking the query into understandable CTEs makes it more readable and allows for reuse in future metrics.
- Maintain consistent structures for related metrics. When creating multiple variations of a metric, use a shared CTE to define the base calculation. This prevents logic from drifting between different reports.
Combine Context Types for Greater Accuracy
In some complex cases, the AI might not follow an SQL example perfectly, leading to logical errors like double counting. You can prevent this by supplementing your Validated Queries with explicit instructions in the Language Context. For example, if there is a pre-aggregatedcountry_population column in a statewise_population table, adding country_population across states in the same country would lead to double counting. An instruction might help to make sure this never happens; add a rule like: "Never sum up country_population column across different rows for the same country" to prevent incorrect aggregations.
Step 4: Test and Measure Your Impact
Context modeling is an iterative process. To validate your improvements and track accuracy over time, you must continuously test your setup using a dedicated evaluation framework.- What you’ll do: Create Evaluation Sets containing test prompts and their expected SQL outputs, run evaluations, and analyze the reports to identify areas for improvement.
- How to do it: Learn the complete testing workflow in our guide, Use Evaluations Sets and Runs.