Modelling OSM for Analytics Warehouses

OSM resists warehouse modelling for one specific reason: the tag set is open. There is no fixed list of attributes, new keys appear continuously, and any schema built by enumerating columns is out of date before it ships.

How OSM elements become a warehouse schema Normalized OSM features enter three parallel modelling decisions. The feature table holds one row per feature with a stable surrogate key, its geometry, and the small set of attributes every consumer needs. The tag model holds the open-ended remainder, either as a key-value table or as a semi-structured column. The dimension tables hold the classifications and administrative hierarchy that queries group by. All three are keyed consistently so a query can join across them without knowing which decision a given attribute fell under. One feature, three modelling decisions normalized features tags and geometry feature table key, geometry, core tag model the open remainder dimensions class and hierarchy consistent keys joinable across all three The middle column is the whole design: which attributes are promoted to columns and which stay in the open model.
Getting the split wrong is recoverable; getting the key inconsistent across the three is not.

The Problem This Topic Solves

You need OSM data in a warehouse so analysts can query it alongside everything else, and the obvious approach — a wide table with a column per interesting tag — fails predictably. It fails when a new key matters, when a region uses a tagging convention nobody anticipated, and when somebody asks a question about a tag that was dropped because it was not in the original list.

The failure scenario is a schema that ossifies. Twenty columns were chosen, the pipeline drops everything else, and eighteen months later answering a new question means re-running the whole ingestion because the data needed was never stored. Meanwhile the twenty columns are mostly null, because most features carry only a handful of tags.

Prerequisites

Have normalized features from Parsing & Tag Normalization Workflows, and understand the identity problem from OSM Feature Identity & ID Stability — a warehouse key that is an OSM identifier inherits every instability that topic describes.

The Three Modelling Decisions

Which attributes become columns. Promote an attribute when most features have it, when queries filter or group on it, and when its type is stable. Feature class, name, administrative area and geometry qualify almost always. A tag carried by two percent of features does not, however interesting it is.

How the remaining tags are stored. Three options, each with a real cost. A key-value table with one row per tag is flexible and makes every tag query a join, which for wide scans is expensive. A semi-structured column — a map or JSON type — keeps everything on the feature row and is queryable in most modern warehouses, at the cost of type discipline. A hybrid promotes a small set and keeps the rest semi-structured, which is what most production schemas converge on.

What the dimensions are. Feature class, administrative hierarchy and time are the three that recur. The administrative hierarchy in particular is worth materialising as a dimension rather than computed per query, because a point-in-polygon join over a country’s boundaries is expensive and the answer changes rarely.

Three ways to store the open-ended tag set, compared A grid of four properties against three tag storage models. A key-value table stores one row per tag, handles any key, requires a join for every tag predicate, and scans poorly for wide queries. A semi-structured map column stores all tags on the feature row, handles any key, requires no join, and scans well but offers weak type guarantees. A hybrid promotes frequently used tags to real columns and keeps the rest in a map, which gives typed fast access to the common cases and full flexibility for the rest at the cost of two places to look. Three tag models, and most schemas end at the third Key-value table Map column Hybrid Any key yes yes yes Join needed always never for the map only Wide scans poor good good Type safety none weak strong where promoted The hybrid's cost is that an analyst must know which attributes are columns, which is solved by a view rather than by a schema change.
Starting with the second model and promoting columns as query patterns emerge is the least regrettable path.
How many features carry each candidate attribute, on a country extract Six candidate attributes with the share of features carrying each, which is the evidence for deciding what to promote to a column. Geometry is present on every feature by definition. A feature class derived from the primary tag is present on nearly all. A name is present on a minority. An address is present on fewer still. Opening hours and a website are each present on a small fraction. A note observes that promoting anything below roughly a fifth produces a column that is mostly null. Presence rate decides what becomes a column Geometry 100% Feature class about 97% Name about 31% Address about 14% Opening hours about 3% Website about 2% Below roughly a fifth, a promoted column is mostly null and costs more in storage and confusion than the join it avoids.
Measuring presence on your own extract is a minute's work and settles an argument that otherwise runs for weeks.

Partitioning Is a Query Decision

Partitioning an OSM warehouse table wrongly is the difference between a query scanning a gigabyte and a terabyte, and the choice follows from how it is queried rather than from how the data arrives.

By administrative area suits queries that are almost always scoped to a country or region, which is the common case for OSM analytics. It partitions unevenly — some countries are enormous — but it matches the filter analysts actually apply.

By feature class suits queries that look at one kind of thing across a wide area. It partitions very unevenly, since buildings and roads dominate.

By a spatial cell — an H3 cell or a quadkey prefix, as compared in Spatial Index Selection: R-tree vs H3 vs Quadkey — partitions evenly and suits arbitrary spatial filters, at the cost of every query needing to translate its region into cells.

By load date suits nothing about the queries and everything about the loading, which is why it is so often chosen and so often regretted.

Most production schemas partition by area and cluster or sort within a partition by a spatial cell, which serves both the common filter and the arbitrary one. The details are worked through in Choosing Partition Keys for an OSM Data Lake.

Geometry in a Warehouse

Geometry is the one column that behaves unlike everything else, and how it is stored decides which questions are answerable at all.

