Designing End-to-End Data Architecture

A system diagram becomes an architecture when its boundaries explain how requirements will be met. An arrow from a database to a warehouse leaves important questions unanswered: which source state crosses that arrow, when it becomes visible, whose identity authorizes the transfer, and what happens if the transfer succeeds but its acknowledgement is lost.

Architecture records the decisions that shape how a system can change: where data is stored, how it moves, what each stage promises, and who owns the result. A data architecture connects source systems to useful outputs while accounting for failures, access, cost, and recovery. Existing tools and team skills constrain the choices; consumer requirements give those choices a purpose.

Consider a fictional subscription shop with an application database, payment and support APIs, event files, and a logistics feed. Its consumers need different representations and different deadlines. We will use those requirements to reason about logical layers, physical deployment, consistency, access, and recovery. The quantities and growth scenarios are design assumptions, not reported production measurements.

Start with the consumers and their decisions

Ask what someone will do with the output, when it must be ready, and what happens if it is late or wrong. Then check whether the sources can support that request. For this fictional shop, the following are assumed requirements to confirm with each owner. They are not industry-wide defaults.

Consumer	Output and timing	Design consequence
Finance	Daily revenue by date and currency; ready by 06:00 UTC; unexplained differences reviewed before close	Retain relevant source evidence; agree reconciliation and correction rules
Executives	Defined KPIs by 08:00 UTC	An owned metric definition and visible freshness status
Product analysts	Daily event detail and funnels initially	Governed access to detailed, cleaned events
Fraud operations	Daily manual review initially; a possible later 60-second alert requirement	Document the accepted interim process and the trigger for a faster path
Marketing	Daily customer segments sent to an email service	Reverse ETL: send modeled results to an operational tool
ML team	Daily training data using only information available at the prediction time	Retain relevant history and test for future-information leakage; serving latency is a separate requirement

This table supports a batch-first design for this shop. Another product may need continuous processing from its first day. Shared revenue definitions should have an owner and a reusable implementation; that does not require every consumer to read one physical table. A metric can be materialized in several places if its definition, version, and reconciliation remain controlled.

For the marketing export, define the destination record key and which system owns each field. Updating a segment is not the same operation as sending a campaign: retries must not trigger repeated messages. Specify what happens when a person leaves the segment, and verify destination state rather than treating an accepted API request as proof that every row was applied.

Four logical layers, with explicit promises

Sources → landing/raw → staging/cleaned → core/modeled → marts/serving → consumers
          source evidence   usable records     shared meaning   consumer outputs

Across all layers: ownership, access, scheduling, quality, lineage, recovery, cost

These are logical responsibilities. They can be schemas in one database, separately stored datasets, or views over other layers. Bronze, silver, and gold in a medallion design describe refinement stages, but neither the names nor the number of layers define their guarantees. Add a boundary when it establishes a useful contract, owner, access policy, or independent lifecycle. Even one consumer can justify a boundary when it depends on such a guarantee.

Landing / raw: preserve the evidence you need

Keep received files unchanged when byte-for-byte evidence matters. For a database extract or API response, document what was captured, the source position or extraction time, and any serialization changes. Converting a CSV into Parquet changes its bytes and may change its interpretation; it is not the same as retaining the original file. An event system may preserve ordering within a partition without providing a global arrival order.

Preserved inputs let you rerun a corrected transformation, provided the required data, reference data, and code versions remain available. Define retention by purpose and dataset, with controlled deletion and access. Append-only ingestion prevents routine overwrites; it does not forbid an authorized deletion process. Budget for storage, retrieval, and replay time together.

Staging / cleaned: make source records usable

Parse types, standardize names, handle duplicate deliveries, and quarantine invalid records with reasons. Define the grain, meaning what one row represents: one order, one order version, or one event are different grains. Removing duplicate deliveries must not remove legitimate updates. Convert timestamps only when the source timezone is known, and preserve any business-calendar context needed later.

Core / modeled: agree on shared meaning

