Transformations

The data pipeline wizard offers transformations that you define before AutoSync loads to the destination. Optional transformations include:

Filter columns: Deselect columns that you do not want to synchronize.
Replace empty values: Define a value to populate in empty fields.

As you navigate through the transformation screens, the data preview updates to show the selected transformations. However, the data is not transformed until AutoSync loads it to the target.

Configure transformations for the CSV file endpoint

A data pipeline using the CSV file endpoint doesn't save the file names after the data loads to the target. However, AutoSync does save any transformation configuration. You can use the same data pipeline to upload one or more files with the same name, and AutoSync applies any previously configured transformations. This is true whether you use the Upload CSV file link on the data pipeline card, or edit it — when you select files with the same name.

For example, you might create a data pipeline named CSV-employees to upload data from an employees.csv file to Snowflake. This file lists phone numbers that shouldn't be exposed in the Snowflake table. When creating the data pipeline, remove the Phone column in the Filter columns dialog to prevent AutoSync from loading it. Later, the HR department hires more people and adds them to the employees.csv file.

You can add the new hires to the target table in one of the following ways:

From the CSV-employees data pipeline card:
1. Click Upload CSV file.
2. Select the updated employees.csv file.
3. Click Save to upload the data without the phone numbers.
Double-click the CSV-employees data pipeline to edit it:
1. In the Endpoints tab, select the updated employees.csv file.
2. Click the Transformations tab and AutoSync prompts you to refresh the preview.
3. Click the Refresh link in the message.
4. The Removed columns list still contains the Phone column.
5. Click Save to load the updated file without the phone numbers.

Guidelines for editing transformations

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 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.

Edits to a data pipeline that change the source endpoint or the tables to load can impact configured transformations. Select the Transformations tab. AutoSync informs you about the change:

Click Refresh to update the preview and review the transformation configuration.