Blocking and Hashing PII in Fivetran: Column-Level Governance Before Data Lands

Most Fivetran governance conversations start too late. The pipeline is already running, the Salesforce connection has been syncing every column for nine months, and someone from legal asks whether contact.national_id is sitting in the warehouse. It is. It has been since the initial sync, it is in every downstream model, and it is in whatever backups your warehouse keeps.

Fivetran gives you two column-level controls that prevent this — blocking and hashing — plus a schema change policy that decides what happens when a source adds a column tomorrow. This tutorial covers how to use all three deliberately, what they cost you in re-syncs, and what evidence to keep so an audit takes an hour instead of a week.

The two controls, precisely

Blocking excludes a column from the sync. Fivetran still reads the row at the source, but the blocked column is dropped in the pipeline and never written to the destination. The column does not appear in the destination table at all.

Hashing replaces the value with a deterministic hash before it is written. The destination gets a stable pseudonymous value: the same input always produces the same hash within a connection, so you can still join, group and count distinct on it. You cannot read it, and you cannot reverse it.

The decision rule is simpler than it looks:

You need to...	Use
Never store the value anywhere downstream	Block
Join or deduplicate on the value, but never read it	Hash
Read the value for a legitimate, documented business use	Sync it, and control access in the warehouse

A worked example on a typical customers table:

• email — hash. Analytics joins on it constantly; nobody needs to read it in the warehouse.
• phone — hash if it is a join key for identity resolution, otherwise block.
• national_id, tax_id, passport_number — block. There is rarely an analytical use, and the compliance blast radius is large.
• date_of_birth — usually block, and ask the source system or a transformation to give you an age band instead.
• street_address — block; keep postal_code and country if geography matters for reporting.
• Free-text fields (notes, description, case_body) — the awkward ones. Hashing is useless because the text is the point, and blocking may break a real use case. Decide explicitly, and if you sync them, treat the whole table as sensitive downstream.

Do this before the initial sync

This is the part that actually matters operationally. Blocking and hashing are applied going forward. If a column has already synced in clear text, changing it to blocked or hashed does not retroactively scrub what is already in the destination — and in most cases Fivetran will require a re-sync of the table for the new setting to be consistently reflected.

So the order of operations for a new connection is:

1. Create the connection but do not start the initial sync. Fivetran pauses after the connection test so you can review the schema.
2. Open the Schema tab, walk the tables you are enabling, and set blocking/hashing per column.
3. Set the schema change handling policy (next section).
4. Then start the initial sync.

If you are remediating an existing connection instead, plan for three things: the setting change, a re-sync of the affected tables, and a clean-up of the already-landed data in the destination — including any downstream tables, clones, zero-copy shares and time-travel windows that contain it. Fivetran will not do that last part for you. In Snowflake, remember that DROP COLUMN does not remove the value from time-travel history; if the retention period matters to your obligation, you may need to rebuild the table and let the old one age out.

A re-sync also re-reads the source, which means the rows count as monthly active rows again. On a large table that is a real line on the invoice, so batch your remediation into one re-sync rather than five.

Schema change handling: the setting that quietly leaks data

Every Fivetran connection has a schema change handling policy with three options:

• Allow all — new schemas, tables and columns are synced automatically.
• Allow columns — new columns in already-synced tables are added automatically; new tables are not.
• Block all — nothing new syncs until a human enables it.

"Allow all" is the default on many connectors and it is the right choice for a warehouse full of harmless product telemetry. It is the wrong choice for Salesforce, HubSpot, Workday, Zendesk, a billing system, or any Postgres database an application team owns. In those systems a new column appears the moment someone adds a custom field, and Custom_SSN__c will land in your warehouse without a ticket, a review, or anyone noticing.

Our default for anything holding customer or employee data is Block all on new tables, with a weekly review of the pending schema changes, and column blocking/hashing applied as part of enabling anything new. It generates a little standing work. It is much less work than a breach notification.

You can see what is waiting via the API:

curl -s -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  "https://api.fivetran.com/v1/connections/<connection_id>/schemas" \
  | jq '.data.schemas'

The response shows each table and column with enabled, enabled_patch_settings (whether you are even allowed to change it), and the current hashed flag. That JSON is also the cleanest artefact to hand an auditor — see the last section.

Applying it: UI, API, Terraform

UI. Connection → Schema tab → expand a table → the per-column toggles. Blocked columns show as excluded; hashable columns get a hash toggle. Note that some columns are not blockable — primary keys and system columns Fivetran needs to sync correctly are locked, which enabled_patch_settings tells you in the API.

API. Patch a schema in one call:

curl -s -X PATCH -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  -H "Content-Type: application/json" \
  "https://api.fivetran.com/v1/connections/<connection_id>/schemas/<schema>/tables/<table>/columns/email" \
  -d '{"enabled": true, "hashed": true}'

