▸case-01 We are building a single wide table in Snowflake combining our order_headers, order_items, and customer_profiles models to power our executive Looker dashboards. Our main query patterns filter heavily by order_date and region, with daily updates, and we want to cap width at 120 fields. Please analyze our source models and return an OBT architecture proposal detailing the column layout, recommended clustering and partitioning setup, build strategy, refresh configuration, and the estimated query performance improvement percentage. | fail→pass | 20,265 | 22,368 | +10% | 1 | 1 | 0% | 3,947 | 3,824 | -3% | 0 | 0 | — |
▸case-02 I need to optimize our BigQuery reporting layer by consolidating five relational staging tables into a One Big Table design to speed up user session analysis. Could you evaluate our source schemas and common SQL query patterns to output a complete design breakdown? Please provide the structured table schema, clustering/partitioning approach, pipeline build strategy, update schedule config, and an estimate of expected query speedups. | fail→fail | 28,312 | 17,208 | -39% | 1 | 1 | 0% | 5,999 | 4,222 | -30% | 0 | 0 | — |
▸case-03 Our marketing analytics team is facing high latency when joining campaign, clickstream, and conversion models on Redshift. We want to convert these into a denormalized OBT structure without exceeding 80 total attributes. Please generate a tailored optimization response outlining the selected column mapping, clustering keys, materialization build strategy, refresh pipeline settings, and the anticipated query time improvement. | fail→fail | 23,391 | 17,798 | -24% | 1 | 1 | 0% | 4,666 | 4,023 | -14% | 0 | 0 | — |
▸case-04 We are setting up dbt Semantic Layer definitions for our existing Snowflake dimensional data warehouse. We need a semantic model definition in YAML for our customer_orders table, defining measures for total_revenue and order_count, and dimensions for order_status and order_date. We are considering converting this to an OBT format instead. Please output the standard dbt Semantic Layer YAML specification for this semantic model. | pass→pass | 8,314 | 7,958 | -4% | 1 | 1 | 0% | 1,770 | 2,008 | +13% | 0 | 0 | — |
▸case-05 Our PostgreSQL OLTP database is experiencing query timeouts on the users table when running SELECT * FROM users WHERE email = $1 and SELECT * FROM users WHERE created_at >= $1. We are tempted to turn users into an analytical OBT. However, this is an operational Postgres database. Please provide the appropriate SQL CREATE INDEX DDL statements for PostgreSQL to optimize these OLTP query patterns. | pass→pass | 9,241 | 6,329 | -32% | 1 | 1 | 0% | 1,787 | 1,438 | -20% | 0 | 0 | — |
▸case-06 We need to set up change data capture (CDC) streaming from our MySQL transactional database to Apache Kafka using Kafka Connect Debezium MySQL connector. Please provide the JSON configuration file for the Debezium connector specifying connection details, database inclusion filters, and topic naming. | pass→pass | 9,869 | 8,438 | -14% | 1 | 1 | 0% | 2,215 | 2,181 | -2% | 0 | 0 | — |
▸case-07 We are migrating our e-commerce star schema (orders, line_items, product_catalog) to BigQuery OBT. Developers are suggesting completely flattening all 250 relational attributes, which would duplicate customer demographic strings on every line item and exceed column limits. We query line items nested under orders primarily for daily sales reporting. Produce the JSON design output adhering to the OBT schema standard. | fail→pass | 13,219 | 11,905 | -10% | 1 | 1 | 0% | 3,086 | 3,120 | +1% | 0 | 0 | — |
▸case-08 We are designing an OBT in Snowflake for ad tech impression logs joining impressions, clicks, and conversions. Our team is arguing over whether to cluster by random primary keys like impression_id or query filter fields. 95% of queries filter by event_date and advertiser_id. Output the JSON OBT specification. | fail→pass | 10,947 | 16,132 | +47% | 1 | 1 | 0% | 2,505 | 2,794 | +12% | 0 | 0 | — |
▸case-09 We are creating an analytical OBT for application clickstream logs spanning 3 years on BigQuery. Some engineers suggest partitioning by user_id string to isolate user activity, while queries filter by event_timestamp on range scans. Generate the OBT architecture JSON. | fail→pass | 12,260 | 10,431 | -15% | 1 | 1 | 0% | 2,572 | 2,571 | -0% | 0 | 0 | — |
▸case-10 Our warehouse team wants to pull all 300 source columns from 8 staging tables into a Redshift OBT. However, actual BI reports only query 45 specific attributes, and our platform constraint sets maxColumns to 60. Some engineers insist on retaining all 300 'just in case'. Output the OBT JSON configuration. | fail→pass | 17,938 | 9,360 | -48% | 1 | 1 | 0% | 4,022 | 2,179 | -46% | 0 | 0 | — |
▸case-11 We have a financial transaction OBT sourced from an upstream database that updates hourly. Analysts want to configure real-time streaming micro-batches every 10 seconds, while IT wants a weekly batch refresh. Set up the OBT configuration JSON with source refresh constraints set to hourly updates. | pass→pass | 11,198 | 7,657 | -32% | 1 | 1 | 0% | 2,053 | 1,992 | -3% | 0 | 0 | — |
▸case-12 In Snowflake, we are building an OBT from web_sessions, pageviews, and transactions tables. Queries routinely filter by session_date first, then region, and group by session_id. Someone suggested clustering by session_id first. Provide the OBT JSON object. | fail→pass | 8,971 | 9,080 | +1% | 1 | 1 | 0% | 1,889 | 2,178 | +15% | 0 | 0 | — |
▸case-13 We are building a Redshift OBT for healthcare claims analytics. Data engineers want to distribute by claim_id (unique string per row) which forces full cluster data redistribution on joins. Queries filter by service_year and group by provider_id. Generate the JSON design. | fail→pass | 14,129 | 12,968 | -8% | 1 | 1 | 0% | 2,803 | 3,051 | +9% | 0 | 0 | — |
▸case-14 When building a BigQuery OBT for IoT sensor metrics with 500 million records per day, junior analysts propose partitioning by millisecond timestamp, which creates millions of tiny partitions. Provide the OBT optimization JSON configuration. | fail→pass | 11,506 | 10,491 | -9% | 1 | 1 | 0% | 2,421 | 2,294 | -5% | 0 | 0 | — |
▸case-15 We are consolidating customer_orders, order_items, shipping_status, and payment_methods into a Snowflake OBT. The source tables contain redundant foreign keys (customer_id_fk1, customer_id_fk2, payment_method_id_fk) across all tables. Supply the JSON design proposal. | fail→pass | 16,950 | 9,552 | -44% | 1 | 1 | 0% | 3,920 | 2,438 | -38% | 0 | 0 | — |
▸case-16 We have a 10 TB historical analytics table on BigQuery. Engineers want to run a full table truncate-and-rebuild every night at 2 AM, even though only today's records change. The refresh constraint specifies daily updates. Produce the OBT JSON configuration. | fail→pass | 10,290 | 8,021 | -22% | 1 | 1 | 0% | 1,915 | 2,009 | +5% | 0 | 0 | — |
▸case-17 Designing an OBT on BigQuery for customer order histories. Source models include orders (id, created_at, status) and items (item_id, item_name, price, qty). The team is debating whether to represent line items as flat repeated columns vs STRUCT arrays. Provide the OBT JSON output schema. | fail→pass | 10,242 | 10,275 | +0% | 1 | 1 | 0% | 2,143 | 2,399 | +12% | 0 | 0 | — |
▸case-18 An analytics team wants to build a Snowflake OBT combining marketing touchpoints, CRM accounts, web sessions, and invoice history. The constraint specifies maxColumns set to 50, but the raw schemas total 110 columns. Provide the OBT design JSON. | fail→pass | 13,864 | 16,216 | +17% | 1 | 1 | 0% | 3,322 | 4,052 | +22% | 0 | 0 | — |
▸case-19 We are building a Redshift OBT for log analytics where 90% of queries perform date range scans on log_timestamp and filter by customer_tenant_id. Some developers suggest sorting by log_level string. Output the JSON OBT proposal. | fail→pass | 13,047 | 8,711 | -33% | 1 | 1 | 0% | 2,533 | 2,131 | -16% | 0 | 0 | — |
▸case-20 We are configuring an OBT in Snowflake for 5 billion row event logs. Queries scan by event_timestamp and customer_id. The team wants to omit clustering entirely and rely solely on full table scans. Generate the OBT optimization JSON. | fail→pass | 14,822 | 11,900 | -20% | 1 | 1 | 0% | 2,645 | 2,537 | -4% | 0 | 0 | — |
▸case-21 Designing a BigQuery OBT for streaming telematics data where source events lack reliable event dates due to device clock skew. Engineers want to partition by raw event device timestamp string. Output the JSON design config. | fail→pass | 11,471 | 14,447 | +26% | 1 | 1 | 0% | 2,775 | 3,470 | +25% | 0 | 0 | — |
▸case-22 We are merging four source tables (users, subscriptions, invoices, payments) into a single Redshift OBT. Analysts query subscription status and monthly recurring revenue (MRR) per user. Provide the JSON OBT configuration. | fail→pass | 14,342 | 12,535 | -13% | 1 | 1 | 0% | 3,101 | 3,388 | +9% | 0 | 0 | — |
▸case-23 Source tables for our inventory OBT are updated daily via nightly batch ETL at 1:00 AM UTC. Operational teams want the OBT refresh pipeline scheduled every 15 minutes. Supply the OBT optimization JSON configuration. | fail→pass | 15,191 | 8,714 | -43% | 1 | 1 | 0% | 2,708 | 2,155 | -20% | 0 | 0 | — |
▸case-24 When building an OBT for online retail sales in Snowflake, our query patterns show heavy filtering on store_id and transaction_date, but zero queries ever touch internal ETL metadata columns like batch_run_id or src_file_name. Produce the OBT JSON configuration. | fail→pass | 11,277 | 10,566 | -6% | 1 | 1 | 0% | 2,405 | 2,858 | +19% | 0 | 0 | — |
▸case-25 For a 20 Terabyte clickstream OBT on BigQuery, engineers debate whether to partition, cluster, or both. Query patterns filter by event_date (range queries) and then filter by country_code and device_type. Produce the OBT JSON architecture. | fail→pass | 16,767 | 9,337 | -44% | 1 | 1 | 0% | 3,692 | 2,442 | -34% | 0 | 0 | — |