Connecting Microsoft Fabric to Churney

Here you can read our guide for connecting your Microsoft Fabric Warehouse 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, client secrets, SAS tokens or storage keys are exchanged; only short-lived federated tokens are used.

How the integration works

Churney exports data through a pipeline in your tenant rather than querying it from outside. On a schedule agreed with you, Churney's sync:

1. Starts a run of a small data pipeline that Churney maintains in a dedicated export workspace in your Fabric tenant. The pipeline runs a query against the hashed views and writes the result as Parquet files to a storage container you own.
2. Copies those files from your container to Google Cloud Storage with Google's Storage Transfer Service.
3. Loads them into BigQuery.

Everything Churney does in your tenant is done as one Entra ID application that you register. The pipeline itself runs as your export workspace's own identity. Files in your container expire automatically after 1 day.

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, which also creates the export pipeline in your export workspace.

Prerequisites

Before you start, make sure that:

• Your data is queryable with T-SQL, in a Fabric Warehouse or in a Lakehouse through its SQL analytics endpoint. Both run on the same engine and both can host the hashed views.
• If your data lives in a Lakehouse, create a Warehouse in the same workspace; it can query the Lakehouse tables directly.
• The workspace is on a Fabric capacity (F SKU), not a trial or Power BI Premium capacity. Workspace identities need an F SKU to access storage.
• The capacity is running when the sync runs. Churney never starts or stops your capacity. If you pause it on a schedule, tell us so we can align the sync window; a sync that hits a paused capacity fails and is retried at the next scheduled run.
• Your Fabric tenant allows service principals to use Fabric APIs. A Fabric administrator enables this under Admin portal → Tenant settings → Developer settings → "Service principals can use Fabric APIs". Scope it to a security group; you add the Churney application to that group in the "Register the Churney application" step.
• You can create an Entra ID app registration, a storage account and role assignments in your Azure subscription, or have someone who can.
• You have the Azure CLI installed and are logged in (az login). The commands below use it; everything can also be done in the Azure portal and the Fabric portal.

Throughout the guide, replace the values in angle brackets. Fabric REST API calls use a token from your own login:

