tdfs4ds.agent.query_optimizer¶
Query Optimizer Agent — structured multi-step pipeline built around a self-contained skill.
Architecture¶
The optimization logic lives entirely in skill_optimize_query, a synchronous function
following the same pattern as the consumer agent skills (skill_search, skill_definition
etc.). The LangGraph StateGraph is a thin multi-turn wrapper that calls the skill and
persists conversation state across turns sharing the same thread_id.
Process-aware entry point
~~~~~~~~~~~~~~~~~~~~~~~~~
When a process_id is supplied, the skill calls
tdfs4ds.process_store.process_store_catalog_management.get_process_info to retrieve:
- The registered SQL view (PROCESS_SQL)
- Pre-existing EXPLAIN documentation (EXPLAIN_ANALYSIS, USER_SCORE,
OPTIMIZATION_SCORE, EXPLAIN_WARNINGS, EXPLAIN_RECOMMENDATIONS)
- Entity and feature column metadata
This avoids re-running the EXPLAIN LLM chain when an up-to-date analysis is already stored.
EXPLAIN analysis responsibility split
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tdfs4ds.genai.documentation.document_sql_query_explain returns a structured dict:
- explanation — plain-language plan description
- user_score — 1–5, quality of what the SQL author controls
- global_score — 1–5, overall execution quality including infrastructure
- warnings — prefixed [You] or [Data Engineer / DBA]
- recommendations— same prefixes
Only the [You]-prefixed items are actionable by the SQL author. The optimizer
uses this split to focus candidate rewrites on author-controllable improvements.
Native Teradata tools (no MCP required)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Seven read-only metadata functions replicate the tools exposed by the
teradata-mcp-minimal server as direct Python calls over the active teradataml
connection. No subprocess, no extra dependencies beyond teradataml.
Pipeline (strategy-based, executed inside skill_optimize_query)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. Gather process context (get_process_info — skipped if no process_id)
2. Analyze original EXPLAIN (document_sql_query_explain — or reuse stored)
3. Build lineage graph (tdfs4ds.build_teradata_dependency_graph)
→ Primary Index + partition columns per underlying object
4. Fetch DDL for referenced objects (_td_get_table_ddl / _td_get_view_ddl)
5. Plan strategies — combinatorial with technique variants (no LLM call):
For each [You] item, extracts technique hints from the recommendation text
(e.g. "CTE", "scalar subquery" → precompute category → CTE, CROSS JOIN,
and scalar variants). Then adds pairwise/group combinations.
Capped at QUERY_OPTIMIZER_MAX_CANDIDATES (default 10).
6. Execute each strategy — For each strategy sequentially:
a. Generate 1 candidate implementing the strategy (_generate_for_strategy_sync)
b. Analyze EXPLAIN → extract remaining [You] items
c. Refinement loop (up to QUERY_OPTIMIZER_MAX_REFINE_ROUNDS, default 3):
each round asks the LLM to fix the remaining [You] items from the
previous round (_refine_candidate_sync)
d. Early stop the entire pipeline when no [You] items remain (5/5 score)
e. Compaction pass (on 5/5): asks the LLM to flatten unnecessary nesting
and simplify the SQL structure; accepts only if the score holds.
7. LLM selects best plan (structured output: PlanComparison)
8. FilterManager check (pure Python, tdfs4ds.FilterManager)
Quick start::
import tdfs4ds
tdfs4ds.INSTRUCT_MODEL_PROVIDER = "vllm"
tdfs4ds.INSTRUCT_MODEL_MODEL = "..."
# requires an active tdml.create_context() connection
result = tdfs4ds.query_optimizer(
"SELECT * FROM dw.orders o JOIN dw.customers c ON o.cust_id = c.id",
thread_id="session-01",
)
print(result["answer"])
print("Best SQL:", result["best_sql"])
# Process-aware: pull SQL + stored analysis from the process catalog
result = tdfs4ds.query_optimizer(
process_id="550e8400-...",
thread_id="session-02",
)
module-attribute
¶
_RESERVED_ALIAS_WORDS: set = {'threshold', 'value', 'count', 'date', 'position', 'type', 'name', 'time', 'period', 'year', 'month', 'day', 'hour', 'minute', 'second', 'result', 'default', 'key', 'index', 'check', 'grant', 'role'}
module-attribute
¶
_TECHNIQUE_CATEGORIES: dict[str, list[tuple[str, str]]] = {'precompute': [('cte', 'precompute the value as a CTE (WITH clause)'), ('crossjoin', 'precompute via CROSS JOIN to a single-row scalar subquery'), ('scalar', 'embed as an inline scalar subquery in the expression')], 'window': [('window', 'use window/analytical functions (e.g., AVG() OVER()) to avoid self-join')], 'singlepass': [('singlepass', 'combine into a single aggregation pass with conditional expressions (CASE WHEN)')]}
module-attribute
¶
_TECHNIQUE_DETECTORS: list[tuple[str, str]] = [('\\bCTE\\b|\\bWITH\\s+clause\\b|\\bcommon\\s+table\\s+expression\\b', 'precompute'), ('\\bscalar\\s+subquery\\b|\\binline\\s+scalar\\b', 'precompute'), ('\\bCROSS\\s+JOIN\\b', 'precompute'), ('\\bprecompute[d]?\\b|\\bcompute.{0,15}once\\b', 'precompute'), ('\\bwindow\\s+function\\b|\\banalytical?\\s+function\\b', 'window'), ('\\bsingle[\\s-]pass\\b|\\bcombine.{0,20}aggregat|\\bone[\\s-]pass\\b', 'singlepass'), ('\\btable\\s+twice\\b|\\bredundant\\s+(work|scan)\\b|\\btwo\\b.{0,20}\\bscan', 'singlepass'), ('\\brecompute\\b.{0,30}\\bevery\\s+row\\b', 'singlepass')]
Bases: BaseModel
class-attribute
instance-attribute
¶
class-attribute
instance-attribute
¶
strategy: str = Field(..., description="Short label for the strategy applied, e.g. 'partition_pruning', 'join_reorder', 'pi_equality_filter', 'cast_removal', 'cte_pre_filter'")
class-attribute
instance-attribute
¶
class-attribute
instance-attribute
¶
business_assumptions: str = Field(default='', description="Describe any domain assumption your rewrite makes about the data or business rules (e.g. 'Assumes transactions older than 2 years are not relevant to current analysis', 'Assumes the ACTIVE_FLAG column reliably filters inactive records'). Leave EMPTY for purely structural rewrites that make no assumption about data scope or business meaning.")
Bases: BaseModel
class-attribute
instance-attribute
¶
candidates: list[CandidateRewrite] = Field(default_factory=list, description='Candidate rewrites ordered from most to least promising. Return an empty list if the original query is already optimal.')
Bases: BaseModel
class-attribute
instance-attribute
¶
best_index: int = Field(..., description='0-based LIST position of the best candidate as shown in the prompt (0 = first candidate listed, 1 = second, …). Set to -1 if the original query has the best plan.')
class-attribute
instance-attribute
¶
class-attribute
instance-attribute
¶
business_logic_preserved: bool = Field(..., description='True only if the selected SQL produces the same result set as the original')
class-attribute
instance-attribute
¶
Bases: BaseModel
class-attribute
instance-attribute
¶
class-attribute
instance-attribute
¶
class-attribute
instance-attribute
¶
targets: list[str] = Field(default_factory=list, description='[You] warning or recommendation items this strategy addresses (quoted verbatim from the EXPLAIN analysis)')
Bases: BaseModel
class-attribute
instance-attribute
¶
strategies: list[StrategySpec] = Field(default_factory=list, description='Ordered list of distinct strategies, most to least promising. Empty if the query is already optimal.')
Bases: BaseCallbackHandler
Accumulates token usage across LLM calls within a skill run.
Bases: TypedDict
Return the prompt template body (after frontmatter) for skill name.
The returned string may contain {variable} placeholders intended for
use with 
meth:str.format (pure-Python prompts) or passed directly to
ChatPromptTemplate.from_template (LangChain prompts).
Parameters¶
name : str
Skill identifier, e.g. 'qo-candidates'.
Returns¶
str Prompt body with frontmatter stripped.
llm.with_structured_output(schema), resilient to a gateway that does
not enforce the schema it was asked for.
The single seam every structured-output call site goes through — one in the classifier, one in EXPLAIN, seven in the query optimizer, all of which fail identically on a relaying gateway. A new call site added directly against the model would silently miss this (there is a test that forbids it).
An explicit method= from the caller always wins, and a configured
STRUCTURED_OUTPUT_METHOD short-circuits the probe.
Wrapper around _get_llm that attaches the active token tracker.
Return the names of all databases visible to the connected user.
Return 'TABLE', 'VIEW', or 'UNKNOWN'.
Return the CREATE TABLE DDL for database.table_name.
Return the CREATE VIEW DDL for database.view_name.
Return the Teradata EXPLAIN plan for query as a single text block.
Return a compact Teradata SQL compliance guide for injection into LLM prompts.
This is the single place to add new syntax rules when a recurring class of EXPLAIN error is observed. Keep entries short — one ✓ / ✗ line per pattern. Group by topic so it stays scannable.
Build the dependency graph via tdfs4ds.build_teradata_dependency_graph and return
a flat dict of per-object metadata keyed by canonical node name::
{'"db"."tbl"': {
"object_type": str,
"primary_index_columns": list[str],
"partition_columns": list[str],
"database": str,
"object": str,
}}
Requires the active teradataml connection. Returns {} on failure.
Extract unique (database, name) pairs from FROM / JOIN clauses.
Try TABLE DDL first, fall back to VIEW DDL.
Determine whether tdfs4ds.FilterManager would benefit this query.
An object is flagged when it has partition_columns in the lineage graph but
none of those columns appear (case-insensitively) in the query text.
Returns::
{
"applicable": bool,
"objects": {node_name: {"database", "object_name", "partition_columns"}},
"suggestion": str (Markdown, empty when not applicable),
}
Summarise the last 3 exchanges for LLM prompt context.
Re-indent CTE body blocks to use consistent 4-space indentation.
Detects lines ending with AS (CTE header) followed by a ( on the
next line. The body between ( and the matching ) is re-indented
to 4 spaces while preserving relative indentation within the block
(e.g. CASE/WHEN nesting).
Non-CTE queries pass through unchanged.
Pretty-print SQL using sqlparse with post-processing for CTE readability.
sqlparse produces poor CTE formatting: deep alignment for continuation
CTEs, REPLACE VIEW ... AS WITH on a single line. Post-processing fixes:
- Newline after
*/block comments. REPLACE VIEW ... AS WITH→... AS\nWITH.- Deep CTE alignment (50+ spaces) collapsed to root level.
- CTE body blocks re-indented to consistent 4-space indent while preserving relative indentation within each block (e.g. CASE/WHEN).
Close any fenced code block the LLM forgot to close before a new section.
Some LLMs write prose text (Key Changes, section headings) inside the
opening sql ... block instead of after it. This function inserts
a closing ` whenever a Markdown section heading (## /# ``) is
encountered while a fence is still open, and appends a final closing
fence if the text ends inside one.
Return the first sentence of text, capped at max_chars.
Format a score value for display, returning '—' for None.
Format an integer metric for display, returning '—' for None.
Format a document_sql_query_explain result dict for use in LLM prompts.
_compute_score_delta(original_analysis: dict, candidate_analyses: list, comparison: 'PlanComparison') -> dict
Compute score improvement between the original query and the best rewrite.
Returns a dict with score deltas for user_score, global_score,
n_steps, and n_spool_objects between original and best rewrite.
Run document_sql_query_explain for structured EXPLAIN analysis.
Falls back to raw EXPLAIN text on any error.
classify=False by default: the optimizer uses raw [You] counts so
that structural issues (redundant scans, nesting) drive simplification
and candidate strategies. The LLM classification runs only on the
final public-facing score (via document_process_incremental).
_generate_candidates_sync(sql_query: str, user_request: str, original_analysis: dict, ddls: dict[str, str], lineage_info: dict, history: list, extra_context: str = '', max_override: Optional[int] = None) -> CandidatesOutput
Synchronous candidate generation via structured output.
history is accepted for API compatibility but intentionally ignored here:
candidate generation is a self-contained step and including prior conversation
messages (which may contain full SQL rewrites) only bloats the prompt.
Synchronous plan comparison via structured output.
_generate_summary_sync(user_request: str, original_analysis: dict, comparison: 'PlanComparison', score_delta: Optional[dict], candidate_count: int) -> str
Generate a 2-4 sentence executive summary via a short LLM call.
_plan_strategies_sync(sql_query: str, user_request: str, original_analysis: dict, ddls: dict[str, str], lineage_info: dict, max_strategies: int) -> StrategiesOutput
_generate_for_strategy_sync(sql_query: str, strategy: 'StrategySpec', user_request: str, original_analysis: dict, ddls: dict[str, str], lineage_info: dict) -> CandidateRewrite
Generate exactly one candidate SQL rewrite for a specific strategy.
Unlike _generate_candidates_sync (generic prompt, up to N candidates),
this function constrains the LLM to produce one rewrite that strictly
follows the given strategy description and targets.
_refine_candidate_sync(sql_query: str, remaining_you_items: list[str], strategy_name: str, user_request: str, ddls: dict[str, str], lineage_info: Optional[dict] = None) -> Optional[CandidateRewrite]
Refine a candidate SQL to fix remaining [You] items without changing the strategy.
remaining_you_items should already have the [You] prefix stripped.
lineage_info provides PI + partition columns so the LLM can make
informed fixes for index-related warnings.
Ask the LLM to produce the most compact, readable form of the SQL.
Flattens unnecessary nesting, merges derived-table layers into CTEs, inlines date arithmetic, and removes identity wrappers — while preserving exact business logic (same result set).
Used as the LLM pass in skill_simplify_query.
Returns None if the LLM cannot simplify further.
_generate_report_sync(sql_query: str, user_request: str, original_analysis: dict, lineage_info: dict, candidate_analyses: list[dict], comparison: PlanComparison, best_sql: str, fm_suggestion: dict, process_info: Optional[dict], score_delta: Optional[dict] = None, strategy_plan: Optional[StrategiesOutput] = None, simplify_result: Optional[dict] = None, original_raw_sql: Optional[str] = None, token_usage: Optional[dict] = None) -> str
Assemble the Markdown optimisation report from structured data.
Only the ## Summary section uses a short LLM call (~100 tokens output). All other sections are built deterministically from the pipeline outputs, avoiding the token-limit failures that occur when the full report is LLM-generated.
Collapse whitespace and lowercase for similarity comparison.
Split a comma-separated SELECT column list, respecting paren depth and quotes.
Return True when position pos in text is the start of a standalone FROM keyword.
Return the index of the first depth-0 FROM keyword starting at start, or -1.
Replace MySQL/generic backtick identifiers with Teradata double-quote identifiers.
LLMs occasionally produce `column_name` style quoting (MySQL habit) which
Teradata rejects with Error 3707. A simple regex substitution is safe here because
we never generate SQL that contains backtick characters for any other purpose.
Replace (SELECT col FROM derived_alias) → derived_alias.col.
Teradata resolves the FROM clause of a scalar subquery against the database catalog, not against derived-table aliases visible in the enclosing FROM clause. The correct pattern is to reference the column directly: derived_alias.col_name.
Algorithm
- Collect all derived-table alias names defined in the SQL
(pattern:
) alias_namefollowed by whitespace or a SQL keyword). - Replace every
(SELECT single_col FROM alias)where alias is a known derived-table alias withalias.single_col.
Complements _fix_reserved_word_alias: both are applied together so that
e.g. (SELECT threshold FROM hvt) is first rewritten to hvt.threshold
and then threshold as a reserved-word alias is renamed if needed.
Deterministically rename a Teradata reserved word used as a column alias.
Teradata Error 3706 sometimes reads
"Syntax error: expected something between '
when a reserved word (e.g. THRESHOLD, DATE, PERIOD) is used as a bare alias.
This function extracts the offending word from the error message and renames
every occurrence (alias definition AND bare column references) to <word>_col.
The rename is a global word-boundary substitution so it covers both
... AS threshold and Transaction_Amount > threshold.
Returns the repaired SQL, or None if the error pattern does not match.
_fix_syntax_error_sync(sql: str, error_message: str, llm, max_fix_attempts: int = 3) -> Optional[str]
Repair a Teradata SQL syntax error, first with deterministic checks then via LLM.
Deterministic pass (no LLM cost):
1. Reserved-word alias — detects the "expected … and the '
LLM pass (up to max_fix_attempts retries): Asks the LLM to fix only the syntax, not the optimization logic. The deterministic post-processing (backtick sanitization, alias injection) is applied to the LLM's output before returning so the caller can go straight to EXPLAIN.
Returns the repaired SQL string, or None if all attempts fail or raise.
Add missing aliases to unaliased derived tables in FROM/JOIN clauses.
Teradata requires every inline view (subquery used as a derived table) to have an alias immediately after the closing parenthesis. The LLM sometimes omits the alias when simplifying nested queries, causing Error 3707: "expected something like a name … between ')' and ';'".
Algorithm: walk the SQL tracking parenthesis depth (skipping string literals),
record every ( that immediately follows FROM or JOIN, then when the matching
) is found check whether it is followed by an SQL keyword or end-of-input
instead of an identifier. If so, inject _dt0, _dt1, … as the alias.
Deterministically collapse a redundant identity-wrapper SELECT.
Targets the pattern::
[DDL] SELECT col1 [AS "col1"], ..., 'lit' AS extra
FROM ( <inner_query> ) AS <alias>
where the outer SELECT has no WHERE / GROUP BY / HAVING / ORDER BY and every
column is either a same-name alias ("x" AS "x") or a literal constant
('v' AS extra).
Literal injection into an aggregation query is safe: SQL constants do not belong in GROUP BY and injecting them at the END of the SELECT list does not shift the positional indices used by any GROUP BY 1, 2, … already present in the inner query.
Returns the original SQL unchanged when the pattern is not clearly recognized.
Return True when two SQL strings are essentially the same query.
Uses exact match after normalisation first (fast path), then falls back to difflib similarity ratio so cosmetic reformats (added aliases, re-indented subqueries) are also detected.
Return the structural skeleton of a SQL string for context feedback.
Skips the SELECT column list (which is identical across all candidates and wastes tokens) and returns instead: - The WITH block if present (CTE names + first line of each CTE body) - Everything from the first top-level FROM keyword onwards
This makes the preview directly comparable across candidates at the level where structural differences actually exist.
_build_attempt_context(attempt_history: list[dict], original_user_score: Optional[int] = None) -> str
Format per-attempt feedback lines into a compact context block.
Improving candidates (score > original) are NOT added to the forbidden list — instead they are surfaced as 'current best' with their remaining [You] issues so the next attempt can build on them rather than abandoning the approach.
Non-improving candidates (score <= original), near-duplicates, and EXPLAIN failures ARE added to the forbidden list.
Extract [You]-prefixed warnings + recommendations, with the prefix stripped.
Remove near-duplicate [You] items using token-overlap similarity.
EXPLAIN warnings and recommendations often describe the same issue in slightly different wording (e.g. a warning says "correlated subquery forces recomputation" and the recommendation says "replace the correlated subquery with a CTE"). Keeping both produces duplicate strategies that waste LLM calls.
For each pair, strip SQL code blocks (which add noise), tokenize the prose, and compute the overlap coefficient (|A∩B| / min(|A|, |B|)). This metric works well for short-vs-long pairs (a brief warning vs. a detailed recommendation). When two items exceed threshold, the shorter one is dropped (the longer one typically carries more context).
Remove block comments (/* ... */) and line comments (-- ...) from SQL.
The LLM is instructed to produce comment-free SQL but sometimes ignores
that rule. Stripping comments prevents formatting issues (sqlparse
jams */ onto the same line as the next token) and avoids polluting
the EXPLAIN analysis with irrelevant text.
Rename Teradata reserved words used as bare identifiers.
Detects reserved words in three contexts:
- Column alias: AS threshold
- CTE name: WITH threshold AS
- Derived-table alias: ) threshold (after closing paren)
When found, renames every occurrence of that word globally (word-boundary
safe) to <word>_val.
Word-boundary matching ensures compound identifiers (e.g. Date_transaction,
CURRENT_DATE) are not affected.
Apply all deterministic SQL sanitization passes in sequence.
- Strip block/line comments (LLM sometimes adds them despite the prompt)
- Proactively rename reserved-word aliases (
AS threshold→AS threshold_val) - Backtick identifiers → double-quote (Teradata-safe)
- Missing derived-table aliases injected
- Identity wrapper flattened
- Scalar subquery → direct derived-table reference
Explicitly bind every provably-unique bare column to its relation alias.
This runs BEFORE any structural rewrite. If a query compiles, every bare column in it already resolves uniquely in its own scope — Teradata rejects ambiguity outright — so the correct qualification is derivable, not a guess. Writing it down removes the degree of freedom that makes flattening dangerous: once scopes merge, an unqualified column can silently rebind to a different relation, or become ambiguous and be "repaired" by the LLM picking an alias at random.
The qualified SQL is EXPLAIN-checked before use. Note this is a compile check guarding against a bug in our own parser — NOT a plan comparison. Plan equality would be the wrong oracle here: a wrongly-bound column very often yields an identical plan (same joins, same spools), so it would miss the error it was meant to catch, while estimated row counts drift for reasons unrelated to semantics and would cause false rejections.
Returns (sql, report_dict|None). On any failure the input is returned
unchanged — the worst case is the previous behaviour.
Reject a rewrite that structurally does LESS than the original.
EXPLAIN proves the rewrite compiles; user_score proves it plans well.
Neither says it still means the same thing — and the score is actively
anti-correlated with safety, since dropping a predicate or a DISTINCT
removes [You] items and therefore raises the score.
Returns (ok, violations). ok=False means at least one blocking
structural difference was found.
Analyze EXPLAIN; if syntax error, attempt repair and re-analyze.
Returns (final_sql, analysis) — analysis["user_score"] is None
when both the original and the repaired SQL failed.
Extract SQL technique variants from a [You] item's text.
When the text mentions a technique in a category (e.g. "CTE" triggers the precompute category), all techniques in that category are returned so the strategy generator can offer multiple structural approaches.
Returns a list of (suffix, hint_description) tuples. An empty list
means no actionable technique was detected — the strategy should be
generated without a technique constraint.
_generate_combinatorial_strategies(you_items: list[str], max_strategies: int) -> list['StrategySpec']
Build strategies by combinatorially addressing [You] items.
Produces strategies in three tiers:
- Tier 1 — technique variants (singles): for each [You] item, extract technique hints from its text (e.g. "CTE", "CROSS JOIN", "scalar subquery"). When hints are found, generate one strategy per technique so the LLM produces structurally diverse SQL. When no hints are detected, one unconstrained strategy is generated.
- Tier 2 — pairwise: one strategy per pair of [You] items — fix two simultaneously.
- Tier 3+ — groups: triplets, quads, … up to
fix-all.
The function stops as soon as max_strategies is reached, prioritising technique diversity in the singles tier.
Refinement rounds within each strategy will address any remaining [You] items that don't disappear after the targeted fix.
skill_generate_candidates(sql_query: str, user_request: str, original_analysis: dict, ddls: dict, lineage_info: dict, history: Optional[list]) -> tuple[list[dict], list[dict], Optional['StrategiesOutput']]
Strategy-based candidate generation with multi-round refinement.
Pipeline¶
- Plan strategies — combinatorial: one strategy per [You] item
(sequential), then one per pair (simultaneous), then triplets, etc.
No LLM call needed; capped at
QUERY_OPTIMIZER_MAX_CANDIDATES. - Execute each strategy — For each strategy sequentially:
a. Generate one candidate implementing that strategy.
b. EXPLAIN the candidate → extract remaining
[You]items. c. If[You]items remain, refine the candidate (up toQUERY_OPTIMIZER_MAX_REFINE_ROUNDSrounds), each round targeting the[You]items from the previous round. d. Early stop the entire pipeline when a strategy achieves user_score = 5/5 (no[You]items remaining).
Returns¶
candidate_analyses : list[dict]
One entry per strategy executed. Each has index, sql,
strategy, strategy_description, strategy_targets,
rationale, analysis, and rounds (list of per-round dicts).
steps : list[dict]
Pipeline step records for skill_optimize_query.
strategy_plan : StrategiesOutput | None
The raw plan returned by the strategy-planning LLM call (None
when the query was already optimal and no strategies were generated).
skill_simplify_query(sql_query: Optional[str] = None, process_id: Optional[str] = None, max_fix_attempts: int = 3) -> dict
Simplify a Teradata SQL query by removing unnecessary nesting.
Applies deterministic passes (comment stripping, identity-wrapper flattening, reserved-word alias fixes) followed by an LLM pass that merges derived-table layers into CTEs and inlines date arithmetic.
The simplified SQL is validated with EXPLAIN; if the score regresses below the original, the deterministic-only version is returned.
Can be used standalone or as a pre-step in skill_optimize_query.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sql_query
|
Optional[str]
|
Raw Teradata SQL to simplify. |
None
|
process_id
|
Optional[str]
|
UUID of a registered tdfs4ds process (fetches SQL from catalog). |
None
|
max_fix_attempts
|
int
|
Max syntax-error repair retries for the LLM output. |
3
|
Returns:
| Type | Description |
|---|---|
dict
|
dict with keys |
dict
|
|
Synchronous entry point for SQL simplification.
Convenience wrapper around skill_simplify_query for scripts and
notebooks. No LangGraph — single-shot operation.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sql_query
|
Optional[str]
|
Raw Teradata SQL to simplify. |
None
|
process_id
|
Optional[str]
|
UUID of a registered tdfs4ds process. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
dict with keys |
dict
|
|
skill_optimize_query(sql_query: Optional[str] = None, process_id: Optional[str] = None, user_request: str = 'Optimize this Teradata SQL query for performance.', history: Optional[list] = None) -> dict
Analyse and rewrite a Teradata SQL query for better performance.
Either sql_query or process_id must be provided. When process_id is given, the SQL and any existing EXPLAIN documentation are pulled from the tdfs4ds process catalog, avoiding a redundant LLM call.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sql_query
|
Optional[str]
|
Raw Teradata SELECT statement to optimise. |
None
|
process_id
|
Optional[str]
|
UUID of a registered tdfs4ds process. When supplied, the
SQL view DDL and stored EXPLAIN analysis are fetched from the
catalog. If |
None
|
user_request
|
str
|
Optional context sentence describing the optimisation goal (surfaced in the report and fed to the LLM). |
'Optimize this Teradata SQL query for performance.'
|
history
|
Optional[list]
|
Prior LangGraph messages for multi-turn context. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
dict with keys:
|
Raises:
| Type | Description |
|---|---|
ValueError
|
If neither sql_query nor process_id is provided. |
async
¶
Build an HTML score-comparison widget from a score_delta dict.
Render an optimization result dict in a Jupyter notebook cell.
Displays a score comparison widget (before vs. after user/global scores) at
the top, then converts the Markdown answer to HTML using the markdown
package (with fenced_code, tables, and sane_lists extensions) and
renders it inside a styled container via IPython.display.HTML.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
result
|
dict
|
Dict returned by |
required |
async
¶
aquery_optimizer(sql_query: Optional[str] = None, thread_id: str = 'default', process_id: Optional[str] = None) -> dict
Async entry point — recommended inside Jupyter notebooks and async frameworks.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sql_query
|
Optional[str]
|
A Teradata SQL statement to optimise, or a follow-up question
referencing a previously optimised query in the same |
None
|
thread_id
|
str
|
Conversation thread identifier for multi-turn memory. |
'default'
|
process_id
|
Optional[str]
|
UUID of a registered tdfs4ds process. When supplied, the SQL and any stored EXPLAIN documentation are fetched from the catalog. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
dict with keys |
dict
|
|
dict
|
|
query_optimizer(sql_query: Optional[str] = None, thread_id: str = 'default', process_id: Optional[str] = None) -> dict
Synchronous entry point — safe to call from scripts and Jupyter notebooks.
When an event loop is already running (Jupyter / IPython) the pipeline executes in a background thread to avoid event-loop conflicts.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sql_query
|
Optional[str]
|
A Teradata SQL statement to optimise, or a follow-up question
referencing a previously optimised query in the same |
None
|
thread_id
|
str
|
Conversation thread identifier for multi-turn memory. |
'default'
|
process_id
|
Optional[str]
|
UUID of a registered tdfs4ds process. When supplied, the SQL and any stored EXPLAIN documentation are fetched from the catalog. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
dict with keys:
|