Why Klaviyo Unlocked Its Data Warehouse with Only 90 Tables: 6.5ms AST Verification and the 87% Dreaming Memory Architecture
Executive Summary & Key Takeaways
- Core Thesis: Enterprise initiatives connecting generative AI agents to cloud data warehouses (Snowflake, BigQuery) routinely fail due to two architectural traps: metric hallucination (different prompts yielding conflicting revenue or churn numbers) and authorization leakage (agents joining permitted tables to expose confidential executive compensation or cross-territory customer data). Klaviyo’s internal business intelligence platform (KBI) scaled from an initial 4-person team to hundreds of active monthly employees within 12 months by pairing a dbt semantic layer (defining 100+ standardized metrics) with a “Walled Garden” that exposes only ~90 vetted, documented tables—acknowledging that semantic models cover less than 2% (<2%) of enterprise warehouse assets. Furthermore, an InfoWorld benchmark on Snowflake Cortex across 900 queries proves that while traditional table-level RBAC permits up to ~80 rows of unauthorized data leakage via cross-table inference, intercepting SQL before execution to rewrite its Abstract Syntax Tree (AST) achieves exactly 0 rows of leakage with an imperceptible average latency overhead of 6.5 milliseconds. Finally, Anthropic Research demonstrates that storing schema history and failed queries via offline consolidation (“Dreaming”) outperforms vector RAG, jumping from 13% to 48% accuracy on a 350,000-file codebase and reaching 87% (vs. 73% for RAG) on legal dependency benchmarks.
- Primary Sources Verified: Klaviyo Engineering Blog (Will Tremml on scaling KBI and deterministic harness systems), InfoWorld Technical Investigation (Venkat Navneeth Burla on Safe SQL and LLM database governance), and Anthropic Research (Lance Martin et al. on The Components of Agent Memory).
- Actionable Engineering & Governance Takeaways:
- Restrict AI access to a curated “Walled Garden” of 50 to 90 certified data mart tables: do not connect agents to raw staging schemas or rely solely on semantic layers, which cover less than 2% of mature enterprise warehouses.
- Implement an AST-rewriting SQL interceptor: enforce row-level scoping (
WHEREclause injection), column-level masking (nullification), and cross-table join prohibitions programmatically in code before execution, rather than relying on natural language system prompts. - Separate online execution from offline memory compilation: utilize cascading schema disclosure in active sessions while running offline “Dreaming” batch jobs during idle hours to synthesize failed queries and schema shifts into a structured Markdown wiki.
- Limits of the Evidence: The 900-query SQL security benchmark evaluated a pharmaceutical sales and prescription schema on Snowflake Cortex; overhead and parsing behavior may vary across complex dialect-specific queries. Klaviyo’s metrics represent an internal enterprise deployment within an established e-commerce marketing platform. Anthropic’s Dreaming benchmarks were measured across the Stainless API codebase (350,000 files) and the Harvey legal reasoning evaluation (LAB).
Japanese Edition: https://consuloji.substack.com/p/klaviyo90ai65sql
1. The Enterprise Warehouse Dilemma: Metric Drift and Permission Leakage
Organizations seeking to democratize data analytics frequently connect large language models directly to enterprise data warehouses. However, production rollouts consistently stall behind two compounding failure modes:

- Metric Drift and Inconsistent Logic: When employees ask simple natural language questions—such as “What was our net churn rate last quarter?”—unconstrained agents generate ad-hoc SQL calculations. Depending on which timestamp column or subscription status filter the model selects, two different users receive conflicting figures, undermining executive trust in analytical outputs.
- Inference Leakage Under Static Permissions: Traditional database security relies on static Role-Based Access Control (RBAC), granting or denying table-level
SELECTpermissions. However, an agent possessing legitimate access to an operational sales table and an employee directory can generate queries that execute relational joins across both datasets, calculating confidential incentive compensation or exposing regional territory data that the querying employee is unauthorized to see.

