Skip to content

Blocking, hashing and filtering what a pipeline syncs

A pipeline from a PostgreSQL source can leave a column out (Block), sync it as a salted SHA-256 hash (Hash), and sync only the rows that meet a row filter. All three happen in the pipeline as it reads each change, never in your database: Reyansh changes nothing at your source (it only reads).

A blocked or hashed value never reaches your destination, our database, a log or an error. That holds for every write: the first copy, the stream, a replay after a restart, and a re-copy. One thing to know: a filter on a hashed column judges its clear values, so which rows it lets through, and the counts of rows it leaves out, say which rows match the values you chose (see Hash a column).

  • New pipeline: on step 5, Choose data, press a table’s name in the list (or move to it and press Enter) to open Columns and filter for schema.table. The wizard reads that table’s columns by testing the connection again for it, and shows each column’s type, whether it is part of the key, and the operators a filter may use on it; each column is Sync, Block or Hash. Done, or Escape, returns to the list, whose Columns cell then says how many of each. A column blocked here is never copied and never created at the destination. Going back to change the pipeline’s name or its destination keeps these choices, and so does trying again after a refused Create pipeline. Another source (another connection string, even to the same database, another saved source, or other details) or another way of reading it lists its own tables: its first test replaces the tables picked and these choices, and Choose data then says so. Typing the same connection string again is the same source, and keeps them.
  • An existing pipeline: on the pipeline’s Schema tab, press a table’s name (or its Columns and filter): its drawer lists each column with Sync, Block and Hash, and the table’s filter. Before the pipeline has read a table, only the columns you set are listed: name one to block or hash it, and the pipeline checks the name when it starts.
  • API: GET and PUT /v1/pipelines/{id}/tables/{table_id}/columns, PUT /v1/pipelines/{id}/tables/{table_id}/filter, and columns and filter on each table of POST /v1/pipelines and POST /v1/pipelines/{id}/tables. See the API reference.

Every setting is applied when the table next starts: within a minute while the pipeline runs, or when you next start it. The page says so beside the setting until it is applied, and the pipeline’s history tells each change: “A change to how a table’s columns are synced was asked for” when you save it, and “A column’s treatment was changed at the destination” when it is done.

A blocked column is never written to the destination. A key column cannot be blocked (a key is always synced as it is), and neither can a column the table’s filter uses, until the filter stops using it.

On a table that already holds this pipeline’s rows, you choose what happens to the values already there:

Choice Snowflake PostgreSQL destination
Set them to NULL (the default) every row’s value is cleared, the column stays the same, in every row of your table, rows you wrote yourself included; they are never filled again
Drop the column the column is dropped not offered: the page shows the ALTER TABLE … DROP COLUMN statement for you to run, because the table is yours
Keep them the values stay as they are and are never updated again: anyone who can read the table can still read them the same

Where a blocked value goes before it is discarded. PostgreSQL sends every column a publication covers, so a blocked column’s bytes still leave your database and are discarded as each change is read. To keep them in your database, either take the table out of the publication and add it back with a column list that leaves the column out, naming it ONLY both times (so no table that inherits from it is taken out or added with it) and keeping its row filter, in one transaction (PostgreSQL 15.2 or later: 15.0 and 15.1 sent unlisted columns in updates and deletes), or grant the replication user only the columns it may read. Check the table’s replica identity first: a column list on a table with REPLICA IDENTITY FULL makes PostgreSQL refuse the table’s own UPDATEs and DELETEs. Reyansh never runs either statement.

Unblocking (setting the column back to Sync) is an owner’s, and never an API key’s: it delivers the values the block was protecting. You choose Fill the rows already there from your database (the default: the column is read once for every row; the other tables pause while it runs, and it counts as copy work on your usage) or Only new changes (the rows keep what they hold until the column itself changes at your database).

A hashed column lands as the Base64 of a SHA-256 of the value’s text followed by your destination’s salt:

Base64(SHA-256(UTF-8(text(value)) || UTF-8(salt)))

This is Fivetran’s construction, so a text column hashes to the same value as it does there with the same salt. One difference: a NULL stays NULL, where Fivetran hashes a NULL as an empty string. A key column cannot be hashed. A filter may use a hashed column: it judges the clear value, before hashing. So the filter tells anyone who can see the destination, or the counts in the pipeline’s history, which rows’ clear values match the filter’s values: set one only where that is fine to know.

