Connecting Azure Databricks to Churney

Here you can read our guide for connecting your Azure Databricks workspace with Churney. In this guide, we go over the steps for giving Churney access to your data with hashed views and minimal permissions, using Entra ID identities only. No passwords, personal access tokens, client secrets, SAS tokens or storage keys are exchanged; only short-lived federated tokens are used.

How the integration works

On a schedule agreed with you, Churney's sync:

1. Logs in to your SQL warehouse as an Entra ID application you register, and runs CREATE EXTERNAL TABLE ... USING PARQUET LOCATION 'abfss://...' for each hashed view, which writes the query result as Parquet files to a storage container you own. Unity Catalog does the writing through an Access Connector identity you control.
2. Copies those files from your container to Google Cloud Storage with Google's Storage Transfer Service.
3. Loads them into BigQuery.

The setup is two hand-overs:

1. Churney sends you two Google Cloud identity ids. You need them in the "Register the Churney application" step; every other step can be done before they arrive.
2. You send Churney the values listed under "What to share with Churney" at the end of this guide. Churney then runs the first sync.

Prerequisites

Before you start, make sure that:

• Your workspace has Unity Catalog enabled and the tables you share are in a Unity Catalog catalog. Workspaces created since late 2023 have it by default; older ones need a metastore attached.
• You have a SQL warehouse Churney can use. Serverless is fine and cheapest to keep idle; a small Pro warehouse with auto-stop also works.
• You are a workspace admin in Databricks and can create Entra ID app registrations, a storage account, an Access Connector and role assignments in your Azure subscription, or have someone who can.
• You have the Azure CLI installed and are logged in (az login), with the databricks extension (az extension add -n databricks).

Throughout the guide, replace the values in angle brackets. The Databricks REST API calls below authenticate with an Entra ID token issued to you, the logged-in Azure CLI user. The id 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d is the fixed, tenant-independent application id of the Azure Databricks service, so the token is scoped to Databricks and nothing else:

DBX=https://<your-workspace>.azuredatabricks.net
TOKEN=$(az account get-access-token --resource 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d --query accessToken -o tsv)

‍What kind of data is required?

The short answer is as much as possible. The long answer is that Churney requires data about:

• Payments
• Trials (if applicable)
• User demographic (if available)
• User activity
• Attribution Data: Source of truth for campaign performance (UTM/MMP/ad network)

Additionally, we need to know the Azure region of your workspace.

Generating hashed views

If you would like Churney's help with the hashed views, run the following query in a SQL editor, where <catalog> and <schema> hold the tables you wish to share with Churney:

SELECT table_schema, table_name, column_name, data_type
FROM <catalog>.information_schema.columns
WHERE table_schema = '<schema>';

‍Download the output as CSV and share it with Churney. We will come back to you with a script that creates the hashed views. Otherwise, continue with the example below.

Create hashed views

For context, Facebook has a guide on how to hash contact information for their conversion API: https://developers.facebook.com/docs/marketing-api/conversions-api/parameters/customer-information-parameters. Google has a similar guide for enhanced conversions: https://developers.google.com/google-ads/api/docs/conversions/enhance-conversions. We want to hash columns that would allow one to determine the identity of a user: first name, last name, birthday, street address, phone number, email address, IP address.

Create a schema churney_views in your catalog and, for each table you share, a view that hashes the personally identifiable columns with SHA-256. Also create the empty schema churney_exports, which Churney uses for the temporary external tables it writes during each sync:

CREATE SCHEMA IF NOT EXISTS <catalog>.churney_views;
CREATE SCHEMA IF NOT EXISTS <catalog>.churney_exports;

CREATE OR REPLACE VIEW <catalog>.churney_views.users AS
SELECT
    user_id,
    sha2(lower(trim(email)), 256)                                        AS email,
    sha2(lower(trim(full_name)), 256)                                    AS full_name,
    sha2(regexp_replace(phone, '[()\\-+ ]', ''), 256)                    AS phone,
    sha2(date_format(birthday, 'yyyyMMdd'), 256)                         AS birthday,
    country_code,
    signup_date,
    created_at
