Not every data team lives inside the Microsoft stack, and not every pipeline belongs in a drag-and-drop designer. For teams that write Python and SQL, two open-source tools cover orchestration and transformation with less ceremony than most platforms: Prefect runs and observes the Python, dbt turns SQL into tested, documented models. Together they give a two-person team a stack that looks and behaves like software.

The division of labor

Prefect is an open-source orchestration engine that turns Python functions into production data pipelines [1]. A flow is a decorated Python function that composes work; it can call tasks and nested flows, take parameters, and ends in a final state (completed, failed, cancelled) that Prefect records [2]. A deployment is the server-side representation of a flow that stores what is needed to schedule it, trigger it on events and run it on remote infrastructure through work pools [3].

dbt transforms raw warehouse data into trusted data products using SQL select statements; the framework handles dependency order, materialization and environments, and you can run it locally or on the dbt platform [4]. Its data tests are SQL assertions that return failing rows, including the generic unique, not_null, accepted_values and relationships tests that cover most grain-level checks in one line each [5]. Incremental models process only new or changed rows on each run, with a unique_key for merges and a --full-refresh escape hatch [6]. Documentation is generated from description keys in YAML into a browsable site [7].

Concern	Prefect	dbt
Extract from APIs, files, SaaS	Python tasks with retries and caching	Not its job
Load to the warehouse	Python task (bulk insert, COPY, Parquet to lake)	Not its job (except seeds)
Transform bronze to silver to gold	Calls dbt	SQL models, in dependency order
Tests	Fails the flow if dbt tests fail	Data tests at the grain
Scheduling, retries, alerts, run history	Deployments, work pools, automations	Not its job
Lineage and documentation	Flow run graph	Model DAG and docs site

A working layout

analytics/
  flows/
    daily_refresh.py        # Prefect flow: extract, load, then run dbt
    sources/erp.py          # extract tasks per source
  dbt/
    dbt_project.yml
    models/
      staging/              # silver: stg_erp__orders.sql, one model per source table
      marts/                # gold: dim_customer.sql, fct_order_line.sql
      staging/_sources.yml  # sources + freshness
      marts/_models.yml     # descriptions + tests
  tests/                    # pytest for extract code
  prefect.yaml              # deployments and schedules

The flow is deliberately short. It does not contain business logic; it sequences the pieces and records what happened.

from prefect import flow, task
import subprocess

@task(retries=3, retry_delay_seconds=60)
def extract_orders(since: str) -> str:
    # pull changed rows from the ERP API or SQL Server, write Parquet to the landing area
    ...
    return "landing/orders.parquet"

@task
def load_parquet(path: str) -> int:
    # bulk load into the bronze schema; return row count for the audit log
    ...

@task
def run_dbt(select: str = "tag:daily") -> None:
    subprocess.run(["dbt", "build", "--select", select, "--fail-fast"], check=True)

@flow(name="daily-refresh", log_prints=True)
def daily_refresh(since: str):
    rows = load_parquet(extract_orders(since))
    print(f"bronze rows loaded: {rows}")
    run_dbt()

if __name__ == "__main__":
    daily_refresh(since="2026-10-08")

dbt build runs models and tests in dependency order and stops at the first failure with --fail-fast, so a broken key never reaches gold. Prefect records the failed state, retries the extract if it was the network, and alerts on the flow’s final state.

The tests that matter

# models/marts/_models.yml
models:
  - name: fct_order_line
    description: "One row per order line as invoiced. Grain: order_id + line_no."
    tests:
      - unique:
          column_name: "order_id || '-' || line_no"
    columns:
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: status
        tests:
          - accepted_values:
              values: ['open', 'invoiced', 'cancelled']

That file is the grain statement, the referential integrity check and the documentation in one place. It is reviewed in a pull request like any other code.

Working with SQL Server and Fabric

dbt connects to SQL Server and to Fabric warehouses through community and vendor adapters, so a team can keep its existing warehouse and still adopt the model-and-test discipline. Prefect runs anywhere Python runs: a small virtual machine, a container, or a scheduled job that uses a work pool. The pattern complements rather than competes with Data Factory: many teams use Data Factory for ingestion into the lake and dbt for the silver-to-gold transformation, with either tool as the scheduler.

Where teams get it wrong

• Business logic in the flow. If a Prefect task is computing margin, the logic is invisible to dbt’s tests and docs. Move it to a model.
• One enormous model. A 600-line gold model with ten CTEs is untestable. Split into staging, intermediate and mart models with a test on each join key.
• Incremental without a key. An incremental model that appends without unique_key will double-count the first time a source row is updated [6].
• No source freshness. dbt can check that a source was updated within an expected window; wire that into the flow so stale sources fail loudly rather than quietly producing yesterday’s numbers.
• Running the scheduler on a laptop. Deploy to a work pool on a server or container with a monitored worker; a scheduler that sleeps with the laptop is not a scheduler.

Why this suits a small team

Both tools are plain files in Git. A new engineer reads the flow, the models and the YAML and understands the whole pipeline in an afternoon. Tests run in continuous integration before anything reaches production. Documentation is generated, not maintained. And the licensing cost is zero until you choose a hosted control plane. For a team of two with a SQL Server warehouse and three or four sources, that is usually the most defensible architecture available, and the one most likely to still be running in five years.

References

1. Prefect, “Introduction” (Prefect 3 documentation), docs.prefect.io/v3/get-started (accessed 9 Oct 2026).
2. Prefect, “Flows,” docs.prefect.io/v3/concepts/flows (accessed 9 Oct 2026).
3. Prefect, “Deployments,” docs.prefect.io/v3/concepts/deployments (accessed 9 Oct 2026).
4. dbt Labs, “What is dbt?” docs.getdbt.com/docs/introduction (accessed 9 Oct 2026).
5. dbt Labs, “Add data tests to your DAG,” docs.getdbt.com/docs/build/data-tests (accessed 9 Oct 2026).
6. dbt Labs, “Configure incremental models,” docs.getdbt.com/docs/build/incremental-models (accessed 9 Oct 2026).
7. dbt Labs, “About documentation,” docs.getdbt.com/docs/build/documentation (accessed 9 Oct 2026).