On a table that already holds this pipeline’s rows, the values already there are cleared first, in one statement, so a column never holds clear values and hashes together. Then you choose Fill them with hashes (the default: the column is read from your database once; it counts as copy work) or Leave them NULL (only new changes are hashed).

  • Snowflake: a hashed column is VARCHAR. A column of another type is cleared, then dropped and added again as VARCHAR, so it moves to the end of the table.
  • PostgreSQL destination: the table is yours, so its column keeps its type. A column that cannot hold a hash (44 characters of text) stops the table with the statement that changes it, and nothing of the table is written until you have run it and resumed it.

Unhashing (back to Sync) is an owner’s. The hashes are cleared, the column returns to its own type, and you choose to fill the clear values from your database or leave them NULL.

Each destination has one salt, shared by every pipeline that writes it, so equal values hash alike across them. It is made the first time a column is hashed into the destination, or when an owner first reveals or previews it. Once a column has used it, it never changes: a leaked salt’s remedy is a new destination, and a copy again.

On the destination’s Hashing salt pane, an owner can:

  • Reveal the salt, with the construction and a recipe for Snowflake and for PostgreSQL that reproduces a hash over a text column. Every reveal is recorded in the salt’s history, with who did it, and only Reyansh’s own page can start one: a link or a form on another site cannot. The salt is never sent to an API key, and never stored by our request log. A reveal sent again (the same request, after its answer was lost) shows the salt that reveal recorded and records nothing twice; if the salt was changed in between, it shows nothing, and revealing again is recorded.
  • Check the hash a value would get without revealing the salt. The value is never kept.
  • Set the salt while no column has used it: for example, paste the salt from Fivetran’s Data Security Settings, so your text columns hash the same as there. 16 to 1,024 bytes, with no NUL neither $$ nor $s$, and not ending in $ or $s (the recipes quote it with them).

A pipeline that hashes a column and was given no salt refuses to start and writes nothing.

Snowflake keeps a table’s earlier versions for its Time Travel retention (1 day unless your account says otherwise) and then 7 days of Fail-safe: a value cleared or dropped is readable there until those end. To shorten that, set the destination’s Time Travel to 0 days before you block or hash a column. A PostgreSQL destination keeps old row versions until VACUUM reclaims them, and in its write-ahead log and its backups. Values in rows deleted at your database, and in earlier versions a history-mode table keeps, can never be filled again.

A table’s filter is at most 10 conditions, joined by one all (AND) or any (OR), as Fivetran’s is. Each condition is a column, an operator its type takes, and a literal:

Column type Operators Literal
smallint, integer, bigint is, is not, greater than, less than, at least, at most, is one of, is empty, is not empty a whole number in the 64-bit range
numeric the same a plain decimal, at most 50 characters; not NaN or infinity
real, double precision the same a decimal or exponent number the column can hold, finite and, unless it is zero, not so small it would read as zero
boolean is, is not, is empty, is not empty true or false
date is, is not, greater than, less than, at least, at most, is empty, is not empty YYYY-MM-DD, years 1000 to 9999
timestamp the same YYYY-MM-DDTHH:MM:SS[.ffffff], as wall-clock time
timestamp with time zone the same YYYY-MM-DDTHH:MM:SS[.ffffff]Z, in UTC
text types, uuid, an enum every operator above, plus contains and starts with text, at most 1,000 characters; an “is one of” list at most 10,000 values in all
  • Text is compared case by case, in the byte order of its UTF-8 text, ignoring trailing spaces except for “contains” and “starts with”. An enum compares as its label’s text, not in the enum’s order. Greater than, less than, at least and at most are offered on text only when your database’s encoding is UTF8, LATIN1 or SQL_ASCII, whose byte order survives the conversion; on any other encoding they are refused, and the create form does not offer them.
  • NULL follows SQL’s rule: a row whose column is NULL meets no condition except “is empty”, so “is not” leaves it out too.
  • A filter may not use a blocked column. Any other type is refused, naming the column.
  • Filter values are stored with your pipeline’s settings, and for a day with each request that set them or answered them (the filter’s change, a table’s create or add, and a column change, whose answer shows the table’s filter), so a retry is answered rather than repeated; then deleted. Do not put personal data in them. A refusal of a value names its condition by number, never the value.

Both readings of the filter must agree: the first copy pushes it down to your database as a WHERE, and the pipeline judges every copied row again with the stream’s own reading. A disagreement, or a value the filter cannot judge, stops the table rather than syncing it unfiltered.

Setting or changing a filter on a table that holds rows

Section titled “Setting or changing a filter on a table that holds rows”

A filter set before a table’s first copy is simply applied by that copy. On a table that may already hold this pipeline’s rows, a new, changed or removed filter copies the table again from your database, behind a checkbox, and counts as copy work on your usage (the other tables pause while it copies). Creating a filter is a member’s; changing or removing one is an owner’s, because it may let in rows the filter kept out.