FROM <catalog>.raw_data.users;

‍Only the hashed views are shared with Churney; the raw tables stay private. Give every table with events a TIMESTAMP column Churney can use for incremental loads, for example event_time or created_at.

Create a storage account and container for Churney

Churney's unload writes Parquet files to an Azure Data Lake Storage Gen2 container. Create it in the same region as your workspace, with hierarchical namespace enabled, and let files expire after one day.

az group create --name churney-export --location <region>

az storage account create \
  --name <storageaccount> --resource-group churney-export --location <region> \
  --sku Standard_LRS --kind StorageV2 --enable-hierarchical-namespace true \
  --allow-shared-key-access false

az storage container create --account-name <storageaccount> --name churney-unload --auth-mode login

az storage account management-policy create --account-name <storageaccount> --resource-group churney-export --policy '{
  "rules": [{"enabled": true, "name": "expire-churney-unload", "type": "Lifecycle",
    "definition": {"actions": {"baseBlob": {"delete": {"daysAfterModificationGreaterThan": 1}}},
                   "filters": {"blobTypes": ["blockBlob"], "prefixMatch": ["churney-unload/"]}}}]}'

--allow-shared-key-access false disables storage account keys entirely; every access in this guide uses Entra ID.

Give Unity Catalog write access to the container

Databricks writes to external storage through an Access Connector for Azure Databricks, a managed identity that Unity Catalog uses on your behalf. Create one, give it write access to the container, and let it request the short-lived signing keys Unity Catalog uses for writes:

az databricks access-connector create --resource-group churney-export --name churney-unload-access --location <region> --identity-type SystemAssigned

CONNECTOR_ID=$(az databricks access-connector show --resource-group churney-export --name churney-unload-access --query id -o tsv)
CONNECTOR_PRINCIPAL=$(az databricks access-connector show --resource-group churney-export --name churney-unload-access --query identity.principalId -o tsv)
STORAGE_ID=$(az storage account show --name <storageaccount> --resource-group churney-export --query id -o tsv)

az role assignment create --assignee-object-id $CONNECTOR_PRINCIPAL --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Data Contributor" --scope "$STORAGE_ID/blobServices/default/containers/churney-unload"
az role assignment create --assignee-object-id $CONNECTOR_PRINCIPAL --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Delegator" --scope "$STORAGE_ID"

‍Unity Catalog checks that the person registering the connector holds Contributor on it, as a direct assignment; an inherited Owner role is not enough:‍


az role assignment create --assignee <your-email> --role Contributor --scope "$CONNECTOR_ID"

Register the connector as a storage credential and the container as an external location. Both are Databricks REST calls; run them a minute after the role assignments so Azure has propagated them:‍


curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  $DBX/api/2.1/unity-catalog/storage-credentials \
  -d "{\"name\":\"churney_unload\",\"azure_managed_identity\":{\"access_connector_id\":\"$CONNECTOR_ID\"}}"

curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  $DBX/api/2.1/unity-catalog/external-locations \
  -d '{"name":"churney_unload","url":"abfss://churney-unload@<storageaccount>.dfs.core.windows.net/","credential_name":"churney_unload"}'

Register the Churney application

Churney authenticates as an Entra ID application in your tenant. Instead of a client secret, the application trusts two Google Cloud identities through federated credentials. Use the two ids Churney sent you: <churney-sync-id> for the SQL login and <churney-transfer-id> for the file transfer.

APP_ID=$(az ad app create --display-name churney-sync --sign-in-audience AzureADMyOrg --query appId -o tsv)
SP_ID=$(az ad sp create --id $APP_ID --query id -o tsv)

