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.
| Assumption | Planning value |
|---|---|
| Daily users of reports and data-backed product features | 10,000 |
| Dashboard and data API traffic | 5 requests/s average, 40 requests/s peak |
| Source systems | 8–15 SaaS APIs and operational databases |
| New landed data | 30 GB/day compressed, with periodic backfills |
| Retention | 13 months of detailed data; longer retention for selected aggregates |
| Team size | 5–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
| ID | Requirement | Acceptance condition |
|---|---|---|
| F1 | Ingest database and SaaS data | Airbyte syncs configured sources into isolated raw schemas |
| F2 | Preserve change history where needed | CDC or source cursors retain the source’s supported change semantics |
| F3 | Transform and publish analytics models | dbt builds staging, intermediate, and mart models in Snowflake |
| F4 | Orchestrate dependencies and recovery | Dagster schedules, retries, and exposes run state for ingestion and dbt |
| F5 | Support replay and backfill | Authorized operators can request bounded replays without editing production tables |
| F6 | Serve reports and product analytics | Analysts and approved applications query curated schemas with role-scoped access |
Non-functional requirements
| ID | Requirement | Target or constraint |
|---|---|---|
| N1 | Freshness | Priority sources normally land within 60 minutes; morning marts meet their agreed business deadline |
| N2 | Reliability | A failed sync or model is visible, retryable, and does not silently publish partial results |
| N3 | Security | Least-privilege identities, encrypted connections, managed secrets, and auditable access |
| N4 | Operability | Each pipeline has an owner, runbook, freshness expectation, and cost attribution |
| N5 | Change safety | SQL, orchestration definitions, and infrastructure changes are reviewed and tested before production |
| N6 | Cost control | Separate 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.
| Component | AWS choice | Azure equivalent | GCP equivalent | Responsibility |
|---|---|---|---|---|
| Ingestion | Airbyte Cloud | Airbyte Cloud | Airbyte Cloud | Connectors, source cursors, and delivery to Snowflake |
| Warehouse | Snowflake on AWS | Snowflake on Azure | Snowflake on GCP | Storage, compute, roles, and SQL serving |
| Transformations | dbt Core container on ECS Fargate | dbt Core on Container Apps | dbt Core on Cloud Run Jobs | Versioned SQL models and tests |
| Orchestration | Dagster OSS on ECS Fargate | Dagster on Container Apps | Dagster on Cloud Run or GKE | Scheduling, dependency management, retries, and run UI |
| Orchestration metadata | Amazon RDS for PostgreSQL, Multi-AZ | Azure Database for PostgreSQL, zone-redundant where available | Cloud SQL for PostgreSQL, regional HA | Dagster run and event metadata |
| Replay queue | Amazon SQS with DLQ | Azure Service Bus queue | Pub/Sub subscription with dead-letter policy | Durable replay and backfill requests |
| Artifact storage | Amazon S3 | Azure Blob Storage | Cloud Storage | dbt artifacts, deployment bundles, and selected exports |
| Secrets | AWS Secrets Manager | Azure Key Vault | Secret Manager | Runtime credentials and API secrets |
| Logs and metrics | Amazon CloudWatch | Azure Monitor | Cloud Monitoring | Container logs, infrastructure metrics, and alert delivery |
| Container images | Amazon ECR | Azure Container Registry | Artifact Registry | Versioned 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 --> SnowflakeCaption: 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 assetsCaption: 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 processingCaption: 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 type | What it covers | Where it runs |
|---|---|---|
| Unit | Python validation, replay bounds, request parsing, and deterministic business logic | Pull request |
| Integration | dbt build and tests against a disposable or isolated Snowflake schema; Airbyte API behavior with a test connection where practical | Pull request or scheduled test environment |
| Contract | Expected source columns, types, cursor behavior, and model interfaces | Pull request and source-connector change review |
| Load | Representative dbt workload, concurrent dashboard queries, and bounded backfill contention | Staging before major changes |
| Recovery | Retry, dead-letter handling, duplicate request, and interrupted run behavior | Staging 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
| Model | Signals to collect |
|---|---|
| RED for Dagster and control APIs | Request/run rate, run failure and API error rate, run or request duration |
| RED for ingestion and dbt jobs | Sync/build throughput, failed jobs or tests, time from start to completion |
| USE for ECS and RDS | CPU utilization, memory utilization, disk and storage capacity, network saturation, database connections |
| USE for Snowflake workloads | Warehouse utilization and queued work, credit consumption, query execution time, and spill indicators |
| Data quality and freshness | Last 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
| SLI | SLO target | Measurement window |
|---|---|---|
| Priority-source freshness | 99% of scheduled syncs land within 90 minutes of their expected interval | Rolling 30 days |
| Morning mart readiness | 99% of business days meet the agreed 07:00 local-time readiness deadline | Rolling 30 business days |
| dbt build success | 99% of production build attempts succeed after permitted retries | Rolling 30 days |
| Analytics query availability | 99.9% of monitored query checks succeed during business hours | Rolling 30 days |
| Replay request acknowledgement | 99% of valid requests are accepted or rejected with a reason within 5 minutes | Rolling 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 area | Assumed monthly amount | Main drivers |
|---|---|---|
| Airbyte Cloud | $1,200 | Connector plan, sync volume, and any contracted capacity |
| Snowflake compute | $3,000 | Transformation and analyst warehouses, schedules, and query load |
| Snowflake storage | $700 | Raw retention, curated models, and historical backfills |
| Dagster ECS and RDS | $900 | Fargate tasks, Multi-AZ metadata database, and operational overhead |
| AWS SQS, S3, ECR, logs, and networking | $450 | Artifact retention, log volume, image storage, and data transfer |
| Monitoring and incident tooling | $400 | Existing platform allocation and alerting services |
| Contingency | $1,500 | Consumption variability and growth headroom |
| Planning total | $8,150 | Within 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 mode | Detection | Response |
|---|---|---|
| Source API throttles or becomes unavailable | Airbyte sync failure, retry growth, freshness breach | Back off within source limits, alert the owner, and avoid launching dependent marts |
| Connector schema changes | Contract tests fail or dbt staging errors | Quarantine the affected model path, inspect the source change, and update the contract deliberately |
| Snowflake warehouse is suspended, overloaded, or unavailable | Query errors, queued work, failed dbt run | Retry transient failures with limits; preserve the Dagster run and rerun only affected assets |
| Dagster task or ECS service fails | ECS health and Dagster run state | Replace failed tasks, use persisted run storage, and restore the service from its image and configuration |
| Metadata database is unavailable | Dagster cannot read or persist run state | Recover RDS using managed Multi-AZ failover and tested backups; prevent duplicate manual launches until state is reconciled |
| Duplicate or malformed replay request | Idempotency conflict, validation failure, or DLQ growth | Reject with a reason, deduplicate by idempotency key, and require operator review for poison messages |
| Incorrect transformation publishes bad data | dbt tests, reconciliation, or consumer report | Stop 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.
| Signal | Next change | Why |
|---|---|---|
| Freshness needs move below the practical source polling interval | Add source-native CDC or event ingestion for only the critical domains | Avoids forcing every dataset into streaming complexity |
| Backfills compete with daily workloads | Add workload-specific Snowflake warehouses and Dagster concurrency pools | Isolates expensive or long-running work |
| Many teams publish independent data products | Establish ownership, shared dbt packages, contracts, and deployment boundaries | Prevents a single project becoming an unreviewable shared namespace |
| Orchestration uptime or maintenance burden exceeds team capacity | Evaluate a managed Dagster control plane or a dedicated platform team | Moves control-plane operations away from a small data team |
| Source count and connector operations grow substantially | Formalize connector lifecycle, source certification, and change notification processes | Reduces failures caused by undocumented upstream changes |
| Data volumes or retention costs rise sharply | Revisit incremental strategies, clustering only where measured, retention tiers, and aggregate models | Optimizes 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
| Phase | Work | Exit criteria |
|---|---|---|
| 1. Foundation | Create AWS environments, networking, IAM roles, secret boundaries, Snowflake account structure, and initial cost alerts | Staging and production access are separated; credentials are managed and audited |
| 2. First source path | Configure Airbyte for one representative source and land it in a raw schema | Syncs are repeatable, monitored, and have an owner and freshness expectation |
| 3. First data product | Build dbt staging and mart models with source contracts, tests, and documentation | Consumers can use a reviewed model instead of querying raw tables |
| 4. Orchestration | Deploy Dagster with persistent metadata, schedules, retries, run visibility, and SQS replay handling | Failed syncs and builds are actionable; replay requests are bounded and auditable |
| 5. Release and operations | Add CI, staging deployment, dashboards, paging rules, runbooks, and restore testing | Changes are promoted through staging and operators can recover from a tested failure |
| 6. Scale onboarding | Add sources in batches, assign domain owners, and review usage and cost monthly | Freshness 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