Skip to main content

Can I use Snowflake Variant & Array

Comments

5 comments

  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    Yes, you can import data into a variant column in Snowflake, and this is the best practice for ELT use cases.  Here are some pics from one of my working flows.  Please note that 'V' is a variant column.  LMK if you need more info.

    0
  • Alex Baeza
    Answer Pro
    Celigo University Level 4: Legendary
    Awesome Follow-up

    Flow Configs

    Flow

    Import settings

    The parsed Output from my export looks like this:

    {
      "page_of_records": [
        {
          "record": {
            "payload": {
              "Orders": [
                {
                "AmazonOrderId": "113-8158555-4786664",
                  "EarliestShipDate": "2022-01-06T07:59:59Z",
                  "SalesChannel": "Amazon.com",
    ...

    Mappings

    I don't have the "PaymentExecutionDetail" key value pair in my source records, but I want to map it for when I import the entire dataset (right now I am testing with just 100 orders).
    In the Output it looks like it has taken this mapping and interpreted to be a string value of "".

    Errors

    As mentioned earlier the retry data does not contain the key value pair for PaymentExecutionDetail

    0
  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    Your setup is not quite the same as what I sent above, and you should copy my exact setup (i.e. 'data type = JSON' and the expression should be '{{{jsonSerialize this}}}' .  Also, due to the possible shifting columns bug that we are looking into now, I highly recommend removing all individual field mappings where the source record field could be empty, and instead just map the entire JSON object into a single variant column. You can define Snowflake views to separate out all the fields into their own columns.

    0
  • Alex Baeza
    Answer Pro
    Celigo University Level 4: Legendary
    Awesome Follow-up

    Thank you!

    I think I misunderstood what {{{jsonSerialize this}}} was.

    I now realize that jsonSerialize is a helper and this represents the entire record.

    This is exactly what I was looking for.

    Now each record is stored entirely in a single variant column!

    0
  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    Awesome!

    0

Please sign in to leave a comment.