Merging a vendor embedding file into an underwriting data warehouse
Most vendor data lands in your warehouse as columns you already understand: roof age, construction class, distance to coast. An embedding file is different. It's a vector, usually 64 to 512 floats, with no obvious meaning attached to any single position. The join mechanics matter more here than with a normal attribute feed, because there's nothing to sanity-check column by column. You either get the key right or you get silent mismatches that don't surface until a model run looks wrong.
Below is what goes sideways on these joins, and how teams who've run this a few renewal cycles handle it.
Keying the join, not the address
Vendors who deliver embeddings almost always key the file to a site ID you provided, not to a street address. That's deliberate. Address strings don't survive a join reliably: unit numbers get dropped, "St" becomes "Street," geocoders round coordinates differently depending on the day you pulled them. If your submission pipeline assigns a stable site ID at intake and that ID goes out in the file you send the vendor, the embedding comes back matched to the same ID. No fuzzy matching, no address normalization step, no manual review of the 200 records that didn't match cleanly.
If your book doesn't already have a stable site ID, this is the thing to fix before you order the file, not after. A lat/long pair rounded to five decimals works as a fallback key but it's worse: two buildings on adjacent lots in a dense urban block can round to the same coordinate, and you'll never catch it without a manual audit.
Where the vector lives in the schema
Two options, and most teams land on the first one eventually even if they start with the second.
Store the embedding as a single array or JSON column (site_id, embedding_vector, vintage_year). This keeps the warehouse schema stable as the vendor's vector length changes between annual deliveries, and it treats the embedding as what it is: one feature, not 256 features. Your modeling layer unpacks it downstream in whatever framework consumes it, not in the warehouse.
The alternative, exploding the vector into 256 named columns (emb_001 through emb_256), looks tidy in a BI tool but breaks the moment the vendor changes vector length or you add a second embedding source. It also makes the warehouse table unreadable to anyone who didn't write the ingestion script. Unless your BI layer genuinely needs flat columns for a dashboard, don't do this.
Tag every row with the delivery vintage. These files refresh on a cycle, typically annual, and a site that changes materially between deliveries (new construction next door, a roof replacement visible in the current imagery) will get a new vector. If you overwrite last year's row instead of versioning it, you lose the ability to check whether a score shift came from the imagery or from something else in your model.
What breaks after the join
The join itself is usually the easy part. What causes rework later is treating the vector as a black box all the way through the pipeline and then not being able to explain a flagged outlier to an underwriter who asks why a specific site scored the way it did. Keep the vendor's delivery notes (resolution, capture date, imagery source) attached at the vintage level, even if they never touch the model. You'll want them the first time someone asks why two deliveries for the same site look different.
A vendor that delivers a vector per site keyed to the list you already maintain, rather than a bespoke attribute feed, removes most of the custom pipeline work: no schema renegotiation every time they add a feature, no manual tagging step to validate. The join logic above is close to all the integration work left on your side.
If you're scoping an imagery feature for next year's renewal cycle, it's worth seeing what the delivery file actually looks like before you build around it.