Transformation tips
Filter columns and null handling
After a data pipeline is saved, you can edit it to change the transformation selections. Before you edit a transformation, consider how it might impact existing data. This is especially important with transformations that remove columns or handle nulls for data pipelines using the Incremental load type.
For example, if the pipeline uses Incremental load, and you change null handling for a column, the columns loaded before the change will still contain empty values. To avoid this, you can set the load type to Full load after changing a transformation, run it once, and switch back to Incremental. For the Full load type, AutoSync drops the table and reloads.
The table below outlines transformation-specific behavior for each load type.
Transformation edit | Full load | Incremental | SCD2 |
---|---|---|---|
Filter column - column removed | Existing table is dropped. The new table does not contain the removed column. | Existing column remains in the destination. Records loaded after the change have a null value in the removed column. To remove the column from the destination, you must remove it manually. | Existing column remains in the destination. Records loaded after the change have a null value in the removed column. Historic entries are unchanged. |
Filter column - column added back after removal | Existing table is dropped. The new table contains the added column. | Existing column remains in the destination. Records loaded before the change have a null value. Records loaded after the change have the source value for the added column. | Existing column remains in the destination. Records loaded before the change have a null value. Records loaded after the change have the source value for the added column. Historical entries are unchanged. |
Replace empty value - change | Existing table is dropped. The new table contains the specified null handling. | Existing values remain empty. Records loaded after the change use the specified null handling. | Existing values remain empty. Records loaded after the change use the specified null handling. Historical entries are unchanged. |