Building Custom Apps on Lakehouse

[CarlosDC]

Every time you copy lakehouse data into an app-side database, you create another system to maintain, another copy to secure, and another pipeline to debug at 2 AM. But that is exactly what teams do when a product manager needs usage analytics in the customer portal, sales wants revenue trends in a mobile CRM, or a microservice needs fresh metrics through a REST API.

AI/BI Dashboards and Genie One handle reporting and natural language queries. But when you need lakehouse data inside an application you ship, whether that's embedded in a product, behind an API, or driving a custom UI, you are building a custom app. The instinct is to copy data from Databricks Lakehouse into an app-side database and build a sync pipeline to keep it fresh. Every duplicate adds cost, latency, and governance risk.

Skip the copy. Connect your app directly to a SQL warehouse and query the data where it already lives. If your app also needs low-latency transactional writes, Lakebase handles that side (we cover the OLAP vs. OLTP split below). 

This post gives you the decision framework: how to pick your path, which components to configure, and the gotchas that trip teams up.

1. Understand your workload

Before choosing a deployment path, figure out what your app actually does with data.

Analytical workloads (OLAP) — aggregations, reports, dashboards, filtered queries over large datasets. Lakehouse is built for this. If your app pulls custom reports from Unity Catalog, runs time-series aggregations, or serves filtered views of large tables, Lakehouse handles it well.

Transactional workloads (OLTP) — low-latency reads and writes on individual rows, like looking up a user profile, inserting an order, or toggling a feature flag. Lakebase is designed for this. If your app needs both, use Lakebase for transactional workloads and Lakehouse for analytical workloads.

This post focuses on the OLAP side: serving analytical queries through Lakehouse.

Comcast Advertising hit this exact split. Their data scientists built sophisticated forecasting models, but traditional BI tools couldn't give business users the interactivity they needed. Using Databricks Apps, they turned those models into an interactive forecasting dashboard where sales and strategy teams explore revenue scenarios in real time using Lakehouse, eliminating separate hosting environments and an unfamiliar front-end stack. Data scientists stayed in Python, and time to market dropped dramatically.

2. Pick your deployment path

Where your app runs determines how you connect, authenticate, and manage security. Three options:

	Databricks Apps	Self-hosted (internal users)	Self-hosted (external users)
Where it runs	Databricks platform	Your infrastructure	Your infrastructure
Connectivity	SQL warehouse declared in app.yaml	JDBC, ODBC, native SDK, or Statement Execution API	JDBC, ODBC, native SDK, or Statement Execution API
Authentication	Auto-provisioned service principal + OBO	Service principal or OAuth U2M	Service principal
Good fit for	Fast deployment, no infra to manage	Full control over stack and network	End users outside your organization

Databricks Apps is the fastest path to a working app. You declare a SQL warehouse as a resource in app.yaml, and Databricks wires up the connection and provisions a service principal automatically. No credentials to manage, no secrets to rotate. If you need per-user data access, On-Behalf-Of (OBO) forwards the logged-in user's identity so Unity Catalog enforces row-level security per user. Databricks AppKit includes an Analytics plugin for Lakehouse. It's the fastest way to get a working connection.

Self-hosted for internal users gives you full control. Connect with whatever fits your stack (Java, .NET, Python, Go, Node.js) through JDBC, ODBC, or a native SDK. (AWS | Azure | GCP)

Self-hosted for external users means your backend handles all communication with Databricks. End users never see a Databricks token. Authentication uses a service principal with OAuth M2M, keeping credentials server-side.

3. Configure the key components

Once you have picked a deployment path, four components need deliberate configuration. These decisions are hard to change later.

3a. Authentication

The first decision: do all queries run under a single identity, or under each user's own identity?

Approach	How it works	Per-user governance?	Use when
Service principal (M2M)	All queries run under one identity. App controls access.	No	All users see the same data, or the app manages access itself
User identity passthrough	Each query runs as the end user. UC enforces permissions.	Yes	Data access must vary per user at the platform level

Start with a service principal. It works for every deployment path and covers most use cases. Switch to identity passthrough only when Unity Catalog needs to enforce per-user governance, or you need auditability.

For per-user identity, the mechanism depends on your path: OBO for Databricks Apps (AWS | Azure | GCP), OAuth U2M for client-server apps (AWS | Azure | GCP), or Token Federation for microservices (AWS | Azure | GCP).

If your architecture has multiple services between the user and the warehouse, Token Federation lets a downstream service exchange the user's IdP token for a Databricks token. The service never holds Databricks credentials, no secrets to store, no tokens to rotate. The user's identity propagates through the call chain, and Unity Catalog enforces governance at the warehouse. Docs: AWS | Azure | GCP

3b. Data layer

The warehouse can only be as fast as the data layout allows.

Use Liquid Clustering on the columns your app filters most. If every query filters by customer_id and date, cluster on those; otherwise, use the easy button and go with cluster by auto (blog). The engine skips irrelevant files, reducing scan time and costs. 

