Pull from Microsoft Fabric¶

Microsoft Fabric is a cloud-based analytics platform that brings data storage, data engineering, and business intelligence together in a single workspace. Data is held in items such as a Warehouse or a Lakehouse, each of which publishes a SQL analytics endpoint that supports both tables and views.

Microsoft Fabric can send tables and views to Amperity from a Warehouse or a Lakehouse SQL analytics endpoint. Name each table or view to pull, and then Amperity reads each one and lands it as a CSV file.

Each run reads every table and view that you configure, in full.

Beta

The Microsoft Fabric source connector is currently in beta. Contact your Amperity representative to learn more.

Amperity can read both tables and views. A view in Microsoft Fabric is a saved query that is evaluated when it is read, so what Amperity lands is the output of the view rather than the contents of the tables beneath it.

Important

If your workspace restricts inbound access, Amperity must be allowed to reach it.

IP firewall rules. If your workspace allows connections only from selected networks, add the Amperity IP address for allowlists for your region to the allowlist.
Private links. Amperity connects over the public internet. A workspace or tenant that blocks public internet access, such as one using Azure Private Link with Block Public Internet Access, cannot be reached by this connector.

Note

A Warehouse and a Lakehouse SQL analytics endpoint are configured the same way. A workspace serves both from one server address, and the two are told apart only by the name that you enter in Database. You do not need to know which kind of item you have.

The steps that are required to pull tables and views to Amperity from Microsoft Fabric:

Configure Microsoft Entra access
Get details
Add courier
Get sample files
Add feeds
Add load operations
Run courier
Add to courier group

Configure Microsoft Entra access¶

Microsoft Fabric does not accept a user name and password. The SQL analytics endpoint supports Microsoft Entra authentication only. Amperity connects as a Microsoft Entra service principal, which must be created and authorized in your Microsoft tenant before a credential can connect.

These steps are usually owned by three different groups. Identify who performs each one before you start, because a step that is discovered midway through an implementation can take days to schedule.

Step	Who performs it
Register an application in the Microsoft Entra admin center . This produces the Client ID.	Your identity or IT team. Registering an application is often restricted to that team.
Create a client secret on that application. This is the Client Secret.	Your identity or IT team. The value is shown once, when it is created, and is masked afterward. Record it at that point.
Enable the tenant setting Service principals can use Fabric APIs, under Admin portal > Tenant settings > Developer settings.	A Fabric administrator. No one else can change a tenant setting.
Grant the service principal access to the workspace that holds the data.	The team that owns the workspace, which is usually the team requesting the integration. Any workspace role is sufficient to connect, including Viewer.

Microsoft requires the tenant setting in the third step before a service principal may use a SQL connection string at all. There is no way to work around it, and a service principal that is otherwise configured correctly cannot connect until a Fabric administrator enables it.

Important

Client secrets expire. Microsoft sets an expiration when the secret is created, and the maximum is 24 months. Data stops arriving when a secret expires, and the only sign of it is a failed courier run.

Whoever owns the application registration must rotate the secret, and that is usually not the person who notices that data has stopped arriving. Record the expiration date when the secret is created, and decide then who renews it.

Get details¶

Microsoft Fabric requires the following configuration details:

The Server for the workspace.

The Server and Database values come from the Microsoft Fabric item that you are reading. The Client ID and Client Secret identify a Microsoft Entra service principal, which is usually created by your identity or IT team rather than by the team that requests the integration.

A Fabric administrator must enable service principal access for your tenant, and the service principal must be granted a role on the workspace. Neither is part of creating the credential, and a credential that is correct in every other way cannot connect until both are done.

Microsoft Fabric shows this value as the SQL connection string, on the Warehouse or on the settings for the Lakehouse SQL analytics endpoint. It is a host name that ends in datawarehouse.fabric.microsoft.com.

Important

Despite that label, this value is a host name and not a full connection string. A value that includes Server=tcp: or a port number does not work. Enter only the host name.
The Database to read from. This is the name of the Warehouse or the Lakehouse SQL analytics endpoint. Names that contain spaces are supported.
The Client ID and Client Secret for the Microsoft Entra service principal, from Configure Microsoft Entra access.
The Tables to read. Enter each table or view using its fully qualified name, including the schema. For example: dbo.vw_active_members. Enter at least one.

Tip

Use SnapPass to securely share configuration details for Microsoft Fabric between your company and your Amperity representative.

Add courier¶

A courier brings data from an external system to Amperity.

To add a courier

From the Sources page, click Add Courier. The Add Courier page opens.
Find, and then click the icon for Microsoft Fabric. The Add Courier page opens.
Enter the name of the courier. For example: “Microsoft Fabric”.

From the Credential field, select an existing credential or select Create a new credential.

To add a credential, enter the name of the credential, a description, and the Microsoft Fabric Server, Database, Client ID, and Client Secret. Click Save.

When finished click Continue.
Under Tables, enter the fully qualified name of each table or view to read. For example: dbo.vw_active_members.
Click Save.

Note

