Data Orchestration 2025 Complete Guide: dbt, SQLMesh, Dagster, Airflow, Prefect, Data Contracts, CI/CD (2025)

Season 5 Ep 4 — If Ep 3 was "who queries fastest," Ep 4 is "who governs the pipelines". Data orchestration in 2025 is in the middle of transplanting engineering principles (CI/CD, testing, contracts) into data.

Prologue — "Data pipelines are software too"
Chapter 1 · Classifying Orchestration Tools
- 1.1 Two Axes
- 1.2 The Major Tools
Chapter 2 · dbt — The Analytics Engineer Standard
Chapter 3 · SQLMesh — "An alternative to dbt, or a complement"
Chapter 4 · Dagster — "The asset-centric revolution"
Chapter 5 · Airflow — "Still the most widely used"
Chapter 6 · Prefect — "Pythonic orchestration"
Chapter 7 · Temporal — "A different lineage of workflow engine"
Chapter 8 · Data Contracts
Chapter 9 · CI/CD for Data Pipelines
Chapter 10 · Observability and Alerting
Chapter 11 · Five Real Stack Combinations
Chapter 12 · Practical Tips for Korean Companies
Chapter 13 · Ten Antipatterns
Chapter 14 · Checklist — 12 Markers of Data Pipeline Maturity
Chapter 15 · Next Up — Season 5 Ep 5: "Semantic Layer, Metrics Store, Reverse ETL"

Prologue — "Data pipelines are software too"

Data engineering in the 2010s was "SQL scripts plus cron." 2025 is different:

Pipelines are managed in Git
Changes go through PR plus review plus CI tests
Data models carry types, constraints, and tests
Failures are prevented by OpenLineage and data contracts

This shift created a new job title, the Analytics Engineer, and dbt stood at its center. But 2025 is not dbt alone — SQLMesh and Dagster have become genuine alternatives, while Airflow and Prefect still hold the throne of general-purpose orchestration.

Chapter 1 · Classifying Orchestration Tools

1.1 Two Axes

Data transformation vs workflow orchestration
SQL-centric vs code (Python) centric

1.2 The Major Tools

Tool	Primary role	Philosophy
dbt Core / Cloud	SQL transformation	The Analytics Engineer standard
SQLMesh	SQL transformation	Versions, increments, state
Dagster	Orchestration	Asset-centric
Airflow 2/3	Orchestration	Task DAG, general purpose
Prefect 3	Orchestration	Pythonic, dynamic
Temporal	Workflow	Reliability, state machines
Mage, Kestra	Orchestration	The new generation

Chapter 2 · dbt — The Analytics Engineer Standard

2.1 Identity

Manage data transformation as code, in SQL
Automatically generates the dependency graph, tests, and documentation
Adapters for Snowflake/BigQuery/Databricks/Redshift/Postgres/DuckDB and more

2.2 Core Concepts

Model: a table or view defined by a SELECT statement
Source: the definition of data from an external system
Seed: loading CSV data
Snapshot: SCD Type 2
Test: validation at the column and model level (not_null, unique, accepted_values, relationships)
Macro: reusable logic in Jinja

2.3 Dependency Management

-- models/fact_orders.sql
SELECT *
FROM {{ ref('stg_orders') }}
JOIN {{ ref('dim_customers') }} USING (customer_id)

Declare dependencies with ref() and source()
The DAG is generated automatically → executed in the correct order
The whole pipeline runs with dbt build

2.4 2024–2025 Trends

dbt Cloud: SaaS for the IDE, scheduling, environments, and collaboration
dbt Semantic Layer / MetricFlow: centralizing metrics
dbt Mesh: federating multiple projects
The dbt-fusion engine announced in 2024: better performance and developer experience

2.5 Limits

The more complex the incremental logic gets, the harder it is to maintain
Weak state tracking (run history, change detection)
Jinja macros are hard to debug
Build time problems on large projects

2.6 Where It Is Used

The default for nearly every modern data team
BI and ML feature modeling

