Data Transformation & Modeling

The "T" in ELT. After ingestion (see ETL / ELT & Ingestion), you transform raw tables into trustworthy models. dbt set the pattern; the field has matured around it.

Transformation frameworks

• ★ dbt-core — SQL + Jinja, models as SELECT statements with refs and tests. Apache 2.0. Still the default; the verb "to dbt" is real. Paid dbt Cloud adds scheduling, IDE, semantic layer, and CI.
• ★ SQLMesh (Tobiko) — modern dbt alternative: virtual data environments, column-level lineage, automatic backfills, multi-engine SQL transpilation via SQLGlot. Apache 2.0. The most-recommended new pick for greenfield projects in 2026, especially when you want type-aware previews and to avoid dbt-cloud pricing.
• dbt-fusion (dbt Labs, 2025) — Rust-rewritten dbt engine; faster compile / parse; ships with dbt Cloud. Watch this; the OSS surface is still in flux.
• Dataform (Google) — BigQuery-flavored dbt-like; free with BigQuery; less popular outside GCP since the GA of native BigQuery DataForm in 2024.

dbt ecosystem packages

• ★ dbt-utils — generic macros (surrogate keys, pivots, date spines).
• dbt-expectations — Great Expectations-style assertions in dbt tests.
• dbt-elementary — observability and freshness reporting on top of dbt artifacts; see Data Observability.
• dbt-osmosis — auto-propagates column descriptions and tests up the lineage.
• dbt-codegen — scaffolds source / staging models from a warehouse schema.
• dbt-checkpoint — pre-commit hooks that enforce dbt project conventions.
• dbt-project-evaluator — opinionated linting against dbt best practices.

Linting / formatting

• ★ sqlfluff — SQL linter; dbt-aware; the default. MIT. Run it in CI.
• SQLMesh's built-in linter / formatter — alternative if you're on SQLMesh.
• dbt-codegen + pre-commit — keep schema YAML in sync with the warehouse.

SQL parsers / transpilers

• ★ SQLGlot — Python SQL parser / transpiler across 20+ dialects; the engine inside SQLMesh. Open source; powers a lot of 2026 tooling.
• JSqlParser, sql-parser (Rust, used by Apache DataFusion) — alternatives in other languages.

Adjacent / "T outside dbt"

• Apache Spark / PySpark — when SQL isn't enough; large-scale transforms.
• Polars / DuckDB in a Python script — for medium data; faster than Spark on a single node.
• fal — Python tasks alongside dbt models; useful for ML feature pipelines.
• Coalesce.io — paid GUI-based transformation; Snowflake-flavored.

Patterns to adopt

• Layered models. Source → staging → intermediate → marts. Naming convention pays off.
• Tests on contracts, not just data. not_null, unique, accepted_values, plus column-level data type tests.
• CI on every PR. dbt build --select state:modified+ + slim CI to only run changed nodes.
• Snapshots for SCD2. dbt snapshots or SQLMesh incremental_by_unique_key with history.
• Don't macro everything. Jinja loops are tempting; readable SQL beats clever Jinja.
• Materialize tables, view ephemerals. Default to view for staging, table / incremental for marts.

Pick this if…

• Default OSS transformation in 2026: dbt-core (mature) or SQLMesh (modern).
• You hate dbt's "rebuild everything to preview": SQLMesh.
• Already on Snowflake / BigQuery and want a paid IDE: dbt Cloud.
• Multi-dialect SQL portability: SQLGlot directly, or SQLMesh on top.
• You want quality / freshness wired in: dbt + Elementary + dbt-expectations.