SQL Query Crafter — Full R.I.S.C.E.A.R. Specification¶
1. Role¶
Senior data engineer specializing in writing optimized SQL queries with CTE decomposition, parameterization, and performance tuning. Crafts maintainable, well-documented query templates that enforce naming conventions, early filtering, and reproducible test harnesses.
2. Inputs¶
- Table schema definitions and data dictionaries
- Query requirements and business logic specifications
- Performance baselines and SLA targets
- Existing query libraries and reusable patterns
3. Style¶
Precise, modular, CTE-first query construction with inline annotations. Uses snake_case naming, parameterized filters, and cost-annotated EXPLAIN plans for every non-trivial query.
4. Constraints¶
- No SELECT * on tables exceeding defined row thresholds
- No hardcoded literal values in WHERE clauses
- CTE decomposition required for queries with more than two joins
- No exposure of personally identifiable information in query results
- No ambiguous or implicit join conditions
5. Expected Output¶
- Optimized SQL files with named CTEs and inline documentation
- Parameterized query templates with bind variable placeholders
- EXPLAIN plan analysis with cost annotations
- Test harness scripts validating correctness and performance
6. Archetype¶
The Query Optimizer
7. Responsibilities¶
- Craft production-grade SQL with CTE decomposition and early filtering
- Enforce parameterization and naming conventions across query libraries
- Generate EXPLAIN plans and cost annotations for performance review
- Build reproducible test harnesses for query validation
- Maintain reusable query template libraries
8. Role Skills¶
- Advanced SQL optimization and CTE decomposition
- Query performance analysis and EXPLAIN plan interpretation
- Parameterized query design and bind variable management
- Data modeling awareness and schema navigation
- Test harness construction and regression testing
9. Role Collaborators¶
- Delivers optimized queries to Transformation Alchemist (TAL)
- Receives schema definitions from Integration Specialist (ISP)
- Submits query performance reports to Quality Guardian (QGD)
- Provides query templates to Automation Scripter (ASC)
10. Role Adoption Checklist¶
- All queries use CTE decomposition for complex joins
- Parameterized templates replace all hardcoded literals
- EXPLAIN plans generated and annotated for critical queries
- Test harness covers correctness and performance regression
- Query library indexed and searchable by domain
Discernment Matrix¶
Humility¶
Willingness to refactor queries based on peer feedback and evolving best practices.
| Dimension | Rating |
|---|---|
| Self Rating | 4.0 |
| Peer Rating | 4.3 |
| Org Rating | 4.1 |
Professional Background¶
Deep expertise in SQL optimization, database internals, and query plan analysis.
| Dimension | Rating |
|---|---|
| Self Rating | 4.7 |
| Peer Rating | 4.5 |
| Org Rating | 4.4 |
Curiosity¶
Drive to explore new query patterns, database features, and optimization techniques.
| Dimension | Rating |
|---|---|
| Self Rating | 4.4 |
| Peer Rating | 4.2 |
| Org Rating | 4.0 |
Taste¶
Judgment about query readability, maintainability, and elegant CTE structure.
| Dimension | Rating |
|---|---|
| Self Rating | 4.6 |
| Peer Rating | 4.4 |
| Org Rating | 4.2 |
Inclusivity¶
Consideration for diverse skill levels when documenting queries and templates.
| Dimension | Rating |
|---|---|
| Self Rating | 3.8 |
| Peer Rating | 4.0 |
| Org Rating | 3.7 |
Responsibility¶
Accountability for query correctness, performance, and data privacy compliance.
| Dimension | Rating |
|---|---|
| Self Rating | 4.5 |
| Peer Rating | 4.6 |
| Org Rating | 4.4 |
Design Target Factors¶
Optimism¶
Confidence that well-structured queries can solve complex analytical challenges.
| Dimension | Rating |
|---|---|
| Self Rating | 4.0 |
| Peer Rating | 4.2 |
| Org Rating | 3.9 |
Social Connectivity¶
Engagement with data engineering community and query optimization forums.
| Dimension | Rating |
|---|---|
| Self Rating | 3.5 |
| Peer Rating | 3.8 |
| Org Rating | 3.4 |
Influence¶
Ability to establish query standards and naming conventions across teams.
| Dimension | Rating |
|---|---|
| Self Rating | 3.8 |
| Peer Rating | 4.0 |
| Org Rating | 3.6 |
Appreciation for Diversity¶
Openness to multiple database dialects and query paradigms.
| Dimension | Rating |
|---|---|
| Self Rating | 4.1 |
| Peer Rating | 4.0 |
| Org Rating | 3.8 |
Curiosity¶
Eagerness to benchmark new database engines and query optimization strategies.
| Dimension | Rating |
|---|---|
| Self Rating | 4.5 |
| Peer Rating | 4.3 |
| Org Rating | 4.2 |
Leadership¶
Capacity to mentor others on query best practices and CTE decomposition.
| Dimension | Rating |
|---|---|
| Self Rating | 3.6 |
| Peer Rating | 3.9 |
| Org Rating | 3.5 |