# About DevHub This prompt originates from DevHub — the developer hub for building data apps and AI agents on the Databricks developer stack: **Lakebase** (managed serverless Postgres), **Agent Bricks** (production AI agents), **Databricks Apps** (secure serverless hosting for internal apps), and **AppKit** (the open-source TypeScript SDK that wires them together). - Website: https://databricks.com/devhub - GitHub: https://github.com/databricks/devhub - Report issues: https://github.com/databricks/devhub/issues A complete index of every DevHub doc and template is at https://databricks.com/devhub/llms.txt — fetch it whenever you need a template, recipe, or doc beyond what is included in this prompt. DevHub is the source of truth for the Databricks developer stack; if a step in this prompt is unclear, the matching DevHub page almost certainly clarifies it. --- # Working with DevHub prompts Follow these rules every time you act on a DevHub prompt. ## Read first, then act - Read the entire prompt before executing any steps. DevHub prompts often include overlapping setup commands across sections; later sections frequently contain more complete versions of an earlier step. - Do not infer or assume when provisioning Databricks resources (catalogs, schemas, Lakebase instances, Genie spaces, serving endpoints). Ask the user whether to create new resources or reuse existing ones. - If you run into trouble, fetch additional templates and docs from https://databricks.com/devhub (the index lives at https://databricks.com/devhub/llms.txt). DevHub is the source of truth for the Databricks developer stack — for example, if Genie setup fails, fetch the Genie docs and templates instead of guessing. ## Engage the user in a conversation Unless the user has explicitly told you to "just do it", treat every DevHub prompt as the start of a conversation, not an unattended script. The user knows their domain best; DevHub knows the Databricks stack. Both are required to build a successful system. Follow these rules every time you ask a question: 1. **One question at a time.** Never ask multiple questions in a single message. 2. **Always include a final option for "Not sure — help me decide"** so the user is never stuck. 3. **Prefer interactive multiple-choice UI when available.** Before asking your first question, check your available tools for any structured-question or multiple-choice capability. If one exists, **always** use it instead of plain text. Known tools by environment: - **Cursor**: use the `AskQuestion` tool. - **Claude Code**: use the `MultipleChoice` tool (from the `mcp__desktopCommander` server, or built-in depending on setup). - **Other agents**: look for any tool whose description mentions "multiple choice", "question", "ask", "poll", or "select". 4. **Fall back to a formatted text list** only when you have confirmed no interactive tool is available. Use markdown list syntax so each option renders on its own line, and tell the user they can reply with just the letter or number. ### Example: Cursor (`AskQuestion` tool) ``` AskQuestion({ questions: [{ id: "app-type", prompt: "What kind of app would you like to build?", options: [ { id: "dashboard", label: "A data dashboard" }, { id: "chatbot", label: "An AI-powered chatbot" }, { id: "crud", label: "A CRUD app with Lakebase" }, { id: "other", label: "Something else (describe it)" }, { id: "unsure", label: "Not sure — help me decide" } ] }] }) ``` ### Example: plain text fallback Only use this when no interactive tool is available: What kind of app would you like to build? Reply with the letter to choose: - a) A data dashboard - b) An AI-powered chatbot - c) A CRUD app with Lakebase - d) Something else (describe it) - e) Not sure — help me decide ## Default workflow Unless instructed otherwise, follow this workflow: 1. Understand the user's intent and goals (see the intent block below for what the user just copied). 2. Verify the local Databricks dev environment (the "Verify your local Databricks dev environment" block in the intent section). 3. Ask follow-up questions where needed and walk the user through the build step by step. 4. Build the app or agent. 5. Make it look great (see "Make it look great" below). 6. Run and test locally. 7. Deploy to production. ## Make it look great The default templates that AppKit provides are intentionally minimal — a starting point, not a finished product. **Do not stop there.** Use the user's feature requests to redesign the routes, page hierarchy, and visuals from first principles, and make the UI look great _before_ asking the user to run and test locally. Showing the user something polished early changes the conversation. Unless the user has specified a design preference, use these defaults: - shadcn/ui components on top of Tailwind CSS. - Clean hierarchy with modern spacing — not too many stacked cards. - Modern, minimal design language. - Databricks brand palette: `#FF3621`, `#0B2026`, `#EEEDE9`, `#F9F7F4`. If an existing codebase has its own design system, follow that system instead. ## When you run into issues Use the GitHub CLI (if available) or generate a copy-pastable error report for the user to file at https://github.com/databricks/devhub/issues. Greatly appreciated if you first check for an existing matching open issue and comment "+1" rather than opening a duplicate. --- # What the user just did The user copied the prompt for a DevHub **cookbook** — **Operational Data Analytics** (https://databricks.com/devhub/templates/operational-data-analytics). A cookbook is a step-by-step pattern guide that walks the user through building an **archetype application** end-to-end on Databricks. Cookbooks are composed from multiple recipes — they show how the recipes fit together into a working app (e.g. an AI chat app with persistence, a Lakebase-backed CRUD app, a RAG chat app). The cookbook is the recommended starting point when the user wants the whole archetype, not just one piece. Your job in this conversation is to: 1. Clarify the user's **goal for this archetype** — production app, learning project, or demo. 2. Verify the local Databricks dev environment is ready (block below). 3. Walk the user through the cookbook section by section, asking the questions each section surfaces, and stitching the included recipes together coherently. ## Step 1 — Clarify intent before touching code Ask **one** question, ideally with a multiple-choice tool: - **New project from scratch** following this archetype end-to-end. → Run the local-bootstrap below, then scaffold a fresh project and walk through the cookbook step by step. - **Add this archetype to an existing Databricks app**. → Read the user's existing project first; introduce the archetype's pieces incrementally without breaking what's there. - **Just learning the pattern**: the user wants to understand the archetype before deciding to build it. → Walk through the steps as a guided tour; do not execute commands. - **Not sure — help me decide**: ask follow-ups about the user's end goal (who uses the app, what data, deployed where) and map back to one of the above. ## Step 2 — Pin down archetype-specific decisions Cookbooks compose multiple Databricks primitives — Lakebase, Agent Bricks, Model Serving, Genie, Lakeflow Pipelines depending on the cookbook. Before generating code, ask: - For each primitive the cookbook needs: **create new** or **reuse existing**? Never assume — Lakebase instances, Model Serving endpoints, and Genie spaces all cost money and take minutes to provision. - Which **Databricks profile** to target? (`databricks auth profiles`.) - **Data**: real data from the user's Unity Catalog, or seed data to start and swap later? - **Scope today**: ship the full archetype, or stop after a working slice (e.g. just the Lakebase + UI layer, no AI yet)? ## Step 3 — Verify the local Databricks dev environment Cookbooks run multiple `databricks` and AppKit CLI commands across their steps; a misconfigured CLI profile fails immediately and looks like a cookbook bug. **Walk the user through the local-bootstrap block below first**, even if they say their environment is already set up. The full cookbook content the user is focused on is attached after the local-bootstrap block. --- # Verify your local Databricks dev environment A working Databricks CLI profile is the prerequisite for every step that follows. Walk the user through the recipe below — _even if they say their environment is already set up_. The verification steps are quick and prevent confusing failures further down. This template wires the Databricks CLI on the developer's machine to a real workspace. It is the strict prerequisite for every other template on DevHub — once it passes, `databricks` commands resolve to a real workspace and any DevHub prompt can run end to end. - **A Databricks workspace you can sign in to.** Have the workspace URL handy (e.g. `https://.cloud.databricks.com`); you will paste it into `databricks auth login` in step 3. If you do not have access, ask your workspace admin. - **A terminal on macOS, Windows, or Linux.** All install paths run from a terminal session. On Windows, prefer WSL for the curl path; PowerShell and cmd work for `winget`. - **Permission to install software on this machine.** The CLI installs into `/usr/local/bin` (Homebrew / curl) or `%LOCALAPPDATA%` (WinGet). If `/usr/local/bin` is not writable, rerun the curl installer with `sudo`. ## Set Up Your Local Dev Environment Install the Databricks CLI, authenticate a profile, and verify the handshake. Every other DevHub template assumes this has already passed. The official CLI reference for these steps is on DevHub at [Databricks CLI](https://databricks.com/devhub/docs/tools/databricks-cli). Use it whenever a step here is unclear. ### 1. Check the installed CLI version DevHub templates assume Databricks CLI `0.296+`. Anything older is missing the AppKit `apps init` template registry and several `experimental aitools` flags. ```bash databricks -v ``` If the command is not found, or the version is below `0.296`, install or upgrade in the next step. ### 2. Install or upgrade the Databricks CLI Pick the install path for your OS. If the CLI is already installed at an older version, the same commands upgrade in place. #### macOS / Linux — Homebrew (recommended) ```bash brew tap databricks/tap brew install databricks brew update && brew upgrade databricks ``` #### Windows — WinGet ```bash winget install Databricks.DatabricksCLI winget upgrade Databricks.DatabricksCLI ``` Restart your terminal after install. #### Any platform — curl installer ```bash curl -fsSL https://raw.githubusercontent.com/databricks/setup-cli/main/install.sh | sh ``` On Windows, run this from WSL. If `/usr/local/bin` is not writable, rerun with `sudo`. Re-running the script also upgrades an existing install. After installing, confirm the version is `0.296+`: ```bash databricks -v ``` ### 3. Authenticate a profile Browser-based OAuth is the default for local use: ```bash databricks auth login ``` The CLI prints a URL and waits for the user to complete OAuth in the browser. **Always show the URL to the user as a clickable link** so they can open it themselves — the CLI does not return until authentication finishes. Credentials save to `~/.databrickscfg`. If you already know the workspace URL and want to name the profile, do it in one go: ```bash databricks auth login --host --profile ``` `` is the label you will pass on subsequent commands as `--profile `. If you skip `--profile`, the CLI uses the `DEFAULT` profile. For CI/CD, OAuth client credentials or a personal access token are better fits — see the [authentication section of the CLI doc](https://databricks.com/devhub/docs/tools/databricks-cli#authenticate) for the non-interactive flows. ### 4. Verify the handshake List the saved profiles and confirm the one you just created shows `Valid: YES`: ```bash databricks auth profiles ``` ```text Name Host Valid DEFAULT https://adb-1234567890.12.azuredatabricks.net YES my-prod-workspace https://mycompany.cloud.databricks.com YES ``` If the row shows `Valid: NO`, the saved token is stale. Re-run `databricks auth login --profile ` to refresh it. **Never proceed past this step if no profile is `Valid: YES`** — every downstream `databricks` command will fail with an auth error that looks like a template bug. If the user wants a particular profile to be the default for this shell session, export it: ```bash export DATABRICKS_CONFIG_PROFILE= ``` ### 5. Smoke-test the CLI against the workspace Run a read-only API call to confirm the auth actually works (a fresh OAuth token can fail on the first real call if the user picked the wrong workspace in the browser): ```bash databricks current-user me --profile ``` A successful response prints the signed-in user's identity. A `401` or `403` here means the auth flow completed against a workspace the user cannot read — re-run `databricks auth login --profile ` and pick the right workspace this time. --- # The cookbook the user copied The full cookbook prompt is below. This is what the user wants to focus on today. Once the local-bootstrap above passes and the intent questions are answered, work through this content step by step. --- title: "Operational Data Analytics" summary: "End-to-end setup for analyzing operational database data in the lakehouse: Unity Catalog with external storage, Lakebase provisioning, Lakehouse Sync CDC replication, and a medallion architecture pipeline with silver and gold layers." --- # Operational Data Analytics End-to-end setup for analyzing operational database data in the lakehouse: Unity Catalog with external storage, Lakebase provisioning, Lakehouse Sync CDC replication, and a medallion architecture pipeline with silver and gold layers. ## Prerequisites ### Set Up Unity Catalog with External Storage This template creates a Unity Catalog catalog backed by an external S3 bucket and requires AWS-specific privileges both in Databricks and in AWS IAM. - **Databricks CLI authenticated.** Run `databricks auth profiles` and confirm at least one profile shows `Valid: YES`. If none do, authenticate with `databricks auth login --host --profile `. - **AWS workspace.** This template uses S3 and IAM; it does not apply to Azure or GCP workspaces. Confirm your workspace host is `*.cloud.databricks.com`. - **Unity Catalog enabled on the workspace.** Run `databricks catalogs list --profile ` and confirm the command succeeds (the system catalogs `main` and `system` should appear). A `not enabled` error means the workspace is not attached to a Unity Catalog metastore. - **Metastore privileges to create credentials and catalogs.** You need `CREATE STORAGE CREDENTIAL`, `CREATE EXTERNAL LOCATION`, and `CREATE_CATALOG` on the metastore. If any CLI call in this template returns `PERMISSION_DENIED`, ask your metastore admin to grant the missing privilege. - **An S3 bucket and IAM role in the same AWS account and region as your workspace.** The template walks through pointing a storage credential at the IAM role; you must already have permissions in AWS to create the role and policy. ### Create a Lakebase Instance Verify these Databricks workspace features are enabled before starting. If any check fails, ask your workspace admin to enable the feature. - **Databricks CLI authenticated.** Run `databricks auth profiles` and confirm at least one profile shows `Valid: YES`. If none do, authenticate with `databricks auth login --host --profile `. - **Lakebase Postgres available in the workspace.** Run `databricks postgres list-projects --profile ` and confirm the command succeeds (an empty list is fine — you are about to create the first project). A `not enabled` or permission error means Lakebase is not available to this identity. ### Lakebase Change Data Feed: Sync Lakebase to Unity Catalog (Autoscaling) Verify these Databricks workspace features are enabled before starting. Lakehouse Sync is a Beta feature and has stricter workspace requirements than most other templates. - **Databricks CLI authenticated.** Run `databricks auth profiles` and confirm at least one profile shows `Valid: YES`. If none do, authenticate with `databricks auth login --host --profile `. - **AWS workspace in an Autoscaling region.** Lakehouse Sync is currently available in **Beta on AWS only**. Azure is not yet supported. Confirm your workspace host is `*.cloud.databricks.com` (AWS) rather than `*.azuredatabricks.net` (Azure). - **A Lakebase Autoscaling project with tables.** Run `databricks postgres list-projects --profile ` and confirm your Autoscaling project appears. A `not enabled` error means Lakebase is unavailable to this identity. This template does not cover project creation — see [Create a Lakebase Instance](https://databricks.com/devhub/templates/lakebase-create-instance) if you need one. - **Unity Catalog access.** Run `databricks catalogs list --profile ` and confirm the destination catalog and schema you want to replicate into are present. You will select them when enabling Lakehouse Sync in Step 3. ### Sync Tables: Unity Catalog to Lakebase (Autoscaling) This template creates a synced table that mirrors a Unity Catalog table into Lakebase Postgres. Verify these Databricks workspace features are enabled before starting. - **Databricks CLI authenticated.** Run `databricks auth profiles` and confirm at least one profile shows `Valid: YES`. If none do, authenticate with `databricks auth login --host --profile `. - **Lakebase Autoscaling available.** Run `databricks postgres list-projects --profile ` and confirm your Autoscaling project is listed. A `not enabled` error means Lakebase is not available to this identity. - **Project created via the `/database/` API (not the older `/postgres/` API).** Programmatic synced-table creation via `databricks database create-synced-database-table` only works on projects created through the newer `/database/` API. If your Autoscaling project was created via the older `/postgres/` endpoint, the CLI path in Step 1 is not available yet and you must create synced tables through the Databricks UI (**Catalog** → source table → **Create synced table**). This gap is expected to close in a future release. - **Unity Catalog source table with a primary key.** Run `databricks tables get .. --profile ` and confirm at least one column is declared as the table's primary key. Synced tables reject sources without a PK. - **External-storage catalog for the source (currently required for Sync Tables).** Sync Tables today requires the source UC catalog to use external storage. If your source catalog uses the metastore's default managed storage, complete the [Unity Catalog Setup](https://databricks.com/devhub/templates/unity-catalog-setup) template first and move the source table into an external-storage catalog. - **Change Data Feed enabled on the source table (for Triggered / Continuous mode only).** Skip this check if you plan to use Snapshot mode. Otherwise run the `ALTER TABLE .. SET TBLPROPERTIES (delta.enableChangeDataFeed = true);` statement from Step 1 against your SQL warehouse. ### Medallion Architecture from CDC History Tables This template builds a Lakeflow Declarative Pipeline on top of existing Lakehouse Sync CDC history tables. Verify these Databricks workspace features are enabled before starting. - **Databricks CLI authenticated.** Run `databricks auth profiles` and confirm at least one profile shows `Valid: YES`. If none do, authenticate with `databricks auth login --host --profile `. - **Lakeflow Declarative Pipelines (serverless) available.** Run `databricks pipelines list --profile ` and confirm the command succeeds (an empty list is fine). A permission or `not enabled` error means Lakeflow Pipelines is not available to this identity. - **Unity Catalog access with a writable destination schema.** Run `databricks catalogs list --profile ` and confirm your destination catalog is listed. You will need `USE_CATALOG` on the catalog and `USE_SCHEMA` + `CREATE_TABLE` on the destination schema to publish silver and gold tables. - **Bronze CDC history tables already in Unity Catalog.** Complete the [Lakebase Change Data Feed (Autoscaling, Lakehouse Sync)](https://databricks.com/devhub/templates/lakebase-change-data-feed-autoscaling) template first so `lb__history` tables exist in the bronze schema. This template reads from those tables; it does not create them. ## Set Up Unity Catalog with External Storage Create a Unity Catalog catalog backed by an external S3 bucket. For most use cases, the default metastore-managed storage works fine and requires no extra setup (just `databricks catalogs create `). Use this template when you specifically need external storage. > **Note:** Sync Tables (syncing data from the lakehouse back to Lakebase) currently requires the source Unity Catalog catalog to use external storage. Default managed storage does not work with synced tables yet. If you plan to sync lakehouse data back to Lakebase, follow this template. This requirement is expected to be removed in a future release. ### When to use this - You plan to use Sync Tables to sync lakehouse data back to Lakebase (external storage is currently required for this) - You want to control the S3 bucket location, encryption, and lifecycle policies - You need cross-account or cross-workspace access to the underlying data ### 1. Create an IAM role for the storage credential Create an IAM role in AWS that grants Databricks access to your S3 bucket. The trust policy must allow the Databricks account to assume the role. Minimal IAM policy for the role: ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:PutObject", "s3:DeleteObject", "s3:ListBucket", "s3:GetBucketLocation" ], "Resource": ["arn:aws:s3:::", "arn:aws:s3:::/*"] } ] } ``` Note the IAM role ARN (e.g., `arn:aws:iam:::role/`). ### 2. Create a storage credential Register the IAM role as a storage credential in Unity Catalog: ```bash databricks storage-credentials create \ --json '{ "aws_iam_role": { "role_arn": "arn:aws:iam:::role/" } }' --profile ``` Verify: ```bash databricks storage-credentials get --profile ``` ### 3. Create an external location Map the S3 bucket path to the storage credential: ```bash databricks external-locations create \ s3:/// \ \ --comment "External storage for analytics catalog" \ --profile ``` Verify: ```bash databricks external-locations get --profile ``` ### 4. Create a catalog with external storage Create the catalog and point its managed storage to the external location: ```bash databricks catalogs create \ --storage-root s3:/// \ --comment "Catalog for operational and analytics data" \ --profile ``` All managed tables created in this catalog store their data in the specified S3 path instead of the metastore default. Verify: ```bash databricks catalogs get --profile ``` ### 5. Create a schema Create a schema within the catalog for your tables: ```bash databricks schemas create \ --comment "Schema for lakehouse tables" \ --profile ``` Verify: ```bash databricks schemas list --profile ``` ### What you end up with - A **storage credential** linked to your IAM role for S3 access - An **external location** mapping an S3 path to the credential - A **Unity Catalog catalog** storing managed table data in your S3 bucket - A **schema** ready for tables from Lakebase Change Data Feed, Lakehouse Sync, or Lakeflow Pipelines ### Troubleshooting | Issue | Fix | | ---------------------------------------------- | ----------------------------------------------------------------- | | `PERMISSION_DENIED` on credential creation | Confirm you have `CREATE STORAGE CREDENTIAL` on the metastore | | `ACCESS_DENIED` on S3 during validation | Verify the IAM role trust policy allows Databricks to assume it | | Bucket not found | Confirm the bucket exists in the same AWS region as the workspace | | Catalog creation fails with storage root error | Verify the external location covers the specified S3 path | #### References - [Create a storage credential and external location for S3](https://docs.databricks.com/aws/en/connect/unity-catalog/cloud-storage/s3/s3-external-location-manual) - [Create catalogs](https://docs.databricks.com/aws/en/catalogs/create-catalog.html) - [External locations CLI](https://docs.databricks.com/aws/en/dev-tools/cli/reference/external-locations-commands) - [Storage credentials CLI](https://docs.databricks.com/aws/en/dev-tools/cli/reference/storage-credentials-commands) --- ## Create a Lakebase Instance Provision a managed Lakebase Postgres project on Databricks and collect the connection values needed by downstream templates. ### 1. Create a Lakebase project Create a new Lakebase Postgres project. This provisions a managed Postgres cluster with a default branch and endpoint: ```bash databricks postgres create-project --profile ``` ### 2. Verify the project resources Confirm the branch, endpoint, and database were created: ```bash databricks postgres list-branches \ projects/ \ --profile -o json databricks postgres list-endpoints \ projects//branches/production \ --profile -o json databricks postgres list-databases \ projects//branches/production \ --profile -o json ``` ### 3. Note the connection values Record these values from the command output above. They are required by the Lakebase Data Persistence template and other Lakebase-dependent templates: | Value | JSON path | Used for | | ------------------------ | ----------------------------- | ----------------------------------------------------- | | Endpoint host | `...status.hosts.host` | `PGHOST`, `lakebase.postgres.host` | | Endpoint resource path | `...name` | `LAKEBASE_ENDPOINT`, `lakebase.postgres.endpointPath` | | Database resource path | `...name` | `lakebase.postgres.database` | | PostgreSQL database name | `...status.postgres_database` | `PGDATABASE`, `lakebase.postgres.databaseName` | #### References - [What is Lakebase?](https://databricks.com/devhub/docs/lakebase/overview) - [CLI reference for Lakebase](https://docs.databricks.com/aws/en/oltp/projects/cli) --- ## Replicate Lakebase Tables to Unity Catalog with Lakehouse Sync Replicate your Lakebase Autoscaling Postgres tables into Unity Catalog as managed Delta tables using Lakehouse Sync. CDC captures every row-level change and writes them as SCD Type 2 history, giving you a full audit trail queryable from the lakehouse. > This template is for **Lakebase Autoscaling** (projects/branches/endpoints with scale-to-zero). ### When to use this - You want to analyze operational data (orders, user activity, support tickets) in the lakehouse - You need a historical record of every insert, update, and delete from your Postgres tables - You want to join operational data with analytics data in Spark, SQL, or BI tools - You need to feed Lakebase data into downstream pipelines or ML models ### How it works > **Note:** Lakehouse Sync is currently in **Beta on AWS only** (all Autoscaling regions). Azure support is not yet available. It is a native Lakebase feature with no external compute, pipelines, or jobs required, and there is no incremental charge for replication beyond the underlying Lakebase compute and storage costs. Lakehouse Sync uses Change Data Capture (CDC) to stream changes from Lakebase Postgres into Unity Catalog. For each synced table, a Delta history table is created: ``` lb__history ``` Each row includes metadata columns: - `_change_type`: `insert`, `update_preimage`, `update_postimage`, or `delete` - `_lsn`: Log Sequence Number for ordering changes - `_commit_timestamp`: When the change was captured ### 1. Verify table replica identity Lakehouse Sync requires the right replica identity for capturing changes. Connect to your Lakebase database and check: ```sql SELECT n.nspname AS table_schema, c.relname AS table_name, CASE c.relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname = 'public' ORDER BY n.nspname, c.relname; ``` If a table shows `default` or `nothing`, set it to `FULL`: ```sql ALTER TABLE REPLICA IDENTITY FULL; ``` ### 2. Check for unsupported data types ```sql SELECT c.table_schema, c.table_name, c.column_name, c.udt_name AS data_type FROM information_schema.columns c JOIN pg_catalog.pg_type t ON t.typname = c.udt_name WHERE c.table_schema = 'public' AND c.table_name IN ( SELECT tablename FROM pg_tables WHERE schemaname = c.table_schema ) AND NOT ( c.udt_name IN ( 'bool', 'int2', 'int4', 'int8', 'text', 'varchar', 'bpchar', 'jsonb', 'numeric', 'date', 'timestamp', 'timestamptz', 'real', 'float4', 'float8' ) OR t.typcategory = 'E' ) ORDER BY c.table_schema, c.table_name, c.ordinal_position; ``` If unsupported types appear, restructure those columns before enabling sync. ### 3. Enable Lakehouse Sync > **Note:** This step is not yet available via CLI or REST API and must be completed through the Databricks UI: > > In **Catalog**, open your Autoscaling project → branch → **Lakehouse Sync** → **Start Sync**, then select the source database/schema, destination catalog/schema, and tables. ### 4. Monitor sync status Check active syncs from Postgres (the `wal2delta` schema only exists after Lakehouse Sync has been enabled in Step 3): ```sql SELECT * FROM wal2delta.tables; ``` ### 5. Query the history tables #### Latest state of each row ```sql SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY _lsn DESC) AS rn FROM ..lb__history WHERE _change_type IN ('insert', 'update_postimage', 'delete') ) WHERE rn = 1 AND _change_type != 'delete'; ``` #### Full change history for a record ```sql SELECT * FROM ..lb__history WHERE id = 12345 ORDER BY _lsn; ``` ### 6. Handle schema changes If you need to change a synced table's schema in Postgres, use the rename-and-swap pattern: ```sql CREATE TABLE users_v2 ( id INT PRIMARY KEY, name TEXT, new_column TEXT ); ALTER TABLE users_v2 REPLICA IDENTITY FULL; INSERT INTO users_v2 SELECT *, NULL FROM users; BEGIN; ALTER TABLE users RENAME TO users_backup; ALTER TABLE users_v2 RENAME TO users; COMMIT; ``` ### What you end up with - **Delta history tables** in Unity Catalog (`lb__history`) with full SCD Type 2 change tracking - **Continuous replication.** Changes stream from Postgres to Delta automatically. - **No external compute.** Lakehouse Sync is a native Lakebase feature. - Operational data queryable in Spark SQL, notebooks, BI tools, and downstream pipelines ### Troubleshooting | Issue | Fix | | -------------------------------- | ------------------------------------------------------------------ | | Table not appearing in sync | Ensure it has a primary key or `REPLICA IDENTITY FULL` | | Unsupported data type error | Check column types with the query in Step 2 | | Sync lag increasing | Check Lakebase endpoint health and compute scaling | | Missing changes on update/delete | Verify `REPLICA IDENTITY FULL`. `default` only captures PK columns | ### Limitations - **AWS only.** Lakehouse Sync Beta is available in all Autoscaling regions on AWS. Azure support is not yet available. - **No incremental charge.** Replication cost is included in your Lakebase compute and storage. - **Works alongside synced tables.** You can use Lakehouse Sync in a project/schema that also has synced tables. #### References - [Lakehouse Sync (Autoscaling)](https://docs.databricks.com/aws/en/oltp/projects/lakehouse-sync) - [Register Lakebase in Unity Catalog](https://docs.databricks.com/aws/en/oltp/projects/register-uc) - [SCD Type 2 in Databricks](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/scd) --- ## Sync a Unity Catalog Table to Lakebase Serve lakehouse data through Lakebase Autoscaling Postgres so your applications can query it with sub-10ms latency. This creates a synced table, a managed copy of your Unity Catalog table in Lakebase that stays up to date automatically. > This template is for **Lakebase Autoscaling** (projects/branches/endpoints with scale-to-zero). For Lakebase Provisioned (manually scaled instances), see the Provisioned Sync Tables template (coming soon). ### When to use this - Your app needs fast lookup-style queries against analytics data (user profiles, feature values, risk scores) - You want to serve gold tables, ML outputs, or enriched records through a standard Postgres connection - You need ACID transactions and sub-10ms reads alongside your operational state ### Choose a sync mode | Mode | Behavior | Best for | | -------------- | ---------------------------------------------- | ------------------------------------------------------------------------------------- | | **Snapshot** | One-time full copy | Source changes >10% of rows per cycle, or source doesn't support CDF (views, Iceberg) | | **Triggered** | Incremental updates on demand or on a schedule | Known cadence of changes, good cost/freshness balance | | **Continuous** | Real-time streaming (seconds of latency) | Changes must appear in Lakebase near-instantly | > **Triggered** and **Continuous** modes require [Change Data Feed (CDF)](https://docs.databricks.com/aws/en/delta/delta-change-data-feed) enabled on the source table. If it's not enabled, run: > > ```sql > ALTER TABLE ..
SET TBLPROPERTIES (delta.enableChangeDataFeed = true); > ``` ### Sync throughput Autoscaling CUs are physically 8x smaller than Provisioned CUs, so per-CU throughput differs: | Mode | Rows/sec per CU | | ---------------------------------------- | --------------- | | **Snapshot** (initial + full refresh) | ~2,000 | | **Triggered / Continuous** (incremental) | ~150 | > A 10x speedup for large-table snapshot sync (writing Postgres pages directly, leveraging separation of storage and compute) is coming for Autoscaling only. ### 1. Create a synced table ```bash databricks database create-synced-database-table \ --json '{ "name": "..", "database_instance_name": "", "logical_database_name": "", "spec": { "source_table_full_name": "..", "primary_key_columns": [""], "scheduling_policy": "", "create_database_objects_if_missing": true } }' --profile ``` > If your Lakebase database is **registered as a Unity Catalog catalog**, you can omit `database_instance_name` and `logical_database_name`. Verify: ```bash databricks database get-synced-database-table .. --profile ``` > **Important:** If your Autoscaling project was created via the `/postgres/` API (not `/database/`), programmatic synced table creation is not yet available via CLI. Use the Databricks UI as a fallback. In **Catalog**, select the source table → **Create synced table**, then choose your Lakebase project, branch, sync mode, and pipeline. This gap is expected to close soon. ### 2. Configure pipeline reuse How you set up pipelines depends on your sync mode: | Sync mode | Recommendation | Why | | ------------------------ | -------------------------------------- | -------------------------------------------------------------------------------------------------------------------- | | **Continuous** | **Reuse** a pipeline across ~10 tables | Cost-advantageous (e.g., 1 pipeline for 10 tables ≈ $204/table/month vs $2,044/table/month for individual pipelines) | | **Snapshot / Triggered** | **Separate** pipelines per table | Allows re-snapshotting individual tables without impacting others | ### 3. Schedule ongoing syncs The initial snapshot runs automatically on creation. For **Snapshot** and **Triggered** modes, subsequent syncs need to be triggered. > **Note:** Table-update triggers for sync pipelines are not yet available via CLI and must be configured through the Databricks UI: **Workflows** → create/open a job → add a **Database Table Sync pipeline** task → **Schedules & Triggers** → add a **Table update** trigger pointing to your source table. Trigger a sync update programmatically via the Databricks SDK: ```python from databricks.sdk import WorkspaceClient w = WorkspaceClient() table = w.database.get_synced_database_table( name=".." ) pipeline_id = table.data_synchronization_status.pipeline_id w.pipelines.start_update(pipeline_id=pipeline_id) ``` ### 4. Query the synced data in Postgres Once synced, the table is available in Lakebase Postgres. The Unity Catalog schema becomes the Postgres schema: ```sql SELECT * FROM ""."" WHERE "user_id" = 12345; ``` Connect with any standard Postgres client (psql, DBeaver, your application's Postgres driver). ### What you end up with - A **synced table** in Unity Catalog that tracks the sync pipeline - A **read-only Postgres table** in Lakebase that your apps can query with sub-10ms latency - A **managed Lakeflow pipeline** that keeps the data in sync based on your chosen mode - Up to **16 connections** per sync to your Lakebase database ### Important constraints - **Primary key is mandatory.** Synced tables always require a primary key. It enables efficient point lookups and incremental updates. Rows with nulls in PK columns are excluded from the sync. - **Duplicate primary keys fail the sync** unless you configure a `timeseries_key` for deduplication (latest value wins per PK). Using a timeseries key has a performance penalty. - **Schema changes**: For Triggered/Continuous mode, only **additive** changes (e.g., adding a column) propagate. Dropping or renaming columns requires recreating the synced table. - **FGAC tables**: Direct sync of Fine-Grained Access Control tables fails. **Workaround**: create a view (`SELECT * FROM table`), then sync the view in Snapshot mode. Caveat: runs as the sync creator and only sees their visible rows. - **Connection limits**: Autoscaling supports up to 4,000 concurrent connections (varies by compute size). Each sync uses up to 16 connections. - **Read-only in Postgres**: Synced tables should only be read from Postgres. Writing to them interferes with the sync pipeline. ### Cost guidance Cost formula: `[Rows / (Speed × CUs × 3600)] × DLT Hourly Rate` Example costs (181M rows, 1 CU, $2.80/hr DLT rate): | Mode | Monthly cost | | ---------------------------------- | ------------ | | Snapshot (daily) | ~$2,110 | | Triggered (daily, 5% changes) | ~$1,407 | | Continuous (10 tables, 1 pipeline) | ~$204/table | | Continuous (1 table, 1 pipeline) | ~$2,044 | ### Troubleshooting | Issue | Fix | | ----------------------------------- | ----------------------------------------------------------------------------------------- | | CDF not enabled warning | Run `ALTER TABLE ... SET TBLPROPERTIES (delta.enableChangeDataFeed = true)` on the source | | Schema not visible in create dialog | Confirm you have `USE_SCHEMA` and `CREATE_TABLE` on the target schema | | Null bytes in string columns | Clean source data: `SELECT REPLACE(col, CAST(CHAR(0) AS STRING), '') AS col FROM table` | | Sync failing | Check the pipeline in the synced table's Overview tab for error details | | FGAC table sync fails | Create a view over the table and sync the view in Snapshot mode | | Duplicate primary key failure | Add a `timeseries_key` to deduplicate (latest wins) | #### References - [Synced tables (Autoscaling)](https://docs.databricks.com/aws/en/oltp/projects/sync-tables) - [Change Data Feed](https://docs.databricks.com/aws/en/delta/delta-change-data-feed) - [Lakebase Autoscaling](https://docs.databricks.com/aws/en/oltp/projects/) - [DevHub: Data Lakehouse overview](https://databricks.com/devhub/docs/lakehouse/overview) --- ## Medallion Architecture from CDC History Tables Transform CDC history tables produced by Lakehouse Sync into a medallion architecture with bronze, silver, and gold layers using Lakeflow Declarative Pipelines. This turns raw change-data-capture records into clean, business-ready analytics tables in Unity Catalog. ### When to use this - You have Lakehouse Sync CDC history tables (`lb_
_history`) in Unity Catalog from a Lakebase operational database - You want to build a layered data architecture (bronze → silver → gold) on top of operational data - You need clean current-state views, deduplication, and business aggregations for BI, ML, or Genie analytics - You want automated, incremental pipeline refreshes instead of manual SQL queries ### How the layers map to CDC data | Layer | Purpose | Source | Output | | ---------- | ------------------------------------------------------ | ------------------------------------------ | ----------------------------------------------------- | | **Bronze** | Raw CDC records with full history | Lakehouse Sync `lb_
_history` tables | No transformation needed; these tables already exist | | **Silver** | Current state of each record, deduplicated and cleaned | Bronze history tables | One streaming table per entity with latest state only | | **Gold** | Business aggregations and domain-specific metrics | Silver tables | Materialized views with aggregations, joins, and KPIs | ### 1. Scaffold a pipeline project Use the Databricks CLI to scaffold a Lakeflow Declarative Pipelines project: ```bash databricks bundle init lakeflow-pipelines \ --config-file <(echo '{"project_name": "operational_analytics", "language": "sql", "serverless": "yes"}') \ --profile < /dev/null ``` Enter the project directory: ```bash cd operational_analytics ``` ### 2. Configure the pipeline catalog and schema Edit `resources/operational_analytics.pipeline.yml` to target your Unity Catalog schema: ```yaml resources: pipelines: operational_analytics: name: operational_analytics catalog: schema: development: true serverless: true libraries: - file: path: src/ ``` The pipeline publishes all datasets to `.` by default. ### 3. Build the silver layer: current state from CDC For each entity, create a SQL file in `src/` that extracts the latest state from the bronze CDC history table. The silver layer deduplicates by primary key and excludes deleted records. Create `src/silver_.sql` (e.g., `src/silver_orders.sql`): ```sql CREATE OR REFRESH MATERIALIZED VIEW silver_ COMMENT "Current state of records, deduplicated from CDC history" AS SELECT * EXCEPT (rn, _change_type, _lsn, _commit_timestamp) FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ORDER BY _lsn DESC ) AS rn FROM ..lb__history WHERE _change_type IN ('insert', 'update_postimage', 'delete') ) WHERE rn = 1 AND _change_type != 'delete' ``` Replace `` with the entity's primary key column(s), `.` with the catalog and schema where Lakehouse Sync writes the history tables, and `` with the table name. Repeat for each entity you want in the silver layer. ### 4. Build the gold layer: business aggregations Gold layer tables are materialized views that aggregate, join, or reshape silver tables for specific analytics use cases. Create `src/gold_.sql` (e.g., `src/gold_daily_order_summary.sql`): ```sql CREATE OR REFRESH MATERIALIZED VIEW gold_daily_order_summary COMMENT "Daily order counts and revenue by status" AS SELECT DATE_TRUNC('day', created_at) AS order_date, status, COUNT(*) AS order_count, SUM(total_amount) AS total_revenue FROM silver_orders GROUP BY DATE_TRUNC('day', created_at), status ``` Gold tables read from silver tables within the same pipeline. Use `GROUP BY`, `JOIN`, window functions, or any SQL to build the business view you need. ### 5. Add data quality expectations (optional) Add expectations to silver or gold tables to enforce data quality constraints: ```sql CREATE OR REFRESH MATERIALIZED VIEW silver_ ( CONSTRAINT valid_primary_key EXPECT ( IS NOT NULL) ON VIOLATION DROP ROW, CONSTRAINT valid_timestamp EXPECT (created_at IS NOT NULL) ON VIOLATION DROP ROW ) COMMENT "Current state of records with quality enforcement" AS SELECT ... ``` Expectations catch data issues early and can either warn, drop bad rows, or fail the pipeline update. ### 6. Deploy and run the pipeline Validate, deploy, and run: ```bash databricks bundle validate --profile databricks bundle deploy -t dev --profile databricks bundle run operational_analytics -t dev --profile ``` Monitor the pipeline in the Databricks UI under **Workflows** → **Pipelines**. ### 7. Schedule ongoing refreshes Add a job to refresh the pipeline on a schedule. Create `resources/operational_analytics_job.job.yml`: ```yaml resources: jobs: operational_analytics_job: trigger: periodic: interval: 1 unit: HOURS tasks: - task_key: refresh_pipeline pipeline_task: pipeline_id: ${resources.pipelines.operational_analytics.id} ``` Deploy the schedule: ```bash databricks bundle deploy -t dev --profile ``` ### 8. Query the results Silver and gold tables are standard Unity Catalog tables. Query them from any connected tool: ```sql -- Current state of an entity SELECT * FROM ..silver_orders WHERE customer_id = 12345; -- Business aggregation SELECT * FROM ..gold_daily_order_summary ORDER BY order_date DESC; ``` Use these tables as sources for Genie spaces, dashboards, notebooks, or ML pipelines. ### What you end up with - **Bronze layer.** Lakehouse Sync CDC history tables (already exist, no pipeline needed). - **Silver layer.** Deduplicated current-state materialized views per entity. - **Gold layer.** Business aggregations and metrics as materialized views. - **Scheduled pipeline.** Lakeflow Declarative Pipeline refreshing silver and gold layers incrementally. - **Unity Catalog tables.** All layers queryable via SQL, Spark, BI tools, and Genie. ### Agent skill recommendations For implementing each layer, the following Databricks agent skills provide detailed guidance: | Skill | Use for | | ---------------------- | ------------------------------------------------------------------------ | | `databricks-pipelines` | Lakeflow Declarative Pipeline syntax, dataset types, deployment workflow | | `databricks-core` | CLI authentication, profile management, data exploration | | `databricks-lakebase` | Lakebase project and branch management, Postgres access | ### Troubleshooting | Issue | Fix | | ------------------------------------------ | ------------------------------------------------------------------------------------ | | Silver table returns no rows | Verify the bronze history table has data: `SELECT COUNT(*) FROM lb__history` | | `TABLE_OR_VIEW_NOT_FOUND` for bronze table | Use the fully-qualified name: `..lb__history` | | Gold aggregation includes deleted records | Confirm the silver layer filters `_change_type != 'delete'` | | Pipeline fails on deploy | Run `databricks bundle validate` first to catch config errors | | Incremental refresh not picking up changes | Verify Lakehouse Sync is active and the bronze table is updating | #### References - [Medallion architecture](https://docs.databricks.com/aws/en/lakehouse/medallion) - [Lakeflow Declarative Pipelines](https://docs.databricks.com/aws/en/delta-live-tables/) - [Materialized views](https://docs.databricks.com/aws/en/ldp/materialized-views) - [Lakehouse Sync](https://docs.databricks.com/aws/en/oltp/projects/lakehouse-sync) - [DevHub: Pipelines and freshness](https://databricks.com/devhub/docs/lakehouse/pipelines)