Building Production Data Agents with Long-Running Context and Iterative Workflows

hex · Tech · 2026 · source Agentic: yes · promotional bias: low · source-backed · extracted with prompt 0.5.5 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Production data analytics agents

Hex needed to move beyond single-shot text-to-SQL toward long-running data agents that can work across notebooks, threads, semantic models, warehouse context, and user feedback while remaining verifiable enough for production analytics.

Constraints:

• Data analysis is iterative, so a one-question-one-answer workflow misses follow-up decisions and exploratory pivots.
• Agents need broad notebook capabilities, but exposing every product operation creates a very large tool surface.
• Context comes from many levels, including warehouse data, semantic models, artifacts, conversations, admin guidance, and user memory, which can conflict.
• Analytics correctness is harder to validate than code because intermediate decisions and metric definitions may be ambiguous.
• Long-running agents need durable orchestration, compaction, iteration ceilings, and UX choices that do not overwhelm business users.

Architecture

Hex built a family of data agents around a durable workflow runner and shared capability bundles. Notebook, Threads, semantic-modeling, and context agents increasingly share tools, prompts, skills/guides, and static context while preserving different user experiences. A Temporal-backed orchestration layer runs the LLM/tool loop, a context-harvesting pipeline supplies dynamic and static workspace context, tool search and consolidation reduce the tool surface, and observability/evaluation systems feed mistakes back into Context Studio and long-horizon benchmarks.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  notebook-agent-surface["Notebook agent surface"] --> capability-bundle-registry["Capability bundle registry"]
  threads-conversational-surface["Threads conversational surface"] --> capability-bundle-registry
  semantic-modeling-agent["Semantic modeling agent"] --> capability-bundle-registry
  capability-bundle-registry --> temporal-workflow-orchestrator["Temporal workflow orchestrator"]
  temporal-workflow-orchestrator --> agent-loop["Agent loop"]
  agent-loop --> context-harvesting-pipeline["Context harvesting pipeline"]
  context-harvesting-pipeline --> semantic-models-and-guides["Semantic models and guides"]
  context-harvesting-pipeline --> warehouse["Warehouse"]
  context-agent["Context agent"] --> context-harvesting-pipeline
  agent-loop --> tool-search-layer["Tool search layer"]
  tool-search-layer --> tool-catalog["Tool catalog"]
  agent-loop --> scratchpad-sql-runner["Scratchpad SQL runner"]
  scratchpad-sql-runner --> warehouse
  agent-loop --> notebook-artifact-writer["Notebook artifact writer"]
  notebook-artifact-writer --> interactive-artifact-renderer["Interactive artifact renderer"]
  threads-conversational-surface --> interactive-artifact-renderer
  agent-loop --> llm-judge-monitors["LLM judge monitors"]
  llm-judge-monitors --> context-studio["Context Studio"]
  context-studio --> semantic-models-and-guides
  context-studio --> context-agent
  temporal-workflow-orchestrator --> internal-observability-and-eval-system["Internal observability and eval system"]
  internal-observability-and-eval-system --> long-horizon-benchmark["Long-horizon benchmark"]
  long-horizon-benchmark --> warehouse
  long-horizon-benchmark --> agent-loop

Components

Data flow

A user asks a question in a notebook, Threads, or semantic-modeling surface. The selected agent capability bundle provides prompts, tools, static context, and final-turn behavior. The workflow runner launches a long-running LLM loop, harvests dynamic context from Hex artifacts, semantic models, guides, conversations, and warehouse metadata, retrieves or narrows the relevant tools, and optionally runs scratchpad SQL probes before writing visible notebook cells or hidden Threads artifacts. Generated answers and traces flow into observability and Context Studio, where LLM judges and admins identify issues, improve guides/semantic models, and feed the context graph used by future agent runs. Separate eval infrastructure tests harness changes and long-horizon learning behavior against synthetic but realistic warehouse scenarios.

Why this architecture

Hex chose a long-running, context-heavy architecture because data analytics requires iterative exploration, workspace history, and governed definitions rather than a single prompt-to-SQL answer. The architecture keeps visible artifacts for technical users, hides code for self-serve users, allows common capabilities to converge across surfaces, and keeps custom orchestration/evaluation control while models and provider APIs change quickly.

Models used: GPT-3.5 Turbo · Claude Sonnet 4.6 · Opus 4.6 · GPT 5.4 · LLM-as-judge models

Scale

• The early production AI feature ran on GPT-3.5 Turbo.
  “This is running on like GPT-3.5 turbo or something and we were like oh my god.”transcript, stated
• The old artifact-ID workaround broke down around notebooks with more than 50 or 60 cells.
  “if you had a notebook with like more than 50 or 60 cells, you'd start to get crazy behavior.”transcript, stated