Every courier run begins by reading one row from each table and view that you named. This verifies that each object exists and can be read, rather than verifying only that the service principal can sign in. A misspelled object, or one that the service principal cannot read, fails the run at the start instead of partway through.

Get sample files¶

Every Microsoft Fabric file that is pulled to Amperity must be configured as a feed. Before you can configure each feed you need to know the schema of that file. Run the courier without load operations to bring sample files from Microsoft Fabric to Amperity, and then use each of those files to configure a feed.

Amperity names each file after the table or view that it was read from, with .csv appended. For example, a view named dbo.vw_active_members lands as dbo.vw_active_members.csv. The first row of each file is a header row.

To get sample files

From the Sources tab, open the menu for a courier configured for Microsoft Fabric with empty load operations, and then select Run. The Run Courier dialog box opens.
Select Load all data. This is the only load option that Microsoft Fabric supports.
Click Run.

Important

The courier run fails, but this process will successfully return a list of files from Microsoft Fabric.

These files will be available for selection as an existing source from the Add Feed dialog box.

Wait for the notification for this courier run to return an error similar to:

Error running load-operations task
Cannot find required feeds: "df-xxxxxx"

Add feeds¶

A feed defines how to load data into a domain table, including specifying required columns and columns with semantic tags for customer profile (PII) or transactions data.

Note

A feed must be added for each file that is pulled from Microsoft Fabric, including all files that contain customer records and interaction records, along with any other files that is used to support downstream workflows.

To add a feed

From the Sources tab, click Add Feed. This opens the Add Feed dialog box.
Under Data Source, select Create new source, and then enter “Microsoft Fabric”.
Enter the name of the feed in Feed Name. For example: “Active members”.

Tip

The name of the domain table will be “<data-source-name>:<feed-name>”. For example: “Microsoft Fabric:Active members”.
Under Sample File, select Select existing file, and then choose from the list of files. For example: “dbo.vw_active_members.csv”.

Tip

The list of files that is available from this dropdown menu is sorted from newest to oldest.
Select Load sample file on feed activation.
Click Continue. This opens the Feed Editor page.
Select the primary key.
Apply semantic tags to customer records and interaction records, as appropriate.
Under Last updated field, specify which field best describes when records in the table were last updated.

Tip

Choose Generate an “updated” field to have Amperity generate this field. This is the recommended option unless there is a field already in the table that reliably provides this data.
For feeds with customer records (PII data), select Make available to Stitch.
Click Activate. Wait for the feed to finish loading data to the domain table, and then review the sample data for that domain table from the Data Explorer.

Add load operations¶

After the feeds are activated and domain tables are available, add the load operations to the courier used for Microsoft Fabric.

Example load operations

Load operations must specify each file that is pulled to Amperity from Microsoft Fabric.

Refer to each file by the name of the table or view, without the .csv extension.

Because Microsoft Fabric is read in full on every run, pair each load with a truncate operation. The truncate empties the domain table so that each run replaces its contents. Without it, every run appends another full copy of the table.

For example:

{
  "df-A1B2C3": [
    {
      "type": "truncate"
    },
    {
      "type": "load",
      "file": "dbo.vw_active_members"
    }
  ],
  "df-D4E5F6": [
    {
      "type": "truncate"
    },
    {
      "type": "load",
      "file": "dbo.transactions"
    }
  ]
}

To add load operations

From the Sources tab, open the menu for the courier that was configured for Microsoft Fabric, and then select Edit. The Edit Courier dialog box opens.
Edit the load operations for each of the feeds that were configured for Microsoft Fabric so they have the correct feed ID.
Click Save.

Run courier manually¶

Run the courier again. This time, because the load operations are present and the feeds are configured, the courier will pull data from Microsoft Fabric.

To run the courier manually

From the Sources tab, open the menu for the courier with updated load operations that is configured for Microsoft Fabric, and then select Run. The Run Courier dialog box opens.
Select Load all data, the only load option that Microsoft Fabric supports. Actual data will be loaded to a domain table because the feed is configured.
Click Run.

This time the notification will return a message similar to:
```
Completed in 5 minutes 12 seconds
```

Add to courier group¶

A courier group is a list of one or more couriers that run as a group. A courier group can act as a constraint on downstream workflows and can run automatically as part of a scheduled workflow.

To add the courier to a courier group

From the Sources tab, click Add Courier Group. This opens the Create Courier Group dialog box.
Enter the name of the courier. For example: “Microsoft Fabric”.
Add a cron string to the Schedule field to define a schedule for the orchestration group.

A schedule defines the frequency at which a courier group runs. All couriers in the same courier group run as a unit and all tasks must complete before a downstream process starts. Define a schedule using cron.

Cron syntax specifies the fixed time, date, or interval at which cron runs. Each line represents a job. 30 8 * * * represents “run at 8:30 AM every day” and 30 8 * * 0 represents “run at 8:30 AM every Sunday”.

