> For the complete documentation index, see [llms.txt](https://api-docs.realfinity.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://api-docs.realfinity.io/mcp/tool-reference/data-warehouse.md).

# Data Warehouse

{% hint style="warning" %}
**These two tools are NOT scoped to the calling user — they are org-wide.** Unlike every other tool in this reference, results here are not filtered to the loans you personally may see. The warehouse tables carry no Realfinity loan-officer or company column, so warehouse rows cannot be row-scoped to a caller at all. Access is controlled by **role** instead of by row.
{% endhint %}

**Who may call these two tools**

| Role              | Access                                                                   |
| ----------------- | ------------------------------------------------------------------------ |
| Admin             | Allowed — org-wide results                                               |
| Processor         | Allowed — org-wide results, **not** limited to their own company's loans |
| ConciergeKey      | Allowed — org-wide results, **not** limited to their managed agents      |
| Agent             | **Rejected**                                                             |
| DualLicensedAgent | **Rejected**                                                             |

An Agent or DualLicensedAgent token that works everywhere else in this section will be rejected here. If a call fails authorization and the token is otherwise valid, check the caller's roles against that list first.

The per-user RBAC ladder described on the other Tool Reference pages — Admin sees all, ConciergeKey sees managed agents, and so on — governs the Cosmos- and SQL-backed tools. On those pages a **Processor** reaches all loans org-wide for per-loan access, and is narrowed to their own company's users only by the aggregate and reporting tools. Neither rule applies to anything on this page. See [Loan Lookup](/mcp/tool-reference/loan-lookup.md) or [Reporting](/mcp/tool-reference/reporting.md) for the scoped tools.

## A different store from the rest of this section

Most tools in this reference read either the denormalized Cosmos loan snapshot (see [Loan Lookup](/mcp/tool-reference/loan-lookup.md) and [Reporting](/mcp/tool-reference/reporting.md)) or the operational SQL database. The two tools here read a third store: the **Data Warehouse**, reached over its own `DataWarehouse` connection, separate from the application database connection.

Concretely, for a caller that means:

* **Different field names.** Warehouse queries use their own whitelist of camelCase keys — `loanId`, `statusCode`, `fundedDate`, `openedDate` — which map to raw MeridianLink columns (`Id`, `sStatusT`, `sFundD`, `sOpenedD`). None of the loan field names used by `query_loans` or `report_loans` are valid here, and vice versa. Always resolve field names with `get_data_warehouse_fields`.
* **Different underlying tables.** The queryable surface is the warehouse's `[MeridianLink].[Loans]` table. The related reporting tools also read `[HubSpot].[DealsRenamed]` and `[HubSpot].[PipelineStages]`. These are warehouse copies of upstream system data, not the Realfinity operational database, so a loan or deal is present here only once it has landed in the warehouse.
* **Different freshness.** Because the warehouse is fed from upstream systems rather than written by the application, warehouse rows lag the operational database. The source does not define a refresh interval, so treat the lag as unspecified and check the per-row `updatedAt` field (the warehouse's own `UpdatedAt` column) when recency matters. For anything that must reflect the current state of a loan right now, use the Cosmos-backed tools instead.

**Which page answers which question.** Use the tools here for ad-hoc exploration of raw MeridianLink loan rows and their milestone dates — "which loans funded between these two dates", "what status codes exist", "show me the milestone dates for this loan id". Pre-aggregated warehouse reports live on [Reporting](/mcp/tool-reference/reporting.md) instead: `warehouse_loan_milestone_report` (org-wide milestone funnel by period) and `mlo_pipeline_report` (MLO onboarding cohorts from HubSpot) are Data Warehouse-backed but belong there because they answer reporting questions. Anything that must be scoped to a single loan officer's pipeline also belongs on Reporting, not here.

**Prerequisite:** call `get_data_warehouse_fields` before `query_data_warehouse` — field keys and operators are validated against a closed whitelist, and an unknown key is a hard error.

### query\_data\_warehouse

Run a custom whitelisted query against the Data Warehouse `[MeridianLink].[Loans]` table.

* **Backing store:** Data Warehouse
* **Roles:** Admin, Processor, ConciergeKey

**Parameters**

| Name         | Type                       | Required | Description                                                                                                                 |
| ------------ | -------------------------- | -------- | --------------------------------------------------------------------------------------------------------------------------- |
| `conditions` | array of condition objects | No       | Whitelist-validated filter conditions. Each: `{ field, operator, value }` or `{ field, "In", values }`. Omit for no filter. |
| `select`     | array of string            | No       | Fields to return. Omit or leave empty to return all fields. Must be keys from `get_data_warehouse_fields`.                  |
| `top`        | integer                    | No       | Max rows to return (1–500, default 50). Values outside the range are clamped.                                               |
| `skip`       | integer                    | No       | Rows to skip for paging (default 0).                                                                                        |

Each entry in `conditions`:

| Name       | Type            | Required    | Description                                                                                                                      |
| ---------- | --------------- | ----------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `field`    | string          | Yes         | Whitelisted field key (e.g. `loanId`, `fundedDate`). See `get_data_warehouse_fields`.                                            |
| `operator` | string          | No          | Comparison: `Eq`, `Ne`, `Gt`, `Gte`, `Lt`, `Lte`, `In` (requires `values[]`), `Contains` (string fields only). Defaults to `Eq`. |
| `value`    | string          | Conditional | Required for `Eq`, `Ne`, `Gt`, `Gte`, `Lt`, `Lte`, `Contains`. Dates as `YYYY-MM-DD`.                                            |
| `values`   | array of string | Conditional | Required, non-empty, for the `In` operator.                                                                                      |

**Returns**

`DwQueryResponse` — `count` (rows returned by this call), `fields` (the column names present in each row) and `rows` (an array of objects keyed by field name, with `null` for warehouse nulls). Rows are ordered by `loanId`; page with `skip`/`top`.

<details>

<summary>Example response (synthetic values)</summary>

```json
{
  "count": 2,
  "fields": ["loanId", "statusCode", "openedDate", "fundedDate"],
  "rows": [
    {
      "loanId": "SAMPLE-0000001",
      "statusCode": "Funded",
      "openedDate": "2026-03-02",
      "fundedDate": "2026-04-15"
    },
    {
      "loanId": "SAMPLE-0000002",
      "statusCode": "Underwriting",
      "openedDate": "2026-03-11",
      "fundedDate": null
    }
  ]
}
```

The values above are fabricated placeholders illustrating the shape only.

</details>

**Notes**

* **This is not raw SQL.** Every `field` key resolves against a closed whitelist to a fixed SQL expression, `operator` must be one of the eight allowed names, and every caller-supplied value is bound as a typed parameter. Nothing you send is interpolated into the SQL text, and the query is read-only — so injection is not possible.
* Values are type-checked on binding. A `Date` field rejects anything that is not parseable as `YYYY-MM-DD`; a `Number` field rejects non-numeric input; `Contains` is rejected outright on non-string fields. These come back as bad-request errors.
* An unknown `field` (in `conditions` or in `select`) or an unknown `operator` is a bad-request error naming the offending value — it does not silently drop the filter.
* Because the surface is org-wide, prefer narrow filters and a small `top`. Note that `loanName` can contain borrower-identifying text; omit it from `select` when you do not need it.
* Date fields are cast from the underlying MeridianLink text columns, so a value that is not a valid date upstream reads as `null` rather than raising an error.

### get\_data\_warehouse\_fields

Return the whitelist of fields available to `query_data_warehouse`: field key, data type, and valid operators.

* **Backing store:** Data Warehouse (schema metadata only — no rows are read)
* **Roles:** Admin, Processor, ConciergeKey

**Parameters**

None.

**Returns**

`DwFieldsResponse` — `table` (the logical table the whitelist describes), `operators` (every operator name `query_data_warehouse` accepts) and `fields` (each whitelisted key with its `type`: `String`, `Date`, or `Number`). Call it first and build `conditions` and `select` from the keys it returns.

<details>

<summary>Example response (live output)</summary>

```json
{
  "table": "MeridianLinkLoans",
  "operators": ["Contains", "Eq", "Gt", "Gte", "In", "Lt", "Lte", "Ne"],
  "fields": [
    { "field": "approvedDate", "type": "Date" },
    { "field": "branchChannel", "type": "String" },
    { "field": "canceledDate", "type": "Date" },
    { "field": "clearToCloseDate", "type": "Date" },
    { "field": "closedDate", "type": "Date" },
    { "field": "docsDate", "type": "Date" },
    { "field": "fundedDate", "type": "Date" },
    { "field": "loanId", "type": "String" },
    { "field": "loanName", "type": "String" },
    { "field": "openedDate", "type": "Date" },
    { "field": "rejectedDate", "type": "Date" },
    { "field": "statusCode", "type": "String" },
    { "field": "submittedDate", "type": "Date" },
    { "field": "underwritingDate", "type": "Date" },
    { "field": "updatedAt", "type": "Date" }
  ]
}
```

</details>

**Notes**

* This is metadata only: it returns no loan rows and no personal data, so it is safe to call freely while composing a query.
* The whitelist is fixed in the server, not discovered from the database — a column that exists in the warehouse but is absent here cannot be queried through MCP.
* Field keys are matched case-insensitively, but the keys returned in `query_data_warehouse` rows use the casing shown here.
* The milestone date fields (`openedDate` through `rejectedDate`) are the same underlying columns the warehouse milestone funnel report aggregates — see [Reporting](/mcp/tool-reference/reporting.md) if you want the counts rather than the rows.

## Related pages

* [Overview](/mcp/overview.md)
* [Authentication](/mcp/connecting/authentication.md)
* [Reporting](/mcp/tool-reference/reporting.md)
* [Loan Lookup](/mcp/tool-reference/loan-lookup.md)
* [Tasks & Conditions](/mcp/tool-reference/tasks-and-conditions.md)
* [Pricing](/mcp/tool-reference/pricing.md)
* [Troubleshooting](/mcp/connecting/troubleshooting.md)