I have setup Snowflake pipe to ingest S3 files, which were created by AWS DMS migration task. The pipe ingests the files in a Snowflake table. S3 file with inserts work fine, but when a file has a record that was deleted, I get this error in Snowflake when the pipe tries to ingest it,
NULL result in a non-nullable column
The files are in parquet format. What am I missing or is deletion not supported in Snowflake pipe ingestion?
Heres the table,
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+--------------------------------------
name | character varying | | not null |
value | character varying | | not null |
type | character varying | | not null |
id | integer | | not null | nextval('constant_id_seq'::regclass)
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
Indexes:
"constant_pkey" PRIMARY KEY, btree (id)
"constant_name_key" UNIQUE CONSTRAINT, btree (name)
Here is the parquet file generated by AWS DMS when a row is deleted,
Op name value type id created_at updated_at
-----------------------------------------------------
D None None None 16 NaT NaT
If its a delete operation why does Snowflake need non null columns to be filled?
Update:
Followed the solution [here][1].
I have set REPLICA IDENTITY FULL on the table. Now when I delete a record in rds, no S3 file is generated by the migration task.
[1]: https://stackoverflow.com/questions/73653720/amazon-dms-not-fully-replicating-deleted-cdc-records