Skip to content

A Modern Data Stack Blueprint: Airbyte, Snowflake, dbt, and Dagster

A medium-scale, AWS-based blueprint for reliable ingestion, warehouse modeling, orchestration, delivery, and operations with Airbyte, Snowflake, dbt, and Dagster.

Ekanatha Reddy Urivakili20 min3,753 words

1. Context and scale

This blueprint serves a product company that needs dependable analytics for dashboards, finance, product decisions, and customer-facing reporting. Operational systems remain authoritative: the data platform copies data into Snowflake, transforms it with dbt, and publishes governed models for downstream users.

The chosen stack is Airbyte Cloud for source ingestion, Snowflake on AWS for the warehouse, dbt Core for transformations, and Dagster OSS for orchestration. Dagster runs on AWS ECS with Fargate; its metadata database is Amazon RDS for PostgreSQL in Multi-AZ mode. This keeps the operating model to a handful of managed services and containers rather than a Kubernetes platform team.

The system is designed for batch and micro-batch analytics, not sub-second operational decisions. A typical source sync runs every 15–60 minutes; larger historical loads run as controlled backfills. Snowflake’s result cache and warehouse caches help repeated dashboard queries, while a small SQS queue carries replay and maintenance requests to the orchestration boundary.

AssumptionPlanning value
Daily users of reports and data-backed product features10,000
Dashboard and data API traffic5 requests/s average, 40 requests/s peak
Source systems8–15 SaaS APIs and operational databases
New landed data30 GB/day compressed, with periodic backfills
Retention13 months of detailed data; longer retention for selected aggregates
Team size5–7 engineers across data and platform responsibilities
Monthly operating budget$8,000–$15,000, including SaaS and cloud consumption

These are planning assumptions, not capacity guarantees or vendor quotes. Validate them with observed ingestion volumes, Snowflake credit consumption, Airbyte plan limits, and query behavior before committing to a budget.

The operational goal is a trustworthy warehouse available for business use each morning, with enough flexibility for intra-day updates. The design favors recoverable runs, explicit ownership, and visible data contracts over a large number of streaming components.

2. Requirements

Functional requirements

IDRequirementAcceptance condition
F1Ingest database and SaaS dataAirbyte syncs configured sources into isolated raw schemas
F2Preserve change history where neededCDC or source cursors retain the source’s supported change semantics
F3Transform and publish analytics modelsdbt builds staging, intermediate, and mart models in Snowflake
F4Orchestrate dependencies and recoveryDagster schedules, retries, and exposes run state for ingestion and dbt
F5Support replay and backfillAuthorized operators can request bounded replays without editing production tables
F6Serve reports and product analyticsAnalysts and approved applications query curated schemas with role-scoped access

Non-functional requirements

IDRequirementTarget or constraint
N1FreshnessPriority sources normally land within 60 minutes; morning marts meet their agreed business deadline
N2ReliabilityA failed sync or model is visible, retryable, and does not silently publish partial results
N3SecurityLeast-privilege identities, encrypted connections, managed secrets, and auditable access
N4OperabilityEach pipeline has an owner, runbook, freshness expectation, and cost attribution
N5Change safetySQL, orchestration definitions, and infrastructure changes are reviewed and tested before production
N6Cost controlSeparate warehouse workloads, auto-suspend compute, and alert on spend anomalies

Targets are service objectives to adopt and measure, not claims about the guarantees of any specific provider. Source APIs can impose rate limits and availability constraints that the platform cannot eliminate.

3. High-level design

Airbyte Cloud connects to the source systems and loads into Snowflake raw schemas. Dagster coordinates sync requests and dbt execution. dbt models make the raw data useful, while Snowflake roles and warehouses separate transformation, analyst, and product-query workloads.

flowchart LR
    sources["Operational databases and SaaS APIs"] --> airbyte["Airbyte Cloud"]
    airbyte --> raw["Snowflake raw schemas"]
    sqs["Amazon SQS replay queue"] --> dagster["Dagster on ECS Fargate"]
    dagster --> airbyte
    dagster --> dbt["dbt Core job"]
    dbt --> snowflake["Snowflake staging and marts"]
    raw --> snowflake
    snowflake --> bi["BI tools and data APIs"]
    dagster --> rds["Amazon RDS PostgreSQL"]
    dagster --> cw["Amazon CloudWatch"]
    dbt --> s3["Amazon S3 artifacts"]

