Forum Discussion
API only delivers one year of data, how to avoid deleting data older than a year?
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.
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:
- 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.
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.