Table & reference tools

Tools for warehouse metadata and the queries hitting a table: look up a table or view by fully-qualified name, resolve a partial or ambiguous reference into a canonical FQN, list the queries that read or produce a table, trace a table's upstream/downstream neighbor tables, inspect per-column schema and cardinality, and find tables that are similar by column set, inputs, consumers, or build logic (locally or in warehouse-wide clusters). For column-usage frequency and table storage-overview metrics, see Table & Column Usage.

All table tools accept a 3-part (db.schema.table) or 2-part (schema.table) name; a bare table name is rejected. A 2-part name resolves exactly when a default database is supplied, otherwise it is suffix-matched (multiple hits return as ambiguous with candidate FQNs).

All sample responses below use synthetic data.

table_detail

Returns physical metadata for a table or view by fully-qualified name. Accepts three-part (db.schema.table) or two-part (schema.table) names.

Sample call

"How big is analytics.orders_fact?"

Sample response (base table)

{
  "table_type": "TABLE",
  "row_count": 1400000000,
  "bytes": 3100000000000,
  "cluster_key": "(order_date, region)"
}

Not found

{ "error": "No table found for fqn='ANALYTICS_DB.ANALYTICS.DOES_NOT_EXIST'" }

resolve_table_reference

Canonicalizes a 1-, 2-, or 3-part name to a fully-qualified table or view, returning physical metadata and (for views) the SQL definition and underlying physical sources.

Resolution behavior

• 3-part FQN → exact match
• 2-part with a known default database → concatenated then exact match
• Otherwise → suffix match; multiple hits return as ambiguous with candidate FQNs

Sample call

"What does orders actually refer to?"

Sample response — base table

{
  "input": "orders_fact",
  "fqn": "analytics_db.analytics.orders_fact",
  "table_type": "TABLE",
  "row_count": 1400000000,
  "bytes": 3100000000000,
  "cluster_key": "(order_date, region)"
}

Sample response — view

{
  "input": "analytics.orders_recent",
  "fqn": "analytics_db.analytics.orders_recent",
  "table_type": "VIEW",
  "row_count": null,
  "bytes": null,
  "cluster_key": null,
  "definition": "SELECT * FROM analytics_db.analytics.orders_fact WHERE order_date >= CURRENT_DATE - 30",
  "definition_truncated": false,
  "definition_full_chars": 84,
  "definition_distinct_versions": 1,
  "physical_tables": [
    {
      "fqn": "analytics_db.analytics.orders_fact",
      "row_count": 1400000000,
      "bytes": 3100000000000,
      "cluster_key": "(order_date, region)"
    }
  ]
}

View definitions are capped at 16,000 characters; when a definition is longer, definition_truncated is true and definition_full_chars reports the full length.

Sample response — ambiguous

{
  "input": "orders",
  "ambiguous": true,
  "candidates": [
    {"fqn": "analytics_db.analytics.orders_fact", "table_type": "TABLE", "row_count": 1400000000, "bytes": 3100000000000, "cluster_key": "(order_date, region)"},
    {"fqn": "analytics_db.staging.orders_raw", "table_type": "TABLE", "row_count": 1400000000, "bytes": 4200000000000, "cluster_key": null}
  ]
}

Sample response — not found

{
  "input": "does_not_exist",
  "error": "No table found for 'does_not_exist'"
}

table_reader_queries

Returns the queries that read a table within a processing-time date range, ordered by cost (highest first), 20 rows per page. Answers "what is actually running against this table?" — the read workloads any clustering, partitioning, or schema change will affect. For the queries that build a table, use table_producers.

• fqn — the table whose readers to list.
• start_date / end_date — inclusive YYYY-MM-DD bounds.
• default_database — optional; prepended to a 2-part fqn for exact resolution.
• page — optional, zero-based.

Sample call

"Which queries read analytics.orders_fact between 2026-05-22 and 2026-05-29, and which cost the most?"

Sample response

[
  { "query_id": "01b2f9a4-7c3d-4e21-9a01-2f5b8c0d1e34", "cost": 12.47, "job_id": "job_8f3c1d", "processing_time": "2026-05-28T00:00:00Z", "signature": "a1c9e7f2" },
  { "query_id": "04e7c211-1b88-4f0a-8d22-6a90fb3c7d10", "cost": 3.10, "job_id": "job_2ab990", "processing_time": "2026-05-27T00:00:00Z", "signature": "9f02bd55" }
]

Sample response — ambiguous

