Context and scale
This blueprint is for a small product team that needs trustworthy reporting without adopting a distributed data platform. The application already writes to PostgreSQL. A separate PostgreSQL database will hold analytics data, and one scheduled job will extract changed records, load them, and run dbt Core transformations overnight.
The chosen cloud is AWS. The design uses Amazon RDS for PostgreSQL, Amazon S3 for durable extracts, Amazon ECS on Fargate for the job, Amazon EventBridge Scheduler for its schedule, Amazon ECR for the container image, and AWS Secrets Manager for credentials. These are managed building blocks around a single batch process—not a streaming platform.
Assumptions for this blueprint:
| Dimension | Assumption |
|---|---|
| Users | Fewer than 1,000 daily application users; roughly 5–20 internal dashboard users |
| Requests/s | Average application traffic below 5 requests/s; short peaks below 25 requests/s |
| Data volume | 20 GB of analytics data initially; up to 2 GB of changed source data per night |
| Team size | Two engineers, with occasional help from a data or product analyst |
| Budget | Target incremental cloud spend below $200/month, excluding the application’s existing infrastructure and analyst tooling |
These are planning assumptions, not capacity guarantees or vendor price quotes. Validate the actual dataset size, query load, network layout, and regional prices before deployment.
The team’s primary constraint is operational attention. The architecture should tolerate a missed run, make a bad run visible, and let engineers rerun a date range without hand-editing production tables. It should not require an always-on orchestration service, a warehouse specialist, or a second team to operate it.
The target outcome is a small set of well-tested models: a fact table for orders, dimensions for customers and dates, and documented definitions that product and finance teams can use consistently. Nightly freshness is acceptable; sub-minute analytics is not a requirement.
Requirements
The first release should answer a narrow set of questions well. It should not attempt to reproduce every application table or build a general-purpose data platform.
Functional requirements:
| Requirement | Design response |
|---|---|
| Extract changed application records | Read from a restricted PostgreSQL account using an indexed updated_at column |
| Keep an auditable copy of each extract | Write compressed, date- and run-scoped objects to S3 |
| Load data for transformation | Upsert extracted records into analytics PostgreSQL raw tables |
| Build business-facing models | Run dbt Core models, tests, and documentation generation in the batch container |
| Support reruns | Record run state and watermarks; make loading idempotent by source key |
| Publish freshness and quality | Emit job metrics and fail the run when critical dbt tests fail |
Non-functional requirements:
| Requirement | Target |
|---|---|
| Freshness | Complete by 07:00 local reporting time on ordinary nights |
| Recovery | An operator can rerun a failed date range without duplicating facts |
| Availability | Analytics may be stale after a failure; application writes must remain unaffected |
| Performance | Source extraction must be bounded and use an index; transformation must fit within the scheduled window |
| Security | No public database endpoint; secrets are not embedded in images or source control |
| Operability | One container image, one scheduled task definition, and a documented manual run procedure |
| Cost | Avoid always-on compute and unnecessary network appliances at the assumed scale |
A missed nightly run is a data-freshness incident, not an application outage. The system should make that distinction explicit in dashboards and alerts.
High-level design
The application database remains the system of record. The batch task reads from it with a dedicated read-only account, stages extracts in S3, loads the analytics database, and runs dbt against that database. Keeping the analytics database separate contains reporting queries and transformations that would otherwise compete with application traffic.
flowchart LR
App["Application"] --> Source["Amazon RDS for PostgreSQL"]
Schedule["Amazon EventBridge Scheduler"] --> Task["Amazon ECS Fargate task"]
Task --> Source
Task --> S3["Amazon S3 raw extracts"]
Task --> Warehouse["Amazon RDS for PostgreSQL analytics"]
Task --> Secrets["AWS Secrets Manager"]
Task --> Logs["Amazon CloudWatch Logs"]
ECR["Amazon ECR"] --> Task
Warehouse --> Consumers["Dashboards and analysts"]The nightly Fargate task extracts, loads, and transforms data between managed AWS services.
The Fargate task runs in the same VPC as the databases. The source and analytics databases are private. The task has no inbound rules; its security group permits outbound connections only to the required database ports and approved service endpoints. Select a network design that provides the task access to ECR, S3, Secrets Manager, and CloudWatch Logs; at this scale, compare NAT gateway cost with the cost and complexity of private endpoints rather than adding either by habit.
The task’s sequence is deliberately linear: extract, persist the extract, load raw tables, run dbt, then advance the watermark. If any step fails, the run is marked failed and the previous successful watermark remains authoritative. An operator can then retry safely.
Cloud service mapping
AWS is the concrete implementation. The other cloud columns show equivalent building blocks, not a claim that their operational semantics or prices are identical.
| Component | AWS choice | Azure equivalent | Google Cloud equivalent |
|---|---|---|---|
| Operational PostgreSQL | Amazon RDS for PostgreSQL | Azure Database for PostgreSQL | Cloud SQL for PostgreSQL |
| Analytics PostgreSQL | Amazon RDS for PostgreSQL | Azure Database for PostgreSQL | Cloud SQL for PostgreSQL |
| Nightly container job | Amazon ECS on AWS Fargate | Azure Container Apps Jobs | Cloud Run Jobs |
| Schedule | Amazon EventBridge Scheduler | Azure Logic Apps or Container Apps Jobs schedule | Cloud Scheduler |
| Extract storage | Amazon S3 | Azure Blob Storage | Cloud Storage |
| Container registry | Amazon ECR | Azure Container Registry | Artifact Registry |
| Secrets | AWS Secrets Manager | Azure Key Vault | Secret Manager |
| Logs and metrics | Amazon CloudWatch | Azure Monitor | Cloud Logging and Cloud Monitoring |
| Identity for deployment | IAM role assumed through GitHub Actions OIDC | Microsoft Entra workload identity federation | Workload Identity Federation |
Keep the application and analytics databases in the same region. Cross-region extraction adds latency, egress cost, and failure modes without helping this single-region workload. If the application database already exists, this design does not require moving it.
Data design
The target database uses three layers. raw holds source-shaped records with ingestion metadata, staging applies consistent types and naming, and marts contains business-facing facts and dimensions. Keep the boundaries visible in PostgreSQL schemas; they make access control and dbt project structure easier to reason about.
The initial grain is one row per order in marts.fct_orders. Customer attributes live in marts.dim_customers; dates are represented by a date dimension or a date field with a documented calendar convention. Store monetary values as fixed-precision decimals, not floating-point numbers.
erDiagram
DIM_CUSTOMERS ||--o{ FCT_ORDERS : places
DIM_DATE ||--o{ FCT_ORDERS : dates
DIM_CUSTOMERS {
bigint customer_id PK
text customer_name
text region
timestamptz updated_at
}
DIM_DATE {
date date_day PK
integer year
integer month
integer day
}
FCT_ORDERS {
bigint order_id PK
bigint customer_id FK
date order_date FK
numeric amount
text status
timestamptz source_updated_at
}The first mart models one order per fact row, linked to customer and calendar dimensions.
A small initial DDL gives the raw layer explicit keys and makes repeat loads safe. Add source-specific columns only when a real reporting use case needs them.
CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS marts;
CREATE TABLE IF NOT EXISTS raw.orders (
order_id bigint PRIMARY KEY,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
amount numeric(12, 2) NOT NULL,
status text NOT NULL,
ingested_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS marts.dim_customers (
customer_id bigint PRIMARY KEY,
customer_name text NOT NULL,
region text,
source_updated_at timestamptz NOT NULL
);
CREATE TABLE IF NOT EXISTS marts.fct_orders (
order_id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES marts.dim_customers(customer_id),
order_date date NOT NULL,
amount numeric(12, 2) NOT NULL,
status text NOT NULL,
source_updated_at timestamptz NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_raw_orders_updated_at
ON raw.orders (updated_at);
The application extraction query should use a stable, indexed cursor such as updated_at, with a small overlap to catch delayed commits and timestamp precision differences. The warehouse load upserts by source primary key. dbt then selects the latest raw record for each key and materializes the mart incrementally.
Do not treat a timestamp watermark as a substitute for idempotency. Store run identifiers, extraction bounds, row counts, and completion state in an ops schema. Advance the committed watermark only after the load and critical dbt tests succeed. If a source can delete records, represent deletions explicitly—through soft-delete flags or a separate deletion feed—because an update-only extract will not discover a hard delete.
Low-level design
The container contains the extraction code, dbt Core project, profiles configuration, and dependency lock files. The entry point coordinates the phases but does not contain business transformations; those belong in dbt models where they can be tested and reviewed as SQL.
classDiagram
class NightlyJob {
+run()
+record_status()
}
class Extractor {
+extract(since, until)
+write_manifest()
}
class Loader {
+copy_to_raw()
+upsert_records()
}
class DbtRunner {
+deps()
+build()
+test()
}
class RunRepository {
+begin_run()
+commit_watermark()
+fail_run()
}
NightlyJob --> Extractor
NightlyJob --> Loader
NightlyJob --> DbtRunner
NightlyJob --> RunRepositoryThe coordinator sequences work; extraction, loading, transformation, and run state stay separate.
A minimal dbt model can establish the project convention and validate connectivity before the team builds its first mart:
-- models/staging/stg_orders.sql
select
order_id,
customer_id,
created_at::date as order_date,
amount::numeric(12, 2) as amount,
lower(status) as status,
updated_at as source_updated_at
from {{ source('raw', 'orders') }}
Notice that this model standardizes types and status casing without mixing in business-specific aggregation.
A realistic incremental mart should define its grain, key, and late-arriving update behavior explicitly:
-- models/marts/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id',
on_schema_change='fail'
)
}}
select
order_id,
customer_id,
order_date,
amount,
status,
source_updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where source_updated_at >= (
select coalesce(max(source_updated_at), '1900-01-01'::timestamptz)
from {{ this }}
)
{% endif %}
Notice that the model is rerunnable by key, and the inclusive cutoff deliberately favors a harmless reprocessing of boundary rows over silently missing them.
The production model should have dbt tests for uniqueness and non-null keys, a relationship test from orders to customers, and accepted-value tests for status. The extraction overlap and the incremental cutoff should be chosen together: a model that filters only on the target’s maximum timestamp can still miss a late update if the source’s updated_at is unreliable.
For the loader, use a transaction around each target batch: copy to a temporary staging table, upsert into raw.orders, and commit. Keep S3 objects even after a successful load for a bounded retention period. A manifest should include the run ID, source bounds, row count, object key, and checksum. That makes an incident replay a controlled operation rather than a guess about which rows were read.
Key flows
The normal run starts from a scheduled event. The task obtains credentials at runtime, establishes a source window from the last successful watermark, and executes the pipeline. Only a successful run advances that watermark.
sequenceDiagram
participant Scheduler as EventBridge Scheduler
participant Task as ECS Fargate task
participant Secret as Secrets Manager
participant Source as Source PostgreSQL
participant S3 as Amazon S3
participant Warehouse as Analytics PostgreSQL
participant Dbt as dbt Core
Scheduler->>Task: Start task
Task->>Secret: Read credentials
Task->>Warehouse: Create run record
Task->>Source: Read changed rows
Task->>S3: Write extract and manifest
Task->>Warehouse: Copy and upsert raw rows
Task->>Dbt: Run build and tests
Dbt-->>Task: Exit status
Task->>Warehouse: Commit watermark or mark failureThe watermark is committed only after the transformations and required tests complete.
A failed run should be recoverable using the recorded run metadata and retained extract. For source connection failures, retry the extraction with bounded backoff. For a failed transformation, prefer rerunning dbt against already-loaded raw data instead of re-extracting, unless the raw load itself was incomplete.
sequenceDiagram
participant Operator
participant Task as ECS Fargate task
participant S3 as Amazon S3
participant Warehouse as Analytics PostgreSQL
participant Dbt as dbt Core
Operator->>Task: Start replay with run bounds
Task->>S3: Read retained extract
Task->>Warehouse: Reload idempotently
Task->>Dbt: Rebuild affected models
Dbt-->>Task: Report tests
Task->>Warehouse: Update run state
Task-->>Operator: Exit status and run IDReplay uses explicit bounds and an idempotent load rather than editing the warehouse by hand.
Build and release
The repository should contain the dbt project, the Python job, container definition, infrastructure code, and deployment workflow. Pin Python dependencies and dbt packages; commit the lock files. Build one image from a reviewed commit and deploy that image by immutable digest or commit-tagged reference.
This Terraform excerpt shows two foundational resources. Production infrastructure should also define the RDS instances and subnet groups, security groups, task execution and task roles, ECS task definition, EventBridge schedule, log group, Secrets Manager policies, and image repository.
resource "aws_s3_bucket" "extracts" {
bucket = "${var.name}-extracts-${var.account_id}"
}
resource "aws_s3_bucket_versioning" "extracts" {
bucket = aws_s3_bucket.extracts.id
versioning_configuration {
status = "Enabled"
}
}
resource "aws_s3_bucket_server_side_encryption_configuration" "extracts" {
bucket = aws_s3_bucket.extracts.id
rule {
apply_server_side_encryption_by_default {
sse_algorithm = "AES256"
}
}
}
resource "aws_cloudwatch_log_group" "job" {
name = "/analytics/${var.name}/nightly"
retention_in_days = 30
}
Notice that extract retention and log retention are explicit operational decisions, not provider defaults.
A CI workflow should test the project before publishing an image. Use GitHub Actions OIDC to assume a narrowly scoped AWS role instead of storing long-lived AWS access keys. The deployment role should not be available to untrusted pull-request workflows.
name: analytics-ci
on:
pull_request:
push:
branches: [main]
permissions:
contents: read
jobs:
test:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-python@v5
with:
python-version: "3.12"
- run: pip install -r requirements.txt
- run: python -m pytest
- run: dbt deps
- run: dbt parse
- run: dbt compile --target ci
Notice that pull requests validate parsing and compilation without needing production credentials or production data.
The release workflow can add an authenticated integration job, build and scan the container, push it to ECR, and update the ECS task definition. Deploying a new job image should not automatically trigger a production backfill. Run an explicit smoke execution, inspect its run record and dbt results, then allow the regular schedule to use the new image.
The test strategy should match the failure boundaries:
| Test level | What to test | Where it runs |
|---|---|---|
| Unit | Watermark calculations, manifest validation, retry limits, row mapping | Every pull request |
| Integration | PostgreSQL upsert behavior, transaction rollback, dbt materialization | CI with disposable PostgreSQL |
| Contract | Required source columns, types, nullability, and status values | CI against a representative fixture; nightly against the source |
| Load | Bounded extraction and transformation time at an assumed peak batch size | Before launch and after major volume changes |
Do not run load tests against the production application database. Use a restored or synthetic dataset, and confirm the source query plan uses its intended index.
Observability
A batch pipeline has a different shape from a request-serving API, but service RED metrics and resource USE metrics remain useful when applied to the task, database, and storage path. Instrument the job with a stable run_id so logs, metrics, traces, S3 manifests, and warehouse run records can be correlated.
A structured log event should describe a phase and its outcome without including credentials or row-level sensitive data:
{
"timestamp": "2026-10-31T02:14:22Z",
"level": "INFO",
"service": "nightly-analytics",
"run_id": "2026-10-31T020000Z-a81c",
"phase": "load_raw_orders",
"status": "completed",
"rows": 18420,
"duration_ms": 7312,
"source_window_start": "2026-10-30T02:00:00Z",
"source_window_end": "2026-10-31T02:00:00Z"
}
This event supports operational diagnosis while avoiding customer-level payloads and secrets.
Track these measures:
| Category | Signals |
|---|---|
| RED — job service | Run count, run duration, failed-run count, retries, dbt test failures |
| RED — database operations | Connection errors, query latency, deadlocks, failed transactions |
| USE — ECS task | CPU utilization, memory utilization, task restarts or out-of-memory exits |
| USE — PostgreSQL | CPU, memory pressure, storage use, connection use, read/write I/O |
| Pipeline quality | Rows extracted and loaded, source-to-target lag, rejected rows, watermark age |
| S3 | Object count and bytes retained; failed writes or reads |
OpenTelemetry can create spans for each phase and export through the Python SDK to an OTLP collector or an approved telemetry backend. Keep attributes low-cardinality: a run ID belongs in logs and trace context, but not as a metric label.
from opentelemetry import trace
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import BatchSpanProcessor
from opentelemetry.exporter.otlp.proto.http.trace_exporter import OTLPSpanExporter
provider = TracerProvider(
resource=Resource.create({"service.name": "nightly-analytics"})
)
provider.add_span_processor(BatchSpanProcessor(OTLPSpanExporter()))
trace.set_tracer_provider(provider)
tracer = trace.get_tracer("analytics.pipeline")
with tracer.start_as_current_span("load_raw_orders") as span:
span.set_attribute("pipeline.source", "orders")
span.set_attribute("pipeline.run_id", run_id)
loaded_rows = load_orders()
span.set_attribute("pipeline.rows_loaded", loaded_rows)
Notice that the span records phase context and row count, not row contents or credentials.
Use service-level objectives that match the actual reporting need rather than promising streaming freshness:
| SLI | Initial SLO | Measurement |
|---|---|---|
| Nightly completion | 95% of scheduled runs complete by 07:00 local reporting time over a rolling 30-day window | Successful completion timestamp |
| Critical model quality | 100% of completed runs pass unique-key and non-null tests on critical marts | dbt test results |
| Freshness | Critical source data is no more than 30 hours old on days with a scheduled run | Successful watermark age |
| Recovery | A failed run can be replayed from retained extracts or re-extracted within one business day | Incident and replay records |
The main dashboard should show last successful run, current phase, elapsed time, source-to-target lag, row counts, dbt test outcomes, and the last seven run statuses. A second dashboard can show RDS capacity trends, task CPU and memory, and S3 retention growth.
Page an engineer for two consecutive failed runs, a run still active beyond the expected upper bound, a database storage threshold that risks exhaustion, or an increase in critical dbt failures. Send a non-paging notification for a single late run if the reporting deadline has not passed. Alert on SLO burn or actionable failure, not every retry.
Security and compliance
Use separate database credentials for application writes, source extraction, and analytics transformations. The extraction role should have SELECT access only to the required source tables. The analytics role should write to raw and run dbt models; dashboard roles should receive read access only to approved marts. Avoid sharing the application’s owner account with the batch job.
Keep both databases private and encrypt connections with TLS. Encrypt S3 objects and database storage at rest. Use Secrets Manager for database credentials and grant the task role access only to the named secrets and required objects. The task role should not be able to change infrastructure or read unrelated application secrets.
Treat S3 extracts as sensitive copies of production data. Define retention based on replay and compliance needs, restrict bucket access, block public access, and avoid placing personal data in logs. If reports require a subset of source data, extract that subset rather than copying every application table “just in case.”
For deployment, use short-lived federated credentials through OIDC and review the trust policy’s repository and branch conditions. Separate production and development secrets, databases, and S3 paths. Record database access and deployment changes in the cloud audit trail. Confirm deletion, retention, data residency, and access-review requirements with the organization’s compliance owner; the infrastructure alone does not establish compliance.
Cost
The following is an assumption-based monthly estimate, not a quote. It assumes one modest analytics RDS instance, small storage, a short nightly Fargate task, low S3 volume, one AWS region, and no expensive always-on NAT gateway. Actual pricing depends on region, engine settings, storage, backup retention, network path, and current rates.
| Component | Planning estimate per month | Assumption |
|---|---|---|
| Analytics RDS for PostgreSQL | $35–$90 | Small single-AZ instance and modest storage; verify the selected class and engine options |
| Fargate job | $2–$15 | One short task per night with modest CPU and memory |
| S3 extracts | $1–$10 | Small retained objects and limited requests |
| CloudWatch logs and metrics | $2–$15 | Bounded log volume and 30-day retention |
| Secrets Manager | $1–$5 | A small number of secrets |
| Network and image storage | $0–$60 | Depends on NAT, endpoint, transfer, and ECR choices |
| Estimated total | $41–$195 | Excludes the existing application database and dashboard licenses |
The main cost trap is not dbt; it is network architecture and an oversized or always-on database. Compare the total cost of a NAT gateway with the needed VPC endpoints and a carefully controlled task network. Do not choose public database access to save money. If the estimate exceeds the budget, first reduce unnecessary retention, right-size the instance from observed utilization, and schedule a run only when data changes—not at the expense of access controls or recoverability.
Failure modes, resilience and disaster recovery
The pipeline is a batch system, so it can usually recover by retrying or replaying rather than requiring synchronous failover. That simplicity is useful only if the load and watermark behavior are designed for it.
| Failure | Expected behavior | Recovery |
|---|---|---|
| Scheduler does not start task | No new run record; freshness alert detects the gap | Start a manual run with the missed window |
| Source database unavailable | Task fails without advancing the watermark | Retry with bounded backoff, then rerun the window |
| Extract succeeds but load fails | S3 object remains available; raw load transaction rolls back | Replay the stored extract |
| dbt test fails | Run is marked failed; watermark does not advance | Fix model or source issue, then rebuild and test |
| Task is interrupted | Run remains incomplete; idempotent load allows retry | Resume from the extract or repeat extraction |
| Analytics database storage fills | Load or transformation fails; application remains isolated | Increase storage, remove expired data, rerun |
| Source schema changes | Contract checks fail before incorrect mart publication | Review the change, update extraction and models |
Use automated backups and point-in-time recovery for the analytics database according to its recovery objectives. Keep S3 versioning and lifecycle rules aligned with the replay window, and test restoring a database backup and replaying an extract. For a small team, a documented restore procedure and a periodic restore exercise are more valuable than adding a second region that nobody has tested.
The source database remains authoritative. If analytics data is lost, rebuild the raw layer from retained extracts where possible, or re-extract from the source using a bounded range. If the source no longer retains the needed history, the S3 retention policy becomes part of the recovery guarantee and must be long enough to meet the business requirement.
Evolving to the next scale
Do not scale this design because a cloud diagram looks small. Scale when measured run duration, source impact, analyst concurrency, or recovery requirements exceed the service objectives.
| Signal | First response | Larger change when needed |
|---|---|---|
| Nightly job approaches its deadline | Optimize extraction query, indexes, batches, and dbt models | Increase task resources or split independent phases |
| Source reads affect application latency | Reduce columns and extraction frequency; verify query plan | Add a replica or use a managed change-data-capture path |
| Warehouse queries compete with transformation | Add indexes and tune PostgreSQL resources | Separate serving and transformation workloads |
| Storage or table maintenance becomes material | Partition only tables that justify the operational cost | Move large history or analytical scans to a columnar warehouse |
| Multiple teams need independent deployments | Add ownership, model contracts, and CI selection | Introduce orchestration and domain-level data products |
| Freshness must be much lower than nightly | Reassess the business requirement and change capture | Adopt incremental streaming or managed replication |
A read replica is not a universal answer: it adds replication lag and still has PostgreSQL’s row-store query characteristics. Likewise, partitioning every table or introducing a distributed orchestrator early can create more work than it removes. First establish the bottleneck with traces, query plans, and workload measurements.
If the team moves to a cloud warehouse, preserve the dbt project’s model contracts and tests while adapting materializations and SQL. Keep source extraction, model logic, and deployment concerns separate enough that a warehouse migration is a deliberate change rather than a full rewrite.
Phased implementation plan
Phase 1 — Define the reporting contract. Pick the first few business questions, define each metric’s grain and ownership, and agree on freshness. Inventory the source tables, update semantics, deletes, personal data, and expected nightly change volume. Reject requests that do not have an identified consumer or decision.
Phase 2 — Build the data path. Provision the private analytics database, S3 bucket, task role, secrets, logging, and schedule. Create the read-only source user and analytics schemas. Test extraction bounds and database query plans against a safe dataset. At this point, the job should write a manifest and run record even before it produces business marts.
Phase 3 — Add dbt models and tests. Implement source definitions, staging models, one fact and its necessary dimensions. Add uniqueness, relationship, accepted-value, and freshness tests where they protect a real contract. Review model SQL with analysts; a passing test suite does not prove that a metric means what its name suggests.
Phase 4 — Exercise failure and replay. Simulate source unavailability, task termination, a failed dbt test, and an interrupted load. Confirm that the watermark does not advance prematurely and that replay does not duplicate facts. Test the database restore procedure and verify extract retention.
Phase 5 — Release with an operator in the loop. Run the pipeline manually for several representative days, compare totals with the source and existing reports, then enable the schedule. Document how to inspect a run, replay a window, rotate credentials, and request a schema change. Review cost and run-time trends monthly until the workload is stable.
The right small-team analytics stack is not the one with the most components. It is the smallest system that gives the team repeatable transformations, explicit data contracts, safe retries, and enough observability to know when yesterday’s data is no longer trustworthy.