Scenario Lab — Scenarios 10–18
10 — Archive or purge?
Situation: records rarely used but must remain retrievable for seven years.
Primary problem: defined retention obligation despite low activity. Roles: data owner, records/legal/governance, DBA operations.
Best action: retain and archive as appropriate while preserving retrieval.
Weaker: purge because rarely used.
Changed fact: retention expired + authorized policy says no continuing value/need → purge.
Source: pp. 186–188.
11 — Delta replication
Situation: only 1% of a very large object changes nightly.
Primary problem: full movement is inefficient. Supporting: CDC/versioning, replication.
Best action: use changed-data/delta/merge logic subject to consistency requirements.
Weaker: full refresh every cycle without need.
Changed fact: virtually all rows change or clean replacement required → full refresh may be simpler.
Source: pp. 188–193.
12 — Immediate failover need
Situation: secondary must be nearly current for immediate failover.
Primary problem: replication latency requirement. Roles: Production DBA, architect, continuity owner.
Best action: favor mirroring/near-immediate synchronization when cost/complexity is justified.
Weaker: periodic log shipping despite immediate requirement.
Changed fact: business tolerates 15–30 minute lag → log shipping may satisfy need at lower complexity/cost.
Source: pp. 190–197.
13 — Slow query after large data changes
Situation: queries suddenly slow after heavy inserts/deletes; optimizer chooses poor plans.
Primary problem: statistics may not reflect changed distribution. Roles: Production/Application DBA, developer.
Best action: inspect optimizer behavior and refresh relevant statistics before structural redesign.
Weaker: denormalize immediately.
Changed fact: statistics current but plan shows missing indexes/inefficient joins → index/query/code remediation leads.
Source: pp. 197–203.
14 — Deadlock complaint
Situation: two processes each hold resources the other needs.
Primary problem: circular lock dependency. Supporting: transaction/concurrency performance, application code. Roles: Production/Application/Procedural DBA, developers.
Best action: resolve deadlock operationally and investigate coding/transaction order/root cause.
Weaker: generic server-capacity diagnosis.
Changed fact: no circular dependency; one query monopolizes CPU/IO → runaway-query/workload-governor analysis.
Source: pp. 197–203.
15 — Runaway analyst query
Situation: one ad hoc query consumes most shared resources.
Primary problem: one workload threatens shared availability/performance. Roles: Production DBA, analytics/application owner.
Best action: ranking/query-governor controls to protect service; pause/kill if needed; then improve query/root cause.
Weaker: redesign entire database first while users remain impacted.
Changed fact: many ordinary queries are slow because platform truly lacks capacity → capacity/performance planning becomes primary.
Source: pp. 197–203.
16 — Protected test data
Situation: realistic customer test records contain restricted fields.
Primary problem: testing realism vs protection. Supporting: Test Data, Security, governance. Roles: test team, DBA, data owner, security/privacy.
Best action: generate/sample realistic data and mask/protect production-derived sensitive fields.
Weaker: copy full Production unchanged.
Changed fact: synthetic data fully exercises required scenarios → avoid Production-derived sensitive data altogether.
Source: pp. 203–205.
17 — Migration with heavy remediation
Situation: project proposes extensive transformation while calling work “pure migration.”
Primary problem: migration vs integration/remediation boundary blurred. Supporting: Integration, Data Quality, project planning. Roles: migration lead/DBA, integration/DQ teams, architect.
Best action: treat migration as transfer with as little change as practical and explicitly plan substantial transformation/remediation.
Weaker: hide complexity/responsibilities under the word migration.
Changed fact: structures/meaning preserved and only storage/platform changes → clean Chapter 6 migration fit.
Source: pp. 205–207; compare Chapter 8.
18 — Audit or validation?
Situation: regulator asks whether stored dataset met contractual/methodological storage requirements.
Primary problem: compliance criteria vs fitness/quality criteria. Supporting: Governance, Data Quality. Roles: auditor/compliance, data owner/steward, operations.
Best action: Data Audit for contractual/methodological/compliance criteria.
Weaker: validation merely because object being checked is data.
Changed fact: question asks whether data is fit for use and meets agreed quality/usability criteria → Data Validation.
Source: pp. 207–208.