Upsolver snowflake output creating NULL records in snowflake child table

Question

We have a nested json in our input stream which we are writing into snowflake normalized tables using Upsolver snowflake outputs. The parent table is fine but seeing NULL records in the child table. Why is this happening and how can we solve this issue?

Answer 1

This is happening perhaps because you have empty child nodes in your input json. Since the parent id is always present in the input record, the child table output record will have this parent id populated and rest of the child table columns will be NULL as there was no data in the input event for it. To account for this, please add "WHERE <child_identifier> is NOT NULL" in your child table Upsolver Snowflake output. This will ensure only valid child nodes would actually get written.

Note: The preview for the Upsolver output for the child record would show nulls as well, that should be an indicator that the SQL for this output needs some correction, in this case, the WHERE filter.

If this job has already been run, please stop the job, truncate the snowflake table, edit the job to add the WHERE criteria and re-run the job from the beginning (replay from beginning).