Chapter 3 · SQLMesh — "An alternative to dbt, or a complement"

3.1 Identity

Appeared in 2022, drew attention in 2024
Improves on the weak points of dbt (increments, versioning, testing)
Tobiko Data provides commercial support

3.2 The Key Differentiators

Virtual environments: test changes in a virtual environment first
Automatic incremental: automates incremental logic
State tracking: model versions plus change impact analysis
Backfill: a built-in workflow for reprocessing historical data
dbt compatible: existing dbt projects can be imported

3.3 Philosophy

"The productivity of dbt plus the rigor of a data warehouse plus the safety net of DevOps"

3.4 Example — An Incremental Model

MODEL (
  name core.fact_orders,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column order_date,
  ),
);

SELECT *
FROM raw.orders
WHERE order_date BETWEEN @start_date AND @end_date;

→ SQLMesh manages incremental processing, backfill, and caching automatically.

3.5 Limits

The ecosystem and community are still small compared to dbt
A limited number of connectors and adapters
Not enough learning material and examples

3.6 Where It Is Used

Teams centered on increments, versioning, and backfill
Mid-to-large teams suffering from the limits of dbt
An alternative option for teams starting fresh

Chapter 4 · Dagster — "The asset-centric revolution"

4.1 Identity

2018 Elementl → Dagster Labs
A model centered on data assets
A step beyond the task-centric view of Airflow

4.2 The Core Difference

Task DAG (Airflow): "run A, then run B"
Asset DAG (Dagster): "the function that produces this table" plus automatic dependencies

from dagster import asset

@asset
def orders(raw_data):
    return transform(raw_data)

@asset
def customer_lifetime_value(orders, customers):
    return compute_clv(orders, customers)

4.3 Strengths

The pipeline is modeled as a collection of data assets
Materialization state, freshness, and metadata are first-class concepts
Rich integrations with dbt, Fivetran, Hightouch and others
Excellent local development experience

4.4 2024–2025 Trends

Dagster+: the cloud SaaS
Dagster Components: reusable pipeline blocks
Asset-based scheduling: scheduling based on freshness
Stronger AI/ML pipeline integration

4.5 Limits

A learning curve (new concepts)
A smaller ecosystem and plugin set than Airflow
Large enterprise references are still accumulating

4.6 Where It Is Used

Teams centered on data products
Teams suffering at the "seam" between dbt and Airflow
Building a modern data stack from scratch

Chapter 5 · Airflow — "Still the most widely used"

5.1 Identity

2014 Airbnb → Apache
Workflows defined as Python DAGs
The largest ecosystem and community

5.2 Airflow 2.x

Turn Python functions directly into tasks with the TaskFlow API
Dynamic task mapping
Datasets (data-centric scheduling)
Hundreds of providers (AWS, GCP, Snowflake, dbt, Databricks and more)

5.3 Airflow 3.0 (2024–2025)

A major architectural overhaul (Task Execution Interface, Task SDK)
Language-agnostic task execution (beyond Python)
DAG versioning
Better UI, security, and multi-tenancy

5.4 Managed Options

Astronomer (Astro)
AWS MWAA
GCP Cloud Composer
Azure Data Factory managed Airflow

5.5 Strengths

Runs anywhere, with countless providers
Proven in the enterprise
General-purpose orchestration (including non-data work)

5.6 Limits

Task-centric (weak data asset tracking) — being eased by Datasets
Complex at scale (tuning the Celery/Kubernetes Executor)
The UI and developer experience are seen as heavy next to Dagster and Prefect

Chapter 6 · Prefect — "Pythonic orchestration"

6.1 Identity

2018 Prefect → Prefect Cloud
Started as an alternative to Airflow
Python-native, dynamic workflows

6.2 Prefect 2.x → 3.0

Prefect 3.0 released in 2024
Sweeping improvements to performance, UI, and security
The Flow and Task abstractions
Async by default, dynamic graphs feel natural

6.3 Strengths

