38 TB in the lakehouse: slow Presto joins, unmasked loyalty PII, and a vector search proposal
A grocery retailer's data platform team has landed 38 TB of point-of-sale, loyalty, and supply-chain data in watsonx.data. Raw files sit in an S3-compatible bucket, curated tables are Iceberg, nightly Spark transformations build aggregates, and 60 BI dashboards are served through Presto. Two problems have surfaced. First, dashboard queries that join five Iceberg tables now take over two minutes, and analysts have started exporting extracts to local laptops, which is exactly what the platform was meant to prevent. Second, the privacy office found that the loyalty tables include member email and phone columns visible to every analyst with query access. Meanwhile the AI team wants semantic search over 900,000 product descriptions and is proposing to load the text into a relational table and match with SQL LIKE. The lead has one quarter to fix performance, enforce column-level protection, and prove lineage for the loyalty dataset before an external audit.
1. How would you diagnose and fix the Presto dashboard latency, and which workload belongs on which engine?
Read the worked answer
Match the engine to the access pattern before tuning anything. Presto is the interactive and BI engine; Spark is the batch transformation engine; Db2-style warehouse engines suit mixed or high-concurrency SQL; Milvus handles vector similarity. The 60 dashboards are interactive, so they belong on Presto - but the nightly Spark job and the dashboards should not be competing for the same undersized resources, so the first check is whether the two workloads overlap in time on one cluster.
For the five-table join, work through the usual suspects in order:
- File layout. Iceberg tables written by many small Spark tasks produce thousands of small files; compaction to roughly 128–256 MB targets cuts planning and read overhead dramatically.
- Partitioning and pruning. Partition by date at the granularity dashboards filter on, and confirm predicates are actually pushing down so partitions are skipped rather than scanned.
- Statistics. Missing table and column statistics produce bad join order and broadcast decisions; collect them after compaction.
- Query shape. Dashboards that select dozens of columns across five tables are doing work the semantic layer should pre-aggregate.
The durable fix is a curated aggregate table rebuilt by Spark and queried cheaply by Presto. The laptop exports are a symptom: analysts route around a platform that cannot answer in their patience window. Fix latency and give them a governed, fast query surface, and the exports mostly stop.
2. How do you enforce column-level protection on the loyalty email and phone columns, and why is a warning not enough?
Read the worked answer
A warning is a policy without an enforcement point, so treat this as an access-control problem. The target state is that only a small loader role can read the base table's sensitive columns; everyone else queries a surface where those columns are masked or absent.
Practical layers, in order of preference:
- Governed views. Create views that drop or hash email and phone, or expose a masked form (domain-only email, last four digits of phone), and grant analysts access to the views rather than to the base table. This works on any engine and is easy to audit.
- Catalog data policies. Where the deployment includes catalog-integrated data policies, define column masks and row filters once and have the engine apply them at query time, so the rule travels with the asset rather than with each view. Confirm the engine version actually enforces them - never assume.
- Role hygiene. Separate loader, transformer, and consumer roles; revoke default broad grants that accumulated over time.
- Audit. Enable and retain query logs so you can show who read which columns.
For the audit itself, you also need lineage for the loyalty dataset: register it as a governed asset, capture its upstream raw files and downstream aggregates and dashboards, and record the transformations. Lineage is what lets you answer 'where did this field come from and who can see it' without a week of interviews.
3. The AI team proposes SQL LIKE for semantic product search. What is the correct architecture in watsonx.data, and what would you validate before committing?
Read the worked answer
SQL LIKE matches characters, not meaning; it cannot map 'fizzy low-sugar drink' to a description that never uses those words, and at 900,000 rows the scan cost grows while recall stays poor. Reject the proposal and use the vector store.
Architecture:
- Keep product text and attributes in Iceberg as the system of record.
- Generate embeddings with an embedding model, using the same model and preprocessing for indexing and for queries - mismatched embedding models are a silent quality killer.
- Store vectors in a Milvus collection keyed by product ID, with scalar metadata (category, brand, region, active flag) so queries can filter as well as search.
- Choose an index and metric appropriate to the model's training objective (cosine or inner product for most sentence embedding models) and tune the recall/latency trade-off on real traffic.
- Join vector hits back to Iceberg for full product details, and rebuild or incrementally sync the collection when products change.
Validate before committing: build a labelled query set of maybe 200 realistic shopper phrases with known relevant products, and measure recall@10 against a keyword baseline. That number, not a demo, is the go/no-go. Also test multilingual queries if the catalogue contains them, and test behaviour on products that were removed - stale vectors returning discontinued items is a common complaint after launch.