Junk Characters coming to Snowflake

[pguddera21]

Replicating from Oracle to Snowflake via QLIK Replicate May 2024 version.

We are starting to see trailing junk characters (red NULs) being brought in to snowflake which does NOT exist in the Oracle Source.
Target:

I checked the source and made sure there was NOT any hidden junk characters or spaces after.

I have the following settings enabled in our task to make sure something like this does not happen:

Please let me know if there is a known issue/fix for this.

[Dana_Baldwin]

Hi @pguddera21 

I see no documented issue or fix for this. To better assist you, please open a support case and attach a diagnostics package for the task. If a log stream configuration, please attach both the staging and replication task diagnostics packages.

If needed, instructions for generating a diagnostics package are here: https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/...

Thanks,

Dana

[john_wang]

Hello @pguddera21 ,

In addition to @Dana_Baldwin 's comments, the characters in question are invisible, but we can detect them by querying their hexadecimal values. Could you please run the following SQL in Oracle:

select colName, dump(colName) from <tableName> where PK=nnn;

This query will help us determine if there are any additional hidden characters in the column.

Hope this helps.

John.

[pguddera21]

Hi John, 

I see the junk characters hidden in the oracle source via that query:

The string iteslf is only 10 characters, but I see the trailing 0's for this row and a select few aswell.

Is there any setting in qlik to remove these?

Typ=1 Len=30: 71,82,79,85,80,32,80,46,67,46,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

[john_wang]

Hello @pguddera21 ,

Thanks for the update.

Based on the values you provided, I assume that your Oracle table column is defined as CHAR(30), and you're storing only 10 characters in it. The remaining 20 characters appear to be padded with null characters (ASCII code 0, hex 0x00, \x00).

By default, Oracle pads unused space in CHAR fields with space characters (ASCII 32). However, in your specific case, the column appears to be padded with null characters (ASCII 0) instead. Both ASCII 0 and ASCII 32 are valid in Oracle and typically do not interfere with replication processes.

In Qlik Replicate, when the target is Snowflake, data is transferred via intermediate CSV files. It's important to note that characters such as ASCII 0 or ASCII 32 in Oracle may not be preserved in the CSV format. Therefore, the NUL value you see in Snowflake might not have been transferred from Oracle by Replicate, but rather could be a display behavior or placeholder on the Snowflake side.

To investigate further, you can:

1- Inspect the CSV Files Used by Replicate

    Enable the following internal parameters on the Snowflake target endpoint:

    keepCSVFiles   = true
    keepErrorFiles = true

    Then re-run the task. Review the generated CSV files (under task sub-folder) to determine whether ASCII 0 characters are present in the data rows.

2- Check How You're Viewing the Data

     Are you using a specific SQL client or application to view the Snowflake data?
     Some tools may render unprintable characters like ASCII 0 as NUL or another placeholder. This could be a visual representation, not an actual stored value.

Regards,

John.