Databricks

Databricks connects a SQL warehouse to Sonora and maps its tables to Sonora records. It can populate product usage, contacts, meetings, engagements, issues, and custom fields — and can either create customers from a warehouse account table or attach data to customers you already have from a CRM.

Authentication uses a Databricks service principal (OAuth machine-to-machine). Sonora connects read-only with the client ID and secret you provide; no personal access tokens are used.

What Sonora reads

You choose which tables map to which Sonora records when you set up the mapping. There are two identity modes:

• Create customers from the warehouse (default) — a warehouse account table becomes your Sonora customers, and every other table links to them.
• Attach to existing customers — your customers already come from a CRM (Salesforce or HubSpot); warehouse rows match those customers by an account-id column, and nothing new is created. Turn on the Attach to existing customers toggle before you generate the mapping.

Shape your usage tables

Product usage imports aggregated values, not raw events. Each row is one account on one date, and each numeric column is a value you have already computed for that account. Sonora never adds rows together, so mapping an event, click or session log as product usage does not work: each event would be read as the account’s whole value for that day. Roll the events up in a view and map the view.

How to aggregate depends on what the column counts:

• Counts (events, API calls, reports run). One value per account per day. Sonora adds days up into weeks and months.
• Snapshots (seats, storage, licenses). One value per account per day. Keep the metric’s aggregation on Last so a month shows its closing value.
• Distinct users (active users, active admins). Count each period in your warehouse. Weekly active users are not the sum of daily active users, because someone active on Monday and Tuesday is one weekly user, not two. Sonora cannot recover distinct users from daily totals, so count days, ISO weeks (starting Monday) and calendar months separately. This is the same split Sonora computes for Mixpanel.

Give each period its own view and map each as a product usage table. Product usage syncs incrementally by date and does not re-read a date once newer rows have landed, so a weekly or monthly count added later to a daily row would be missed. Date each row by the first day of its period (the Monday, or the 1st), and publish a week or month only once it is complete.

View	account_id	date	Columns
usage_daily	0015g00000AbCdE	2026-09-29	events 1102, active_users_day 37
usage_weekly	0015g00000AbCdE	2026-09-28	active_users_week 88
usage_monthly	0015g00000AbCdE	2026-09-01	active_users_month 131

The three views, built from an event log:

CREATE OR REPLACE VIEW analytics.usage_daily AS
SELECT account_id, CAST(event_timestamp AS DATE) AS date,
  COUNT(*) AS events,
  COUNT(DISTINCT user_id) AS active_users_day
FROM analytics.events
GROUP BY 1, 2;

-- date_trunc('WEEK', …) starts weeks on Monday.
CREATE OR REPLACE VIEW analytics.usage_weekly AS
SELECT account_id, CAST(date_trunc('WEEK', event_timestamp) AS DATE) AS date,
  COUNT(DISTINCT user_id) AS active_users_week
FROM analytics.events
WHERE event_timestamp < date_trunc('WEEK', current_date())
GROUP BY 1, 2;

CREATE OR REPLACE VIEW analytics.usage_monthly AS
SELECT account_id, CAST(date_trunc('MONTH', event_timestamp) AS DATE) AS date,
  COUNT(DISTINCT user_id) AS active_users_month
FROM analytics.events
WHERE event_timestamp < date_trunc('MONTH', current_date())
GROUP BY 1, 2;

The WHERE clauses hold back the week and month in progress. Today’s daily row is the newest date in its view, so it is re-read on every sync and fills in through the day.

Sonora creates every new metric with Last aggregation. After the first sync, open Settings → Products:

• For each count (events), set Aggregation to Sum, or a week shows its last day instead of its total.
• For each active-user metric, keep Last and set Chart tabs to the one period it counts (active_users_week → Week). The chart then never rolls one period’s count into another.

Prerequisites

• A Databricks SQL warehouse Sonora can query.
• Permission to create a service principal and grant it access (metastore admin or catalog owner, for the grants below).
• To attach to existing customers: a configured Salesforce or HubSpot connection in Sonora.

Setup

1. Create a service principal

In Databricks, click your username (top bar) → Settings → Identity and access, and next to Service principals click Manage. Add a service principal (or select an existing one) — adding it here also assigns it to the current workspace, which must be the workspace hosting your SQL warehouse. On its Entitlements, make sure Databricks SQL access is enabled; without it the principal can’t query SQL warehouses even after the grants below. Open the principal’s Secrets tab, click Generate secret, and copy both the secret and the client ID — the secret is shown only once. You’ll enter the client ID (the principal’s application ID) and secret in Sonora.