And to block a column outright:

curl -s -X PATCH -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  -H "Content-Type: application/json" \
  "https://api.fivetran.com/v1/connections/<connection_id>/schemas/<schema>/tables/<table>/columns/national_id" \
  -d '{"enabled": false}'

Terraform. This is where it belongs long term, because the policy becomes reviewable in a pull request instead of living in one person's memory of what they clicked:

resource "fivetran_connection_schema_config" "salesforce_prod" {
  connection_id        = fivetran_connection.salesforce_prod.id
  schema_change_handling = "BLOCK_ALL"

  schema {
    name = "salesforce"

    table {
      name = "contact"

      column {
        name    = "email"
        enabled = true
        hashed  = true
      }

      column {
        name    = "national_id"
        enabled = false
      }

      column {
        name    = "birthdate"
        enabled = false
      }
    }
  }
}

Check the resource and attribute names against the provider version you have pinned — the schema config resource has been renamed across major versions of the Fivetran provider, and the connector/connection rename applies here too. Run terraform plan and confirm it reports no destructive change to the connection itself before you apply; you want a config patch, not a replacement.

If you already manage Fivetran in Terraform, import the existing schema config rather than declaring it fresh, for the same reason you import connections: a recreate means a re-sync means MAR.

Verifying it actually worked

Do not trust the toggle. Verify in the destination after the next sync.

-- 1. Blocked columns should not exist at all
SELECT column_name
FROM   information_schema.columns
WHERE  table_schema = 'SALESFORCE'
AND    table_name   = 'CONTACT'
AND    column_name IN ('NATIONAL_ID', 'BIRTHDATE', 'STREET_ADDRESS');
-- expect zero rows

-- 2. Hashed columns should look hashed, not like emails
SELECT email, COUNT(*) AS n
FROM   salesforce.contact
GROUP  BY email
ORDER  BY n DESC
LIMIT  5;
-- expect fixed-length opaque strings, no '@'

-- 3. Sanity-check that hashing is still deterministic enough to join
SELECT COUNT(DISTINCT c.email) AS contacts,
       COUNT(DISTINCT l.email) AS leads,
       COUNT(DISTINCT CASE WHEN l.email = c.email THEN c.email END) AS matched
FROM   salesforce.contact c
FULL OUTER JOIN salesforce.lead l ON l.email = c.email;

That third query is the one that catches the real trap: hashing is deterministic within a connection. Two different connections — say Salesforce and HubSpot — hash the same email to different values, so cross-source identity resolution on hashed columns will silently match nothing. If you need cross-source joins on a hashed key, you have two honest options: normalise and hash in a transformation layer with a salt you control, or keep the column clear in the warehouse and enforce access with column-level masking policies in Snowflake, BigQuery or Databricks.

Run the first two queries on a schedule and alert on them. A column that reappears is a schema change policy that slipped.

Where Fivetran's controls stop

Be honest with your stakeholders about the boundary, because "we hash PII in Fivetran" is often over-claimed:

• Blocking is not source-side redaction. The value is read from the source and dropped in transit. If your requirement is that data never leaves a network boundary at all, that is a Hybrid Deployment or private networking conversation, not a column toggle.
• Hashes are pseudonymous, not anonymous. Under GDPR, a hashed email is still personal data — it is reversible by dictionary attack for a low-entropy field like an email address or a phone number. Hashing reduces exposure; it does not take you out of scope.
• Free-text columns defeat both. A blocked national_id column means nothing if a rep pasted the number into case.description.
• Deletion requests still need warehouse-side work. Column controls govern what lands, not how you honour an erasure request across downstream models.

Column blocking and hashing are the cheapest, earliest control in the chain, and they should be paired with warehouse-side masking policies and access roles rather than replacing them.

A checklist you can actually run

1. Inventory every connection and export its schema JSON via the API.
2. Tag each column: block / hash / sync-and-mask, with the reason recorded.
3. Set schema change handling to Block all on every source that holds customer or employee data.
4. Apply the settings before the initial sync on new connections; batch remediation into a single re-sync on existing ones.
5. Move the whole configuration into Terraform so changes are reviewed.
6. Add the two verification queries to your monitoring.
7. Store the dated schema JSON export as audit evidence, quarterly.

That last point is worth more than it sounds. When someone asks "what PII is in the warehouse and who decided that?", a versioned Terraform file plus a dated API export answers it in minutes.

---

Need this done properly across a fleet of connections? SyncSpur configures Fivetran column governance, schema change policies and Terraform-managed pipeline config for enterprises with real compliance obligations — including remediating accounts where the data already landed. Get in touch and tell us which sources are worrying you.