Data Architecture Decision Framework

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.

For advancement data, the fit is strong. Gift transactions, constituent profiles, fund hierarchies, and campaign structures are inherently relational and well-structured. A warehouse handles these workloads with minimal operational overhead. The medallion pattern maps cleanly: 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.

The trade-offs: schema rigidity slows iteration when CRM custom fields change frequently. Unstructured data (email engagement logs, web analytics sessions, wealth screening scores in varying formats) requires pre-structuring before load. 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.

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.

The case for a lakehouse in advancement strengthens when the data team's ambitions include 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. Schema evolution handles CRM custom field changes gracefully; additive columns merge without breaking downstream consumers. Storage costs on object storage run at a fraction of managed warehouse storage at scale.

The trade-off is operational complexity. Ad hoc query performance lags purpose-built warehouses without careful compaction, file sizing, and Z-ordering. The team needs comfort with infrastructure beyond SQL, or must 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.

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 provide unified access while data stays in place. It also works as a migration bridge: query across legacy and replacement systems during a transition 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. Governance and security push down to each source system rather than being managed centrally. This is rarely the right sole analytical platform for teams with significant reporting needs.

Managed connector platforms maintain pre-built integrations for CRM platforms, SIS, event tools, and giving systems. Each connector tracks its own sync state, handles incremental extraction via cursors or platform-native change tracking, and manages schema evolution when source fields change. Data lands raw in a bronze schema; a SQL-based transformation framework then models it through staging, intermediate, and mart layers.

The extraction layer handles the hardest operational problems: API pagination, rate limit management, error retry, and incremental state tracking. For a Salesforce-based CRM, a managed connector uses the Bulk API for initial historical loads and the platform's change tracking for ongoing incremental sync, avoiding the daily API quota constraints that throttle REST-based polling approaches.

The trade-off is a recurring SaaS cost that scales with connector count and sync frequency. The transformation logic lives in SQL, which is portable, version-controlled, and testable, but the extraction layer is a managed dependency your team does not control.

Change data capture reads the database transaction log and reinterprets it as a change stream, capturing inserts, updates, and deletes at the row level without querying source tables. The operational load on the source system is minimal because the log exists regardless; CDC reads what the database engine already writes for its own durability.

For CRM platforms, native CDC is the preferred approach. Salesforce's Change Data Capture feature publishes events to its event bus whenever a tracked object's records change, subscribable via the Pub/Sub API. Dynamics 365 offers Change Tracking through the Web API, and Synapse Link for continuous export to Azure Data Lake Storage. These platform-native mechanisms bypass standard API rate limits and deliver changes within seconds.

The trade-off is infrastructure and operational maturity. A CDC-first pipeline involves a message bus for durable, ordered delivery of change events, a consumer framework for processing and landing events in the bronze layer, and monitoring for subscriber lag, schema evolution, and event retention. CRM event buses retain events for a limited window (72 hours on Salesforce); a downstream outage longer than that requires a full resync. This pattern fits teams with dedicated data engineering capacity.

Most advancement data pipelines have a small number of feeds where latency matters (giving day totals, payment confirmations, event capacity) and a larger number where daily or hourly batch is adequate. A hybrid architecture uses managed ELT for the batch-appropriate majority and adds CDC only for the specific feeds where near-real-time delivery has real business value.

This is the pattern that scales most gracefully. Managed connectors handle the operational burden for stable, batch-cadence data flows. CDC handles the narrow set of feeds where sub-hour freshness matters. The two patterns share a common landing zone (the bronze layer) and a common transformation framework, so downstream modeling and governance remain unified.

The risk in a hybrid approach is scope creep: every feed's owner argues their data is time-sensitive. Each CDC feed should require a concrete justification grounded in the decision cadence of the humans consuming it. Gift processing consumed in daily reports does not need streaming. Gift processing consumed during a live telethon does.

Deterministic matching links records on fields that must match exactly. A verified email shared between CRM and SIS connects the two records with certainty. A composite key (last name + date of birth + zip code) provides a secondary path when no single strong identifier exists.

The method is computationally cheap, operationally simple, and produces no false positives. When it finds a match, the match is correct. The failure mode is false negatives: any typo, missing field, or legitimate variation (nickname vs. legal name, maiden vs. married name, outdated email) produces a missed match with no mechanism to quantify the uncertainty.

For advancement shops with a single CRM as the dominant source, deterministic matching on a CRM-assigned constituent ID handles most cross-system matching if that ID propagates to downstream systems. The problem surfaces when integrating external sources (wealth screening results, event platform registrations, online giving forms) that create records without looking up the existing constituent ID first.