For example:
```
┌───────── minute (0 - 59)
│ ┌─────────── hour (0 - 23)
│ │ ┌───────────── day of the month (1 - 31)
│ │ │ ┌────────────── month (1 - 12)
│ │ │ │ ┌─────────────── day of the week (0 - 6) (Sunday to Saturday)
│ │ │ │ │
│ │ │ │ │
│ │ │ │ │
* * * * * command to execute
```
Amperity validates the cron syntax and shows you the results. You may also use crontab guru to validate cron syntax.
Set Status to Enabled.
Specify a time zone.

A courier group schedule is associated with a time zone. The time zone determines the point at which a courier group’s scheduled start time begins. A time zone should be aligned with the time zone of system from which the data is being pulled.

Use the Use this time zone for file date ranges checkbox to use the selected time zone to look for files. If unchecked, the courier group uses the current time in UTC to look for files to pick up.

Note

The time zone that is chosen for an courier group schedule should consider every downstream business processes that requires the data and also the time zones in which the consumers of that data will operate.
Add at least one courier to the courier group. Select the name of the courier from the Courier dropdown. Click + Add Courier to add more couriers.
Click Add a courier group constraint, and then select a courier group from the dropdown list.

A wait time is a constraint placed on a courier group that defines an extended time window for data to be made available at the source location.

Important

A wait time is not required for a bridge.

A courier group typically runs on an automated schedule that expects customer data to be available at the source location within a defined time window. However, in some cases, the customer data may be delayed and is not made available within that time window.
For each courier group constraint, apply any offsets.

A courier can be configured to look for files within range of time that is older than the scheduled time. The scheduled time is in Coordinated Universal Time (UTC), unless the “Use this time zone for file date ranges” checkbox is enabled for the courier group.

This range is typically 24 hours, but may be configured for longer ranges. For example, it is possible for a data file to be generated with a correct file name and datestamp appended to it, but for that datestamp to represent the previous day because of how an upstream workflow is configured. A wait time helps ensure that the data at the source location is recognized correctly by the courier.

Warning

This range of time may affect couriers in a courier group whether or not they run on a schedule. A manually run courier group may not take its schedule into consideration when determining the date range. Only the provided input days to load data from are used as inputs.
Click Save.

How data is pulled¶

Every run reads everything. Each run reads every table and view that you configure, in full. There is no incremental or date-windowed pull, and there is no setting that requests one.

Because each object is read in full, a record that was deleted in Microsoft Fabric can stop appearing in Amperity after the next run. This requires load operations that truncate the domain table before loading, as described in Add load operations. A load operation without a truncate appends each run to the previous one, and a delete is never reflected.

Important

Reading a table or a view is a query, and each run consumes the Microsoft Fabric capacity that is assigned to your workspace. Take this into account when you set the schedule for the courier group, especially for large tables.

A view is evaluated when it is read. Filters and expressions in the view definition are applied by Microsoft Fabric before any data reaches Amperity. What Amperity lands is the output of the view, not the contents of the tables beneath it. A view that filters on a status column and lowercases an email address returns only the matching rows, already lowercased.

A SQL NULL lands as an empty value. CSV cannot distinguish an empty value from an empty string, so the difference between the two is lost when the data is read. If that distinction matters to a downstream workflow, preserve it in the view definition. For example, return a specific value in place of NULL.

Every column is read as text. Amperity converts each column to a string as it writes the CSV file, so dates, timestamps, decimals, and other typed columns land in whatever text form the SQL driver produces for them. Review a sample file before you configure the feed, and cast a column in the view definition when a downstream workflow requires a specific format.

Troubleshoot errors¶

The following errors may occur when a courier runs.

Error	Resolution
Microsoft Entra rejected the service principal.	The client ID or client secret is incorrect, or the client secret has expired. Confirm both values with whoever owns the application registration, and check the expiration date on the secret.
The credential could not connect to the configured database.	The database name does not match a Warehouse or Lakehouse SQL analytics endpoint in the workspace, or the service principal has not been granted access to the workspace. Microsoft Fabric reports both of these the same way, so check both.
Invalid object name `'dbo.vw_active_members'`.	A configured table or view does not exist. The name is misspelled, is missing its schema, or names an object that the service principal cannot read. Enter the fully qualified name, such as `dbo.vw_active_members`.
A message from Microsoft Fabric about a system update, a shutdown in progress, or a workspace that is temporarily unavailable.	Microsoft Fabric interrupted the connection for maintenance or an internal operation. Amperity retries these automatically.
Reading a table or view stopped making progress.	The read was ended after ten minutes without progress, rather than being left running. The error names the table or view and the number of rows that had been read. Amperity retries these automatically. Contact your Amperity representative if the error recurs.
Any other error reported by Microsoft Fabric.	An error that Amperity does not recognize is retried automatically. Contact your Amperity representative if the error recurs.

Choose topic collections

Pull from Microsoft Fabric¶

Configure Microsoft Entra access¶

Get details¶

Add courier¶

Get sample files¶

Add feeds¶

Add load operations¶

Run courier manually¶

Add to courier group¶

How data is pulled¶

Troubleshoot errors¶