{
  "ambiguous": true,
  "candidates": ["analytics_db.analytics.orders_fact", "analytics_db.staging.orders_raw"]
}

table_producers

Returns representative examples of the SQL statements that produce (build) a table — the answer to "how is this table built?". It captures incremental writers (INSERT / DELETE / MERGE) as well as full rebuilds, so it works for tables maintained incrementally, not just those rebuilt from scratch. Returns the latest statement per producer (one row per job_tag), up to 20, ordered by day_cost descending.

• destination_fqn — the table whose producers to list.
• start_date / end_date — inclusive YYYY-MM-DD bounds.
• default_database — optional; prepended to a 2-part name for exact resolution.

The statement type (INSERT / DELETE / MERGE / …) is the last &&& segment of job_tag. An empty list means no producer was recorded for the table in the range.

Sample call

"How is analytics.orders_daily_agg built?"

Sample response

[
  {
    "job_tag": "analytics_dag&&&orders_daily_agg&&&INSERT",
    "job_id": "job_2ab990",
    "sample_query": "INSERT INTO analytics.orders_daily_agg SELECT order_date, region, SUM(amount) FROM analytics.orders_fact WHERE order_date = CURRENT_DATE - 1 GROUP BY 1, 2",
    "sample_query_id": "04e7c211-1b88-4f0a-8d22-6a90fb3c7d10",
    "day_cost": 8.42,
    "day_count": 1
  }
]

table_producer_chain

Walks a table's producer lineage upstream and returns every hop's producer SQL in a single call — the multi-hop version of table_producers. Use it when judging whether a full-history rebuild can be made incremental: the bounded refresh window that proves the inputs immutable (e.g. a trailing DELETE … WHERE ds BETWEEN on an upstream) often sits 2+ hops up. The walk follows full-rebuild (CTAS) hops upstream via each statement's inputs and stops at DELETE/MERGE producers, append-only producers, tables with no recorded producer, and its traversal budgets. Intra-job staging tables are folded into their final table's node.

• destination_fqn — the table to start from.
• start_date / end_date — inclusive YYYY-MM-DD bounds.
• default_database — optional; prepended to a 2-part name for exact resolution.
• max_depth — optional; hops to walk (default 3, capped at 4).

Returns {target, nodes, not_expanded, limits}. Each nodes entry is one visited table (table, depth, read_by — the downstream table whose producer reads it, null for the target — and producers, same row shape as table_producers). not_expanded lists upstream edges seen but not walked, each with a reason (no_producer_recorded / small_table / max_fanout_exceeded / max_depth_reached / max_nodes_reached). no_producer_recorded means the table is raw/ingested here — not that it is immutable. Judge immutability from the sample_query texts yourself; the walk only decides which edges to follow and returns raw evidence.

Sample call

"Trace how reporting.revenue_daily is built, upstream, so I can tell whether it can be incrementalized."

Sample response

