Data quality

Quality is measured per dimension, with a rule and a threshold

silver.customersidemailcountry1ana@mail.comGB2NULLGB3bo@FR4cy@mail.comFrance5di@mail.comDE5di@mail.comDE6ed@mail.comIN7fa@mail.comGBMeasured on these rowsCompleteness7 of 8 emails present88%Validity6 of 7 emails well formed86%Uniqueness7 distinct ids in 8 rows88%Consistency7 of 8 countries as ISO codes88%Timelinessloaded 26 h ago; promise 24 h92%Accuracy, the sixth, needs a trusted source to compare against: is Ana really in GB?Data quality is measured, dimension by dimension: is it present, well formed, unique, consistent, on time and true?On eight customer rows: one email missing, one malformed, one id twice, one country written “France” instead of FR, and the load 2 hours late.Each dimension becomes a rule a pipeline can check, with a threshold. Scores like these, tracked over time, tell consumers whether to trust a tableand tell its owner when something upstream changed.

Measure completeness, validity, uniqueness, consistency, timeliness and accuracy, each as a rule a pipeline can check.

Data quality · Going deeper

Data contracts and data observability

A data contract states what a producer promises; data observability watches freshness, volume, schema and distribution.

Agree what “good” means

A data contract is a written, versioned agreement between the team that produces a table and those that use it: the schema, the meaning of each column, quality rules, freshness, and who to call. Breaking changes go through it, like an API.

What to monitor automatically

Freshnesswhen the table last changed, against the promise.
Volumerow counts per load; a sudden drop to zero or a doubling is a symptom.
Schemacolumns added, removed or retyped upstream.
Distributionnull rates, ranges and category shares drifting from normal.
Lineagewhich upstream change explains it, and which dashboards it reaches.

Quality checks

Each rule needs a decision: record it, drop the row, or stop

CONSTRAINT valid_total EXPECT (total > 0 AND total < 100000) ON VIOLATION …1,000 rows12 break the rulewarn (default)keep all rows, record the failures1,000 written12 flagged in metricsDROP ROWkeep only rows that pass988 written12 droppedFAIL UPDATEstop the update, write nothing0 writtenbatch rejected; alertQuality rules belong inside the pipeline, with a decision for each: record the failure, drop the row, or stop the load.Lakeflow Declarative Pipelines call these expectations; the default keeps rows and records failures, DROP ROW removes them, FAIL UPDATE halts.Choose by consequence: a wrong price stops the load, a missing middle name is only counted. Dropped rows are better sent to a quarantine tablefor review than lost. The same idea runs as dbt tests, AWS Glue Data Quality rules and Great Expectations suites.

A pipeline expectation can warn and keep rows, drop failing rows, or fail the update.

Data lineage

Lineage traces each column back to its source and forward to its users

orders apporders.amountPostgreSQLbronze.orders_rawamount (string)as receivedsilver.orderstotal DECIMALcast, deduplicatedgold.daily_revenuerevenueSUM(total)schema checknot null, > 0, unique idrow count vs silverBIMLUpstream (root cause): revenue fell 30% → silver.orders → bronze → a change in the orders appDownstream (impact): renaming silver.orders.total breaks gold.daily_revenue, a dashboard and a modelLineage records where every table and column came from and what reads it, so a problem can be traced both ways.Read it upstream to find why a number changed, and downstream to see what a change will break before you make it. Unity Catalog captureslineage automatically, down to columns, for queries run on Databricks; Microsoft Purview and AWS (Amazon DataZone, SageMaker Catalog) collectit across services; OpenLineage is the open standard. Checks at each hop mean a fault is caught near where it entered.

Lineage shows where data came from and what depends on it: upstream for root cause, downstream for impact analysis.

Data catalogues

A catalogue says what data exists, what it means and who owns it

catalog entry: main.sales.ordersTechnicalDelta · 41 columns · s3://lake/sales/orders/Businessowner: sales-data team · “one row per order line”glossary: Net revenue · domain: SalesTagsPIIemailcertifiedOperationalupdated 02:14 · quality 99.6% · 38 readers this weeklineage: 3 upstream, 11 downstreamWhere it livesUnity CatalogDatabricksGlue Data CatalogAWS; crawlers infer schemasMicrosoft Purviewscans and classifiesOneLake catalogMicrosoft FabricHorizonSnowflakeA catalogue is the searchable inventory of data: what exists, what it means, who owns it, and whether it can be trusted.Technical metadata (schema, format, location) is collected automatically; business metadata (owner, description, glossary terms) is added bypeople; tags such as PII drive policies; usage, freshness and quality show whether a table is alive. Without a catalogue, a lake becomes aswamp: the data is there, but nobody can find it or knows which copy is right.

A data catalogue holds technical, business and operational metadata, and tags such as PII that drive policy.

Access control

Grant the least access to groups, on the object and its containers

