SCD 2 in snaplogic with snowflake as target table

[mangeshj]

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

[RogerSramkoski]

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]

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]

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.

mangeshj:

some time it gives invalid identifier error for age column but it is resolved .

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?

[mangeshj]

Hi rsramkoski,

Attaching screen shot of the snaps and table structure and src file

person_src.txt (88 Bytes)

and this is table structure with initial one record

insert into snp_scd_usecase_Person
(id ,
first_name,
last_name,
AGE,
current_row,
start_date,
end_date)

values
(5,‘Vishal’,‘Singh’,30,1,current_date-2,current_date-1)

and source file has updated record with age = 32

so i want output with two records along with start date and end date
sample output expected
id name age current row
5 vishal 30 0
5 vishal 32 1

please suggest

i got this 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
but not able to resolve it .
Regards,

Mangesh