IntegrationsData WarehousesAzure Synapse

Azure Synapse Analytics

Datafly Signal delivers events to Azure Synapse Analytics for enterprise-scale data warehousing, big data analytics, and unified data integration.

Prerequisites

Before configuring Azure Synapse Analytics in Signal, you need an Azure account with a Synapse workspace, a dedicated SQL pool, and a target table.

Create an Azure Account

If you don’t already have one, sign up at azure.microsoft.com.

Create a Synapse Workspace

  1. In the Azure portal, search for Azure Synapse Analytics and click Create.
  2. Select your Subscription and Resource group.
  3. Enter a Workspace name (e.g. datafly-synapse).
  4. Select or create a Data Lake Storage Gen2 account for the workspace.
  5. Set the SQL administrator login and Password (for the built-in serverless SQL pool).
  6. Click Review + Create > Create.

Create a Dedicated SQL Pool

  1. Open your Synapse workspace in the Azure portal.
  2. Go to SQL pools > + New.
  3. Enter a Dedicated SQL pool name (e.g. datafly_pool).
  4. Choose a Performance level (start with DW100c for development, scale up for production).
  5. Click Review + Create > Create.

Dedicated SQL pools are billed while running. Pause the pool when not in use to save costs. Serverless SQL pools are billed per query and do not need to be paused.

Create a Table

Connect to the dedicated SQL pool using Synapse Studio, Azure Data Studio, or SSMS:

CREATE TABLE dbo.events (
  event_id NVARCHAR(64) NOT NULL,
  type NVARCHAR(20),
  event NVARCHAR(256),
  anonymous_id NVARCHAR(64),
  user_id NVARCHAR(256),
  timestamp DATETIME2,
  received_at DATETIME2,
  sent_at DATETIME2,
  context NVARCHAR(MAX),
  properties NVARCHAR(MAX),
  traits NVARCHAR(MAX),
  source_id NVARCHAR(64),
  integration_id NVARCHAR(64)
)
WITH (
  DISTRIBUTION = HASH(anonymous_id),
  CLUSTERED COLUMNSTORE INDEX
);

Hash distribution on anonymous_id optimises queries that filter or join by user. Clustered columnstore index provides excellent compression and analytics query performance.

Get the Connection String

  1. In the Synapse workspace overview, find the Dedicated SQL endpoint (e.g. datafly-synapse.sql.azuresynapse.net).
  2. The connection string format is: Server=tcp:datafly-synapse.sql.azuresynapse.net,1433;Database=datafly_pool;User ID=your_user;Password=your_password;Encrypt=True;.

Configure Firewall Rules

  1. In the Synapse workspace, go to Security > Firewalls.
  2. Add the IP addresses of your Signal deployment.
  3. Click Save.

Configuration

FieldTypeRequiredDescription
workspace_namestringYesThe Azure Synapse workspace name.
sql_pool_namestringYesThe dedicated SQL pool name.
databasestringYesThe target database name (typically the same as the SQL pool name).
schema_namestringYesThe schema to write to. Defaults to dbo.
table_namestringYesThe target table name to insert rows into.
connection_stringsecretYesThe connection string for the Synapse dedicated SQL endpoint.

Signal Setup

Quick Setup

  1. Navigate to Integrations in the sidebar.
  2. Open the Integration Library tab.
  3. Find Azure Synapse Analytics or filter by Cloud Storage.
  4. Click Install, select a variant if available, and fill in the required fields.
  5. Click Install Integration to create the integration with a ready-to-use default blueprint.

API Setup

curl -X POST http://localhost:8084/v1/admin/integration-catalog/azure_synapse/install \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Azure Synapse Analytics",
    "variant": "default",
    "config": {
      "workspace_name": "datafly-synapse",
      "sql_pool_name": "datafly_pool",
      "database": "datafly_pool",
      "schema_name": "dbo",
      "table_name": "events",
      "connection_string": "Server=tcp:datafly-synapse.sql.azuresynapse.net,1433;Database=datafly_pool;User ID=sqladmin;Password=YOUR_PASSWORD;Encrypt=True;"
    },
    "delivery_mode": "server_side"
  }'

Testing

  1. Enable the integration in Signal and trigger a test event on your website.
  2. Open Synapse Studio and navigate to the dedicated SQL pool.
  3. Run a query to check for events:
SELECT TOP 10 * FROM dbo.events ORDER BY timestamp DESC;
  1. Verify that event rows are appearing with correct data.
  2. In Signal, check the Live Events view to confirm delivery status shows as successful.

Troubleshooting

ProblemSolution
Events not appearing in the tableVerify the connection string, database, schema, and table name are correct. Ensure the dedicated SQL pool is running (not paused).
Connection refused / timeoutCheck the Synapse firewall rules. Add Signal’s IP addresses.
Login failedVerify the username and password in the connection string.
INSERT permission deniedThe user lacks INSERT permission. Grant it with GRANT INSERT ON dbo.events TO your_user;.
SQL pool is pausedDedicated SQL pools must be running to accept connections. Resume the pool in the Azure portal.
Invalid object nameThe table does not exist or the schema is incorrect. Verify with SELECT * FROM INFORMATION_SCHEMA.TABLES;.
Performance issuesCheck the SQL pool performance level (DWU). Scale up if insert throughput is insufficient.

Visit Azure Synapse Analytics documentation for full T-SQL reference, distribution strategies, and performance tuning guides.

Azure Data ExplorerDatabricks