Exposing GeoJSON API endpoints from dbt models

This page builds the warehouse side of a GeoJSON feature service: a serving model whose rows are already features, a query contract that forces callers to bound their requests, grants narrow enough that the API user cannot reach the marts, and tests that keep a response inside its size budget.

When to use this approach

  • Consumers want features, not tiles. A search result list, a routing input, an export button and a “what is near me” call all want individual features with attributes — that is GeoJSON’s shape, not a tile’s.
  • The client is not a map renderer. Vector tiles are optimized for drawing; a mobile app that needs the five nearest depots wants five objects, and encoding them as tiles is work nobody benefits from. The comparison is in serving spatial data to consumers.
  • You need a service without a new pipeline. The API layer here is a thin read over a dbt-built table, so the data keeps its lineage and tests.

Prerequisites

  • ST_AsGeoJSON on the engine — available on PostGIS, BigQuery, Snowflake and DuckDB.
  • A serving schema with its own role, per data security scoping rules.
  • A simplified geometry band to serve from; full-precision geometry in an API response is the same mistake as in a tile.
  • An agreed response contract: maximum feature count, maximum payload bytes, and the required bounding-box parameter.

Step-by-step instructions

1. Shape the model like a feature, not like a row

A GeoJSON Feature has three parts — a geometry, a properties object, and an id. Build them as columns so the API layer does nothing but concatenate.

sql
-- models/serving/serve_zone_features.sql
{{ config(
    materialized = 'table',
    indexes = [{'columns': ['geom'], 'type': 'gist'}, {'columns': ['zone_id'], 'unique': true}]
) }}

select
    z.zone_id                                            as feature_id,
    z.geom,
    jsonb_build_object(
        'name',        z.zone_name,
        'category',    z.zone_category,
        'updated_at',  z.updated_at,
        'activity',    a.activity_band
    )                                                    as properties,
    st_asgeojson(z.geom, 6)::jsonb                       as geometry_json,
    z.geom::box2d::text                                  as bbox_text,
    '{{ run_started_at }}'                               as built_at
from {{ ref('serve_zone_geometry') }} as z
left join {{ ref('mart_zone_activity') }} as a using (zone_id)
where z.band_name = 'local'

The 6 in ST_AsGeoJSON is the coordinate precision, and it is the cheapest byte reduction available — roughly 11 cm on the ground, far below what any client resolves.

Verify a row is a valid feature before writing any API code:

sql
select jsonb_build_object(
    'type', 'Feature',
    'id', feature_id,
    'geometry', geometry_json,
    'properties', properties
) as feature
from {{ ref('serve_zone_features') }} limit 1;
-- Paste the result into any GeoJSON validator; it should parse as a single Feature
Mapping serving-model columns onto the parts of a GeoJSON FeatureCollection On the left, the columns of the serving model: feature_id, geometry_json, properties and bbox_text. On the right, the structure of a GeoJSON FeatureCollection containing Feature objects with id, geometry, properties and bbox members. Arrows connect each column to the member it becomes, showing that the API layer only wraps rows in a collection envelope rather than transforming them. serving model columns feature_id geometry_json properties bbox_text FeatureCollection Feature "id" "geometry" "properties" "bbox" The API adds an envelope and nothing else — no transformation at request time.

2. Make the bounding box a required parameter

An endpoint without a mandatory spatial filter will eventually be called without one, and the response will be the whole table. Encode the contract in the query the service runs.

sql
-- The service's parameterized query, not a dbt model
select
    feature_id,
    geometry_json,
    properties
from serving.serve_zone_features
where geom && st_makeenvelope($1, $2, $3, $4, 4326)   -- minx, miny, maxx, maxy
  and st_intersects(geom, st_makeenvelope($1, $2, $3, $4, 4326))
order by feature_id
limit $5                                              -- hard ceiling, not caller-controlled

Two properties matter. The && operator makes the GiST index serve the filter, following the pattern in tuning ST_Intersects joins with bounding-box prefilters. And the limit is a server-side constant with a caller-supplied value clamped below it, so a client asking for a million features receives the ceiling and a truncation flag rather than a timeout.

Verify the index is used for a realistic viewport:

sql
explain (analyze) select feature_id from serving.serve_zone_features
where geom && st_makeenvelope(13.3, 52.4, 13.5, 52.6, 4326);
-- Expect an Index Scan using the GiST index, not a Seq Scan
What each part of the query contract prevents Three contract clauses with the failure each one prevents. The required bounding box prevents a full-table response. The server-side limit ceiling prevents a caller requesting a million features. The stable order-by prevents pagination repeating or skipping rows. Each clause is drawn as a gate with the blocked failure listed beneath it. required bounding box geom && ST_MakeEnvelope(…) without it: the whole table in one response server-side ceiling LIMIT least($5, 5000) without it: a caller asks for a million features stable ordering ORDER BY feature_id without it: page two repeats rows from page one All three belong in the query the service runs — not in the client's good intentions.

3. Grant only what the service needs

sql
-- macros/grant_serving_reader.sql
{% macro grant_serving_reader() %}
  {% set sql %}
    grant usage on schema {{ target.schema }} to role api_reader;
    grant select on {{ this }} to role api_reader;
  {% endset %}
  {% do run_query(sql) %}
{% endmacro %}
yaml
models:
  dbt_geospatial:
    serving:
      +post-hook: "{{ grant_serving_reader() }}"

