Generating vector tiles from dbt marts

This page turns a polygon mart into a Mapbox Vector Tile pyramid that lives in the warehouse as an ordinary dbt model: keyed by zoom, column and row, clipped per tile, encoded as binary MVT, rebuilt incrementally, and tested against a payload budget.

When to use this approach

  • Your map data changes on a build cadence, not continuously. A tile pyramid rebuilt nightly by dbt inherits lineage, tests and scheduling for free. If tiles must reflect edits within seconds, use a dynamic tile server instead.
  • The pyramid is shallow enough to store. Through zoom 10 for a country is a manageable table; through zoom 14 is hundreds of millions of rows, and the economics reverse — see the depth discussion in serving spatial data to consumers.
  • You want the tiles tested. A tile is a binary blob, which sounds untestable, but its size, feature count and coverage are all assertable in SQL — which is the main reason to build it here rather than in a separate service.

Prerequisites

  • PostGIS 3.0+ with ST_AsMVT and ST_AsMVTGeom, or a warehouse with an equivalent MVT encoder.
  • A mart with valid geometry, a stable feature id, and the small set of attributes the map will render.
  • A serving schema with its own grants.
  • Web Mercator, EPSG:3857 — the tile grid is defined in it, so a reprojection step is required if your canonical SRID is 4326.
  • dbt-utils for the range tests used below.

Step-by-step instructions

1. Enumerate the tiles you intend to build

A tile pyramid is a join between features and a tile grid, so the grid needs to exist as rows. Generate it for the zoom range you decided on, restricted to the extent your data actually covers.

sql
-- models/serving/serve_tile_grid.sql
{{ config(materialized = 'table') }}

with bounds as (
    select st_transform(st_setsrid(st_extent(geom), 4326), 3857) as extent
    from {{ ref('mart_zones') }}
),

zooms as (
    select generate_series({{ var('tile_min_zoom') }}, {{ var('tile_max_zoom') }}) as z
),

grid as (
    select
        z.z,
        x.x,
        y.y
    from zooms z
    cross join lateral (
        select generate_series(
            floor((st_xmin(b.extent) + 20037508.34) / (2 * 20037508.34 / pow(2, z.z)))::int,
            floor((st_xmax(b.extent) + 20037508.34) / (2 * 20037508.34 / pow(2, z.z)))::int
        ) as x
        from bounds b
    ) x
    cross join lateral (
        select generate_series(
            floor((20037508.34 - st_ymax(b.extent)) / (2 * 20037508.34 / pow(2, z.z)))::int,
            floor((20037508.34 - st_ymin(b.extent)) / (2 * 20037508.34 / pow(2, z.z)))::int
        ) as y
        from bounds b
    ) y
)

select * from grid

Verify the grid is the size you expected before building anything on top of it:

sql
select z, count(*) as tiles from {{ ref('serve_tile_grid') }} group by z order by z;
-- Each zoom level should be roughly 4× the one below; a sudden jump means the extent is wrong
How a tile address maps to a rectangle of the world at each zoom level Three panels showing the same area at successive zoom levels. At zoom zero a single tile covers the whole world. At zoom one, four tiles are addressed by column and row from the top left. At zoom two, sixteen tiles, with one highlighted and labelled with its z, x and y address. A caption notes that the grid model in the previous step generates exactly these addresses for the data's extent. z = 0 0/0/0 one tile, the whole world z = 1 1/0/0 1/1/0 1/0/1 1/1/1 x counts east, y counts south z = 2 2/1/1 16 tiles — and 4× again at z = 3

2. Clip each feature to its tile

ST_AsMVTGeom does three jobs at once: it clips the geometry to the tile envelope, translates it into the tile’s local coordinate space, and quantizes it to the tile’s integer extent. Skipping it is the single most common cause of enormous tiles.

sql
-- models/serving/serve_zone_tile_features.sql
{{ config(materialized = 'table', indexes = [{'columns': ['z', 'x', 'y']}]) }}

