# Question on Ingesting Raw JSON into Snowflake Using Matillion API Quer

**URL:** <https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502>\
**Category:** Matillion ETL\
**Created:** [March 23, 2026, 9:08pm UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502 "2026-03-23T21:08:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![srinadhan\_samudrala](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/srinadhan_samudrala/32/1225_2.png) [@srinadhan\_samudrala](https://matillioncommunity.discourse.group/u/srinadhan_samudrala)\
**Post date:** [March 23, 2026, 9:08pm UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/1 "2026-03-23T21:08:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\
**Post date:** [March 24, 2026, 10:44am UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/2 "2026-03-24T10:44:48Z")

</div>

> [@srinadhan\_samudrala](#):
>
> 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?

Hello @srinadhan_samudrala

Thank you for your post. I will take this away and look into it for you.

Kind regards Joe

---

<div class="post-metadata">

**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\
**Post date:** [March 24, 2026, 4:07pm UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/3 "2026-03-24T16:07:48Z")

</div>

Hello @srinadhan_samudrala

Thank you for your patience whilst I went away and looked into this.

You’re hitting a common default behaviour: API queries often default to `VARCHAR` this because they can’t always predict the structure of the incoming data. If you want more control, specifically to use a `VARIANT` type for JSON, you’ll need to handle the table schema manually.

Here is the standard workflow to fix this:

### **1. Capture the Full Payload**

Instead of mapping individual fields in your API profile, create a profile with a **single column**. You’ll likely need to use a special attribute (often called **Aggregate** ) to ensure you are capturing the entire JSON blob rather than just a specific root value.

### **2. Manage the Column Size**

By default, these columns often cap out at `VARCHAR(2000)`. If your JSON payload is beefy, you’ll need to override this in your connection options:

- **Parameter:** `defaultcolumnsize`

- **Value:** Set this to a significantly larger number (e.g., `16777216` for Snowflake) to prevent truncation before the data hits your table.

### **3. Manual Table Creation**

To get that `VARIANT` data type:

- **Create the target table manually** in your database with the column defined as `VARIANT` (or the equivalent JSON type for your specific warehouse).

- **Disable “Create Target Table”** within the component settings. This forces the component to map the incoming data to your existing, correctly-typed table rather than trying to build a new `VARCHAR` one on the fly.

---

<div class="post-metadata">

**Author:** ![srinadhan\_samudrala](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/srinadhan_samudrala/32/1225_2.png) [@srinadhan\_samudrala](https://matillioncommunity.discourse.group/u/srinadhan_samudrala)\
**Post date:** [March 24, 2026, 9:04pm UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/4 "2026-03-24T21:04:59Z")

</div>

Hello , thank you very much for detailed set, here below is my API Query profile,towards setting up of the column I tried below option and just got blank, do you have sample RSD file to refer?

---

<div class="post-metadata">

**Author:** ![srinadhan\_samudrala](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/srinadhan_samudrala/32/1225_2.png) [@srinadhan\_samudrala](https://matillioncommunity.discourse.group/u/srinadhan_samudrala)\
**Post date:** [March 25, 2026, 1:33am UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/5 "2026-03-25T01:33:51Z")

</div>

Issue is resolved now, I was able to user suggestion along with other posted information

---

<div class="post-metadata">

**Author:** ![Joe-Community-Manager](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/joe-community-manager/32/897_2.png) [@Joe-Community-Manager](https://matillioncommunity.discourse.group/u/Joe-Community-Manager)\
**Post date:** [March 25, 2026, 11:52am UTC](https://matillioncommunity.discourse.group/t/question-on-ingesting-raw-json-into-snowflake-using-matillion-api-quer/3502/6 "2026-03-25T11:52:09Z")

</div>

That is awesome @srinadhan_samudrala

Thank you for letting us know.

Kind regards, Joe
