query_substrate

What it answers

1. 1Set fresh=true for a user-requested saved-view update: execute without the result cache; all access and scan limits still apply.
2. 2Run a read-only SQL SELECT directly over the Vlada cost-data substrate (Athena/Iceberg) and get the rows back, with provenance.
3. 3Use this when the pre-built tools don't answer your exact question.
4. 4WORKFLOW: first call find_data_sources / describe_data_source to find the table + read its card (grain, columns, and the CAVEATS you must apply — e.g. filter ghost rates, never blend across modifiers/methodologies), THEN write SQL against the table's fully-qualified name (e.g. vlada_curated.fact_hospital_chargemaster).
5. 5RULES: read-only SELECT only (no INSERT/UPDATE/DDL — rejected); you may only query tables in the queryable set your account is entitled to (others are rejected);
6. 6M-2 versioned tables must be filtered on is_current (injected for a single-table SELECT, otherwise rejected with the column named); results are LIMIT-bounded by your plan's per-call row cap (at most 10000), the delivered payload is bounded to ~47K chars, and the scan is cost-capped.
7. 7Any bound that drops rows is reported as truncated:true with rows_returned + rows_dropped/total_rows_estimate and a caveat saying how to get the rest: aggregate in SQL (GROUP BY, COUNT, approx_percentile) or narrow the predicate, so the engine computes over the full set and returns the answer in a few rows.
8. 8Vlada answers questions; it does not export whole tables: paging past the first page (OFFSET) and the bulk lane (submit_query) are not on free or Studio.
9. 9Every result carries provenance (the engine, query id, bytes scanned, tables read).
10. 10Apply the source card's methodology yourself: raw SQL does not enforce it.

Inputs (3)

Call it

# MCP (https://mcp.vladahealth.com/mcp): the tool by name
query_substrate(sql="SELECT * FROM medicare_silver.dim_hospital_quality LIMIT 3", max_rows=3)

curl -X POST https://api.vladahealth.com/v1/tools/query_substrate \
  -H "Authorization: Bearer $VLADA_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT * FROM medicare_silver.dim_hospital_quality LIMIT 3","max_rows":3}'

# the schema, no key
curl https://api.vladahealth.com/v1/tools/query_substrate/schema

A real answer

