--- title: Context-Aware SQL description: How KTX turns reviewed context, grain, and relationship evidence into safe SQL for agents. --- ## Why query planning needs context Agents can generate SQL from schema alone, but safe analytics SQL needs more than table names. KTX uses reviewed context to understand grain, joins, measures, filters, and where aggregation must happen. Read this page as four mechanics: - context files feed the semantic engine; - evidence becomes a join graph with grain and relationship metadata; - review keeps the graph current; - query planning avoids fan-out and ambiguous joins. ## Where the semantic layer fits This planner is one subsystem inside KTX's broader context layer. It uses source YAML, wiki context, scan evidence, and provenance to make context actionable for SQL generation.

{"Context inputs"}

semantic-layer/

{"source YAML, measures, joins, grain"}

wiki/

{"business rules, definitions, caveats"}

raw-sources/

{"schema scans, keys, imported metadata"}

provenance

{"ingest decisions and review history"}

{"Semantic layer engine"}

Join graph

{"sources as nodes, joins as typed edges"}

Grain

{"row identity before aggregation"}

Measures

{"verified formulas and filters"}

Relationships

{"many_to_one, one_to_many, one_to_one"}

{"Safe query planning before SQL is generated."}

{"Agent workflows"}

{"Search sources and wiki pages"}

{"Compile trusted SQL"}

{"Explain metrics and provenance"}

{"Patch files and validate review"}

## Join graph A semantic source is a node. A join is a typed edge. KTX uses the graph to choose valid paths and detect row-multiplying joins before SQL is generated. | Relationship | Planning impact | |--------------|-----------------| | `many_to_one` | Usually safe for adding dimensions | | `one_to_many` | Can multiply measures and trigger fan-out handling | | `one_to_one` | Usually safe when keys are correct | | Equal-cost paths | Ambiguous unless aliases or explicit joins disambiguate |

customers

grain: customer_id

orders

grain: order_id

order_items

grain: order_id, line_id

orders -> customers: many_to_one

orders -> order_items: one_to_many

{"Example: "} {"refunds joins to orders. Used carefully, it explains net revenue. Joined naively, it can duplicate order-level measures."}

The graph is bidirectional for planning. If `orders -> customers` is `many_to_one`, the reverse path is `one_to_many`. ## Building and maintaining the graph KTX starts from evidence, writes reviewable source YAML, and treats the merged diff as the accepted graph. | Evidence | What it contributes | |----------|---------------------| | Declared primary keys | Initial row grain | | Declared foreign keys | Formal join candidates | | Inferred relationships | Edges when warehouses lack constraints | | dbt, MetricFlow, and LookML imports | Existing metrics, dimensions, explores, and joins | | Query history | Real join and filter patterns | | Analyst review | Final authority before context is merged |

{"Semantic maintenance loop"}

{"Every accepted correction becomes input to the next graph build."}

{"reviewed context"}

{"The accepted graph becomes the starting point for the next build."}

{"Step 1"}

{"ingest evidence"}

{"scan schemas, imports, and accepted files"}

{"Step 2"}

{"YAML diff"}

{"draft source, join, grain, and measure changes"}

{"Step 3"}

{"validation"}

{"check relationships, syntax, and unsafe query shapes"}

{"Step 4"}

{"analyst review"}

{"accept, edit, or reject generated context"}

{"Step 5"}

{"agent use"}

{"serve context to search, explain, and query"}

{"Step 6"}

{"corrections"}

{"agent and analyst fixes become new evidence"}

## Modeling problems Fan-out is the classic failure mode: an order-level measure joins to line-item rows before aggregation, so one order becomes many rows. | Problem | What happens | How KTX handles it | |---------|--------------|--------------------| | Order measure joins to `order_items` | `orders.revenue` repeats once per item | Detect `one_to_many` and pre-aggregate | | Two fact sources share `customers` | Measures multiply across the shared dimension | Treat as a chasm trap and plan each fact locally | | Filter crosses `one_to_many` | Filtering changes measure grain | Reject or localize the filter | | Equal-cost paths connect sources | Join choice is ambiguous | Prefer safer paths or require aliases | ## Execution planning The planner resolves sources, chooses a join tree, checks relationship paths, and picks a simple or aggregate-locality SQL shape. | Naive SQL shape | Semantic-layer SQL shape | |-----------------|--------------------------| | Join facts and dimensions first, then aggregate | Aggregate each fact source at its own grain, then join results | | Put every filter in one outer `WHERE` clause | Keep measure filters with the measure source when locality is needed | | Trust the shortest textual join path | Prefer safe relationship paths and reject disconnected sources | | Let dimension grain differ across facts | Raise when asymmetric dimensions would fan out another measure |

{"Fan-out handling"}

{"The same question planned before and after KTX preserves the measure grain."}

{"Unsafe shape"}

{"Join first, aggregate later"}

{`orders
  -> join order_items
  -> join customers

group by
  customer_segment

measure
  sum(orders.amount)`}

{"Order-level revenue is exposed to line-item fan-out before aggregation."}

{"KTX shape"}

{"Aggregate locally, then join"}

{`orders_agg as (
  select customer_id, sum(amount) revenue
  from orders
  group by customer_id
)
select customers.segment, sum(revenue)
from orders_agg
join customers`}

{"The measure is pre-aggregated at order grain before dimensions are joined."}

The result is structured planning: validated sources, typed relationships, graph search, fan-out detection, aggregate locality, and dialect transpilation. ## Agent usage notes Use this page when an agent needs to explain how KTX turns reviewed semantic context into SQL, why relationship metadata matters, or why a query was rejected as unsafe. | Agent task | Relevant section | Next page | |------------|------------------|-----------| | Explain why KTX asks for `grain` and relationship types | Join graph | [Writing Context](/docs/guides/writing-context) | | Diagnose duplicated measures after a join | Modeling problems | [ktx sl](/docs/cli-reference/ktx-sl) | | Explain safe SQL generation | Execution planning | [ktx sl](/docs/cli-reference/ktx-sl) | | Describe how semantic context stays current | Building and maintaining the graph | [Context as Code](/docs/concepts/context-as-code) |