Redact a column

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.

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 before you add the next.

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

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

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

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

Check the result in Query

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

SELECT * FROM customers_shared LIMIT 20

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

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).
• 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 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. The assistant follows this as guidance, and it can still read the source.