From Kafka json message to Snowflake table

I am trying to implement a Snowflake Sink connector so that can i load messages coming to a Kafka topic directly to an appropriate Snowflake table. So far, I could only get to the point of loading the raw json into a table with two columns (RECORD_METADATA and RECORD_CONTENT). My goal would be to directly load the json messages into an appropriate table by flattening them. I have the structure of what the table should be, so I could create a table and directly load into that. But I need a way for the load process to flatten the messages.

I have been looking online and through the documentation, but haven't found a clear way to do this. Is it possible or do I have to first load the raw json and then do transformations to get the table that I want?

Thanks

Solution 1:

You have to load the raw JSON first then you can do transformations.

Each Kafka message is passed to Snowflake in JSON format or Avro format. The Kafka connector stores that formatted information in a single column of type VARIANT. The data is not parsed, and the data is not split into multiple columns in the Snowflake table.

For more information you can read here