Best Practice for Ingesting External APIs and CSV Data into Microsoft Fabric

Hi FabricEnjoyer​ ,

There isn’t one Fabric ingestion tool that is best for every external source. For a production solution, I use a combination of Data Pipelines, Copy Activity, Notebooks, Dataflow Gen2, and a Lakehouse, with each component having a clear responsibility.

A practical architecture would be:

External sources → Pipeline orchestration → Bronze Lakehouse → Silver transformations → Gold serving layer

1. API-based sources

For a relatively simple REST API, start with the REST connector in a Fabric pipeline Copy Activity. It supports GET and POST requests, JSON responses, authentication options, schema mapping, and configurable pagination. Microsoft documents these capabilities in the Fabric REST Copy Activity documentation.

I would use a Notebook instead when the API involves:

• OAuth token refresh or a custom authentication flow
• Complex cursor- or token-based pagination
• API throttling and HTTP 429 handling
• Multiple dependent API calls
• Nested JSON requiring custom processing
• Dynamic endpoints or request bodies
• Complex incremental-load logic
• File or binary downloads
• API-specific retry and recovery requirements

2. CSV ingestion

For CSV files arriving on a schedule, Pipeline Copy Activity or Copy Job is generally the simplest ingestion option. A pipeline is more appropriate when the file ingestion is part of a larger workflow involving validation, transformation, dependencies, notifications, or downstream processing.

For manually uploaded files, provide a controlled landing location in OneLake or another supported storage service. The pipeline can process files from that location on a schedule or through an appropriate storage event trigger.

I would initially preserve each file in the Bronze layer with its original filename and structure. Files should normally be moved or logically marked as processed only after successful ingestion. Failed or structurally invalid files should be placed in a quarantine area rather than silently skipped.

3. Notebook versus Dataflow Gen2

I normally use Dataflow Gen2 when:

• The source and transformation are supported by Power Query
• The data volume is moderate
• The API authentication and pagination are straightforward
• The transformations are primarily filtering, joining, renaming, and type conversion
• A low-code solution will be easier for the support team to maintain

Dataflow Gen2 can access REST data through the Web API connector. The Web connector is currently supported in Dataflow Gen2 but not as a pipeline connector; pipelines use the separate REST connector. This distinction is covered in the Web connector documentation.

I use a Notebook when:

• Custom Python libraries or SDKs are needed
• JSON is deeply nested
• Authentication or pagination is complex
• Large numbers of API requests must be coordinated
• Detailed logging and checkpointing are required
• Schema changes need custom handling
• The same reusable ingestion framework will support multiple APIs

A Notebook provides more control, but it also introduces more code to maintain. Dataflow Gen2 should not be selected only because it is low-code; operational requirements and API complexity matter more.

4. Landing destination

A Lakehouse is a good default landing destination for this type of data, especially when the sources contain CSV, JSON, nested structures, or semi-structured data.

I would normally use:

• Bronze: Original CSV files and API responses, with minimal modification
• Silver: Validated, flattened, deduplicated, and standardized Delta tables
• Gold: Business-ready tables for reporting, semantic models, or downstream consumption

Gold data can remain in a Lakehouse or be loaded into a Fabric Warehouse when SQL-based modelling and serving are more appropriate. 

Medallion architecture is useful, but it does not require three separate workspaces or three copies of every dataset. The physical design should reflect the size of the implementation, security boundaries, deployment process, and operational requirements.

5. Authentication, pagination, incremental loading and retries

Credentials should never be stored directly in Notebook code or pipeline definitions. Use Fabric connections with supported OAuth, service-principal, or managed-identity authentication. For custom Notebook scenarios, use an approved secret-management solution such as Azure Key Vault.

For incremental API ingestion, store a checkpoint for each source or endpoint, such as:

• Last successful modified timestamp
• Last processed record ID
• Pagination cursor or continuation token
• API-specific synchronization token

Only advance the checkpoint after the corresponding data has been successfully persisted. This makes reruns safer and avoids losing data after a partial failure.

Retries should focus on transient failures such as timeouts, HTTP 429 responses, and HTTP 5xx errors. Use exponential backoff and respect the API’s Retry-After header. Fabric pipeline activities also provide retry settings, as described in the activity documentation.

The ingestion process should be idempotent so that rerunning the same batch does not create duplicate records.

6. Schema drift

For CSV sources, avoid allowing an unexpected column change to corrupt an existing table automatically.

A safer approach is to:

1. Retain the original file in Bronze.
2. Validate the header, delimiter, encoding, and required columns.
3. Compare the incoming structure with the expected schema.
4. Allow approved additive changes where appropriate.
5. Quarantine files with breaking changes.
6. Apply controlled schema changes to Silver tables.

Keep the expected schema in metadata or configuration rather than embedding it independently in every pipeline. This also makes onboarding new sources easier.

7. Recommended production pattern

For a standard onboarding framework, I would make it metadata-driven. Each source configuration could define:

• Source and endpoint
• Ingestion method
• Authentication connection
• Pagination method
• Incremental-load field
• Expected schema
• Bronze destination
• Retry policy
• Rate limits
• Notebook or Dataflow to execute
• Schedule and dependencies

The pipeline then becomes the orchestration layer, while Copy Activity, Dataflow Gen2, or a Notebook performs the ingestion based on the source’s requirements.

In summary:

• Use Pipeline REST Copy Activity for straightforward REST APIs.
• Use Notebooks for complex authentication, pagination, nested JSON, throttling, and custom logic.
• Use Copy Activity or Copy Job for straightforward scheduled CSV ingestion.
• Use Dataflow Gen2 for supported, low-code ingestion and moderate Power Query transformations.
• Use Pipelines for scheduling, dependencies, retries, monitoring, and orchestration.
• Use a Lakehouse Bronze layer to retain replayable source data.
• Transform data into validated Silver Delta tables and business-ready Gold tables.
• Standardize the implementation through metadata, auditing, checkpoints, quarantine handling, and reusable components.

The most important lesson is to preserve the source data and ingestion state. API behavior, CSV layouts, and business rules will change; a replayable Bronze layer and metadata-driven framework make those changes much easier to manage.

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.

If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!