> Documentation index: https://docs.beetl.io/llms.txt

# Redact a column

> Dropping, masking or hashing personal data in a pipeline's output.

Source: https://docs.beetl.io/pipelines/redact-columns/
Last updated: 2026-10-03

This recipe writes a copy of a dataset with its personal data, such as names, email addresses,
phone numbers and customer IDs, dropped, masked, hashed or coarsened. Dashboards, exports and
questions to the assistant can then use the copy instead of the source.

> **Redaction changes what the destination contains, not who can read the source.**
> The source dataset keeps the raw values. Every user in the tenant and every MCP key can query
> it, and the assistant can read any dataset in the tenant, including this one. Point people,
> dashboards and prompts at the redacted dataset, knowing the raw data stays one query away for
> anyone in the tenant.

## When you need this

* A dashboard or export goes to people who should not see customer contact details.
* A partner needs order data that can be joined per customer without knowing who the customer is.
* You want the assistant to answer most questions from a dataset without personal data in it.
  Query rows the assistant reads are sent to the model, so the dataset it reads matters.

## The pipeline

The examples read a source with the alias `customers_raw` and write `customers_shared`. Each step
is its own stage, and each stage reads the one before it by name. Preview every stage in the
[pipeline builder](https://docs.beetl.io/pipelines/builder) before you add the next.

    ## Allowlist the columns [#allowlist-the-columns]

    Start with an explicit list of the columns the output needs, plus any raw column a later stage
    derives something from. A column added to the source later then stays out of the destination
    until you add it here.

    ```sql
    -- stage: selected
    SELECT
      customer_id,
      email,
      phone,
      birth_date,
      postcode,
      country,
      signed_up_at,
      lifetime_value
    FROM customers_raw
    ```

    `full_name` is not in the list, so it is gone from this point on.

    ## Replace identifiers with salted hashes [#replace-identifiers-with-salted-hashes]

    A hash gives each customer a stable key that joins across datasets and runs without showing the
    underlying value. `sha256` returns binary, so `encode(..., 'hex')` turns it into a 64-character
    string. The salt comes from a pipeline variable named `hash_salt` (see
    [Template variables](https://docs.beetl.io/pipelines/template-variables)).

    ```sql
    -- stage: keyed
    SELECT
      *,
      encode(sha256('{{ vars.hash_salt }}' || CAST(customer_id AS VARCHAR)), 'hex') AS customer_key,
      encode(sha256('{{ vars.hash_salt }}' || lower(trim(email))), 'hex') AS email_key
    FROM selected
    ```

    Normalize a value before you hash it. `Anna@Example.com` and `anna@example.com` produce different
    hashes unless you apply `lower` and `trim` first. For another algorithm, use
    `digest(value, 'sha512')` in place of `sha256(value)`.

    ## Generalize dates and locations [#generalize-dates-and-locations]

    Exact birth dates, signup timestamps and full postcodes can identify a person when combined.
    Coarser values are usually enough for reporting.

    ```sql
    -- stage: generalized
    SELECT
      *,
      CAST(date_trunc('month', signed_up_at) AS DATE) AS signup_month,
      left(postcode, 2) AS postcode_area,
      extract(YEAR FROM CURRENT_DATE) - extract(YEAR FROM birth_date)
        - CASE WHEN to_char(CURRENT_DATE, '%m%d') < to_char(birth_date, '%m%d') THEN 1 ELSE 0 END
        AS age_years
    FROM keyed
    ```

    ## Mask what stays and write the output [#mask-what-stays-and-write-the-output]

    The final stage chooses the destination's columns. Masked values keep enough to be useful,
    such as an email domain or the last digits of a phone number, and every raw column is left out.

    ```sql
    -- stage: redacted
    SELECT
      customer_key,
      email_key,
      '***@' || split_part(email, '@', 2) AS email_masked,
      '****' || right(regexp_replace(phone, '[^0-9]', '', 'g'), 4) AS phone_last4,
      country,
      postcode_area,
      signup_month,
      CASE
        WHEN age_years IS NULL THEN NULL
        WHEN age_years < 25 THEN 'under 25'
        WHEN age_years < 40 THEN '25-39'
        WHEN age_years < 60 THEN '40-59'
        ELSE '60 and over'
      END AS age_band,
      lifetime_value
    FROM generalized
    ```

## Treat the salt as configuration

Pipeline variables are stored with the pipeline, so anyone in the tenant who opens it can read the
salt, and so can the assistant and MCP clients. That is fine here: the salt stops
someone who only has the shared dataset from hashing a list of known email addresses to find
matches, and inside the tenant the raw source is available anyway.

Treat hashed values as pseudonymous. Anyone with the salt can recompute them, and
short values such as phone numbers or sequential IDs can be found by trying every possibility.
Data protection rules such as the GDPR generally still treat such data as personal data.

Use only letters and digits in the salt. The value goes into the SQL text as written, so a quote
character would end the string literal.

## Write mode

Use **Overwrite, full table** unless the source is too large to reprocess. Every run then applies
the current redaction rules to every row, and rows deleted from the source disappear from the
output. With Append or Merge, rows written earlier keep the rules that applied when they were
written. For a partitioned source, overwrite by partition instead. See
[Write modes and partitioning](https://docs.beetl.io/pipelines/write-modes-and-partitioning).

## Check the result in Query

Look at a sample of the output and at its schema on Home before anyone else uses it:

```sql
SELECT * FROM customers_shared LIMIT 20
```

Then confirm that the keys are unique per customer and that no raw address slipped through:

```sql
SELECT
  count(*) AS row_count,
  count(DISTINCT customer_key) AS distinct_keys,
  count(*) FILTER (WHERE email_masked NOT LIKE '***@%') AS unmasked_emails
FROM customers_shared
```

`distinct_keys` should match the number of distinct `customer_id` values in the source, and
`unmasked_emails` should be 0.

## Pitfalls

* Concatenating with `||` keeps a `NULL` as `NULL`, so a missing email gets no key. `concat()`
  treats `NULL` as an empty string, which would give every missing email the same hash of the
  salt alone.
* Changing the salt changes every key, and joins against older outputs stop matching.
* `left`, `TRIM` and `split_part` need text, so cast numeric columns to `VARCHAR` first. A postcode
  read from CSV as a number has already lost its leading zeros.
* Removing a column from the final stage changes the destination's schema, so the run fails and
  the old data stays in place. To stop publishing a column, write to a new destination and delete
  the old dataset, whose files stay in storage (see
  [Retention and deletion](https://docs.beetl.io/security/retention-and-deletion)).
* An MCP key is scoped to read or write, not to datasets. To give redacted data to a tool outside
  Beetl, hand over an [export](https://docs.beetl.io/query/export) instead of a key.
* To steer the assistant toward the redacted dataset, mention it in your message or ask a
  TenantAdmin to name it in the [assistant instructions](https://docs.beetl.io/concepts/ai-settings#assistant-instructions).
  The assistant follows this as guidance, and it can still read the source.