Probabilistic matching compares records across multiple fields with similarity scores rather than exact equality. Each field agreement contributes a match weight proportional to how discriminating that agreement is: matching on an unusual surname contributes strong evidence; matching on a common state code contributes almost none. The Fellegi-Sunter framework formalizes this as a log-likelihood ratio computed per field pair, summed into an overall confidence score.

Blocking restricts full comparison to candidate pairs sharing a coarse key (same zip code + phonetic code of last name) before running expensive field-by-field scoring. Without blocking, a 500K-record dataset compared against itself produces 125 billion pair comparisons. String similarity metrics (Jaro-Winkler for names, edit distance for addresses) quantify the degree of match on each field.

Two thresholds classify scored pairs: above the upper threshold, auto-merge; below the lower, reject; the band between routes to a data steward for human review. Household resolution uses this same framework with address-based blocking and name-matching verification, handling the common case where spouses share an address but have different surnames.

A full MDM implementation combines deterministic pre-matching on strong keys, probabilistic scoring as a fallback, and a governed steward workflow for uncertain matches. The golden record is assembled from multiple source records via survivorship rules that determine which conflicting value wins per attribute: the CRM is authoritative for giving history and preferred name; the SIS is authoritative for degree data and legal name; neither is authoritative for current employer, so most-recent-wins applies.

Survivorship rules are rarely a single global strategy. Production systems combine approaches per attribute: source-system precedence for authoritative fields, most-recent-wins for volatile attributes like employer, most-complete-wins for contact information. The system must support unmerge as a first-class operation. False positive merges are often worse than unresolved duplicates because they contaminate giving histories and assignment records.

At this level, the MDM platform maintains a registry of cross-references linking each source-system record to its golden record. New records entering any source system are matched against the golden record before creation, preventing duplicates at the point of entry. This is the right investment for large organizations with multiple source systems, dedicated data stewards, and operational processes that depend on accurate identity: prospect assignment, major gift solicitation, and board-level reporting.

A daily batch pipeline extracts source-system data on a schedule, processes it through the full medallion stack, and delivers refreshed gold-zone tables by the time consumers need them. This is the right default for advancement analytics. Gift processing reports, portfolio reviews, campaign progress dashboards, board reporting, and prospect research scores are all consumed on daily or weekly cadences by humans making decisions at the same pace.

Batch processing is simpler to build, operate, monitor, and debug than streaming. Failures are contained to a run; a failed batch can be re-run without complex offset management. Schema changes in source systems surface at the next run and can be handled in a controlled maintenance window. Cost is predictable and bounded.

Data observability in a batch context focuses on freshness (did the pipeline run on time), volume (did the expected number of rows arrive), and schema (did any source fields change shape). These three checks, run automatically after each batch, catch the majority of data incidents before downstream consumers encounter a stale or broken report.

Most advancement data platforms have one or two data domains where near-real-time matters and a dozen where batch is adequate. Selective real-time adds CDC-based streaming to specific feeds, landing them in bronze alongside batch-loaded data, and processing them through a shared transformation pipeline with more frequent refresh.

The feeds that typically justify near-real-time in advancement: giving day and telethon gift totals (a live event where a running total drives decisions), payment gateway confirmations (donors expect immediate acknowledgment), event registration during capacity-constrained programs, and matching-gift deadline tracking. Everything else, including the vast majority of constituent data, prospect research scores, and campaign-level reporting, remains on daily batch.

The observability layer expands to include streaming-specific monitors: subscriber lag, event delivery rate, and retention boundary alerts. On Salesforce, the 72-hour event retention window means an outage longer than three days requires a full resync, not a replay. Each streaming feed should have a concrete justification: a named decision made by a human within the latency window that batch already provides.

A stream-first architecture uses change data capture or event-driven publication as the default integration pattern for all source systems, processes events through a streaming framework (Flink, Kafka Streams, or equivalent), and materializes analytical tables via continuous incremental view maintenance rather than scheduled batch runs.

The technical argument for stream-first is simplicity of the programming model: one pipeline, one codebase, one processing framework, rather than maintaining both batch and streaming code paths. Reprocessing and backfill are handled by replaying the event log from an earlier offset.

The practical argument against stream-first for most advancement shops: donor and constituent data is fundamentally relational. Gifts, pledges, households, relationships, and multi-table joins across CRM objects are the norm. Stream processing handles append-only event data well; it struggles with the join-heavy, lookup-intensive patterns characteristic of CRM analytical workloads. Single-CRM advancement shops rarely have the cross-domain real-time requirements that justify this level of infrastructure investment.