Caption: Airbyte owns source extraction, dbt owns transformations, and Dagster owns coordination and run visibility.

The diagram separates responsibilities deliberately. Airbyte is not the transformation layer; its job is to move source data reliably into raw schemas. dbt is not the scheduler; its project defines transformations and tests, while Dagster decides when assets should be built and how failures should be surfaced.

SQS is for discrete control-plane requests such as an approved backfill or replay. It is not a replacement for Dagster’s run coordination. Dagster’s run queue, concurrency limits, and persisted run state remain authoritative. A small sensor consumes requests, validates their scope, and launches the corresponding run. The SQS dead-letter queue preserves requests that repeatedly fail validation or processing.

4. Cloud service mapping

Airbyte Cloud, Snowflake, dbt, and Dagster are the selected products; the table maps the AWS hosting components and reasonable equivalents in other clouds. Provider equivalents are not necessarily interchangeable in networking, identity, or operations.

ComponentAWS choiceAzure equivalentGCP equivalentResponsibility
IngestionAirbyte CloudAirbyte CloudAirbyte CloudConnectors, source cursors, and delivery to Snowflake
WarehouseSnowflake on AWSSnowflake on AzureSnowflake on GCPStorage, compute, roles, and SQL serving
Transformationsdbt Core container on ECS Fargatedbt Core on Container Appsdbt Core on Cloud Run JobsVersioned SQL models and tests
OrchestrationDagster OSS on ECS FargateDagster on Container AppsDagster on Cloud Run or GKEScheduling, dependency management, retries, and run UI
Orchestration metadataAmazon RDS for PostgreSQL, Multi-AZAzure Database for PostgreSQL, zone-redundant where availableCloud SQL for PostgreSQL, regional HADagster run and event metadata
Replay queueAmazon SQS with DLQAzure Service Bus queuePub/Sub subscription with dead-letter policyDurable replay and backfill requests
Artifact storageAmazon S3Azure Blob StorageCloud Storagedbt artifacts, deployment bundles, and selected exports
SecretsAWS Secrets ManagerAzure Key VaultSecret ManagerRuntime credentials and API secrets
Logs and metricsAmazon CloudWatchAzure MonitorCloud MonitoringContainer logs, infrastructure metrics, and alert delivery
Container imagesAmazon ECRAzure Container RegistryArtifact RegistryVersioned Dagster and dbt images

Snowflake is a separate service boundary even when its deployment region matches AWS. Use private connectivity where the chosen Snowflake account and AWS networking plan support it; otherwise enforce TLS, narrow source and destination IP policies where practical, and avoid exposing credentials in container configuration.

A dedicated Redis service is not justified at this scale. Repeated warehouse queries can benefit from Snowflake caching, and dashboard products may provide their own caching. Adding a second cache creates invalidation and freshness questions without solving the main data-platform bottleneck.

5. Data design

The warehouse separates ingestion from business meaning. Airbyte writes into a raw schema per source or source domain. dbt then builds:

  • Staging models that normalize types, names, timestamps, and deletion semantics.
  • Intermediate models that reconcile source identities and apply shared business rules.
  • Marts that expose stable, documented facts and dimensions to analysts and applications.

The example below models order events. fct_order has one row per business order, not one row per source change record. If the business needs an audit trail of every order state change, model that separately as an event fact rather than overloading the current-state fact.