{
 "value": {
  "rows": [
   {
    "ccn": "640001",
    "facility_name": "LBJ TROPICAL MEDICAL CENTER",
    "city": "PAGO PAGO",
    "state_cd": "AS",
    "zip_cd": "96799",
    "county_parish": "EASTERN DISTRICT",
    "hospital_type": "Acute Care Hospitals",
    "hospital_ownership": "Government - Hospital District or Authority",
    "emergency_services": true,
    "birthing_friendly": null,
    "overall_rating": null,
    "overall_rating_footnote": "19",
    "observed_period": "2026-09-22",
    "asserted_at": "2026-09-22",
    "effective_period_start": "2026-09-22",
    "effective_period_end": "2099-12-31",
    "cms_vintage_sequence": 1,
    "parse_revision": 1,
    "parser_artifact_hash": "01dfeeb3d0b7f7f8f6b2ef70abdfe464d9a56c02bdab90047222ec71d2472511",
    "source_url": "https://data.cms.gov/provider-data/sites/default/files/resources/893c372430d9d71a1c52737d01239d47_1785189955/Hospital_General_Information.csv",
    "sha256_content_hash": "f874f09fef895a1ccf5bb7392dcbb2be05c9860339261b093fe00f0c1b013480",
    "source_id": "cms_hospital_general_information",
    "is_current": true,
    "bronze_object_key": null,
    "s3_version_id": null,
    "vintage_tag": "general_info_2026-09-22",
    "drug_source_version_id": null,
    "latitude": null,
    "longitude": null
   },
   {
    "ccn": "240001",
    "facility_name": "NORTH MEMORIAL HEALTH HOSPITAL",
    "city": "ROBBINSDALE",
    "state_cd": "MN",
    "zip_cd": "55422",
    "county_parish": "HENNEPIN",
    "hospital_type": "Acute Care Hospitals",
    "hospital_ownership": "Voluntary non-profit - Private",
    "emergency_services": true,
    "birthing_friendly": "Y",
    "overall_rating": 1,
    "overall_rating_footnote": null,
    "observed_period": "2026-09-22",
    "asserted_at": "2026-09-22",
    "effective_period_start": "2026-09-22",
    "effective_period_end": "2099-12-31",
    "cms_vintage_sequence": 1,
    "parse_revision": 1,
    "parser_artifact_hash": "01dfeeb3d0b7f7f8f6b2ef70abdfe464d9a56c02bdab90047222ec71d2472511",
    "source_url": "https://data.cms.gov/provider-data/sites/default/files/resources/893c372430d9d71a1c52737d01239d47_1785189955/Hospital_General_Information.csv",
    "sha256_content_hash": "f874f09fef895a1ccf5bb7392dcbb2be05c9860339261b093fe00f0c1b013480",
    "source_id": "cms_hospital_general_information",
    "is_current": true,
    "bronze_object_key": null,
    "s3_version_id": null,
    "vintage_tag": "general_info_2026-09-22",
    "drug_source_version_id": null,
    "latitude": null,
    "longitude": null
   },
   {
    "ccn": "240002",
    "facility_name": "ESSENTIA HEALTH ST MARY'S MEDICAL CENTER",
    "city": "DULUTH",
    "state_cd": "MN",
    "zip_cd": "55805",
    "county_parish": "ST. LOUIS",
    "hospital_type": "Acute Care Hospitals",
    "hospital_ownership": "Voluntary non-profit - Private",
    "emergency_services": true,
    "birthing_friendly": "Y",
    "overall_rating": 4,
    "overall_rating_footnote": null,
    "observed_period": "2026-09-22",
    "asserted_at": "2026-09-22",
    "effective_period_start": "2026-09-22",
    "effective_period_end": "2099-12-31",
    "cms_vintage_sequence": 1,
    "parse_revision": 1,
    "parser_artifact_hash": "01dfeeb3d0b7f7f8f6b2ef70abdfe464d9a56c02bdab90047222ec71d2472511",
    "source_url": "https://data.cms.gov/provider-data/sites/default/files/resources/893c372430d9d71a1c52737d01239d47_1785189955/Hospital_General_Information.csv",
    "sha256_content_hash": "f874f09fef895a1ccf5bb7392dcbb2be05c9860339261b093fe00f0c1b013480",
    "source_id": "cms_hospital_general_information",
    "is_current": true,
    "bronze_object_key": null,
    "s3_version_id": null,
    "vintage_tag": "general_info_2026-09-22",
    "drug_source_version_id": null,
    "latitude": null,
    "longitude": null
   }
  ],
  "columns": [
   "ccn",
   "facility_name",
   "city",
   "state_cd",
   "zip_cd",
   "county_parish",
   "hospital_type",
   "hospital_ownership",
   "emergency_services",
   "birthing_friendly",
   "overall_rating",
   "overall_rating_footnote",
   "observed_period",
   "asserted_at",
   "effective_period_start",
   "effective_period_end",
   "cms_vintage_sequence",
   "parse_revision",
   "parser_artifact_hash",
   "source_url",
   "sha256_content_hash",
   "source_id",
   "is_current",
   "bronze_object_key",
   "s3_version_id",
   "vintage_tag",
   "drug_source_version_id",
   "latitude",
   "longitude"
  ],
  "row_count": 3,
  "rows_returned": 3,
  "returned_row_count": 3,
  "rows_dropped": 0,
  "total_rows_estimate": null,
  "truncated": true,
  "truncation": {
   "by_row_limit": true,
   "by_payload_bytes": false,
   "row_limit_applied": 3,
   "payload_budget_chars": 44721,
   "matching_row_count_known": false,
   "matching_row_count_at_least": 3
  },
  "tables_read": [
   "medicare_silver.dim_hospital_quality"
  ],
  "provenance": {
   "query_id": "8bdb8af1-ae01-4f91-89a4-7193ee1ac1cb",
   "bytes_scanned": 14928,
   "cached": true,
   "tables_read": [
    "medicare_silver.dim_hospital_quality"
   ],
   "trace_to_source": "row-level",
   "sources": [
    {
     "table": "medicare_silver.dim_hospital_quality",
     "domain": "hospital",
     "trace_to_source": "row-level",
     "row_provenance_columns": [
      "asserted_at",
      "source_url",
      "sha256_content_hash",
      "bronze_object_key",
      "s3_version_id",
      "drug_source_version_id"
     ],
     "source_origin": "Hospital Price Transparency files",
     "catalog_freshness_hint": "Refreshed when CMS republishes Care Compare (roughly quarterly).",
     "catalog_freshness_basis": "Static catalog metadata; may describe an earlier profile, a record update or a release cadence. Not a measured data edition or publisher release date for this query."
    }
   ]
  },
  "rejected_reason": null,
  "rejected_detail": null
 },
 "unit": "rows",
 "vintage": null,
 "source": {
  "table": "medicare_silver.dim_hospital_quality",
  "dataset": "Care Compare hospitals (general information, quality measure
…

What comes back

{
 "$defs": {
  "Quality": {
   "description": "Row-level quality signal. All flags precomputed at build time\n(stored on the gold mart) and passed through here.",
   "properties": {
    "is_outlier": {
     "default": false,
     "title": "Is Outlier",
     "type": "boolean"
    },
    "is_ghost_candidate": {
     "default": false,
     "title": "Is Ghost Candidate",
     "type": "boolean"
    },
    "n_similar_rates": {
     "default": 0,
     "title": "N Similar Rates",
     "type": "integer"
    },
    "confidence": {
     "default": 1,
     "title": "Confidence",
     "type": "number"
    }
   },
   "title": "Quality",
   "type": "object"
  },
  "Source": {
   "description": "Cell-level provenance. Every served value traces back here.\n\n`snapshot_id` is the Iceberg snapshot the tool read from. Two calls\nat the same snapshot MUST produce byte-identical responses —\nthat's the content-addressed caching guarantee.",
   "properties": {
    "table": {
     "title": "Table",
     "type": "string"
    },
    "rate_id": {
     "anyOf": [
      {
       "type": "string"
      },
      {
       "type": "null"
      }
     ],
     "default": null,
     "title": "Rate Id"
    },
    "source_files": {
     "items": {
      "type": "string"
     },
     "title": "Source Files",
     "type": "array"
    },
    "snapshot_id": {
     "anyOf": [
      {
       "type": "integer"
      },
      {
       "type": "null"
      }
     ],
     "default": null,
     "title": "Snapshot Id"
    }
   },
   "required": [
    "table"
   ],
   "title": "Source",
   "type": "object"
  }
 },
 "properties": {
  "value": {
   "default": null,
   "title": "Value"
  },
  "unit": {
   "anyOf": [
    {
     "type": "string"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Unit"
  },
  "vintage": {
   "anyOf": [
    {
     "type": "string"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Vintage"
  },
  "source": {
   "$ref": "#/$defs/Source"
  },
  "methodology": {
   "anyOf": [
    {
     "type": "string"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Methodology"
  },
  "quality": {
   "$ref": "#/$defs/Quality"
  },
  "invariants_applied": {
   "items": {
    "type": "string"
   },
   "title": "Invariants Applied",
   "type": "array"
  },
  "caveats": {
   "items": {
    "type": "string"
   },
   "title": "Caveats",
   "type": "array"
  },
  "response_hash": {
   "default": "",
   "title": "Response Hash",
   "type": "string"
  },
  "semantic_version": {
   "default": 1,
   "title": "Semantic Version",
   "type": "integer"
  },
  "tool_version": {
   "default": "unknown",
   "title": "Tool Version",
   "type": "string"
  },
  "explanation": {
   "anyOf": [
    {
     "type": "string"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Explanation"
  },
  "status": {
   "default": "complete",
   "title": "Status",
   "type": "string"
  },
  "refusal": {
   "anyOf": [
    {
     "additionalProperties": true,
     "type": "object"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Refusal"
  },
  "failure": {
   "anyOf": [
    {
     "additionalProperties": true,
     "type": "object"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "Failure"
  },
  "as_of": {
   "anyOf": [
    {
     "type": "string"
    },
    {
     "type": "null"
    }
   ],
   "default": null,
   "title": "As Of"
  },
  "freshness_state": {
   "default": "unknown",
   "title": "Freshness State",
   "type": "string"
  }
 },
 "title": "QuerySubstrateResponse",
 "type": "object"
}

Sources

Known limits (1)

1. 1Raw SQL does NOT enforce a source's methodology (ghost-rate filter, modifier/methodology non-blending) — the agent must apply the caveats from the source card. For warranted, methodology-enforced numbers use the source's specialized tool.