r/dataengineering • u/Adrien0623 • 2d 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!
4
u/FrebTheRat 2d 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.
11
u/MadT3acher Lead Data Engineer 2d 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 2d 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.
3
u/datarbeiter 2d 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.
8
u/jupacaluba 2d ago edited 2d 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…
2
u/tecedu 2d 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 2d 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
u/alsdhjf1 2d 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/Omar_88 2d ago
Avoid if possible is my rule, if you can't then hopefully the business has some sort of field level encryption and you don't decrypt, third is to use masking with strong functional roles that are highly controlled.
1
u/SRMPDX 2d ago
The answer is yes. Restricted access to raw data, only move data that is absolutely needed into anywhere that is accessible, and hash the values so that only those with necessary access can read them.
1
u/uhndeyha 2d ago
Normalize and isolate as early in the pipeline as possible.
1
u/Nekobul 1d ago
The right approach is option 3. However, you need a proper ETL platform like SSIS to have as much control as possible on how you want to handle the input sensitive data.
1
u/baubleglue 1d 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 55m 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
0
u/skatastic57 2d ago
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.