The Data Engineering Toolchain

• Categorize common data tooling into its job: code, orchestrate, transform, store, serve, observe
• Explain how the pieces interoperate in a modern stack
• Evaluate tools against the actual job instead of vendor marketing

• Fewer, composable tools beat a bespoke homegrown pipeline per team.
• Knowing the roles lets you evaluate the next shiny tool by which job it fills.
• Interoperability is the point: orchestrators trigger transforms that read from a warehouse/catalog.
• The skill that transfers is architecture judgment, not keystrokes in one vendor's UI.

A wrench is for turning bolts, a socket for nuts, a torque wrench for the final tight. Wrong tool, stripped bolt. Data tools are the same: pick the tool that matches the job.

• Code layer: Python/SQL/version control/CI for every pipeline artifact.
• Orchestration: a scheduler with DAGs manages dependencies, retries and backfills (Airflow/Dagster).
• Transformation: SQL modeling tools (dbt/SQLMesh) turn raw into clean, tested models.
• Heavy compute: Spark/Warehouse engines do the big joins and aggregations.
• Streaming: brokers + consumers process unbounded events (Kafka/Flink/Spark Streaming, Kinesis).
• Observability & quality: freshness, row counts, schema checks, lineage and alerting (Great Expectations, Monte Carlo, Soda).

• Airflow schedules a dbt run every morning; dbt models define tests; Spark does the one heavy nightly join; Great Expectations asserts row counts; lineage updates the catalog.
• Kafka carries click events; Flink windows aggregates; a warehouse serves the resulting metrics; monitoring pages on staleness.
• A small team runs DuckDB + dbt + Prefect entirely self-hosted for a metrics stack with zero engines to maintain.

• Use the orchestrator for workflow, the transformation tool for modeling — don't write orchestration logic in SQL files.
• Keep every artifact in Git: DAGs, models, tests, catalog expectations.
• Let one tool own each job; resist 'this tool can also do X' sprawl.
• Choose tools the team can run on-call — ops risk is a decision factor.

• Choosing tools by hype and building a stack nobody can operate.
• Writing ETL glue in one-off scripts instead of using the platform's transformation layer.
• Forking orchestration logic into BI tools, killing lineage.
• Collecting 10 observability dashboards that nobody reads because alerts weren't scoped to SLAs.

• Isolate the failing role first: scheduling? transform SQL? storage? quality gate?
• For a failing dbt model, check upstream model outputs and tests, not just the final SQL.
• For orchestrator gaps, look at dependencies/scheduler timezone drift (classic DST bug).
• Compare 'code version deployed' vs 'code version that produced the data' — reproducibility needs both.