TOKEN=$(az account get-access-token --resource https://api.fabric.microsoft.com --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 Fabric capacity.

Generating hashed views

If you would like Churney's help with the hashed views, connect to your Warehouse and run the following query, where <your_schema_name> is the schema of the tables you wish to share with Churney:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<your_schema_name>';

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.

Normalization Patterns

The specific contact information columns normalization patterns are as follows:

• email (Meta) — lowercase, leading and trailing spaces removed → john.doe+promo@gmail.com
• google_email (Google) — same, and for gmail.com / googlemail.com also remove every period and any + suffix before the @ → johndoe@gmail.com
• phone (Meta) — digits only, country code kept, leading zeros removed, no + → 442071838750
• google_phone (Google) — the same digits with a + prefix → +442071838750

Also, please be aware of the following requirements:

Phone numbers must include a country code. Meta and Google both match on the country code, and Google rejects a number without one instead of repairing it. Always store the country code, even when every customer is in one country. Churney cannot add it later, because a SHA-256 hash cannot be reversed.

Drop the national trunk prefix. A UK number written 020 7183 8750 is +44 20 7183 8750, not +44 020 7183 8750. The SQL below removes leading zeros and the 00 international prefix, but it cannot find a trunk zero in the middle of a number.

Do not hash an empty value. SHA256('') is a valid 64-character hash that every customer without a phone number would share. Churney rejects it. The SQL below returns NULL instead. Do the same for placeholder strings such as none or null.

Hashes must be lowercase hex, 64 characters, with no 0x prefix.

Two columns per identifier. Meta and Google need different normalization, so share both email and google_email, and both phone and google_phone.

Example T-SQL

Create a schema churney_views in your Warehouse (or in the Lakehouse's SQL analytics endpoint) and, for each table you share, a view that hashes the personally identifiable columns with SHA-256. Fabric uses T-SQL:

CREATE SCHEMA churney_views;
GO

CREATE VIEW churney_views.users AS
WITH cleaned AS (
  SELECT
    *,
    NULLIF(LOWER(TRIM(CONVERT(VARCHAR(320), email))), '') AS email_normalized,
    NULLIF(REPLACE(TRANSLATE(CONVERT(VARCHAR(64), phone), ' ()-.+/', '       '), ' ', ''), '')
      AS phone_all_digits
  FROM raw_data.users
),
parts AS (
  SELECT
    *,
    LEFT(email_normalized, NULLIF(CHARINDEX('@', email_normalized), 0) - 1) AS email_local_part,
    SUBSTRING(email_normalized, NULLIF(CHARINDEX('@', email_normalized), 0) + 1, 320) AS email_domain,
    NULLIF(SUBSTRING(phone_all_digits,
                     PATINDEX('%[^0]%', phone_all_digits + 'x'),
                     LEN(phone_all_digits)), '') AS phone_digits
  FROM cleaned
)
SELECT
  user_id,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', email_normalized), 2)) AS email,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256',
    CASE
      WHEN email_domain IN ('gmail.com', 'googlemail.com')
           OR email_domain LIKE 'gmail.co.%'
           OR email_domain LIKE 'googlemail.co.%'
      THEN REPLACE(
             CASE WHEN CHARINDEX('+', email_local_part) > 0
                  THEN LEFT(email_local_part, CHARINDEX('+', email_local_part) - 1)
                  ELSE email_local_part END,
             '.', '') + '@' + email_domain
      ELSE email_normalized
    END), 2)) AS google_email,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', phone_digits), 2)) AS phone,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', '+' + phone_digits), 2)) AS google_phone,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256',
    NULLIF(LOWER(TRIM(CONVERT(VARCHAR(200), full_name))), '')), 2)) AS full_name,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256', CONVERT(VARCHAR(8), birthday, 112)), 2)) AS birthday,
  country_code, signup_date, created_at
FROM parts;

LOWER(CONVERT(..., 2)) renders the hash as lowercase hex without the 0x prefix, which matches the hashes Churney receives from other sources. Keep the LOWER(). Churney accepts lowercase hex only.

Each value is cast to VARCHAR before hashing. HASHBYTES hashes the raw bytes of its argument, so an NVARCHAR input hashes UTF-16 bytes and produces a different hash for the same text than every other warehouse does.

TRANSLATE removes spaces, parentheses, hyphens, periods, plus signs and slashes from the phone number. T-SQL has no regular expressions, so it cannot remove every non-digit character. If your phone column can contain letters or other symbols, clean it before the view.

Only the hashed views are shared with Churney. The raw tables stay private.

Two things to keep in mind for the views:

• Give every table with events a timestamp column (DATETIME2) that Churney can use for incremental loads, for example event_time or created_at.
• Fabric Warehouse created through the API uses a case-sensitive collation. Table and column names in the views must be referenced with the exact casing. The domain comparisons in the view, IN ('gmail.com', 'googlemail.com') and LIKE 'gmail.co.%', are case-sensitive for the same reason. They work only because LOWER() is applied to the whole address first, so do not remove it.

‍

‍

Create a storage account and container for Churney

Churney's pipeline writes Parquet files to an Azure Data Lake Storage Gen2 container. Create it in the same region as your Fabric capacity, with hierarchical namespace enabled, and let files expire after 1 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.

Create the Churney export workspace

The pipeline Churney runs lives in its own workspace, so that Churney never needs any role in the workspace that holds your data. Create the workspace on your capacity and give it a workspace identity.

CAPACITY_ID=$(curl -s -H "Authorization: Bearer $TOKEN" https://api.fabric.microsoft.com/v1/capacities \
  | jq -r '.value[] | select(.displayName=="<your capacity name>") | .id')

EXPORT_WS=$(curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  https://api.fabric.microsoft.com/v1/workspaces \
  -d "{\"displayName\":\"churney-export\",\"capacityId\":\"$CAPACITY_ID\"}" | jq -r .id)

curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Length: 0" \
  "https://api.fabric.microsoft.com/v1/workspaces/$EXPORT_WS/provisionIdentity"

The last call is asynchronous. After about ten seconds, check the result in the Fabric portal under the export workspace → Workspace settings → Workspace identity. It shows the identity's service principal ID; keep it as <identity-sp-id>. If the state shows Failed the first time an identity is created in a tenant, delete it, wait five minutes and create it again.

Grant the workspace identity write access to the container:

az role assignment create --assignee-object-id <identity-sp-id> --assignee-principal-type ServicePrincipal \  --role "Storage Blob Data Contributor" \  --scope "/subscriptions/<subscription>/resourceGroups/churney-export/providers/Microsoft.Storage/storageAccounts/<storageaccount>/blobServices/default/containers/churney-unload"

Grant the export workspace read access to the hashed views

The export workspace's identity needs to read churney_views, and nothing else. Access is opt-in: share the Warehouse with the identity without data access, then grant the one schema in SQL.

1. In the Fabric portal, open the workspace that holds your Warehouse, select the Warehouse → Share. Add churney-export (the workspace identity has the same name as the export workspace). Leave every checkbox unticked, in particular "Read all data using SQL", and select Grant. This lets the identity connect to the Warehouse but see no tables.
2. Connect to the Warehouse and grant the hashed-view schema:‍


GRANT SELECT ON SCHEMA::churney_views TO [churney-export];

Do not give the identity a workspace role; Viewer would grant read access to every table in the workspace. Sharing an item is a portal action; Fabric's REST API for item role assignments is not yet enabled for warehouses.

Create the connections for the Churney pipeline

The pipeline Churney runs uses two connections, both authenticated with the workspace identity: one to your Warehouse's SQL endpoint, one to the container. The warehouse connection uses Fabric's generic SQL connection type over the Warehouse's TDS endpoint, not the Fabric-specific Warehouse connector: only the generic type supports workspace identity as the credential, the Warehouse connector supports signed-in users only. Find the SQL endpoint under your Warehouse (or SQL analytics endpoint) → Settings → SQL connection string; it looks like <guid>.datawarehouse.fabric.microsoft.com.

SQL_CONN=$(curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  https://api.fabric.microsoft.com/v1/connections -d '{
  "connectivityType":"ShareableCloud","displayName":"churney-warehouse",
  "connectionDetails":{"type":"SQL","creationMethod":"SQL","parameters":[
    {"dataType":"Text","name":"server","value":"<sql-endpoint>"},
    {"dataType":"Text","name":"database","value":"<warehouse-name>"}]},
  "privacyLevel":"Organizational",
  "credentialDetails":{"singleSignOnType":"None","connectionEncryption":"Encrypted","skipTestConnection":false,
    "credentials":{"credentialType":"WorkspaceIdentity"}}}' | jq -r .id)

ADLS_CONN=$(curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  https://api.fabric.microsoft.com/v1/connections -d '{
  "connectivityType":"ShareableCloud","displayName":"churney-unload",
  "connectionDetails":{"type":"AzureDataLakeStorage","creationMethod":"AzureDataLakeStorage","parameters":[
    {"dataType":"Text","name":"server","value":"https://<storageaccount>.dfs.core.windows.net"},
    {"dataType":"Text","name":"path","value":"churney-unload"}]},
  "privacyLevel":"Organizational",
  "credentialDetails":{"singleSignOnType":"None","connectionEncryption":"Encrypted","skipTestConnection":false,
    "credentials":{"credentialType":"WorkspaceIdentity"}}}' | jq -r .id)

Print the two connection ids; you share them with Churney:

echo "$SQL_CONN $ADLS_CONN"

Churney creates the pipeline itself, named churney_unload, in the export workspace using these two connections, and keeps its definition up to date on every sync. It is a single Copy activity with two parameters, query and sink_path; it has no schedule and no query of its own. You can open it in the Fabric portal to inspect it. Manual edits to it are overwritten by the next sync.

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 sync itself 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: runs the export pipeline and reads table metadata"}'

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"}'

