{
  "schema_version": 1,
  "skill_name": "data-architect",
  "evals": [
    {
      "id": "quickscan-assessment",
      "prompt": "My team's data pipelines keep breaking, the cloud bill is climbing without explanation, and nobody agrees on what 'customer' means across our reports. I do not know where to start. Run a quick scan of our data organization and tell me what the top gaps are.",
      "expected_output": "A quick-scan assessment that turns the symptoms into a structured gap list: the response walks the five-minute scan over the common failure areas \u2014 pipeline reliability (how data is loaded, where failures happen and whether they are detected), cost (where spend concentrates, whether compute is runaway or rightsized), definitions (whether 'customer' and other core entities are defined once or redefined per report), ownership (who owns each pipeline and what happens when it breaks), and trust (whether anyone can trace a number on a dashboard to its source). It maps each symptom to the likely root gap: breaking pipelines to missing ownership and observability, climbing bills to ungoverned compute, and the customer disagreement to a missing shared semantic layer. It ends with the prioritized gaps and the first concrete action for each.",
      "assertions": [
        "The scan covers pipeline reliability, cost, definitions, ownership, and trust",
        "Each symptom is mapped to a likely root gap rather than a superficial fix",
        "The 'customer' disagreement is tied to a missing shared definition layer",
        "The output is a prioritized gap list with a first concrete action per gap",
        "The response reflects a quick scan and names the deeper review each gap needs"
      ]
    },
    {
      "id": "pipeline-architecture-review",
      "prompt": "We ingest events from our app into a warehouse through a chain of scripts, transform them in the database, and export dashboards. The pipeline works but takes 14 hours and fails often. Review this architecture and tell me what should change.",
      "expected_output": "An evidence-aware review of the reported 14-hour runtime and failures. Investigate orchestration, retries, query execution, incremental processing, and quality gates without assuming these are absent or that database transforms are inherently wrong. Prioritize changes supported by observations, propose bounded checks for uncertain causes, and retain working components.",
      "assertions": [
        "Separates observed runtime and failures from hypothesized causes",
        "Investigates retries, monitoring, incremental processing, and query execution without claiming missing features as facts",
        "Makes proposed architecture changes conditional on evidence of bottlenecks",
        "Prioritizes next checks and changes by failure and runtime impact",
        "Retains working components when no evidence supports replacing them"
      ]
    },
    {
      "id": "platform-decision-framework",
      "prompt": "We need a data platform and are torn between using our existing Postgres for everything, adopting a cloud warehouse, and a newer lakehouse stack. The team has different opinions and the vendors are pushing hard. How do I make this decision properly?",
      "expected_output": "A decision framework that defers the platform choice until the requirements are understood: the response identifies the decisions the platform must serve \u2014 the workloads (analytics, ML feature access, real-time versus batch), the data volumes and concurrency, the team's skills and operating capacity, and the future direction (lakehouse expansion, streaming). It frames the comparison across the named options on those requirements, including the total cost of ownership (licensing, compute, storage, and the people cost of operating each), and it exposes the vendor-pressure dynamic by grounding the choice in the workload evidence rather than platform enthusiasm. The framework produces a recommendation with the conditions under which the other options would win, and a pilot or proof-of-value step before commitment. It explicitly warns against picking the platform to avoid a later decision.",
      "assertions": [
        "The framework defines the workloads, volumes, concurrency, and team capacity before comparing platforms",
        "Options are compared on total cost of ownership including operating people-cost",
        "The choice is grounded in workload evidence rather than vendor momentum",
        "The recommendation includes the conditions under which each alternative would win",
        "A pilot or proof-of-value step precedes the commitment"
      ]
    },
    {
      "id": "semantic-layer-governance",
      "prompt": "Marketing reports revenue one way, finance reports it another, and the two numbers are different by 12%. I need to fix the definitions and stop the argument. How do I set up a governed semantic layer without freezing all data work?",
      "expected_output": "A semantic-layer governance design that treats definitions as owned artifacts: the response establishes a single source of truth for core metrics (revenue, customer, active user) with a written definition, the calculation, and the owner for each, and it explains the governance model \u2014 definitions change through a review process with recorded rationale rather than per-report improvisation. The migration path keeps work moving: the semantic layer is introduced for the disputed metrics first, reports are migrated one at a time with a comparison period showing the old and new numbers side by side, and the legacy report is retired only when it matches. The response explains the 12% difference by identifying the likely divergence points (inclusion of refunds, definition of the reporting period, deduplication rules) and prescribes documenting those as part of the definition.",
      "assertions": [
        "Core metrics get a written definition, calculation, and named owner",
        "Definitions change through a review process, not per-report improvisation",
        "Migration is incremental with side-by-side comparison before retiring the legacy report",
        "The 12% divergence is diagnosed against likely divergence points such as refunds and period definitions",
        "The governance model prevents the argument from restarting without freezing data work"
      ]
    },
    {
      "id": "strategy-roadmap",
      "prompt": "Our data team spends all its time firefighting broken pipelines and has no time to build the analytics the business is asking for. Leadership wants a data strategy. What should the strategy and roadmap contain, and how do we get out of firefighting?",
      "expected_output": "A data strategy that addresses the firefighting trap structurally: the response frames the strategy around the outcomes the business needs and the capabilities required, then sequences a roadmap that first stabilizes the foundation \u2014 ownership for the broken pipelines, observability so failures are detected and repaired fast, and the quick wins that stop the most frequent incidents \u2014 before adding net-new analytics. The roadmap is phased with explicit criteria for moving from one phase to the next: the firefighting load must fall below a threshold, not just a calendar date. It covers the governance and staffing implications (who owns the platform, how new requests are triaged), and it names the metrics that show the strategy working: incident rate, time-to-repair, on-time analytics delivery. The response resists a roadmap that schedules the new analytics first while the foundation stays broken.",
      "assertions": [
        "The strategy is organized around business outcomes and required capabilities",
        "The roadmap sequences foundation stabilization before net-new analytics",
        "Phase transitions are gated on firefighting-load criteria, not calendar dates",
        "Ownership, triage, and governance implications are addressed",
        "The metrics showing the strategy works are named, including incident rate and delivery"
      ]
    },
    {
      "id": "mesh-readiness-decision",
      "prompt": "Our central data team is overloaded, and leadership wants us to adopt data mesh. Three product teams own useful source data, but none has a funded support rotation, our catalog is incomplete, and the platform team is already committed to a warehouse migration. Assess whether we should adopt mesh now and give us a safe next step.",
      "expected_output": "A readiness-based recommendation that separates the central bottleneck from mesh prerequisites. It assesses domain authority, product accountability, consumer demand, self-service platform capacity, federated governance, and sponsorship; identifies missing evidence and operating load; and recommends a bounded pilot or centralized/hybrid foundation rather than treating mesh as the default. The plan has entry and exit evidence, named owners, and a stop condition.",
      "assertions": [
        "The recommendation assesses domain ownership, product accountability, platform capacity, governance, and team funding",
        "Missing support rotations, incomplete catalog coverage, and platform overload are treated as readiness gaps",
        "The response does not present data mesh as the universal or inevitable target",
        "The next step is bounded and includes measurable exit evidence and a stop condition",
        "The response distinguishes architecture decisions from platform implementation work"
      ]
    },
    {
      "id": "event-driven-product-recovery",
      "prompt": "Orders are published as events for fraud analytics and a customer-facing order timeline. We want to replace our nightly export with a streaming data product. Design the architecture decisions we must make before implementation, including snapshots, replay, duplicate and late events, ordering, compatibility, and consumer recovery.",
      "expected_output": "An architecture design that distinguishes the event stream from a current snapshot and historical analytical view, records event and publication time and ordering scope, defines producer and consumer responsibilities, and specifies duplicate, late, delayed, unavailable, poison, replay, backfill, compatibility, and recovery behavior. It routes formal event contract syntax to api-design-and-evolution and implementation to data-engineering, while still making the product's required semantics and service expectations explicit.",
      "assertions": [
        "The design distinguishes events, snapshots, and historical analytical data",
        "Duplicate, late, delayed, unavailable, poison, replay, and backfill behavior is addressed",
        "Ordering scope and event-time versus publication-time semantics are made explicit",
        "Compatibility and deprecation decisions are included without pretending schema additivity guarantees consumer safety",
        "Interface contract semantics and pipeline implementation are routed to the neighboring skills"
      ]
    },
    {
      "id": "pattern-landscape-choice",
      "prompt": "We have a regulated finance workload, a growing ML team, several operational systems, and analysts who need governed SQL. One group proposes a lakehouse, another says data mesh, and a vendor is selling a data fabric. Compare centralized warehouse, lakehouse, fabric capabilities, mesh, and a hybrid design and recommend how to choose.",
      "expected_output": "A workload- and organization-grounded comparison that explains what each pattern solves, the obligations it introduces, and where it can fail. It distinguishes fabric capabilities from the mesh ownership model, recognizes that a hybrid can be deliberate, accounts for regulation, ML, SQL serving, skills, governance, latency, portability, and operating cost, and recommends evidence or a pilot before commitment.",
      "assertions": [
        "All five candidate shapes are compared by workload, ownership, governance, skills, and operating obligations",
        "Data fabric is distinguished from data mesh rather than treated as a synonym",
        "A hybrid design is evaluated as a deliberate option with explicit boundaries",
        "The recommendation accounts for regulated serving, ML, governed SQL, and portability",
        "The decision includes a pilot or evidence plan instead of relying on vendor positioning"
      ]
    },
    {
      "id": "architecture-design-session",
      "prompt": "Facilitate a design session for a company deciding whether customer behavior should be a domain-owned data product. We need current and target flows, workloads, candidate patterns, decisions, assumptions, experiments, owners, and handoffs.",
      "expected_output": "A completed or clearly structured design-session artifact with a session brief, current source-to-consumer flow and ownership, workload table, candidate comparison, chosen scope and rationale, governance and quality expectations, explicit assumptions and evidence gaps, experiments with owners and exit evidence, transition phases, open risks, and handoffs to API, data-engineering, and platform-engineering owners.",
      "assertions": [
        "The artifact includes current flows, ownership, consumers, and workload freshness or latency needs",
        "Candidate patterns include centralized, hybrid, fabric, mesh, and event-driven options where relevant",
        "Decisions, assumptions, evidence gaps, experiments, owners, and exit criteria are separately captured",
        "The artifact names interface, implementation, and platform-operations handoffs",
        "The session does not skip unresolved risks by forcing premature consensus"
      ]
    },
    {
      "id": "boundary-event-contract",
      "prompt": "Write the Avro schema and compatibility policy for our customer-events Kafka topic, including serializer settings and consumer versioning.",
      "expected_output": "A routing response that explains this is an interface-contract and implementation task rather than a data-architecture strategy review. It routes schema, serialization, compatibility, and consumer versioning to api-design-and-evolution and Kafka or pipeline implementation to data-engineering, while optionally stating the architecture context that would be needed before making broader product or platform decisions.",
      "assertions": [
        "The response does not attempt to author the requested schema or serializer configuration as a data-architect output",
        "Event contract and compatibility semantics route to api-design-and-evolution",
        "Kafka or pipeline implementation routes to data-engineering",
        "The boundary is explained in terms of architecture strategy versus interface and implementation ownership"
      ]
    },
    {
      "id": "federated-computational-governance",
      "prompt": "Design an operating method for federated computational governance in a data mesh. It must distinguish enterprise interoperability and policy rules from domain-local rules, explain how rules are encoded and enforced, preserve execution evidence, handle temporary exceptions, assign remediation ownership, and turn recurring exceptions into better standards.",
      "expected_output": "A compact original operating method that uses a two-layer rule model and an evidence feedback loop. It defines global versus domain-local rule authority, gives machine-readable rule fields and execution points, records per-run evidence, makes exceptions scoped and time-bounded with expiry handling, assigns remediation to domain, platform, or federated governance owners, and describes a recurring review that converts patterns into standards or an explicit decision not to standardize. It remains an architecture operating model rather than a vendor-specific implementation guide.",
      "assertions": [
        "Global rules explicitly cover shared interoperability and enterprise policy while domain rules cover local meaning and operating choices",
        "Rules have versioned machine-readable encoding and named execution or enforcement points",
        "Evidence records link the rule version and product or release to the evaluation result, time, and remediation reference",
        "Exceptions are scoped, approved, compensating-control aware, and time-bounded with expiry or renewal handling",
        "Remediation ownership is assigned across domain product, platform enforcement, and federated governance responsibilities",
        "Recurring failures or exceptions feed a review that proposes a global rule, domain rule, platform control, or explicit non-standardization decision"
      ]
    },
    {
      "id": "bounded-transactional-store",
      "prompt": "Two engineers maintain a small order service on PostgreSQL. We need atomic order and inventory updates at 30 writes/sec and have no analytics consumer. Compare keeping it with adopting a lakehouse. Give a practical recommendation.",
      "expected_output": "Recommend retaining the transactional store subject to verifying workload requirements, with concrete tradeoffs and a bounded validation plan.",
      "assertions": [
        "Leads with the recommendation or relevant workload tradeoffs rather than persona or a maturity questionnaire",
        "Uses atomic updates and operational ownership as decision criteria",
        "Does not introduce a warehouse or mesh without a consumer requirement",
        "Names evidence needed to validate workload fit rather than asserting capacity from write rate alone"
      ]
    },
    {
      "id": "recovery-deletion-evidence",
      "prompt": "We have nightly backups of our transactional evidence store. Records deleted after a backup must not reappear to users after restoring it. What must our architecture decision address before we claim recovery works?",
      "expected_output": "Define deletion continuity across restoration, verification before serving, and evidence needed from a rehearsal.",
      "assertions": [
        "Distinguishes backup existence from tested recovery evidence",
        "Addresses a deletion record or equivalent mechanism that survives restoring an older snapshot",
        "Requires reapplying deletions before reopening access and checking deleted records remain absent",
        "Routes PostgreSQL recovery implementation to postgres instead of supplying an operations runbook"
      ]
    },
    {
      "id": "conditional-choice-no-questionnaire",
      "prompt": "We already operate Postgres for a 20 GB transactional application with two maintainers. We need a store choice this week, but peak concurrency is unknown. Help us decide without running a company-wide maturity assessment.",
      "expected_output": "Provide a conditional workload-grounded recommendation and a focused check of concurrency.",
      "assertions": [
        "Uses supplied context without requesting a broad inventory or maturity score",
        "Names peak concurrency as an uncertainty and a bounded way to measure its effect",
        "Includes maintainability and the cost of introducing another platform",
        "States a condition that would change the recommendation"
      ]
    }
  ]
}

