Process documentation¶

document_process calls the configured LLM to produce three artefacts for any registered process and persist them in the feature store:

Artefact	Stored in	What it contains
Business logic	`FS_PROCESS_DOCUMENTATION_BUSINESS_LOGIC`	Markdown description of what the SQL does, written in business terms
Feature descriptions	`FS_PROCESS_DOCUMENTATION_FEATURES`	One row per feature column with a plain-language definition
EXPLAIN analysis	`FS_PROCESS_DOCUMENTATION_EXPLAIN`	Scored EXPLAIN with `USER_SCORE`, `OPTIMIZATION_SCORE`, `N_STEPS`, `N_SPOOL_OBJECTS`, raw plan

All three tables are VALIDTIME temporal — re-running the function overwrites the current row and preserves the history.

Synchronous call¶

import tdfs4ds
from tdfs4ds.genai import document_process

tdfs4ds.INSTRUCT_MODEL_PROVIDER = 'openai'
tdfs4ds.INSTRUCT_MODEL_MODEL    = 'gpt-4o'
tdfs4ds.INSTRUCT_MODEL_API_KEY  = os.getenv('OPENAI_API_KEY')

doc = document_process(
    process_id        = '<UUID>',
    show_sql_query    = True,
    show_explain_plan = True,
)

doc['DOCUMENTED_SQL']           # business logic + per-feature descriptions
doc['EXPLAIN_ANALYSIS']         # plain-text plan analysis
doc['USER_SCORE']               # 1–5, SQL author quality
doc['OPTIMIZATION_SCORE']       # 1–5, overall execution quality
doc['EXPLAIN_WARNINGS']         # list[str]
doc['EXPLAIN_RECOMMENDATIONS']  # list[str] split [You] / [DBA]

Resolve by view name¶

When you only have the qualified view name, use get_process_id:

from tdfs4ds.process_store.process_query_administration import get_process_id
process_id = get_process_id('"my_db"."FEAT_ENG_CUSTOMER"')

get_process_id strips quotes, accepts db.view or plain view names, and falls back to tdfs4ds.SCHEMA when no database prefix is given.

Incremental variant¶

document_process_incremental is identical to document_process but skips processes that already have a stored documentation and an unchanged SQL hash. It is the right call from a notebook that documents many processes in a loop:

from tdfs4ds.genai import document_process_incremental

for pid in tdfs4ds.process_catalog()[['PROCESS_ID']].to_pandas().PROCESS_ID:
    document_process_incremental(process_id=pid)

Full-store one-liner¶

from tdfs4ds.genai import document_feature_store_incremental

summary = document_feature_store_incremental(
    language     = 'English',
    force_update = False,
    upload       = True,
)
# summary keys: processes_documented, datasets_documented,
#               processes_skipped,    datasets_skipped

Objects are processed in dependency order (leaves first, roots last) so upstream context is always available when documenting downstream views.

Retrieve without re-running¶

from tdfs4ds.process_store.process_store_catalog_management import get_process_info

info = get_process_info(process_id)
print(info.get('DOCUMENTED_SQL'))
print(info.get('EXPLAIN_ANALYSIS'))

EXPLAIN score interpretation¶

Score	Meaning
5/5	Optimal use of PI and partitioning
3–4/5	Partial optimisation; some full-AMP / full-partition scans remain
1–2/5	Full-table scans, product joins, or spool-heavy operations

See EXPLAIN scoring for the full metric definitions and how the deterministic counters complement the LLM scores.

Programmatic SQL scoring¶

If you want to score raw SQL without registering a process:

from tdfs4ds.genai import document_sql_query_explain

scored = document_sql_query_explain(
    sql_query = "SELECT ... FROM ...",
    classify  = True,        # filter LLM false positives via deterministic signals
)

classify=False is the right choice when the score is consumed by another automated step (the query optimizer uses it that way internally).