Data Architecture Decision Framework

Configure a reference architecture for your advancement data platform based on your environment, scale, and analytical priorities.

Environment profile

The tool bases its recommendations in all four decision areas on these inputs. Set them to match your current environment.

The framework recommends a pattern for each of four architecture decisions and generates an implementation plan.

Choose where your analytical data is stored and processed. Your choice sets which analytical workloads your team can run and how much work it takes to keep them running.

Architecture detail

Compute and storage scale independently; you pay for query execution time, not idle capacity. Tables are columnar-compressed and partitioned for analytical query patterns. SQL is the primary interface, which means your existing team's skills transfer directly.

Gift transactions, constituent profiles, fund hierarchies, and campaign structures are inherently relational and well-structured. A warehouse handles these workloads with minimal operational overhead. In the medallion pattern, bronze tables hold raw CRM exports with source-system naming preserved, silver tables normalize and resolve constituent identity across systems, gold tables build Kimball star schemas shaped for specific reporting use cases.

When admins change CRM custom fields often, the fixed schema slows your team down. Your team must structure unstructured data (email engagement logs, web analytics sessions, wealth screening scores in varying formats) before it loads. Query cost scales with bytes scanned, making unpartitioned full-table scans expensive. Partition and cluster on the columns your queries filter most: gift date, constituent ID, fund code.

Architecture detail

Data sits in Parquet files on object storage. The table format (Delta Lake, Apache Iceberg, Apache Hudi) adds a metadata and manifest layer that provides transactional guarantees, schema enforcement, and point-in-time queries on top of raw files. Any compute engine that understands the format can read and write: Spark, Trino, Flink, or native warehouse SQL interfaces.

A lakehouse makes more sense in advancement when the data team plans to run ML and AI workloads: propensity scoring, donor segmentation, engagement prediction. A lakehouse avoids the cost of running both a warehouse and a separate ML platform. When CRM custom fields change, schema evolution adds the new columns without breaking downstream consumers. Storage costs on object storage run at a fraction of managed warehouse storage at scale.

Ad hoc queries run slower than on a purpose-built warehouse unless the team tunes compaction, file sizing, and Z-ordering. The team must be comfortable with infrastructure beyond SQL, or invest in a SQL-native lakehouse interface. For teams under five people focused primarily on reporting, a managed warehouse is almost always the better starting point.

Architecture detail

The engine stores no data. Connectors adapt each backing system's API or query interface into a common catalog. At query time, the coordinator parses SQL, pushes predicates and projections where the connector supports it, and dispatches parallel execution across worker nodes that query the underlying systems directly. A single SQL statement can join a CRM table with a Postgres table with a Parquet file on object storage.

Federated query works best alongside a central platform. Organizations with strong data sovereignty requirements, many specialized systems, or political constraints that prevent full centralization use it to give one point of access while the data stays in place. During a migration, your team can also use it to query across the legacy and replacement systems without building throwaway ETL.

Performance is bounded by the slowest underlying source. Complex analytical workloads, large aggregations, and many-table joins perform poorly compared to native warehouse execution. Each source system enforces its own governance and security; no one manages them centrally. This is rarely the right sole analytical platform for teams with significant reporting needs.

Anti-patterns

  • Business logic in the bronze zone. If you transform data during ingestion, the code becomes hard to maintain and you cannot re-process the data when a transformation turns out wrong.
  • Skipping the silver zone. If you go directly from raw data to consumption marts, each mart must re-implement cleaning and normalization logic on its own.
  • Gold zone sprawl. The team builds a new mart for each dashboard request, and the copies of the business logic come to differ across dozens of nearly identical tables.
  • Premature lakehouse adoption. For SQL-only teams focused on reporting, the added operational work outweighs the flexibility gained.

Reference architecture

The tool builds this from your profile and selections. The diagram and summary update as you change inputs.

Implementation roadmap

Tell us what you're working on.

Start a conversation