• The raw tool surface reached roughly 100,000 tokens of tool definitions.
  “Not quite 100,000 but something like that tokens worth of tools in the tool kit, which is too much to be clear.”transcript, stated
• Some modern-agent runs could spend around 50 hidden SQL probes before producing visible work.
  “It might run like 50 ephemeral SQL queries just to be really sure before it actually starts doing any real work.”transcript, stated
• Hard eval sets are kept small, around 30 to 50 cases, then repeated many times.
  “Our eval sets are like 30 to 50 for these very very difficult cases. But again, we we run multiple repetitions on them.”transcript, stated
• The long-horizon benchmark simulates 90 days of warehouse changes, tickets, and agent knowledge work.
  “I've built a 90-day simulation during which the clock ticks, and every time the day turns over DBT models run and alter the warehouse”transcript, stated
• On the 90-day simulation, Sonnet 4.6 was reported at 24% on day 90 and about 4% on day zero.
  “Sonnet 4.6 gets 24% on day 90.”transcript, stated

  “What does it get on day zero? >> Like 4%.”transcript, stated

Alternatives considered (roads not taken)

Continue single-shot text-to-SQL and text-to-answer generation

Rejected because: The team found that data analytics is iterative and the earlier single-shot workflow was stalling out.

Keep separate notebook, Threads, and semantic-modeling agents

Rejected because: Users expected similar tools and context access across surfaces, so Hex began unifying tools, prompts, and skills into capability bundles.

Adopt an off-the-shelf agent orchestrator

Rejected because: Hex prioritized moving quickly on its own terms despite the maintenance cost, then migrated to a Temporal-based long-running workflow architecture.

Expose generic SQL/Python execution instead of specific tools

Rejected because: Specific tools let Hex shape behavior around when to probe, when to write visible artifacts, and how to avoid agents disappearing into hidden work.

Use very large public-style benchmark sets as the main eval strategy

Rejected because: Hex favored smaller, actively maintained eval sets that encode known failure traps and support repeated runs.

Move wholesale to provider SDKs or stateful server-side tool calling

Rejected because: The team saw value in provider features but did not have clear quantitative proof that giving up control for in-distribution behavior was worth it.

Patterns

durable-execution (orchestration)

Hex migrated from a single-shot bulk-queue flow to a Temporal-based workflow substrate so notebook and data agents could run long, multi-step tasks with proper orchestration.

tool-use-orchestration (agent-architecture)

The agents expose notebook, chart, SQL, Python, and product operations through tools, then use tool search and tool consolidation to manage the large tool surface.

rag-retrieval (context)

Hex treats workspace artifacts, threads, semantic models, guides, and admin context as retrievable context that agents harvest during operation.

agent-memory (context)

Hex separates user-level memory from data-team-governed context and treats memory cautiously because wrong user feedback can conflict with semantic models and guides.

human-in-the-loop-gate (governance)

Context Studio turns suspected agent failures into admin-visible feedback so the data team can improve semantic models, guides, and context before future runs.

llm-as-judge (evaluation)

Hex uses separate LLM judges to detect possible wrong answers, confusion, warnings, and conflicts with semantic models for admin review.

eval-driven-development (evaluation)

The team uses handcrafted eval sets, internal usage-derived scenarios, and a 90-day simulation to judge harness changes, model upgrades, and long-horizon agent learning.

context-compaction (context)

Hex evaluates long-context model features and compacts earlier than the full window when longer contexts start degrading model behavior.

observability-tracing (operations)

Hex built internal observability and experimentation tooling, then began converging it with admin-facing Context Studio signals.

Tradeoffs

Switch from single-shot AI cells to long-running agents.

Chose Agents that operate across a project and take multiple steps over Single-shot text-to-SQL/text-to-answer generation scoped to individual cells.

Rationale: Data analytics requires follow-up, pivots, and investigation, so the broader agent workflow better matches the domain even though it is harder to evaluate.

Build and maintain custom orchestration.

Chose A custom, Temporal-based long-running orchestration stack over Fully adopting off-the-shelf orchestrator technology.

Rationale: The team wanted control and speed in a fast-moving model/product environment, accepting significant maintenance overhead.

Use hidden scratchpad SQL probes for investigation.

Chose Small ephemeral SQL queries before visible notebook output over Writing every investigative query as a visible notebook cell.

Rationale: Private probes help the agent inspect tables and produce a better first visible query, but require behavioral controls so users still get proof.

Keep specific product tools despite pressure toward generic code execution.

Chose Specific tools with behavioral guidance over Only giving the agent generic SQL/Python execution.

Rationale: Specific tools let the team constrain when an agent should probe, write visible artifacts, or take product actions.

Unify multiple agent capability sets.

