Hi Team,
I have developed an API Query component to ingest data from Oracle CPQ. However, the API response is a fairly large JSON payload, and the ingestion process is running very slowly and even timing out. My understanding is that the API Query Profile is spending significant time parsing and flattening the JSON before inserting it into Snowflake.
As a comparison, I tested the same API call using a Python component in Matillion, where I queried the API, parsed the JSON, and inserted it into Snowflake. This approach was noticeably faster than the API Query Profile.
My question is: Is there a way to configure the API Query component to simply retrieve the API response and ingest the raw JSON directly into a VARIANT column in Snowflake, without flattening?
I was advised to look into the API Extract component, but it does not support “Advanced Mode,” so I’m unable to apply the additional configuration needed.
Thanks,
Srinadhan