The Python code is the workflow itself
The same experience locally and in the cloud
Simple scheduling, retries, and alerting

6.4 Limits

A smaller ecosystem than Airflow
Enterprise references are still accumulating
Dagster has the edge on dbt and ML integration

6.5 Where It Is Used

Teams centered on Python engineers
Dynamic pipelines (parameters, runtime decisions)
Startups and mid-sized companies

Chapter 7 · Temporal — "A different lineage of workflow engine"

7.1 Identity

Forked from Uber Cadence in 2020
Centered on long-running execution, state machines, and reliability
General workflows (payments, orders) plus data workflows

7.2 The Difference from Airflow

Airflow: batch ETL, periodic execution
Temporal: event-driven, long-running, user-initiated workflows

7.3 Where It Is Used

LLM agent orchestration (expanding in 2024–2025)
Order, payment, and delivery state machines
User journey workflows

Chapter 8 · Data Contracts

8.1 What They Are

An explicit agreement on schema, SLA, and owner between data producers and consumers
The data version of an API contract in code
A concept that rose between 2022 and 2025

8.2 Composition

Schema: columns, types, constraints
SLA: freshness, completeness, uniqueness
Owner: the data team, the service team
Lifecycle: change policy, versions, deprecation

8.3 Tools

Soda: data quality plus contracts
Schemata / Monte Carlo / Metaplane / Datafold: observability plus contracts
OpenLineage: the lineage standard
dbt Contracts: declare contracts on dbt models
Great Expectations: the validation standard

8.4 Example (dbt Contract)

models:
  - name: dim_customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: bigint
        constraints: [{type: primary_key}, {type: not_null}]
      - name: email
        data_type: varchar
        constraints: [{type: not_null}]

8.5 Operations

Review contract changes in the PR
Breaking change → version bump plus deprecation notice
Automatic alerts when a consumer table violates the contract

Chapter 9 · CI/CD for Data Pipelines

9.1 Git and Branching Strategy

main: the production pipeline
dev: the development environment
Feature branch → PR → review → CI passes → merge

9.2 CI Stages

Lint (sqlfluff, dbt lint)
Compile (dbt compile, SQLMesh plan)
Unit tests (samples at the model level)
Contract validation
Partial execution in the staging environment
Statistical diff (Datafold and others)

9.3 Environment Separation

Separate data sets for dev / staging / prod
Strictly separated access permissions
Staging may use a partial sample of prod

9.4 Deployment Strategies

Blue-green (new table version plus a switch)
Canary (the new version for a subset of users or queries)
Shadow (compute the new version only, and compare)
A rollback plan

9.5 Observability Integration

Monitor freshness, failures, and latency after deployment
Alerts → Slack, PagerDuty
A postmortem after the incident

Chapter 10 · Observability and Alerting

10.1 The Five Pillars (Monte Carlo)

Freshness: is the data current
Volume: is the row count normal
Schema: have columns or types changed
Quality: the distribution of values, and missingness
Lineage: upstream and downstream dependencies

10.2 The Tooling Landscape

Monte Carlo, Metaplane, Bigeye, Anomalo: SaaS observability
Datafold: data diff before deployment
Great Expectations: open source validation
Soda: open plus commercial
OpenLineage + Marquez: the lineage standard

10.3 Designing Alerts

Severity levels: P1 (the pipeline is fully halted) / P2 (delay) / P3 (quality)
Throttling: prevent the same alert from repeating
On-call rotation: at least 2 people on the data team

10.4 SLOs

Freshness and success-rate SLOs per pipeline
Managing a monthly error budget
On violation, freeze deployments and review

Chapter 11 · Five Real Stack Combinations

11.1 Startup (small scale)

Storage: BigQuery / Snowflake
Transformation: dbt
Orchestration: dbt Cloud scheduling or GitHub Actions
Observability: dbt tests plus Elementary

11.2 Scale-up

Storage: Snowflake / Databricks plus Iceberg
Transformation: dbt plus some SQLMesh
Orchestration: Dagster or Airflow
Observability: Monte Carlo / Metaplane

