Data Engineering Exploration

Data Engineering Exploration

You are the Data Engineering Exploration orchestrator. Drive the 7-stage flow that converts a data engineering ask + any available reference material into a complete data architecture + transformation logic spec + validation/lineage/observability plan + OpenSpec change. Each stage's 3-reviewer convergence runs inside ralph-loop:ralph-loop with a total-agreement completion-promise. Each ralph-loop runs until its completion-promise is satisfied (total reviewer agreement); there is no iteration cap (per common-pipeline-conventions ## Unbounded solving discipline).

When this skill runs

Three callers as of v3.50.0:

1. architect-team-pipeline Phase 0c — Data-engineering dispatch check — the new Phase 0c heuristic detector matched (per common-pipeline-conventions/SKILL.md ## Data engineering exploration discipline (v3.5.0)). The orchestrator dispatches this skill BEFORE Phase 0 (Detection & Normalization).

2. architect-team-pipeline mixed-mode — when both a frontend / API surface (Phase 0a or 0b) AND a data-engineering surface (Phase 0c) are detected (e.g., "build the analytics warehouse AND the dashboard UI on top"). Phase 0a or 0b runs first; this skill runs after, using the upstream API contract as Stage 1 domain context input.

3. data-eng-pipeline Phase D0 — the data-engineering lane (v3.50.0) — the third caller. When a data-eng-primary ask routes to the data-eng-pipeline lane at the front door (Phase −2 returns kind: data-eng, or /architect-team:data-eng / --data-eng), the lane dispatches this skill VERBATIM at its Phase D0 — no fork, no duplication of the 7-stage flow. The lane wraps this exploration with its own D−1 warm-catalog-first check and D7 catalog-refresh disciplines, but the exploration itself is unchanged.

Inputs

The caller passes a structured inputs object:

{
  "request_summary": "<verbatim user prose excerpt>",
  "codebase_inputs": ["<absolute-path-to-existing-data-codebase>", ...],
  "doc_inputs": ["<absolute-path-to-doc>", ...],
  "upstream_api_contract_path": "<absolute-path-or-null>",
  "output_dir": "<absolute-path-to-output-directory>",
  "openspec_change_name": "<kebab-case-change-name>",
  "data_eng_classification": "warehouse" | "streaming" | "lakehouse" | "elt-pipeline" | "feature-store" | "cdc" | "analytics-api" | "data-mesh" | "data-product" | "mixed",
  "completion_promise": "DATA ENGINEERING EXPLORATION COMPLETE"
}

At least one of codebase_inputs / doc_inputs / upstream_api_contract_path MUST be non-empty. Pure greenfield-no-reference data eng work still falls through to Phase 0 plain-branch authoring; this skill exists to extract structure from available evidence.

Phase D0 — Initialization

1. Resolve <workspace> via git -C <cwd> rev-parse --show-toplevel (cwd fallback).
2. Allocate <exploration-id> as data-eng-exploration-<YYYY-MM-DD-HHMMSS>-<6-char-rand>.
3. Create the working dir: <workspace>/.architect-team/data-engineering-exploration/<exploration-id>/{stage-1,stage-2,stage-3,stage-4,stage-5,stage-6,stage-7}/.
4. Persist scope: <workspace>/.architect-team/data-engineering-exploration/<exploration-id>/scope.json with the verbatim caller inputs + parsed classification + the request_summary.

Stage 1 — Domain context (delegates to domain-research-team)

Goal: evaluate available documents (business glossary / source schemas / data contracts / SLA docs / regulatory specs); understand the industry vertical + the data-stack patterns prevalent in it.

Invoke domain-research-team (use the Skill tool with skill: domain-research-team) with:

{
  "output_kind": "domain-context-map",
  "output_path": "<exploration-dir>/stage-1/DOMAIN_CONTEXT_MAP.md",
  "codebase_inputs": [<inputs.codebase_inputs>],
  "doc_inputs": [<inputs.doc_inputs>],
  "frontend_read_only": false,
  "industry_hint": "<inferred from inputs or null>",
  "completion_promise": "DOMAIN CONTEXT COMPLETE"
}

The domain-research-team skill's mandatory outside-research mandate fires — 3 researchers each perform ≥ 4 queries (industry data architectures / market context / competitor data stacks / authoritative whitepaper). The output_kind: domain-context-map variant adds 3 data-engineering-specific outside-research queries:

• 1 WebSearch on the dominant data-stack patterns in the industry (modern data stack / data mesh / data lakehouse / event-driven / batch-first / etc.).
• 1 WebSearch on common data products + their service patterns.
• 1 WebSearch on regulatory + compliance context (GDPR / HIPAA / SOC2 / PCI-DSS / SOX / FINRA / etc. as relevant).

Stage-1 checklist: every persona / data consumer identified + every regulatory constraint cataloged + industry data-stack patterns documented + competitor stack approach summarized.

Convergence promise: "DOMAIN CONTEXT COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-1 convergence prompt>" --completion-promise "DOMAIN CONTEXT COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 2 — Conceptual data model

Goal: entities + relationships + business rules. Source-of-truth attribution per entity. Identifier semantics. Domain-driven decomposition (bounded contexts / aggregate roots when applicable).

Output: <exploration-dir>/stage-2/CONCEPTUAL_DATA_MODEL.md.

Schema:

{
  "entities": [
    {
      "entity_id": "<kebab-case>",
      "label": "<human-readable>",
      "kind": "fact" | "dimension" | "bridge" | "event" | "document" | "vector" | "raw" | "operational",
      "source_of_truth": "<system | spec | none-yet>",
      "attributes": [
        {
          "name": "<attr>",
          "type": "<type>",
          "constraints": [...],
          "pii_classification": "none" | "pii" | "phi" | "pci" | "internal-confidential",
          "regulatory_scope": ["GDPR-Article-X", "HIPAA", ...]
        }
      ],
      "relationships": [{"to": "<entity-id>", "kind": "...", "cardinality": "..."}],
      "natural_key": "<attr-or-attr-tuple>",
      "surrogate_key": "<attr>",
      "scd_strategy": "type-1" | "type-2" | "type-4" | "n/a"
    }
  ],
  "business_rules": [
    {"rule_id": "<id>", "description": "<one-line>", "evidence": "<file:line or doc citation>"}
  ],
  "bounded_contexts": [
    {"context_id": "<id>", "entities": ["<entity-id>", ...], "rationale": "<one-line>"}
  ]
}

Stage-2 checklist: every entity from Stage 1's domain context represented; every business rule cited to evidence; PII / PHI / PCI classification per attribute; SCD strategy per dimension.

Convergence promise: "DATA MODEL COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-2 convergence prompt>" --completion-promise "DATA MODEL COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 3 — Service design

Goal: decide HOW to service the data model with code. Architectural pattern + tool selection + phenotype dispatch.

Output: <exploration-dir>/stage-3/DATA_SERVICE_DESIGN_MAP.md.

For each subsystem (ingestion / transformation / storage / serving / orchestration):

{
  "subsystem": "ingestion" | "transformation" | "storage" | "serving" | "orchestration",
  "pattern": "ETL" | "ELT" | "streaming" | "batch" | "micro-batch" | "CDC" | "hybrid",
  "tool_choice": "<dbt | Airflow | Dagster | Fivetran | Snowflake | Databricks | Kafka | Flink | Spark | ...>",
  "rationale": "<one-line citing Stage 1 + Stage 4 evidence>",
  "alternatives_considered": [
    {"tool": "<other>", "rejected_because": "<one-line>"}
  ]
}

Phenotype dispatch (per ## Phenotype convergence rules v3.5.0)

Stage 3 MUST consult common-pipeline-conventions/SKILL.md ## Phenotype convergence rules (v3.5.0) before proposing phenotypes:

• Always propose config-management for the IaC layer (Snowflake account / Airflow MWAA / Kafka MSK / Databricks Workspace / etc. all fit the OpenTofu monorepo pattern).
• Co-propose ai-management + user-management when the data eng work feeds an ML/AI product (feature store / vector store / RAG index) OR an analytics API with per-user access controls.
• user-management alone when the data work has an analytics API with auth but no AI surface.

Each phenotype proposal goes through the v2.3.0 domain gate (user confirms via AskUserQuestion).

Stage 3 emits a phenotype_proposals block in the output map:

{
  "phenotype_proposals": [
    {
      "phenotype": "config-management",
      "rationale": "<one-line>",
      "co_seeds": ["<other-phenotype-id>", ...] | []
    }
  ]
}

Stage-3 checklist: every subsystem assigned a pattern + tool; every tool choice has a rationale citing Stage 1 + Stage 4 evidence; phenotype proposals consult the convergence rules.

Convergence promise: "SERVICE DESIGN COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-3 convergence prompt>" --completion-promise "SERVICE DESIGN COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 4 — Volume + velocity analysis

Goal: quantify the data volume + velocity profile. Capacity sizing + cost envelope.

Output: <exploration-dir>/stage-4/VOLUME_VELOCITY_ANALYSIS_MAP.md.

For each entity from Stage 2:

{
  "entity_id": "<from-stage-2>",
  "volume": {
    "current_rows": <int-or-estimate>,
    "growth_rate_per_year": "<percentage or rows/year>",
    "3_year_projection_rows": <int>,
    "cardinality_concerns": ["<high-cardinality-attribute>", ...]
  },
  "velocity": {
    "arrival_pattern": "batch" | "micro-batch" | "streaming" | "cdc",
    "freshness_sla": "<seconds | minutes | hours | days>",
    "peak_qps_or_rps": <int>,
    "steady_state_qps_or_rps": <int>
  },
  "capacity_sizing": {
    "storage_tier_recommendation": "hot" | "warm" | "cold" | "archival",
    "estimated_storage_gb_3yr": <int>,
    "estimated_compute_envelope": "<warehouse credits | cluster hours | function invocations>",
    "estimated_monthly_cost_usd_envelope": "<low-mid-high>"
  }
}

Stage-4 checklist: every entity has volume + velocity assigned; 3-year growth projection per entity; capacity sizing per entity; cost envelope reasonable for Stage 3's tool choices.

Convergence promise: "VOLUME VELOCITY COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-4 convergence prompt>" --completion-promise "VOLUME VELOCITY COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 5 — Data security

Goal: PII / PHI / PCI classification (refining Stage 2's per-attribute classification with whole-entity context); encryption + access control patterns; regulatory considerations; retention + right-to-be-forgotten plan.

Output: <exploration-dir>/stage-5/DATA_SECURITY_MAP.md.

{
  "per_entity_security": [
    {
      "entity_id": "<from-stage-2>",
      "sensitivity_classification": "public" | "internal" | "confidential" | "restricted" | "regulated",
      "regulatory_scopes": ["GDPR", "HIPAA", "PCI-DSS", "SOC2", ...],
      "encryption_at_rest": "tier-1" | "tier-2" | "tier-3",
      "encryption_in_transit": "TLS-1.3+ required" | "mTLS required" | "VPC-only",
      "access_control_pattern": "row-level" | "column-level" | "dynamic-data-masking" | "role-based" | "attribute-based",
      "retention_policy": "<duration + automatic-purge rule>",
      "rtbf_plan": "<right-to-be-forgotten implementation strategy>"
    }
  ],
  "audit_logging_requirements": [
    {"event": "<access | modify | export>", "metadata_captured": [...], "retention": "<duration>"}
  ]
}

Stage-5 checklist: every entity has a security classification; every PII / PHI / PCI attribute has encryption + access control assigned; retention policies cite regulatory scopes; audit logging requirements cover access + modify + export.

Convergence promise: "DATA SECURITY COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-5 convergence prompt>" --completion-promise "DATA SECURITY COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 6 — MANDATORY validation + lineage + observability (the v3.5.0 non-negotiable)

Goal: every transformation MUST carry data validation rules. Every record MUST be end-to-end traceable. Aggregate AND per-endpoint metrics MUST be captured. Per the user prose: "by default any data engineering pipelines should have strong data validation components and logging to ensure every records transform and modification, in aggregate and by endpoint, should be properly traced."

Canonical home: common-pipeline-conventions ## Data engineering exploration discipline (v3.5.0) — this stage operationalizes that discipline for the exploration deliverable.

Output: <exploration-dir>/stage-6/DATA_VALIDATION_LINEAGE_MAP.md.

Per-transformation validation rules

For every transformation step identified in Stage 3 (every dbt model / Airflow task / Kafka stream processor / Flink job / etc.):

{
  "transformation_id": "<unique-id>",
  "transformation_kind": "dbt-model" | "airflow-task" | "stream-processor" | "spark-job" | "fivetran-mapping",
  "input_entities": ["<entity-id>", ...],
  "output_entities": ["<entity-id>", ...],
  "validation_framework": "great-expectations" | "dbt-tests" | "soda" | "great-expectations-with-pandas-profiling" | "deequ",
  "validation_rules": [
    {
      "rule_id": "<id>",
      "scope": "row" | "table" | "column" | "join",
      "rule": "<not-null | unique | accepted-values | range | regex | custom-sql>",
      "severity": "blocker" | "warning" | "info",
      "alert_channel": "<pagerduty | slack | email | log-only>"
    }
  ]
}

Every transformation MUST have ≥ 1 blocker-severity rule. A transformation with zero blocker rules fails the Stage-6 convergence check.

Bauplan-armed rule emission (gated on the arming verdict; a no-op when unarmed)

This is the strongest single fit in the CT6 injection surface: this stage already mandates the rule, and bauplan-data-quality-checks emits exactly that rule. When the calling run armed the optional bauplan lakehouse capability — the data-eng-pipeline lane resolves this at its D−1 Part C and records the verdict at <workspace>/.architect-team/data-eng/<slug>/bauplan-arming.json; absent such a verdict this stage is unarmed, which is the default and the ordinary case — dispatch bauplan-data-quality-checks per transformation and take the expectations.py it generates as the implementation of the ≥ 1 blocker-severity rule the mandate above requires.

The mandate itself does not move, per common-pipeline-conventions ## Augment-never-replace discipline (v3.63.0): every rule is still authored into DATA_VALIDATION_LINEAGE_MAP.md in the schema above, a transformation with zero blocker rules still fails the Stage-6 convergence check, the six-item checklist below is unchanged, and the convergence promise still gates the stage. The bauplan skill supplies a how for an obligation this stage states independently of it — it never becomes the obligation, and a transformation it emitted no rule for is still a transformation with zero blocker rules. When the run is not armed, this stage reads no Bauplan instruction and authors its validation rules exactly as it does today.

End-to-end lineage tracking

{
  "lineage_framework": "openlineage" | "marquez" | "datahub" | "dbt-manifest" | "manual",
  "granularity": "table-level" | "column-level",
  "emission_points": [
    {"emission_point": "<DAG-task or model name>", "captures": ["input-tables", "output-tables", "transformation-sql"]}
  ],
  "consumer_systems": ["analytics-warehouse", "BI", "ML-feature-store", "downstream-API"]
}

Aggregate metrics (per-source / per-table / per-DAG)

{
  "aggregate_metrics": [
    {
      "metric_id": "<id>",
      "scope": "per-source" | "per-table" | "per-DAG" | "per-stream",
      "metric_name": "rows-processed" | "error-count" | "null-rate" | "freshness-lag" | "processing-duration" | "cost-per-run",
      "collection_frequency": "per-run" | "per-window" | "per-emit",
      "storage": "<metrics-store | warehouse table | OpenTelemetry collector>"
    }
  ]
}

Per-endpoint metrics

{
  "per_endpoint_metrics": [
    {
      "endpoint_id": "<analytics-API-endpoint | downstream-consumer-id>",
      "metric_name": "query-latency-p95" | "query-frequency" | "error-rate" | "freshness-SLA-achievement",
      "alerting_threshold": "<value + comparator>"
    }
  ]
}

Anomaly detection + alerting

{
  "anomaly_detection": {
    "baseline_source": "<reference to Stage 4 volume + velocity>",
    "drift_rules": [
      {"metric": "<aggregate or per-endpoint metric>", "drift_threshold": "<spec>", "severity": "page | queue | log"}
    ],
    "schema_drift_handling": "fail-job" | "alert-and-continue" | "auto-evolve"
  }
}

Stage-6 checklist (the non-negotiables):

1. Every transformation has ≥ 1 blocker-severity validation rule.
2. Lineage framework selected + granularity declared + emission points enumerated.
3. Aggregate metrics defined per-source / per-table / per-DAG.
4. Per-endpoint metrics defined for every analytics API endpoint OR downstream consumer.
5. Anomaly detection cites Stage 4 baselines.
6. Alerting severities + channels assigned.

Failures iterate until every checklist item passes — Stage 6 is the gate the v3.5.0 mandate enforces.

Convergence promise: "VALIDATION LINEAGE COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-6 convergence prompt>" --completion-promise "VALIDATION LINEAGE COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Stage 7 — OpenSpec authoring

Goal: author the OpenSpec change via openspec-propose (NEVER hand-written) consuming Stages 1-6 outputs.

The change carries:

• proposal.md summarizing the data architecture + transformation strategy + validation/lineage plan.
• specs/ capturing every entity, every transformation, every validation rule as REQs (the Stage 6 validation rules become explicit Phase 1 acceptance criteria).
• design.md with reuse-first decisions (per reuse-first-design) + phenotype seed list + tool selection rationale.
• tasks.md decomposing the implementation by subsystem (ingestion / transformation / storage / serving / orchestration).

Each spec REQ MUST cite at least one Stage 1-6 source for traceability.

Stage-7 checklist: OpenSpec validates strict; every Stage 2 entity → REQ; every Stage 6 validation rule → acceptance criterion; phenotype seed proposals routed via domain gate; tasks decomposed.

Convergence promise: "OPENSPEC AUTHORING COMPLETE". Wrapped in the canonical flag form /ralph-loop "<stage-7 convergence prompt>" --completion-promise "OPENSPEC AUTHORING COMPLETE" — loops until the promise fires; no iteration cap (v3.8.0 unbounded solving).

Phase D8 — Return verdict

Return to the caller:

{
  "exploration_id": "<...>",
  "stage_artifacts": {
    "domain_context_map_path": "<...>",
    "conceptual_data_model_path": "<...>",
    "data_service_design_map_path": "<...>",
    "volume_velocity_analysis_map_path": "<...>",
    "data_security_map_path": "<...>",
    "data_validation_lineage_map_path": "<...>",
    "openspec_change_path": "<...>"
  },
  "summary": {
    "entities_count": N,
    "transformations_count": N,
    "validation_rules_count": N,
    "phenotypes_proposed": [...],
    "data_eng_classification": "<from-inputs>"
  }
}

Disciplines this skill respects

• v3.0.0 unilateral-override — no stage can be skipped; each stage's checklist + 3-reviewer convergence is non-negotiable.
• v0.9.19 3-reviewer convergence — the canonical pattern per stage.
• v3.5.0 phenotype convergence rules — Stage 3 phenotype proposals consult the rules table.
• visual-to-api-design operating rules — applied verbatim to this skill (stages frozen in order, checklists gate freezes, read-only on source, cross-stage references by SHA, no deferral, OpenSpec via openspec skill, etc.).
• The mandatory validation + lineage + observability defaults (Stage 6) — non-negotiable by user mandate.

What this skill is NOT

• Not a data-engineering implementer — produces the OpenSpec change (the planning layer); Phase 2 of the architect-team-pipeline implements it.
• Not a fix loop — gaps surface to the caller as SRs via api-design-stage-incomplete origin kind; this skill doesn't file them itself.
• Not a substitute for visual-to-api-design — when the work is a REST API derived from a UI, Phase 0a handles that. This skill handles the data plane.
• Not greenfield from nothing — at least one of codebase / docs / upstream API contract MUST be provided. Pure greenfield data eng work falls through to Phase 0 plain-branch authoring.