How to connect Microsoft Fabric to the Supermetrics API

You can pull Supermetrics data into Microsoft Fabric directly from the Supermetrics API, using either Dataflow Gen2 or a Pipeline with a Copy activity. Both land the data in a Fabric Lakehouse as a Delta table. This path gives you control over how and when data from Supermetrics, a marketing intelligence platform, lands in Fabric, and it suits teams who already run their other ingestion jobs in Fabric and want this one to appear in the same run history and failure alerts. Both paths have been tested end-to-end in a live Fabric workspace. The connector names, error messages, and workarounds below come straight from that run.

There are two ways to do this in Fabric, and the right one comes down to how you handle repeat runs:

Two paths pull Supermetrics data into Microsoft Fabric through the API: Dataflow Gen2 (no-code) and a Pipeline with a Copy activity (orchestrated).
Dataflow Gen2 supports Append and Replace only. There's no Upsert, so a refresh window that resends recent days creates duplicate rows.
A Pipeline with a Copy activity supports Upsert, plus retries and run-level monitoring. It's the right default for anything on a schedule.
Parquet is the output format to pick. Dataflow Gen2 needs one extra Power Query step; the Copy activity needs two activities, staging the file to Files before writing to Tables.
Both paths land the data as a Delta table in a Fabric Lakehouse.

See the table for more details.

Factor	Dataflow Gen2 (Option A)	Pipeline + Copy activity (Option B)
Setup	No-code, Power Query editor	Pipeline canvas, two Copy activities
Write modes	Append, Replace	Append, Overwrite, Upsert
Overlapping refresh windows	Creates duplicates	Handled by Upsert
Parquet handling	One Power Query step	Two-hop: Binary to Files, then Parquet to Tables
Retries and conditional logic	No	Yes
Best for	One-time loads and non-overlapping time ranges	Scheduled incremental loads (most setups)

Use Option A, Dataflow Gen2, for a one-time load, a quick check, or any query whose time range never overlaps between runs. Duplicates aren't a concern in those cases, and it's the faster no-code route.
Use Option B, Pipeline with a Copy activity, if your query will run on a recurring schedule with a refresh window that resends recent days, the normal setup for most Supermetrics data. It's the only one of the two that supports Upsert, matching and updating existing rows instead of creating duplicates. It also adds retries, conditional logic, and run-level monitoring, so you have more control over how failures are handled.

Alternative approaches
Looking for a managed connection instead? If you'd rather use a fully managed connection instead of building and maintaining queries, see How to configure a Microsoft Fabric destination for the direct Supermetrics-to-Fabric connector.
Using standalone Azure Data Factory instead of Fabric? This guide covers Microsoft Fabric's built-in Data Factory experience, writing to a Fabric Lakehouse as a Delta table. If you're using a separate Azure Data Factory resource writing to Azure Storage instead, see How to use Azure Data Factory with the Supermetrics API.
How to choose: The managed connector needs no query maintenance and is the faster setup, while the API path is worth the extra work when you want the query logic and the run schedule to live inside Fabric.

Before you begin

Here’s what you need before you get started:

A Supermetrics account with API access. Learn how to create and manage your API key.
A working Supermetrics query URL. You can build one in two ways:
- Use the visual query builder in the Supermetrics Hub. This is useful if you want to preview the results before connecting Fabric to them.
- Build it programmatically, following the Making requests and Output formats reference docs.
A Microsoft Fabric workspace backed by a Fabric capacity (F SKU or trial), with permissions to create a Dataflow or a Pipeline.
A Lakehouse in your Fabric workspace to land the data in. The steps below use a schema-enabled Lakehouse.

Keep the API key safe
The query URL includes an API key that gives anyone with the URL access to your data. Treat this as you would a password and keep it safe. If you need to share the URL publicly, use the Short URL option that does not display your API key. The short URL hides the key but still returns your data, so treat it with the same care.

Build a Supermetrics API query URL

