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.

Havasu tables by default

Every persisted model — table, incremental, and snapshot — materializes as a Havasu (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.

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.

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 Hub.

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. Each thread opens its own connection to Wherobots. With the default `session_type`, 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`, always start a new runtime instead of attaching to an existing one. Useful for isolating concurrent runs from each other.
`shutdown_after_inactive_seconds`	Server default, about 5 minutes	How long, in seconds, the runtime keeps running after its last connection closes. A run that starts within that window attaches to the same runtime instead of waiting for a new one.

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.

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 is available to every Organization Edition. wherobots_pro_data is available only to paid Organization Editions. 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.
Attach to a warm runtime by leaving force_new at false, or raise shutdown_after_inactive_seconds so your runtime stays available between runs.

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 Hub.
wherobots_pro_data is available only to paid Organization Editions. Contact Wherobots Support to confirm yours.

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 four formats, manage SRIDs, and test spatial invariants.

Write spatial queries

The Spatial SQL functions available to your dbt models.

​Benefits

​Before you start

​Install the adapter

​Configure your profile

​Profile reference

​Required

​Optional

​Read from other catalogs

​What works today

​Limitations

​Troubleshooting

​Next steps

Materializations

Spatial data in dbt models

Write spatial queries

Benefits

Before you start

Install the adapter

Configure your profile

Profile reference

Required

Optional

Read from other catalogs

What works today

Limitations

Troubleshooting

Next steps