Wherobots dbt Adapter

The Wherobots dbt adapter (wherobots-dbt) makes Wherobots Cloud a target in a dbt project. You write models as Spatial SQL files. dbt builds them in dependency order and Wherobots runs them against your catalogs. Your spatial models are then version-controlled, tested, and documented like the rest of your dbt project.

Benefits

Spatial models work like any dbt model

Spatial models are ordinary dbt models: dbt orders them by their ref() dependencies, and lineage, selectors, and scheduling work as they do in any dbt project. Each dbt invocation runs against one target, so if the same project also builds models on another warehouse, build the Wherobots models and the warehouse models in separate runs, each with its own target and a selector. The Wherobots models can read only catalogs your organization can access, so any warehouse output they depend on has to be in one of those catalogs. See Read from other catalogs.

Tests and documentation work as usual

Generic tests (unique, not_null, relationships, accepted_values), singular tests, and dbt docs generate all work against Wherobots relations. You can assert spatial invariants, such as a consistent spatial reference identifier (SRID), with plain SQL tests.

Iceberg tables by default

Every persisted model — table, incremental, and snapshot — materializes as an Apache Iceberg table, so you get schema evolution, partitioning, and time travel without configuring a file format. A view model stays a view, and an ephemeral model is never persisted at all.WherobotsDB stores tables in Apache Iceberg. New tables in Wherobots-managed catalogs, such as org_catalog, are format-version 3 (V3), which has geometry and geography column types, and Wherobots adds a raster column type. Tables created before September 15, 2026 were created at format-version 2 (V2), and Wherobots doesn’t raise them to V3. Which column types a table can have, and whether WherobotsDB skips data files for spatial filters, depend on its format version; see the Iceberg Tables Overview.

Geometry is a column type

Seeds load geometry from well-known text (WKT), extended well-known text (EWKT), GeoJSON, or well-known binary (WKB), and the adapter enforces a real, non-zero SRID on every value. See Spatial data in dbt models. For tables whose geometry columns have no declared CRS, see Upgrade Existing Geometry Tables.

Before you start

Wherobots requirements

A Wherobots Account. See Create a Wherobots Account.
A Wherobots API key. See API keys.
A catalog you can write to. The adapter has no implicit default catalog, so you must name one in your profile. See Data Catalogs.

dbt requirements

Python 3.10 or later.
dbt Core 1.9 or later (below 2.0).
Familiarity with dbt projects, profiles, and models. See the dbt documentation.

Install the adapter

Install the adapter from PyPI into the same Python environment as dbt:

pip install wherobots-dbt

If that environment has no dbt Core yet, the command also installs a compatible version (1.9 or later, below 2.0). dbt discovers the adapter through the Python module system. Installing the package registers dbt.adapters.wherobots, so any profiles.yml target with type: wherobots resolves automatically with no further configuration.

Configure your profile

Add a Wherobots target to profiles.yml

Add an output with type: wherobots to ~/.dbt/profiles.yml. Read the API key from an environment variable so no secret is written to disk.

~/.dbt/profiles.yml

my_project:
  target: dev
  outputs:
    dev:
      type: wherobots
      host: api.cloud.wherobots.com
      catalog: my_catalog
      schema: my_namespace
      api_token: "{{ env_var('WHEROBOTS_API_KEY') }}"
      runtime: micro
      region: aws-us-west-2
      threads: 1

catalog is the Wherobots name for dbt’s database. Use one or the other, never both — dbt rejects a target that sets both keys.

Export your API key

export WHEROBOTS_API_KEY="<your-api-key>"

Point your project at the profile

Set profile: in dbt_project.yml to the profile name you defined above.

dbt_project.yml

name: 'my_project'
version: '1.0.0'
config-version: 2

profile: 'my_project'

Verify the connection

Run dbt debug from your project directory.

dbt debug

You should see Connection test: [OK connection ok], along with the resolved host, port, database, schema, threads, retries, runtime, and region. Your API token is not included in this output.The first connection provisions Wherobots compute, which can take several minutes. dbt logs provisioning heartbeats while it waits, so you can tell the run is not stuck.

Profile reference

The following keys are valid in a type: wherobots output.

Required

