Retail Month-End: One Presto Engine for BI and ETL
Northwind Retail has migrated 18 TB of order, inventory, and clickstream data from an on-premises Hive cluster to IBM Cloud Object Storage and catalogs it in watsonx.data. Forty-five analysts use Presto for dashboards, and a nightly Spark job loads 2 TB and must finish by 06:00. The team provisioned one Presto engine with one coordinator and two workers, then ran both BI and ETL on it. At month-end, 60 dashboards refresh concurrently; users report queue times over eight minutes, and the ETL job misses its SLA three nights a week. Spark writes thousands of roughly 1 MB Parquet files per partition, and the Hive metastore already holds 80,000 partitions. Object-storage request charges have doubled. The CIO wants a target architecture that separates workloads, controls cost, and preserves a single governed copy of the data. The team can add engines and change table layout but cannot move the data out of the current IBM Cloud region.
1. Recommend a target engine topology and justify it using the concurrency and SLA numbers. What should remain shared?
Read the worked answer
Topology: separate compute by workload. Keep one governed Iceberg catalog and one COS bucket, but create a BI Presto engine and a separate engine for ETL and compaction (Spark or a second Presto engine). In watsonx.data, engines attach to catalogs; sharing the catalog does not force sharing compute. Reasoning: 60 concurrent dashboards create many short, memory-heavy queries, while a 2 TB nightly load creates long, I/O-heavy scans and writes. On one two-worker engine, ETL saturates workers and the BI queue grows. Adding a coordinator does not fix worker contention. Separate engines isolate queueing and failure domains and let you size each against its own SLA. Sizing: benchmark 60 concurrent dashboard queries against 6-10 workers; use query queueing or resource groups to cap runaway queries. Do not size for 60 workers by headcount alone. Give Spark the ETL and compaction work. Shared: catalog, storage, IAM groups, and audit. Trade-off: more engines cost more, but the current COS request and missed-SLA cost is higher. Start with a pilot and scale workers based on observed queue time.
2. They want to keep the existing Hive metastore and store Iceberg tables. What catalog and table-layout checks would you require before go-live?
Read the worked answer
Catalog: confirm the Hive metastore version and Iceberg support in the deployed watsonx.data release. 80,000 partitions is a metadata-scale signal: test create, rename, and query-planning time under load. If metastore operations dominate, evaluate whether the service version supports an Iceberg REST catalog. Layout: partition by a typed business date, not a string, and avoid over-partitioning. Target 128-256 MB data files; run compaction on the partitions Spark just wrote. Expire old snapshots and manifests so planning does not degrade. Collect table and column statistics where supported. Storage: keep the COS bucket in the same region as the engines, review lifecycle rules, and measure request charges before and after compaction. Go-live gate: a query that filters on the partition column must scan only matching partitions in EXPLAIN output, and the plan time for the largest table must stay within the dashboard SLA.
3. Name three metrics you would monitor to prove the new topology is meeting the SLA, and explain what each tells you.
Read the worked answer
- Presto queue time and p95 query latency - separates waiting from execution. If p95 is high but queue time is low, tune the query or table layout; if queue time is high, add workers or tighten resource limits. 2. Spark job duration and stage skew - shows whether the 2 TB load meets the 06:00 SLA and whether a few partitions dominate. Track bytes read, shuffle, and task duration. 3. File count and average file size per partition - detects small-file regression after ingestion. Pair this with COS request rate and cost so compaction is tied to spend. Add failed-query rate and authentication failures for reliability and security. Review weekly and after every month-end peak; a metric that is not tied to a decision is noise.