# Data Architecture Anti-Patterns Catalog

A catalog of named anti-patterns with symptoms, root causes, and remediations.
Sources: Sunil Prakash (Enterprise Data Architecture), Kengie Ho (Common Enterprise Architectural Pitfalls), DataForest, and industry experience.

## 1. Silver Bullet Thinking

**Symptom:** Adopting Data Mesh, Data Fabric, or another pattern because it's trendy, without organizational readiness.

**Root cause:** Believing an architectural pattern alone solves what are fundamentally organizational problems (ownership, trust, skills).

**Remediation:** Before adopting Data Mesh, verify you have: domain teams willing to own data products, a platform team to build self-serve infra, and executive sponsorship for federated governance. If any is missing, start smaller.

## 2. SoR vs SSoT Confusion

**Symptom:** Business leaders point to a legacy 20-year-old billing system and say "That's our Single Source of Truth." Modern apps are forced to dumb down their data to fit the legacy system's narrow schema.

**Root cause:** Confusing a transactional System of Record (SoR) — where data is originally authored — with a Single Source of Truth (SSoT) — an aggregated, reconciled, governed view across the enterprise.

**Remediation:** Decouple the two domains. Let operational systems be localized SoRs for their domain. Build a dedicated layer (MDM platform, data lakehouse, or data mesh) above them that ingests from every SoR, resolves conflicts, applies quality rules, and serves as the true SSoT.

## 3. High-Fidelity / Lossy Sync Destruction

**Symptom:** A modern CRM with rich, structured data is synced bidirectionally with a legacy ERP. The ERP truncates names, flattens addresses, drops extra fields — then syncs that flattened data back to the CRM, permanently overwriting the high-fidelity records.

**Root cause:** Allowing a low-fidelity system to blindly update a high-fidelity system through lossy synchronization. Known formally as semantic heterogeneity, information loss in schema mapping, and destructive overwrites.

**Remediation:** Place an integration middleware or MDM platform at the system boundary with **metadata-driven field-level survivorship**. Apply hierarchical rules: "The ERP always wins financial fields, but the CRM always wins customer contact info." Build an Anti-Corruption Layer (from Domain-Driven Design) to protect modern systems from legacy constraints.

## 4. Fragmented Identity / "Customer 720"

**Symptom:** Customer logs in with email (System A), calls support as "Jane Doe" (System B). Without a shared primary key, the integration layer creates a duplicate record. Instead of Customer 360, you get "Customer 720."

**Root cause:** Relying on simple SQL JOINs or exact-match keys for entity resolution across systems that have no common identifier.

**Remediation:** Introduce an Identity Resolution Engine at the data ingestion layer. Use two-stage matching:
- **Deterministic:** exact matches on hashed email, SSN, phone
- **Probabilistic:** ML-based matching (Levenshtein distance, shared device IPs, behavioral patterns) with confidence scoring

Treat identity resolution as a foundational requirement, not a downstream analytics problem.

## 5. Schema Evolution Mismatch

**Symptom:** A modern microservices team splits `FullName` into `FirstName`/`LastName`. A downstream legacy app consuming the data stream crashes because it expects the old schema.

**Root cause:** Speed mismatch between agile schema evolution and rigid legacy consumers. Tightly coupled interfaces without mediation.

**Remediation:** Introduce an Anti-Corruption Layer (ACL) between modern and legacy systems. The ACL translates schemas at the boundary — the modern system evolves freely, the ACL provides backward-compatible views to legacy consumers.

## 6. The Ivory Tower

**Symptom:** Architecture team produces standards, roadmaps, and reference architectures in isolation, without engaging delivery teams or business stakeholders. Artifacts are technically correct but irrelevant to actual problems.

**Root cause:** Architecture practiced as a detached design discipline rather than an embedded consulting function.

**Remediation:** Embed architects with delivery teams. Architecture decisions should emerge from concrete problems, not abstract frameworks. The architect who writes a standard without understanding the team's actual constraints is building shelfware.

## 7. Tool-First Architecture

**Symptom:** Organization picks Snowflake (or Databricks, or Kafka) and then goes looking for problems to solve with it.

**Root cause:** Technology selection driven by vendor marketing, hiring availability, or executive preference rather than requirements analysis.

**Remediation:** Start with business requirements, data profiles, and consumer needs. Let the architecture drive the tool selection, not the other way around. Run POCs against real workloads before committing.

## 8. Governance as an Afterthought

**Symptom:** "We'll add governance later." Data proliferates uncontrolled for 18 months. When governance finally arrives, it requires a multi-quarter cleanup effort and meets resistance from teams accustomed to full autonomy.

**Root cause:** Treating governance as a phase rather than a design constraint.

**Remediation:** Establish minimum viable governance from day one: data classification, ownership tagging, basic lineage. Add rigor incrementally as the platform matures. Governance that grows with the system is adopted; governance imposed after the fact is rejected.

## 9. Premature Petabyte

**Symptom:** Designing for "petabyte scale" when the organization has 2TB of data. Complex, expensive architecture that delivers no marginal benefit for years.

**Root cause:** Architects designing for resumes rather than actual problems. Future-proofing without understanding the cost of complexity.

**Remediation:** Design for 10x growth, not 1000x. Use architectures that scale horizontally without requiring upfront complexity. The cloud warehouse patterns (Snowflake, BigQuery) handle growth without premature optimization.

## 10. Streaming Everything

**Symptom:** Kafka for every data movement, even daily batch reports. Streaming infrastructure cost exceeds the value of real-time data.

**Root cause:** Assuming "real-time is better" without quantifying the latency requirement. Conflating event-driven architecture with streaming infrastructure.

**Remediation:** Require a concrete latency SLA before introducing streaming. If the business can tolerate 15-minute latency, micro-batch is sufficient. If it can tolerate hourly, batch is fine. Keep streaming infrastructure for the subset of pipelines that genuinely need sub-second delivery.

## 11. Copy-Paste FAANG Architecture

**Symptom:** Replicating the data infrastructure patterns of Google, Netflix, or Uber without accounting for your organization's actual scale, budget, team capability, or problem complexity.

**Root cause:** Assuming that what works at hyperscale is optimal at any scale. FAANG patterns evolved to solve FAANG-scale problems — not general problems.

**Remediation:** Choose the simplest architecture that meets your actual requirements. Managed services (Snowflake, BigQuery, Fivetran, dbt Cloud) are often the right choice for teams of 5-50, even if they wouldn't scale to FAANG levels. Complexity should be earned, not inherited.

## 12. Design by Committee

**Symptom:** Architecture that tries to accommodate every stakeholder's preference and ends up satisfying no one. Compromised designs that mix incompatible patterns.

**Root cause:** Architecture by consensus rather than by clear ownership and decision rights.

**Remediation:** Assign clear decision authority for architecture decisions. Solicit input from stakeholders, but the architect makes the final call with documented rationale. ADRs (Architecture Decision Records) make this explicit: "We considered X, Y, Z. We chose X because... The tradeoffs are..."

## 13. Neglecting the Team

**Symptom:** Designing a technically elegant system that the team cannot operate, troubleshoot, or extend. High "bus factor" on the architecture itself.

**Root cause:** Designing for technical purity rather than organizational reality.

**Remediation:** Factor in team size, skill level, and operational maturity. A simple architecture operated well is better than a clever architecture nobody understands. Build runbooks, invest in training, and design for debuggability.

# Data Architecture Patterns — Decision Framework

A practical guide to choosing among warehouse, lakehouse, fabric, mesh, and hybrid shapes. Choose a pattern from workload, organization, governance, skills, latency, and portability evidence rather than from a label.

## Decision Tree — Which Pattern to Use?

```mermaid
flowchart TD
    Q1["What's your primary use case?"] --> Q1a{Analytics/BI<br>or ML/AI?}
    Q1a -->|Analytics & BI| Q2["What's your organizational context?"]
    Q1a -->|ML/AI + Analytics| Q5["Do you have a dedicated<br>infrastructure engineering team?"]
    
    Q2 -->|Startup or small team| A[Kimball + dbt<br>on managed cloud warehouse]
    Q2 -->|Enterprise, complex<br>cross-domain integration| Q3["Can you invest in upfront design<br>and wait longer for value?"]
    Q2 -->|Regulated industry<br>banking, insurance, healthcare| B[Data Vault 2.0]
    
    Q3 -->|Yes, we have time<br>and budget| C[Inmon: normalized EDW<br>+ derived data marts]
    Q3 -->|No, need iterative<br>delivery| D[Hybrid: Inmon staging layer<br>+ Kimball marts]
    
    Q5 -->|Yes → we have infra engineers| E[Lakehouse: open formats,<br>multiple compute engines]
    Q5 -->|No → we want managed| F[Cloud warehouse + dbt<br>Snowflake / BigQuery / Redshift]
    
    style A fill:#e3f2fd,stroke:#1565c0
    style B fill:#fce4ec,stroke:#c62828
    style C fill:#f3e5f5,stroke:#6a1b9a
    style D fill:#e8f5e9,stroke:#2e7d32
    style E fill:#fff3e0,stroke:#e65100
    style F fill:#e0f7fa,stroke:#00695c
```

