I am using matillion data productivity cloud and using custom connector to invoke the API endpoints. I want to store the todo items retrieved from the todos endpoint in a Snowflake table and then use a grid iterator to iterate over the IDs stored in that Snowflake table to fetch individual todo items from the todo endpoint.
Here is the approach that I took Staging Table: This table will store the todo items fetched from the todos endpoint initially. Final Table: This table will store the individual todo items fetched from the todo endpoint using the IDs stored in the staging table.
- Retrieve Todo List: Use your custom connector to make a GET request to the todos endpoint (https://jsonplaceholder.typicode.com/todos). This will give you a list of todo items.
- Store Todo Items in Snowflake: After retrieving the todo items, store them in a Snowflake table.
- Use Grid Iterator: Use a grid iterator in Matillion Data Productivity Cloud to iterate over the IDs stored in the Snowflake table.
- Retrieve Individual Todo Items: For each ID retrieved from the grid iterator, construct the URL for the todo endpoint (https://jsonplaceholder.typicode.com/todos/{todo_id}) and make a GET request to fetch the individual todo item.
- Store Individual Todo Items in Snowflake: After retrieving each individual todo item, store the data in a Snowflake table.
However, the load strategy to the snowflake table has only following options.
How can I invoke the todo item endpoint for all the ids and store it in snowflake table.