What happens to the rows that no longer match follows the table’s mode: in Snowflake they are marked deleted (_REYANSH_DELETED_AT) in soft delete, removed in live, and their current version closed in history; a table kept as a log of changes keeps every row already written. On a PostgreSQL table with a primary key, kept live or in soft delete, you choose, with no default:

  • Keep them. Kept live, they are no longer kept up to date; in soft delete, its copy marks them deleted (_reyansh_deleted_at) all the same. Kept live, one is removed when it next changes at your database and still does not match.
  • Remove them: every row of the table that does not match the new filter, judged on the table’s own values with the same condition, rows Reyansh did not write included. They are counted first, and the history says “N rows … were removed after its copy (M were counted first)”. They are kept instead (in soft delete, marked deleted by its copy), and the history says why, when the filter uses a column this pipeline hashes, a column whose type in your table differs from your database’s, or the role may not delete from the table.

Removing a filter is the same, with no rows outside it.

A table with no primary key is not asked: its copy keeps only the rows that match the new filter, and the rows already there that do not are marked deleted in soft delete, removed in live, as the table’s mode does with a delete (neither keep nor remove applies to it).

Under REPLICA IDENTITY DEFAULT PostgreSQL leaves an unchanged large value (one stored apart, “TOAST”) out of an UPDATE, and sends only the key of a deleted row. So:

  • A row that enters the filter on such an update cannot be written whole. Until Reyansh can read that value from your database (a later release), the table stops, and resuming it copies the table again, free of charge. When you set a filter on such a table, the form asks you to choose, with neither preselected: I will run this on my source (the statement it shows), or Pause the table and copy it again, free of charge, whenever this happens.
  • A change that moves a row out of the filter is written as a delete by key, which may find nothing. The history counts these.
  • The statement the form shows, which a table’s drawer on the pipeline’s Schema tab shows too (in its columns, and for a stopped table in what it says), run on your source as the table’s owner, makes every change carry the whole old row and avoids both. It gives the table and each of its partitions REPLICA IDENTITY FULL, in one transaction, where PostgreSQL allows it. Where PostgreSQL would then refuse the table’s UPDATE and DELETE (a publication that sends them has a column list for it, or, on PostgreSQL 18 or later, does not send one of its generated columns), it changes nothing and says what must change first. Do not give such a table REPLICA IDENTITY FULL any other way.

The history tells what each table’s filter did, at most every ten minutes: “Changes left out by a table’s filter”, with the changes left out, the deletes by key, the rows that left and the rows that entered.

The table says What to do
Its filter cannot judge a column (its type changed at your database) change the filter so it can read the column, then resume
A filter reached it while it was being copied open Columns and filter and answer the filter’s change: the table is copied again under it
The destination’s own record says another pipeline keeps a column otherwise answer on the table: keep the columns as the destination holds them, or clear them and sync them as this pipeline is set to; for another pipeline’s row filter, confirm a copy again under this one’s
Reyansh cannot change a column itself (the role cannot, or the type must change) run the statement shown on your destination, then resume
A column this pipeline blocks or hashes became part of the key set it back to Sync, then resume
A column of a table with no primary key that holds rows was set to sync another way (a setting saved before Reyansh refused it) set the column back to how it is synced now, then resume; nothing of the table was written meanwhile
A row entered the filter with a value left out, the filter needs a value left out, or a table kept as a log of changes met a change without its whole old row run the statement the pipeline’s page shows in the table’s drawer on the Schema tab, on your source as the table’s owner, so it does not happen again (it gives the table REPLICA IDENTITY FULL where PostgreSQL allows it, and otherwise changes nothing and says what must change first), then resume: the table is copied again, free of charge
Our records and the destination disagree, or our two readings of the filter disagreed a fault of ours, and we have been told; nothing of the table is written meanwhile

No new price. Changes a filter leaves out are read (they are part of the change log) and write nothing. A fill or a re-copy you ask for is copy work, billed as a re-sync is. A re-copy a stop forces, which you did not ask for, is recorded and never charged, and a run that fails is free.

Masking, encryption, renaming a field, hashing a key column, rotating a salt, and a filter change that copies only the difference. Columns and filters are offered for a PostgreSQL source.

Blocking, hashing or un-hashing a column of a table with no primary key once it holds rows. Such a table’s rows are each identified by a hash of all of their values, so the change would give every row already in your destination a new identity: a later update or delete at your database would no longer find its row, and an identity computed from the value you are hiding would still reveal it. Reyansh refuses the setting (and never fills such a table), and says so on the column. Set it before the table’s first copy, when it holds no rows, or give the table a primary key at your database: with a key, its columns can be blocked or hashed like any other table’s.