az ad app federated-credential create --id $APP_ID --parameters '{
  "name": "churney-sync",
  "issuer": "https://accounts.google.com",
  "subject": "<churney-sync-id>",
  "audiences": ["api://AzureADTokenExchange"],
  "description": "Churney sync: logs in to the SQL warehouse"}'

az ad app federated-credential create --id $APP_ID --parameters '{
  "name": "churney-transfer",
  "issuer": "https://accounts.google.com",
  "subject": "<churney-transfer-id>",
  "audiences": ["api://AzureADTokenExchange"],
  "description": "Google Storage Transfer Service: reads the churney-unload container"}'

# Read the exported files
az role assignment create --assignee-object-id $SP_ID --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Data Reader" --scope "$STORAGE_ID/blobServices/default/containers/churney-unload"

Add the application to your workspace as a service principal and let it use the SQL warehouse. Find the warehouse id in the SQL warehouse's Connection details; it is the last part of the HTTP path:‍‍

curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  $DBX/api/2.0/preview/scim/v2/ServicePrincipals \
  -d "{\"schemas\":[\"urn:ietf:params:scim:schemas:core:2.0:ServicePrincipal\"],\"applicationId\":\"$APP_ID\",\"displayName\":\"churney-sync\",\"active\":true}"

curl -s -X PATCH -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  $DBX/api/2.0/permissions/sql/warehouses/<warehouse-id> \
  -d "{\"access_control_list\":[{\"service_principal_name\":\"$APP_ID\",\"permission_level\":\"CAN_USE\"}]}"

Finally, grant the Unity Catalog privileges in a SQL editor. The principal is the application (client) id:‍


GRANT USE CATALOG ON CATALOG <catalog> TO `<app-id>`;
GRANT USE SCHEMA, SELECT ON SCHEMA <catalog>.churney_views TO `<app-id>`;
GRANT USE SCHEMA, CREATE TABLE ON SCHEMA <catalog>.churney_exports TO `<app-id>`;
GRANT READ FILES, WRITE FILES, CREATE EXTERNAL TABLE ON EXTERNAL LOCATION churney_unload TO `<app-id>`;

The application can read the hashed views and write temporary external tables into churney_exports and the container; it has no access to your raw tables.

What to share with Churney

When the steps above are done, send Churney:

Churney fills these into your sync configuration and runs the first sync; nothing else needs to happen on your side.

Setup & onboarding overview

Once you have shared the details above, Churney:

1. Creates a Google Cloud project for your data and, in it, the service account and the Storage Transfer Service identity whose ids you add as federated credentials.
2. Runs a first sync of every view in churney_views and checks the result with you.
3. Schedules the recurring sync at the cadence agreed with you.

Churney holds no credentials for your tenant or workspace at any point: no personal access token, no client secret, no storage key, no SAS token. Access can be revoked by deleting the churney-sync app registration or removing the service principal from the workspace.

Authentication

Every connection uses Entra ID and short-lived tokens:

• Churney → Databricks. Churney's sync runs as a Google Cloud service account. For each run, Google issues it a signed identity token, which Entra ID exchanges for an access token for the churney-sync application through the federated credential you created. Azure Databricks accepts that Entra token directly as the SQL login. The token is valid for about an hour and is never stored.
• Databricks → container. Unity Catalog writes the Parquet files as the Access Connector's managed identity, which Azure manages. Churney never sees these credentials.
• Churney → container. Google's Storage Transfer Service authenticates as the churney-sync application through the second federated credential, with read-only access to the container.

Data sync

The sync is incremental: rather than re-copying entire tables each time, we only pull rows that are new or have changed since the last successful sync, using the timestamp column you designate for each view. We also apply a configurable safety-margin window so that late-arriving or recently-updated records are not missed. Each table is exported independently, so if one table fails, the others still complete and the specific failures are reported clearly.

Each run first drops the previous run's external tables in churney_exports, then writes to a fresh folder in the container, named after the run; the lifecycle rule deletes the files after one day. Unload queries run on your SQL warehouse and consume its compute.

‍