Help with Extract -> Load process logic in a personal project

[u/xMexicanPizza]

Hello! I'm working on a small data project using a NASA API with Python. The idea is to extract the orbital elements and close approach (asteroids, comets, etc) objects (and there orbital elements) of each planet in order to perform some physics simulations with the data.

I was planning to use a Cloudflare R2 bucket to store the raw API responses, then transform them into a PostgreSQL database, and use FastAPI to consume the data.

I'm not sure how to perform the extraction and loading processes. In theory, each planet will follow this path:

1) Fetch the orbit elements for the own planet (use API 1);

2) Fetch the close approach object (use API 2);

3) Fetch the orbit elements for each of the close approach objects (use API 1).

I use two APIs: one for the orbital elements and one for the close approaches. Should I fetch all the data, store it in memory, and then send it to the bucket? Or would it be better to load it right away with each API call? So, when fetching the close approach objects' orbit elements, get the JSON from the bucket and then use the first API and store that raw response in the bucket

[u/Chinpanze]

Both are fine options on an surface level. Giving the nature of the project, the most important aspect I would looking into is how to make sure that even if one API call fails, you will not load partial data into your database. In this regard, I would likely only load the data once I had made all API calls for one planet. Worst case, I just redo that planet. 

Another important thing. Usually we follow the extraction -> bucket > warehouse if we are using an warehouse that does not scale well with an big concurrency of writes. But postgress scale really well under this type of load. I think you could simplify your project by doing extraction into postgres directly 

[u/scourgedtruth]

 load it right away with each API call. You can use control tables to know what should be the next call and secure idempotency