`fs-analyze`¶

Full scalability analysis — lineage + EXPLAIN score + partition opportunities + FilterManager proposal.

name: "Feature Store: Analyze" description: "Full scalability analysis of a registered tdfs4ds process — builds the dependency graph (primary index + partition columns per source table), scores the EXPLAIN plan via LLM, detects partition opportunities in source tables that the current query does not exploit, and proposes a rewritten process view with a FilterManager. Orchestrates fs-lineage and fs-document in a single workflow." argument-hint: "[process-id]" allowed-tools: "Read, Edit, Write, Bash, Glob, Grep"

Perform a full scalability analysis of a registered tdfs4ds process. This skill orchestrates the same underlying calls as fs-lineage and fs-document, then adds a partition-opportunity analysis and — when relevant — proposes a complete FilterManager rewrite.

User context: $ARGUMENTS

Step	Skill	Why
Sub-step	`fs-lineage`	Same dependency graph, called in isolation when you don't need the LLM.
Sub-step	`fs-document`	Same EXPLAIN scoring, called in isolation when partition analysis is not needed.
Rewrite	`fs-filter`	Apply the partition-pruning rewrite recommended at the end of the analysis.
Deep dive	`td-explain`	Convert the raw EXPLAIN into a visual flow diagram with bottleneck highlighting.

Ask for any missing values before generating code: - Process ID — UUID of the process to analyse. - LLM provider + credentials — for the EXPLAIN score step.

Emit the following pattern, substituting real values. After the code runs, reason over the output and produce a written recommendation (see the Analysis section at the bottom).

import tdfs4ds                                  # reads the TDFS4DS_* settings of .env
tdfs4ds.reconnect_teradata(raise_error=True)    # teradataml context from TDFS4DS_TD_HOST / _USERNAME / _PASSWORD
tdfs4ds.connect(database='<DATABASE>', create_if_missing=True)
import teradataml as tdml
tdfs4ds.select_data_domain('<DATA_DOMAIN>')   # or set TDFS4DS_DATA_DOMAIN in .env

# LLM config (for EXPLAIN scoring).
# Preferred: set TDFS4DS_INSTRUCT_MODEL_* in .env — auto-loaded at import.
tdfs4ds.INSTRUCT_MODEL_PROVIDER = '<PROVIDER>'
tdfs4ds.INSTRUCT_MODEL_URL      = '<MODEL_URL>'
# API key: keep it in .env as TDFS4DS_INSTRUCT_MODEL_API_KEY (never in code)
tdfs4ds.INSTRUCT_MODEL_MODEL    = '<MODEL_NAME>'

PROCESS_ID = '<PROCESS_ID>'

# ===========================================================================
# STEP 1 — Retrieve process SQL  (same as fs-lineage)
# ===========================================================================
from tdfs4ds.process_store.process_store_catalog_management import get_process_info

process_info = get_process_info(PROCESS_ID)
sql = process_info['PROCESS_SQL']
print("=== Process SQL ===")
print(sql)

# ===========================================================================
# STEP 2 — Build dependency graph + collect PI / partition info per source
#           (same function as fs-lineage)
# ===========================================================================
from tdfs4ds.lineage import build_teradata_dependency_graph

graph = build_teradata_dependency_graph(sql_query=sql)

print("\n=== Source table analysis ===")
partition_candidates = {}   # {table_name: {pi: [...], part: [...]}}

for name, node in graph['nodes'].items():
    ti = node.get('table_info')
    if ti is None:
        continue
    pi   = ti.get('primary_index_columns', [])
    part = ti.get('partition_columns', [])
    lvls = ti.get('partitioning_levels', [])
    print(f"\n  {name}")
    print(f"    Primary Index   : {pi}")
    print(f"    Partition cols  : {part}")
    for lvl in lvls:
        print(f"      Level {lvl['level']}: {lvl['kind']} on {lvl['columns']}")
    if part:
        partition_candidates[name] = {'primary_index': pi, 'partition_columns': part,
                                      'partitioning_levels': lvls}

# ===========================================================================
# STEP 3 — EXPLAIN score  (same call as fs-document)
# ===========================================================================
from tdfs4ds.genai import document_process

doc = document_process(
    process_id=PROCESS_ID,
    show_sql_query=False,
    show_explain_plan=True,
)
user_score   = doc.get('USER_SCORE', 'N/A')
global_score = doc.get('OPTIMIZATION_SCORE', 'N/A')
print(f"\n=== EXPLAIN scores: user={user_score}/5  global={global_score}/5 ===")
print(doc.get('EXPLAIN_ANALYSIS', ''))