Attempting to resolve these risks through prompt engineering—instructing the model in its system prompt to “only query authorized regions” and “calculate churn using official company logic”—fails because probabilistic models treat natural language instructions as soft guidance rather than hard constraints. Securing enterprise data agents requires deterministic boundaries.
2. The Klaviyo KBI Architecture: The 2% Semantic Ceiling and the 90-Table Walled Garden
At marketing automation platform Klaviyo, data engineer Will Tremml and an initial four-person team designed Klaviyo Business Intelligence (KBI) to solve this challenge. Within twelve months, KBI scaled to support hundreds of monthly active employees, replacing the majority of manual, ad-hoc Snowflake queries with autonomous agent workflows.

Klaviyo’s key structural insight was acknowledging the practical limit of semantic layers. While modern semantic frameworks (such as dbt Semantic Layer) are essential for defining canonical business metrics, building and maintaining formal semantic models across an entire enterprise warehouse is unfeasible. In mature enterprise environments containing thousands of raw, staging, and feature-store tables, formal semantic layers cover less than 2% (<2%) of all data models.
If an agent is restricted exclusively to the semantic layer, it can answer high-level corporate KPI questions (such as monthly recurring revenue or total active accounts), but it fails when stakeholders ask deeper exploratory questions—such as diagnosing conversion drops within specific customer cohorts or investigating regional delivery latencies.

To support ad-hoc exploration without risking data corruption or hallucination, Klaviyo structured KBI across three deterministic layers:
Layer 1: Canonical Metric Governance (dbt Semantic Layer)
KBI models over 100 official corporate metrics across dozens of dbt semantic definitions. When users query core KPIs, the agent references verified semantic definitions rather than generating raw calculations, ensuring 100% numerical consistency across departments.
Layer 2: The “Walled Garden” (Curated Marts)
For the 98% of analytical questions extending beyond the semantic layer, KBI blocks the agent from querying thousands of raw production tables, temporary scratchpads, or deprecated schemas. Instead, the team constructed a strictly bounded “Walled Garden” containing approximately 90 vetted, documented data mart tables. Every table in this garden adheres to strict naming conventions, documented column descriptions, and automated data freshness guarantees.
Layer 3: Cascading Disclosure and Deterministic Hooks
Stuffing 90 table definitions with hundreds of schema attributes into an agent’s initial prompt (CLAUDE.md) causes prompt bloat, increases latency, and degrades model attention. KBI implements cascading schema disclosure:
- The agent begins with an index of table summaries.
- It dynamically invokes schema-inspection tools only for relevant candidate tables when formulating a plan.
- Crucially, execution safety is enforced by deterministic
PreToolUsehooks: programmatic rules running in the harness that validate query syntax, restrict runtime parameters, and block unauthorized operations before queries touch Snowflake.
3. The 900-Query Safety Benchmark: 80-Row Leakage and 6.5ms AST Rewriting
The security vulnerabilities inherent in standard Text-to-SQL workflows were measured quantitatively in an InfoWorld investigation conducted by Venkat Navneeth Burla on Snowflake Cortex (Your LLM writes good SQL. That’s not the same as safe SQL.).

The evaluation tested an AI analytics agent deployed over a pharmaceutical sales schema containing regional prescription volumes, medical provider accounts, and representative incentive targets across 900 distinct analytical queries.
Under traditional configurations relying on static table-level RBAC:
- Even though the LLM generated syntactically perfect SQL that satisfied standard execution parsers, it frequently violated organizational security policies.
- The model generated queries that retrieved data across non-assigned sales territories, selected restricted personal identifiable information (PII) columns, and performed relational joins between prescription volumes and incentive compensation tables.
- Across the 900 test queries, standard Text-to-SQL leaked an average of up to approximately 80 rows of unauthorized data per query.
The Policy-Aware AST Interceptor
To eliminate this vulnerability without degrading agent utility, researchers deployed a policy-aware SQL interceptor between the LLM and the database engine. Using an Abstract Syntax Tree (AST) parser (such as sqlglot), the interceptor intercepts the generated SQL string and programmatically applies three deterministic modifications:
- Dynamic Row-Level Filtering: Inspects the authenticated user’s session claims (e.g.,
territory_id = 'NE-04') and injects mandatoryWHEREclauses into every table reference. - Column-Level Null Masking: Detects references to unauthorized columns and rewrites the AST to return
NULLwhile preserving query structure. - Cross-Domain Join Prohibition: Inspects the
JOINgraph within the syntax tree and aborts execution if an agent attempts to combine isolated domains (e.g., operational activity and compensation).
The benchmark results demonstrate the power of this mechanism:
- Unauthorized Data Leakage: Dropped from up to ~80 rows down to exactly 0 rows across all 900 evaluation queries.
- Latency Overhead: The entire AST parsing, rule evaluation, and SQL rewriting pipeline added an average latency of just 6.5 milliseconds (0.0065 seconds)—an overhead completely imperceptible to users waiting for warehouse queries to return.
Deterministic code, operating in single-digit milliseconds, provides absolute policy enforcement where natural language prompts consistently fail.
4. Long-Term Schema Retention: Why Offline Dreaming Outperforms Naive Vector RAG
Once the execution harness and security gates are established, engineering teams face a secondary maintenance bottleneck: how does the agent retain knowledge of evolving database schemas, business edge cases, and past SQL errors?