Chose Shared tools, prompts, skills, and context bundles across agent surfaces over Separate notebook, Threads, and semantic-modeling agent stacks.

Rationale: Users expected similar capabilities across similar surfaces, and unification reduces capability gaps between interfaces.

Treat semantic models and admin context as the stronger correctness layer.

Chose Governed context, semantic models, and admin feedback loops over Relying mainly on visible citations or raw reasoning traces for data correctness.

Rationale: For data work, correctness depends on metric definitions and governed context more than on merely showing the SQL path.

Measure long-horizon compounding instead of only day-zero answers.

Chose A 90-day simulation with evolving data, tickets, and proactive knowledge work over Only static one-shot benchmark questions.

Rationale: Hex believes the product should improve as it accumulates context, so evaluation must test whether the agent compounds knowledge over time.

Adopt provider model features selectively.

Chose A la carte use of long context, compaction, and other provider features over Wholesale migration to provider SDKs/server-side execution.

Rationale: The team wants the benefits of new provider features but does not yet see enough measured upside to surrender custom-harness control.

Gotchas (what bit them)

Single-shot data generation fails on iterative analytical work.

Root cause: Data analysis has branching decisions, follow-up questions, and ambiguous correctness checks rather than a simple pass/fail artifact.

Resolution: Hex moved toward agents that can operate across the whole notebook/project and continue through multi-step exploration.

Harness workarounds become tech debt as models improve.

Root cause: Systems built to compensate for earlier model limits can become unnecessary but hard to remove once embedded in the agent platform.

Resolution: Run evals against old compensating systems and remove them when current models no longer need them.

Tool surfaces can explode into too much prompt/tool context.

Root cause: A flexible product encourages many small tools, and each tool may carry repeated instructions.

Resolution: Consolidate normalized tool families and add tool search/retrieval so the agent sees fewer tools at once.

Hidden scratchpad tools can make agents produce answers without visible proof.

Root cause: Modern models may use hidden SQL/Python probes extensively and then skip the user-visible artifact the user expected.

Resolution: Keep specific tools and behavioral guidance for when to probe privately versus when to write visible cells or charts.

Conflicting context can cause long reasoning collapses.

Root cause: Models struggle with contradictory guidance across user memory, semantic models, admin guides, and warehouse context.

Resolution: Keep memory separate from governed context, use admin-managed semantic models/guides, and test contradictory-context behavior explicitly.

Agent observability cannot simply expose raw traces for production customer data.

Root cause: Internal teams can inspect development traces deeply, but production user data and admin workflows require privacy-aware summaries and higher-level issue clusters.

Resolution: Use LLM judges and clustered insights to surface failure types while keeping the feedback loop higher level and privacy-aware.

Public evals can measure narrow retrieval quirks instead of useful analytical reasoning.

Root cause: Many benchmarks encode repeated variants of a small gotcha or bad ground truth rather than representative exploratory data work.

Resolution: Maintain smaller, actively worked evals with known failure traps and realistic internal usage scenarios.

Provider-specific agent SDKs may trade control for uncertain in-distribution gains.

Root cause: Model providers ship useful proprietary execution, compaction, and tool-calling features, but custom product tools may still move the system out of the provider's trained distribution.

Resolution: Adopt provider features a la carte where evals show value while retaining the custom harness until the quantitative benefit is clear.

Evaluation

Hex combines internal observability, LLM-as-judge post-action checks, actively maintained eval sets, model/harness comparison runs, and a 90-day simulation benchmark. The evaluation philosophy emphasizes whether changes have desired and undesired effects, whether agents handle realistic messy warehouses, and whether context accumulation improves long-horizon performance.

Metrics: Hard eval set pass rates across repeated runs · Desired-effects and undesired-effects checks for product changes · LLM judge flags for wrong answers, confusion, semantic-model conflicts, and warnings · Clustered production failure categories and shifts over time · Day-zero versus day-90 benchmark accuracy · Latency/quality tradeoffs under model effort settings · Long-context and compaction behavior under different context-window sizes

Outcomes

• Internal release of the notebook agent produced an immediate enthusiastic reaction from users.
• Temporal-backed long-running orchestration enabled Hex to build additional agents beyond the notebook agent.
• Tool search reduced pressure from a very large tool surface.
• A context-studio feedback loop gives admins a path to improve future agent context after failures.
• The 90-day simulation produced a measurable long-horizon benchmark where Sonnet 4.6 reached 24% on day 90.

The transcript describes an agent platform operating across notebooks, Threads, semantic modeling, context synthesis, observability, and evaluation. It includes concrete scale markers such as a roughly 100,000-token tool surface, notebooks with more than 50 or 60 cells, hidden probes that may reach 50 ephemeral SQL queries, eval sets of 30 to 50 difficult cases, and a 90-day benchmark.