**Quick summary:**
- **Regulated, audit-heavy** → Data Vault 2.0
- **Startup, need speed** → Kimball + dbt
- **Enterprise, big investment** → Inmon or hybrid
- **ML + analytics from same data** → Lakehouse
- **Frequent source schema changes** → Data Vault
- **Many domains need governed, discoverable products** → Consider mesh only after the readiness checks in `data-mesh-readiness-and-operating-model.md`
- **Many tools need shared discovery and policy across distributed systems** → Consider fabric capabilities or a hybrid, not automatically mesh

## Kimball (Dimensional / Bottom-Up)

**Philosophy:** Build data marts for individual business processes first. The enterprise warehouse emerges from integrating marts via conformed dimensions.

**Core artifacts:** Fact tables (measurements: revenue, clicks, orders) + Dimension tables (context: customers, products, dates) arranged in star schemas.

**Strengths:**
- Business users can query marts directly with minimal SQL
- Fast delivery — build one domain at a time
- Maps naturally to BI tool concepts (dimensions as filters, facts as measures)

**Weaknesses:**
- Conformed dimensions are hard to maintain as marts proliferate
- Inconsistent grain definitions across marts create data discrepancies
- Bottom-up can produce marts that are hard to integrate later for cross-domain questions

**When to choose:**
- Startup or small team building the first warehouse
- Business intelligence and reporting are the primary use case
- Need fast time-to-value per business domain
- Using modern tooling (dbt on Snowflake/BigQuery/Redshift)

## Inmon (3NF / Top-Down / Corporate Information Factory)

**Philosophy:** Design a normalized, subject-oriented, integrated enterprise warehouse first, then derive data marts as views or aggregates on top.

**Core structure:** Third normal form (3NF) — normalized to eliminate redundancy, subject-oriented (customer, product, transaction rather than source-system-oriented), integrated across all source systems.

**Strengths:**
- Genuine single source of truth enforced at the schema level
- Flexible — can answer questions not anticipated at design time
- Data quality and integration enforced centrally

**Weaknesses:**
- Upfront design effort is substantial
- Time-to-first-insight is much longer than Kimball
- Normalized schemas require complex queries for business users
- Iterative delivery is harder when everything flows from a central schema

**When to choose:**
- Enterprise with complex cross-domain integration requirements
- Data consistency is the highest priority
- Organization has the patience and resources for upfront design
- Regulatory environment demands strict auditability of data lineage

## Data Vault 2.0

**Philosophy:** Decompose every data entity into three types: hubs (business keys only), links (relationships between hubs), and satellites (descriptive attributes with full history).

**Core structure:**
```
hub_customer (customer_hk, customer_id, load_date, record_source)
hub_order (order_hk, order_id, load_date, record_source)
link_customer_order (customer_order_hk, customer_hk, order_hk, load_date)
sat_customer_details (customer_hk, load_date, name, email, segment, hash_diff)
sat_order_details (order_hk, load_date, status, amount, hash_diff)
```

**Strengths:**
- Extremely flexible to schema changes in source systems
- Complete auditability (every record has load date and source)
- Supports parallel loading (hubs, links, satellites load independently)
- Scales to massive heterogeneous data environments

**Weaknesses:**
- High structural complexity — raw vault is not queryable by analysts
- Most consumers need a "business vault" or information mart layer on top
- Tooling and expertise are less common than Kimball or Inmon
- Generally overkill outside regulated enterprise environments

**When to choose:**
- Regulated industry (banking, insurance, healthcare) with strict audit requirements
- Multiple heterogeneous source systems that change frequently
- Historical tracking and data provenance are legal requirements
- Organization has the expertise to operate it

## Lakehouse (Modern Synthesis)

**Philosophy:** Separate storage from compute using open table formats (Apache Iceberg, Delta Lake, Apache Hudi) on object storage (S3, GCS). Multiple compute engines (Spark, Trino, DuckDB, Snowflake, BigQuery) read from and write to the same storage.

**Layer structure:**
```
s3://data-lake/
  bronze/     # Raw, source-aligned (Inmon influence)
  silver/     # Cleaned, validated, integrated
  gold/       # Business-ready, query-optimized (Kimball influence)
```

**Strengths:**
- Decoupled storage and compute — pay for compute only when querying
- Multiple engines serve different use cases from the same data
- Open formats avoid vendor lock-in
- Layer structure combines Inmon's integration discipline with Kimball's query performance
- Serves both SQL analytics and ML pipelines from the same storage

**Weaknesses:**
- More moving parts than a managed warehouse (Snowflake, BigQuery)
- Operational complexity of managing object storage + table format metadata + multiple engines
- "Best of both worlds" marketing often undersells the engineering work required

**When to choose:**
- Team with significant unstructured/semi-structured data
- ML workloads alongside analytics from the same data
- Team has dedicated infrastructure engineers
- Need to avoid vendor lock-in at the storage layer

## Data Fabric (Capability Pattern)

**Philosophy:** Make distributed data easier to discover, govern, connect, and use through shared metadata, policy, lineage, integration, and access capabilities. Fabric is a capability-oriented description, not a mandate to buy a product or centralize every dataset.

**Strengths:**
- Improves discovery and policy consistency across heterogeneous systems
- Can preserve local system ownership while adding shared control points
- Useful where the main constraint is fragmented metadata, access, or integration

**Weaknesses:**
- Can become a tooling program with no improvement in product ownership or consumer outcomes
- Central metadata and policy services add their own availability and stewardship burden
- Vendor claims often blur catalog, integration, governance, and analytics capabilities

**When to choose:**
- The organization has distributed systems but needs common discovery, lineage, policy, or interoperability
- Domain teams are not yet ready to own analytical products independently
- A federated capability layer solves a measured problem without forcing a reorganization

**Do not confuse with mesh:** Fabric emphasizes shared capabilities over ownership model. Mesh requires domain ownership and product accountability; the two can coexist.

## Data Mesh (Operating Model and Product Shape)

**Philosophy:** Organize data ownership around business domains, publish data as products with accountable quality, provide a self-service platform, and apply shared governance through enforceable rules.

**Strengths:**
- Puts context and quality decisions close to the domain that creates the data
- Can reduce a central data team's intake bottleneck when domains have real capacity
- Makes product ownership, consumer needs, and lifecycle decisions explicit

**Weaknesses:**
- Requires domain teams to take on durable product and operational responsibilities
- Multiplies coordination, compatibility, discovery, and governance work
- Fails when mesh language is applied to centrally owned tables without changing accountability

**When to choose:**
- Domain boundaries are meaningful, teams have capacity and authority, and central bottlenecks are evidenced
- Product consumers need dependable, discoverable data with different access modes
- A platform and federated governance investment is affordable and measurable

**When not to choose:**
- Ownership is unclear, domains cannot fund product work, or the platform team is already overloaded
- The problem is only a missing catalog, slow pipeline, or poorly governed warehouse
- A centralized or hybrid design meets the requirements with less coordination risk

## Hybrid Patterns

Hybrid is a deliberate combination, not an admission of design failure. Common forms include a central warehouse with domain-owned products, a lakehouse for engineering and ML with a governed warehouse serving BI, or a shared metadata/policy fabric over systems that retain local ownership.

Choose hybrid when workloads, regulatory controls, or team boundaries genuinely differ. Name the boundary for each data set: who authors it, who transforms it, which plane serves it, where the contract lives, and who pays the operating cost. Reject hybrid when it merely duplicates data across platforms without a consumer, latency, governance, or recovery reason.

## Batch vs Streaming

### Decision Tree — When to Stream?

```mermaid
flowchart LR
    Q1["What latency does<br>the business need?"] --> Q1a{Sub-second?}
    Q1a -->|Yes| Q2["Is the data volume<br>high and continuous?"]
    Q1a -->|No| Q3{"< 1 minute?"}
    
    Q2 -->|Yes| A["Streaming<br>Kafka + Flink"]
    Q2 -->|No| B["Micro-batch<br>Spark Streaming"]
    
    Q3 -->|Yes| B
    Q3 -->|No| C{"< 15 minutes?"}
    
    C -->|Yes| D["Micro-batch<br>Spark / Kafka Connect"]
    C -->|No| E["Batch<br>Airflow / Dagster"]
    
    style A fill:#fce4ec,stroke:#c62828
    style E fill:#e8f5e9,stroke:#2e7d32
```

**Rule of thumb:** Start with batch. Only move to streaming when you have a concrete latency requirement that batch can't meet. "Real-time" is rarely worth the complexity premium.

### Batch (Airflow/Dagster scheduled jobs)
- **Strengths:** Simpler, cheaper, easier to reprocess, well-understood failure modes
- **Weaknesses:** Higher latency (minutes to hours), stale data between runs
- **When:** Reporting, ML training data, any scenario where sub-minute freshness isn't required

### Streaming (Kafka/Flink)
- **Strengths:** Real-time (sub-second), event-driven, supports reactive systems
- **Weaknesses:** Significantly more complex, harder to reprocess, state management challenges, expensive
- **When:** Fraud detection, real-time dashboards, operational alerts, event-driven microservices

### Micro-batch (Spark Streaming, Kafka Connect)
- **Strengths:** Sweet spot for most use cases — seconds to minutes latency without full streaming complexity
- **Weaknesses:** Not truly real-time, batch windows create artificial latency
- **When:** Most enterprise real-time use cases that don't need sub-second

**Rule of thumb:** Start with batch. Only move to streaming when you have a concrete latency requirement that batch can't meet. "Real-time" is rarely worth the complexity premium.

## Star Schema vs Snowflake Schema

### Star Schema
- Denormalized dimensions, fewer joins, simpler queries
- Better BI tool performance
- Preferred for analytics

### Snowflake Schema
- Normalized dimensions, saves storage, enforces integrity
- More complex queries, worse query performance
- Generally not worth the maintenance cost — star is almost always the right choice for analytics

**Verdict:** "Never! — I strongly believe the high maintenance of this outweighs any benefits compared to other methods." Use star schema for analytics. Use 3NF for operational/transactional systems.

## Decision Flow

1. **What's the primary use case?**
   - Analytics/BI → go to 2
   - ML/AI + analytics → lean Lakehouse
   - Transactional/operational → this is OLTP, not data warehousing

2. **What's the organizational context?**
   - Startup/small team → Kimball + dbt on managed cloud warehouse
   - Enterprise, complex integration, can invest upfront → Inmon or hybrid Inmon staging + Kimball marts
   - Regulated, audit-heavy → Data Vault 2.0
   - Has dedicated infra team, ML workloads → Lakehouse

3. **What's the data profile?**
   - Structured, predictable → any pattern works
   - Semi-structured, schema-on-read important → Lakehouse
   - Frequent source schema changes → Data Vault

4. **What's the team capability?**
   - Generalist data team → managed warehouse + Kimball
   - Strong engineering team with infra skills → Lakehouse or Data Vault
   - Small team, need speed → Kimball + dbt

5. **Is the proposed organizational change justified?**
   - Need shared discovery and policy but not domain product ownership → fabric capability or centralized/hybrid design
   - Need domain-owned products and have readiness evidence → mesh may fit
   - Unclear readiness → start with a bounded product pilot and strengthen ownership, quality, and platform foundations first

## Source References

- Ryan Kirsch, "Data Warehouse Architecture Patterns: Kimball, Inmon, and the Modern Lakehouse" — practical comparison with code examples
- TalkingSchema, "Kimball vs Inmon vs Data Vault 2.0: Choose Like an Architect, Not a Fanboy" — decision framework with debunked myths
- Benjamin Tabares Jr., "Data Modelling Frameworks: Understanding Inmon, Kimball, and Data Vault"
- Blockmill, "OBT vs Star Schema vs Data Vault vs Inmon and more" — hybrid approach patterns
- LinkedIn Data Warehousing, "Comparing and Contrasting Three Data Warehouse Design Frameworks"

# Data Architecture Case Studies

Real-world architecture transformations. Sources: Hiflylabs, Datalere, Databricks customer stories, Qubika, and Atlan.

## Commercial Bank: Data Vault 2.0 for Payment Processing

**Source:** Hiflylabs case study
**Context:** A commercial bank needed to modernize its payment processing data warehouse without a full system overhaul.

