Chapter 17 · Security, Privacy, Governance, and Auditability
Retention, Deletion, Lineage, and Data Quality Rules
Design retention, deletion, lineage, and data-quality rules so data remains governed across its lifecycle, including downstream copies, archives, and derived structures.
Learning outcomes
Governance continues after data is inserted. Every important data set needs rules for retention, deletion, lineage, ownership, and quality. These rules become difficult when data has been copied into caches, warehouses, backups, search indexes, exports, and materialized views.
Model retention and deletion as lifecycle rules.
Trace data lineage into derived systems.
Define data-quality constraints beyond column types.
Build a governance registry for WorkshopHub.
Retention is a business rule
Examples:
diagnostic raw payloads: 90 dayscompleted WorkOrders: 7 yearstemporary import staging: 7 dayssecurity audit records: policy-defined periodActual periods depend on business, contractual, and legal requirements.
Do not encode arbitrary retention
“Keep everything forever” increases cost, breach impact, deletion complexity, and privacy exposure. “Delete everything after 30 days” may violate operational or legal needs. Retention must have a documented rationale.
Deletion semantics
Deletion can mean:
- hard delete;
- soft delete;
- anonymization;
- archive then delete;
- cryptographic erasure;
- logical tombstone in an event stream.
Soft delete is not actual deletion
deleted_at IS NOT NULLkeeps the row and its sensitive contents. It helps recoverability or workflow but does not satisfy every deletion requirement.
Choose soft delete for lifecycle semantics, not as a substitute for real erasure when erasure is required.
Cascade deletion
Deleting Customer raises questions for:
AssetsWorkOrdersInvoicesAuditEventsSearchIndexWarehouseBackupsSome should delete, some anonymize, some remain for legitimate retention purposes.
Delete by ownership graph
Document data ownership relationships so a deletion workflow can discover dependent data reliably.
Derived data must be included
Chapter 13's projections and caches require deletion propagation:
Backups complicate deletion
Backups may be immutable for recovery. Governance should define backup retention and what happens if deleted data is restored—for example, replay deletion tombstones after restore.
Lineage
Lineage makes impact analysis and deletion propagation possible.
Technical lineage metadata
Track:
- source dataset;
- target dataset;
- transformation/job;
- version;
- run timestamp;
- owner;
- freshness.
Business lineage
Technical lineage says where bytes moved. Business lineage explains meaning, such as:
monthly_repair_count= count of WorkOrders with completion date in monthexcluding cancelled ordersData quality dimensions
| Dimension | Question |
|---|---|
| Completeness | Are required facts present? |
| Validity | Do values obey domains/rules? |
| Uniqueness | Are duplicates prevented? |
| Consistency | Do related datasets agree? |
| Timeliness | Is data fresh enough? |
| Accuracy | Does data reflect reality? |
Database constraints are quality controls
NOT NULLCHECKUNIQUEFOREIGN KEYThese provide immediate deterministic quality guarantees.
Cross-row quality rules
Examples:
no overlapping AssetOwnership intervalsclosed WorkOrder has closed_atPartUsage quantity > 0no duplicate active primary assignmentSome need transactions, exclusion constraints, triggers, or validation jobs.
Freshness quality
For a projection:
source newest timestamp - projection newest timestamp <= 60 secondsFreshness is a measurable data-quality rule.
Reconciliation quality
Derived totals can be compared against source facts. Quality monitoring should alert on drift before users discover it.
Data owner and steward
Each important dataset should have a named owner responsible for meaning and access, and often a steward/operational team responsible for quality and lifecycle.
Governance registry
Dataset: CustomerBillingProfileOwner: BillingClassification: highly sensitiveRetention: contract + policyAllowed roles: billing_serviceDownstream copies: invoice warehouseQuality checks: tax_id format, billing_email validityDeletion action: policy-definedWorkshopHub lifecycle examples
| Dataset | Lifecycle concern |
|---|---|
| DiagnosticCapture | Shorter raw-payload retention |
| WorkOrder | Long operational history |
| Search projection | Delete/rebuild from source |
| AuditEvent | Longer protected retention, minimized content |
| Import staging | Automatic short TTL cleanup |
Practice: deletion request
Customer erasure workflow
A Customer must be deleted or anonymized according to policy. Which systems must be considered?
Review answer
Primary relational rows, child entities according to ownership rules, caches, search indexes, analytics/warehouse copies, exported files where governed, materialized projections, audit records according to retention/legal basis, and backup restore procedures. The exact action may be delete, anonymize, or retain under a documented obligation.
Chapter 17 synthesis
governed data = explicit owner + least privilege + sensitivity classification + tenant boundary + auditable changes + retention/deletion + lineage + measurable qualitySummary and next chapter
Chapter 17 connected database design to security and governance. You can now build least-privilege access boundaries, minimize and protect sensitive data, enforce tenant isolation, preserve audit/provenance, and govern retention, deletion, lineage, and quality. Chapter 18 is the capstone: you will apply the entire course to design, normalize, implement, index, secure, evolve, test, document, and defend a production-ready database.
References
- NIST security and privacy guidance.
- OWASP database, logging, and authorization guidance.
- DBMS documentation for roles, row-level security, auditing, constraints, and encryption capabilities.
- Applicable organizational, contractual, and legal retention/privacy requirements.