Connect source records into customers, orders, payments, and other business entities or events. Shared identity and common source semantics belong here when they support reuse. A domain-specific metric, such as recognized revenue, belongs with its accountable owner; a central core need not own every definition. When a report needs customer country at order time, it needs suitable dated history. A join to the current customer table cannot reconstruct information that was overwritten. Preserve the distinction between a late record and a correction to what an earlier record meant.

Marts / serving: shape the output for its reader

A finance mart might contain one row per day and currency; an analyst may need event-level detail; an application may need a keyed lookup. Marts need not all be wide or aggregated. Rebuildable outputs still need a publication plan: build and validate a replacement, then switch readers to it, keeping the previous version if recovery requires it. Dropping a live mart before rebuilding it creates an outage.

A layer contract should name the producer, grain and keys, schema and units, event and arrival times, supported updates/deletes, quality policy, and consumer notification process. For the finance output, define a complete date/currency population and an owner who can approve corrections. A new nullable column can be compatible for named-column readers yet break SELECT * exports; metric changes can be incompatible without any schema change. Version the contract and test affected consumers before switching them.

For this example, derived tables read lower layers and never write into their inputs. The resulting dependency graph has no cycles and can be built in order. This is a useful local rule, not a ban on governed analyst access to staging or every same-layer dependency in other architectures. Shared business definitions belong in a controlled model or semantic layer; consumer-specific presentation can remain in serving.

Decide which boundaries need stored results

Materialization means storing a derived result instead of calculating it whenever a consumer reads it. A view can avoid an independently refreshed copy, but its freshness still depends on its inputs and the engine that reads them. A stored table can isolate consumers from expensive or unstable upstream queries and give them an explicit published version. It adds storage, refresh work, and the possibility of drift from the source.

For example, the shop can expose a logical order model as a view while publishing a versioned daily finance table. The finance table may remain on yesterday’s approved version while today’s candidate is investigated. That can be the intended behavior if the interface clearly reports its coverage and status. Merely presenting the old result without that information would turn a deliberate hold into misleading freshness.

Several stored layers also multiply the work of corrections and deletion. Every extra copy needs an owner, a refresh or invalidation rule, and a retention policy. Conversely, eliminating all copies can couple every consumer to source availability and query load. The decision follows from the independence each consumer needs, not from a rule that more or fewer layers are always better.

Design operation alongside data flow

The data plane stores and processes records. The control plane configures and coordinates that work: scheduling, permissions, metadata, and operating policy. Quality checks execute against data, while their rules and results inform control decisions; the boundary is conceptual rather than a separate product requirement.

• Orchestration: wait for required inputs, run dependencies in order, retry safely, and stop publication when a required check fails. A retry should not duplicate yesterday’s output.
• Quality and observability: measure freshness, volume, and agreed invariants. Record an owner and a response for each alert.
• Catalog and lineage: record what tables mean and which inputs produced them, including the relevant run and code version.
• Access and environments: separate production permissions, keep credentials out of source code, and use suitable masked or synthetic test data. Promote the same code artifact with environment-specific configuration.
• Recovery and cost: define how to restore service, test that procedure, and attribute storage and processing spend. A cron job can be adequate if dependencies, failure handling, and ownership are explicit.

Choose storage and ingestion from those requirements

A warehouse provides a database designed for analytical workloads. A lake stores datasets as files, often in object storage. A lakehouse combines lake storage with table-management and query capabilities. Table formats such as Iceberg add metadata and transaction mechanisms beyond individual files, but engine compatibility and supported operations still need checking. Warehouses can also work with open tables; proprietary storage is not their defining property. Separating storage and compute can help independent scaling, but it does not by itself guarantee portability.

ETL transforms data before loading the chosen analytical destination; ELT loads it there before transforming it. The distinction is relative to that destination, and one architecture can use both. Choose the transformation boundary from sensitive-data handling, available processing capacity, replay needs, and source/destination constraints. An open table format alone does not guarantee multi-table atomicity or interoperability; check the Iceberg specification and the engines and catalog used for the intended operations.

Batch processes a bounded input; micro-batching repeats that work in small increments; streaming processes an ongoing input. An hourly scheduled job is still batch, and a streaming engine may itself use micro-batches. Compare required latency, source delivery, operating effort, and measured cost. If batch and streaming outputs coexist, agree event-time windows, duplicate handling, late corrections, and metric versions. Writing both to one core layer alone does not make their numbers equal.