**Problem:** Legacy payment processing architecture was slow and inflexible. Core payment data processing took 4-5 minutes per run, with the full process chain taking 15-20 minutes. The legacy system couldn't support the bank's evolving business rules.

**Approach:** Rather than rebuilding everything, narrowed focus to two key data sources and three downstream processes. Hybrid approach — bridging the legacy system with Data Vault 2.0 methodology through 15 views and a new hub-link-satellite model on Snowflake.

**Results:**
- Core payment data processing: **4-5 minutes → 10 seconds** (30x improvement)
- Overall process chain: **15-20 min → 8-10 min**
- Enabled parallel processing of satellite tables
- Automation toolkit for Data Vault component generation
- Reverse ETL for historical data migration, legacy systems undisturbed

**Key lesson:** Data Vault doesn't require a big bang. Targeted application to the highest-pain area delivered in two sprints (one month). Automation toolkit meant they could extend to other domains.

## Avant: Lakehouse Modernization (Fintech)

**Source:** Qubika / Databricks
**Context:** Fintech company needed faster credit decisions, smarter marketing, and automated dispute resolution.

**Approach:** Migrated to Databricks lakehouse with Delta Lake, MLflow, Unity Catalog. Built end-to-end ML pipelines.

**Results:**
- **56% increase** in delivery velocity
- **60% reduction** in data initiative costs
- **15+ production ML models**
- **10% faster** model predictions
- Lower default rates, improved cash flow

**Key lesson:** Lakehouse served both analytics and ML from the same platform, eliminating data duplication. Cost reduction came from retiring the legacy stack, not optimizing it.

## Janus Henderson: Hybrid Snowflake + Databricks

**Source:** Datalere (Mark Goodwin, Data Architect at Janus Henderson)
**Context:** Investment firm with both BI/reporting and data science/streaming needs.

**Problem:** Adopted both platforms independently, creating duplication and inconsistent data.

**Approach:** Designed unified architecture with clear ownership boundaries:
- **Databricks** → complex transformations, data science, streaming
- **Snowflake** → BI, reporting, governed analytics

Data flows from Databricks engineering → Snowflake consumption.

**Results:**
- Eliminated data duplication
- Clear ownership per platform
- BI teams got governed, consistent data
- Engineering teams kept flexibility

**Key lesson:** Hybrid works with explicit boundaries and data lifecycle governance. Without those, it's worse than picking one.

## Insulet: Lakehouse for Medical Manufacturing

**Source:** Databricks Data + AI Summit
**Context:** Medical device manufacturer needed to unify Salesforce, SAP, and other data.

**Approach:** Replaced outdated ETL with Lakeflow, Delta Lake for ACID on the lake.

**Results:**
- **12x faster** real-time data processing
- **83% fewer SQL queries** after replacing ETL
- **97% lower TCO** by eliminating third-party ETL tools

**Key lesson:** Biggest win was eliminating expensive middleware entirely, not optimizing it.

## 7-Eleven: AI at 13,000+ Stores

**Source:** Databricks Data + AI Summit
**Context:** Retailer needed AI-driven store insights across a massive footprint.

**Approach:** Multi-agent marketing assistant on Databricks. RAG for maintenance knowledge retrieval. Unity Catalog for governance at scale.

**Results:**
- AI-powered search across all stores
- Technician productivity improved via RAG
- Streamlined governance migration

**Key lesson:** At 13K+ stores, AI isn't optional — it's how you keep per-store costs from growing linearly.

## Additional References

- **Delivery Hero** — Data mesh for multi-market scale. Each market = domain. Result: faster onboarding, required significant platform investment.
- **Intuit** — Data mesh across QuickBooks, TurboTax, Mint. Platform treated as product with its own roadmap.
- **Dr. Martens** (via Atlan) — Impact analysis from 4-6 weeks to under 30 min via data catalog.
- **Kiwi.com** (via Atlan) — 53% engineering workload reduction in 90 days.

## Sources

- Hiflylabs, "Commercial Bank Data Warehouse Case Study"
- Datalere, "Using Snowflake and Databricks Together: A Unified Architecture"
- Databricks, "Data Intelligence in Action: 100+ Data and AI Use Cases"
- Qubika, "Avant and Qubika" case study
- Atlan, "Data Mesh: Architecture, Principles, and Case Studies"

# Cloud Data Platform Comparison

A practical comparison of Snowflake, Google BigQuery, Amazon Redshift, and Databricks. Based on practitioner research, vendor documentation, and comparison guides from Reintech, Eidosoft, Improvado, Flexera, and Lakshmanan (ex-Google Cloud).

## Decision Tree — Which Platform to Choose?

```mermaid
flowchart TD
    Q1["What's your primary workload?"] --> Q1a{SQL Analytics<br>or ML / Data Science?}
    Q1a -->|SQL Analytics, BI, Reporting| Q2["What's your cloud strategy?"]
    Q1a -->|Data Science, ML,<br>complex transformations| Q5["Do you have a team with<br>Spark / Python skills?"]
    
    Q2 -->|Multi-cloud or no preference| A[Snowflake]
    Q2 -->|GCP-native / startup| B[BigQuery]
    Q2 -->|AWS-native / existing AWS| Q3["Do you have DBA expertise<br>for tuning?"]
    
    Q3 -->|Yes, we can tune| C[Redshift provisioned]
    Q3 -->|No, want managed| D[Redshift Serverless<br>or Snowflake on AWS]
    
    Q5 -->|Yes → engineering team| E[Databricks]
    Q5 -->|No → SQL-only team| F[Snowflake Cortex AI<br>or BigQuery + Vertex AI]
    
    style A fill:#e3f2fd,stroke:#1565c0
    style B fill:#e8f5e9,stroke:#2e7d32
    style C fill:#fce4ec,stroke:#c62828
    style E fill:#fff3e0,stroke:#e65100
```

**Quick summary:**
- **Don't know where to start?** → Snowflake. It's the safest default for most organizations
- **Already on GCP / startup** → BigQuery (zero ops, AI included)
- **Already on AWS with DBA staff** → Redshift (best price/performance at scale)
- **ML is as important as analytics** → Databricks (lakehouse, Spark-native)
- **Small team, want managed** → BigQuery or Snowflake

## Platform Comparison

### Snowflake

**Architecture:** Cloud-agnostic (AWS, Azure, GCP). Separates storage and compute completely — multiple virtual warehouses access the same data without copying. Proprietary storage layer.

**Pricing model:** Credit-based. Compute ($2-$6/credit depending on edition/region). Storage ($40/TB/month on-demand, $23/TB on capacity). Cloud services (free up to 10% of compute). Per-second billing with 60s minimum. Capacity contracts cut credits 20-30%.

**Scaling:** Spin up multiple warehouses of different sizes (XS through 6XL). Independent scaling for ETL vs analytics workloads. Auto-suspend and auto-resume. Multi-cluster warehouses for concurrency.

**Performance strengths:**
- Concurrent mixed workloads (100+ concurrent users)
- Data sharing across organizations (no data movement)
- Query performance on structured analytics
- Materialized views, search optimization, clustering

**Weaknesses:**
- Cost unpredictable — hidden costs from serverless features (materialized views, Search Optimization, Snowpipe), Cortex AI, Time Travel storage
- No native ML training (limited to SQL-based ML)
- Proprietary storage format (though now supports Iceberg writes)
- Frozen data costs for high-update tables

**Vendor lock-in vectors:** Proprietary storage format, unique SQL dialect, marketplace ecosystem dependency, data egress costs to migrate out. Iceberg support (2025) reduces lock-in.

**Best for:** BI-heavy teams, data sharing across business units, multi-cloud strategy, organizations that want managed service with minimal operational burden.

### Google BigQuery

**Architecture:** Fully serverless — no clusters to provision. Google's Dremel query engine with columnar Capacitor storage. Tightly integrated with GCP ecosystem.

**Pricing model:** On-demand ($5/TB queried, 1TB free/month) or flat-rate (slot-based reservations). Storage ($20/TB/month for active, $10/TB for long-term). Committed Use Discounts (CUDs) for predictable workloads. Gemini AI bundled at no extra cost.

**Scaling:** True auto-scaling — no manual sizing. Resources allocated per query. Slot reservations for predictable performance. Max number of concurrent queries based on slot pool.

**Performance strengths:**
- Ad-hoc analytics on petabyte-scale datasets
- Automatic optimization — no tuning required
- Streaming ingestion and real-time analytics
- Gemini AI (natural-language pipeline creation, visual data exploration)
- Multi-region replication

**Weaknesses:**
- Less control over resource allocation for complex queries
- No separation of compute for ETL vs analytics (everything shares slot pool)
- Query costs unpredictable for exploratory workloads
- Multi-region data transfer fees (starting Feb 2026)
- Weakest ecosystem outside GCP

**Vendor lock-in vectors:** Proprietary query engine, storage format coupling, strict GCP dependency for native integration. BigQuery Omni provides limited multi-cloud. Open-source connector ecosystems reduce but don't eliminate dependency.

**Best for:** GCP-native organizations, ad-hoc analytics, startups that want zero ops, ML/AI teams using Vertex AI integration, variable workloads that benefit from serverless.

### Amazon Redshift

**Architecture:** Columnar warehouse on AWS. Traditional provisioned clusters + Redshift Serverless (matured by 2026). RA3 nodes with managed storage separate from compute. Deep AWS integration.

**Pricing model:** Provisioned (pay per node-hour) or Serverless (pay per RPU-hour). RA3 pricing separates compute and storage billing. Spectrum queries data in S3 without loading. Reserved Instances for 1-3 year terms.

**Scaling:** Elastic resize (minutes) or classic resize (hours). Concurrency scaling for burst. Redshift Serverless auto-scales. Multi-dimensional data layouts (10x better price/performance on qualifying workloads).

**Performance strengths:**
- Massively parallel processing on huge datasets
- AQUA (Advanced Query Accelerator) for S3-based queries
- Zero-ETL integrations with 23 sources (PostgreSQL, DynamoDB, Salesforce)
- Materialized views, sort keys, distribution keys
- Redshift ML for SQL-based model creation

**Weaknesses:**
- Historically required significant tuning (sort keys, distribution styles, compression encodings)
- Workload management requires manual configuration
- Ecosystem less developed for ML/AI compared to Databricks
- Serverless is newer (less proven at extreme scale)
- Concurrency scaling costs add up

**Vendor lock-in vectors:** Deep AWS ecosystem integration, unique workload management model, Spectrum ties to S3. Migration-friendly via open formats (Parquet, Iceberg). AWS ecosystem lock-in is the real concern, not Redshift itself.

**Best for:** AWS-native organizations, high-performance BI on very large datasets, organizations that have DBA expertise for tuning, cost-sensitive workloads with predictable patterns.

### Databricks

**Architecture:** Lakehouse — combines data lake (cheap object storage, open formats) with warehouse (ACID, performance). Built on Apache Spark. Open table formats (Delta Lake, Iceberg, Hudi). Multi-cloud.

**Pricing model:** DBU-based (Databricks Units). Compute: $0.07-$2.00+/DBU depending on workload type (SQL, jobs, all-purpose) and tier. Storage charged separately by cloud provider (S3, GCS, ADLS). Serverless SQL warehouses available.

**Scaling:** Elastic auto-scaling for Spark clusters. Photon engine for accelerated SQL. Serverless SQL for BI workloads. Delta Sharing for cross-platform data access.

**Performance strengths:**
- ML/AI workloads alongside SQL analytics from same data
- Unity Catalog for unified governance across data + models
- Delta Lake for ACID transactions on data lakes
- Photon engine for performant SQL
- Model-agnostic approach (train any framework)

**Weaknesses:**
- Higher operational complexity than managed warehouses
- Requires Spark/Scala/Python skills beyond SQL
- Unity Catalog lock-in for governance (governance features not fully present in the open-source version)
- Cost tracking complex (DBU model + separate cloud infra costs)
- Cross-cloud management complex