{
  "target": "analytics_db.reporting.revenue_daily",
  "nodes": [
    {
      "table": "analytics_db.reporting.revenue_daily",
      "depth": 0,
      "read_by": null,
      "producers": [
        { "job_tag": "reporting_dag&&&revenue_daily&&&INSERT", "job_id": "job_77a1c2", "sample_query": "INSERT INTO reporting.revenue_daily SELECT ds, region, SUM(revenue) FROM analytics.orders_daily_agg GROUP BY 1, 2", "sample_query_id": "aa11c2d3-0000-0000-0000-000000000001", "day_cost": 5.10, "day_count": 1 }
      ]
    },
    {
      "table": "analytics_db.analytics.orders_daily_agg",
      "depth": 1,
      "read_by": "analytics_db.reporting.revenue_daily",
      "producers": [
        { "job_tag": "analytics_dag&&&orders_daily_agg&&&DELETE", "job_id": "job_2ab990", "sample_query": "DELETE FROM analytics.orders_daily_agg WHERE ds = CURRENT_DATE - 1; INSERT INTO analytics.orders_daily_agg SELECT ... FROM analytics.orders_fact WHERE order_date = CURRENT_DATE - 1 GROUP BY 1, 2", "sample_query_id": "bb22d3e4-0000-0000-0000-000000000002", "day_cost": 8.42, "day_count": 2 }
      ]
    }
  ],
  "not_expanded": [
    { "table": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT", "read_by": "analytics_db.analytics.orders_daily_agg", "reason": "no_producer_recorded" }
  ],
  "limits": { "max_depth": 3, "max_nodes": 50, "max_expand_per_node": 10, "truncated": false }
}

table_lineage

Returns a table's one-hop neighbor tables within a processing-time date range. upstream_tables are the tables that produce it — the source tables of the queries that write it, plus a clone-provenance edge when the table was created via CREATE TABLE ... CLONE. downstream_tables are the tables built from queries that read it. Each neighbor carries n_queries, total_cost (dollars), and via. start_date and end_date are inclusive YYYY-MM-DD bounds; limit caps neighbors per direction (default 50).

via is a stackable list of edge tags — [] for an ordinary query-derived edge, ["clone"] for clone provenance, ["staging"] when the neighbor matches the staging-table naming convention, and ["clone", "staging"] when both apply.

If you pass a view, it is dissolved to its physical source tables: the response gains a top-level resolved_from_view note and each edge carries from_physical (which physical source it was reached through).

Upstream comes from writer queries (destination_table), which are sparse and absent for engines with no captured SQL text — treat an empty upstream_tables as "unknown", not "no producer".

Sample call

"What feeds analytics.orders_daily_agg and what reads from it between 2026-05-22 and 2026-05-29?"

Sample response

{
  "fqn": "analytics_db.analytics.orders_daily_agg",
  "upstream_tables": [
    { "fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT", "n_queries": 14, "total_cost": 142.5, "via": [] }
  ],
  "downstream_tables": [
    { "fqn": "ANALYTICS_DB.REPORTING.REVENUE_DAILY", "n_queries": 31, "total_cost": 88.0, "via": [] },
    { "fqn": "ANALYTICS_DB.STAGING.ORDERS_DAILY_AGG_TMP", "n_queries": 4, "total_cost": 6.2, "via": ["staging"] }
  ]
}

table_column_detail

Returns per-column metadata, one row per column ordered by position: field_name, data_type, cardinality, part_cluster. cardinality is the schema crawler's distinct-value estimate — read it as a magnitude signal: a column whose cardinality approaches the row count is a poor leading cluster key, while a low-cardinality column used often in filters is a strong candidate. part_cluster flags an existing partition/cluster role.

Sample call

"Show me the columns and cardinalities for analytics.orders_fact."

Sample response

[
  { "field_name": "order_id", "data_type": "NUMBER(38,0)", "cardinality": 1400000000, "part_cluster": null },
  { "field_name": "order_date", "data_type": "DATE", "cardinality": 730, "part_cluster": "CLUSTER" },
  { "field_name": "region", "data_type": "VARCHAR", "cardinality": 6, "part_cluster": "CLUSTER" }
]

Finding similar tables

Four tools surface redundant copies, mergeable pipelines, tables that must move together, and pipelines that compute the same thing — each ranks candidates by Jaccard similarity (min_jaccard threshold in [0.5, 1], default 0.8) and paginates with page / page_size. Every candidate carries its own table_type so views can be told apart from base tables.

similar_tables_by_schema

Compares column structure. Splits results into two buckets: identical (exactly the same column set — the strongest redundant-copy signal) and similar (column-set Jaccard ≥ min_jaccard, excluding the identical ones). Both buckets report exact totals (identical_count / similar_count).

Sample call

"Are there any tables with the same columns as analytics.orders_fact?"

Sample response

{
  "fqn": "analytics_db.analytics.orders_fact",
  "table_type": "BASE TABLE",
  "identical_count": 1,
  "similar_count": 2,
  "identical": [
    { "fqn": "analytics_db.staging.orders_fact_bak", "table_type": "BASE TABLE" }
  ],
  "similar": [
    { "fqn": "analytics_db.analytics.orders_fact_v2", "jaccard": 0.92, "shared": 22, "table_type": "BASE TABLE" },
    { "fqn": "analytics_db.reporting.orders_enriched", "jaccard": 0.81, "shared": 19, "table_type": "VIEW" }
  ],
  "page": 0,
  "page_size": 20,
  "identical_has_more": false,
  "similar_has_more": false
}

similar_tables_by_upstream

Compares the upstream set (the source tables a table is built FROM). A high Jaccard means two tables are built from the same inputs — likely redundant or mergeable pipelines. A single similar list ranked by Jaccard descending; axis is "upstream". Often empty for source tables, which read nothing.

Sample call

"Which tables are built from the same sources as reporting.revenue_daily?"

Sample response

{
  "fqn": "analytics_db.reporting.revenue_daily",
  "table_type": "BASE TABLE",
  "axis": "upstream",
  "similar_count": 1,
  "similar": [
    { "fqn": "analytics_db.reporting.revenue_weekly", "jaccard": 0.88, "shared": 7, "table_type": "BASE TABLE" }
  ],
  "page": 0,
  "page_size": 20,
  "similar_has_more": false
}

similar_tables_by_downstream

Compares the downstream set (the tables built FROM a table — its consumers). A high Jaccard means the same downstream depends on both: interchangeable/redundant supply, or tables that must move together in a migration. A single similar list ranked by Jaccard descending; axis is "downstream".

Sample call

"What's interchangeable with analytics.orders_fact downstream?"

Sample response

{
  "fqn": "analytics_db.analytics.orders_fact",
  "table_type": "BASE TABLE",
  "axis": "downstream",
  "similar_count": 1,
  "similar": [
    { "fqn": "analytics_db.staging.orders_raw", "jaccard": 0.95, "shared": 18, "table_type": "BASE TABLE" }
  ],
  "page": 0,
  "page_size": 20,
  "similar_has_more": false
}

similar_tables_by_transformation

Compares build logic — the transformations each table's producer SQL computes, from the transformation_features fingerprint (per-output-column expressions, GROUP BY grain, WHERE conjuncts, and source tables; output column names are excluded, so A.revenue matches B.rev when the formula matches). A high Jaccard means the same pipeline logic even when column names differ. axis is "transformation".

Beyond the score, each match is classified by exact set algebra (relation): duplicate (same logic, grain, filters, sources), rollup (same expressions, different grain), same_logic_different_source, same_logic_different_join_key, filtered_variant (different WHERE conjuncts), projection (a strict subset of expressions), or partial_overlap. Each candidate also carries the expression-level evidence — families (per-family overlap counts) and shared_expressions / input_only_expressions / candidate_only_expressions / input_only_filters / candidate_only_filters (the matching and differing formulas, literals normalized to ?) — so you can see why it matched.

Coverage: only tables with an extractable SQL producer are fingerprinted (not DDL-only / MERGE-built / raw-ingested). An empty result with a note means "not fingerprinted", not "unique".

Sample call

"Which tables are built with the same logic as reporting.revenue_daily, even if the columns are renamed?"

Sample response (expression-detail fields omitted for brevity)

{
  "fqn": "analytics_db.reporting.revenue_daily",
  "table_type": "BASE TABLE",
  "axis": "transformation",
  "similar_count": 1,
  "similar": [
    {
      "fqn": "analytics_db.reporting.revenue_daily_v2",
      "jaccard": 0.86,
      "shared": 6,
      "table_type": "BASE TABLE",
      "relation": "same_logic_different_source"
    }
  ],
  "page": 0,
  "page_size": 20,
  "similar_has_more": false
}

list_similar_table_groups

The warehouse-wide, seed-free counterpart to similar_tables_by_transformation. Instead of "given table X, what looks like it", it returns the biggest global clusters of transformation-similar tables across the whole warehouse, ranked by total storage (bytes). Each group is a connected component (at Jaccard ≥ min_jaccard) over the transformation_features fingerprints. Use it to find the largest pools of duplicated computation to consolidate.

• mode — which token family drives the match:
  • "source_bound" (default) — tables group only when they compute the same logic reading the same upstream tables/columns → true duplicates (dedup lens).
  • "source_abstracted" — folds away each column's schema.table, so dev/prod copies and per-team template clones group too → consolidation lens ("these could share a precomputed input"). Treat its groups as candidates to review, not confirmed duplicates.
• min_jaccard — clustering threshold in [0.5, 1.0] (default 0.9); higher is tighter.
• limit — max families to return (default 20), taking those with the largest total_bytes.

Coverage is limited to tables with an extractable SQL producer.

Sample call

"Where is the most duplicated computation in the warehouse? Show me the biggest clusters of tables built the same way."

Sample response

{
  "mode": "source_bound",
  "groups": [
    {
      "tables": [
        { "fqn": "analytics_db.reporting.revenue_daily", "bytes": 4200000000, "row_count": 180000000, "table_type": "BASE TABLE" },
        { "fqn": "analytics_db.reporting.revenue_daily_v2", "bytes": 3900000000, "row_count": 175000000, "table_type": "BASE TABLE" }
      ],
      "table_count": 2,
      "total_bytes": 8100000000,
      "total_row_count": 355000000,
      "min_jaccard_in_group": 0.94
    }
  ],
  "group_count": 1,
  "min_jaccard": 0.94,
  "returned": 1
}