lakehouse-query

name

lakehouse-query

description

Answer cross-domain analytical questions about Redstone's pricing/entrata/accounting/marketing/hr/legal data. Use whenever the user asks an analytical question that could be answered by reading one or more `derived/*.parquet` files — e.g., "which properties have the deepest concessions and lowest NOI?", "where is renewal exposure highest?", "show me revenue per FTE by market". Triggers on any question mixing two or more of {pricing, occupancy, leases, leads, concessions, GL, NOI, budget variance, headcount, payroll, rent control, eviction holds, fair-housing flags}.

Lakehouse Query Skill

Answer cross-domain analytical questions by calling the lakehouse tools. Do not hand-roll SQL from source files — the parquets in derived/ are the contract, and the tools enforce read-only access + parquet-path citations.

Tools

You have three tools available via the redstone-lakehouse MCP server (loaded automatically through .mcp.json in the repo root):

• list_domains() — returns the catalog of every derived parquet: [{domain, entity, columns, row_count}]. Always call this first when the user's question touches a domain you haven't queried this session.
• run_sql({ query }) — executes a read-only DuckDB query against s3://${bucket}/derived/**/*.parquet. Reject reasons surface as structured errors; do not retry destructive SQL.
• read_kernel({ name: "scoreBed", signals }) — returns the canonical pricing decision {action, reason[]} for a signal bundle. Use this rather than re-deriving "should we increase rent" in prose.

Workflow

1. Discover. Call list_domains() if you don't already know the relevant parquet columns. Cache the catalog mentally for the session.
2. Write SQL. Compose a single SELECT / WITH query that joins the parquets needed. Use read_parquet('s3://${bucket}/derived/<name>.parquet') for each source. Prefer one query that returns the answer over multiple round-trips.
3. Run. Invoke run_sql({ query }). If the validator rejects, fix the query — do not work around the validator.
4. Cite. Every factual claim in the answer must reference the s3://.../derived/{name}.parquet paths the data came from. Inline, like: "NOI margin = -8% per derived/noi_by_property.parquet".
5. Decide via kernel when relevant. If the question is "should we raise/hold/cut rent here?", invoke read_kernel rather than reasoning about thresholds yourself.

Conventions

• Read-only by construction. The SQL validator rejects INSERT, UPDATE, DELETE, COPY, ATTACH, CREATE, DROP, ALTER, PRAGMA, and semicolon-injection. If you find yourself wanting to write, the answer is to extend the data pipeline (src/{domain}.ts + src/lake.ts), not to bypass the read-only contract.
• Cap result sets — the tool caps at 1000 rows. Aggregate or LIMIT explicitly when full enumeration isn't needed.
• Bounded reasoning. If you can't answer in 3–4 tool calls, say so and ask the user to narrow the question. Don't loop.
• Stateless. Each invocation is independent; the tools have no session memory.

Examples

Q: Which Provo-market properties have NOI margin below 15% AND high renewal exposure?

SELECT n.property_id, n.noi_margin, r.renewal_exposure
FROM read_parquet('s3://warehouse/derived/noi_by_property.parquet') n
JOIN read_parquet('s3://warehouse/derived/renewal_exposure_by_property.parquet') r
  USING (property_id)
JOIN read_parquet('s3://warehouse/derived/current_rent_roll.parquet') rr
  USING (property_id)
WHERE n.noi_margin < 0.15
  AND r.renewal_exposure > 0.25
  AND rr.market = 'Provo, UT'
GROUP BY n.property_id, n.noi_margin, r.renewal_exposure
ORDER BY r.renewal_exposure DESC
LIMIT 25;

Cite both parquets in the answer.

Q: Where is cost-per-lease most out of line with revenue-per-FTE?

Join cost_per_lease_by_property.parquet × revenue_per_employee.parquet on property_id, compute the ratio, rank descending. Cite both.

When NOT to use this skill

• Schema/migration questions about how derived/ parquets are built — read src/lake.ts directly.
• Bug investigations in the pricing kernel — read src/recommendations.ts.
• Anything requiring writes — extend the pipeline, don't bypass the tools.