A snapshot captures state at a point in time. Comparing two complete snapshots by key can identify rows no longer present, but cannot recover every intermediate event: an order created and deleted between snapshots is absent from both. A watermark extracts changes after a stored position, such as an update timestamp; ties, late updates, and deletes require a policy. Log-based change data capture (CDC) can capture updates and deletes, but depends on capture scope, retained logs, and recovery from the saved position. Plan for redelivery and the ordering guarantees of the specific source. PostgreSQL’s logical decoding documentation explains snapshot alignment and possible repeated delivery after a crash.

For timestamp-based incremental extraction, choose a fixed upper bound for each run and advance the saved checkpoint only after the destination commits successfully. A key can break pagination ties within a stable extraction, but it does not catch rows committed later with an older timestamp. An overlap window plus idempotent writes can reduce that risk only within a justified lateness bound. Hard deletes need a tombstone, a change feed, or a separate comparison. This extraction watermark is different from a streaming event-time watermark, which tracks progress for window processing.

Pull means the platform requests data; push means a producer sends it. Either may be used for state or events. A receiving broker has finite capacity and can fail or reject requests: design acknowledgements, buffering, retries, and backpressure, which slows producers when receivers cannot keep up.

Define consistency at each boundary

An extraction boundary identifies the source state a delivery claims to cover. For one database this might be a consistent snapshot followed by changes from an aligned log position. For independent services there may be several source positions and no common transaction. Reading orders at one moment and payments a minute later does not automatically create a globally consistent business snapshot. The consumer contract must specify whether temporary disagreement is acceptable and how reconciliation resolves it.

The publication boundary determines what consumers see as one release. Atomically replacing an orders table does not also replace a separately maintained customers table or dashboard cache. If a report requires a mutually consistent set, its design needs a supported multi-object transaction or a release manifest that names compatible versions and that readers actually honor. A manifest ignored by one query path supplies no guarantee to that reader.

A writer boundary determines which process may advance the published state. An old backfill can finish after a newer correction and overwrite it unless the destination enforces a version or generation rule. A scheduler lock may prevent two starts but cannot necessarily stop a paused worker from writing after its lease expires. The protected write must reject obsolete work, or the publication mechanism must otherwise serialize the intended order.

These boundaries explain why saving a checkpoint after a destination commit is necessary in many designs but insufficient on its own. There is still a crash window between the two. Retries need to recognize an already-applied operation, and a newer source version may need to reject an older replay. The exact mechanism depends on what the source, destination, and connector can coordinate; the arrow on a diagram should name that mechanism or record it as an unresolved requirement.

Separate access boundaries from folder boundaries

A schema, bucket prefix, workspace, or account is an organizational unit whose isolation depends on the product and configuration. Two workspaces using the same constrained compute pool can still interfere with each other. Two schemas readable by the same broad service account are not separate confidentiality boundaries. Mark which boundary controls identity, storage access, compute contention, and failure propagation.

Follow the identity through the complete read path: a human or application invokes a service, that service may use its own credentials, and the engine resolves data and policy through a catalog or storage API. A row filter enforced by a SQL endpoint does not automatically protect direct file access. Exports, caches, notebooks, and model inputs need to be included in the access model. The architecture must either preserve the policy through these paths or restrict paths that bypass it.

Location has several meanings too. Where data is stored, where computation occurs, who can access it, and where query results or backups are exported are distinct questions. A storage-region choice answers only one of them. Network routes, private endpoints, encryption, and key access are part of the data path and recovery plan, not decorations on a completed diagram.

Write measurable non-functional requirements

Non-functional requirements describe how well the system must operate. An SLO is a service-level objective, such as a target proportion of on-time daily runs. RPO is the maximum tolerable data-loss interval; RTO is the target time to restore service. The following are illustrative design inputs, not legal retention rules or measured facts about a real company.

