Skip to content

Small-Team Analytics Stack: PostgreSQL, dbt Core, and a Nightly ELT Job

A practical AWS blueprint for a small team building dependable nightly analytics with PostgreSQL, dbt Core, and a deliberately simple ELT pipeline.

Ekanatha Reddy Urivakili18 min3,564 words

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:

DimensionAssumption
UsersFewer than 1,000 daily application users; roughly 5–20 internal dashboard users
Requests/sAverage application traffic below 5 requests/s; short peaks below 25 requests/s
Data volume20 GB of analytics data initially; up to 2 GB of changed source data per night
Team sizeTwo engineers, with occasional help from a data or product analyst
BudgetTarget 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:

RequirementDesign response
Extract changed application recordsRead from a restricted PostgreSQL account using an indexed updated_at column
Keep an auditable copy of each extractWrite compressed, date- and run-scoped objects to S3
Load data for transformationUpsert extracted records into analytics PostgreSQL raw tables
Build business-facing modelsRun dbt Core models, tests, and documentation generation in the batch container
Support rerunsRecord run state and watermarks; make loading idempotent by source key
Publish freshness and qualityEmit job metrics and fail the run when critical dbt tests fail

Non-functional requirements:

RequirementTarget
FreshnessComplete by 07:00 local reporting time on ordinary nights
RecoveryAn operator can rerun a failed date range without duplicating facts
AvailabilityAnalytics may be stale after a failure; application writes must remain unaffected
PerformanceSource extraction must be bounded and use an index; transformation must fit within the scheduled window
SecurityNo public database endpoint; secrets are not embedded in images or source control
OperabilityOne container image, one scheduled task definition, and a documented manual run procedure
CostAvoid 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.

ComponentAWS choiceAzure equivalentGoogle Cloud equivalent
Operational PostgreSQLAmazon RDS for PostgreSQLAzure Database for PostgreSQLCloud SQL for PostgreSQL
Analytics PostgreSQLAmazon RDS for PostgreSQLAzure Database for PostgreSQLCloud SQL for PostgreSQL
Nightly container jobAmazon ECS on AWS FargateAzure Container Apps JobsCloud Run Jobs
ScheduleAmazon EventBridge SchedulerAzure Logic Apps or Container Apps Jobs scheduleCloud Scheduler
Extract storageAmazon S3Azure Blob StorageCloud Storage
Container registryAmazon ECRAzure Container RegistryArtifact Registry
SecretsAWS Secrets ManagerAzure Key VaultSecret Manager
Logs and metricsAmazon CloudWatchAzure MonitorCloud Logging and Cloud Monitoring
Identity for deploymentIAM role assumed through GitHub Actions OIDCMicrosoft Entra workload identity federationWorkload 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 --> RunRepository

The 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 failure

The 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 ID

Replay 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 levelWhat to testWhere it runs
UnitWatermark calculations, manifest validation, retry limits, row mappingEvery pull request
IntegrationPostgreSQL upsert behavior, transaction rollback, dbt materializationCI with disposable PostgreSQL
ContractRequired source columns, types, nullability, and status valuesCI against a representative fixture; nightly against the source
LoadBounded extraction and transformation time at an assumed peak batch sizeBefore 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:

CategorySignals
RED — job serviceRun count, run duration, failed-run count, retries, dbt test failures
RED — database operationsConnection errors, query latency, deadlocks, failed transactions
USE — ECS taskCPU utilization, memory utilization, task restarts or out-of-memory exits
USE — PostgreSQLCPU, memory pressure, storage use, connection use, read/write I/O
Pipeline qualityRows extracted and loaded, source-to-target lag, rejected rows, watermark age
S3Object 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:

SLIInitial SLOMeasurement
Nightly completion95% of scheduled runs complete by 07:00 local reporting time over a rolling 30-day windowSuccessful completion timestamp
Critical model quality100% of completed runs pass unique-key and non-null tests on critical martsdbt test results
FreshnessCritical source data is no more than 30 hours old on days with a scheduled runSuccessful watermark age
RecoveryA failed run can be replayed from retained extracts or re-extracted within one business dayIncident 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.

ComponentPlanning estimate per monthAssumption
Analytics RDS for PostgreSQL$35–$90Small single-AZ instance and modest storage; verify the selected class and engine options
Fargate job$2–$15One short task per night with modest CPU and memory
S3 extracts$1–$10Small retained objects and limited requests
CloudWatch logs and metrics$2–$15Bounded log volume and 30-day retention
Secrets Manager$1–$5A small number of secrets
Network and image storage$0–$60Depends on NAT, endpoint, transfer, and ECR choices
Estimated total$41–$195Excludes 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.

FailureExpected behaviorRecovery
Scheduler does not start taskNo new run record; freshness alert detects the gapStart a manual run with the missed window
Source database unavailableTask fails without advancing the watermarkRetry with bounded backoff, then rerun the window
Extract succeeds but load failsS3 object remains available; raw load transaction rolls backReplay the stored extract
dbt test failsRun is marked failed; watermark does not advanceFix model or source issue, then rebuild and test
Task is interruptedRun remains incomplete; idempotent load allows retryResume from the extract or repeat extraction
Analytics database storage fillsLoad or transformation fails; application remains isolatedIncrease storage, remove expired data, rerun
Source schema changesContract checks fail before incorrect mart publicationReview 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.

SignalFirst responseLarger change when needed
Nightly job approaches its deadlineOptimize extraction query, indexes, batches, and dbt modelsIncrease task resources or split independent phases
Source reads affect application latencyReduce columns and extraction frequency; verify query planAdd a replica or use a managed change-data-capture path
Warehouse queries compete with transformationAdd indexes and tune PostgreSQL resourcesSeparate serving and transformation workloads
Storage or table maintenance becomes materialPartition only tables that justify the operational costMove large history or analytical scans to a columnar warehouse
Multiple teams need independent deploymentsAdd ownership, model contracts, and CI selectionIntroduce orchestration and domain-level data products
Freshness must be much lower than nightlyReassess the business requirement and change captureAdopt 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.