API only delivers one year of data, how to avoid deleting data older than a year?

[Bokazoit]

The API I am retrieving data from only delivers one year total of data, meaning today I can get data from 2024-06-04 to 2025-06-04. Tomorrow it will be 2024-06-05 to 2025-06-05 etc. 

So in the above I will be missing the data from 2024-06-04. In a DB I can insert, update, delete. How to handle the insert in this example?

[v-pnaroju-msft · Accepted solution]

Hi Bokazoit,

Thank you for your valuable feedback, and kindly accept our apologies for any inconvenience caused.

As the API enforces a strict 365-day rolling window, retaining data older than one year is possible; however, it requires an approach that decouples storage from the API’s limitations. 

Please find below an approach that helps resolve this issue:

1. Leverage Microsoft Fabric Pipelines to ingest data daily and save it into a Lakehouse in OneLake. Treat the Lakehouse as your permanent vault, storing each daily snapshot. Even if the API drops a particular day (for example, 06/04/2024), your Lakehouse will still retain the data. Each day, append the new day’s data from the API. Use a partitioned table in the Lakehouse by date to efficiently manage and query data over time.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi Bokazoit,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[Bokazoit]

It is not helpful nor a solution. If all solutions in the world were all the "not possible" the world would have standed still and the same for evolutoin

[Poojara_D12]

Hi @Bokazoit 

In your scenario, where the API only provides a rolling one-year window of data that shifts daily—meaning each day you lose the oldest day’s data and gain a new day’s data—handling inserts in your database requires a strategy to maintain a complete historical record despite the limited API window. Since the API data window moves forward every day, you cannot rely on the API alone to keep the full dataset, especially for older dates falling outside the current one-year window. To handle inserts effectively, you should implement an incremental load process that fetches the latest available data from the API each day and inserts new records for those new dates. At the same time, your database must maintain all previously collected data for dates outside the current API window so you don’t lose historical data. This often means your ETL or data pipeline should detect which dates are newly available from the API, insert or update those records, and avoid deleting older data that is no longer provided by the API. In other words, you treat the API as a moving snapshot and maintain your own persistent storage that accumulates data over time. This approach ensures that even when the API drops older dates from its response, your database retains a full historical timeline by only inserting and updating new or changed records, and never deleting older data unless explicitly required.

[v-pnaroju-msft]

Hi @Bokazoit,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

To the best of my understanding, since the API provides data only for a rolling one-year window, the most effective way to prevent the loss of older data is to implement a daily incremental data ingestion process. This process should store each day’s retrieved data into a persistent storage medium such as a Microsoft Fabric Lakehouse, Warehouse, or SQL database.

Please consider the following approach, which may help resolve the issue:

1. As Power BI Desktop does not retain data across refreshes, it is advisable to store historical data in an external location, such as an Azure SQL Database, Azure Data Lake, a local SQL Server, or Excel/CSV files on OneDrive or SharePoint.
2. Adjust your API calls to fetch only data that is newer than the most recent record available in your stored dataset.
3. Append the newly fetched data to the existing historical data each time, either within Power BI or the external data storage.
4. Utilize Power Automate or Azure Data Factory to schedule automatic API data retrieval and storage.
5. For scalable and well-managed historical data handling, consider using Power BI Dataflows or the Incremental Refresh feature available in Power BI Premium or Microsoft Fabric.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who face similar issues.

Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.