**Vendor lock-in vectors:** Unity Catalog governance (open-source version is less capable), DBU pricing model makes total cost hard to compare, Delta Lake is open-source but Databricks-specific optimizations create gravity. Phoenix engine is proprietary.

**Best for:** Organizations with data science/ML workloads alongside analytics, teams with Spark/Python engineering skills, data platform teams wanting unified governance, anyone needing open table formats and avoiding proprietary storage.

## Side-by-Side: Decision Matrix

| Factor | Snowflake | BigQuery | Redshift | Databricks |
|---|---|---|---|---|
| **Ops burden** | Low | Lowest | Medium | Medium-High |
| **SQL-first** | Yes | Yes | Yes | Yes (Photon) |
| **ML/AI native** | Cortex AI | Vertex AI | Redshift ML | Native (Spark) |
| **Open formats** | Iceberg (2025) | No native | Parquet/Iceberg | Delta/Iceberg/Hudi |
| **Multi-cloud** | Yes (3 clouds) | GCP only | AWS only | Yes (3 clouds) |
| **Max concurrency** | Very high (multi-cluster) | High (slot pool) | Medium (WLM) | High (auto-scaling) |
| **Data sharing** | Native (marketplace) | Limited (authorized views) | Limited (datashare) | Delta Sharing |
| **Cost predictability** | Fair | Poor (slot/CUD helps) | Good (reserved) | Poor |

## Cost Warning Signs

**Snowflake:** Check for forgotten serverless features (materialized views, Search Optimization), Cortex AI line items, Time Travel on heavy-update tables, oversized warehouses.

**BigQuery:** Watch for runaway queries on large tables, no slot reservations for mixed workloads, multi-region transfer fees.

**Redshift:** Concurrency scaling costs, Spectrum data scan charges, resize downtime for provisioned clusters.

**Databricks:** DBU costs + separate cloud infra costs double the bill, all-purpose vs jobs vs SQL pricing tiers matter hugely, Photon vs non-Photon pricing difference.

## Sources

- Lakshmanan, "Snowflake or Databricks? BigQuery or Dataproc? Redshift or EMR?" (LinkedIn, 2022) — architecture philosophy framework
- Eidosoft, "Cloud Data Warehouse Comparison 2026" — comprehensive platform comparison with AI capabilities
- Improvado, "Snowflake Competitors in 2026" — decision rubric with migration guide
- Reintech, "Snowflake vs BigQuery vs Redshift 2026" — practitioner comparison
- Flexera, "Snowflake vs BigQuery: 7 Critical Factors" (2026)
- Definite, "Snowflake Pricing 2026" — detailed cost model analysis

# Compliance by Framework — Data Architecture Implications

What major regulatory frameworks require from a data architecture perspective.
This is not legal advice — it's a guide to the **design implications** each framework creates.

## GDPR (General Data Protection Regulation) — EU

**Scope:** Personal data of EU residents, regardless of where the organization operates.

**Key architectural implications:**

- **Data inventory and mapping** — you must know what personal data you hold, where it came from, and who you share it with. Requires: automated data discovery and classification, data lineage tracking, data catalog
- **Right to erasure (Article 17)** — you must be able to delete an individual's data across all systems, including backups. Requires: cascading delete patterns, purging from archives, backup retention hygiene
- **Data minimization (Article 5)** — collect only what you need. Requires: schema design discipline, separate PII from analytics data, column-level access controls
- **Consent management** — track what data subjects consented to, and when. Requires: consent flag as metadata on customer records, integration between data platform and consent system
- **Data Protection Impact Assessment (Article 35)** — document high-risk processing. Requires: process metadata, data flow documentation, classification lineage
- **72-hour breach notification** — you must detect and report breaches. Requires: data access auditing, anomaly detection, automated alerting

**Design patterns:**
- PII/PHI tagging at column level in the data catalog
- Separate PII store from analytical data warehouse
- Data masking at query time (not at rest) for analytics access
- Immutable audit logs with retention policies
- Pseudonymization as a transformation step (replace PII with tokens before analytics)

## HIPAA (Health Insurance Portability and Accountability Act) — US Healthcare

**Scope:** Protected Health Information (PHI) of US patients.

**Key architectural implications:**

- **PHI must be encrypted at rest and in transit** — applies to databases, backups, data lakes, and data warehouse
- **Access controls (minimum necessary rule)** — RBAC at row and column level. Not just who can access a table, but who can see which rows and columns within it
- **Audit trails** — who accessed what PHI, when, and from where. Requires: access logging, immutable audit storage
- **Business Associate Agreements (BAAs)** — all vendors handling PHI must sign a BAA. Requires: vetting every tool in the data pipeline (warehouse, ETL tool, BI tool, catalog)
- **Data retention** — PHI must be retained per state and federal requirements, then securely destroyed
- **Breach notification** — 60 days to report breaches affecting 500+ individuals

**Design patterns:**
- Column-level encryption for sensitive PHI fields (SSN, diagnosis codes)
- Row-level security filters in the data warehouse (analyst sees only their department's patients)
- Separate de-identified data marts for analytics (no PHI)
- Logging pipeline that captures all queries against PHI tables
- Automated PHI detection and classification at ingestion

## CCPA/CPRA (California Consumer Privacy Act) — US California

**Scope:** Personal information of California residents. Broader definition of "personal information" than GDPR.

**Key architectural implications:**

- **Right to know** — what data you've collected about a consumer. Requires: data inventory, consumer-level data mapping
- **Right to delete** — similar to GDPR, with exceptions. Requires: purging consumer data across systems
- **Right to opt out of sale/sharing** — treats "sharing for cross-context behavioral advertising" as a sale. Requires: data sharing classification, consent metadata propagation
- **Non-discrimination** — can't penalize consumers for exercising rights. Requires: separation of operational data from consent data

**Design patterns:** Similar to GDPR, with additional attention to data sharing classification. Any data shared with third parties (advertising, analytics vendors) needs opt-out tracking.

## SOX (Sarbanes-Oxley Act) — US Public Companies

**Scope:** Financial reporting accuracy and controls for publicly traded US companies.

**Key architectural implications:**

- **Internal controls over financial reporting** — every data transformation that touches financial reports must be auditable and verifiable. Requires: immutable data lineage, version-controlled transformations
- **Data retention** — financial records must be retained for 7+ years. Requires: archival data storage with retrieval capabilities
- **Segregation of duties** — the person who enters data shouldn't be the person who approves the report. Requires: role-based access control, separation of data producer and data consumer roles
- **Full audit trail** — every change to financial data must be logged with who, what, when

**Design patterns:**
- Data Vault 2.0 (hub-link-satellite with load dates and record sources for every row)
- Immutable data lake (write-once, never modify)
- Version-controlled transformation code (dbt with git, CI/CD for data pipelines)
- Reconciliation processes between source systems and reporting layer

## PCI DSS (Payment Card Industry Data Security Standard)

**Scope:** Any organization handling credit card data.

**Key architectural implications:**

- **Cardholder data must be encrypted at rest and in transit** — full encryption everywhere
- **Access must be on a need-to-know basis** — strict RBAC, no shared credentials
- **Tokenization** — replace PAN (Primary Account Number) with tokens in analytics environments
- **Logging and monitoring** — all access to cardholder data must be logged
- **Segmentation** — cardholder data environment must be network-separated from non-cardholder systems

**Design patterns:**
- Tokenization at ingestion (PCI data never enters the analytics warehouse as raw card numbers)
- Separate data environment for PCI data with stricter controls
- Query-level monitoring for any access to token vault
- Automated masking for any UI/reporting layer

## BCBS 239 (Basel Committee) — Banking

**Scope:** Risk data aggregation and reporting for global systemically important banks.

**Key architectural implications:**

- **Data lineage** — full traceability from source to regulatory report. Every field in a regulatory return must trace to a source system field.
- **Data quality** — accuracy, completeness, timeliness must be demonstrated. Requires: data quality dashboards with formal metrics, automated quality checking
- **Data dictionary** — common definitions across the organization. Requires: business glossary with formal definitions, data ownership
- **Audit trail** — all data transformations must be auditable. Requires: version-controlled transformation, change data capture, immutable logs
- **IT resilience** — data architecture must support stress testing and recovery scenarios

**Design patterns:**
- Data Vault 2.0 (provides built-in auditability, source tracking, and flexibility for regulatory change)
- Formal data quality framework with automated scorecards
- Enterprise data catalog acting as the single source of definitions
- Reconciliation processes between source data warehouse → data mart → regulatory report

## General Architecture Principles for Compliance

1. **Design for auditability from day one.** Retrofitting audit trails is 10x harder than building them in.
2. **Separate sensitive data from analytics data.** Use de-identified data marts for BI; keep raw PII/PHI behind strict access controls.
3. **Automate compliance where possible.** Manual compliance processes fail at scale. Automate PII detection, data classification, lineage capture, and quality monitoring.
4. **Data retention is an architecture concern, not just a policy.** If you can't enforce retention at the storage layer, it won't happen consistently.
5. **Access controls must be granular enough for minimum necessary access.** Table-level access is not sufficient for HIPAA or GDPR (need column and row level).
6. **Data catalogs are compliance infrastructure.** Without knowing what data you have and where it lives, you cannot comply with any framework's inventory requirements.

## Industry-to-Framework Quick Reference

Use this table when someone asks "what compliance frameworks apply to us?"

| Industry | Likely Frameworks | Primary Architectural Impact |
|---|---|---|
| Healthcare (US) | HIPAA, GDPR (if EU patients), CCPA (if CA patients) | PHI encryption, row/column-level access controls, BAAs with every vendor, de-identified analytics marts |
| Financial services (US public) | SOX, PCI DSS (if cards), BCBS 239 (if systemic bank) | Immutable audit trails, 7-year retention, Data Vault for lineage, segregation of duties |
| Financial services (EU) | GDPR, PSD2, BCBS 239 | Consent management, data minimization, lineage for regulatory reporting |
| E-commerce / retail | CCPA (if CA customers), GDPR (if EU customers), PCI DSS | PII classification, data sharing opt-out tracking, tokenization |
| SaaS / technology | GDPR, CCPA, SOC 2 (voluntary) | Data inventory, right to erasure, data processing agreements |
| Insurance | HIPAA (if health), SOX (if public), GDPR, CCPA | Multiple frameworks simultaneously — layered compliance architecture |
| Government / defense | FedRAMP, ITAR, GDPR (if EU citizens) | Data residency controls, classification markings, strict access segmentation |
| Any B2B (global) | GDPR (EU customer data applies extraterritorially) | PII discovery, data mapping, consent tracking — even if not EU-based |

**If the answer is "we're not sure":** Start with a data inventory and classification. You can't determine which frameworks apply until you know what data you hold, where it lives, and who your data subjects are. The data catalog isn't just compliance infrastructure — it's how you scope your compliance obligations.

# Data Mesh Readiness and Operating Model

Use this reference before recommending a mesh adoption program. It is an assessment and design method, not a maturity badge or a preferred end state.

## Start With the Pressure

Name the measurable problem that centralization is failing to solve:

- Which consumers are blocked, and by what queue, quality failure, or missing context?
- Which domains generate the data, and which teams have authority to change it?
- What is the cost of delay, duplicate transformation, or unreliable data today?
- Could a catalog, ownership assignment, quality agreement, or platform improvement solve the problem without changing the operating model?

If the answer is unclear, do not begin a mesh rollout. Run discovery and a small product experiment first.

## Readiness Assessment

Assess each condition as **evidenced**, **partial**, or **absent**. Record the evidence, accountable owner, and next action.

| Condition | Evidence to seek | If absent |
|---|---|---|
| Meaningful domains | Stable business boundaries and named domain decision makers | Keep ownership centralized or pilot one bounded domain |
| Product accountability | A team can own semantics, quality, support, change, and retirement | Assign a product owner before publishing a mesh product |
| Consumer demand | Identified consumers with concrete freshness, quality, and access needs | Avoid platform work without a use case |
| Self-service platform | Repeatable paths for storage, processing, access, metadata, testing, and observability | Build the smallest enabling capability first |
| Federated governance | Shared definitions, classification, compatibility, access, and quality rules that can be checked | Establish minimum standards and decision rights |
| Team capacity | Domain and platform teams have funded time and operating skills | Use a centralized or hybrid transition |
| Executive sponsorship | Authority to resolve cross-domain conflicts and fund shared capabilities | Limit scope until sponsorship exists |

Do not collapse these into a numeric score. One absent prerequisite can dominate several positive signals.

## Data Product Minimum Contract

For every proposed product, document:

- **Identity:** product name, domain, owner, support path, version, and lifecycle status
- **Meaning:** business definitions, grain, units, keys, time semantics, and known exclusions
- **Consumers:** named use cases and whether access is push, pull, or both
- **Quality:** accuracy checks, completeness expectations, validity rules, freshness, availability, and incident response
- **Discoverability:** catalog entry, sample queries or payloads, lineage, classification, and access request path
- **Change:** compatibility policy, notification window, deprecation process, and consumer migration responsibility
- **Security and policy:** permitted use, sensitivity, retention, residency, masking, and audit requirements

The product is not complete because a table or topic exists. It is complete when consumers can find it, understand it, use it safely, and recover when it changes or fails.

## Operating Model Boundaries

- **Domain teams** own source meaning, product quality, support, and lifecycle decisions for the products they publish.
- **The self-service platform team** owns reusable paved paths and guardrails for storage, processing, access, metadata, testing, observability, and recovery. It does not become the hidden owner of domain semantics.
- **Federated governance** owns shared rules and conflict resolution for definitions, classification, compatibility, access, retention, and quality. Prefer automated checks and clear escalation over blanket approval queues.
- **Consumers** state their use case, service expectations, access need, and migration plan. Consumption does not transfer product ownership.

Keep interface contract syntax and protocol semantics in `api-design-and-evolution`; keep pipeline construction in `data-engineering`; keep platform deployment and operations in `platform-engineering`.

## Federated Computational Governance Method

Use a **rule plane and evidence loop** rather than a committee queue. The rule plane has two layers:

- **Global rules** cover interoperability and enterprise policy: shared identifiers and time semantics, minimum metadata, classification, access and retention constraints, compatibility, and required quality signals. They apply to every product and are changed only by the federated governance decision body.
- **Domain rules** cover local meaning and operating choices: valid business states, acceptable lateness, source-specific quality thresholds, and consumer-specific transformations. A domain owner may change them within the global envelope and must publish their scope and rationale.

Encode each rule as a versioned, machine-readable policy with a stable identifier, scope, owner, severity, effective time, and test expression. Execute it at the earliest useful control point: admission checks for schemas and metadata, build or publish checks for quality and compatibility, and read-time authorization or retention enforcement for policy rules. A human review is an escalation path, not the default execution engine.

For every execution, retain an evidence record linking rule ID and version, product and data release, subject, timestamp, evaluator, result, and remediation reference. Permit an exception only as a time-bounded record naming the approving authority, affected scope, reason, compensating control, expiry, and renewal decision; expired exceptions fail closed or return to the owning team for review. The domain product owner remediates local failures; the platform owner remediates broken enforcement; the federated governance owner resolves global-rule ambiguity or conflict. Review exception and failure records on a recurring cadence: repeated patterns become a proposed global rule, a clearer domain rule, a platform control, or an explicit decision not to standardize, with the decision and evidence retained.

## Transition Choices

Choose the smallest transition that tests the hypothesis:

1. **Centralized foundation:** Improve ownership, cataloging, quality, and serving in the existing platform.
2. **Domain pilot:** Publish one high-value product with a willing domain and a small consumer group.
3. **Hybrid:** Retain central conformance or regulatory serving while domains own bounded products and their source context.
4. **Broader mesh:** Expand only after the pilot demonstrates consumer value, product reliability, workable governance, and sustainable team capacity.

For each step define entry evidence, exit evidence, reversible actions, new operational load, and what will remain centralized. A transition plan that only lists platform components is incomplete.

# Architecture Discovery Framework

A structured approach to understanding a client's context before making recommendations. Use this when starting an architecture consulting session.

## Phase 1: Problem & Context

Establish what we're actually solving:

**Business:**
- What business problem are you solving? What's the measurable outcome?
- Who is asking for this? What's their pain point?
- What happens if we do nothing?
- What does success look like in 3 months, 12 months?

**Organizational:**
- Who are the stakeholders? Who has decision authority?
- What's the team structure — who builds, who operates, who consumes?
- What's the current team capability (size, seniority, key skills)?
- Is there executive sponsorship for cross-team changes?

**Temporal:**
- What's the timeline? What's driving the urgency?
- What's frozen (can't change) vs flexible?
- Are there existing commitments (vendor contracts, hiring freezes, compliance deadlines)?