2. Grant it access

Two separate grants are required, and they fail at different points:

• The SQL warehouse. Grant the service principal Can use on the warehouse in your connection’s HTTP path. This is what Sonora’s connection test checks — it exchanges a token and reads the warehouse, but touches no data.
• The data. The dry-run and every import run real SELECTs, so the principal also needs read access to each catalog, schema, and table your mapping references. A passing connection test does not imply data access.

Unity Catalog access is hierarchical — reading a table requires three privileges: USE CATALOG on its catalog, USE SCHEMA on its schema, and SELECT on the table. Granting USE SCHEMA and SELECT at the catalog level cascades to everything under it.

Run one block per catalog your mapping touches, as a metastore admin or catalog owner. Replace the catalog name and use the service principal’s application (client) ID:

GRANT USE CATALOG ON CATALOG `your_catalog` TO `<service-principal-app-id>`;
GRANT USE SCHEMA  ON CATALOG `your_catalog` TO `<service-principal-app-id>`;
GRANT SELECT      ON CATALOG `your_catalog` TO `<service-principal-app-id>`;

For least privilege, grant on only the schemas and tables the mapping reads instead of the whole catalog:

GRANT USE CATALOG ON CATALOG `your_catalog` TO `<service-principal-app-id>`;
GRANT USE SCHEMA  ON SCHEMA  `your_catalog`.`your_schema` TO `<service-principal-app-id>`;
GRANT SELECT      ON TABLE   `your_catalog`.`your_schema`.`your_table` TO `<service-principal-app-id>`;

<service-principal-app-id> is the principal’s application (client) ID — the same value you enter as the Client ID in Sonora, not your own user.

Metadata access. Discovering tables and generating a mapping also read table and column metadata from Databricks’ system.information_schema. In most workspaces the system catalog is readable by default; if Discover tables or generation fails with a permission error on system.information_schema, ask a metastore admin to grant the service principal access to it:

GRANT USE CATALOG ON CATALOG `system` TO `<service-principal-app-id>`;
GRANT USE SCHEMA  ON SCHEMA  `system`.`information_schema` TO `<service-principal-app-id>`;

3. Connect in Sonora

In Settings → Integrations → Databricks → Configure, enter:

Field	Example
Workspace host	https://dbc-1234abcd-5678.cloud.databricks.com
SQL warehouse HTTP path	/sql/1.0/warehouses/abc123
Client ID	the service principal’s application (client) ID
Client secret	the OAuth secret you generated

Find the host and HTTP path under SQL Warehouses → your warehouse → Connection details in Databricks. Use Test connection to verify the warehouse grant (it doesn’t check data access), then Configure to save the connection — or Update when editing an existing one.

4. Generate the mapping

Open Generate mapping and pick a source:

• From connection — select the tables to map, then Generate. Sonora reads your warehouse, proposes a mapping, and dry-runs it.
• From file — upload a file describing your tables (CSV, Excel, JSON, YAML, Markdown, or text) if you’d rather not connect for discovery.

If a CRM (Salesforce or HubSpot) is already connected and owns your customer records, turn on Attach to existing customers before generating. That keeps the CRM as the source of truth for who your customers are — warehouse rows attach to those existing customers by account id, and Databricks never creates or overwrites customer records.

Review the proposed mapping in the editor, adjust tables and columns as needed, and run the dry-run — it checks that your rows resolve (against the warehouse, or against your existing customers). It also reports any missing table grants, so it doubles as a check that access is in place. When it passes, press Update to save the mapping (Configure for a first-time connection).

Troubleshooting

Connection test passes but tables can’t be read — The warehouse grant is in place but the data grant isn’t (a passing test only checks the warehouse). Sonora flags the tables as “could not be read (missing or no grant)”, and the dry-run surfaces the underlying Databricks permission error. Grant the USE CATALOG / USE SCHEMA / SELECT chain on the affected catalog (step 2).

“Could not connect to the warehouse” — Confirm the workspace host and HTTP path match your warehouse’s Connection details exactly, and that the service principal has Can use on that warehouse.

Some tables were skipped — They couldn’t be read (missing or not granted). Grant SELECT on those tables and regenerate.

0% of keys resolve (attach mode) — The account-id column doesn’t match your existing customers’ external IDs. Confirm the column holds the same IDs as the CRM source you’re matching against (Salesforce Account IDs or HubSpot Company IDs), and that the correct identity source is selected in the editor.