Requirement	Illustrative requirement and its meaning
Freshness	At least 99% of daily finance runs ready by 06:00 UTC over a calendar month
Accuracy and completeness	Reconcile order counts and amounts by date, currency, and agreed status; investigate every unexplained difference before close
Volume and growth	Assume 40 million events and 200 GB raw per day at stage 2; measure peak rates and query concurrency
Retention	Assign a period to each dataset and purpose; track deletion through raw, derived tables, exports, and backups
Recovery	Example: RPO 24 hours, RTO 4 hours; confirm the accepted loss boundary and identify every dependency required for usable service
Privacy	Classify personal fields, define authorized roles, and specify test-data handling
Availability	Agree the acceptable success rate during business hours and the incident response owner
Cost	Set a monthly budget and an 80% alert; include ingestion, queries, retrieval, and recovery tests

Define the measurement population and start/end points. With one scheduled finance run per day, a 30-day month has only 30 observations: one missed deadline gives 29/30 = 96.67%, so a 99% monthly target permits no misses in that month. Decide in advance how source delays, planned exclusions, reruns, and incomplete but timely outputs count. Retrying a failed run must not add an extra success to the denominator. Report correctness/completeness separately as well as the ready-by deadline.

Check requirements together. A four-hour recovery target is not credible if retrieving archived input alone takes longer. Restoring files is insufficient when catalog metadata, source positions, encryption keys, or publication records are unavailable or incompatible. Replication can preserve availability while also propagating an accidental deletion; a retained recovery point addresses a different failure. AWS’s disaster-recovery guidance distinguishes resilience objectives and recovery planning. The applicable failure model, accepted loss, and restoration time determine the design, rather than a general rule to use a second region.

Choose a deployment pattern, then compare products

One physical design can concentrate analytical storage and compute in a managed warehouse, with ingestion delivering records and transformations running near those records. Another can center on object storage, a transactional table format, and several compatible engines. An integrated analytics platform can bundle orchestration, catalog, compute, and serving. These patterns overlap: a warehouse may read open tables, and a lakehouse platform may provide managed SQL warehouses.

Snowflake, BigQuery, Redshift, Databricks, and Fabric are worth understanding beyond their category labels. Product comparison should ask where tables and metadata live, how compute is allocated, which interfaces enforce permissions, what makes a write visible, and what remains to operate. The vendor may manage infrastructure while the customer still owns source contracts, business meaning, permissions, capacity choices, and failure response. A managed service changes the division of work; it does not remove these responsibilities.

Connector and engine compatibility are architectural requirements. Naming Iceberg does not settle which catalog API, reader features, delete representation, or credentials a consuming engine supports. Naming CDC does not settle snapshot handoff, schema changes, replay limits, or restart behavior. List the operations the intended reader and writer paths require, including corrections and deletes, rather than accepting a shared product label as interoperability evidence.

Cost follows the whole route. Count stored copies, ingest charges, queries or compute time, idle capacity, cross-region transfer, backfills, recovery, and operating labor where applicable. Independent compute can reduce workload interference while leaving shared metadata, storage throughput, or quotas as bottlenecks. A lower query price can be offset by more data movement or by a deployment the team cannot support reliably.

Record decisions, including their limits

An architecture decision record (ADR) preserves the context, choice, alternatives, and consequences. Record assumptions separately from measured costs, and write the condition for revisiting a decision. Keep proposed and accepted decisions distinct; when an accepted decision changes, link the replacement and retain the earlier rationale. For example:

ADR-0003: Preserve received vendor files separately from parsed tables
Status: proposed for the fictional shop
Context: We need to diagnose parsing errors and replay retained deliveries.
Decision: Keep received CSV files in a restricted landing area, identified by
          source, arrival time, and delivery ID. Build typed tables separately.
Alternatives: Warehouse landing tables; keeping only parsed Parquet.
Trade-off: Original files retain parsing evidence but add storage and access work.
Retention: Agree dataset-specific periods and controlled deletion before launch.
Revisit: Measured retrieval cost, restore time, or source contracts change.

Plan change without assuming rollback restores data