## Phase 2: Current State

Understand what exists today:

**Data Profile:**
- What data sources exist? (Count, types, systems of record)
- Current data volume, velocity, variety — and projected 18-month growth
- Where is data quality acceptable vs problematic?
- What's the schema evolution frequency of source systems?

**Architecture:**
- Diagram the current data flow — source to consumption
- What's the current stack? (warehouse, pipeline tooling, BI layer, catalog)
- What's working well (don't fix what isn't broken)?
- What's the biggest operational pain point?

**Consumption:**
- Who consumes data and how? (analysts, data scientists, operational apps, ML models)
- What are their actual latency, freshness, and accuracy requirements?
- What do they complain about most?

**Governance:**
- What data governance exists currently? (ownership, quality, lineage, access control)
- What compliance/regulatory frameworks apply? (GDPR, HIPAA, SOX, CCPA, BCBS 239)
- Are there existing data sharing agreements or contractual constraints?

**Cost:**
- Current data infrastructure spend (compute, storage, tools, people)
- Where is cost growing fastest?
- Are there wasteful patterns (orphaned tables, unused pipelines, over-provisioned compute)?

## Phase 3: Constraints

Identify the hard boundaries:

**Non-negotiable:**
- Regulatory requirements
- Budget ceilings
- Hiring timeline
- Existing vendor contracts

**Tradeable:**
- Time-to-market vs robustness
- Feature scope vs timeline
- Central control vs team autonomy
- Managed service premium vs operational flexibility

**Unknowns to validate:**
- What assumptions are we making that should be tested?
- What POCs should we run before committing to an architecture?

## Phase 4: Recommendation Structure

Present findings as:

**Quick wins (this month)**
High impact, low effort — do these immediately regardless of long-term direction.
*(e.g., add data quality checks at ingestion, document current lineage, consolidate redundant pipelines)*

**Foundation (next quarter)**
Medium-term investments that enable future phases.
*(e.g., implement data catalog, establish governance board, standardize naming conventions)*

**Transformation (next 12 months)**
Major architectural shifts that require sequence and dependency management.
*(e.g., migrate from on-prem EDW to cloud, adopt Data Mesh, implement streaming platform)*

**Deferred / Not Yet**
Things that were requested but don't make sense given the current context.
*(e.g., "we should add real-time streaming" when batch is meeting all SLAs)*

## Phase 5: Risk Register

Flag these for every engagement:

- **Single point of failure** — one person who knows how X works
- **Vendor lock-in trajectory** — growing dependence on a platform without migration plan
- **Technical debt that compounds** — shortcuts taken now that make future changes harder
- **Skills gap** — architecture assumes capabilities the team doesn't have
- **Organizational dependency** — project requires another team's cooperation without their buy-in
- **Compliance cliff** — approaching regulatory deadline that current architecture can't meet

## Consulting Session Flow

```
1. "Tell me about the problem" → listen, don't prescribe
2. "What have you tried?" → understand the history
3. "What's the actual scale?" → quantify before designing
4. "Draw me your current flow" → visualize the as-is
5. "What hurts most?" → identify leverage points
6. "What's the simplest thing that could work?" → avoid over-engineering
7. "Here's what I recommend, and here's why..." → tradeoffs explicit
8. "What did I miss?" → invite challenge
```

# Event-Driven Data Products

Use this guide when operational events, change streams, or event-oriented products feed analytical, ML, or cross-domain consumers. It covers architecture decisions; implementation belongs to `data-engineering` and interface contract semantics belong to `api-design-and-evolution`.

## Choose the Product Shape

An event stream, a current snapshot, and a historical analytical table answer different questions. For each consumer, decide whether it needs:

- **Events:** append-oriented facts for reacting to changes or rebuilding a view
- **Snapshots:** the latest state for query consumers that should not replay history
- **History:** time-aware records for audit, trend, and backfill use cases
- **A combination:** a stream for incremental consumers plus a queryable snapshot for recovery and exploration

Record the source of truth, event time, publication time, identity key, ordering scope, retention, and whether the product is immutable. Do not promise an event stream as a universal replacement for queryable data.

## Producer and Consumer Responsibilities

The producer owns the meaning and publication behavior of facts it authors. It must identify what changed, preserve the product's declared time semantics, publish quality and freshness signals, and communicate lifecycle changes. Consumers own their projections, checkpoints, deduplication state, and migration work.

The architecture must state whether delivery is at-most-once, at-least-once, or effectively-once for the use case. Avoid claiming exactly-once behavior without defining the boundary at which it is verified.

## Failure and Recovery Decisions

For each product, answer:

1. What happens when delivery is delayed, duplicated, reordered, or unavailable?
2. Can a consumer rebuild from retained events, a snapshot, or a separate backfill source?
3. How are late events reconciled with already published aggregates?
4. Who detects a freshness or completeness breach, and who communicates it?
5. What is the safe behavior when a consumer cannot upgrade before a producer change?
6. How are poison records quarantined without silently dropping business facts?

Prefer explicit replay and reconciliation paths over an assumption that a consumer can simply restart. Recovery cost, retention, and replay throughput are architecture inputs.

## Compatibility and Lifecycle

Define compatibility by consumer impact, not by a label alone. A change may be additive at the schema level but still break a consumer through changed meaning, units, defaults, cardinality, ordering, or timing. Capture:

- compatible and incompatible changes;
- announcement and overlap period;
- version or translation strategy;
- consumer inventory and usage evidence;
- deprecation owner and removal condition;
- historical correction and backfill policy.

Route the formal event schema, serialization, API, webhook, and compatibility contract to `api-design-and-evolution`. This skill supplies the consumer and architecture decisions that the contract must support.

## Access Modes and Planes

Separate the operational plane, where producers and reactive consumers exchange timely facts, from the analytical plane, where data is validated, reconciled, retained, and queried. Decide whether consumers pull from a product store, subscribe to events, or use both. Make the latency, freshness, availability, cost, and security consequences visible for each mode.

An event-driven design is justified when the business benefit of timely change exceeds the cost of retention, replay, monitoring, compatibility, and operating the path. Batch or micro-batch remains a valid design when those costs are not justified.

# Data Governance Maturity Model

A staged framework for data governance, synthesized from DAMA-DMBOK, Atlan, Gartner, and DATAVERSITY sources. Use this when designing or assessing a governance program.

## The DAMA-DMBOK Framework

The Data Management Association's Data Management Body of Knowledge (DAMA-DMBOK) is the canonical reference. It organizes data management into **11 knowledge areas** with Data Governance at the center:

1. **Data Governance** (central) — planning, oversight, and control over data management
2. **Data Architecture** — enterprise data models and data flow designs
3. **Data Modeling & Design** — analysis, design, and implementation of data structures
4. **Data Storage & Operations** — database and data warehouse management
5. **Data Security** — privacy, confidentiality, access control
6. **Data Integration & Interoperability** — acquisition, extraction, transformation, movement
7. **Document & Content Management** — managing unstructured data
8. **Reference & Master Data** — managing shared data entities
9. **Data Warehousing & Business Intelligence** — analytical data management
10. **Metadata Management** — data about data
11. **Data Quality** — quality dimensions, measurement, improvement

DAMA-DMBOK is **not prescriptive** — it doesn't mandate specific tools or technologies. It's a vendor-neutral reference that organizations adapt to their context.

