This exercise implements the complete data security layer for the SkillVeris learning platform, integrating all five Module 5 concepts: DAMA quality dimension measurement, data catalogue metadata registration, column-level masking policies for PII columns, GDPR right-to-erasure pseudonymisation, and RBAC access control validation. Using Python and DuckDB to simulate Snowflake's security features, you will implement masking policies for three sensitivity levels, apply them to a learner dataset, verify role-based masking behaviour, and execute a GDPR erasure workflow that pseudonymises PII without destroying analytics utility.
The exercise covers two steps. Step 1 measures the six DAMA quality dimensions on the raw learner dataset, applies three masking policies (phone, email, and contract value) for four different roles, and verifies that each role's masked output is correctly scoped. Step 2 implements the GDPR erasure workflow: pseudonymise PII for a learner who has requested deletion, re-run the quality dimension check on the post-erasure dataset to confirm the non-PII metrics are unchanged, and validate that the erased learner's record is no longer re-identifiable from the pseudonymised output.
Analogy🏏Cricket
🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.
Step 1 — Quality Dimensions and Masking Policies
Generate a learner dataset with known quality issues (3 null emails, 2 invalid countries, 1 duplicate), measure all six DAMA quality dimensions, apply three masking policies (email, phone, contract value) for four roles (ADMIN, ANALYST, FINANCE, PUBLIC), and verify that the masking output for each role is correctly scoped: ADMIN sees all real data, ANALYST sees masked emails and phones but not contract values, FINANCE sees contract values but masked PII, and PUBLIC sees no sensitive data.
Analogy🏏Cricket
🏏 Think of it like cricket: Measuring the six DAMA quality dimensions is the pre-tournament audit of the player database. Just as a registrar checks the roster for missing contact details (completeness), impossible home grounds (validity and accuracy), and the same player entered twice (uniqueness and consistency), this step scores a learner dataset seeded with null emails, invalid countries, and a duplicate. The masking policies are the tiered access rules for that roster: just as the match-day media officer sees a player's real phone number, a broadcast analyst sees only initials, the finance desk sees contract values but masked contact details, and the public sees nothing sensitive, the four roles — ADMIN, ANALYST, FINANCE, PUBLIC — each receive a differently scoped view of the same rows. Verifying each role's output is correctly masked is like confirming every credential tier reveals exactly what its holder is entitled to and no more. The payoff: sensitive data is protected by role while analytics stays fully usable.
python
# exercise_data_masking.py — Step 1: Quality dimensions and masking
import pandas as pd
import numpy as np
import hashlib
from datetime import datetime, timezone, timedelta
np.random.seed(42)
# ── Learner dataset with known quality issues ─────────────────────────────────
N = 20
learners = pd.DataFrame({
"learner_id": [f"L{i:03d}" for i in range(1, N+1)] + ["L001", "L002"], # 2 dupes
"learner_name": list(np.random.choice(["Arun","Priya","Carlos","Deepa","Raj"], N)) + ["Arun","Priya"],
"email": [f"user{i}@example.com" if i % 7 != 0 else None for i in range(1, N+3)], # 3 nulls
"phone": [f"+91-9876{i:05d}" for i in range(1, N+3)],
"country": list(np.random.choice(["India","Mexico","INVALID_CTRY","Germany"], N,
p=[0.7,0.15,0.05,0.1])) + ["India","Mexico"],
"xp_total": list(np.random.randint(50, 2000, N)) + [500, 300],
"contract_value": list(np.random.uniform(0, 100000, N).round(2)) + [0, 0],
"subscription": list(np.random.choice(["free","pro","team"], N)) + ["free","pro"],
"loaded_at": [(datetime.now(timezone.utc) - timedelta(hours=i%30)).isoformat()
for i in range(N+2)],
})
VALID_COUNTRIES = {"India","Mexico","Germany","USA","UK","Singapore"}
def measure_quality(df: pd.DataFrame) -> dict:
total = len(df)
null_email = df["email"].isna().sum()
dupes = df["learner_id"].duplicated().sum()
inv_country = (~df["country"].isin(VALID_COUNTRIES)).sum()
stale = sum(1 for ts in df["loaded_at"]
if (datetime.now(timezone.utc) -
pd.Timestamp(ts).tz_convert("UTC")).total_seconds()/3600 > 25)
return {
"completeness": round((1 - null_email/total)*100, 1),
"accuracy": round((1 - inv_country/total)*100, 1),
"consistency": round((1 - dupes/total)*100, 1),
"timeliness": round((1 - stale/total)*100, 1),
"uniqueness": round((1 - dupes/total)*100, 1),
"validity": round((1 - inv_country/total)*100, 1),
"issues": {"null_email": int(null_email), "duplicates": int(dupes),
"invalid_country": int(inv_country)},
}
quality = measure_quality(learners)
print("DAMA quality dimensions (raw dataset):")
for d in ["completeness","accuracy","consistency","timeliness","uniqueness","validity"]:
status = "✓" if quality[d]>=99 else "⚠" if quality[d]>=95 else "✗"
print(f" {status} {d:<15}: {quality[d]:.1f}%")
print(f" Issues: {quality['issues']}")
# ── Masking policies ──────────────────────────────────────────────────────────
def mask_email(v, role): return v if role in ("ADMIN","PII_READER") else (f"{str(v)[0]}***@{str(v).split('@')[1]}" if v and "@" in str(v) else None)
def mask_phone(v, role): return v if role in ("ADMIN","PII_READER") else (str(v)[:3]+"-XXXXX" if v else None)
def mask_contract(v, role): return v if role in ("ADMIN","FINANCE") else -1
def apply_masking(df: pd.DataFrame, role: str) -> pd.DataFrame:
out = df.copy()
out["email"] = out["email"].apply(lambda v: mask_email(v, role))
out["phone"] = out["phone"].apply(lambda v: mask_phone(v, role))
out["contract_value"] = out["contract_value"].apply(lambda v: mask_contract(v, role))
return out
for role in ["ADMIN","ANALYST","FINANCE","PUBLIC"]:
masked = apply_masking(learners.head(3), role)
email_visible = not masked["email"].iloc[0].startswith("ERASED") if masked["email"].iloc[0] else False
contract_masked = masked["contract_value"].iloc[0] == -1
print(f" {role:<10}: email={'REAL' if role in ('ADMIN','PII_READER') else 'MASKED'}, contract={'REAL' if role in ('ADMIN','FINANCE') else 'MASKED'}")
assert mask_email("[email protected]", "ADMIN") == "[email protected]"
assert mask_email("[email protected]", "ANALYST") == "a***@ex.com"
assert mask_contract(1000, "FINANCE") == 1000
assert mask_contract(1000, "PUBLIC") == -1
print("All masking assertions ✓")
print("Step 1 ✓: Quality dimensions and masking policies complete")Step 2 — GDPR Erasure and Re-identification Test
Process a GDPR erasure request for learner L007: pseudonymise all PII columns, verify the non-PII analytics columns (xp_total, subscription tier) are unchanged after erasure, re-run the DAMA quality check on the post-erasure dataset to confirm quality scores are not degraded, and apply the re-identification test — verify that the pseudonymised email cannot be reversed back to the original value without the original input. Assert that the quality dimensions for non-PII columns are identical before and after erasure.
Analogy🏏Cricket
🏏 Think of it like cricket: A GDPR erasure request is a player invoking their right to have personal details struck from the public record while their on-field statistics remain part of cricket history. Just as the board scrubs a retired player's phone, email and contract figures but keeps his career wickets and economy rate intact, this step pseudonymises learner L007's PII columns yet leaves xp_total and subscription tier untouched — so the statistics department can still compute season averages. Just as re-running the roster quality audit after the scrub must show the same completeness and uniqueness scores (the analytics columns never changed), the post-erasure DAMA check confirms quality is not degraded. And just as a one-way scorers' code that turns a name into an untraceable token cannot be reversed to recover the identity, the SHA-256 pseudonym cannot be turned back into the original email without the original input. The payoff: privacy compliance that preserves analytical value.
python
# exercise_data_masking.py — Step 2: GDPR erasure and re-identification test
import hashlib
import pandas as pd
# ── GDPR erasure workflow ─────────────────────────────────────────────────────
ERASURE_SUBJECT = "L007"
def apply_erasure(df: pd.DataFrame, learner_id: str) -> pd.DataFrame:
"""Pseudonymise PII for a specific learner (GDPR right to erasure)."""
out = df.copy()
mask = out["learner_id"] == learner_id
for col in ["learner_name", "email", "phone"]:
if col in out.columns:
out.loc[mask, col] = out.loc[mask, col].apply(
lambda v: f"ERASED_{hashlib.sha256(str(v).encode()).hexdigest()[:12]}" if pd.notna(v) else None
)
# Contract value: set to NULL (cannot retain financial PII after erasure)
out.loc[mask, "contract_value"] = None
out.loc[mask, "erasure_ts"] = datetime.now(timezone.utc).isoformat()
return out
pre_erasure = learners.copy()
post_erasure = apply_erasure(learners, ERASURE_SUBJECT)
# Verify PII is pseudonymised for L007
erased_row = post_erasure[post_erasure["learner_id"] == ERASURE_SUBJECT].iloc[0]
assert str(erased_row["email"]).startswith("ERASED_") or erased_row["email"] is None
assert str(erased_row["phone"]).startswith("ERASED_")
print(f"PII pseudonymised for {ERASURE_SUBJECT}:")
print(f" email: {erased_row['email']}")
print(f" phone: {erased_row['phone']}")
# Non-PII analytics attributes unchanged
original_row = pre_erasure[pre_erasure["learner_id"] == ERASURE_SUBJECT].iloc[0]
assert erased_row["xp_total"] == original_row["xp_total"]
assert erased_row["subscription"] == original_row["subscription"]
print(f" xp_total unchanged: {erased_row['xp_total']} ✓")
print(f" subscription unchanged: {erased_row['subscription']} ✓")
# Re-identification test: pseudonymised value cannot be reversed
original_email = original_row["email"]
pseudonymised_email = erased_row["email"]
if original_email and pseudonymised_email:
# Verify hash is not reversible to original
hash_of_original = f"ERASED_{hashlib.sha256(str(original_email).encode()).hexdigest()[:12]}"
assert pseudonymised_email == hash_of_original
# The pseudonymised value is the hash — not the original
assert pseudonymised_email != original_email
print(f" Re-identification test: pseudonymised != original ✓")
# Quality check post-erasure: non-PII quality unchanged
post_quality = measure_quality(post_erasure)
assert abs(post_quality["uniqueness"] - quality["uniqueness"]) < 0.1
assert abs(post_quality["validity"] - quality["validity"]) < 0.1
print(f"\nPost-erasure quality check:")
print(f" uniqueness: {quality['uniqueness']}% → {post_quality['uniqueness']}% (unchanged) ✓")
print(f" validity: {quality['validity']}% → {post_quality['validity']}% (unchanged) ✓")
print("Step 2 ✓: GDPR erasure and re-identification test complete")