Key	Description
`type`	Must be `wherobots`.
`host`	The Wherobots API endpoint for your organization, for example `api.cloud.wherobots.com`. A leading `https://` is accepted and stripped.
`catalog`	The catalog to build into. This is an alias of dbt’s `database`; set one, not both. There is no implicit default catalog.
`schema`	The default namespace to build relations into.
`api_token`	Your Wherobots API key. Required under token authentication.

Optional

Key	Default	Description
`port`	`443`	Endpoint port.
`threads`	`1`	Number of models dbt builds concurrently. dbt holds at most `threads` + 1 connections to Wherobots at a time. With the default `session_type` and `force_new`, those connections share one runtime and its SQL session.
`retries`	`1`	How many times to retry opening a connection. Retries apply only to transport errors.
`authenticator`	`token`	Authentication method. `token` is the only supported value; anything else fails with a configuration error.
`runtime`	Server default	Wherobots runtime to attach to, for example `micro`. See Runtimes.
`region`	Server default	Wherobots region, for example `aws-us-west-2`. See Availability.
`session_type`	`multi`	`multi` lets connections share a runtime. `single` gives a connection a runtime that no other open connection is using, so two connections open at the same time get separate runtimes.
`force_new`	`false`	When `true`, each connection the adapter opens starts a new runtime instead of attaching to a running one, so one run can start up to `threads` + 1 runtimes. Other runs can still attach to those runtimes. See Keep runs apart.
`shutdown_after_inactive_seconds`	Server default, about 5 minutes	How long, in seconds, the runtime keeps running after its last connection closes. The runtime stops up to about 3 minutes after this timeout runs out. See Sessions and billing.

A missing catalog, host, schema, or api_token fails at profile load with a configuration error naming the missing key. The adapter has no default for any of the four, so fix the profile.

Sessions and billing

dbt runs every statement on a Wherobots SQL session, which runs on a runtime. The adapter attaches to a session at a run’s first statement and reuses the run’s connections for later models, so the number of connections doesn’t grow with the number of models. The adapter attaches another connection only when every open one is busy. With the default session_type and force_new, all of a run’s connections land on one session. dbt closes the run’s connections when it exits. Billing for the session starts when the session is ready, not while its runtime starts. See What starts a billable event. After the session’s last connection closes, the session keeps running, and billing, until it stops. If no other run uses the session, its last connection closes when dbt exits.

The session’s idle timeout is shutdown_after_inactive_seconds, counted from when its last connection closes, whichever run that connection belongs to. The default is about 5 minutes.
Wherobots checks for idle sessions periodically, so the session stops up to about 3 minutes after the timeout runs out.
A session also stops when it reaches your organization’s Runtime TTL.

A run with force_new: false that starts before the session stops attaches to it in seconds instead of waiting for a new runtime.

wherobots-dbt 0.1.2 and earlier open a new connection for every model. On those versions, force_new: true starts a new runtime for every model.

A connection with force_new: false attaches to a running session when all of the following match:

The Wherobots user that the API key belongs to.
region.
runtime and session_type, after defaults are filled in. An unset session_type matches multi, and an unset runtime matches your organization’s default runtime.
shutdown_after_inactive_seconds, as written. An unset value matches only another unset value, not an explicit number, even one equal to the server default.

The dbt project, target, catalog, schema, and threads don’t affect the match. Two unrelated projects that use the same API key, runtime, and region share one session, and with the default session_type: multi they share it while both run. How a session started doesn’t affect the match either: a session that a force_new: true connection started accepts any matching connection that comes after it. Runs on one session share its compute, its Spark configuration (anything a set statement changes), and its temporary views. The adapter derives each staging view’s name from the catalog, schema, and name of the table it builds, and sets the partition overwrite mode on each statement, so its statements from runs that build different tables don’t collide. Two runs that build the same table at the same time use the same staging view and write the same table, so don’t run them concurrently. That includes runs that use different dbt targets with the same catalog and schema. A hook or model of your own that runs set or creates a temporary view affects every run on the session.

Keep runs apart

Use session_type: single. Runs that overlap in time get separate runtimes, and a run with threads above 1 can hold up to threads + 1 runtimes. After a run exits, a later run with the same settings still attaches to its session until the session stops. force_new: true doesn’t keep runs apart. Each connection the adapter opens waits for a new runtime instead of attaching to a running one, so one run can start up to threads + 1 runtimes. With session_type: multi, another run with matching settings and force_new: false can still attach to those sessions, while the forcing run is connected or after it exits.

