Can I reload data in destination database for given period?

Connector Development
1

Hello! I’m new in airbyte connector’s developing :slight_smile: I have a question: can I reload data in destination for period. For example, every day my source API-connector gets data for last 30 days and I need delete this 30 days in my destination database, loads new one and don’t delete older than 30 days data. Is it real or only one way is load data incremental and then select latest version?

2

Hello @coca-alex,
Thank you for this question, and I’m glad you are considering developing a new connector!
Source and destination are two very well separated resources in Airbyte. It means a source connector only outputs data and can’t act on the destination database.
To achieve what you want, here’s my suggestion:
On the source side:

  • Implement a “lookback window” in your source connector by adding an (optional?) configuration field that will take the number of days you want to load data for on each sync, 30 days in your example.

On the connection setting side:

  • Use a full refresh - overwrite sync mode, this will erase all your destination data and reload with the newest one coming from the source.

On each sync the connector will retrieve only 30 days of data and the full refresh overwrite mode will guarantee that you only have the most up to date data in your database.

Does it make sense?

3

Thank you for your reply!

Yep, I implemented “lookback window” in my API source connector, but on connection side full refresh - overwrite sync mode is inappropriate for me because data that I get from API may be updated in the future. I need store not only 30 days, but also older ones.

Example day-by-day: :slightly_smiling_face:
Today Sync (First launch sync)

  1. get from api data for the last 30 days (selected by date_field)
  2. insert newest data to database table from step 1

Tomorrow Sync

  1. get from api data for the last 30 days (selected by date_field)
  2. delete from database_table where date_field between tomorrow - 30 and tomorrow
  3. insert newest data to database table from step 1

In this case rows with date_field = tomorrow - 31 from Today Sync doesn’t be reloaded or deleted and that’s what I need.

4

Thank you for the details. Do you think using Incremental Deduped History sync mode would work for you? Two records from different syncs with the same primary key will be deduplicated, only keeping the latest record according to the cursor value.

5

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.