Verify the grant is as narrow as intended by checking what the role can actually see:

sql
set role api_reader;
select count(*) from analytics.mart_zones;   -- expect: permission denied
select count(*) from serving.serve_zone_features;  -- expect: a number
reset role;

4. Give responses a cache identity

A feature service that cannot be cached will be slow no matter how fast the query is. The build stamp carried on every row gives the service an ETag with no extra state.

sql
select
    max(built_at) as collection_version,
    count(*) as feature_count
from serving.serve_zone_features
where geom && st_makeenvelope($1, $2, $3, $4, 4326)

The service hashes collection_version with the request parameters, returns it as an ETag, and answers a matching If-None-Match with 304 Not Modified. Because built_at changes only when dbt rebuilds the model, caches stay valid for a whole build cycle and invalidate exactly when the data changes.

Verify the stamp advances on rebuild and not otherwise:

bash
dbt run --select serve_zone_features
psql -c "select distinct built_at from serving.serve_zone_features;"
# One value, equal to the run's start time
Request path with the build stamp used as an ETag between builds A client request reaches the service, which computes an ETag from the build stamp and the bounding box. If the client sends a matching If-None-Match header the service returns 304 with no database work. Otherwise it runs the bounded query and returns features with the ETag. A timeline underneath shows the stamp changing only when dbt rebuilds the model, which is when caches invalidate. client If-None-Match service etag = hash(built_at, bbox) clamps the limit 304 Not Modified no database work bounded query && envelope · LIMIT FeatureCollection + ETag header dbt run dbt run dbt run Caches stay valid for a whole build cycle and invalidate exactly when the data changes.

5. Test the response contract, not just the data

yaml
# models/serving/schema.yml
models:
  - name: serve_zone_features
    description: One GeoJSON-shaped feature per zone, local band. Served read-only to api_reader.
    columns:
      - name: feature_id
        tests: [unique, not_null]
      - name: geometry_json
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "geometry_json ? 'type'"
      - name: properties
        tests:
          - dbt_utils.expression_is_true:
              expression: "jsonb_typeof(properties) = 'object'"

Add a worst-case response test, since the contract is about the largest plausible request rather than the average one:

sql
-- tests/assert_worst_case_response_size.sql
select
    sum(octet_length(geometry_json::text) + octet_length(properties::text)) as bytes
from (
    select geometry_json, properties
    from {{ ref('serve_zone_features') }}
    order by octet_length(geometry_json::text) desc
    limit 1000
) worst
having sum(octet_length(geometry_json::text) + octet_length(properties::text)) > 2000000

Configuration reference

Parameter Where Typical value Note
coordinate precision ST_AsGeoJSON(geom, n) 6 About 11 cm; the cheapest size reduction available
feature ceiling service constant 1,000–5,000 Server-side; clamp the caller’s value below it
bounding box required query parameter — Without it the endpoint eventually returns the whole table
built_at model column run_started_at Cache identity with no extra state to manage
grants model post-hook select on serving only The API role must not reach marts or intermediate models
geometry band source model filter local or regional Never serve the full-precision mart geometry

Gotchas & edge cases

  • ST_AsGeoJSON output is a JSON string in some engines and JSON in others. Cast explicitly, or the API returns a quoted string where clients expect an object.
  • properties built with jsonb_build_object silently drops nulls in some formulations. Decide whether a missing attribute should be absent or explicitly null, and test for it — clients often branch on the difference.
  • A bounding box crossing the antimeridian is two boxes. ST_MakeEnvelope with a minimum longitude greater than the maximum produces an empty result; split the request server-side.
  • The bbox member is optional in GeoJSON but valuable. Clients use it to skip parsing features outside the viewport; computing it once in the model is cheaper than per-request.
  • Ordering matters for pagination. LIMIT without a stable ORDER BY returns arbitrary subsets, so page two can repeat rows from page one.

FAQ

Should the API assemble the FeatureCollection, or should SQL?

Assemble in the API. A single jsonb_agg in SQL is tempting, but it builds the entire collection in the database’s memory before the first byte is sent, which turns a streamable response into a spike. Returning one row per feature lets the service stream and lets the database stop early when a limit is hit.

How do I support "features near me" as well as a viewport?

Add a second query shape using ST_DWithin against a point with a server-clamped radius, ordered by distance and limited. Keep it as a separate endpoint rather than an optional parameter, so each has one index-eligible predicate and one obvious plan.

Is it acceptable to serve directly from the warehouse?

For low request rates, yes — with a bounded query, a narrow grant and a cache in front. For high rates, copy the serving model into a database sized for concurrency; analytical warehouses charge per query and are not built for thousands of small reads per second. Either way the model stays the dbt-built one, so lineage and tests are unchanged.

What should the endpoint return when the limit truncates the result?

The features it has, plus an explicit flag — a "truncated": true member alongside the collection, or a Preference-Applied header. Silent truncation is the failure mode that produces “the map is missing features” bug reports that nobody can reproduce, because it depends on the viewport.

Up: Part of Serving Spatial Data to Consumers.