Standard implementations rely on naive vector Retrieval-Augmented Generation (RAG), embedding conversation histories and schema files into a vector database for semantic similarity retrieval. Anthropic Research’s study The Components of Agent Memory (Lance Martin et al.) demonstrates that this approach—termed “Search Alone”—breaks down across complex, interdependent domains. Vector search retrieves isolated text fragments based on surface keyword similarity, failing to synthesize causal relationships or resolve conflicting schema definitions.
Anthropic evaluated an alternative architecture inspired by human memory consolidation: “Dreaming.” In a Dreaming architecture, the agent does not attempt to reorganize its long-term memory during active query execution. Instead, during scheduled offline periods (such as nightly batch jobs), the system analyzes historical query logs, user corrections, and execution errors, distilling raw session telemetry into a structured Markdown wiki (comprising an index.md catalog cross-linked to specialized domain pages).
The empirical advantages of offline memory consolidation are substantial:
- Large-Scale Software Navigation (Stainless Codebase): Across a monorepo comprising approximately 350,000 files, an agent utilizing naive search alone achieved low initial task success. Under a Dreaming architecture, as historical execution traces were iteratively consolidated across 2 to 128 sessions, task accuracy climbed steadily from 13% to 48%—a ~3.7× performance increase.
- Complex Dependency Reasoning (Harvey Legal Benchmark LAB): On corporate governance evaluations requiring multi-hop dependency analysis across restructuring agreements, standard vector RAG achieved 73% accuracy. The autonomously synthesized Dreaming wiki scored 87%, outperforming search alone by 14 percentage points.

This bifurcated design establishes an optimal operational balance: during live business hours, agents operate with lean context windows, cascading schema tools, and 6.5ms AST security interceptors for high-speed execution; during offline hours, Dreaming jobs consolidate operational lessons into structured documentation.

Three Concrete Implementation Steps for Engineering Teams
- Establish a 50-to-90 Table Walled Garden: Revoke agent access to raw database schemas and staging models. Define core corporate KPIs in a formal semantic layer (such as dbt), while provisioning a dedicated, documented data mart layer of 50 to 90 certified tables for exploratory querying.
- Deploy an In-Line AST Policy Interceptor:
Embed a deterministic SQL parser (such as
sqlglot) within your agent’s query execution hook. Programmatically inject user-scoped row filters (WHEREpredicates) and column masks before queries are dispatched to the warehouse, eliminating inference-based data leaks with sub-10ms overhead. - Automate Offline Memory Consolidation: Replace raw vector database storage of chat transcripts with a scheduled nightly Dreaming pipeline. Process successful query patterns, failed executions, and user clarifications into a clean, cross-referenced Markdown knowledge repository that the agent references via cascading disclosure.
Sources
- Klaviyo Engineering Blog (Will Tremml), “How Klaviyo Scaled Analytics by Getting Out of the Way & Inside the Harness: Deterministic Systems”: https://klaviyo.tech/blog/how-klaviyo-scaled-analytics-by-getting-out-of-the-way
- InfoWorld (Venkat Navneeth Burla), “Your LLM writes good SQL. That’s not the same as safe SQL.”: https://www.infoworld.com/article/safe-sql-llm-governance
- Anthropic Research (Lance Martin et al.), “The Components of Agent Memory”: https://www.anthropic.com/research