# How to Trace Personal Data (PII) Through Your SQL Models

> Tag a column as personal data and see every table it flows into, and which columns are hashed or masked. Free, runs in your browser, no database needed.

Page: https://datautility.in/blog/trace-pii-through-sql-lineage
This is the Markdown copy of the page. The tool itself runs in the browser: your files are processed on your own device and are not uploaded.

Published 2026-11-18 · Analyze & protect · pii data lineage, trace pii through sql, column level lineage pii, find personal data in dbt models, sensitive data lineage, where does email go sql, pii tracking sql

When someone asks "where does a customer's email end up?", most teams answer by searching the code and hoping. Column-level lineage gives a better answer, because it already knows which output columns are built from which source columns. Mark a column as personal data and you can follow it through every model. This is how that works in the SQL lineage analyzer, with a real example of what it reports.

Open the tool: [SQL Lineage Analyzer](https://datautility.in/sql-lineage-analyzer)

## Why column lineage is the right tool

Personal data spreads by copying. A raw email column is selected into staging, lowercased in a dimension, joined into a report, and sometimes hashed for marketing. A text search finds the word email, but not the dimension column that was renamed to contact. Column lineage follows the actual data flow, so a rename does not hide it.

## How the tool finds personal columns

The SQL lineage analyzer looks at column names. Names that look like email, phone, mobile, SSN, passport, date of birth, address, salary, IBAN, card, first name, last name, full name, national ID, Aadhaar or IP address are tagged automatically. Only columns that start in the project count: a column that is built from another column is covered by following the first one.

That is a guess from names, nothing more. A column called notes may hold personal data and a column called address_type does not. So every tag can be removed, and you can add your own: open the Columns tab, click a column and press **Tag as PII**.

## An example

A small dbt project: raw customers feed a staging model, an intermediate model, a dimension, a hashed marketing audience and a count of emails. The PII tab reports:

```
2 tagged columns: raw.raw_customers.email, raw.raw_customers.first_name

stg_customers        email       copied as is, 1 step
                     first_name  copied as is, 1 step
int_customer_orders  email       copied as is, 2 steps
dim_customers        email       derived (lower), 3 steps
marketing_audience   email_hash  protected by md5(), 2 steps
                     first_name  copied as is, 2 steps
email_count          emails      protected by count(), 2 steps
```

Read it from the top. Five columns in four tables hold the data in the clear, including dim_customers.email, where the lower() call changed the text but not the fact that it is an email address. Two columns are protected, so the trail stops there.

## What counts as protected

A column is marked protected when it is produced by a hashing, masking or encryption style function (md5, sha, hash, mask, encrypt, anonymize, redact, tokenize, pseudonymize, obfuscate) or by count(). The trace does not continue past it, because the person can no longer be read from the value. Everything else, including lower(), trim() and concat(), still exposes the data and is followed.

Be careful with this rule. A function with an unusual name is not recognised, and min(email) or max(email) still returns an email address, so they are shown as exposed. Hashing without a salt can also be reversed for common values. Protected means "the tool recognised a protective function", not "proven safe".

## Catch new exposure before it ships

Edit a model so it selects a personal column that it did not select before, and the impact card under the graph reports it as a risk: personal data from the source column now reaches that column. That is a cheap review check for a pull request. See [edit your SQL and see what breaks](https://datautility.in/blog/edit-sql-see-what-breaks-what-if-impact-analysis).

## Export and share

The PII tab copies the result as Markdown for a ticket or an audit note, and downloads it as CSV with the table, column, where it comes from, the number of steps and whether it is protected.

## Limits

- Detection is by column name. Review the tags.

- It sees only the SQL you loaded, not the data itself, other repositories, spreadsheets or dashboards.

- Columns used only in joins or filters are not followed.

- A map of where personal data flows is a good first inventory. It does not replace a data catalog, access controls or legal advice.

Once you know where the data goes, [Data Masking](https://datautility.in/data-masking) can hide it in a file before you share it.

## How to use the SQL Lineage Analyzer: step by step

1. Load your SQL or dbt project in the SQL lineage analyzer.
2. Open the PII tab, or click a column in the Columns tab and press Tag as PII.
3. Read which tables hold the data and which columns are protected.
4. Copy the Markdown summary or download the CSV.

## When to use the SQL Lineage Analyzer

- Find every table that holds an email column
- Review a model that touches personal data
- Prepare an inventory before a privacy review
- Check that a hashed column really is hashed

## Example

Input:

```
models/ folder: raw schema, staging views, marts, reports (7 .sql files)
```

Output:

```
A graph of 10 tables and views, 40 column links and the order to run the files
```

## Frequently asked questions

### How do I find where an email column is used in SQL?
Load the project in the SQL lineage analyzer and open the PII tab. Columns named like an email are tagged, and every table that holds data built from them is listed.

### Does it know which columns are really personal data?
No. It guesses from column names. You can remove wrong tags and add your own with Tag as PII in the Columns tab.

### What does protected mean?
The column is produced by a hashing, masking or encryption style function, or by count(). The trace stops there. It is a recognised function, not proof of safety.

### Is my SQL uploaded?
No. The files are read and analysed in your browser.

### Can I export the result?
Yes. Copy it as Markdown for a ticket or download it as a CSV.

Tip: Start by removing the wrong tags, then add the columns the names missed. A short, correct list is worth more than a long guessed one.

## Related tool pages

- [dbt Lineage Viewer for Your SQL Models](https://datautility.in/dbt-lineage-viewer): Drop your dbt models folder and see the lineage graph from sources through staging to marts, with column links. No dbt run, no warehouse. Free, private.
- [SQL Column Lineage Tool](https://datautility.in/sql-column-lineage-tool): See which source columns build every column of a view or table across many SQL files. Trace to the raw table or forward to dependants. Free, in your browser.
- [SQL File Dependency Analyzer](https://datautility.in/sql-file-dependency-analyzer): Find out which SQL file must run before which, from the tables each file creates and reads. Get a run order, circular dependencies and tables nothing uses.
- [SQL Impact Analysis: What Breaks If a Table Changes](https://datautility.in/sql-impact-analysis): Pick a table and see everything downstream that depends on it, and every upstream source it is built from. Works across a whole folder of SQL files, free.
- [SQL File Lineage Tool](https://datautility.in/sql-file-lineage): Free SQL file lineage tool: drop a folder of .sql files and see which file feeds which table, column by column. Nothing is uploaded.
- [SQL Query Visualizer](https://datautility.in/sql-query-visualizer): Free SQL query visualizer: paste a query or drop SQL files and see tables, joins and columns as a clear diagram. Runs in your browser, no upload.

## Also try

- [Edit SQL and see what breaks](https://datautility.in/blog/edit-sql-see-what-breaks-what-if-impact-analysis)
- [What is column-level lineage](https://datautility.in/blog/what-is-column-level-data-lineage-with-sql-examples)
- [Mask emails, phones and PII](https://datautility.in/blog/data-masking-hide-emails-phones-pii)

## More guides

- [Data Profiler: Check Data Quality Before You Use It](https://datautility.in/blog/data-profiler-check-data-quality)
- [Compare Two CSV or Excel Files: Find Added, Removed and Changed Rows](https://datautility.in/blog/compare-two-csv-files-find-differences)
- [Data Masking: Hide Emails, Phones and Names Before Sharing](https://datautility.in/blog/data-masking-hide-emails-phones-pii)
