r/dataengineering • u/Adrien0623 • 6d ago
Discussion Ingestion of sensitive data fields
Hello,
In some industries, we have to deal with sensitive data fields, whether it's PII (personally identifiable information) or other types of confidential data (e.g. investment orders, tax classes, medical records, etc.).
So far, in the European companies I've worked for, I've seen different approaches to this problem, despite all being subject to the same regulations. I've also tried reading some online content to get different points of view, but the approaches seem to vary quite a bit. These are the main options I've found or come across:
Import everything into the datalake/bronze layer, then only provide access to the datamart/silver/gold layers, which contain only anonymized or aggregated data.
Skip ingesting sensitive data fields unless they are strictly needed for downstream transformations.
Mask or hash sensitive data values at ingestion.
My take on these options is:
- Option 1 feels like the most natural approach because it fully replicates the source data. However, it also brings the highest compliance risks, as sensitive data is stored in multiple locations, increasing the attack surface and the number of places where access needs to be controlled.
- Option 2 is the strictest and potentially the most compliant approach, but it means our datalake/bronze layer is no longer a full replication of its data sources. If some of the excluded fields are needed later, we might have lost access to historical data. The same applies to option 3 when masking is used.
- With option 3, hashing preserves the ability to perform joins and unique/distinct operations. However, apparently, some regulators still consider hashed PII to be PII if the hashing function is injective (i.e. each output value maps to a single input value). Even though the original PII values are no longer available, we can still link an individual across multiple records.
Overall, I think the right approach also depends on whether the people working with the data have a way to temporarily access the source systems directly. If they do, there may be no need to store sensitive data in the datalake/bronze layer. If they don't, keeping it there provides a way to access it when needed without having to configure privileged access to the source systems.
At my current company, there is quite a bit of disagreement on this topic. My manager is in favor of option 1, based on what he has seen in his previous experience, while the security/risk/data protection teams lean more towards option 2, arguing that sensitive data should only be stored where there is a valid business purpose and with strict access permissions.
If you've worked in an industry with regulations around sensitive data, what approach did your company take? I guess it is very different from one company and country to another but I'm curious about what approach you use(d) and if there are other alternatives to these 3 options.
Thanks!
12
u/MadT3acher Lead Data Engineer 6d ago
Work with PII data in a multinational company. The answer for us was twofold: work council country buy in and just internal agreement for the people accessing the data.
Also, because of the ever changing nature of the data we had a very specific replication of the entire bronze layer once a week. It still works apparently.
4
u/Bingo-heeler 6d ago
I like to pretend its my data that I am handling (in some jobs it literally was).
On principle I want as little PII (and data in general) as possible. If you don't need it, why would you want it in the first place?
Add in privacy audits and security concerns, it's just not worth it. And then if we got hacked I gotta be in some MIM call checking if I let PII get exposed? Fuck that noise.
10
u/jupacaluba 6d ago edited 6d ago
Option 1 is definitely a NO go. If you’re ingesting the data as raw as possible, then you need at minimum to mask it and restrict the un-masking to a specific user group.
It’s crazy that your manager think it’s ok having plain names or personal information laying out in the open in a database.
In any case, you should never expose the hashed values (if that is the approach), so tables need to be tightly controlled and if some sort of metric is to be developed, make sure it’s aggregated on something else beforehand. Be sure to understand the data, sometimes a name is not needed to infer that a specific record belongs to someone…
3
u/datarbeiter 6d ago
Replace sensitive values with lookup keys at ingestion, simultaneously creating or updating a separate sensitive key-value data store. This way you only have a single place of sensitive data to secure access to.
I view your option 2 is a layer on top in general, and what I would prefer with any data set: unless you have a proven downstream need for a piece of data to be replicated to the lake, it's not getting replicated. "What if we need it in the future" is not a proven case.
2
u/tecedu 6d ago
I used to do 1, just because it used to all internal people accessing it and all and realistically if 80% of the company can access the data in their excel sheets then so should we be able to use it as well.
With AI game has changed, have our own internal ML libraries which you can train on the prod data, use it to generate synthentic data, so like option 3 without it being determinstic hashing, so your data is non prod is sitll unique-ish, follows prod paradigms and can also allow you to run locally or in pipelines to generate test data.
Unless your data is more than 1TiB which most personal data isnt, its super super simple and quick way to do it.
2
u/SearchAtlantis Lead Data Engineer 6d ago
We go with 1, but that's because those sensitive fields are necessary and we can't do without them. But all users have a need for the information (think care-management in healthcare setting).
We are working on a managed export but it's basically still option 1 - with aggregation rules and additional anonymization layers. Sensitive conditions are masked per informaticist direction, and we've added both generalized cell size and k-anonymity constraints.
1
1
u/TakingtheLin2020 6d ago
We do a version of 3. Some call it tagging. We (liberolabs.ai) created a system that tracks and enforces semantic properties and rules across transformations…PII is a very important property.
1
u/alsdhjf1 6d ago
Another requirement you’ll face is how to delete these sensitive data based on retention or user requests. Hashes aren’t necessarily sufficient in my experience, so a replacement identifier can be used to link to a “sensitive” table that has short retention and very locked down access.
1
u/VirginiaVN900 6d ago
I think the architecture is driven by this hierarchy of importance.
1.) Regulatory/Compliance. Avoid breaking trust, or incurring fines.
2.) Data Policy Agreements. - contractual obligations for your data owners and customers.
3.) Business need - How to solve the problem, with best practice and least privileged principles.
The downstream segmentation of permissions/access may be driven by the audience consuming it.
Example being, Health Care Provider data typically can’t be used by Health Consumer Marketing teams, but can be used by Research/Informational teams in the same org.
So you see it done differently due to the different needs of different industries.
If your manager is defining architecture simply on the past, without analyzing the current factors, they aren’t doing their job.
1
1
u/HAC_AWJ_ENGINEERING 5d ago
I have worked with data in a large healthcare organization where PII, and its dreaded subset, PHI are things that engineers as well as end-users have to work with all day, all week. I think your question has a philosophical aspect to it, and I can bet we can find companies and organizations building any of the above mentioned models. It also depends highly on what the infrastructure at the company is, which controls deeply the security and authorization aspect.
We used EPIC as our primary EHR system, and EPIC allows you to query anything and everything through its reporting models depending on your role or security group, which we defined for each employee. In such cases, this headache is abstracted away from engineers to the software or system deployed.
A huge chunk of our reporting and Business Intelligence was done in MS Fabric utilizing Power BI, Power BI paginated reports, etc. To feed the downstream reporting needs, we used AD groups, RLS, and workspace access in MS Fabric to distribute reports. This way, analysts and engineers were able to build semantic models, reports, metrics, etc by ingesting raw data from on-prem databases, but we would authorize only relevant people to access the reports through the above-mentioned techniques. When I left the organization, we had halfway completed moving everything from on-prem servers to the cloud, so there might be a slight difference in the approach being used there now.
It all depends, in my humble opinion, on how the data is required downstream and what the reporting requirements are. And every decision is not only based on what happens at the incoming end (ingestion), but also depends heavily on what options are available downstream and mid-stream to handle security and authorization. Across every argument I have read and come across in tech in general, I have realized that there is no such thing as a single solution. Everyone does it in a slightly different, if not completely different way and that's fine. Just make sure you have solid reasoning and thought processes that back any architectural decision you make!
And to anyone who differs or has a different opinion, I would love to hear it and learn from it.
1
u/baubleglue 5d ago
You forgetting about encryption of the data in transition. I've worked in a company which encrypted pii data values with a daily rotated token, the tokens were stored on dedicated server. Only whitelisted hosts could access the server with authentication. The policy was to use library to call encrypt/decrypt functions. Internal code was not allowed to store cache data in filesystem.
1
u/fetus-flipper 4d ago
Hashing has its uses, you'd just need to make sure you salt them, otherwise things like SSNs/TINs are very easy to bruteforce
1
u/rahul03rk 3d ago
theres a 4th way, split the pii out at ingestion into its own locked down table and keep a token or hmac in bronze instead. bronze is still a full copy, joins still work, and only one place has the real values. in data vault terms its just a separate pii satellite
also hashing doesnt take you out of gdpr, its still personal data. for deletes look at crypto shredding, encrypt per person and throw away the key
1
u/Cazzah 1d ago edited 1d ago
There are three types of PII. There is the 5% that is irrelevant to the company as useful data and can be just left where it is (eg Name), and then there is the 10% that is useful to the comapny (eg age) and then there is the 85% of data that is useful to the company and nobody thinks is PII but in fact is.
Almost all data is PII in the right context, and anyone who is trying to tell you that there is PII and not PII and the lines are easy is selling you a bridge. The idea of not using PII is hilarious. It's risk management all the way down. You can't not use it.
Let's take a simple example. I work in healthcare. We need to know what treatments are working and not, what is efficient and not. So we don't need PII, right? Just what actions of care were made, what the outcomes were, what was prescrbed, etc. Now of course we need this cross referenced against the medical history of the patient. A patient who has terminal cancel is maybe not going to respond as well to this asmtha treatment as a healthy 20 year old. Ok, so we use hashed identifiers to link episodes of care, then we bin the age groups so they're useful but can't be too easy to pin down.
Shit, except that there was a newspaper about how a local star athelete went to your hospital today for a knee injury in the latest match. So now all anyone needs to do is search the cases for leg care at that time, and they have his anonymous ID and can work out all his stuff and your anonymous dataset you provided to researchers has just headlined that he has AIDs.
Or you know, they could just do an INNER JOIN against many different leaked datasets that could corrobate various factors about patients until you've got 90% confidence of a 1:1 match across most of the dataset.
So, turns out most of the patient's information is actually PII because it can be reidentified with the right data.
I'll give you another one. Our government in Aus released fully anonymised data on care episodes for research purposes. One such care episode is coming to the hospital for a birth. It was quickly shown that someone could look up a wikipedia page for a female politician, find the ages of her two children, do a JOIN against two birth episodes on those years to her age group in her local hospital area, and identify her with around 80% confidence.
Everything is PII. It's all risk management. And the things that are most in front of you are often the least risky. There's the saying "At night, the drunk man searches for the keys he dropped under the street light, not where he dropped them". Safety is a lot like that.
"Option 1 feels like the most natural approach because it fully replicates the source data. However, it also brings the highest compliance risks, as sensitive data is stored in multiple locations, increasing the attack surface and the number of places where access needs to be controlled."
I honestly feel like this one is the least deal. One database, two databases, three databases. Does it matter if people then read the numbers on an excel report generated by a program connected to the database? Then you have 1000+ access and attack surfaces, and it can be emailed around. Databases are nice and centralised, easy to control and tick boxes against. It doesn't matter if you share access via the bronze layer or via the application layer, you're still sharing it, and you will still have to share it. Users are the weak link.
"Skip ingesting sensitive data fields unless they are strictly needed for downstream transformations."
The most sensitive yet critical field in any dataset is often the join keys. And it's absolutely critical for analytics because you need to consider transactions in the wider context and linked to other data.
"With option 3, hashing preserves the ability to perform joins and unique/distinct operations. However, apparently, some regulators still consider hashed PII to be PII if the hashing function is injective (i.e. each output value maps to a single input value"
It's still PII yes. This is true even if it's not injective. Because in most non unique hash algorithms the number of hash collissions are generally extremely low, so injectiveness is actually somewhat irrelevant. And my above examples showed you didn't even need to decrypt the join keys. Hashed join keys where the hashing was unbroken was more than enough to breach privacy.
0
u/skatastic57 6d ago
However, apparently, some regulators still consider hashed PII to be PII if the hashing function is injective (i.e. each output value maps to a single input value).
Technically, no hash function's output maps to a single input value. Maybe what they mean is that the hash of two independently collected PII data points refer to the same person even if you can't go backwards to identify the person. I'm fortunate enough not to deal with PII so I don't deal with these things in practice but that's my first thought.
I think you should generally favor some version of option 1 unless you've promised, or are mandated, not to. As you note, you never know what you might need later so saying you're only going to keep the things you know you need downstream is an issue waiting to happen. I say this from the perspective of being fortunate enough not to deal with PII professionally and just thinking about times I chose not keep some data that I later wanted.
That said I'm also not naive enough to think that my good intentions are enough to keep that data secure so the word "generally" above is doing a lot of work.
The issue you see with your manager vs compliance team is absolutely endemic with compartmentalized corporate bureaucracy. One team only perceives the downside and the other side only perceives the upside. A better way to describe that is (usually) one team gives too much weight to black swan events while the other team doesn't consider them at all.
Another approach would be to physically have 2 bronze layers where one is behind a data diode and stores the PII while the other follows option 2. But for the data coming in through the diode, that system would be air gapped. Alternatively for every row and field of PII you make a uuid which goes to the (relatively) open lake and the air gapped system just holds a key (the uuid) value (the PII) pair.
1
5d ago
[removed] — view removed comment
1
u/dataengineering-ModTeam 2d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
4
u/FrebTheRat 6d ago
Use a metadata tool that will allow easy identification and classification of data assets and lineage. Only bring staged data through to transformed/modeled assets when necessary for the use case. Use your metadata repo to track where your PII/GDPR related data is across your enterprise as well as using it to expose where assets sit at rest for stakeholders and compliance officers. Create automated processes that audit your assets for compliance. Exercise good role based security access to limit the audience that uses data assets to those who need them for real business processes. You will have to use PII data so you're better off knowing where all of it is end to end and constantly auditing through automation than bending over backwards to prevent downstream vetted consumers from getting data they need because of it's classification.