Other frameworks: **CMMI DMM** (Data Management Maturity) provides a formal assessment model. **ISO 8000** covers data quality standards. **COBIT** focuses on governance and control objectives for IT.

## Maturity Stages

Based on Atlan's 6-stage framework (extending the common 5-stage model):

### Level 0: Unaware / Ad Hoc
- **What it looks like:** Governance is not a recognized concept. Data is managed by individual teams with no coordination. Data issues are discovered when reports don't match.
- **Policies:** None
- **Roles:** No data owners identified
- **Tools:** Spreadsheets, shared drives
- **Metrics:** None tracked
- **Core challenge:** Making the case for investment by showing the business impact of data failures

### Level 1: Initial / Reactive
- **What it looks like:** Basic awareness exists. Some teams document data ownership informally. Governance happens reactively in response to crises (audit findings, data breaches).
- **Policies:** Sparse, undocumented, inconsistently applied
- **Roles:** Occasional data stewards assigned per project
- **Tools:** Basic data catalog (AWS Glue, manual spreadsheet), ad-hoc quality checks
- **Metrics:** Percentage of datasets with an identified owner
- **Core challenge:** Scaling beyond a single team; avoiding "governance as IT project" trap

### Level 2: Managed / Developing
- **What it looks like:** Formal governance structures emerge — an early data governance council, documented policies for priority domains, a data catalog tool in production. Efforts still siloed and resource-constrained.
- **Policies:** Documented for critical domains, version-controlled
- **Roles:** Data stewards assigned per domain, data owners for critical data
- **Tools:** Enterprise data catalog with automated metadata ingestion, basic column-level lineage, scheduled quality checks
- **Metrics:** Catalog completeness (% of systems registered), data quality scores by domain
- **Core challenge:** Securing ongoing budget and executive sponsorship

### Level 3: Defined / Standardized
- **What it looks like:** Governance is consistent across the organization. A data governance council makes decisions. Policies cover all major domains. Roles are clearly defined. Data quality standards are enterprise-wide.
- **Policies:** Enterprise-wide, reviewed quarterly, enforced through tools
- **Roles:** Data Governance Council (monthly), domain stewards, data custodians, CDO
- **Tools:** Active metadata platform, cross-system column-level lineage, automated data quality monitoring, stewardship workflows
- **Metrics:** Data trust scores, policy compliance rate, time to resolve data incidents
- **Core challenge:** Shifting from compliance focus to cultural adoption and proactive risk management

### Level 4: Managed / Integrated
- **What it looks like:** Governance is embedded into daily workflows. Quality monitoring is continuous and automated. Access adapts based on context. Governance metrics connect directly to business performance.
- **Policies:** Automated policy enforcement (data masking applied at query time, automated retention schedules)
- **Roles:** CDO with embedded governance liaisons in each department; stewardship is a recognized role with dedicated time
- **Tools:** Active metadata management, real-time data quality monitoring with alerts, automated lineage, programmatic RBAC/ABAC
- **Metrics:** Data utilization (% of data actively used), reduction in data incidents, cost of poor data quality
- **Core challenge:** Integrating governance signals seamlessly into tools so teams can act on them without friction

### Level 5: Optimized / Transformative
- **What it looks like:** Governance is a strategic capability. AI enhances monitoring and policy recommendations. Automation enforces controls without manual effort. Governance is part of organizational culture.
- **Policies:** AI-suggested policies based on usage patterns, self-healing quality rules
- **Roles:** Governance is everyone's responsibility; AI handles routine oversight. CDO role shifts to strategic innovation.
- **Tools:** AI-driven auto-classification, anomaly detection, automated stewardship via workflow bots, self-service governance
- **Metrics:** AI governance accuracy, business impact of data quality improvements (ROI), data velocity (time from ingestion to trusted consumption)
- **Core challenge:** Sustaining innovation and keeping governance aligned with fast-changing business needs

## Practical Progression

Most organizations are at Level 1-2. Here's how to advance:

**Level 0 → 1:** Start with one critical data domain. Identify the owner. Create a simple data dictionary. Show one success story — "we fixed the revenue report that never matched."

**Level 1 → 2:** Deploy a data catalog. Automate metadata ingestion from your warehouse. Assign stewards for priority domains. Establish a data quality dashboard for the most-used datasets.

**Level 2 → 3:** Formalize the governance council. Roll out policies enterprise-wide. Implement column-level lineage for critical reports. Set data quality SLAs and publish them.

**Level 3 → 4:** Embed governance into CI/CD pipelines. Automate PII classification and masking. Link governance metrics to business KPIs (not just data KPIs).

**Level 4 → 5:** Deploy AI-driven policy recommendations and anomaly detection. Make data stewardship frictionless through automation. Measure governance effectiveness by business outcomes.

## Key Principle

**Governance maturity is not about more rules — it's about making good data practices the path of least resistance.** The goal at every stage is to reduce friction for data consumers while increasing trust. If governance makes people's jobs harder without a visible benefit, it will fail regardless of maturity level.

## Sources

- DAMA International, DAMA-DMBOK 2.0 — the canonical data management body of knowledge
- Atlan, "How to Choose a Data Governance Maturity Model" (2026) — 6-stage framework
- DATAVERSITY, "Data Governance Maturity Model Guide" (2026) — assessment methodology
- Springer, "Data Governance Frameworks: Models and Best Practices" (2024) — comparative analysis of DAMA, CMMI DMM, ISO 8000
- Gartner, "80% of D&A Governance Initiatives Will Fail by 2027" (2024)

# Vendor Evaluation Framework

Structured comparison criteria for common data tool categories. Based on Atlan's buyer's guide, Improvado's enterprise data management analysis, and practitioner comparisons from Promethium, Basedash, and LinkedIn.

## Data Catalogs

### Decision Tree — Which Catalog Fits?

```mermaid
flowchart TD
    Q1["What kind of team are you?"] --> Q1a{Engineering-led<br>or Governance-led?}
    Q1a -->|Engineering-led,<br>self-hosted| Q2["Do you have engineering<br>time to maintain it?"]
    Q1a -->|Governance-led,<br>regulated| Q5["What's your budget?"]
    Q1a -->|Modern data stack<br>Snowflake/dbt/BQ| A[Atlan]
    
    Q2 -->|Yes → engineer it| B[DataHub or OpenMetadata]
    Q2 -->|No → buy it| C[Secoda or Atlan]
    
    Q5 -->|$100k+/year| D[Collibra]
    Q5 -->|$25k-$100k| E[OvalEdge]
    Q5 -->|Minimal budget| F[DataHub OSS]
    
    style A fill:#e3f2fd,stroke:#1565c0
    style D fill:#fce4ec,stroke:#c62828
    style B fill:#fff3e0,stroke:#e65100
```

**Staffing warning labels:**
- **Collibra / Informatica** → needs dedicated data stewards or becomes shelfware
- **Alation** → needs 15+ active catalogers for ROI
- **DataHub / OpenMetadata** → needs engineering time to deploy and maintain
- **Atlan / Secoda** → lower staffing bar, designed to reduce curation effort

### Quick Comparison Table

| Tool | Best For | G2 Rating | Deploy Time | Starting Price | Staffing Required |
|---|---|---|---|---|---|
| **Atlan** | Modern data stacks (Snowflake, dbt, Databricks) | 4.5/5 | 4-6 weeks | Custom enterprise | Low — active metadata reduces curation |
| **Alation** | Analytics-first orgs; mixed legacy/modern | 4.4/5 | 6-12 weeks | Custom enterprise | 15+ active catalogers recommended |
| **Collibra** | Regulated enterprises, governance-led | 4.2/5 | 3-9 months | $100k+/year | Dedicated data stewards needed |
| **DataHub (LinkedIn)** | API-first, engineering teams, self-hosted | N/A | Self-hosted | Free + infra cost | Engineering team to deploy |
| **OpenMetadata** | Broad connectors, engineers + analysts | N/A | Self-hosted | Free + managed plan | Engineering team to deploy |
| **Microsoft Purview** | Azure-first orgs | N/A | Days to weeks | Azure consumption | Low if Azure-native |
| **Informatica IDMC** | Multi-cloud, 600+ integrations | 4.2/5 | 6-9 months | Custom enterprise | Large governance team |
| **Secoda** | Fast-growing modern-stack teams (5-50 users) | 4.5/5 | 1-2 weeks | ~$500/month | Minimal |
| **Apache Atlas** | Hadoop-centric platforms | N/A | Self-hosted | Free | Hadoop ops team |
| **OvalEdge** | Mid-market; $25k-$100k budget | 4.9/5 | 4-8 weeks | $25k-$100k/year | Moderate |

### Key Evaluation Dimensions

**1. Architecture**
- Is it API-first and extendable? (DataHub excels here)
- Does it use active metadata (query-parsed, continuously updated) vs passive (scheduled crawls)?
- Is the metadata layer open or proprietary?

**2. Lineage Depth**
- Column-level granularity (not just table-level)?
- Cross-platform lineage spanning dbt, Airflow, Spark, and BI tools?
- Automated vs manual lineage capture?

**3. Deployment Speed**
- Does it deploy in weeks or months? Real customer timelines (not vendor estimates)
- Self-hosted vs SaaS vs hybrid?

**4. Staffing Requirements**
- Some tools (Collibra) require dedicated stewards to get ROI
- Others (Atlan, Secoda) are designed to reduce curation burden through active metadata
- Open-source tools (DataHub, OpenMetadata) need engineering investment to operationalize

**5. Pricing Model**
- Per-user? Per-data-asset? Consumption-based? Enterprise contract?
- Hidden costs: professional services ($80k-$200k), custom connectors ($15k-$50k each), cloud egress

**6. Evaluation Questions by Profile**

*For modern data stack (Snowflake/dbt/BigQuery/Databricks):*
- Does the catalog natively parse dbt manifest files for lineage?
- Can it ingest from both warehouse AND transformation tool?
- How fresh is the metadata — real-time or batch?

*For regulated enterprise:*
- Does it support RBAC/ABAC at column level?
- Can it automate PII classification across all sources?
- Does it provide compliance audit trail export?

*For engineering-first team:*
- Is there a REST API or GraphQL endpoint for programmatic access?
- Can we build custom connectors?
- Is the metadata model extensible?

### Staffing Failure Thresholds
- **Collibra / Informatica:** Needs dedicated data stewards; without them, tools become shelfware
- **Alation:** 15+ active catalogers needed for ROI; under-resourced teams underutilize it
- **DataHub / OpenMetadata:** Requires engineering time for setup and maintenance; budget headcount, not just licensing
- **Atlan / Secoda:** Lower staffing bar — designed to reduce curation effort through automation

## ETL/ELT & Data Integration

| Tool | Best For | Pricing | Strength |
|---|---|---|---|
| **Fivetran** | Managed ELT, broad connector library | Usage-based ($0.25-$1.00+/MAR) | Zero-maintenance connectors, 500+ sources |
| **Airbyte** | Open-source ELT, custom connector needs | Free (OSS) + Cloud plans | 350+ connectors, open protocol |
| **dbt** | Transformation-as-code | Free core + Cloud ($100-$$$$) | The standard for analytics engineering |
| **Talend** | Traditional ETL with complex transformations | Per-core licensing | Broad on-prem connector support |
| **Informatica** | Enterprise data integration | Custom enterprise ($500k+) | 600+ certified connectors, mature governance |

### Key Questions
- Do you need managed or self-hosted? (Fivetran vs Airbyte)
- Is the primary need ingestion (move data) or transformation (shape data)? (Fivetran/Airbyte vs dbt)
- What's your source system diversity? (narrow = cheaper, broad = need broad connector coverage)
- What's your latency requirement? (batch ELT vs real-time CDC)

## Orchestration