catalog mainUSE CATALOGschema salesUSE SCHEMAtable ordersSELECTGRANT USE CATALOG ON CATALOG main TO `analysts`;GRANT USE SCHEMA ON SCHEMA main.sales TO `analysts`;GRANT SELECT ON TABLE main.sales.orders TO `analysts`;REVOKE SELECT ON TABLE main.sales.orders FROM `interns`;Grant to groups, never to peopleLeast privilege: only what the job needsAudit: every access is loggedAccess is granted on securable objects to groups: to read a table you need permission on it and on every container above it.In Unity Catalog that is USE CATALOG, USE SCHEMA and SELECT; privileges granted on a catalog or schema are inherited by everything inside.Snowflake grants privileges to roles and roles to users; AWS Lake Formation grants on Data Catalog databases and tables, down to columns.Grant to groups so joiners and leavers are handled in one place, give the least access that does the job, and review the audit log.

Use GRANT and REVOKE on securable objects, to groups, following least privilege; Unity Catalog needs USE CATALOG, USE SCHEMA and SELECT.

Access control · Going deeper

Access models on Snowflake and AWS

Snowflake uses role-based access control; AWS Lake Formation adds table, column and row permissions on top of IAM.

Snowflake

  • Roles hold privileges; users hold roles GRANT SELECT ON TABLE orders TO ROLE analyst; GRANT ROLE analyst TO USER asha. Roles can be granted to roles, forming a hierarchy.
  • System roles ACCOUNTADMIN (top, use sparingly), SECURITYADMIN (manages grants), USERADMIN (users and roles), SYSADMIN (warehouses and databases), PUBLIC (everyone).
  • Policies masking policies hide column values and row access policies filter rows, both by role, attached to columns or tables.

AWS

  • IAM who can call which service and API, and on which S3 paths.
  • Lake Formation grants on Glue Data Catalog databases, tables and columns, with data filters for rows and cells, enforced for Athena, Redshift Spectrum, EMR and Glue. Tag-based access control (LF-Tags) grants by tag instead of by table.

Row and column security

One table can return different rows and values to each user

ALTER TABLE sales SET ROW FILTER region_filter ON (region);ALTER TABLE sales ALTER COLUMN email SET MASK email_mask;SELECT * FROM sales as an adminidregionemailtotal1EUana@mail.com120.002USbob@mail.com80.503EUcy@mail.com42.004APACdi@mail.com310.005EUed@mail.com15.25the same query, as an EU analystidregionemailtotal1EUa***@mail.com120.003EUc***@mail.com42.005EUe***@mail.com15.253 of 5 rows (row filter) · emails masked (column mask)Row filters and column masks make one table return different rows and values to different people, from the same query.Each is a SQL function attached to the table: the filter returns true for the rows a user may see, the mask returns the value they maysee, both deciding by group membership or tags. No copies per audience, no views to keep in step, and every tool gets the same rule. UnityCatalog, Snowflake (row access and masking policies), Lake Formation (data filters) and Fabric offer them. Results computed from the rows shown.

A row filter limits which rows a user sees; a column mask changes what a column shows; both are functions on the table.

Masking and anonymising · Going deeper

Masked, hashed and tokenised data is still personal data

Techniqueana@mail.com becomesCan it be reversed?Joins still work?Personal data?Dynamic maskinga***@mail.com, when queriedthe stored value is unchangednoyesHashing (SHA-256)5011aed537bfa79d…no, but guessable from a listyes: same input, same hashyesKeyed hash (HMAC)a hash made with a secret keyno, without the keyyesyesTokenisationtok_…, a random stand-inyes, through the token vaultyesyesEncryptionciphertextyes, with the keyonly if deterministicyesGeneralisationmail.com · Leeds · age 30 to 39nonono, if truly anonymousA masking policy as written in Snowflake:CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('PII_READER') THEN val ELSE '***' END;ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;Masking hides a value, hashing and tokenising replace it; only anonymising makes it no longer personal.A plain hash of an email is not protection: hash a list of known addresses and compare. Add a secret key, or use tokenswhose mapping sits in a vault. All of these are pseudonymisation: under GDPR Article 4(5) the data can still be linked backto a person, so it remains personal data. Anonymous data falls outside GDPR only when nobody can reasonably re-identify it,and a rare combination of town, age and date often can. k-anonymity is the test: every row shares its quasi-identifiers withat least k − 1 others. The hash shown is the real SHA-256 of the address.

Pseudonymisation (hashing, tokenisation) can be linked back and stays personal data; anonymisation cannot, and does not.

Encryption

Encrypt in transit and at rest; whoever holds the key holds the data

