Forum Discussion
Best patterns for ingesting dynamic tax & compliance API data into Fabric Lakehouse?
Hi everyone,
We are currently migrating our firm's analytical workloads to Microsoft Fabric. A huge part of our business involves tracking dynamic regional tax rates, company registration statuses, and financial compliance data for startups.
Currently, our analysts manually pull this data from external regulatory sources and calculators, like this FBR Sales Tax Calculator, and we import it via Excel/CSV into Power BI.
We want to fully automate this in Fabric. What is the recommended pattern for hitting external REST APIs on a scheduled basis (daily) to pull dynamic financial data?
Should we use Data Factory pipelines with a Web activity to land the JSON directly into a Lakehouse, or is it better to use a Notebook (PySpark) to handle the API pagination and JSON flattening before writing to delta tables?
Would appreciate any architecture advice for handling external financial APIs in Fabric! Thanks.
1 Reply
Hi secpporta ,
For a daily ingestion scenario like this, the decision really depends on how complex the REST API is and what processing is required.
If the API is relatively straightforward (for example, a single endpoint returning a manageable JSON payload with simple authentication), a Data Factory pipeline using a Copy Data activity with a REST source is typically sufficient. It can schedule the ingestion and write the data directly to a Lakehouse in OneLake.
However, if the API requires more advanced handling—such as pagination, rate limiting, OAuth token refresh, nested JSON, retries, or custom business logic—a Notebook (PySpark or Python) is usually a better choice. Python libraries provide much greater flexibility for interacting with external APIs and transforming complex JSON before writing Delta tables.
A pattern I've seen work well is:
- Data Pipeline for orchestration and scheduling.
- Notebook for calling the REST API, handling authentication, pagination, retries, and JSON transformation.
- Write the raw response to a Bronze layer for traceability.
- Transform into curated Silver/Gold Delta tables for reporting.
This approach also makes it easier to implement monitoring, incremental loads, schema evolution, and replay data if an API changes or fails.
Since you're working with regulatory and financial data, I'd also recommend considering:
- Storing API credentials in Azure Key Vault (or another secure secret store) rather than hardcoding them.
- Logging API responses and failures for auditing.
- Implementing retry logic and handling API rate limits.
- Capturing the ingestion timestamp and source metadata so analysts know when the data was last refreshed.
In short, use the simplest solution that meets your requirements. If the API is simple, a Copy activity may be enough. If the API involves significant logic or transformation, orchestrating a Notebook from a Data Pipeline is generally the more maintainable architecture.
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!
