When working with cloud data pipelines, especially those involving Amazon Redshift and Informatica Cloud (IICS), one common challenge developers face is the mishandling of empty strings and NULL values during data unload and load operations. This is primarily due to the default behavior of the UNLOAD and COPY commands that govern how data moves between Redshift and S3. This article explains the root cause and how to correctly configure Informatica to manage this issue.
Thank you for reading this post, don't forget to subscribe!Understanding the Issue
By default, Redshift’s COPY command interprets empty strings ("") as NULL values during data loads from S3. This behavior creates confusion in scenarios where empty strings and NULLs need to be treated as is coming from the sources.
For example:
- A column in your source might contain an empty string (which still holds semantic meaning such as “value intentionally left blank”).
- Redshift, during the load, will treat this empty string as NULL by default.
- As a result, empty strings are mistakenly interpreted as NULL, and actual NULLs are also loaded as NULL, making it impossible to distinguish between the two after the load.
Root Cause
This behavior stems from how the following commands work by default:
- UNLOAD: When exporting from Redshift to S3, it doesn’t differentiate NULLs from empty strings unless explicitly told to do so.
- COPY: During load from S3 to Redshift, the default setting is
NULL AS '', which tells Redshift to treat any empty string as a NULL value.
Solution: Use Custom NULL Markers
To fix this, Informatica allows you to override these defaults using advanced properties for both source and target configurations.
✅ Step 1: Configure Source Properties
Use the source option:
Treat NULL value as NULL
And in the Unload Options, set:
NULL = '#@!'
This instructs Redshift to replace all true NULL values with a special marker (e.g., #@!) when unloading to S3. This helps preserve the distinction between NULL and empty string in the output file.
✅ Step 2: Configure Target Properties
The default COPY command behavior in Redshift is:
NULL ''
This means empty strings will be treated as NULLs, which is not what we want.
To override this behavior in Informatica, use:
Require Null value for CHAR and VARCHAR
Here, you set the same special NULL marker (#@!). This ensures that:
- Only
#@!will be treated as NULL - Empty strings will remain as empty strings
Benefits of This Approach
- ✅ Preserves Data Accuracy: Ensures empty strings are retained and not lost as NULLs.
- ✅ Ensures Reversibility: Data unloaded from Redshift can be safely reloaded without introducing ambiguity.
- ✅ Simplifies ETL Logic: Reduces need for downstream transformation or error handling.
- ✅ Handling NULLs and empty strings properly in Redshift is crucial for data integrity, especially when integrating with ETL tools like Informatica IICS. By using a custom NULL marker (such as
#@!) and configuring both source and target settings, you maintain a clear distinction between NULL and empty string, ensuring the accuracy and consistency of your data pipelines.
Also Read: Data Engineering Roadmap for ETL Developers Transitioning in 2025
