Forum Discussion

mangeshj's avatar
mangeshj
New Contributor
4 years ago

SCD 2 in snaplogic with snowflake as target table

hi team,

would like to implement scd type 2 in snaplogic and target dw is snowflake table.

scenario : 1) Need to select sample txt / json file from local drive
2) process file into snowflake table
3) what are the snaps and how it links in pipeline to capture scd type 2 scenario
can anyone provides some guidance, i am not getting which snaps need to linked
earlier i had used Json generator snaps , snowflake scd2 snaps and snowflake bulk upsert
but it did not process json document . and there is no error . it did not read the json document
Regards,
Mangesh

4 Replies

  • RogerSramkoski's avatar
    RogerSramkoski
    Employee
    4 years ago

    Hello @mangeshj! Sounds like there may be a couple issues here, so let me recap what I’m reading to make sure I understand correctly.

    1. How to create a pipeline in SnapLogic for SCD2 in Snowflake that reads from a local file
    2. You built a test pipeline with JSON Generator instead of reading the file but it did not process the data

    For the first point, our SCD2 documentation has an example that reads from a file. Please check that out if you have not already as it includes a walk through and pipeline you can import to test. One question I do have for the first point is regarding the local file - do you mean local like it’s on your laptop/workstation? Or local as in it exists on the Snaplex node?

    For the second point, can you provide more detail or a screenshot to show what you observed? Did you verify that the file was not process by looking in Snowflake, or just in SnapLogic? More details about how you verified the data was not processed would help too.

    • mangeshj's avatar
      mangeshj
      New Contributor
      4 years ago

      thanks for the reply , i am trying resovled scd type 2 issue .

      still getting this error in SNOWFLAKE scd 2 snaps : Failure: Input data contains no entry for natural key: $first_name, Reason: Input data contains no entry for natural key: $first_name, Resolution: Please check if input data has valid entries for Natural key
      to implement scd 2 using snowflake , i have added one initial record in snowflake table

      and then adding corresponding updated record in local file and trying to read the same updated record from file reader . i am using file reader , csv parser , mapper , snowflakescd type 2 , snowflake bulk upsert snaps .
      some time it gives invalid identifier error for age column but it is resolved .
      but it is not updating existing record in snowflake and it is not inserting new record into snowflake

      Regards,
      Mangesh

      • RogerSramkoski's avatar
        RogerSramkoski
        Employee
        4 years ago

        Hello @mangeshj, the first error Failure: Input data contains no entry for natural key: $first_name, Reason: Input data contains no entry for natural key: $first_name, Resolution: Please check if input data has valid entries for Natural key sounds like the input data (from the file) was missing the $first_name key. If your Snap is set to stop pipeline execution when an error occurs and just one record fails with that error, a simple step for this test pipeline would be to check the “Null safe access” box in your Mapper snap.

        To clarify this sentence, are you still seeing the invalid identifier error for $age or is that completely resolved? I believe it is the latter but just want to verify.

        It sounds like there is a misalignment between the input data file and the columns in Snowflake, and possibly a setting in the Mapper or either Snowflake Snap. Could you please share your pipeline and test file? If not, can you share screenshots of your Mapper and both Snowflake Snaps? Could you also show a screenshot of the table structure in Snowflake so we can see the column names?