dbt-fabric called from a Fabric notebook

On an integrated data platform, the warehouse exposes a SQL endpoint and the matching dbt adapter exists. One dull, blocking question remains: where to run the command. There is no machine to drop a project on and run a command at five in the morning; there are notebooks and a scheduler able to trigger them. The answer is therefore a library rather than an infrastructure.

It is easily forgotten that dbt can be called from Python. `dbtRunner` runs the same commands as the command line and returns an object rather than an exit code: the list of models, their status, their duration. In a notebook that changes everything: there is no text output left to parse to find out what failed.

from dbt.cli.main import dbtRunner, dbtRunnerResult

# The project is mounted from the lake: it stays versioned elsewhere, and the
# notebook holds no copy of it that could drift.
PROJECT = "/lakehouse/default/Files/dbt/warehouse"

runner = dbtRunner()
result: dbtRunnerResult = runner.invoke([
    "build",                       # run + test, in graph order
    "--project-dir", PROJECT,
    "--profiles-dir", PROJECT,
    "--target", "prod",
])

if not result.success:
    # No log to re-read: each node carries its own status and message.
    failures = [
        f"{r.node.name} — {r.message}"
        for r in result.result
        if r.status in ("error", "fail")
    ]
    raise RuntimeError("dbt failed:
" + "
".join(failures))

A notebook has no stable home directory to drop a profile file in. It is therefore written at the start of the run, from secrets read out of the platform’s vault, which has the advantage of guaranteeing no credential sleeps in the repository, and the drawback of making the run silent if the vault is misconfigured. An acceptable trade-off, provided it is said in the code rather than left to be guessed.

warehouse:
  target: prod
  outputs:
    prod:
      type: fabric
      driver: "ODBC Driver 18 for SQL Server"
      server: "<sql-endpoint>.datawarehouse.fabric.microsoft.com"
      database: warehouse
      schema: marts
      # Service principal authentication: the notebook does not borrow the
      # identity of whoever started it, otherwise the scheduled run would
      # depend on one person.
      authentication: ServicePrincipal
      tenant_id: "{{ env_var('TENANT_ID') }}"
      client_id: "{{ env_var('CLIENT_ID') }}"
      client_secret: "{{ env_var('CLIENT_SECRET') }}"
      threads: 4

The platform’s scheduler triggers the notebook, and that is all it can do: it sees one task where there are two hundred models. It is exactly the flaw described in another piece, except that here there is no alternative: the platform exposes no mechanism for making the graph visible. What can be done is to make it legible afterwards: the results returned by the entry point are written to a table, and a report built on it says which model failed and since when.

I do not compare this with the platform’s native pipelines, which do part of the same thing without dbt. The choice turns on a criterion technique does not supply: if the team already knows dbt and practises it elsewhere, keeping it beats learning one more tool; if it does not, introducing it for a single warehouse is a debt taken on without saying so. And I say nothing about running dbt tests in production, which we did at the same time as the build: convenient and arguable, since a wrong table is then published before being declared wrong.

• dbt · point d’entrée en Python
• dbt-fabric