erDiagram
    DIM_CUSTOMER ||--o{ FCT_ORDER : places
    DIM_DATE ||--o{ FCT_ORDER : dates
    DIM_PRODUCT ||--o{ FCT_ORDER : contains
    FCT_ORDER ||--o{ FCT_ORDER_LINE : has
    DIM_PRODUCT ||--o{ FCT_ORDER_LINE : identifies

    DIM_CUSTOMER {
        string customer_key PK
        string source_customer_id
        string segment
        timestamp valid_from
        timestamp valid_to
    }
    DIM_DATE {
        int date_key PK
        date calendar_date
        int year
        int month
    }
    DIM_PRODUCT {
        string product_key PK
        string source_product_id
        string category
    }
    FCT_ORDER {
        string order_id PK
        string customer_key FK
        int order_date_key FK
        string status
        number order_total
        timestamp updated_at
    }
    FCT_ORDER_LINE {
        string order_id FK
        string product_key FK
        int line_number
        number quantity
        number net_amount
    }

Caption: The reporting model declares grain and relationships explicitly so measures do not depend on source-table quirks.

Snowflake DDL establishes stable consumer-facing structures. dbt owns the lifecycle of these tables in practice; the DDL shows the intended model and types.

create schema if not exists ANALYTICS.MARTS;

create table if not exists ANALYTICS.MARTS.DIM_CUSTOMER (
    customer_key varchar not null,
    source_customer_id varchar not null,
    segment varchar,
    valid_from timestamp_ntz not null,
    valid_to timestamp_ntz,
    is_current boolean not null
);

create table if not exists ANALYTICS.MARTS.FCT_ORDER (
    order_id varchar not null,
    customer_key varchar not null,
    order_date date not null,
    status varchar not null,
    order_total number(18, 2) not null,
    updated_at timestamp_ntz not null
);

Snowflake does not enforce every declared constraint in the same way as a transactional database. Treat uniqueness and referential integrity as dbt tests and operational checks. For large event or transaction facts, use incremental dbt models and a date-based filter aligned with the source’s update semantics. Do not assume that an updated_at field captures late-arriving corrections unless the source contract says so.

Raw schemas should be access-restricted and treated as implementation details. Curated marts are the supported interface. A model owner should document grain, freshness, source lineage, sensitive fields, and whether historical corrections rewrite prior results.

6. Low-level design

Dagster represents source syncs and dbt models as assets or asset groups. A production deployment separates the webserver and daemon from run execution, persists orchestration state in PostgreSQL, and applies concurrency limits so a burst of backfills does not saturate Snowflake or upstream APIs.

classDiagram
    class DagsterWebserver {
        +serve UI and GraphQL
    }
    class DagsterDaemon {
        +evaluate schedules
        +evaluate sensors
    }
    class DagsterRun {
        +execute selected assets
        +persist events
    }
    class AirbyteClient {
        +start sync
        +read job status
    }
    class DbtCliResource {
        +build selected models
        +emit materialization events
    }
    class Snowflake {
        +store raw data
        +serve curated models
    }
    class PostgresStorage {
        +persist runs and events
    }
    DagsterWebserver --> PostgresStorage
    DagsterDaemon --> PostgresStorage
    DagsterDaemon --> DagsterRun
    DagsterRun --> AirbyteClient
    DagsterRun --> DbtCliResource
    AirbyteClient --> Snowflake
    DbtCliResource --> Snowflake

Caption: Dagster coordinates work while the ingestion and transformation tools retain their own execution responsibilities.

Minimal runnable example

Install Dagster in an environment, save this as hello.py, then run dagster dev -f hello.py. The command starts the local development UI and loads the asset definition.

from dagster import Definitions, asset

@asset
def hello():
    return "warehouse pipeline is ready"

defs = Definitions(assets=[hello])

What to notice: a Dagster asset is a named, observable unit of work; production assets should describe data products, not just generic task steps.

Realistic dbt asset example

This example uses the dagster-dbt integration to run a dbt project as a Dagster asset selection. Install compatible dagster, dagster-dbt, and dbt-snowflake packages, set up the dbt profile through the runtime environment, and build the manifest during the image build or development preparation step.

from pathlib import Path

from dagster import Definitions
from dagster_dbt import DbtCliResource, DbtProject, dbt_assets

project = DbtProject(project_dir=Path(__file__).parent / "transform")
project.prepare_if_dev()

@dbt_assets(manifest=project.manifest_path)
def warehouse_models(context, dbt: DbtCliResource):
    yield from dbt.cli(["build"], context=context).stream()

defs = Definitions(
    assets=[warehouse_models],
    resources={
        "dbt": DbtCliResource(project_dir=str(project.project_dir)),
    },
)

What to notice: dbt tests execute as part of the asset build, and Dagster receives materialization events rather than a single opaque “run dbt” task.

A corresponding incremental model can make late-arriving updates explicit. Its unique key and lookback window must match the source’s update contract; the interval below is an example policy, not a universal default.

{{ config(
    materialized = 'incremental',
    unique_key = 'order_id'
) }}

select
    order_id,
    customer_id as source_customer_id,
    cast(order_date as date) as order_date,
    status,
    cast(order_total as number(18, 2)) as order_total,
    updated_at
from {{ source('commerce', 'orders') }}

{% if is_incremental() %}
where updated_at >= (
    select dateadd(day, -3, coalesce(max(updated_at), '1900-01-01'::timestamp_ntz))
    from {{ this }}
)
{% endif %}

The lookback intentionally reprocesses a bounded overlap, reducing the chance that delayed updates are missed. For sources with hard deletes, implement and test tombstone handling; a timestamp filter alone cannot discover a deleted row.

7. Key flows

Scheduled ingestion and modeling

Dagster schedules a run, asks Airbyte to execute a sync, waits for the job outcome, and only then proceeds to dbt. The run should fail visibly if ingestion fails; building marts from incomplete raw data should require an explicit, documented exception.

sequenceDiagram
    participant D as Dagster
    participant A as Airbyte Cloud
    participant S as Snowflake
    participant B as dbt Core
    participant O as BI consumers
    D->>A: Start configured sync
    A->>S: Write source records to raw schema
    A-->>D: Return sync job status
    D->>D: Validate sync outcome and freshness
    D->>B: Run dbt build selection
    B->>S: Build staging and mart models
    B-->>D: Return tests and materializations
    D-->>O: Publish successful assets

Caption: A successful source sync is a precondition for publishing the dependent dbt assets.

Operator-requested replay

Replay requests carry a source identifier, a bounded date range, a requester, and an idempotency key. Dagster validates the request against an allowlist and concurrency policy before starting work. An operator cannot submit arbitrary SQL or an unbounded historical reload through this interface.

sequenceDiagram
    participant U as Operator
    participant Q as Amazon SQS
    participant G as Dagster sensor
    participant A as Airbyte Cloud
    participant S as Snowflake
    U->>Q: Submit scoped replay request
    G->>Q: Receive request
    G->>G: Validate source, range, and idempotency
    G->>A: Start permitted sync or backfill
    A->>S: Reload source records
    A-->>G: Return job status
    G->>G: Launch dependent dbt assets
    G-->>Q: Delete message after accepted processing

Caption: The queue makes replay requests durable; validation and idempotency prevent it from becoming an unrestricted production control plane.

8. Build and release

Infrastructure should be versioned alongside the Dagster definitions and dbt project, but deployed with separate permissions. A Terraform snippet can define the durable replay queue and artifact bucket; production configuration also needs ECS task and service definitions, IAM roles, RDS, networking, alarms, and encryption policies.

resource "aws_sqs_queue" "replay_dlq" {
  name                      = "data-replay-dlq"
  message_retention_seconds = 1209600
  sqs_managed_sse_enabled   = true
}

resource "aws_sqs_queue" "replay" {
  name                      = "data-replay"
  visibility_timeout_seconds = 900
  message_retention_seconds = 345600
  sqs_managed_sse_enabled   = true

  redrive_policy = jsonencode({
    deadLetterTargetArn = aws_sqs_queue.replay_dlq.arn
    maxReceiveCount     = 5
  })
}

resource "aws_s3_bucket" "artifacts" {
  bucket = var.artifact_bucket_name
}

resource "aws_s3_bucket_versioning" "artifacts" {
  bucket = aws_s3_bucket.artifacts.id

  versioning_configuration {
    status = "Enabled"
  }
}

resource "aws_s3_bucket_server_side_encryption_configuration" "artifacts" {
  bucket = aws_s3_bucket.artifacts.id

  rule {
    apply_server_side_encryption_by_default {
      sse_algorithm = "AES256"
    }
  }
}

The snippet is illustrative, not a complete production module. Add public-access blocking, lifecycle rules, access policies, and explicit KMS configuration if required by the organization’s security baseline. Keep state in a protected remote backend and review plans before applying.

A CI pipeline should test before building and deploying. Use GitHub Actions OIDC to assume a narrowly scoped AWS role rather than storing long-lived AWS keys. The task-definition template and deployment script are repository-owned artifacts.

name: data-platform
on:
  pull_request:
  push:
    branches: [main]

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-dev.txt
      - run: pytest
      - run: dbt deps
        working-directory: transform
      - run: dbt compile
        working-directory: transform
      - run: terraform fmt -check -recursive
      - run: terraform validate
        working-directory: infra

  deploy-staging:
    if: github.ref == 'refs/heads/main'
    needs: test
    runs-on: ubuntu-latest
    permissions:
      contents: read
      id-token: write
    steps:
      - uses: actions/checkout@v4
      - uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: ${{ secrets.STAGING_DEPLOY_ROLE_ARN }}
          aws-region: us-east-1
      - uses: aws-actions/amazon-ecr-login@v2
      - run: ./scripts/build-and-push.sh staging
      - run: ./scripts/deploy-dagster-staging.sh

The action versions should be reviewed and pinned according to the organization’s dependency policy. Production promotion should use an approved image digest that passed staging, not rebuild a different image from the same source commit.

Test strategy

Test typeWhat it coversWhere it runs
UnitPython validation, replay bounds, request parsing, and deterministic business logicPull request
Integrationdbt build and tests against a disposable or isolated Snowflake schema; Airbyte API behavior with a test connection where practicalPull request or scheduled test environment
ContractExpected source columns, types, cursor behavior, and model interfacesPull request and source-connector change review
LoadRepresentative dbt workload, concurrent dashboard queries, and bounded backfill contentionStaging before major changes
RecoveryRetry, dead-letter handling, duplicate request, and interrupted run behaviorStaging and periodic game day

Mocks are useful for unit tests but do not prove connector compatibility or Snowflake SQL behavior. Keep an isolated integration environment with synthetic data and safe credentials so source and warehouse changes can be validated without exposing production records.

9. Observability

Instrument the pipeline around data outcomes, not just container health. A green ECS service does not mean a source sync completed, a dbt test passed, or a mart is fresh. Every scheduled asset should report expected cadence, actual completion time, row-count signals where meaningful, and the owning team.

Structured logs

Emit JSON logs from Dagster jobs and helper code. Include identifiers that let an operator correlate orchestration with Airbyte, dbt, and Snowflake without logging secrets, raw payloads, or customer data.

{
  "timestamp": "2026-11-05T06:42:18Z",
  "level": "INFO",
  "event": "asset_materialized",
  "run_id": "dagster-run-7f31",
  "asset_key": "marts.fct_order",
  "source": "commerce",
  "airbyte_job_id": "job-2941",
  "dbt_invocation_id": "dbt-8ac2",
  "duration_seconds": 84,
  "rows_affected": 18240,
  "warehouse": "TRANSFORM_WH",
  "environment": "staging"
}

Use a stable event name and bounded-cardinality labels. Do not put run IDs, customer IDs, or table names into metric labels if that would create unbounded series; keep those values in logs and traces.

RED and USE signals

ModelSignals to collect
RED for Dagster and control APIsRequest/run rate, run failure and API error rate, run or request duration
RED for ingestion and dbt jobsSync/build throughput, failed jobs or tests, time from start to completion
USE for ECS and RDSCPU utilization, memory utilization, disk and storage capacity, network saturation, database connections
USE for Snowflake workloadsWarehouse utilization and queued work, credit consumption, query execution time, and spill indicators
Data quality and freshnessLast successful sync, source-to-mart freshness, row-count change, uniqueness and relationship test failures

Metrics need dimensions for environment, pipeline, source, and owning team, with cardinality reviewed before rollout. Warehouse consumption should be attributed by workload using distinct roles and virtual warehouses where the budget supports that separation.

OpenTelemetry tracing

Use OpenTelemetry spans around meaningful orchestration boundaries, and propagate trace context through custom clients when possible. Airbyte Cloud and Snowflake may expose their own identifiers rather than participating in the same trace; attach those identifiers to the Dagster span so logs and vendor run records remain correlatable.

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": "dagster-data-platform"})
)
provider.add_span_processor(BatchSpanProcessor(OTLPSpanExporter()))
trace.set_tracer_provider(provider)
tracer = trace.get_tracer("data_platform")

def record_sync_result(source_name: str, airbyte_job_id: str, status: str) -> None:
    with tracer.start_as_current_span("airbyte.sync") as span:
        span.set_attribute("data.source", source_name)
        span.set_attribute("airbyte.job_id", airbyte_job_id)
        span.set_attribute("sync.status", status)

Export the OTLP endpoint and authentication through managed secrets or the runtime environment. Production code should also record exceptions on failed operations and avoid attaching sensitive source identifiers as attributes.

SLI and SLO targets

SLISLO targetMeasurement window
Priority-source freshness99% of scheduled syncs land within 90 minutes of their expected intervalRolling 30 days
Morning mart readiness99% of business days meet the agreed 07:00 local-time readiness deadlineRolling 30 business days
dbt build success99% of production build attempts succeed after permitted retriesRolling 30 days
Analytics query availability99.9% of monitored query checks succeed during business hoursRolling 30 days
Replay request acknowledgement99% of valid requests are accepted or rejected with a reason within 5 minutesRolling 30 days

These targets should be negotiated with consumers and refined from a baseline. Exclude documented source-provider outages only through an explicit policy; otherwise the SLO hides the very dependency users experience.

Dashboards and paging

Maintain a platform overview showing freshness by source, Dagster run state, dbt test failures, Snowflake spend, and the oldest queued replay request. Add a separate cost dashboard by warehouse and environment, and a source-level dashboard for connector errors and rate limits.

Page on a missed critical freshness deadline, repeated production failures after retries, a growing dead-letter queue, exhausted warehouse or database capacity that blocks recovery, and unexpected sustained spend. Notify rather than page on a single transient retry or a non-critical test failure. Alerts should link to the failed run, owner, and runbook.

10. Security and compliance

Use separate AWS accounts or environments for development, staging, and production. Dagster execution roles should have only the permissions required to read their secrets, write logs, access the artifact bucket, and consume the replay queue. The Dagster webserver role should not inherit broad administrative permissions just because it is an operator-facing service.

In Snowflake, separate roles for Airbyte ingestion, dbt transformation, analysts, and application consumers. Give Airbyte write access to raw schemas, dbt the minimum permissions to read raw and write curated schemas, and consumers read-only access to approved marts. Use dedicated warehouses for distinct workloads where useful so query concurrency and cost attribution are easier to manage.

Additional controls include:

  • Store Snowflake, Airbyte, and source credentials in a managed secret store; rotate them and audit access.
  • Use TLS for service connections and encrypted storage for RDS, S3, and queue data.
  • Restrict PII access with Snowflake roles, masking policies, or secure views as required by policy.
  • Define data retention, deletion propagation, and legal hold behavior before onboarding regulated sources.
  • Exclude raw records, tokens, SQL literals containing sensitive values, and credentials from logs.
  • Review Airbyte connector permissions and source OAuth scopes whenever a connection changes.

A warehouse copy is still personal data when it contains personal data. The organization’s retention, access, and deletion obligations must apply to raw and modeled layers, snapshots, exports, and backups.

11. Cost

The following is an assumption-based monthly planning estimate, not a current vendor price list or quote. Actual costs depend on Airbyte’s contract and connector usage, Snowflake credits and region, query patterns, retention, AWS commitments, and support requirements.

Cost areaAssumed monthly amountMain drivers
Airbyte Cloud$1,200Connector plan, sync volume, and any contracted capacity
Snowflake compute$3,000Transformation and analyst warehouses, schedules, and query load
Snowflake storage$700Raw retention, curated models, and historical backfills
Dagster ECS and RDS$900Fargate tasks, Multi-AZ metadata database, and operational overhead
AWS SQS, S3, ECR, logs, and networking$450Artifact retention, log volume, image storage, and data transfer
Monitoring and incident tooling$400Existing platform allocation and alerting services
Contingency$1,500Consumption variability and growth headroom
Planning total$8,150Within the assumed $8,000–$15,000 budget range

The most useful cost controls are separate Snowflake warehouses for transform and interactive workloads, auto-suspend and sensible sizing, query and credit attribution by role, bounded backfill concurrency, and retention policies on artifacts and logs. Track cost per successful pipeline run and per published data product as well as total spend; a cheap but chronically stale warehouse is not an optimization.

12. Failure modes, resilience and disaster recovery

Failure modeDetectionResponse
Source API throttles or becomes unavailableAirbyte sync failure, retry growth, freshness breachBack off within source limits, alert the owner, and avoid launching dependent marts
Connector schema changesContract tests fail or dbt staging errorsQuarantine the affected model path, inspect the source change, and update the contract deliberately
Snowflake warehouse is suspended, overloaded, or unavailableQuery errors, queued work, failed dbt runRetry transient failures with limits; preserve the Dagster run and rerun only affected assets
Dagster task or ECS service failsECS health and Dagster run stateReplace failed tasks, use persisted run storage, and restore the service from its image and configuration
Metadata database is unavailableDagster cannot read or persist run stateRecover RDS using managed Multi-AZ failover and tested backups; prevent duplicate manual launches until state is reconciled
Duplicate or malformed replay requestIdempotency conflict, validation failure, or DLQ growthReject with a reason, deduplicate by idempotency key, and require operator review for poison messages
Incorrect transformation publishes bad datadbt tests, reconciliation, or consumer reportStop downstream promotion, fix the model, rebuild affected partitions, and communicate the correction

Use bounded retries with exponential backoff for transient failures, but do not retry permanent errors indefinitely. A retry policy that hides repeated authentication failures or schema incompatibility turns a clear incident into delayed data.

RDS should use Multi-AZ availability and automated backups with a retention policy that meets recovery objectives. Store deployment artifacts and selected dbt manifests in versioned S3. Snowflake recovery options and retention features depend on the account configuration; confirm recovery windows and permissions with the platform owner rather than assuming that a warehouse backup is equivalent to a full disaster-recovery plan.

Define recovery objectives explicitly. A reasonable starting assumption for this platform is a four-hour recovery time for orchestration control and a 24-hour recovery point for orchestration metadata, with Snowflake data recovery governed separately by its configured retention and source replay capabilities. Run a restore exercise at least periodically: a backup that has not been restored is an unverified assumption.

13. Evolving to the next scale

Do not move to streaming or Kubernetes because the platform has become fashionable. Change the architecture when measured constraints make the current operating model insufficient.

SignalNext changeWhy
Freshness needs move below the practical source polling intervalAdd source-native CDC or event ingestion for only the critical domainsAvoids forcing every dataset into streaming complexity
Backfills compete with daily workloadsAdd workload-specific Snowflake warehouses and Dagster concurrency poolsIsolates expensive or long-running work
Many teams publish independent data productsEstablish ownership, shared dbt packages, contracts, and deployment boundariesPrevents a single project becoming an unreviewable shared namespace
Orchestration uptime or maintenance burden exceeds team capacityEvaluate a managed Dagster control plane or a dedicated platform teamMoves control-plane operations away from a small data team
Source count and connector operations grow substantiallyFormalize connector lifecycle, source certification, and change notification processesReduces failures caused by undocumented upstream changes
Data volumes or retention costs rise sharplyRevisit incremental strategies, clustering only where measured, retention tiers, and aggregate modelsOptimizes measured query and storage costs rather than guessing

Preserve the same core boundaries as the system grows: ingestion contracts, modeled warehouse interfaces, and orchestration ownership. Scaling by introducing a service for every pipeline usually increases deployment and permission overhead faster than it improves reliability.

14. Phased implementation plan

PhaseWorkExit criteria
1. FoundationCreate AWS environments, networking, IAM roles, secret boundaries, Snowflake account structure, and initial cost alertsStaging and production access are separated; credentials are managed and audited
2. First source pathConfigure Airbyte for one representative source and land it in a raw schemaSyncs are repeatable, monitored, and have an owner and freshness expectation
3. First data productBuild dbt staging and mart models with source contracts, tests, and documentationConsumers can use a reviewed model instead of querying raw tables
4. OrchestrationDeploy Dagster with persistent metadata, schedules, retries, run visibility, and SQS replay handlingFailed syncs and builds are actionable; replay requests are bounded and auditable
5. Release and operationsAdd CI, staging deployment, dashboards, paging rules, runbooks, and restore testingChanges are promoted through staging and operators can recover from a tested failure
6. Scale onboardingAdd sources in batches, assign domain owners, and review usage and cost monthlyFreshness and spend remain within agreed objectives as source coverage grows

Start with one source whose failure modes represent the broader estate, not the easiest connector. A production-shaped vertical slice reveals identity, networking, secret management, schema drift, and recovery problems before multiple teams depend on the platform.

The blueprint is successful when data consumers can trust both the numbers and the delivery process: they know what a model means, when it should be fresh, who owns it, and what happens when an upstream system fails.


This article was generated with AI. #AIGenerated