Shorten the idle time of scheduled runs

For a job that runs once a day, a session left running after the job finishes only adds cost. Lower shutdown_after_inactive_seconds:

~/.dbt/profiles.yml

my_project:
  target: prod
  outputs:
    prod:
      type: wherobots
      host: api.cloud.wherobots.com
      catalog: my_catalog
      schema: analytics
      api_token: "{{ env_var('WHEROBOTS_API_KEY') }}"
      threads: 1
      shutdown_after_inactive_seconds: 60

With this profile, the session stops 1 to 4 minutes after its last run exits. If a schedule runs several dbt commands one after another, such as dbt seed and then dbt build as separate Airflow tasks, set the timeout longer than the longest gap between them. Each command then attaches to the running session instead of waiting for a new runtime.

Read from other catalogs

The profile’s catalog and schema control where your models are written. Inputs are separate: a source’s database: key names the catalog that source reads from, so one project can read Wherobots-hosted catalogs and write to your own.

models/staging/_sources.yml

version: 2

sources:
  - name: nyc_taxi
    database: wherobots_pro_data
    schema: nyc_taxi
    tables:
      - name: yellow_2009_2010

  - name: overture_maps_foundation
    database: wherobots_open_data
    schema: overture_maps_foundation
    tables:
      - name: places_place

  - name: us_census
    database: wherobots_pro_data
    schema: us_census
    tables:
      - name: zipcode

A model can then join across catalogs, because the adapter writes every relation as a fully qualified name. The filter on the source’s bbox columns keeps the scan to New York City instead of every place on the planet:

select z.zip, count(*) as place_count
from {{ source('overture_maps_foundation', 'places_place') }} p
join {{ ref('stg_zipcode_zones') }} z
  on ST_Contains(z.zone_geom, ST_SetSRID(p.geometry, 4326))
where p.bbox.xmin >= -74.05 and p.bbox.xmax <= -73.90
  and p.bbox.ymin >= 40.68 and p.bbox.ymax <= 40.85
group by z.zip

wherobots_open_data and wherobots_pro_data are available to every Organization Edition. See Spatial catalog. If a source fails to resolve, see Troubleshooting.

What works today

dbt command	Supported
`dbt debug`	Yes
`dbt seed`	Yes. See Seeds.
`dbt run`	Yes. See Materializations.
`dbt test`	Yes, for generic and singular tests.
`dbt snapshot`	Yes. See Snapshots.
`dbt docs generate`	Yes.
`dbt show`	Yes.
`dbt source freshness`	Yes, with a `loaded_at_field` that evaluates to a timestamp.

dbt source freshness is expensive on large hosted sources. It reads loaded_at_field over the whole source table, and the filters and variables that bound your models do not bound it. Run it only when you need it.

Limitations

Query cancellation is not supported

The Wherobots driver has no query cancel. Interrupting dbt run stops the dbt process but does not stop work already running on the server. Let a long query finish, or stop the runtime from the Wherobots Cloud interface.

Only token authentication is supported

authenticator must be token and api_token must be set. Single sign-on and OAuth are not available through the adapter.

The microbatch incremental strategy is unavailable

The adapter supports append, merge, delete+insert, and insert_overwrite. Selecting microbatch fails with dbt’s own “not valid for this adapter” error before the model’s materialization SQL runs. The model’s pre-hooks still run first.

on_schema_change='sync_all_columns' is unsupported

Syncing column types requires alter column ... type, which the Iceberg catalog rejects. Use append_new_columns, fail, or ignore.

Non-string seed column_types overrides fail

Seed values are written as string literals, and the engine rejects an implicit string-to-date or string-to-timestamp assignment. A seed with +column_types: {birthday: date} fails at analysis time. Declare the column as string and cast it in a downstream model instead. The geometry override is the exception — it is handled by the geometry type handler. See Spatial data in dbt models.

Seeds cannot declare a geography column

A seed with +column_types: {g: geography} fails with the engine’s UNSUPPORTED_DATATYPE error, because a bare geography column declares no coordinate reference system. Models can still materialize geography columns. See Geography.

Model contracts are not enforced

