Understand a table

Resolve a table name, read its metadata, walk how it is built, see what scanning it costs, and find tables built the same way.

Questions you can ask your agent:

  • "What is orders_daily_v, and which table is behind it?"
  • "Which workloads scan analytics.orders_fact the most, and does pruning work for them?"
  • "Is there another table that computes the same thing as analytics.orders_daily?"

Table names

Most tools take fqn: a 3-part db.schema.table or a 2-part schema.table name. A bare table name is rejected, except by resolve_table_reference. A 2-part name resolves exactly when you also pass default_database; otherwise it is matched by suffix, and several matches come back as {"ambiguous": true, "candidates": [...]}. A name that can't resolve returns {"error": ...}.

All sample responses use synthetic data and are shortened.

resolve_table_reference

Resolves any table or view reference and returns its metadata. For a view, also returns its definition and the physical tables behind it.

When to use: you have a partial name from SQL or a user, or you need to know whether a name is a table or a view.

Parameters
ParameterTypeRequiredDefaultMeaning
table_refstringYesA 1-, 2- or 3-part name.
default_databasestringNo""Pass only when you know the default database and the input is 2-part.

Returns one of:

  • Base table: fqn, table_type, row_count, bytes, cluster_key, total_partitions, partition_keys.
  • View: the same, plus definition (the view DDL) and physical_tables (each with fqn, row_count, bytes, cluster_key). A view known only from the views feed has null sizes and listed_in: "schema_view".
  • Ambiguous: ambiguous: true, up to 10 candidates (tables written in the last 7 days first, then by size), total_matches and truncated.
  • Not found: {"error": ...}.
Example

"What is orders_daily_v?"

{
  "fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY_V",
  "table_type": "VIEW",
  "definition": "CREATE VIEW orders_daily_v AS SELECT * FROM analytics.orders_daily WHERE is_current",
  "physical_tables": [{"fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY", "row_count": 720000, "bytes": 51200000, "cluster_key": null}]
}

table_detail

Returns a table's metadata.

When to use: you have a full or 2-part name and need size, cluster key or partitions.

Parameters
ParameterTypeRequiredDefaultMeaning
fqnstringYes2- or 3-part name.
default_databasestringNo""Prepended to a 2-part name for an exact match.

