Overview
Azure Synapse Analytics is an analytics service that brings together enterprise data warehousing and big data analytics. Learn more about Azure Synapse Analytics. Etlworks includes several pre-built Flows optimized for Synapse Analytics.
Microsoft Fabric Warehouse is a cloud-native, fully-managed data warehousing service integrated within Microsoft Fabric. It combines the capabilities of traditional SQL data warehousing with advanced analytics. Learn more about Microsoft Fabric Warehouse.
Microsoft Fabric Warehouse is compatible with Azure Synapse
Etlworks connections and flows optimized for Azure Synapse Analytics are fully compatible with Microsoft Fabric Warehouse, although some configuration steps differ slightly.
Flows optimized for Synapse Analytics and Microsoft Fabric Warehouse
| Flow type | When to use |
|
Any to Synapse Analytics or Fabric Warehouse:
|
When you need to extract data from any source, transform it and load it into Synapse Analytics or Microsoft Fabric Warehouse. |
| Bulk load files in Azure Blob into Synapse Analytics or Fabric Warehouse | When you need to bulk-load files that already exist in Azure Blob without applying any transformations. The flow automatically generates the COPY command and MERGEs data into the destination. |
| Stream CDC events into Synapse Analytics or Fabric Warehouse | When you need to stream updates from the database which supports Change Data Capture (CDC) into Synapse Analytics or Microsoft Fabric Warehouse in real-time. |
| Stream messages from a queue into Synapse Analytics or Fabric Warehouse | When you need to stream messages from the message queue which supports streaming into Synapse Analytics or Microsoft Fabric Warehouse in real time. |
Videos
|
ETL and CDC data into Synapse Analytics Watch how to create flows to ETL and CDC data into Synapse Analytics |
Related resources
|
ELT with Azure Synapse Analytics Etlworks supports executing complex ELT scripts directly in Azure Synapse Analytics or Microsoft Fabric Warehouse which greatly improves the performance and reliability of the data ingestion. |
Reverse ETL with Azure Synapse Analytics as a relational database You can use any |
|
Connect to Azure Synapse Analytics To create a Connection to an Azure Synapse Analytics database, open the |
Load multiple tables by a wildcard name You can ETL data from multiple database objects (tables and views) into Synapse Analytics or Microsoft Fabric Warehouse by a wildcard name without creating individual source-to-destination transformations. |
|
Setup incremental replication using High Watermark (HWM) Using HWM replication you can load only new and updated records into Synapse Analytics or Microsoft Fabric Warehouse.
|
|
Related case studies
|
Real-time CDC and batch ETL data from MySQL, Postgres, Oracle, APIs, and file-based sources. |
Tallink, a leading provider of passenger and cargo transport services in the Baltic Sea region, needed a solution to integrate data from various sources into multiple destinations, including MySQL, Postgres, Oracle databases, files, and APIs. With Etlworks, they achieved real-time Change Data Capture (CDC) and robust data recovery through batch ETL, enabling reliable, high-performance data flows. |
|
Cloud migrations from databases and file-based sources to Azure.
|
BioUrja Group, a global leader in energy and agricultural commodities, turned to Etlworks for their cloud migration needs. By integrating data from multiple databases and file sources into cloud-based destinations, Etlworks enabled BioUrja to migrate billions of records from proof of concept (PoC) to production in just a few weeks. With unmatched support and flexibility, Etlworks delivered the perfect mix of features and value to meet BioUrja’s tight timeline and budget constraints. |
Connect to Azure Synapse Analytics
Here are the steps to connect to Azure Synapse Analytics:
Step 1. Open the Connections window and click +.
Step 2. Type synapse into the search field.
Step 3. Select Azure Synapse Analytics Connection and continue by defining the Connection parameters.
Step 4. Enter SQL endpoint and SQL pool. Keep URL unchanged.
Step 5. Configure authentication using either Default authentication with user name and password
or Active Directory (4 options):
Read more about Active directory authentication.
Step 6. Test connection.
Connect to Microsoft Fabric Warehouse
Configure Microsoft Fabric Warehouse
Step 1: Create a Microsoft Fabric Workspace
Skip this steps if you already have a Workspace associated with Microsoft Fabric.
1. Sign in to Microsoft Fabric.
2. Select "Workspaces" on the left navigation pane.
3. Click "New workspace."
4. Provide a workspace name and description, set permissions as needed, and click "Save."
Step 2: Assign Workspace to Fabric Capacity
Skip this steps if workspace is already assigned to Fabric Capacity.
1. In your workspace, click "Workspace settings."
2. Under the "Premium" section, select "Assign to capacity."
3. Select your Fabric capacity or start a free trial if necessary by clicking "Start trial" and following the prompts.
4. Save the changes.
Step 3: Create a Microsoft Fabric Warehouse
Skip this steps if you already have Microsoft Fabric Warehouse.
1. Inside your workspace, select "New" and then choose "Data warehouse."
2. Enter a warehouse name and click "Create."
Step 4: Retrieve the Synapse-Compatible Connection String
1. Once your warehouse is created, select "Warehouse" from the workspace.
2. Click on the "SQL Endpoint" to view and copy your connection string.
3. Your connection string will look similar to:
your-endpoint.datawarehouse.fabric.microsoft.com
Step 5: Create connection for Microsoft Fabric Warehouse
Etlworks version 7.8.5 or newer
1. Create connection for Microsoft Fabric Warehouse:
2. Enter "SQL Endpoint" from step 4 into field SQL endpoint.
3. Enter warehouse name from step 3 into field Warehouse.
4. Keep URL unchanged.
5. Authenticate with Azure and Test connection.
Etlworks version older than 7.8.5
1. Create connection for Synapse Analytics:
2. Enter "SQL Endpoint" from step 4 into field SQL endpoint.
3. Enter warehouse name from step 3 into field SQL pool.
4. Keep URL unchanged.
5. Configure DATETIME2 to be compatible with Microsoft Fabric Warehouse
Etlworks Synapse connector creates timestamp and date columns as DATETIME2. In Microsoft Fabric DATETIME2 columns require explicitly defined fractional second precision between 0 and 6:
-
Use DATETIME2(0) for second-level precision (no fractions).
-
Use DATETIME2(3) for millisecond precision (most common).
-
Use DATETIME2(6) for microsecond precision (maximum precision).
To make connection compatible with Microsoft Fabricadd the following code to Override Create and Alter Table SQL:
value = com.toolsverse.util.Utils.findAndReplace(sql, 'DATETIME2 ', 'DATETIME2(3) ', true);
6. Authenticate with Azure and Test connection
- Select Active directory interactive in Authentication type. Do not enter user name and password
- Click OAuth Token and proceed to authenticating with Azure.
- Click Test connection to verify that just created connection can connect to Microsoft Fabric Warehouse.
Step 6: Create an Azure Data Lake Gen2 Storage Account
Etlworks flows optimized for Synapse and Microsoft Fabric Warehouse stage data in Azure Data Lake Gen2 Storage, then load stages files into warehouse using COPY command.
1. Go to the Azure portal.
2. Click "Create a resource" and select "Storage Account."
3. Choose your subscription and resource group, enter your storage account name, and select your region.
4. IMPORTANT: Under "Advanced," enable "Hierarchical namespace" for Data Lake Storage Gen2.
5. Click "Review + create," then "Create."
Step 7: Assign Required Roles to Storage Account
Unlike Synapse Microsoft Fabric Warehouse requires special permissions and linking storage account to Microsoft Fabric Workspace.
1. Open your newly created storage account.
2. Go to "Access Control (IAM)" and click "Add" > "Add role assignment."
3. Assign the "Owner" role to the user connecting the workspace.
4. Save the changes.
Step 8: Link Storage Account to Power BI
1. In Power BI, navigate to the same workspace.
2. Click "Workspace settings" and then select "Azure connections."
3. Choose the same Data Lake Storage Gen2 account and confirm the connection.
Step 9: Link Storage Account to Fabric Workspace
1. In Fabric, navigate to the same workspace.
2. Click "Workspace settings" and then select "Azure connections."
3. Choose the same Data Lake Storage Gen2 account and confirm the connection.
Comments
0 comments
Please sign in to leave a comment.