# ===========================================================================
# STEP 4 — Sankey visualisation  (same as fs-lineage)
# ===========================================================================
from tdfs4ds.lineage import plot_lineage_sankey, show_plotly_robust

fig = plot_lineage_sankey(graph, title=f'Lineage — {PROCESS_ID}')
fig.show()
# show_plotly_robust(fig, html_path='lineage_analysis.html')

Analysis — what to do after running the code¶

After executing, reason over the collected data and produce a written recommendation following this logic:

1. Check the EXPLAIN score¶

Two scores are returned by document_process: - user_score — quality of the SQL itself (what you, the data scientist, control) - global_score — overall execution quality including infrastructure (PI design, statistics, table partitioning)

Decision logic: - user_score 4–5: SQL is well-written. If global_score is low, this is a DBA/data-engineer concern. Document with fs-document and optionally flag for the DBA. - user_score 1–3: proceed to partition analysis — the SQL can be improved.

2. Inspect `partition_candidates`¶

For each source table that has partition_columns, check whether those columns appear in the process SQL's WHERE clause or JOIN predicates.

Not filtered in the SQL → the query scans all partitions. This is a scalability risk.
Already filtered → partitioning is exploited; score penalty likely comes from something else (e.g. spool redistribution).

3. Classify the partition opportunity¶

Based on the partition column types and number of distinct values:

Partition column type	Recommended FilterManager mode
Date / timestamp column only	Standard FilterManager — iterate one date at a time (or use TimeManager + roll_out)
Categorical column only	Standard FilterManager — iterate one category at a time
Date + categorical	Hybrid FilterManager — pre-compute (date × category) combinations

4. Propose the rewrite¶

When a partition opportunity is found, generate the following changes:

A. Rewrite the process view SQL to filter on the partition column(s) via scalar subqueries. Scalar subqueries let the optimizer resolve the partition value before scanning the source table:

# Original source query (no filter):
# SELECT ... FROM <source_db>.<source_table> A

# Rewritten to use FilterManager on partition column <PARTITION_COL>:
df_features_raw = tdml.DataFrame.from_query(f"""
    SELECT A.*
    FROM <source_db>.<source_table> A
    WHERE A.<PARTITION_COL> = (SELECT <PARTITION_COL> FROM {filtermanager.schema_name}.{filtermanager.view_name})
""")
# For hybrid (date + category):
# WHERE A.<DATE_COL> = (SELECT BUSINESS_DATE   FROM {filtermanager.schema_name}.{filtermanager.view_name})
#   AND A.<CAT_COL>  = (SELECT <PARTITION_COL> FROM {filtermanager.schema_name}.{filtermanager.view_name})

B. Re-crystallize the view under a new name (or replace the existing one):

from tdfs4ds.utils.lineage import crystallize_view

df_features_proc = crystallize_view(
    df_features_raw.groupby(...).agg(...),   # same feature engineering as before
    view_name='<VIEW_NAME>_FILTERED',
    schema_name='<DATABASE>',
)

C. Recreate the FilterManager and re-register:

from tdfs4ds.utils.filter_management import FilterManager
from tdfs4ds import upload_features

# Standard:
filtermanager = FilterManager(table_name='<FILTER_TABLE>', schema_name='<DATABASE>')
filtermanager.load_filter(source_data.groupby('<PARTITION_COL>').count()[['<PARTITION_COL>']])

# Hybrid:
# filtermanager.load_filter(filter_df, time_column='<DATE_COL>')

upload_features(
    df_features_proc,
    entity_id=[<ENTITY_COLUMNS>],
    feature_names=<FEATURE_NAMES>,
    metadata=<METADATA>,
    filtermanager=filtermanager,
)

Rules¶

The partition columns from build_teradata_dependency_graph come from the source table DDL — they tell you how Teradata physically stores the data. Filtering on those columns eliminates partition scans.
Primary index columns are used for AMP routing — filtering on the PI in a WHERE clause does not help (the PI is used in JOINs). For PI-only tables without partitions, a FilterManager on a high-cardinality column may still help by reducing per-run spool size.
Always re-run document_process after the rewrite to verify the EXPLAIN score improved.
Use fs-lineage independently for pure graph exploration without the LLM cost.
Use fs-document independently to document a process without running the full lineage analysis.

fs-analyze¶

Related skills (this skill orchestrates them)¶