with tile_envelopes as (
    select
        z, x, y,
        st_tileenvelope(z, x, y) as env
    from {{ ref('serve_tile_grid') }}
),

clipped as (
    select
        t.z, t.x, t.y,
        m.zone_id,
        m.zone_name,
        m.activity_band,
        st_asmvtgeom(
            st_transform(m.geom, 3857),
            t.env,
            4096,      -- tile extent in integer units
            64,        -- buffer, in the same units, for edge continuity
            true       -- clip geometry to the tile
        ) as mvt_geom
    from tile_envelopes t
    join {{ ref('mart_zones') }} m
      on st_intersects(st_transform(m.geom, 3857), t.env)
)

select * from clipped where mvt_geom is not null

The where mvt_geom is not null matters: a feature whose clipped remnant is degenerate returns null, and passing nulls into the encoder produces empty features rather than an error.

Verify the clipping actually reduced vertex counts:

sql
select
    z,
    round(avg(st_npoints(mvt_geom))) as avg_vertices,
    max(st_npoints(mvt_geom)) as max_vertices
from {{ ref('serve_zone_tile_features') }}
group by z order by z;
-- Values should stay in the hundreds; thousands means the source needs simplification first
Clipping a large polygon to tile boundaries instead of repeating it whole On the left, one large region polygon overlapping a two-by-two grid of tiles; without clipping, each of the four tiles contains the entire polygon, so the same geometry is stored four times. On the right, the same polygon after clipping, where each tile holds only the portion inside it plus a small buffer, and the total stored geometry equals the polygon once. unclipped · stored four times z/x/y every tile carries the whole shape clipped · stored once, in pieces each tile carries only its own portion

3. Encode one row per tile

sql
-- models/serving/serve_zone_tiles.sql
{{ config(materialized = 'table', indexes = [{'columns': ['z', 'x', 'y'], 'unique': true}]) }}

select
    z, x, y,
    st_asmvt(f.*, 'zones', 4096, 'mvt_geom') as mvt
from (
    select z, x, y, zone_id, zone_name, activity_band, mvt_geom
    from {{ ref('serve_zone_tile_features') }}
) as f
group by z, x, y

The layer name — 'zones' — is part of the client contract: a style referencing source-layer: "zones" breaks if this string changes. Treat it like a column name, not like an implementation detail.

Verify a tile decodes and holds the expected features:

sql
select z, x, y, octet_length(mvt) as bytes
from {{ ref('serve_zone_tiles') }}
order by bytes desc limit 5;
-- Then fetch one and open it in a client; a tile that renders is the only real proof

4. Rebuild only the tiles whose features changed

A full pyramid rebuild is wasteful when a handful of boundaries moved. Restrict the rebuild to the tiles intersecting the changed extent.

sql
{{ config(
    materialized = 'incremental',
    unique_key = ['z', 'x', 'y'],
    incremental_strategy = 'delete+insert'
) }}

with changed_extent as (
    select st_transform(st_setsrid(st_extent(geom), 4326), 3857) as extent
    from {{ ref('mart_zones') }}
    {% if is_incremental() %}
    where updated_at > (select coalesce(max(built_at), '1900-01-01') from {{ this }})
    {% endif %}
),

affected_tiles as (
    select g.z, g.x, g.y
    from {{ ref('serve_tile_grid') }} g, changed_extent c
    where st_intersects(st_tileenvelope(g.z, g.x, g.y), c.extent)
)

select
    t.z, t.x, t.y,
    st_asmvt(...) as mvt,
    current_timestamp as built_at
from affected_tiles t
join ...
group by t.z, t.x, t.y

Verify the incremental run touched a plausible number of tiles:

sql
select count(*) as rebuilt from {{ ref('serve_zone_tiles') }} where built_at > current_date;
-- A boundary edit in one city should rebuild hundreds of tiles, not millions

5. Test the payload budget and coverage

