Load-time transformations

PipelineWise can transform selected fields during ingestion. PostgreSQL and MySQL/MariaDB FastSync to Snowflake apply top-level transformations in the source SELECT, before CSV generation, for both FullSync and PartialSync and both native and managed Iceberg v3 targets. Local export files, S3 staging, archived load files, and Snowflake staging therefore receive only the configured output. There is no subsequent Snowflake transformation UPDATE on these routes.

Conditional rules intentionally retain the value when their conditions do not match; columns without transformations are unchanged. Configure rules accordingly before treating them as a security boundary. Other FastSync routes retain their existing target-side transformation behaviour. Singer applies transformations between the tap and target. Perform joins, aggregations, and analytical modelling after ingestion.

Source-side masking uses source database CPU instead of Snowflake update work. Measure that load when scheduling bulk exports; a 64-character hash can increase the size of a short input even though raw values no longer need to be transferred.

Transformation types

Available

Type	Compatible input	Output
SET-NULL	Any supported field	NULL.
HASH	String	SHA-256 representation.
HASH-SKIP-FIRST-n	String; n is 1–9	First n characters plus a hash of the remainder.
MASK-DATE	Date or timestamp	Date masked to 1 January.
MASK-NUMBER	Numeric	Zero.
MASK-HIDDEN	String	hidden.
MASK-STRING-SKIP-ENDS-n	String; n is 1–9	Preserves the first and last n characters and masks the middle.

For MASK-STRING-SKIP-ENDS-n, values no longer than 2 * n are fully masked.

Mapped column types

Transformations do not choose or change the target column type. FastSync uses its existing source-to-Snowflake mappings; Singer uses its existing schema mappings. Managed Iceberg uses the selected version’s canonical physical types. FastSync validates each rule against that mapped type before export:

Snowflake FastSync type support

Mapped type	Supported transformations
VARCHAR	SET-NULL, HASH, HASH-SKIP-FIRST-n, MASK-HIDDEN, MASK-STRING-SKIP-ENDS-n
NUMBER, FLOAT / Iceberg DOUBLE	SET-NULL, MASK-NUMBER
DATE, TIMESTAMP_NTZ	SET-NULL, MASK-DATE
BOOLEAN, TIME, BINARY, VARIANT	SET-NULL

For example, MySQL VARBINARY maps to BINARY, so hashing it is rejected; it is not silently remapped to text. Fixed-size string outputs must fit the mapped width. Unknown mapped types and unsupported combinations fail before CSV creation. Correct the configuration rather than relying on an implicit cast.

Existing native PartialSync columns must also accept the mapped values without changing type family or losing width or precision. Compatible narrow text columns still widen automatically. Iceberg retains its stricter canonical type and recovery checks. See PartialSync behaviour.

Configuration

tables:
  - table_name: "users"
    replication_method: "LOG_BASED"
    transformations:
      - column: "email"
        type: "HASH"
      - column: "phone"
        type: "MASK-STRING-SKIP-ENDS-2"

Nested JSON fields

field_paths applies a transformation to selected paths inside the JSON object named by column. A condition can similarly inspect when[].field_path.

Warning

PipelineWise rejects field_paths and when[].field_path for every connector pair that has a FastSync component: MariaDB/MySQL, PostgreSQL, or MongoDB sources targeting PostgreSQL or Snowflake. This route-level check applies even when the configured table would use Singer replication. On those routes, transform only top-level columns or omit the nested condition.

Conditions

when applies a transformation only when every condition matches:

transformations:
  - column: "value"
    type: "SET-NULL"
    when:
      - column: "field_name"
        regex_match: "password|secret|token"
      - column: "environment"
        equals: "production"

Conditions can inspect a top-level column. Nested field_path conditions are subject to the FastSync-route restriction above. Multiple entries use logical AND.

Snowflake FastSync semantics

Source-side execution preserves the existing Snowflake FastSync order:

1. Conditional transformations run individually in configuration order. Each condition sees the result of preceding conditional transformations.

2. Unconditional transformations then run together against that resulting row, regardless of their position among the conditional rules in the configuration.

This differs from Singer’s sequential configuration order. Avoid depending on the difference in tables that switch between FastSync and Singer.

Other existing Singer differences also remain: HASH-SKIP-FIRST-n slices the hash input by UTF-8 bytes rather than characters; regex conditions search within the string; and equality conditions with 0, false, or null are ignored and do not trigger a rule on their own. For PostgreSQL JSON object/array schemas, Singer can load a transformed None as JSON null rather than FastSync’s SQL NULL. These differences are tested explicitly; matching column types alone does not guarantee identical transformation results.