Choose Parquet as the output format for both routes below. For Dataflow Gen2 (Option A), it needs only one extra step in Power Query. For the Copy activity (Option B), it needs a two-step staging pattern, since REST and HTTP sources can't parse Parquet directly. See Option B for the full pattern.

Build the API query URL on the Supermetrics Hub

On the Supermetrics Hub, go to API queries under Manage.
Click Select data source, then choose a data source and connection to use. Any Supermetrics source works here, including Google Ads, Facebook Ads, LinkedIn Ads, TikTok Ads, and Google Analytics 4. See the data source connection guides for prerequisites for connecting.
Build your query in the sidebar. We recommend saving working queries into a custom schema and using a relative time range (for example, last 7 days) to keep incremental loads light and stable. A fixed date range means editing the URL every time you want newer data, and a very wide range makes each run slower and more likely to time out.
Click Run to test the query and review the results in the preview.
From the URL format options, select Parquet.

Copy the query URL. It will look something like this:

https://api.supermetrics.com/enterprise/v2/query/data/parquet?json={"api_key":"YOUR_API_KEY", ...}

Build the API query URL programmatically

Follow the Making requests documentation to construct your query URL, specifying your data source, accounts, fields, and time range as parameters.
Choose your output format endpoint from Output formats. Recommendation: use Parquet.

Instructions

Option A: Load the data with Dataflow Gen2 (no-code, no Upsert)

Use this if your team prefers a no-code setup and doesn't need retry or error-handling logic. Dataflow Gen2 has no Upsert option, so it only works cleanly when your query's time range never overlaps between runs.

Open your Fabric workspace and select New item → Dataflow Gen2.
Select Get data, then choose the Web API connector.
Paste your Supermetrics query URL, choose Anonymous as the authentication kind (your API key is already part of the URL), then click Next.
Select Create to load the query into the editor.
If you selected the Parquet output format, the preview won't show a table automatically. You'll see a single file object instead (for example, api.supermetrics.com, 45,107 bytes; your file size will differ). This is expected: Power Query fetches the raw bytes but doesn't yet know how to parse them. In the formula bar, wrap the existing query in Parquet.Document(...), so it reads:
```
Parquet.Document(Web.Contents("your query URL"))
```
Then press Enter.
Select Add data destination → Lakehouse, and choose New table. Dataflow Gen2 only writes to Lakehouse Tables, so your data lands as a Delta table automatically. There's no separate format to choose.
No Upsert option in Dataflow Gen2
Dataflow Gen2 only supports Append or Replace. There's no Upsert option. On a brand-new table, Replace is the only choice available. Append only appears once you point the destination at an existing table. If your Supermetrics query uses a refresh window that resends recent days, use Replace with a query that covers your full desired time range each run, or Append only if your time range never overlaps with previous runs. For deduplicated incremental loading, use Option B instead.
Select Save & run. Open your Lakehouse and check the new table under Tables. Your row count should match the row count in the Supermetrics Hub preview for the same query.
To keep this running automatically, go to your workspace's item list, and select Schedule. Check the end date carefully, it defaults to one year out, so a schedule can silently stop running after that date. You can also add an email under Failure notifications to get alerted if a scheduled run fails.

Option B: Load the data with a Pipeline and a Copy activity (supports Upsert)

Use this if your query runs on a schedule with an overlapping refresh window, since the Copy activity is the only one of the two paths that supports Upsert. It also gives you retries, conditional logic, and detailed run monitoring with Fabric's orchestration tool rather than its no-code one.

Parquet can't be parsed directly from an HTTP stream, so this requires two Copy activities: the first saves the raw file to your Lakehouse's Files area, and the second reads it as Parquet and writes it to a table.

Step 1. Configure copy activity 1 to stage the file

Open your Fabric workspace and select New item → Pipeline.
From the ribbon, select Copy data → Add copy data activity.
On the Source tab, set Connection to HTTP, and paste your full Supermetrics query URL (with the Parquet output format) directly into the URL field. Leave Authentication kind as Anonymous.
Set File format to Binary.
On the Destination tab, choose your Lakehouse, and select Files as the root folder. Give it a path and filename.

