ometere123/the-skills-bible/analytical-data-store-engineer

Analytical Data Store Engineer

Purpose

Support trustworthy large-scale analysis without destabilising transactional systems or losing lineage and historical meaning.

Use this skill when

• Operational databases cannot support analytical scans safely.
• Reports and dashboards need historical, cross-domain data.
• Columnar storage or warehouse modelling is required.
• Analytical cost and scan volume are excessive.

Do not use this skill as the primary workflow when

• The task is transactional schema design.
• The main need is data science modelling.
• A small operational report can run safely on the primary store.

Required inputs

• Analytical questions and metrics.
• Source systems and ownership.
• Data volume, arrival rate, history, and freshness.
• Query concurrency and cost targets.
• Privacy, lineage, and retention requirements.

Classify missing information as blocking, important, or optional. Inspect the actual schemas, constraints, queries, execution plans, data distributions, storage configuration, replication state, backups, retention rules, metrics, and incidents before asking for information that can be verified directly.

Core operating principles

• Treat data correctness, durability, recoverability, privacy, and ownership as primary system requirements.
• Choose storage technology from access patterns, invariants, scale, consistency, lifecycle, and operational evidence rather than fashion.
• Keep one authoritative owner for each durable fact and document all replicas, caches, projections, and derived copies.
• Enforce critical invariants as close to the authoritative data as practical.
• Design for concurrency, retries, partial failure, schema evolution, replay, and recovery from the beginning.
• Measure representative workloads and real query plans instead of relying on synthetic intuition.
• Minimise sensitive data, privileges, retention, and unnecessary copies.
• Make migrations restartable, observable, reversible where possible, and reconcilable where rollback is unsafe.
• Test backups by restoring them and test high availability through controlled failover.
• Avoid coupling durable data meaning to one ORM, driver, vendor, or storage engine.

Workflow

1. Define analytical outcomes

• List measures, dimensions, grains, filters, cohorts, and historical questions.
• Define metric ownership.

2. Choose data grain

• State exactly what one row represents.
• Avoid mixing grains in one fact structure.

3. Design facts and dimensions

• Model additive, semi-additive, and non-additive measures.
• Use conformed dimensions where meanings align.

4. Model history

• Choose current, snapshot, event, or slowly changing dimensions according to analytical need.
• Preserve effective time.

5. Design physical layout

• Partition, cluster, sort, compress, and distribute from common filters and joins.
• Avoid over-partitioning.

6. Build aggregates

• Create materialised summaries for expensive stable workloads.
• Keep definitions and lineage consistent.

7. Isolate workloads

• Separate analytical compute and storage from transactional primaries.
• Set concurrency, queue, and cost controls.

8. Protect data

• Apply row, column, masking, aggregation, and purpose-based access.
• Minimise personal data copies.

9. Validate metrics

• Reconcile counts, totals, dimensions, late data, corrections, and metric definitions with source owners.
• Track quality.

10. Operate lifecycle

• Monitor scan bytes, runtime, queue, skew, freshness, storage, and cost.
• Archive or compact old data according to policy.

Decision rules

Required outputs

• Analytical question model.
• Grain definitions.
• Fact and dimension design.
• History model.
• Physical layout.
• Aggregate strategy.
• Workload isolation.
• Access controls.
• Metric reconciliation.
• Cost and lifecycle plan.

Data and storage quality gates

• Ownership gate: Every durable fact has one authoritative owner.
• Model gate: Schema and storage structure reflect real meaning and access patterns.
• Integrity gate: Required uniqueness, references, ranges, and lifecycle rules are enforced.
• Transaction gate: Atomicity, isolation, conflict, retry, and partial completion are intentional.
• Performance gate: Representative queries, writes, scans, compaction, and maintenance meet approved budgets.
• Availability gate: Replication, failover, restore, and dependency behaviour are proven.
• Security gate: Access, tenancy, encryption, credentials, and sensitive data are protected.
• Lifecycle gate: Retention, archival, expiry, deletion, and legal holds are explicit.
• Migration gate: Schema and data evolution preserve compatibility and reconcile outcomes.
• Observability gate: Capacity, lag, locks, errors, backup status, and recovery health are visible.

Evidence standard

For consequential conclusions, identify the exact engine or storage class, version, schema, query, plan, isolation level, data size and distribution, tenant context, configuration, environment, and time window. Include authoritative state checks, repeatable procedures, and known fidelity gaps. Do not call data protected because a backup job reported success; restore evidence is required.

Failure handling and escalation

Pause or escalate when:

• The authoritative source of truth is unclear.
• A change could corrupt, duplicate, disclose, or irreversibly delete data.
• Schema, migration, isolation, replication, or retention semantics are undefined.
• A production write, destructive query, failover, restore, or key rotation lacks approval and a tested recovery path.
• Backups, replicas, or checksums show inconsistency.
• Required load, restore, failover, security, or migration verification cannot be performed.
• Residual durability, privacy, compliance, or recovery risk exceeds delegated authority.

Report the exact blocker, verified state, affected data, impact, safe options, and smallest required decision.

Common anti-patterns

• Running warehouse scans on production primary.
• Rows with mixed grain.
• Metrics defined differently by each dashboard.
• Partitioning by high-cardinality fields.
• Copying sensitive source tables wholesale.
• Aggregates with no lineage.
• History overwritten.
• Success measured only by query speed without cost.

Completion checklist

• [ ] Every table has a clear grain.
• [ ] Metrics and history are governed.
• [ ] Physical layout matches filters.
• [ ] Transactional workloads are isolated.
• [ ] Sensitive data is minimised.
• [ ] Reconciliation and cost monitoring exist.

Data implementation record

# Data and Storage Implementation Record
 
## Outcome
[Required data capability or decision]
 
## Scope
[Stores, schemas, tables, collections, indexes, partitions, and exclusions]
 
## Ownership and model
[Authoritative facts, entities, relationships, and access patterns]
 
## Integrity and consistency
[Constraints, transactions, isolation, ordering, and conflict]
 
## Performance and capacity
[Workload, plans, latency, throughput, storage, and cost]
 
## Security and privacy
[Identity, privileges, tenancy, encryption, and sensitive data]
 
## Availability and recovery
[Replication, backup, restore, failover, RPO, and RTO]
 
## Lifecycle
[Retention, archival, expiry, deletion, and legal hold]
 
## Migration
[Compatibility, backfill, validation, cutover, and rollback]
 
## Verification
[Tests, plans, metrics, checksums, restores, and drills]

Related skills

• database-architecture-specialist
• time-series-data-engineer
• change-data-capture-engineer
• data-retention-archival-engineer

Maintenance rules

Review this skill when storage capabilities, workload, regulation, or support policy change; incidents reveal a missing invariant or recovery step; the workflow becomes tied to one vendor; the file approaches 500 lines; or reusable queries, validators, migration scripts, or restore drills would improve execution.