String operations count characters, not UTF-8 bytes. Hashes remain hexadecimal SHA-256. NULL and empty strings stay distinct; MASK-HIDDEN and MASK-NUMBER replace even NULL with their configured constant. Date masking retains the time and fractional seconds. Existing export normalization, including MySQL/MariaDB NUL removal and invalid-date handling, happens before transformation.

Equality comparisons are case-sensitive and preserve trailing spaces. Regex conditions accept text and integer columns; equality requires matching string, numeric, or Boolean values, or NULL. Implicit date/string/numeric coercions and floating-point regex rendering are rejected.

Regex conditions match the whole string, are case-sensitive, and . does not match a newline. Source execution accepts a portable regex subset: literals, character ranges, groups, alternation, ordinary repetitions, and bounded repetitions up to 255. Engine-specific constructs such as lookarounds, backreferences, inline flags, locale character classes, and character-class set operators such as [a&&b] are rejected rather than delegated to a different regex engine with potentially different results. Backslashes in equality values or regex patterns are also rejected: the previous Snowflake SQL literals interpreted them as escapes, so treating them literally could change which rows are masked. Use an equivalent unambiguous condition (for example, [.] for a literal dot) and verify its intended matches.

Unknown or ambiguous columns, nested paths, incompatible types, unsupported condition coercions, and duplicate unconditional assignments to one column fail before an export file is opened. There is no fallback to exporting raw values or running the transformation after loading. Correct the rule or source schema and retry; do not disable the transformation to bypass this guard. Managed-Iceberg runs validate the projection before creating a new recovery attempt, so a rejected rule does not leave a manifest blocking its correction. Existing recovery attempts still require their original transformation configuration.

PartialSync still selects its inclusive range against original source values, before transformations. Bookmarks and target range predicates remain unchanged. Use an untransformed boundary column so the source and target ranges remain comparable; hashing or masking that column does not translate the target range.

FastSync rejects transformations on an INCREMENTAL replication-key column before querying its maximum: resuming requires the original value in local state and recovery manifests, which would bypass the transformation boundary. Choose a non-sensitive, untransformed replication key or another replication method. Range arguments and dynamic-boundary results remain operational metadata and can appear in logs/recovery manifests; do not use sensitive values as boundaries.

Validation and rollout

pipelinewise validate checks source-side rule syntax, portable regexes, duplicate unconditional rules, and transformed INCREMENTAL keys without connecting to databases. import_config repeats those checks before discovery and validates fields against the Singer catalog. FastSync checks mapped column types and source capabilities at runtime; passing offline validation does not prove that an existing target is compatible. See PartialSync behaviour.

MySQL/MariaDB regex conditions require ICU/PCRE support. FastSync checks the export connection with a literal-only query before using regex conditions; unsupported engines fail before CSV creation. The result is reused only for that connection. Equality-only or unconditional rules do not run this probe.

Transformation changes affect newly extracted records only. To update historical target values, perform a controlled resync and verify that temporary files, staging tables, logs, and rejected records do not expose the original value.

Test deterministic output, NULL handling, nested paths, conditional misses, and schema changes before relying on a transformation as a security boundary.

Before upgrading to source-side execution, complete pending managed-Iceberg recovery for transformed streams with the previous version. Retained attempts from target-side execution are rejected because their staging may contain raw values. They block replication; they do not automatically restart from scratch. Do not delete recovery manifests to bypass this check. Include pending recovery in the deployment handover to the on-call operator.

The upgrade does not rewrite or remove historical staging/archive files. Treat pre-upgrade FastSync exports as potentially containing unmasked source values. Review access, retention, retained object versions, and rollback needs separately; do not assume a successful resync removes old files.

On the first representative bulk exports, compare source CPU, replica lag, and export duration with the previous baseline. Include wide columns and tables with many conditional rules; small development benchmarks do not establish production capacity.

Optional monitoring can count unexpected output shapes without logging values. For a column whose final, unconditional transformation is HASH:

SELECT COALESCE(COUNT_IF(
           "EMAIL" IS NOT NULL
           AND NOT REGEXP_LIKE("EMAIL", '[0-9a-f]{64}', 'c')
       ), 0) AS unexpected_hash_values
  FROM replicated_schema.customers;

This permits SQL NULL and checks lowercase hexadecimal SHA-256 shape only; it does not prove the correct input was hashed. Do not apply it unchanged to conditional hashes, prefix-preserving hashes, or columns with later rules. Keep deterministic transformation tests as the correctness check; do not restore the old UPDATE, which would transform values twice.