| Tool | Best For | Language | Strength | Weakness |
|---|---|---|---|---|
| **Airflow** | Most common, broadest ecosystem | Python (DAGs) | Largest community, 1000+ providers | Complex, not idempotent by default, scheduler bottlenecks |
| **Dagster** | Developer experience, testing | Python (software-defined assets) | Better testing, asset-centric, type system | Smaller community, fewer integrations |
| **Prefect** | Cloud-native, serverless | Python (decorators) | Automatic retries, built-in observability | Fewer community providers than Airflow |
| **Mage** | Data platform teams, simple syntax | Python, SQL, R, YAML | Developer-friendly, built-in data integration | Newest, smallest ecosystem |

### Key Questions
- How important is community and ecosystem breadth? (Airflow)
- Do you want code-first or declarative?
- Is testing and local development a priority? (Dagster)
- Do you need serverless execution or run your own infra?

## Source References
- Atlan, "16 Best Data Catalog Tools in 2026: A Complete Buyer's Guide"
- Improvado, "15 Best Enterprise Data Management Tools for 2026"
- Promethium, "Data Governance Tools Comparison: Collibra vs Alation vs Atlan vs Purview" (2026)
- LinkedIn, "Top 5 Data Governance Tools Compared"
- Basedash, "Best Data Integration Tools Compared 2026"

#!/usr/bin/env python3
"""
Governance Maturity Assessment — evaluate your data governance program.

Usage:
    python3 scripts/governance-assessment.py
    python3 scripts/governance-assessment.py --json

Answers 15 scored questions across 5 dimensions. Produces a maturity level,
dimension scores, and prioritized recommendations. Outputs human-readable
text by default, or JSON with --json for machine consumption.
"""

import json
import sys
import textwrap

# === Scoring ===

DIMENSIONS = {
    "policy": "Policies & Standards",
    "roles": "Roles & Ownership",
    "tools": "Tooling & Metadata",
    "quality": "Data Quality",
    "culture": "Culture & Adoption",
}

QUESTIONS = [
    # (dimension, question, low_label, high_label)
    ("policy", "How are data governance policies documented?",
     "No formal policies", "Enterprise-wide policies, reviewed quarterly with automated enforcement"),
    ("policy", "How consistently are policies followed across teams?",
     "Ad hoc, varies by team", "Automated enforcement with audit trails"),
    ("policy", "How do you handle regulatory compliance requirements?",
     "Reactively when audited", "Proactive, automated compliance checks embedded in pipelines"),

    ("roles", "Who owns data quality and definitions?",
     "No clear ownership", "Dedicated data stewards per domain with formal charters"),
    ("roles", "How is data ownership assigned?",
     "No owners identified", "Every dataset has a documented owner with performance goals"),
    ("roles", "Is there a data governance council?",
     "No council exists", "Active council meeting monthly with executive sponsorship"),

    ("tools", "How do users discover and understand data?",
     "Ask colleagues or read source code", "Active metadata catalog with automated lineage and semantic search"),
    ("tools", "How is data lineage tracked?",
     "Not tracked", "Automated column-level lineage across all systems"),
    ("tools", "How do you manage metadata?",
     "Spreadsheets or shared docs", "Active metadata platform with automated ingestion and enrichment"),

    ("quality", "How do you measure data quality?",
     "Not measured systematically", "Automated quality dashboards with SLAs per dataset"),
    ("quality", "How are data quality issues detected and resolved?",
     "Found by users during analysis", "Automated monitoring with alerts and tiered SLAs"),
    ("quality", "How do you handle data quality at ingestion?",
     "No validation at entry", "Automated validation rules, schema enforcement, and anomaly detection"),

    ("culture", "How do teams perceive data governance?",
     "As a bottleneck or blocker", "As an enabler — governance makes data easier to use"),
    ("culture", "How is governance funded and resourced?",
     "Project-based, inconsistent", "Dedicated budget and headcount with executive sponsorship"),
    ("culture", "How does governance affect decision-making velocity?",
     "Slows teams down", "Faster decisions because trusted data is easier to find and use"),
]


def score_response(response):
    """Convert 1-5 response to a score."""
    try:
        val = int(response)
        if 1 <= val <= 5:
            return val
    except (ValueError, TypeError):
        pass
    return None


def text_prompt(dimension, question, low_label, high_label):
    """Present a question and get a 1-5 response."""
    print(f"\n--- {DIMENSIONS[dimension]} ---")
    print(f"Q: {question}")
    print(f"  1 = {low_label}")
    print(f"  5 = {high_label}")
    while True:
        try:
            resp = input("  Score (1-5): ").strip()
            score = score_response(resp)
            if score:
                return score
            print("  Please enter a number between 1 and 5.")
        except (EOFError, KeyboardInterrupt):
            print("\n  Assessment cancelled.")
            sys.exit(1)


def json_prompt():
    """Return placeholder scores for JSON mode (no interactivity)."""
    print("Run without --json for interactive assessment.", file=sys.stderr)
    sys.exit(0)


def calculate_maturity(avg_score):
    """Map average score to maturity level."""
    if avg_score < 1.5:
        return (0, "Unaware — no governance concept exists")
    elif avg_score < 2.5:
        return (1, "Initial — reactive, ad hoc, crisis-driven")
    elif avg_score < 3.5:
        return (2, "Managed — basic structures, siloed, early tools")
    elif avg_score < 4.0:
        return (3, "Defined — enterprise-wide, consistent, council-driven")
    elif avg_score < 4.5:
        return (4, "Integrated — embedded in workflows, automated enforcement")
    else:
        return (5, "Optimized — AI-driven, strategic, culture-embedded")


def generate_recommendations(scores):
    """Generate prioritized recommendations based on lowest scores."""
    dim_avgs = {}
    for dim in DIMENSIONS:
        dim_scores = [s for d, s in scores if d == dim]
        dim_avgs[dim] = sum(dim_scores) / len(dim_scores) if dim_scores else 0

    sorted_dims = sorted(dim_avgs.items(), key=lambda x: x[1])

    recommendations = []
    for dim, avg in sorted_dims:
        if avg < 2.0:
            recommendations.append(f"[HIGH] {DIMENSIONS[dim]}: avg {avg:.1f}/5 — Start with basic {dim.replace('_', ' ')}. See references/governance-maturity.md Level 0→1.")
        elif avg < 3.0:
            recommendations.append(f"[MEDIUM] {DIMENSIONS[dim]}: avg {avg:.1f}/5 — Formalize {dim.replace('_', ' ')}. See references/governance-maturity.md Level 2→3.")
        elif avg < 4.0:
            recommendations.append(f"[LOW] {DIMENSIONS[dim]}: avg {avg:.1f}/5 — Improve {dim.replace('_', ' ')} consistency. See references/governance-maturity.md Level 3→4.")
        else:
            recommendations.append(f"[MONITOR] {DIMENSIONS[dim]}: avg {avg:.1f}/5 — Maintain. See references/governance-maturity.md Level 4→5.")

    return recommendations


def main():
    json_mode = "--json" in sys.argv

    if json_mode:
        print(json.dumps({
            "error": "Run interactively without --json for assessment",
            "usage": "python3 scripts/governance-assessment.py"
        }, indent=2))
        return

    print("=" * 60)
    print("  Data Governance Maturity Assessment")
    print("=" * 60)
    print("  Rate each dimension from 1 (worst) to 5 (best).")
    print("  Be honest — the assessment is for you, not anyone else.")
    print("=" * 60)

    scores = []
    for dim, question, low, high in QUESTIONS:
        score = text_prompt(dim, question, low, high)
        scores.append((dim, score))

    # Calculate results
    dim_avgs = {}
    for dim in DIMENSIONS:
        dim_scores = [s for d, s in scores if d == dim]
        dim_avgs[dim] = sum(dim_scores) / len(dim_scores) if dim_scores else 0

    overall = sum(dim_avgs.values()) / len(dim_avgs)
    level, level_label = calculate_maturity(overall)

    print("\n" + "=" * 60)
    print("  RESULTS")
    print("=" * 60)
    print(f"\n  Overall Maturity: Level {level} — {level_label}")
    print(f"  Average Score: {overall:.1f}/5\n")
    print("  Dimension Scores:")
    for dim in DIMENSIONS:
        bar = "█" * int(dim_avgs[dim]) + "░" * (5 - int(dim_avgs[dim]))
        print(f"    {DIMENSIONS[dim]:25s} {bar} {dim_avgs[dim]:.1f}/5")

    print("\n  Recommendations:")
    recs = generate_recommendations(scores)
    for r in recs:
        print(f"    {r}")

    print("\n  For detailed stage descriptions, see:")
    print("    references/governance-maturity.md")
    print("=" * 60)


if __name__ == "__main__":
    main()

# Architecture Decision Record

Use this template to capture architecture decisions during consulting sessions. Fill in each section as the decision is made. Save the result to the team's ADR directory.

```markdown
# ADR-[NUMBER]: [Title]

## Status
[Proposed | Accepted | Deprecated | Superseded]

## Context
What's the problem we're solving? What constraints, assumptions, and forces are at play?

* Business driver:
* Technical context:
* Constraints (budget, timeline, team capability, compliance):
* Key stakeholders:

## Options Considered

### Option A: [Name]
* How it works:
* Pros:
* Cons:
* Migration cost:
* Operational complexity:

### Option B: [Name]
* How it works:
* Pros:
* Cons:
* Migration cost:
* Operational complexity:

*(Add more options as needed)*

## Decision
**Chosen option:** [A / B / C]

**Rationale:**
1. [Primary reason]
2. [Secondary reason]
3. [Tiebreaker]

**Consequences:**
* Positive:
* Negative / tradeoffs accepted:
* What we're explicitly not doing:

## Verification
How will we know this was the right decision?

* [Metric or milestone]
* [Review date]

## References
* [Link to relevant reference file in this skill]
* [Link to vendor docs, case studies, or external sources]
```

## Usage

When I recommend a specific architecture or tool during a consulting session, I'll offer to produce an ADR. If you want one, say "capture that as an ADR" and I'll fill in the template from our conversation.

# Data Architecture Design Session

Use this worksheet to facilitate a decision session. Capture evidence and unresolved assumptions rather than filling gaps with consensus.

## Session Brief

- **Decision or problem:**
- **Business outcome and cost of inaction:**
- **Decision owner:**
- **Participants:** business, domain producers, consumers, data, platform, security, compliance
- **Date and decision deadline:**
- **Non-negotiable constraints:**
- **Evidence gaps:**

## Current State

### Flows and Ownership

- Sources and systems of record:
- Current transformations and storage:
- Operational consumers:
- Analytical, ML, and reporting consumers:
- Data owners and support paths:
- Current quality, freshness, availability, and incident evidence:

### Workloads

| Consumer/use case | Data needed | Access mode | Freshness/latency | Quality or recovery need |
|---|---|---|---|---|
| | | | | |

## Candidate Shapes

| Candidate | Problem it solves | New obligations | Key risks | Evidence needed |
|---|---|---|---|---|
| Centralized | | | | |
| Hybrid | | | | |
| Fabric capabilities | | | | |
| Mesh/domain products | | | | |
| Event-driven product | | | | |

Name what remains centralized and what becomes locally owned. For event-driven candidates, record replay, ordering, late data, compatibility, and consumer recovery decisions.

## Decision Record

- **Chosen shape and scope:**
- **Why it fits the evidence:**
- **Rejected alternatives and conditions that would change the decision:**
- **Ownership and decision rights:**
- **Product quality and service expectations:**
- **Governance checks and escalation path:**
- **Interface-contract handoff:** `api-design-and-evolution`
- **Implementation handoff:** `data-engineering`
- **Platform-operations handoff:** `platform-engineering`

## Experiments and Transition

| Hypothesis | Smallest experiment | Owner | Exit evidence | Reversible action |
|---|---|---|---|---|
| | | | | |

- **Phase 1 foundation:**
- **Phase 2 pilot:**
- **Phase 3 expansion or stop condition:**
- **Metrics:** consumer adoption, quality/freshness, incident recovery, delivery time, cost, and team load

## Open Risks

- Assumption that needs validation:
- Cross-domain dependency:
- Security, privacy, or compliance concern:
- Cost or capacity concern:
- Failure and recovery concern:
- Reason to keep the current architecture for now: