Modelling Sensitive Data: Designing for Least Privilege
Sensitive data requires a slightly different modelling approach compared to the classic data modelling we are used to. When it comes to data like employee compensation or personal information, its security should be part of the architecture and not something to be considered once the project is complete.
When modelling sensitive data, we don’t want to make it completely inaccessible. This is where the principle of least privilege becomes particularly important: only the appropriate consumers have access to the information they actually need.
Assess the sensitivity levels
Understanding what the data will be used for will be a deciding factor when choosing the appropriate architecture for it.
I would start by assessing the sensitivity of the data and separating highly sensitive attributes from more general analytical information. This allows us to create purpose-specific datasets that expose only what is required for a specific use case. For example:
employee
├── employee_id
├── department
├── job_level
└── employment_status
employee_compensation
├── employee_id
├── salary
└── bonus
compensation_by_department
├── department
├── job_level
├── employee_count
└── median_salary
This is an example of data minimisation by design: expose the minimum information necessary to answer the business question.
Make sensitive models obvious
Anyone looking at a dbt project should be able to understand and recognise which models contain sensitive information and are not appropriate for wide analytical access.
One way to do it is to have a separate area for restricted models. The exact folder structure is individual to each project, but the principle is important: sensitive models should be clearly identifiable and consistently organised.
For example:
models/
├── staging/
│ ├── hibob/
│ └── finance/
│
├── intermediate/
│ └── people/
│
├── core/
│ └── people/
│ ├── employees.sql
│ ├── jobs.sql
│ └── teams.sql
│
├── restricted/
│ └── compensation/
│ └── employee_compensation.sql
I would also utilise dbt descriptions and tags to make the sensitivity of a model more explicit. For example, a model could be tagged with restricted.
It is important to remember that the dbt project structure itself does not provide the security boundary. The actual access boundary is defined and enforced by the underlying data platform. Warehouse roles and permissions determine who can access which schemas and objects, while dbt helps us organise and document the models and supports governance.
Masking can also be a useful control, but I would not rely on it as a primary strategy.
Use least privilege and audit access
Least privilege means giving a user the minimum access required to perform their task.
For sensitive data, I would want access to be attributable to a named identity wherever possible. The warehouse's query and access logs can provide information such as:
who accessed the data
when it was accessed
which object was accessed
which role was used
potentially which query or application initiated the access
This information can then be modelled into an audit layer that supports monitoring and investigation.
Focus on purpose-specific models
Well-designed marts that are able to answer business questions effectively are essentially a controlled analytical interface that is secure and does not expose sensitive data unnecessarily. The underlying data used to build them may be sensitive, but users only see the information they need to see.
For example, a stakeholder will not need access to an individual employee's salary to answer questions like:
How many employees are in each department?
What is the median compensation by job level?
Instead of giving someone access to the employee_compensation table, providing access to a purpose-specific mart model that contains only the information they need is a more appropriate approach.
Be explicit about the grain
When presenting stakeholders with aggregated data, it is especially important to be clear about what one row of data represents. Being clear about what one row of data means in the model helps avoid unnecessary duplication and confusion.
This becomes even more important with sensitive data because consumers may not have access to the underlying employee-level records to independently validate the results.
It is also important to remember that aggregation does not automatically make sensitive data anonymous. For example, if a department contains only two employees, an aggregated salary figure may still reveal information about individuals.
Disclosure controls, such as suppressing results for populations below an agreed threshold, can therefore be considered for particularly sensitive datasets. These rules should be agreed with the data owners.
History is important
Depending on the source and requirements, the temporal semantics need to be decided on early in the architectural design to avoid disrupting downstream reporting.
If the sensitive data is expected to have more than one state, the approach to preserving historical accuracy needs to be decided appropriately based on the pattern, tested, and made as explicit as possible. Therefore, I would consider tests covering structural integrity, anomalies, business rules, and reconciliation where appropriate.
For business-critical analytical models, I would consider using dbt contracts to make the expected model structure explicit.