Best practices for ingesting Marketo data into Microsoft Fabric using REST API / Bulk API

Hi RMori95610 ,

Thank you for reaching out to Microsoft Fabric Community Forum,Below are the few points which can resolve your issue.Let us know if you need any further assistance.

• There is no native Microsoft Fabric connector for Marketo; use the Marketo REST API or Bulk Extract API.
• Use Fabric Notebooks for production ingestion to handle authentication, pagination, retries, rate limits, and incremental loading.
• Use Data Pipelines for orchestration, scheduling, monitoring, and triggering notebook execution.
• Use the Bulk Extract API for high-volume data (such as Activity Logs) instead of the REST API.
• Follow the Medallion Architecture: store raw CSV files in Bronze and transform them into Delta tables in Silver.
• Separate ingestion from transformation to support reprocessing, auditing, and easier maintenance.
• Implement incremental loading using a watermark (last successful extraction time) to reduce API calls and improve performance.
• Retain Marketo Person ID along with Salesforce Lead/Contact IDs to establish reliable relationships between Marketo and Salesforce data..
• Secure API credentials using Fabric Variable Libraries or Azure Key Vault.

Thanks,

Chaithanya.

Hi RMori95610​,

I’d probably keep the architecture pipeline-first rather than defaulting to a notebook for all Marketo ingestion.

For the normal REST API, Fabric’s REST connector in Copy activity already supports GET/POST requests, pagination, additional headers and configurable intervals between requests. Marketo authentication is a client-credentials flow where you first obtain a short-lived access token from the Identity endpoint and then pass it as a Bearer token.

Because of that, one low-code pattern I would consider is:

Web activity → obtain Marketo token → REST/Copy activity → Lakehouse Bronze

and let the pipeline handle orchestration, retries, scheduling and watermarking.

I would move to a notebook when the API behaviour becomes awkward to express declaratively - for example complex pagination, custom retry/backoff logic, schema variation, or several dependent Marketo calls. I wouldn’t treat a notebook as mandatory just because there is no native Marketo connector.

For the Activity Log/Bulk Extract side, I would use a slightly different pattern:

Pipeline → create Bulk Extract job → enqueue → poll status → download completed file → Bronze Files → transform into Silver Delta tables

That maps quite naturally to how Marketo’s Bulk Extract API works because the exports are asynchronous.

I would definitely land the exported CSV unchanged in Bronze first, rather than converting it immediately during ingestion. Then parse/deduplicate/type it into Silver and expose business-ready datasets from Gold. That also aligns well with Microsoft’s recommended medallion pattern for Fabric.

One reason I would keep ingestion and transformation separate is Marketo’s own Bulk API limits. The default allocation is currently 500 MB/day, with only 2 export jobs processing concurrently and 10 jobs allowed in the queue. That makes incremental extraction and a reliable watermark/control table fairly important.

For example, I’d persist something like:

source | object/activity type | last_successful_watermark | job_id | status | extracted_at

and only advance the watermark after the file has landed successfully. That makes reruns much safer than deriving the next window purely from the pipeline execution time.

So my preference would be:

• Smaller/incremental REST extraction: Pipeline + REST/Web activities where practical.
• Complex REST behaviour: Notebook inside a pipeline.
• High-volume activities: Bulk Extract API, orchestrated by a pipeline.
• Storage: Raw response/files in Bronze → validated Delta tables in Silver → curated Gold as needed.

I’d also keep the Salesforce and Marketo ingestion paths separate at Bronze and join/conform them in Silver rather than coupling the source extractions together.

I really hope this helps. I think Fabric can handle this quite cleanly without requiring every Marketo interaction to become custom notebook code.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Thankyou ShivekMaharaj  for Addressing the issue.

Hi RMori95610   ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

Hi @RMori95610   ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya

Apologies for the delayed response, as I was away on vacation.

Thank you both,  v-kathullac​  and ShivekMaharaj​, very much for your detailed responses. They were extremely helpful.

v-kathullac​ , your recommendation to use a Notebook to handle authentication, pagination, retries, API limits, and incremental loading, with a Pipeline handling scheduling and orchestration, helped clarify one possible production architecture.

ShivekMaharaj​ , your suggestion of taking a pipeline-first approach was also very helpful — using Web / REST / Copy activities where practical, and moving to a Notebook only when the API logic becomes more complex.

Based on both responses, for the standard Marketo REST API, we are considering first validating whether the ingestion can be implemented using Pipeline activities, and then using a Notebook where necessary, for example when complex pagination, custom retry/backoff logic, or other API-specific handling is required.

I also understand from both responses that for high-volume data such as Activity Logs, using the Bulk Extract API and landing the raw CSV files in Bronze before transforming them into Delta tables in Silver would be a good approach.

I have one additional question regarding how incrementally extracted data should be loaded into the Silver Delta tables.

I understand that a watermark can be used to retrieve only the Marketo data that has changed since the last successful extraction.

However, for data such as Marketo Person records, existing records may also be updated. Therefore, after retrieving the incremental data, we would need not only inserts but also updates (Upsert/MERGE) when loading the data into the existing Silver Delta table.

My understanding is that there are limitations around Upsert when using Dataflow Gen2 with a Lakehouse Delta table as the destination.

In a pipeline-first architecture, what would be the recommended approach for implementing Upsert/MERGE into the Silver Delta tables?

For example, would the following be a common pattern?

Pipeline incremental extraction → Bronze → MERGE using a Notebook (or another Fabric component) → Silver Delta table

Also, would you recommend using different Silver loading patterns for append-oriented data such as Activity Logs versus mutable data such as Person records?

Any guidance on the recommended Fabric pattern would be greatly appreciated.

Thank you again, for taking the time to provide such detailed guidance.

Hi ShivekMaharaj​ 
Thank you very much for the detailed clarification. This was extremely helpful.

I now understand that for a straightforward key-based upsert, we can use Copy Activity with Upsert directly into the Silver Lakehouse table, without necessarily introducing a Notebook.

The distinction between the three patterns — Upsert for current-state data such as Person, Append for immutable Activity/Event data, and Notebook/Delta MERGE for more complex logic — was especially helpful and makes the implementation approach much clearer.

We will use this as a basis for our Fabric architecture and further validate the implementation details.

Thank you again for taking the time to answer my follow-up question!

Hi @RMori95610   ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Chaithanya