yaml
# models/serving/schema.yml
models:
  - name: serve_zone_tiles
    description: MVT pyramid keyed by z/x/y, layer name 'zones'.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [z, x, y]
    columns:
      - name: mvt
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "octet_length(mvt) between 100 and 500000"

The lower bound is as useful as the upper one: a tile of a hundred bytes is an empty tile, which usually means the join lost its features rather than that the area is genuinely empty.

The four models that make a tile pyramid, and what each one is tested for Four models in sequence. The tile grid model is tested for tile counts growing fourfold per zoom. The clipped features model is tested for vertex counts per tile. The encoded tile model is tested for payload size and unique z x y keys. The incremental rebuild is tested for a plausible number of rebuilt tiles. Each test is drawn attached beneath its model. serve_tile_grid z / x / y rows tile features ST_AsMVTGeom encoded tiles ST_AsMVT incremental rebuild changed extent only tiles ×4 per zoom extent sanity vertices per tile no null geometry 100 B – 500 KB unique z/x/y rebuilt-tile count plausible, not millions A binary blob is still testable — by its size, its key, and the features it contains.

Configuration reference

Parameter Where Typical value Note
tile extent ST_AsMVTGeom / ST_AsMVT 4096 Must match between the two calls or coordinates are wrong
buffer ST_AsMVTGeom 64 Prevents visible seams where a feature crosses a tile edge
clip ST_AsMVTGeom true The setting that stops whole-geometry duplication
layer name ST_AsMVT 'zones' Part of the client style contract; changing it breaks the map
tile_min_zoom / tile_max_zoom project vars 0 / 10 Each extra level multiplies the pyramid by four
unique_key model config ['z','x','y'] Required for delete+insert rebuilds
target SRID transform 3857 The tile grid is defined in Web Mercator

Gotchas & edge cases

  • Mismatched extents between ST_AsMVTGeom and ST_AsMVT produce geometry in the wrong place, and the tile still renders — just wrongly. Set the value once in a var and reference it twice.
  • Features exactly on a tile boundary appear twice unless the client dedupes by id. Always include a stable feature id in the encoded attributes.
  • Simplify before tiling, not instead of it. Clipping reduces duplication; simplification reduces vertices. They solve different problems, and a pyramid built from unsimplified geometry is large even when perfectly clipped — see simplifying geometries for map payloads.
  • Empty tiles are still rows. Decide whether to store them (predictable lookups, larger table) or omit them (smaller table, client must handle 404) and make the client agree.
  • generate_series on a large zoom range can explode before any tiles are built. Cap tile_max_zoom in the var, and check the grid counts in step 1 before proceeding.

FAQ

Why store tiles in the warehouse rather than a file store?

Because they get tested, versioned and rebuilt with the rest of the DAG. A tile in a bucket has no lineage: when a map shows a stale boundary, you cannot tell which build produced it. Exporting to object storage afterwards is easy and worth doing for serving; generating there is what loses the guarantees.

How deep should the pyramid go?

To the deepest zoom your users actually reach, which is a measurement rather than a guess — instrument the client. Common practice is to pre-generate through zoom 10 or 12 and generate deeper tiles on demand behind a cache, because each level costs four times the previous one.

Can I build vector tiles on BigQuery or Snowflake?

Not with ST_AsMVT, which is PostGIS-specific. On those engines the usual pattern is to serve GeoJSON or to export features and encode tiles in a separate step. If tiles are a firm requirement, keep a small PostGIS instance as the serving engine and feed it from the warehouse — the split is discussed in warehouse-native GIS adapters.

What is the buffer parameter actually for?

Continuity. A polygon crossing a tile edge would otherwise be clipped exactly at the boundary, and the renderer would draw a hairline seam where two tiles meet. A 64-unit buffer at 4096 extent gives the renderer enough overlap to hide the join, at a small cost in bytes.

Up: Part of Serving Spatial Data to Consumers.