Store it, do not derive it. A table holding only a centroid can answer “how many” and “where roughly”, and cannot answer anything about shape, adjacency or overlap. Since a warehouse is where questions arrive that nobody anticipated, dropping geometry to save space is the single most regretted storage decision in this area.

Store it once, in one projection. Geographic coordinates are the right storage form because they are what the source uses and what every consumer can reproject from. Storing a second projected copy for convenience doubles the column and creates the possibility of the two disagreeing after an update.

Keep the derived measures alongside. Area and length computed correctly at load, as described in Measuring Area Accurately on OSM Polygons, are far cheaper to store than to recompute per query, and they remove the most common opportunity for an analyst to compute them wrongly.

Expect geometry to dominate the bytes. On a feature table, geometry is routinely most of the storage and most of the scan cost, which is why a query that does not need it should not read it. Columnar formats make that free; row-oriented ones do not, and that difference alone often decides the storage engine.

The practical consequence is that a warehouse table over OSM should carry full geometry, a small number of correctly computed measures, and a column layout that lets the common non-spatial queries avoid reading the geometry at all.

Incremental Loading and History

A full reload of a continental extract is hours of work to change a fraction of a percent of rows, which is why the diff stream matters here as much as it does for tiles. The pattern is the one in Incremental Updates for Derived Datasets: read the change file, resolve the affected features, and merge them into the table.

History raises a separate question: does the warehouse hold the current state or the history of states? A current-state table is simpler and answers “what is there now”. A slowly changing dimension keeps validity dates per row and answers “what was there in March”, at the cost of a table several times larger and every query needing a date predicate.

The honest answer for most analytics is current state plus a snapshot: keep the live table current, and write a dated copy periodically. It answers the historical questions people actually ask — comparing quarters — without the cost of row-level history nobody queries.

Validation and Error Handling

Condition Root cause Detection Remediation
New question needs a re-ingest Tags dropped at load time The data was never stored Keep unpromoted tags in a semi-structured column
Table mostly null columns Columns enumerated optimistically Column null rates above ninety percent Demote rarely used columns back into the map
Queries scan everything Partitioned by load date Partition pruning never applies Partition by the dimension queries filter on
Row counts drift after edits Keyed on a bare OSM identifier Duplicates across element types Key on the type and identifier pair, or a surrogate
Joins to areas are slow Administrative hierarchy computed per query Repeated point-in-polygon work Materialise the hierarchy as a dimension
Historical comparison impossible Only current state retained No past rows exist Write dated snapshots on a schedule
Load takes hours for a small change Full reload on every run Runtime independent of change size Drive loading from the diff stream

Performance and Scale

Three things dominate.

Scan volume, governed by partitioning and by how wide the rows are. A semi-structured tag column keeps rows compact when most features carry few tags, which is the common case.

Join cost, governed by whether tags require a join and whether the administrative hierarchy is materialised. Both are avoidable.

Load cost, governed by whether the load is incremental. A full reload is simple and becomes untenable exactly when the dataset becomes valuable.

The measurement worth instrumenting is bytes scanned per query, because it is the number that both explains cost and responds to the partitioning decision. It is also, in most cloud engines, what the query is billed at, which makes it the rare technical metric a finance team already understands. A query pattern whose scan volume does not fall when a partition key is added is filtering on something the partitioning does not express.

One further practice is worth adopting early: record, for each promoted column, why it was promoted. A column list assembled over two years by several people accumulates entries nobody can justify, and the review that would remove them stalls on nobody being sure what depended on them. A one-line note per promotion — the query pattern that motivated it — turns that review into a reading.

Guides in This Topic

Frequently Asked Questions

Should tags be columns or a semi-structured field?

Both, in a hybrid. Promote the handful of attributes that most features carry and that queries filter on — class, name, area, geometry — to real typed columns, and keep everything else in a map or JSON column. That gives fast typed access to the common cases without ever dropping a tag, which is what makes a new question answerable without re-ingesting. Starting with everything semi-structured and promoting as patterns emerge is the least regrettable order.

What should the warehouse key be?

Not a bare OSM identifier, since those are scoped by element type and collide across them. At minimum, the type and identifier together. Better, a surrogate key your pipeline owns, mapped to OSM objects through a versioned table — that insulates downstream models from the splits and merges that ordinary mapping produces, which otherwise show up as unexplainable changes in counts.

How should the table be partitioned?

By whatever analysts actually filter on, which for OSM is usually an administrative area. Partitioning by load date is common because it matches how data arrives, and it is almost always wrong because no query filters on it. Clustering within a partition by a spatial cell serves the arbitrary spatial filters that area partitioning alone cannot prune.

Do I need row-level history?

Rarely. Most historical questions are comparisons between periods — how many cafés this quarter against last — which dated snapshots answer at a fraction of the cost. Row-level validity dates make every query carry a date predicate and multiply the table size, and they are worth it only when somebody genuinely needs to reconstruct an arbitrary past moment rather than compare two known ones.

Is a full reload ever acceptable?

For a small region, yes, and it is simpler than anything else. It stops being acceptable exactly when the dataset becomes large enough to be valuable, because the reload time grows with the data while the change per day stays roughly constant. Building the incremental path early is cheap; retrofitting it to a pipeline whose consumers now depend on a nightly full refresh is not.

Up one level: Parsing & Tag Normalization Workflows.