REST doesn't support Parquet
If you use the REST connection type with Parquet, you'll get a doesn't support random read error. Microsoft's supported-connector list for Parquet doesn't include REST, and REST can't read Parquet as a binary stream over HTTP.

Step 2. Configure copy activity 2 to convert to a Delta table

Add a second Copy data activity to the same pipeline.
On the Source tab, set Connection to your Lakehouse, and browse to the file you staged in the last step of Copy activity 1 configuration. Set File format to Parquet.
On the Destination tab, choose your Lakehouse, and select Tables as the root folder.
Choose your table load behavior: Append, Overwrite, or Upsert. If your Supermetrics query uses a refresh window that resends recent days on every run, use Upsert with key columns that reflect the full grain of your data, for example, Date and Campaign name together, rather than Date alone. Anything less, and the rows collapse into each other. See our blog about Parquet to Delta in Microsoft Fabric for more on why key columns matter.
On the Mapping tab, select Import schemas to auto-populate column mapping between the source file and destination table.
Run the pipeline and check the run history. Both activities should show Succeeded, and the new table should appear under Tables in your Lakehouse with the expected row count.

Step 3. Set scheduling

Once you're happy with the output, publish your changes.

From the workspace item list, select Schedule for your pipeline. Fixed is the usual choice for daily marketing loads, but Interval-based is safer when runs can occasionally overrun, since it waits for the previous run to finish. Check the end date carefully, it defaults to one year out.

Troubleshooting Supermetrics API connections in Microsoft Fabric

Preview or connection test fails: Confirm your query URL was copied in full. Query URLs can be long, and truncating them during copy/paste is a common cause of failure.
Authentication error, or stuck asking for credentials: Make sure the connector's authentication kind is set to Anonymous. Since the API key is already part of the query URL, any other authentication kind is likely to fail or prompt for credentials you don't have.
Request times out or is blocked: Some organizations restrict outbound calls to external domains by default. Check with your Fabric admin whether api.supermetrics.com needs to be allow-listed on your network.
API_ROW_QUOTA_EXCEEDED or a request rejected for too many rows: Supermetrics enforces a per-query limit of 1,000,000 rows (set with max_rows) and a monthly account quota of 10,000,000 rows from the API. If you hit the monthly quota, you'll see API_ROW_QUOTA_EXCEEDED in the response. Narrow your query's time range or fields, or check your plan's row limits.
Data lands but looks wrong or incomplete: Re-check your query in the Supermetrics Hub (or your request parameters) to confirm the fields, filters, and time range match what you expect.
The run times out on a wide date range: Narrow the range and schedule more frequent runs.
A field added to the Supermetrics query doesn't appear in the Delta table: Re-run Import schemas on the Copy activity mapping.
Duplicate or missing rows in your Delta table (Option B): If you're using Upsert, confirm your key columns reflect the full grain of your data, for example, date and campaign, not date alone. You can check directly with a query such as:
```
SELECT [key columns], COUNT(*) FROM [table] GROUP BY [key columns] HAVING COUNT(*) > 1
```
Any rows returned mean your key columns aren't unique enough. See our blog post about Parquet to Delta in Microsoft Fabric for more on why this matters.
Scheduled refresh stopped running with no error: Check the schedule's End date. It defaults to one year from creation. If that date has passed, the schedule is no longer active and needs to be renewed.
"QueriesMetadata must not be empty" on refresh: This can happen if a data destination was removed and reconfigured after publishing. If you hit this, delete the dataflow and rebuild it rather than continuing to edit the broken one.
"Doesn't support random read" error on a REST or HTTP source with Parquet format: The connector can't parse Parquet directly from an HTTP stream. Use two Copy activities instead: the first copies the raw file as Binary into your Lakehouse's Files section, the second reads that staged file with Parquet format and writes it to a Table.