analystTLS 1.2+query engineTLSstorageIn transitTLS on every connection, so nothing readable crosses the network.At restAES-256 on disk by default in every major cloud (S3, Azure Storage, Cloud Storage).Key managementAWS KMS · Azure Key Vault · Cloud KMScustomer-managed keys: you rotateor revoke, and every use is loggedKeysProvider-managed by default; customer-managed keys when policy demands control of the key.BeyondPseudonymise or tokenise identifiers so analysts can join on them without seeing them; keep secrets in a vault, not in code.Encrypt data in transit and at rest, and control the keys: whoever controls the key controls the data.Cloud storage now encrypts everything at rest by default, so the real decisions are who manages the keys and who may use them. With acustomer-managed key, disabling it makes the data unreadable everywhere at once. Encryption does not replace access control: a user allowed toquery sees decrypted data. Credentials and connection strings belong in Key Vault, Secrets Manager or Databricks secret scopes.

Use TLS in transit and encryption at rest, with keys in a key management service; customer-managed keys give you control.

Envelope encryption · Going deeper

A data key encrypts the data; the KMS key encrypts the data key

KMS keynever leaves the key serviceevery use checked and loggedData keyone per object or fileAES-256, made by the serviceYour dataa file, a table, a backupencrypted locally, any sizeencryptsencryptsWhat is stored, side by side:encrypted data keyencrypted dataTo read: send the small encrypted data key to the key service (Decrypt), get the plain data key back in memory,decrypt the data with it, and forget it.Disable the KMS key and every data key under it is useless: all that data becomes unreadable at once.A data key encrypts the data, and a key that never leaves the key service encrypts the data key.It exists because a key service cannot encrypt gigabytes: AWS KMS Encrypt takes at most 4 KB, enough for a key. Rotationre-wraps small keys instead of re-encrypting data. On S3: SSE-S3 (Amazon’s keys, the default for every new object), SSE-KMS(a KMS key you control, each use logged in CloudTrail; S3 Bucket Keys cut the KMS calls), SSE-C (you send the key with eachrequest) or client-side. Azure SQL and SQL Server call it transparent data encryption (TDE), on by default in Azure SQLDatabase; Snowflake rotates its keys every 30 days.

Envelope encryption stores an encrypted data key beside the data; only the key service can unwrap it.

Privacy and retention

A deleted row survives in history and copies until those are purged

A customer asks to be erased (GDPR Article 17). In a Delta table:day 0day 1day 2day 3day 4day 5day 6day 7day 8day 9DELETE FROM customers WHERE id = 42gone from the current versionold files still hold id 42: VERSION AS OF reads it for 7 daysVACUUM customersfiles deletedAlso: bronze copies, exports, backups, caches, and the Fail-safe or soft-delete periods of each platform.Respond within one month (Art. 12); keep data only as long as its purpose needs (Art. 5, storage limitation).Deleting a row does not erase it while table history, copies and backups still hold it; erasure needs every copy covered.Delta, Iceberg and Snowflake keep old files for time travel, so a DELETE is only complete after VACUUM, here after the default 7-day retention.Design for privacy from the start: collect only what you need, tag personal data in the catalogue, keep identifiers in few tables, setretention on every layer, and know where each person’s data lives so a request can be answered. Not legal advice; involve your privacy team.

Erasure under GDPR means every copy: after DELETE, time travel still exposes the row until VACUUM, here after 7 days.

GDPR in numbers · Going deeper

GDPR in the numbers a data team has to build for

A breach is reported within 72 hours, a request answered within one month, and the controller answers for both.

72 hoursto tell the regulator about a personal data breach, from becoming aware of it (Article 33).
1 monthto answer a person’s request, extendable by two more for complex ones (Article 12).
€20M or 4%the top fine: whichever is higher, 4% being of worldwide annual turnover (Article 83).
6lawful bases for processing: consent, contract, legal obligation, vital interests, public task, legitimate interests (Article 6).

Who is responsible

  • Controller decides why and how personal data is processed: your company. It answers to the regulator.
  • Processor processes on the controller’s instructions: the cloud provider, a SaaS tool. Bound by a data processing agreement.

What each right asks of the platform

  • Access and portability (Articles 15, 20) find every row about one person, in every table, and export it. Needs a catalogue that tags personal data and a common person key.
  • Rectification (Article 16) fix it at the source and let the pipeline carry the fix through.
  • Erasure (Article 17) delete from every layer, then purge history: VACUUM, Time Travel, backups.
  • Storage limitation (Article 5) a retention period on every table, enforced by a job or a lifecycle rule, not by memory.

Other regimes, and the tools for retention

  • CCPA, HIPAA, PCI DSS California’s consumer privacy law (know, delete, opt out of sale); US health records; card data, where the security code may never be stored after authorisation.
  • Deleting on time S3 Lifecycle rules move objects to colder classes and expire them; Azure Blob lifecycle management does the same.
  • Keeping on purpose S3 Object Lock and Azure immutable storage make objects write-once: in compliance mode nobody, the root user included, can delete them before the date.

Lesson complete

You finished “Data quality and governance”

Nicely done.

Open in a new tab