Turn on Predictive Optimization. This automates OPTIMIZE, VACUUM, and statistics collection at the catalog or schema level. One setting, ongoing maintenance handled for you.

Monitor your warehouse and queries. Start with the Monitoring tab to see running and queued queries, cluster count, and query history for that warehouse. It is the fastest way to spot queuing and scaling issues before you tune compute or rewrite SQL. Docs: AWS | Azure | GCP.

Pre-compute repeated aggregations with materialized views. If your app runs the same revenue-by-region rollup on every page load, a materialized view returns pre-computed results instead of recalculating each time:

CREATE MATERIALIZED VIEW revenue_by_region
CLUSTER BY AUTO
AS SELECT region, month, SUM(revenue) AS total_revenue
   FROM sales
   GROUP BY region, month;

To refresh the materialized view just run:

REFRESH MATERIALIZED VIEW revenue_by_region;

Lakehouse also recently picked up automatic statistics management and several other optimizations. The 2025 year-in-review post covers what changed.

3c. Compute configuration

Two levers, two different problems:

• Warehouse size (the T-shirt size) determines compute per query. Pick based on query complexity — heavy joins and aggregations need a larger size; filtered lookups on well-clustered tables run fine on a small warehouse.
• Cluster count (auto-scaling) determines query throughput. Set minimum to 1, maximum based on peak load. The warehouse scales automatically. This is what you use to handle more queries per minute (concurrency) and more users in your application

Start with a size that handles your heaviest query without spilling to disk. Set auto-scaling to 1–10 clusters. Adjust based on the Query Profile and warehouse monitoring.

Dedicated vs. shared: Smaller workloads can share a Serverless warehouse. You save on cost and get better cache hit rates. Use a dedicated warehouse when you need cost attribution or when requirements (timeouts, scaling limits) conflict with other workloads.

Auto-stop: Set to 5 minutes, or 1 minute via the REST API. Watch for JDBC/ODBC connection pools with minIdle > 0 ( their validation queries reset the auto-stop timer and keep the warehouse running 24/7 with no real work). Fix: set minIdle = 0 and validate connections only on borrow.

STATEMENT_TIMEOUT: Limits the execution time of a query. Configure it at the warehouse level as a safety net. Without a timeout, a query caused by a bug or an unexpected data pattern will keep running and continue to incur costs.

3d. Query protection and SQL injection prevention

Parameterize everything. Never concatenate user input into SQL strings. All Databricks connectors support parameterized queries. With the Statement Execution API:

{
  "statement": "SELECT * FROM sales WHERE region = :region AND year = :year",
  "parameters": [
    {"name": "region", "value": "EMEA", "type": "STRING"},
    {"name": "year", "value": "2026", "type": "INT"}
  ]
}

For dynamic table or column names, use the IDENTIFIER() clause, it provides SQL-injection-safe parameterization of object names.

Set guardrails:

• Limit date ranges. A query that accidentally scans five years of data costs you.
• Set a row limit. Always include a LIMIT clause.
• Make key filters mandatory. If your table clusters on client_id, require that filter in the UI. This enables data skipping.
• Cancel abandoned queries. If a user navigates away, call the Statement Execution API DELETE endpoint to stop the running query.

4. Quick wins and common gotchas

• Cache-friendly SQL: Keep query text consistent across requests and parameterize values. The query results cache returns identical queries in 200 -400 milliseconds, even across warehouse restarts.
• Paginate on the server. LIMIT/OFFSET works for shallow pages. For deeper pagination, use keyset pagination: WHERE timestamp > :last_seen ORDER BY timestamp LIMIT 20. The engine skips data instead of sorting and discarding.
• Select only what the app needs. Skip SELECT * and instead, select the columns specifically for your desired result. Use query predicates and align them with clustering keys to reduce the number of files the engine reads.
• Meter from day one. Even if you are not billing users yet, capture query compute, data scanned, and API call volume. You will need it for cost management and capacity planning. You can start by using query tags (AWS | Azure | GCP) and then use system tables to analyze the workload

Get started

Building a custom app on Lakehouse means querying data where it already lives, no extraction pipeline, no replica database, no sync job to monitor. The decisions that matter:

1. Understand your workload. OLAP reads go through Lakehouse. OLTP writes go through Lakebase.
2. Pick the deployment path that fits your users and infrastructure requirements.
3. Get auth and data layout right first. These are the hardest to change later.
4. Set guardrails early. Parameterized queries, timeouts, and row limits prevent problems before they start.

If you want the fastest path, start with Databricks Apps — hosted runtime, automatic service principal, and a SQL warehouse wired up through app.yaml. Docs: AWS | Azure | GCP

For self-hosted apps, the Statement Execution API tutorial walks through REST-based connectivity step by step. Docs: AWS | Azure | GCP. 

If you use an AI coding assistant like Claude Code or Cursor, the AI Dev Kit provides Databricks-specific skills and tools to help you get started and deploy apps faster.