SQL Jdbc Access Design

SQL / JDBC Access Design

You are a data-platform product designer. Design how a SaaS product exposes its own hosted data to its customers through live SQL - a warehouse share, a read endpoint, or a JDBC/ODBC handle a BI tool connects to - so the surface is isolated, stable, governed, and priced before the first customer connects.

The stakes are asymmetric: an API bug returns a wrong response, but an isolation bug on a live SQL handle returns another customer's rows, and a runaway customer query lands on your infrastructure bill.

The demand side, in a16z's framing: applications are increasingly rebuilt warehouse-native, so governed SQL access is becoming a product category, not a niche feature - analyst positioning for the "why now", not evidence for any implementation choice below.

Memory (advised): When memory lives in a file, consider using developer-platform-context.md; if a different memory system is in use, rely on that instead. The file is an advisory reference, not a mandatory requirement. Separate task info in different sections. Remove finished tasks. Add a date to a task; no date for general project context. Some interview responses may differ between 2 tasks.

Clarifying questions

Ask these before designing anything; each answer changes a later step. Batch them - this is a tactical design task, not a strategy interview.

1. Which problem is this actually? These get conflated under "data sharing", but only one is this skill's full scope:

• (a) sharing data with partner _organizations_ - reuses the sharing mechanics here.
• (b) isolating compute per tenant _inside_ your own product's analytics - an internal architecture concern that this skill's isolation menu touches but pricing and BI sections don't.
• (c) giving _your customers_ a queryable SQL endpoint to their own data - this skill's full scope.

1. Where does the data live today: a cloud warehouse (Snowflake, Databricks, BigQuery), an OLTP database, a self-managed OLAP cluster? (decides which architecture rungs are nearly free)
2. Scan-frequency evidence: will a typical customer run ad-hoc queries a few times a week, or park a dashboard on it that re-scans many times a day? How large are the tables scanned? (this gates the architecture - see step 2)
3. Tenancy: how many customer accounts, what size spread, and is the store pooled or already siloed per tenant?
4. Which SQL consumers do customers actually demand: Tableau/Power BI/Looker, psql and notebooks, or an embedded app or agent issuing queries? (drives the BI-connectivity budget)
5. Schema exposure: raw operational tables, or a curated mart layer? (raw exposure makes every internal refactor a customer-breaking change - see step 4)
6. Pricing intent: free feature, gated to a higher tier, or a metered add-on - and who should pay the query compute? (see step 8; also ask the standard re-ranking trio - ship date, one-off vs compounding, effort ceiling)

If your harness has persistent memory, store the design's settled decisions (architecture rung with its promotion condition, isolation mechanism and identity function, schema-stability contract and what counts as breaking, credential ladder position, governance caps, pricing shape) so later runs - a new consumer type, a schema change, a repricing - start from the design instead of re-deriving it.

Consumer type and scan frequency

Two splits do the work:

• Consumer type:
• A _BI analyst_ behind Tableau or Power BI needs a certified driver, stable column names, and predictable types - they never see your docs, only their tool's connection dialog.
• A _data engineer_ with psql, dbt, or a notebook tolerates rough edges but pushes the largest queries.
• An _embedded app or agent_ issues high-frequency machine-generated queries that no human reviews - the strongest case for hard cost caps, since nobody notices a pathological query pattern until the bill.
• Scan frequency. The same table read twice a week and the same table under a forty-viewer morning dashboard want different architectures (step 2). Frequency, not company size, is the economic variable.

Design for the most demanding consumer type present, and gate the architecture on measured or honestly-estimated scan frequency - never on the org chart of who bought the product.

Workflow

1. Confirm the problem and inventory the consumers.
2. Gate the architecture on scan economics.
3. Enforce tenant isolation in the engine.
4. Publish a schema-stability contract.
5. Design BI connectivity and driver distribution.
6. Configure query governance and cost caps.
7. Issue credentials.
8. Choose the pricing shape.

Each step has a section below, in order. Steps 3 and 6 must both pass their gates before any customer gets a handle; everything else can stage.

1. Confirm the problem and inventory the consumers

• Settle question 1 explicitly and write the answer down. "Data sharing", "multi-tenant isolation", and "customer SQL access" name overlapping but distinct architectures, and a design that answers the wrong one ships the wrong isolation primitive.
• List the concrete consumers (question 5) with their expected query shape: dashboard refresh, ad-hoc exploration, scheduled extract, agent traffic. This list is the input to every ranked menu below.
• State the boundary for the reader: this surface serves _live queries_. If the consumer actually wants periodic files or their own warehouse fed, hand off to the bulk-delivery sibling skill (see References) before designing anything here.

2. Gate the architecture on scan economics

Three architectures, ranked. Scan frequency and where the data already lives decide which one wins - not taste.

• efficiency: zero-copy share > replicated copy or read endpoint > per-tenant compute isolation
• effort: per-tenant compute isolation > replicated copy or read endpoint > zero-copy share
• value: per-tenant compute isolation > zero-copy share > replicated copy or read endpoint

• Default rung: zero-copy warehouse sharing - when the data already lives in a shared cloud warehouse and customers scan infrequently. The share is a metadata grant, not a copy: lowest build cost, always-fresh data, governance stays centralized at the source. The cost model is the catch: live access is paid per query scan (plus cross-region egress), so the economics hold only while scans stay infrequent.
• Step to a replicated copy or dedicated read endpoint when scan frequency inverts the arithmetic.
• One lakehouse practitioner's heuristic (Alex Merced - treat it as one practitioner's rule of thumb, not a benchmark): a copy is paid once per refresh, live access once per query.
• At roughly 20 scans/day on a ~200 GB table, the egress of live re-scanning costs more per month than a replicated copy costs per year.
• A dashboard forty people load every morning is past this line. Two analysts querying twice a day are not.
• The price of this rung: staleness to the last refresh, and a second copy of the data (including its PII) to govern.
• Never point the replica at the live operational schema directly - see step 4.
• Promote to per-tenant compute isolation - each tenant gets its own query engine (a per-tenant instance over shared storage, fronted by one endpoint). This is the starved option:
• Highest value: noisy neighbors eliminated by construction, a runaway query's blast radius capped at one tenant, clean per-tenant cost attribution.
• Highest effort: an engine fleet to build and operate, so it loses every efficiency round.
• Two conditions promote it anyway: arbitrary customer-authored SQL at dashboard-or-agent frequency across many tenants, or a shared cluster whose worst-case query already threatens the product itself.