The ticket said “the contract value in the dashboard is wrong.” It wasn’t. The number in the warehouse table was right for the version of the PDF that had been extracted eleven months earlier. Someone had uploaded an amended contract to the same S3 prefix, the extraction job had picked it up, written a new row, and nothing downstream knew which of the two rows was current. Both were true. Only one was correct.
That is what combining structured and unstructured data on AWS actually looks like when it goes wrong. Not a service outage. Not a permissions error. A quiet correctness bug that survives every test you wrote, because every individual component did exactly what it was told.
The storage side of this problem is solved. S3 holds the PDFs, Iceberg tables hold the rows, Bedrock Data Automation pulls fields out of the documents, S3 Vectors or OpenSearch holds the embeddings. None of that is hard any more. The hard part is the join, and the join is where nearly all the failures live.
What this post covers
Five failure families I keep running into on mixed-data architectures, why each one is invisible until it isn’t, and a working shape that avoids most of them. Then troubleshooting, mistakes, and the questions people actually ask before they build this.
Not a service tour. AWS documentation already covers what Glue, Athena, Lake Formation and Bedrock do. What it covers badly is what happens at the seams between them.
Failure family one: there is no join key, only a filename
Structured data arrives with identity built in. A row has a primary key, a foreign key, a surrogate key from the CDC pipeline. Unstructured data arrives with an object key, which is a path, which is a decision someone made about folder layout six months ago.
Object keys are not identity. They are location. The difference matters the moment anyone overwrites an object, moves a prefix, or uploads the same document twice under two names. Your join silently changes meaning and nothing raises an error, because to Athena a string is a string.
What to use instead
Mint a document ID at ingestion, before extraction, and carry it everywhere. Store it three places: as S3 object metadata on the source object, as a column on every extraction output row, and as vector metadata on every chunk you embed. That ID is the only thing that ties the three planes together.
Version the object too. S3 versioning gives you a version ID per object, and a document ID plus a version ID is a genuinely stable identity for “this exact bytes-on-disk artefact.” If you skip versioning on a bucket that feeds extraction, an overwrite destroys the evidence for every downstream row derived from it. You will be asked to reproduce that extraction eventually. Auditors, disputes, model retraining, all three.
S3 Metadata is worth knowing about here. It maintains queryable Apache Iceberg tables describing the objects in a general purpose bucket, so you can join object-level facts against your business tables in Athena without writing an inventory crawler. Two limits shape how you use it: metadata tables live in an AWS managed table bucket rather than one you own, and the journal table only records change events that happen after you create the configuration. Enable it on day one or you inherit a permanent blind spot for everything already in the bucket. The inventory table covers the existing objects, which is why you want both.
Failure family two: extraction confidence gets flattened into a fact
Bedrock Data Automation, Textract, and any model-based extraction return confidence alongside values. Every pipeline I have reviewed does the same thing with it: reads it once, applies a threshold, writes the value into a typed column, and drops the score.
Now a decimal that the model was 62% sure about sits in the same column as one it was 99% sure about, and every dashboard, every SQL aggregate, every model feature treats them identically. The uncertainty didn’t go away. You just stopped being able to see it.
Keep the score as a column. It costs one float per field and it buys three things: the ability to filter low-confidence rows out of a report without rerunning extraction, a review queue that ranks by expected damage rather than arrival order, and a way to measure whether a blueprint change actually improved anything. Add two more columns while you are there, one for the extractor or blueprint identifier and one for the timestamp. When a field goes wrong at scale the first question is always “which extractor produced these,” and without that column the answer is a week of archaeology.
-- Upsert extraction output into an Iceberg table, keyed on document
-- identity rather than object key. Athena performs merge-on-read for
-- MERGE INTO, so the write is cheap and the read pays the cost until
-- compaction catches up.
MERGE INTO contracts_extracted AS t
USING extraction_staging AS s
ON t.document_id = s.document_id
AND t.source_version_id = s.source_version_id
WHEN MATCHED THEN UPDATE SET
contract_value = s.contract_value,
contract_value_conf = s.contract_value_conf,
extractor_id = s.extractor_id,
extracted_at = s.extracted_at
WHEN NOT MATCHED THEN INSERT (
document_id, source_version_id, contract_value,
contract_value_conf, extractor_id, extracted_at
) VALUES (
s.document_id, s.source_version_id, s.contract_value,
s.contract_value_conf, s.extractor_id, s.extracted_at
);The merge-on-read detail matters more than it looks. Athena writes delete files rather than rewriting data files, so a chatty extraction pipeline that merges every few minutes will quietly degrade query performance until compaction runs. On S3 Tables that maintenance is handled for you. On self-managed Iceberg in a general purpose bucket it is your job, and forgetting it is one of the more common reasons a lakehouse gets slower every week for no visible reason.
Failure family three: two catalogs, two permission models
This is the one that bites teams who did everything else right.
Your tables are governed by Lake Formation, with grants at table and column level. Your documents are governed by S3 bucket policies and IAM. Your vectors are governed by IAM on the vector bucket or index ARN. Three planes, three models, no shared enforcement point.
The result is predictable and unpleasant. An analyst who is correctly denied the salary column in Athena runs the same question against the RAG endpoint and gets the number read straight out of the source PDF, because nobody applied column-level thinking to the chunks. The retrieval layer had no idea it was quoting a restricted field.
There is no single AWS switch that fixes this. What works is applying the same classification at ingestion and enforcing it independently in each plane:
- Classify the document once, at ingestion, and write the classification into the sidecar metadata that the knowledge base ingests.
- Use metadata filters on retrieval so a caller only ever searches the chunks their tier permits.
- Keep restricted documents in a separate index or knowledge base where the classification is coarse and the blast radius matters.
- Redact at extraction time for anything you genuinely cannot let leak, rather than relying on a filter you might misconfigure later.
Bedrock Knowledge Bases reads per-document metadata from a sidecar JSON file that sits beside the source object and carries the same filename with .metadata.json appended. That file is where classification, tenant, document ID and effective dates belong.
{
"metadataAttributes": {
"document_id": "doc_01HX9K4RZP",
"classification": "restricted",
"tenant_id": "acme",
"effective_year": 2031
}
}Filterable metadata is what makes tenant isolation possible on a shared index. It is also the thing most likely to be wrong on the first attempt, which brings us to the next failure family.
Failure family four: index decisions you cannot take back
S3 Vectors makes vector storage cheap enough that people stop thinking about it, and then hit a wall that has nothing to do with cost. When you create a vector index you fix three things permanently: the dimension, the distance metric, and the list of non-filterable metadata keys. Changing any of them means creating a new index and re-embedding everything.
By default every metadata key you attach is filterable. Filterable metadata is subject to a tighter size budget than the total metadata per vector, so if you push a large chunk of text through as a filterable key, writes start failing with validation errors rather than degrading gracefully. Query a key you declared non-filterable and you get a 400 back, not an empty result set.
If you are pointing Bedrock Knowledge Bases at an S3 Vectors index you created yourself, declare the Bedrock-managed text and metadata keys as non-filterable at creation time. They hold chunk text and are large. Leaving them filterable is the single most common reason a knowledge base sync fails partway through with validation exceptions and no obvious cause.
# Non-filterable keys are set once, at index creation, and cannot be
# changed afterwards. Keep the list short: the cap is small, and every
# key you spend here is one you can never filter on.
aws s3vectors create-index
--vector-bucket-name my-vector-bucket
--index-name documents
--data-type float32
--dimension 1024
--distance-metric cosine
--metadata-configuration '{"nonFilterableMetadataKeys":["AMAZON_BEDROCK_TEXT","AMAZON_BEDROCK_METADATA"]}'The dimension has to match your embedding model’s output exactly, and the same model has to be used for ingestion and for query. Swapping embedding models mid-life is not a config change, it is a migration. Plan the index around the model you intend to keep, not the one that is convenient today.
Worth being even-handed here: S3 Vectors is the cheap, durable option and it is the right default for large corpora with modest query rates. If you need heavy filtering, hybrid keyword-plus-vector search, or very high query throughput, OpenSearch Serverless or a dedicated store like pgvector on Aurora will serve you better, and AWS supports tiering between them for exactly that reason. Pick on query pattern, not on storage price.
Failure family five: reprocessing rewrites history
You improve the extraction blueprint. You rerun it across the corpus. Numbers in last quarter’s board pack change.
This is the failure that ends trust in the platform, and it is entirely avoidable. Extraction output is a derived opinion about a document at a point in time. It is not a fact about the world. Model it that way.
Keep two timestamps on every extraction row: when the source document became effective, and when this extraction ran. Reports pin to the first, audits use the second. Now a reprocess adds rows rather than changing answers, and “why did this number move” has an answer you can query instead of guess at.
Iceberg gives you snapshot isolation and time travel for free on the table side, which is genuinely useful for reconciliation. It does not, on its own, tell you that the value changed because a different extractor ran. Only your columns do that.
A shape that works for combining structured and unstructured data on AWS
Nothing exotic. The ordering is what matters, because several of these decisions are hard to reverse.
- Land raw, enable versioning, enable S3 Metadata. Do this before you ingest anything you care about. Both are cheap to turn on and impossible to backfill properly.
- Mint the document ID at landing. Write it to object metadata and to the sidecar metadata file in the same step. One writer, one source of truth.
- Classify before you extract. Classification drives which extraction path runs, which index the chunks land in, and which Lake Formation grants apply downstream.
- Extract to a staging table, not straight to the curated table. Staging gives you somewhere to run validation before anything touches a table an analyst can query.
- Merge into Iceberg on document identity, carrying confidence and provenance columns. Never merge on object key.
- Embed separately, keyed by the same document ID. The vector plane and the table plane are different systems with different refresh rates. Treat them as such.
- Join at query time in Athena. Retrieval returns document IDs, SQL turns those into rows. Resist the urge to materialise one giant denormalised table containing both.
Step seven is the one people argue with. The argument for denormalising is query simplicity. The argument against is that you now have a third copy that drifts from both parents, and drift in a table nobody owns is how you get back to the ticket at the top of this post.
Troubleshooting
Athena says the principal has no privilege on the resource
This is a Lake Formation grant, not an IAM one. IAM policy alone is not enough for catalog-governed tables. Grant on the catalog and database first, then the table. For S3 Metadata tables and S3 Tables specifically, the grant needs to reach the table bucket catalog and namespace, not just the table.
Queries against metadata tables fail with a syntax error
Metadata table namespaces need to be quoted with double quotes or backticks in Athena and Redshift. It looks like a parser bug and it is not.
Knowledge base sync fails partway with validation errors
Almost always filterable metadata size. Check whether the Bedrock-managed text keys were declared non-filterable when the index was created. If they were not, you need a new index, because that setting is immutable.
Retrieval returns documents the caller should not see
Check that the filter is actually being applied at query time and that the metadata key is filterable. A filter on a non-filterable key returns an error rather than silently failing, so if you are getting results, the filter is probably absent from the request entirely.
Athena gets slower week over week on a table that isn’t growing much
Delete files accumulating from merge-on-read writes, or small file proliferation from frequent micro-batches. Check snapshot and manifest counts before you start tuning queries.
Common mistakes
- Joining on the S3 object key because it was there and it worked in the prototype.
- Enabling S3 Metadata after the bucket is full, then wondering why the journal table is missing most of the history.
- Applying a confidence threshold and discarding the score.
- Creating the vector index before deciding on the embedding model.
- Assuming Lake Formation column grants protect the same field when it is quoted out of a PDF by a retrieval endpoint.
- Reprocessing in place, with no record of which extractor produced which value.
- Turning off S3 versioning on the raw bucket to save on storage, then being asked to reproduce an extraction.
Best practices
- One ingestion writer that mints identity. Everything downstream reads that ID and never invents its own.
- Confidence and provenance columns are not optional. They are the difference between debugging and guessing.
- Treat extraction output as append-mostly and bitemporal. Effective time and extraction time, always both.
- Decide the embedding model before the index, and budget non-filterable keys deliberately. The cap is small and permanent.
- Enforce classification in all three planes independently. There is no single choke point.
- Watch table maintenance the way you watch disk. Compaction and snapshot expiry on self-managed Iceberg; a monitoring stack such as Amazon Managed Grafana or Grafana Cloud pointed at query runtime and file counts will show the trend long before an analyst complains.
- Keep a small, cheap reprocessing rig. A modest VPS from a provider like Contabo or InterServer is enough to dry-run a blueprint change against a sample corpus before you spend Bedrock invocations on the full set.
FAQ
Do I need a lakehouse to combine structured and unstructured data on AWS?
No. You need a stable join key and a place to put extraction output. Athena over Iceberg tables in S3 is enough for most workloads. The lakehouse framing matters when you have several engines and warehouses to unify, not when you have documents and one analytics database.
Should extraction output live in the same table as the structured source data?
Separate tables, joined at query time. They have different refresh cadences, different failure modes and different trust levels. Merging them into one table means a bad extraction run corrupts data that had nothing wrong with it.
Is S3 Vectors a replacement for OpenSearch?
For cost-sensitive storage of large vector sets with moderate query volume, yes. For high query throughput, rich filtering or hybrid search, OpenSearch or pgvector remains the stronger fit. Tiering between them is a supported pattern and often the honest answer.
How do I keep the vector index in sync with the documents?
Drive both from the same ingestion event. S3 event notifications into a queue, then fan out to extraction and embedding. Reconcile periodically by comparing document IDs present in the table against those present in the index, because “mostly in sync” degrades quietly.
What breaks first when this architecture is done badly?
Trust. Long before anything errors, someone finds a number that disagrees with the source document and stops using the dashboard. The technical failure is downstream of the social one.
Can I use Textract instead of Bedrock Data Automation?
Yes, and for pure OCR and form or table extraction with predictable layouts it is still a good fit. Bedrock Data Automation is aimed at mixed modalities and schema-shaped custom output through blueprints. The architectural advice here does not change either way, because the failure families are about identity and governance, not about which extractor you picked.
Does Lake Formation govern my documents as well as my tables?
It governs the catalog and the tables registered in it. Raw objects in a general purpose bucket are governed by S3 and IAM, and vectors by IAM on the vector resources. Assume three enforcement points until you have tested otherwise.
The one thing worth remembering
Combining structured and unstructured data on AWS is not a storage problem and it has not been one for a while. Every service in the path works. What fails is identity: the assumption that a file path is a key, that a confidence score is a fact, and that a rerun is idempotent.
Mint an ID at the door, carry it into every plane, keep the score and the provenance, and model extraction output as an opinion with a timestamp. Do that and the rest of the architecture is ordinary engineering. Skip it and you will spend a year explaining numbers that were never wrong, exactly.
Need help wiring documents and tables together?
This is a large part of what I do. If you are standing one of these up, or unpicking one that has drifted, I can help with:
- Designing document identity and provenance so extraction output survives reprocessing, overwrites and audits.
- Building the Glue, Athena and Iceberg side of a mixed-data pipeline, including merge strategy and table maintenance that does not rot.
- Setting up Bedrock Data Automation or Textract extraction with confidence handling and a human review path that ranks by risk.
- Getting metadata filtering and tenant isolation right on S3 Vectors, OpenSearch or Bedrock Knowledge Bases before the index is locked in.
- Reconciling Lake Formation grants against what a retrieval endpoint can actually surface, and closing the gaps.
- Reviewing an existing lakehouse for the failure families above and giving you a prioritised list rather than a rewrite.
Send me a table schema, a sample extraction output, or the query that is returning the wrong number, and I will tell you what I see.