A schema can remain syntactically compatible while its meaning changes. Switching an amount from gross to net under the same column name is one example. Treat contract changes as migrations: identify affected consumers, describe old and new semantics, and choose an overlap or coordinated cutover policy. Renaming a field and adding a nullable field are not interchangeable risks, and consumers using SELECT * may react differently from named-column queries.

For a new pipeline or platform, compare old and candidate outputs against the same source boundary and definition before interpreting differences. During catch-up, source changes continue; the cutover needs a clear point after which the new path owns publication. If both paths write, define conflict handling and ownership. If the old path stops receiving updates, routing readers back to it later can expose stale state rather than undo the migration.

Code rollback and data repair therefore need separate plans. Reverting a transformation does not restore overwritten history, retract an external message, or repair a destination already changed under the new rules. Architecture decisions should name the retained evidence and recovery path that make a reversal possible, along with the point beyond which forward correction is the safer option.

A fictional shop grows in three stages

The shop has 200,000 customers, an application database, payment and support APIs, event files, and a logistics CSV feed. These are illustrative inputs, not a report of the author’s employer. The table above describes its initial consumers.

Stage 1: daily processing with explicit ownership

Sources → scheduled ingestion → retained landing files
                                    ↓
                         staging → core → marts → consumers
                         SQL on DuckDB or a small warehouse
Operations: dependency scheduling, validation, restricted access, recovery procedure

Use a small deployment if measured runtimes and concurrency fit it. The largest table being 30 GB is not enough evidence by itself: joins, memory use, disk spill, and query load also matter. The team accepts daily fraud review for now. A confirmed 60-second requirement would change the design immediately; it should not be dismissed merely to keep the diagram simple.

Stage 2: grow where measured constraints appear

Assume two years later the shop has 2 million customers and 40 million events per day. A four-hour database export misses its agreed window; concurrent analyst queries miss their latency target. Evaluate CDC for the affected tables and a warehouse or lakehouse for the analytical workload. If several engines need shared tables, test an open table format with those engines. Keep original landing evidence separately where required. Expand cataloging, access controls, deployment automation, and cost tracking from their stage-1 baseline. No fixed cost ratio justifies these changes; compare representative workloads.

Stage 3: add the path that the latency requirement needs

Events → broker → stream processor → low-latency fraud store → fraud tool
                       ↓
             retained events/results → shared models → daily reports

Allocate the 60-second end-to-end target across the critical path. A provisional budget might allow 20 seconds for source delivery, 15 for processing, 10 for serving visibility, and 5 for alert delivery, leaving 10 seconds of margin. These are design allowances to validate, not measured percentiles that can simply be added. Measure actual end-to-end delay and account for source clock quality, queueing, and late events. If the source delivers every five minutes, a faster processor alone cannot meet 60 seconds.

Now suppose fraud operations needs signals within 60 seconds of the source event. Measure that delay end to end, including ingestion and the fraud store, not just processor runtime. Assign an operator, a replay procedure, stable event identifiers for deduplication, and rules for late events. Provisional alerts and finalized daily reports may differ until late data is reconciled. The useful continuity across stages is ownership and meaning; physical storage or layer boundaries may change when a documented requirement warrants it.

What a complete architecture description makes visible

For the shop, the useful result is a set of connected explanations: which consumer decisions the system supports; the grain, source coverage, and correction rules of its outputs; the logical transformations and their physical placement; and the publication, identity, and recovery boundaries that make the commitments credible. A dependency diagram provides one view. Contracts, source and destination capabilities, and a few well-scoped decision records explain the behavior the boxes cannot show.

Some choices remain hypotheses before a project begins. Expected throughput, peak concurrency, recovery duration, and operating cost may require later measurement. Marking them as assumptions preserves the distinction between a plausible design and demonstrated capacity. Conceptual preparation is sufficient to identify what must be decided and why; it should also reveal which claims only implementation and operation can establish.

References and scope

References:

• Apache Iceberg table specification
• PostgreSQL logical decoding concepts
• AWS Well-Architected Framework, Reliability Pillar: Plan for Disaster Recovery (DR)

Source-specific guarantees must be checked for the deployed versions and connectors. The architecture patterns and shop scenarios are explanatory design examples, not measured product comparisons or an executed deployment.