Grant the application the permissions it needs:

# Use the two connections in the pipeline definition
for CONN in $SQL_CONN $ADLS_CONN; do
  curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
    "https://api.fabric.microsoft.com/v1/connections/$CONN/roleAssignments" \
    -d "{\"principal\":{\"id\":\"$SP_ID\",\"type\":\"ServicePrincipal\"},\"role\":\"User\"}"
done

# Create, update and run the pipeline in the export workspace
curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  "https://api.fabric.microsoft.com/v1/workspaces/$EXPORT_WS/roleAssignments" \
  -d "{\"principal\":{\"id\":\"$SP_ID\",\"type\":\"ServicePrincipal\"},\"role\":\"Contributor\"}"

# Read the exported files
az role assignment create --assignee-object-id $SP_ID --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Data Reader" \
  --scope "/subscriptions/<subscription>/resourceGroups/churney-export/providers/Microsoft.Storage/storageAccounts/<storageaccount>/blobServices/default/containers/churney-unload"

# Use the two connections in the pipeline definition
for CONN in $SQL_CONN $ADLS_CONN; do  curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \    "https://api.fabric.microsoft.com/v1/connections/$CONN/roleAssignments" \    -d "{\"principal\":{\"id\":\"$SP_ID\",\"type\":\"ServicePrincipal\"},\"role\":\"User\"}"done

The application also needs to read column metadata (names and types) from the hashed views, but not the rows. Share the Warehouse with churney-sync without data access (Warehouse → Share, no checkboxes ticked), then grant metadata visibility only:

GRANT VIEW DEFINITION ON SCHEMA::churney_views TO [churney-sync];

With this, the Churney application can list the views and their columns but cannot SELECT from them; rows are only ever read by the pipeline, as your workspace identity.

‍

Finally, add the application to the security group that the tenant setting "Service principals can use Fabric APIs" is scoped to.

What to share with Churney

When the steps above are done, send Churney:

1. Azure region of your Fabric capacity | Azure portal → the Fabric capacity resource → Overview → Location
2. Tenant ID | az account show --query tenantId -o tsv, or Azure portal → Microsoft Entra ID → Overview → Tenant ID
3. Application (client) ID of churney-sync | echo $APP_ID, or Azure portal → Microsoft Entra ID → App registrations → churney-sync → Application (client) ID
4. Export workspace ID | echo $EXPORT_WS, or open the churney-export workspace in the Fabric portal; the GUID after /groups/ in the URL
5. Warehouse connection ID and ADLS connection ID | echo "$SQL_CONN $ADLS_CONN", or Fabric portal → Settings (gear) → Manage connections and gateways → Connections → open each → ID in the URL
6. SQL endpoint host and Warehouse (or Lakehouse) name | Warehouse or SQL analytics endpoint → Settings → SQL connection string
7. Storage account name and container name | <storageaccount> as you named it, and churney-unload; Azure portal → Storage accounts → the account → Containers

Churney fills these into your sync configuration and runs the first sync. That run creates the churney_unload pipeline in your export workspace; 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 at any point: no client secret, no storage key, no SAS token. Access can be revoked by deleting the churney-sync app registration or the export workspace.

‍

Authentication

Every connection uses Entra ID and short-lived tokens:

• Churney → Fabric. 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. This is Entra ID's workload identity federation; the token is valid for about an hour and is never stored. The same token is used to start the pipeline and to read column metadata from the hashed views. The application has no SELECT permission, so it cannot read rows.
• Pipeline → Warehouse and container. The pipeline's two connections authenticate as the export workspace's identity, which Fabric 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 by its own pipeline run and processed independently, so if one table fails, the others still complete and the specific failures are reported clearly.

Each run writes to a fresh folder in the container, named after the run, and the lifecycle rule deletes it after 1 day. Pipeline runs consume capacity units on the capacity the export workspace is assigned to.

‍