Returns fqn, table_type, row_count, bytes, cluster_key, total_partitions (null when your metadata feed doesn't report it), partition_keys (the columns a pruning predicate must use; [] when none is recorded) and is_iceberg (true for an Iceberg table, where Snowflake DDL such as a cluster key may not apply; null when not yet crawled). A view known only from the views feed returns table_type: "VIEW" with null sizes and its physical_tables.

Example

"How big is analytics.orders_fact and what's its cluster key?"

{
  "fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT",
  "table_type": "BASE TABLE",
  "row_count": 1400000000,
  "bytes": 1200000000000,
  "cluster_key": "LINEAR(ORDER_DATE)",
  "total_partitions": 3650,
  "partition_keys": [],
  "is_iceberg": false
}

table_producer_chain

Walks a table's producers upstream and returns every hop's producers, with their SQL, in one call.

When to use: judging whether a full-history rebuild can be made incremental. The bounded refresh window that proves the inputs don't change often sits two or more hops up.

Parameters
ParameterTypeRequiredDefaultMeaning
destination_fqnstringYesThe table. 2- or 3-part.
start_datestring (YYYY-MM-DD)YesInclusive.
end_datestring (YYYY-MM-DD)YesInclusive.
default_databasestringNo""For a 2-part name.
max_depthintegerNo3Hops to walk. Capped at 4.

The walk follows full-rebuild (CREATE ... AS) hops upstream. It stops at DELETE/MERGE producers, append-only producers, tables with no recorded producer, and its budgets. Returns {target, nodes, not_expanded, limits}:

  • nodes: one per table visited, with depth, read_by and producers (at most 10 per node, 40 in all). SQL keeps its head and tail within a budget; past it sample_query is null with sql_omitted: "budget".
  • not_expanded: upstream edges seen but not walked, with a reason. no_producer_recorded means raw or ingested here, not unchanging.
  • limits: the budgets used and whether SQL was cut.

The walk returns evidence, not conclusions. Judge whether inputs change from the SQL yourself.

Example

"Can analytics.orders_daily be built incrementally? Show me its producer chain for last week."

{
  "target": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY",
  "nodes": [
    {"table": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY", "depth": 0, "producers": [{"job_tag": "nightly_revenue &&& build_orders_daily &&& CREATE"}]},
    {"table": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT", "depth": 1, "read_by": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY", "producers": [{"job_tag": "nightly_revenue &&& load_orders &&& DELETE"}]}
  ],
  "not_expanded": [],
  "limits": {"max_depth": 3}
}

table_scan_cost

Snowflake only. Returns daily scan cost and pruning per table, query signature and filter columns.

When to use: "which workloads scan this table the most, and does pruning work for them?" The table_scan_analysis prompt builds on it.

Parameters
ParameterTypeRequiredDefaultMeaning
fqnstringNoall tablesTable filter. 2- or 3-part.
signaturestringNoall signaturesQuery signature filter.
start_datestring (YYYY-MM-DD)No30 days before end_dateInclusive.
end_datestring (YYYY-MM-DD)Nomost recent completed processing dayInclusive.
default_databasestringNo""For a 2-part name.
sizeintegerNo10Groups per page.
pageintegerNo0Zero-based page.

Groups are ordered by total scan cost across the range. Each row has processing_time, table_fqn, signature, filter_cols, execution_count, total_scan_cost, total_query_cost, avg_scan_pct (scan cost as a share of query cost) and avg_pruning_fraction (0 to 1; higher means more partitions pruned).

Use one broad date range and paginate, rather than one call per day or per signature.

Example

"Break down scan cost on analytics.orders_fact over the last month."

[
  {
    "processing_time": "2026-05-08T00:00:00Z",
    "table_fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT",
    "signature": "nightly_revenue &&& build_orders_daily",
    "filter_cols": "ORDER_DATE",
    "execution_count": 1,
    "total_scan_cost": 16.9,
    "total_query_cost": 18.42,
    "avg_scan_pct": 91.7,
    "avg_pruning_fraction": 0.0
  }
]

table_similarity

The single entry point for "which other tables are like this one?". Results are grouped by partner table.

When to use: looking for duplicate or consolidation candidates for one table. To see the evidence behind one partner, your client also lists similar_tables_by_transformation (parsed SQL) and similar_tables_by_plan (execution plans).

Parameters
ParameterTypeRequiredDefaultMeaning
fqnstringYes2- or 3-part name.
default_databasestringNo""For a 2-part name.
limitintegerNo8Partners per axis before merging.
min_jaccardnumberNo0.8Threshold for SQL evidence, 0.5 to 1.
min_cosinenumberNo0.9Threshold for plan evidence, 0 to 1. Not comparable to min_jaccard.

The comparison uses the table's producer SQL where it was parsed, which also gives a relation verdict. Where it wasn't (every Spark table, and a Snowflake table whose write was profiled), it uses the write's execution plan instead, with no verdict. There is no blended score.

Returns fqn, table_type, evidence, axes (each with a status: not_fingerprinted, no_features, empty or error when it found nothing), partners (each with fqn, table_type, relation, axes_fired, corroboration and per_axis), partner_count and how_to_read.

Example

"Is there another table like analytics.orders_daily?"

{
  "fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_DAILY",
  "evidence": "sql",
  "partners": [{"fqn": "ANALYTICS_DB.REPORTING.DAILY_ORDERS", "relation": "duplicate", "axes_fired": ["transformation"], "corroboration": 1}],
  "partner_count": 1
}

Related


Did this page help you?