A model with contract: {enforced: true} builds as a plain create table as select. The adapter does not check the query’s columns or types against the contract.

Troubleshooting

Wherobots profile is missing 'catalog' (dbt 'database')

Cause: The target has neither catalog nor database set. The adapter has no implicit default catalog.Solution:

Add catalog: <a-catalog-you-can-write-to> to the output in profiles.yml.
Do not set both catalog and database — dbt rejects the target before validation runs.

dbt run appears to hang on the first model

Cause: Wherobots compute is provisioning. A cold start can take several minutes.Solution:

Watch for provisioning heartbeats in the dbt log — they confirm the attach is progressing.
Leave force_new at false, so the run attaches to a running session when one matches. See When runs share a session.
If you run dbt commands back to back, set shutdown_after_inactive_seconds longer than the gap between them. The session keeps running, and billing, for that long after each command.

Runtime Error: SQL connection lost

Cause: The connection to the SQL session dropped while a statement was running. The statement may still have completed on Wherobots, so dbt reports

SQL connection lost (session=…, execution=…). Commit outcome is unknown; verify the operation before retrying writes.

The adapter does not retry a statement that may have reached the server.Solution: Run the failed models again, after checking what a second run writes. The connection can drop during any statement of a model, including a hook, and that statement may have committed:

A re-run runs every hook again, so a hook that writes data writes it a second time if the failed attempt already ran it.
A view, table, seed, or snapshot ends with the same rows, and so does an incremental model that uses insert_overwrite.
An incremental model that uses merge or delete+insert ends with the same rows, except for rows a re-run inserts a second time:
- every row, when the model has no unique_key
- a row whose unique_key is NULL (see merge)
- a row whose existing copy falls outside the model’s incremental_predicates
A delete+insert model deletes and then inserts in two statements. If the connection dropped after the delete committed, a re-run restores the deleted rows only if the model still selects them. An is_incremental() filter that keeps only keys already in the table does not, and those rows stay missing.
An append model inserts every row a second time, unless its is_incremental() filter excludes rows already in the table.

TABLE_OR_VIEW_NOT_FOUND on a source

Cause: The source’s database: names a catalog your organization cannot read, or the relation does not exist in that catalog.Solution:

Confirm the catalog is attached to your organization in Data Catalogs.
wherobots_open_data and wherobots_pro_data are available to every Organization Edition. If either is missing from Data Catalogs, contact Wherobots Support.

MERGE_CARDINALITY_VIOLATION during an incremental run

Cause: The staged rows hold more than one row for a unique_key value that already exists in the table. dbt’s unique_key contract requires the key to be unique in the model’s output. The table is left unchanged.Solution:

Deduplicate the model’s select so each unique_key value appears once.
Add a unique test on the key. The run fails only when a duplicated key already exists in the table. Duplicates of a new key are inserted with no error, and the test is what catches them.

Next steps

Materializations

Views, tables, ephemeral models, the four incremental strategies, snapshots, and seeds.

Spatial data in dbt models

Seed geometry from five formats, manage SRIDs, and test spatial invariants.

Spatial SQL functions

The Spatial SQL functions available to your dbt models.

Start here

Notebooks

Editors and AI assistants

VS Code extension

MCP server and agent skills

dbt

Apache Airflow

Storage management

Task schedules

Deployment and monitoring

Wherobots dbt Adapter

Benefits

Before you start

Install the adapter

Configure your profile

Profile reference

Required

Optional

Sessions and billing

Keep runs apart

Shorten the idle time of scheduled runs

Read from other catalogs

What works today

Limitations

Troubleshooting

Next steps

Materializations

Spatial data in dbt models

Spatial SQL functions

​Benefits

​Before you start

​Install the adapter

​Configure your profile

​Profile reference

​Required

​Optional

​Sessions and billing

​When runs share a session

​Keep runs apart

​Shorten the idle time of scheduled runs

​Read from other catalogs

​What works today

​Limitations

​Troubleshooting

​Next steps

Materializations

Spatial data in dbt models

Spatial SQL functions

Benefits

Before you start

Install the adapter

Configure your profile

Profile reference

Required

Optional

Sessions and billing

When runs share a session

Keep runs apart

Shorten the idle time of scheduled runs

Read from other catalogs

What works today

Limitations

Troubleshooting

Next steps