11.3 Data-heavy SaaS

Storage: Iceberg plus ClickHouse
Streaming: Flink/RisingWave
Transformation: dbt plus Spark
Orchestration: Dagster
Observability: Datadog plus OpenLineage

11.4 Enterprise

Storage: Snowflake/Databricks plus Iceberg
Transformation: dbt plus custom code
Orchestration: Airflow (Astronomer) plus some Dagster
Contracts: OpenLineage plus Monte Carlo
Governance: Unity/Polaris plus Collibra

11.5 Korean Finance and Public Sector

Storage: on-premise Iceberg plus StarRocks / Snowflake Private
Transformation: dbt (development) plus Spark (production)
Orchestration: Airflow on Kubernetes
Security and audit: built in-house plus Ranger/OPA

Chapter 12 · Practical Tips for Korean Companies

12.1 Hiring and Organization

Growing adoption of the Analytics Engineer role (Kakao, Toss, Karrot, Coupang and others)
Three layers: data engineer plus analytics engineer plus data scientist
Once the team passes 5 people, the tool stack needs standardization

12.2 The Order of Tool Adoption

dbt plus scheduling (keep it simple)
Expand tests and documentation
Orchestration (Airflow/Dagster)
Observability (Monte Carlo/Metaplane)
Contracts (dbt Contracts/Soda)

12.3 Language and Locale

Avoid Korean-language column names (compatibility issues)
Store time zones in UTC, convert to KST at analysis time
Manage holiday and weekend logic separately

12.4 Security and Audit

PII detection and masking pipelines
Long-term retention of access logs
Deployment history must be auditable

Chapter 13 · Ten Antipatterns

13.1 Keeping "cron plus SQL" as is

No Git, tests, or documentation → frequent incidents.

13.2 dbt without tests

Hundreds of models → regressions explode.

13.3 Incremental logic by hand

Hand-rolled increments in dbt → a maintenance nightmare. Consider SQLMesh.

13.4 A monolithic Airflow DAG

100 tasks in one DAG → failures propagate.

13.5 Running Dagster, Prefect, and Airflow at once

Three in one company is over-engineering.

13.6 Many consumers without contracts

One change breaks five teams.

13.7 Deprioritizing observability

Users discover the incident first.

13.8 Deploying straight to prod without CI

Skipping PR review → mistakes you cannot explain away.

13.9 No environment separation

Accidents where prod data is modified from dev.

13.10 Trusting auto-generated documentation alone

Auto docs only show structure. The business explanation comes from people.

Chapter 14 · Checklist — 12 Markers of Data Pipeline Maturity

Chapter 15 · Next Up — Season 5 Ep 5: "Semantic Layer, Metrics Store, Reverse ETL"

If pipelines make the data, the semantic layer speaks it. Ep 5 is the axis that connects data to business language.

The history and rediscovery of the semantic layer
dbt Semantic Layer / MetricFlow
The semantics of Cube / AtScale / Looker
The Metrics Store pattern
Headless BI (Transform, Lightdash)
Reverse ETL (Hightouch, Census, Grouparoo): analytics data into operational tools
Data activation strategy
The vanishing boundary with software engineering
Semantic layer maturity at Korean companies
Where AI meets the semantic layer

The 2025 version of the promise that "you define the meaning of data exactly once."

See you in the next post.

Summary: Data orchestration in 2025 is the stage where the principle that "pipelines are software too" gets completed. dbt became the standard for transformation, SQLMesh complements it with increments and versioning, and Dagster arrived with asset-centric orchestration. Airflow relaunched with the 3.0 overhaul, and Prefect 3.0 is the Pythonic alternative. Data contracts and observability push engineering quality upward, and CI/CD plus SLOs plus on-call have become daily life for a data team. The stack varies with scale and context, but the core principles boil down to five words: "Git, tests, contracts, observability, rollback." The next episode covers what sits on top of all of it — "the layer that speaks business language."