app.dataengineerhub.blog

Blog

  • From ETL Pipelines to Data Products: Designing Reusable Data Infrastructure for Enterprise AI

    From ETL Pipelines to Data Products: Designing Reusable Data Infrastructure for Enterprise AI

    Enterprise data platforms often begin with a simple objective: move data from operational systems into a place where it can be analyzed. Over time, however, the number of data sources, consumers, and business requirements grows. A pipeline originally created for one dashboard becomes useful to another team. A transformation developed for a reporting workload is recreated for an application. An AI team builds yet another pipeline because the existing data was not structured for its requirements.

    The problem is not that organizations have too few pipelines. In many mature environments, they have too many pipelines performing overlapping work.

    This creates a different challenge for data engineering: how do we build data infrastructure that can be reused across analytics, applications, and AI without turning every new requirement into another independent pipeline?

    One answer is to move from thinking primarily about ETL pipelines toward thinking about data products.

    A data product is not simply a table in a warehouse or a dataset stored in a lake. It is a reusable data asset with defined meaning, ownership, quality expectations, metadata, lineage, and consumers. The objective is to make the data useful beyond the specific pipeline that originally produced it.

    The Problem with Use-Case-Specific Pipelines

    Traditional ETL architectures are often organized around downstream requirements. A team receives a request for a report, builds an extraction and transformation process, and produces the required dataset. Another team later needs similar information and creates another pipeline because its requirements are slightly different.

    At first, this approach is reasonable. The system is small, the requirements are clear, and the fastest solution is often to build exactly what is needed.

    The difficulty appears as the organization grows.

    Multiple pipelines may independently extract the same source data, apply similar business rules, and create slightly different versions of the same business entity. One pipeline may define an active customer differently from another. One dashboard may calculate revenue using one transformation while another application uses a different version.

    Eventually, the organization has a collection of pipelines that individually work but collectively create a difficult data environment.

    The goal of a data product approach is not to eliminate pipelines. Pipelines remain essential. The change is in what the pipeline is designed to produce.

    Instead of building a pipeline exclusively for one downstream consumer, the pipeline can contribute to a reusable data asset with clearly defined characteristics.

    Figure 1. The evolution from use-case-specific ETL pipelines toward reusable data products.

    From Pipelines to Data Products

    The distinction is subtle but important.

    A pipeline describes how data moves and changes.

    A data product describes what trusted data is made available for others to use.

    For example, an organization may have customer, order, inventory, or product information arriving from multiple operational systems. Instead of creating separate transformations for every consumer, the platform can produce a curated data product representing a well defined business concept.

    That product should answer basic questions before another team consumes it:

    • What does this data represent?
    • Who owns it?
    • How frequently is it updated?
    • What quality expectations does it have?
    • What transformations have been applied?
    • Where did the data originate?
    • Which downstream systems depend on it?
    • How should consumers interpret important fields?

    This turns the dataset from an anonymous technical output into something that other teams can confidently build upon.

    The distinction becomes particularly important when AI systems enter the architecture. AI applications need access to enterprise information, but simply exposing more raw data does not necessarily produce better results. The data must have consistent meaning, appropriate granularity, and enough context for the consuming system to use it correctly.

    Designing the Architecture for Reuse

    A reusable data architecture does not require one enormous centralized pipeline. Instead, it separates concerns while establishing clear interfaces between layers.

    A typical architecture can begin with operational databases, APIs, files, event streams, and other enterprise sources. An ingestion layer brings that information into the platform, where raw data can be preserved before transformation.

    Transformation and quality processes then produce curated datasets. The important difference is that these curated datasets are designed as reusable products rather than temporary outputs for one report.

    Figure 2. A reusable data product architecture separates ingestion, transformation, quality, governance, and consumption.

    The architecture can support multiple consumers from the same trusted data product.

    Analytics teams may use it for dashboards and reporting. Applications may consume it through APIs or services. Data scientists may use it for machine learning workflows. AI systems may use it as part of retrieval, contextualization, or decision support workflows.

    This does not mean every consumer receives exactly the same representation. Different consumers may require different interfaces or derived views. The important principle is that core business logic should not be unnecessarily duplicated.

    Data Contracts Make Reuse Possible

    Reusability becomes difficult when consumers do not know what they can rely on.

    A data contract provides an explicit agreement between data producers and consumers about the expected characteristics of a data asset. At the simplest level, this can include schema and data types. In a mature environment, the contract can go further.

    It can define expected semantics, ownership, freshness, acceptable values, compatibility expectations, and changes that require communication.

    Consider a field called status.

    From a technical perspective, a string is a perfectly valid datatype. But what does the string mean?

    Does active mean an account is currently usable? Does it mean the customer has purchased something recently? Does it mean a subscription is paid?

    Schema validation cannot answer that question.

    For reusable data products, semantic consistency is as important as structural consistency.

    A contract therefore becomes a mechanism for protecting consumers from unexpected changes while giving producers a clear responsibility for maintaining the data they publish.

    Quality Is Part of the Product

    Data quality should not be treated as a final step performed after a pipeline has been built.

    If a dataset is intended to become a reusable data product, quality is part of the product itself.

    Different products will require different checks, but common considerations include completeness, validity, uniqueness, consistency, and freshness.

    For example, a product containing transactional information might need to detect duplicate records. A product supporting operational decisions might require strict freshness expectations. A product used for historical analysis may tolerate delayed updates but require strong consistency over time.

    The important point is that quality expectations should be explicit and measurable.

    This also changes how data engineers think about failures. Instead of asking only whether a pipeline completed successfully, engineers can ask whether the resulting data product continues to meet its defined expectations.

    Metadata and Lineage Are Not Optional Extras

    When organizations have hundreds of datasets, discovering what a dataset means can become as difficult as producing it.

    Metadata helps answer questions such as where a dataset came from, what its fields represent, how frequently it changes, and who is responsible for it.

    Lineage provides another important dimension: understanding how data moved through the system and which upstream sources contributed to the final product.

    This becomes especially valuable when something changes.

    If a source field is modified, engineers should be able to determine which transformations and downstream consumers may be affected. Without lineage, that investigation can become a manual search across pipelines and documentation.

    For data products to remain reusable, discoverability and explainability need to be designed alongside the data itself.

    One Data Product, Multiple Consumers

    A major advantage of this approach is that the same trusted data foundation can support different types of workloads.

    Figure 3. A reusable data product can support analytics, applications, and AI workloads without duplicating core transformation logic.

    Consider a curated product representing a business entity such as a product, customer, transaction, or inventory position.

    An analytics team might use it to create operational dashboards. An application might use the same information to support a workflow. An AI system might use it to provide context to an agent or model.

    The consumers are different, but the underlying business definitions do not need to be reinvented each time.

    This is where the concept becomes particularly powerful for enterprise AI.

    Making Data Products Useful for AI

    AI systems introduce a new category of data consumer.

    Traditional analytical workloads often operate through structured queries and predefined metrics. AI applications may need to retrieve information dynamically, combine multiple pieces of context, interpret relationships, and use that information as part of an inference or action.

    That places additional demands on the underlying data.

    AI systems benefit from data that is:

    • semantically consistent
    • sufficiently granular
    • appropriately contextualized
    • discoverable
    • governed
    • fresh enough for the intended use case
    • accessible through reliable interfaces

    This does not mean every data product needs to be redesigned specifically for AI.

    Instead, organizations should build reusable data foundations that can support AI as one of several consumers.

    That distinction helps prevent a common architectural mistake: creating an entirely separate data ecosystem every time a new AI initiative appears.

    Avoiding the “One Pipeline Per Use Case” Trap

    The answer is not to centralize every transformation into one massive pipeline.

    Over centralization can create its own problems. A change made for one consumer can unexpectedly affect many others. Teams may also become dependent on a central group for every modification.

    A better approach is to identify which data assets and transformations are genuinely reusable.

    Common business entities and shared definitions are strong candidates for reusable products. Highly specialized analytical logic may remain closer to the consuming workload.

    The architectural question should therefore be:

    What should be shared, and what should remain specific to the consumer?

    Good data engineering is not about maximizing reuse at any cost. It is about finding the right boundaries.

    Practical Principles for Building Reusable Data Infrastructure

    Organizations beginning this transition can start with a few practical principles.

    Build once, consume many times.
    When multiple teams repeatedly implement the same business logic, investigate whether the underlying data should become a reusable product.

    Define ownership early.
    A reusable dataset without clear ownership eventually becomes nobody’s responsibility.

    Treat metadata as part of the product.
    Documentation, definitions, lineage, and discoverability are not administrative additions. They determine whether another team can actually use the data.

    Make quality measurable.
    Define expectations around freshness, completeness, validity, and other characteristics that matter to the product’s consumers.

    Design for change.
    Schemas, business rules, and upstream systems will evolve. Data products should have clear compatibility and change management practices.

    Separate shared data from consumer specific logic.
    Not every transformation needs to be centralized. Reuse the parts that represent stable, broadly useful business concepts while allowing downstream teams to build specialized views.

    Conclusion

    The evolution from ETL pipelines to data products is not about replacing one technology with another. It is a shift in how organizations think about the outputs of data engineering.

    A pipeline can successfully move data from one system to another and still create little long term value if every downstream consumer must interpret, validate, and transform that data independently.

    A data product takes a different approach. It treats trusted data as a reusable enterprise capability with defined meaning, quality expectations, ownership, metadata, and lineage.

    That approach becomes increasingly important as organizations add AI systems to their technology landscape. AI does not eliminate the need for sound data infrastructure. It increases the number of ways that trusted enterprise data can be consumed.

    The mature data platform, therefore, is not simply a collection of pipelines.

    It is an ecosystem of reliable data products that allows analytics, applications, and AI systems to build on the same trusted foundation.

  • ETL vs. ELT vs. Reverse ETL: A Practitioner’s Framework for Choosing the Right Pattern for a Given Workload

    ETL vs. ELT vs. Reverse ETL: A Practitioner’s Framework for Choosing the Right Pattern for a Given Workload

    As a data engineer, you’ve likely been in a design review where someone says at the very end, “We should just do ELT for everything!”. You are a data engineer, and you’ve probably been in a design review where you heard someone say at the end, “We should just do ELT for everything!”. Or you’ve inherited a package in an old 10-year-old version of SSIS that you would have to pick apart row-by-row in a painful manner, and the business is wondering why the “modern” warehouse team can’t simply replace it overnight. The industry has morphed ETL, ELT and now Reverse ETL into tribal identities: pick one, fight about it in slack threads, ship. But that’s backwards. The pattern is not the personality, it is a means to an engineering end.

    The reality, though, is that ETL, ELT, and Reverse ETL are not mutually exclusive approaches — they are three different kinds of pipelines that are tackling three different problems and most production data pipelines require all three to be running concurrently. Don’t identify a winner. It’s to align the pattern with the limitations of the workload: data sensitivity, complexity of transformation, latency requirements, and the most cost-efficient place to execute the workload.

    The Problem Deep Dive

    All three patterns use the same three verbs—extract, load, transform—but in different orders: that’s why it’s a bit of a muddle.

    ETL (Extract, Transform, Load): ETL is the process of moving data into a staging area, where it is transformed in some way outside of the target system before it is loaded into the clean data in the target system. This was the standard practice for decades as target systems or data warehouses, particularly transactional databases, would be costly to compute on and required to be buffered from raw and dirty data. This pattern has become the backbone of careers such as those of tools like SSIS, Informatica, and Talend.

    ELT (Extract, Load, Transform) first loads raw data into the destination, and then transforms it-while-stored using compute functions on the destination. It really only became dominant when cloud warehouses (Snowflake, BigQuery, Redshift, Azure Synapse) separated storage from compute and started to offer cheap ways to store raw data and transform it later, incrementally, using tools like dbt.

    Reverse ETL moves the already transformed, already modeled data out of the warehouse and into operational systems for business teams to take action: Salesforce, HubSpot, Braze (an ad platform). It’s more of a third point on the same spectrum; it’s the vehicle for the other two, which is a problem that neither ETL nor ELT were intended to solve—getting warehouse truth into the tools where other things and humans are happening.

    The sticking point is that teams find themselves using these as “short cuts” rather than as decisions based on the workload:

    ·       Failure of compliance due to ELT by-default. A healthcare team loads raw PII into a warehouse without masking or tokenization as that is the way modern data stacks work.A healthcare or fintech team places the raw PII in a warehouse without masking or tokenization first. Now, without the mask, SSNs or PHI are stored in raw schemas that are accessible to half the analytics org and the cost of the compliance retrofit outweighs the transform step. This is the same for ETL’s pre-load transformation; scrub before, don’t scrub after it lands.

    ·       Runaway warehouse costs due to misusing ELT. A team pushes a full nightly extract of a 500-million-row transactional table into Snowflake, and uses dbt models to re-scan the data on every run, rather than incremental models. The warehouse bill expands due to the compute moving from a dedicated ETL server to the meterized cloud credits, without any one looking at the meter.

    ·       Operational data that has been stale due to the lack of Reverse ETL. A customer lifetime value model is created in the marketing team’s warehouse, but not piped back to the CRM. The warehouse model has no way to get back out of Salesforce, so the Sales reps still see the raw purchase counts. The insight is there, but it isn’t there where the person needs it at decision time.

    ·       ETL (Row-by-row) instead of ELT (Set-based). Some classic SSIS or legacy on-prem pipelines where they were changing records one by one inside the pipeline, and the same transformation can be written as a simple set-based SQL statement in the target warehouse and run in a fraction of the time.

    All these are the correct tools for the wrong jobs — but not bad tools!

    The Solution: A Decision Framework

    Ask 4 questions about each workload, instead of “which pattern do we standardize on?”. In reality, all three patterns are implemented together on various pipelines on most platforms.

    1. Is the data required to be scrubbed, masked, or filtered before it reaches any place it can be queried? If yes — PII, PHI, cardholder data, anything under GDPR/HIPAA/PCI scope — transform before load. This remains ETL’s main reason to be, regardless of the ELT fashions. Mask/tokenize in the extraction layer (Azure Data Factory data flows, or a simple Python/SQL Server SSIS step), meaning that any raw sensitive values never reach the raw schema of the warehouse. For those on the Microsoft stack, this is the typical best case scenario for maintaining ADF or SSIS in the mix in an otherwise ELT-focused Azure Synapse or Fabric pipeline, instead of removing it from the mix because dbt is cool.

    2. Does the transformation require a lot of steps and iterations and needs to be versioned, tested, re-run by analysts? If yes, use ELT. Bring in raw (or lightly scrubbed) data to the warehouse and leave the work of in-place modeling to dbt, stored procedures or Synapse/Databricks notebooks. You have version-controlled transformation logic, built-in automated testing, lineage graphs, and can rebuild history without re-extracting from a fragile source system when someone discovers a bug in the transformation.

    The most important decision when creating a model that causes cost blowouts is whether to run it incrementally (only the new or changed rows since the last run) or not. The key for teams transitioning from SQL Server/SSIS to a dbt-style ELT mindset is to get this concept in their heads early: a transformation is no longer “a step in a pipeline,” it’s a “materialized view” that needs to be refreshed, and it’s that refresh strategy where most of the warehouse bill lives or dies.

    3. What is the latency requirement and is it variable with each hop? Batch analytics (nightly board reporting) does not require ELT’s load then transform lag. Extracting and initial transforming typically occur within a streaming layer (Kafka/Event Hubs + stream processing) before anything enters a warehouse, or in the context of ETL, transform early.

    4. Is there a need for this insight to act upon outside of the warehouse for a human or downstream system(s)? Whether the data pipeline was ETL or ELT, without Reverse ETL the value of the data is lost once it’s returned. Tools such as Census, Hightouch, or a scheduled Azure Function or Logic App that read from a warehouse view and write to an API fill in the gap. Model it once, sync it wherever it needs to act it out. What this typically involves in practice is creating a single, well-governed model in the warehouse, e.g. a “customer health” or “customer segment” view, which categorizes each customer as “at-risk”, “high-value” or “standard” according to recency and lifetime spend, and then letting a Reverse ETL tool match that segment field with a custom field in the CRM, following a set schedule. Never again will any engineer have to create a CSV to Salesforce, and a sales rep’s view of the segment is always up to date with the warehouse model that powers it.

    In reality: streaming or batch extraction with PII scrubbed at the source (ETL) → raw but safe data deposited in the warehouse (ELT) → curated marts synced back to operational tools (Reverse ETL). Three patterns, one pipeline, for each one of them it is really good at doing.

    Proof: What This Looks Like in Practice

    The initial design on a retail insight app I was working on was ELT only – raw order and customer data was coming straight from Salesforce and the transactional database into Snowflake and all masking and transformation was being done downstream in dbt. Until a compliance audit brought up the fact that raw customer PII was accessible to any analytics use case with access to the warehouse, which also accessed some fields that never saw use in any analytics use case.

    But it wasn’t about giving up on ELT. It was putting in a thin ETL step at extraction: an Azure Data Factory data flow that was stripping out PII fields before putting data into the raw schema, and everything else flowed directly through to dbt for modeling. Access to detokenized values was restricted to a few service accounts.

    The measurable outcome: compliance gap was closed without any action on the 40+ dbt models that were already deployed, as the business logic that needed to be changed didn’t need to be moved. Compute cost was not impacted — the masking step did not cost anything in the warehouse compute, it did on the extraction layer. The team then integrated an hour-by-hour (via Hightouch) Reverse ETL sync to move the customer health segment view from the marketing platform into the CRM – removing a weekly manual CSV export performed by a marketing analyst every Monday. None of these three required giving up the other changes, and each pattern was used precisely where the compromises were warranted.

    The Close

    ETL, ELT, and Reverse ETL are not competing architectures for your loyalty, but three tools to address three different questions: what must be cleaned before it is put into the warehouse, what is less expensive to transform when compute resides there, and what must come out of the warehouse to have an impact? The ones who are burned are those who choose one pattern and apply all of their workloads through it.

    The next time you are thinking of setting up a pipeline, don’t ask “are we an ETL shop or an ELT shop?”. For each workload: Does it need scrubbing before it lands? Is the transform complex enough to benefit from version control and incremental materialization? Does it need to be materialized at extraction time (latency) given that output needs to walk back out of the warehouse to do any good? When answering the four questions truthfully for each data flow, the correct pattern — typically multiple patterns — emerges spontaneously.

  • Data Engineering ROI: How to Justify Your Data Platform Investment to the Board

    Data Engineering ROI: How to Justify Your Data Platform Investment to the Board

    Every data engineering leader has sat through the same meeting: the platform work is done, the pipelines are stable, but defending its value to the board is difficult because it rarely appears on a single P&L line. Data platform budgets are often treated as discretionary IT spend, meaning they get cut first during a downturn. Getting ROI measurement right is what keeps your next initiative fundable.

    Why Data Engineering ROI Is Hard to Measure (and Why It Matters)

    Boards fund outcomes. Data engineering delivers infrastructure. That mismatch is the root of the problem.

    A board can evaluate a sales tool by pipeline generated, or a marketing spend by cost-per-acquisition. Data platform investments don’t map that cleanly pipeline reliability, schema governance, and data quality improvements are foundational, meaning they enable other initiatives rather than generating value on their own. An Al model that improves fraud detection accuracy gets the credit; the governed, clean, well-lineaged data pipeline underneath it, without which the model wouldn’t have worked, gets none.

    This isn’t just a communication problem. Left unaddressed, it becomes a funding problem. Data platform budgets get treated as discretionary IT spend, get cut first in a downturn, and then get blamed when the next Al initiative underperforms because the data underneath it was never solid. Getting ROI measurement right isn’t an exercise in optics, it’s what keeps the next initiative fundable.

    Metrics That Actually Translate to Business Impact

    The fix starts with picking metrics a board member without a data engineering background can actually interpret. A few that consistently translate well:

    Data downtime cost avoided: Every hour a critical pipeline is down or serving bad data has a real cost delayed reporting, blocked decisions, or in regulated industries, compliance exposure. Tracking incidents avoided (or their reduced frequency after a platform investment) turns an abstract reliability improvement into a dollar figure.

    Time-to-insight reduction: How long does it take from “we need this data” to “here’s the answer”? If that cycle shrinks from days to hours after a platform investment, that’s a directly measurable efficiency gain that maps to faster business decisions.

    Engineering hours reclaimed from firefighting: A mature platform investment shows up as a shift in how engineers spend their time less time patching broken pipelines and chasing data quality issues, more time building new capabilities. That ratio, tracked before and after, is one of the cleanest ROI signals available.

    Data quality incident rate: Fewer downstream errors caused by bad data, wrong numbers in a report, a broken dashboard, a flawed model input is a leading indicator of platform health that’s easy to track and easy to explain.

    Cost-per-query or compute efficiency: For teams on modern cloud data stacks, tracking compute spend against query volume or data processed shows whether platform investments are actually improving unit economics, not just adding capability.

    None of these require exotic instrumentation. Most are extractable from existing observability and cost-monitoring tools already in place. The work is in deciding which ones matter for a given business and tracking them consistently.

    Connecting Data Initiatives to Business Outcomes

    Metrics alone don’t make the case they need to be tied to a specific business decision or outcome of the platform investment enabled or unblocked.

    The strongest version of this argument doesn’t say “we modernized our data stack.” It says: “faster, more reliable data pipelines cut our fraud review time from four hours to forty minutes,” or “consolidating our data sources let underwriting make decisions same-day instead of next-day.” Specific, traceable, and tied to something the board already understands the value of.

    This only works if a baseline exists before the investment. Teams that skip measuring the “before” state lose the ability to prove improvement later, a gap worth closing at the start of any platform initiative, not after the fact when the board asks for numbers. A structured data-readiness assessment before a major platform investment is one of the more reliable ways to establish that baseline, since it forces a documented starting point across data quality, infrastructure, and governance maturity that the post-investment numbers can be measured against.

    Framing matters too. An investment task built around “we need to modernize our data infrastructure” competes with every other infrastructure request in the budget cycle. An investment task built around “this unblocks same-day underwriting decisions” competes on the same terms as revenue-generating initiatives and tends to win more often.

    Making the Case to the Board

    When it’s time to present, resist the instinct to show everything. A board conversation isn’t the place for a full metrics dashboard, it’s the place for three or four numbers, chosen because they answer the two questions every board member is actually asking: why now, and what happens if we don’t.

    “Why now” is answered by connecting the investment to a business pressure the board already recognizes regulatory deadlines, a competitor’s faster decision cycles, or a growth plan that the current data infrastructure can’t support. “What happens if we don’t” is answered by quantifying the cost of inaction: the downtime already being absorbed, the compliance exposure already being carried out, the engineering hours already being spent on maintenance instead of building.

    This is a distinction we see play out constantly at Samta.ai, working with BFSI and regulated clients across Singapore. The teams that get board sign-off aren’t necessarily running the most technically impressive platforms, they’re the ones who walked into the room with a baseline, a business outcome, and a dollar figure attached to inaction.

    A recent IDC-backed business value study on enterprise data platform investments found that organizations with mature data discovery and governance infrastructure consistently recovered platform costs through reduced analyst search time and fewer duplicate data efforts alone before counting any downstream Al or analytics gains. That’s the kind of framing that resonates with a board: cost recovery that doesn’t depend on a speculative future win.

  • Identifying Hidden Token Costs in Snowflake Cortex AI

    Identifying Hidden Token Costs in Snowflake Cortex AI

    The demo works. It always does. You call AI_CLASSIFY on a sample of 10,000 rows, the credits barely move, and someone in the room says “this is so much cheaper than sending data to an external API.” Three weeks later your first real workload hits production — a million rows, five label classes, a moderately verbose model — and the bill is three times what you modelled. Nobody touched the model. Nobody changed the prompt. The data volume was planned. What went wrong?

    The short answer: Snowflake Cortex AI has three independent cost meters running in parallel, and two of them are nearly invisible until you go looking. The warehouse credit line your resource monitors watch? That’s only one of the three. The other two — AI token consumption and always-on serving compute — accumulate quietly in tables most engineers haven’t queried yet.

    After the April 2026 introduction of AI Credits as a separate billing currency, the gap between what teams expect to pay and what actually lands on the invoice got wider, not narrower. This piece maps exactly where the hidden costs live, shows you the math on each one, and gives you the SQL to surface them before your finance team does.

    TL;DR

    • Snowflake Cortex AI bills across three independent meters — warehouse compute, AI token consumption, and serving compute — and resource monitors only cover the first one.
    • Functions like AI_CLASSIFY, AI_SENTIMENT, and AI_SUMMARIZE silently inject a system prompt before your text, so the billed token count is always higher than the text you actually sent.
    • For AI_CLASSIFY, your label list is counted as input tokens for every single row processed, not once per call — a five-class classifier with verbose descriptions can multiply your expected token count by 2–4×.
    • Cortex Search charges a continuous serving-compute fee per GB of indexed data per month, regardless of whether any queries are running — a 70 GB corpus costs roughly $882/month at rest.
    • As of April 2026, AI Features bill in AI Credits ($2.00 global / $2.20 regional), which are separate from Platform Credits; the two currency types can coexist on the same bill and require different monitoring queries.
    • Query SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY per function and model to find your real cost breakdown; do not try to sum multiple overlapping views or you will double-count.
    • Model selection is still the single largest cost lever — the same classification workload can differ by 10–60× in price depending on which model you choose.

    Why the Demo Lied to You

    The confusion starts with how Snowflake traditionally teaches cost intuition. For years, the mental model was: bigger warehouse = more credits = more cost. You learned to right-size warehouses, use auto-suspend, and watch the METERING_HISTORY view. That model works fine for compute-heavy SQL. It actively misleads you for Cortex AI.

    When you run a Cortex AI function, the warehouse compute cost still applies — your VWH is active while the query runs, so those credits accumulate. But the AI token charges are separate, billed in a different currency against a different meter, and they show up in different Account Usage views. A demo on a SMALL warehouse processing 10,000 rows barely registers on either meter. A production run of one million rows with a frontier model is a completely different animal.

    One well-documented real-world example: a team processed 1.18 billion records using Cortex Functions and received a single-query bill of nearly $5,000 — almost entirely from token costs, with minimal warehouse compute. Their resource monitors never triggered because resource monitors don’t watch the AI token meter. The bill simply appeared.

    The April 2026 billing restructure added another wrinkle. Snowflake introduced AI Credits as a separate billing currency, flat-priced at $2.00 per credit for global routing or $2.20 for regional routing, independent of your Snowflake edition. This means an Enterprise customer and a Standard customer pay exactly the same rate for AI inference — but the two credit types appear as separate line items and require separate monitoring logic. If you built a cost dashboard before April 2026, it is almost certainly incomplete.

    The Token Inflation You’re Not Accounting For

    Most engineers assume “tokens billed = tokens in my text.” For AI_COMPLETE with a hand-written prompt that assumption is roughly correct. For the structured AI functions — AI_CLASSIFY, AI_SENTIMENT, AI_FILTER, AI_AGG, AI_SUMMARIZE, AI_TRANSLATE — it is wrong in ways the documentation buries in a footnote.

    According to Snowflake’s official cost documentation, these functions add a system prompt to your input text before sending it to the model. The billed token count is therefore always higher than the number of tokens in the text you provide. You pay for the system prompt on every row. You have no visibility into how long that system prompt is. You cannot opt out.

    For AI_CLASSIFY specifically, the hidden cost compounds further: your label list, descriptions, and examples are counted as input tokens for every record processed, not once per call. If you have five label classes with 30-word descriptions each, you’re paying for roughly 150 extra tokens on every single row. Run that against a million-row table and you’ve added 150 million tokens of cost that had nothing to do with your data.

    The fix is to measure before you scale. Snowflake provides a AI_COUNT_TOKENS function that reports token counts without incurring LLM charges — use it on a sample to calibrate your label overhead before committing to a full-table run:

    -- Estimate label overhead before running AI_CLASSIFY at scale
SELECT
  COUNT(*) AS sample_rows,
  AVG(SNOWFLAKE.CORTEX.AI_COUNT_TOKENS(
    'llama3.1-8b',
    your_text_column
  )) AS avg_text_tokens,
  -- Add your label string manually to see the combined token count
  AVG(SNOWFLAKE.CORTEX.AI_COUNT_TOKENS(
    'llama3.1-8b',
    your_text_column || ' CATEGORIES: positive, negative, neutral, urgent, spam'
  )) AS avg_with_labels_tokens
FROM your_table
LIMIT 5000;

    The gap between avg_text_tokens and avg_with_labels_tokens is your label overhead per row. Multiply by row count and by the per-million-token rate for your chosen model to get a cost estimate before you fire the real query. This takes five minutes and can prevent a four-figure surprise.

    The Two-Currency Problem

    Before you can build a cost dashboard, you need to understand which features bill in which currency — because the monitoring SQL differs by type.

    Cortex FeatureCredit TypeBilling DimensionPrimary Usage View
    AI Functions (AI_COMPLETE, AI_CLASSIFY, AI_EMBED, etc.)AI CreditPer million tokens (input + output)CORTEX_FUNCTIONS_USAGE_HISTORY
    Cortex AgentsAI CreditPer million tokens; additive across sub-callsCORTEX_AGENT_USAGE_HISTORY
    Cortex Search (serving)AI CreditPer GB indexed per month, continuousCORTEX_SEARCH_SERVING_USAGE_HISTORY
    Cortex Search (embedding)AI CreditPer token on insert/updateCORTEX_SEARCH_SERVING_USAGE_HISTORY
    AI Parse DocAI CreditPer 1,000 pages; each page = 970 tokensCORTEX_DOCUMENT_PROCESSING_USAGE_HISTORY
    Cortex Analyst API (standalone)Platform CreditPer 1,000 messagesMETERING_DAILY_HISTORY
    Cortex Fine-tuningPlatform CreditPer compute jobMETERING_DAILY_HISTORY
    Virtual Warehouse (any query)Platform CreditPer second, 60-second minimumWAREHOUSE_METERING_HISTORY

    The important detail: Cortex AI Functions like AI_COMPLETE stack two meters simultaneously. You pay AI Credits for the tokens, and you pay Platform Credits for the warehouse time your query consumed. A query that takes 30 seconds on a MEDIUM warehouse and processes 500,000 tokens is billing on two completely separate ledgers. Neither one cancels the other. Snowflake’s recommendation is to use no larger than a MEDIUM warehouse for Cortex AI calls, because a larger warehouse doesn’t speed up token processing — it just burns more Platform Credits for the same result.

    The Cortex Search Idle Tax

    Cortex Search is architecturally different from the AI SQL functions. It’s a managed vector-search service: you create a search service over a table, Snowflake indexes it, and you query it via a REST call or through Cortex Agents. The billing model reflects this — and it’s the most surprising line item for teams that build and then deprioritize a search-based RAG feature.

    Cortex Search’s serving compute bills continuously per GB of indexed data per month, while the service is resumed — whether or not any queries are running. The Snowflake pricing documentation confirms this: “A running search service incurs costs even when it isn’t serving queries.” Based on the Service Consumption Table, the serving rate is 6.3 AI Credits per GB per month. At the global AI Credit price of $2.00, that’s $12.60 per GB per month, every month, at rest.

    Run the math for a team that has multiple Cortex Search services:

    ScenarioIndexed Data (GB)AI Credits/moCost/mo (global)
    Single knowledge base (small)20 GB126 Cr$252 / mo
    Single knowledge base (medium)70 GB441 Cr$882 / mo
    5 domain services × 70 GB350 GB2,205 Cr$4,410 / mo
    Dev service (left running)30 GB189 Cr$378 / mo (wasted)

    The dev service row is where most teams first notice the problem. Someone spun up a search service in a development environment to prototype a chatbot, the project shifted priorities, and the service kept running. It doesn’t consume query tokens because nobody’s hitting it. It consumes serving compute because it exists. That’s $378/month for a service that produced zero output in that billing period.

    The mitigation is straightforward: configure AUTO_SUSPEND on any search service that has predictable idle windows, and manually suspend development services when a feature is deprioritised. Snowflake Batch Search is an alternative for workloads that don’t need real-time retrieval — its serving compute runs only during the batch job, not continuously.

    Cortex Agents: The Cost Multiplier Nobody Drew on the Whiteboard

    Cortex Agents are billed per million tokens, in AI Credits, with rates determined by the underlying model. That sounds simple. The complication is that agents orchestrate multi-step workflows, and every step that invokes a sub-service generates its own token consumption. Snowflake’s official pricing docs state it directly: costs are additive across the underlying services the agent invokes.

    A realistic agent loop might look like this: the agent receives a user question (input tokens), calls Cortex Search to retrieve context (embedding tokens + serving compute), calls Cortex Analyst to generate SQL (Analyst tokens), executes the SQL on a warehouse (Platform Credits), and then calls an LLM to formulate a final answer (more input + output tokens). Every hop generates its own consumption. The result visible to the user is a single response. The result visible to your billing dashboard is five separate line items, split across two credit types, spread across four different usage views.

    Standard monitoring via CORTEX_FUNCTIONS_USAGE_HISTORY does not provide agent-specific breakdowns. To get token-level visibility per agent, you need to query SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS — a system table that captures token counts, models used, timing, and execution context for each agent invocation. That table is not surfaced by default in the Snowflake UI; you have to query it directly.

    -- Per-agent token cost attribution
-- Requires ACCOUNTADMIN or SNOWFLAKE_TELEMETRY privilege
SELECT
  agent_name,
  model_name,
  DATE_TRUNC('day', event_timestamp) AS event_day,
  SUM(input_tokens)                  AS total_input_tokens,
  SUM(output_tokens)                 AS total_output_tokens,
  SUM(input_tokens + output_tokens)  AS total_tokens
FROM SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS
WHERE event_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY total_tokens DESC;

    For AI SQL functions, your canonical daily monitoring query should look like this:

    -- Cortex AI function cost by model and function — last 30 days
-- Use CORTEX_FUNCTIONS_USAGE_HISTORY as the single source; do NOT sum
-- across CORTEX_AISQL_USAGE_HISTORY and CORTEX_FUNCTIONS_USAGE_HISTORY together
SELECT
  DATE_TRUNC('day', start_time)   AS usage_day,
  function_name,
  model_name,
  SUM(input_tokens)               AS input_tokens,
  SUM(output_tokens)              AS output_tokens,
  SUM(credits_used)               AS ai_credits,
  ROUND(SUM(credits_used) * 2.00, 2) AS est_cost_usd
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY ai_credits DESC;

    One critical warning from Snowflake’s own community documentation: the views CORTEX_AISQL_USAGE_HISTORY, CORTEX_FUNCTIONS_USAGE_HISTORY, and an incremental metering path all overlap. Summing them produces double-counts. Pick one canonical view per service type and reconcile totals against the matching service type in METERING_DAILY_HISTORY.

    Non-Text Inputs: The Per-Page and Per-Second Trap

    If your team is using Cortex for document intelligence — contract review, PDF extraction, audio transcription — the token model changes again. AI_PARSE_DOCUMENT and AI_EXTRACT bill by page rather than by text token: each page in a document counts as 970 tokens. A 50-page contract isn’t 50 pages of your text column — it’s 48,500 tokens before a single word of your prompt or the model’s output enters the meter.

    Audio inputs bill at 50 tokens per second of audio. A one-hour customer support call is 180,000 audio tokens before output tokens are added. At frontier model rates, an hour of audio can cost more than a thousand-word document by a wide margin.

    The implication for pipeline design: always pre-filter. Before sending a document to AI_EXTRACT, check page count. Before sending audio to a transcription function, check duration. For PDFs specifically, page-level sampling — sending only the pages likely to contain the target information — can reduce cost by 60–80% compared to sending the full document.

    The Gotchas Nobody Warns You About

    Your existing resource monitors don’t cover AI token spend.Resource monitors in Snowflake watch warehouse compute credits. They have no visibility into AI Credit consumption. A runaway AI_CLASSIFY job on a large table will not trigger your existing budget alerts. You need separate alerting built on CORTEX_FUNCTIONS_USAGE_HISTORY and wired to a Snowflake Task and notification integration.

    The regional routing setting silently raises every AI bill by 10%.If your account has CORTEX_ENABLED_CROSS_REGION set to DISABLED or a specific regional setting for data residency, you’re paying $2.20 per AI Credit instead of $2.00. That’s a 10% tax on every token across every Cortex AI feature, and it’s an account-level parameter many teams set once during a compliance review and never revisit against their cost model.

    Cortex Analyst through the standalone API still bills in Platform Credits, not AI Credits.If you’re calling Cortex Analyst via the REST API directly rather than through Cortex Agents, it bills per 1,000 messages at Platform Credit rates — which vary by your Snowflake edition. The same Analyst call made through a Cortex Agent costs in AI Credits. The same feature, two different billing regimes, depending on how you invoke it.

    Materializing AI results is almost always cheaper than recomputing them.Teams building pipelines that call AI_CLASSIFY or AI_SENTIMENT inside a scheduled task often reprocess unchanged records on every run. The AI functions have no inherent awareness of which records changed since the last run. Join against your source table’s UPDATED_AT column, write results to a separate table, and only pass new or modified rows to the AI function. This pattern, applied consistently, can reduce ongoing AI Credit consumption by 50–90% for stable datasets.

    The Cortex Guard security layer adds its own token cost on top of AI_COMPLETE.If you’re using Cortex Guard to filter model outputs for safety — which is sensible for user-facing applications — it bills separately from the underlying AI_COMPLETE call. The input token count for Cortex Guard is based on the number of tokens in AI_COMPLETE’s output. In other words, longer model responses cost more not once but twice: once when generated, and again when scanned by the guard.

    The One Principle

    “Treat Cortex AI cost engineering the same way you treat warehouse sizing — measure before you scale, not after. The token meter doesn’t have a circuit breaker unless you build one.”

    Related reading: Cortex Search RAG guide · Cortex Code and dbt optimization · Governing AI Agents in Snowflake · AI coding agents and pipeline security · What actually works when building AI agents · Snowflake Cortex AI cost docs (official) · Snowflake AI pricing and AI Credits (official)

  • Snowflake Time Travel vs. Fail-safe: What Gets Recovered and When

    Snowflake Time Travel vs. Fail-safe: What Gets Recovered and When

    3:14 a.m., and a migration script hands off to DROP TABLE orders_staging; against what everyone on the team swore was a permanent table. It wasn’t. Somewhere in the last quarter it got recreated as TRANSIENT to shave storage costs, and nobody updated the runbook. The on-call engineer isn’t worried — Snowflake has Time Travel, Snowflake has Fail-safe, this is a solved problem. Except Fail-safe doesn’t apply to transient tables. Zero days. The table is gone the moment its one-day Time Travel window closes, and by the time anyone notices, it already has. Six hours of ingestion, rebuilt by hand from source, on a Saturday.

    That’s the gap this article is about. Time Travel and Fail-safe get talked about together so often that people assume they’re one continuous safety net. They’re not the same feature, they don’t behave the same way, and the difference has real financial and recovery-time consequences that most teams only discover during an incident.

    TL;DR

    • → Time Travel lets you query, clone, or UNDROP historical data yourself for a retention window you configure — 0 to 1 day on Standard Edition, up to 90 days on Enterprise Edition and above, for permanent objects.
    • → Fail-safe is a separate, fixed 7-day recovery period that starts after Time Travel expires, and it is not self-service — only Snowflake Support can pull data back from it, and only for permanent tables.
    • → Transient and temporary tables carry zero Fail-safe days. They’re cheaper precisely because Snowflake gives up that protection.
    • → Both features bill as storage: Time Travel data accrues at the normal storage rate, and a table with heavy daily updates on a long retention window can multiply its effective storage several times over.
    • → Time Travel doesn’t create a second copy of your table — it retains the old micro-partitions that a write would otherwise discard, via Snowflake’s copy-on-write architecture.
    • → UNDROP TABLE, AT, and BEFORE only work inside the Time Travel window. Once an object crosses into Fail-safe, none of those commands work anymore.

    What Time Travel Actually Stores

    Snowflake tables are stored as immutable micro-partitions — compressed, columnar chunks of roughly 50–500 MB of uncompressed data each. When you run an UPDATE or DELETE, Snowflake doesn’t rewrite rows in place. It writes new micro-partitions reflecting the change and stops referencing the old ones from the table’s current state. That’s the whole trick: Time Travel is Snowflake choosing not to immediately throw those old partitions away.

    Snowflake doesn’t back up a table for Time Travel — it just delays discarding the partitions a write would otherwise drop.

    Every table, schema, and database has a DATA_RETENTION_TIME_IN_DAYS parameter that controls how long those superseded partitions stick around before they’re eligible for permanent deletion. Retention is inherited: set it on a database and every schema and table created under it picks up the value unless overridden lower down. There’s also an account-level MIN_DATA_RETENTION_TIME_IN_DAYS floor — if it’s set, the effective retention for any object becomes whichever is larger, its own setting or the floor. That parameter is easy to forget exists and even easier to be surprised by months later.

    Standard Edition accounts get 1 day of retention by default, and you can only turn it down to 0 — there’s no way to go longer without upgrading to Enterprise Edition. Enterprise and above allow up to 90 days for permanent databases, schemas, and tables, configurable per object.

    Retention by Table Type — the Comparison Nobody Reads Until It’s Too Late

    The gotcha in the opening story lives entirely in this table. Table type determines the retention ceiling independent of edition, and it determines whether Fail-safe exists at all.

    Table typeTime Travel rangeFail-safe periodNotes
    Permanent0–1 day (Standard) · 0–90 days (Enterprise+)7 days, fixedThe only table type with Fail-safe protection
    Transient0–1 day, on any edition0 daysCapped at 1 day even on Enterprise — cannot be extended
    Temporary0–1 day, session-scoped0 daysDropped automatically when the session ends

    Notice that transient tables don’t just lose Fail-safe — their Time Travel ceiling is capped at one day regardless of what edition you’re on or what the account default says. That’s the entire reason transient tables cost less to store: Snowflake is retaining less history for them, full stop.

    Querying and Restoring Inside the Window

    Time Travel is queryable directly in SQL, three ways: by timestamp, by relative offset, or by the query ID of the statement that changed the data.

    -- Query a table as it existed at a specific timestamp
SELECT * FROM orders
AT (TIMESTAMP => '2026-07-28 09:00:00'::timestamp);

-- Query as it existed immediately before a specific statement ran
SELECT * FROM orders
BEFORE (STATEMENT => '8e5d0c1d-0073-4f57-8263-6e6bb1a2b1d4');

-- Restore an accidentally dropped table, in place
UNDROP TABLE orders_staging;

-- Clone a table's state from 6 hours ago into a new object,
-- useful for diffing without touching the live table
CREATE TABLE orders_audit_clone
CLONE orders AT (OFFSET => -60*60*6);

    All four of those commands only work while the object — or the specific rows you’re targeting — is still inside its Time Travel window. Past that point, UNDROP returns an object-not-found error, not a graceful fallback into Fail-safe. This trips people up constantly: Fail-safe existing doesn’t mean these commands quietly keep working against it. They don’t.

    Fail-safe: What It’s For, and What It Isn’t

    Fail-safe is the part of this system most engineers get wrong, because the name suggests self-service safety and it’s the opposite. It’s a fixed, non-configurable 7-day period that begins the moment an object’s Time Travel retention expires, and it exists for Snowflake’s disaster-recovery purposes — not for routine “oops I dropped a table” moments. You cannot query it, clone from it, or run UNDROP against it. The only path back is a support ticket, and Snowflake is explicit that recovery through Fail-safe can take anywhere from hours to several days, positioning it as a last resort rather than a recovery SLA you can plan around.

    Only permanent tables get Fail-safe. It’s a fixed 7-day buffer after Time Travel expires, and it’s not something you access yourself.

    And critically, Fail-safe only exists for permanent tables. Look back at the comparison table above — transient and temporary objects get 0 days of it. If your team leans on transient tables for staging (a completely reasonable cost optimization, discussed in our zero-copy cloning guide), you’ve implicitly decided that a mistake on those tables gets exactly one day — the Time Travel window — before it’s unrecoverable at any price, support ticket included.

    The Cost Math

    Time Travel and Fail-safe both bill as storage, at your account’s normal per-terabyte rate. As of 2026, Snowflake’s published on-demand list price is around $23 per compressed terabyte per month for AWS US East, with regional variation — worth flagging because a lot of older guides still cite a $40/TB figure that Snowflake has since moved off of.

    The part that surprises people isn’t the rate, it’s the multiplier. Time Travel storage isn’t billed once — it’s billed for the entire retention window, for every version of every changed row, not just until the next write overwrites it.

    Illustrative numbers, not a live account screenshot — but the shape of the math holds: retention window × daily churn rate is the real cost driver, not table size alone.

    A 100 GB table with 10% of its rows modified daily, sitting on a 90-day retention setting, accrues on the order of 10 GB of Time Travel history per day. Over the full window that’s roughly 900 GB — close to a 9x storage multiplier over the table’s own size, from one retention setting on one heavily-churned table. Multiply that across every staging and fact table on a 90-day account default and it stops being a rounding error.

    This is also where the MIN_DATA_RETENTION_TIME_IN_DAYS parameter bites teams that think they’ve already optimized. Someone sets an individual table’s retention to 1 day to cut costs, but the account-level minimum is still 30 — the effective retention is the larger of the two, and the storage bill doesn’t move.

    The Gotchas Nobody Warns You About

    Transient tables have zero Fail-safe, by design, not by oversight. That’s the trade you’re making every time you choose transient for cost savings. It’s a good trade for genuinely disposable staging data. It’s a bad surprise for anything that turns out to matter more than you thought.

    Dropping and recreating a schema resets what its children inherit. If you drop a schema and recreate it with a different DATA_RETENTION_TIME_IN_DAYS, tables created afterward inherit the new value — but objects dropped under the old setting keep whatever retention was active at the time they were dropped, not the new one. It’s easy to assume a schema-level change is retroactive. It isn’t.

    The account-level minimum silently overrides a lower table-level setting. As covered above — if you’re trying to cut Time Travel storage costs by lowering retention on specific tables and the number on your bill doesn’t move, check MIN_DATA_RETENTION_TIME_IN_DAYS before assuming the change didn’t take.

    Cloning at a past timestamp quietly locks in stale data. CREATE TABLE ... CLONE x AT (...) is a zero-copy operation that materializes as a real object pointing at that historical state. It’s easy to leave one of these lying around after an investigation and forget it’s not tracking the live table anymore.

    Fail-safe recovery is not a routine operation, and Snowflake treats it that way. There’s no dashboard, no self-service button, and no fixed turnaround time — it’s a support ticket that gets prioritized as the disaster-recovery mechanism it was designed to be, not an extension of your undo history.

    The One Principle

    Time Travel is a tool you use; Fail-safe is a safety net Snowflake uses on your behalf — design your retention and table types as if Fail-safe doesn’t exist, because for anything outside a permanent table, it doesn’t.

    Related reading: Snowflake Time Travel architecture, deep dive · zero-copy cloning for storage and CI/CD · how Snowflake stores data internally · Snowflake docs: Understanding and using Time Travel · Snowflake docs: Understanding storage cost

  • SQL Patterns That Actually Show Up in Interviews (All 10, With Code)

    SQL Patterns That Actually Show Up in Interviews (All 10, With Code)

    Nobody fails a SQL interview because they don’t know the syntax. They fail because they stare at a question about “users with a 5-day login streak” and don’t recognize it as the exact same problem as “continuous subscription periods” and “consecutive winning games” — three phrasings of one pattern with one trick. The engineers who breeze through SQL rounds aren’t faster typists or syntax savants. They’ve seen the patterns enough times that a novel-sounding question instantly collapses into “oh, that’s gaps-and-islands” — and then the SQL is the easy part.

    There are about ten of these patterns, and they cover the overwhelming majority of what product companies actually ask. This is a tour of all ten — the keyword that gives each one away, the core trick, real SQL, and the follow-up the interviewer asks when you get the first version right. The goal isn’t to memorize ten queries; it’s to build the recognition reflex so that in the room, you spend your time on the interesting variation instead of rediscovering the base pattern from scratch. If you want the deeper argument for why this recognition skill matters more than raw syntax, I’ve made it in why senior engineers write SQL differently.

    One thing worth pinning to the wall before we start — the logical execution order of a query, because half of “why doesn’t my WHERE see my alias” questions dissolve once you know it: FROM → JOIN → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY.

    The whole game in one table: interviewers rarely name the pattern, but the words they use give it away. Train yourself to hear the keyword and reach for the trick.

    1. Gaps and islands (consecutive sequences)

    Keywords: consecutive, streak, continuous, sessions, “5 days in a row.” This is the one that trips people up most, and the trick is almost magical once it clicks: subtract a row number from the ordered date. Consecutive dates produce the same constant; a gap shifts it, creating a new group.

    Why it works: for consecutive days the row number grows in lockstep with the date, so date − rn is constant. The moment a day is skipped, the constant jumps — and that jump is your new streak boundary.

    WITH distinct_logins AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM distinct_logins
),
login_groups AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL rn DAY) AS grp
  FROM numbered
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_length
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 5
ORDER BY user_id;

    The follow-up: “what if you need to detect the gaps themselves, not the streaks?” Switch to LAG() — compare each row to the previous login, flag where the difference isn’t 1 day, and cumulative-sum those flags into group IDs. Same idea, different vehicle. Note the DISTINCT up front: duplicate logins on the same day would silently break the row-number arithmetic.

    2. Top-N per group

    Keywords: top 3 per department, highest/lowest per group, latest record, first/last. The single most common window-function question. Partition by the group, order by the metric, filter on the rank.

    WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

    The follow-up you must nail: “what if two people tie on salary?” That’s the interviewer probing whether you know the three ranking functions — and this is one of the most common trip-ups, so know it cold: ROW_NUMBER() gives 1,2,3 with an arbitrary tiebreaker; RANK() gives 1,1,3 (ties share a rank, next rank skips); DENSE_RANK() gives 1,1,2 (ties share, no gap). If the question is “top 3 salaries” and ties should all count, you want DENSE_RANK(), not ROW_NUMBER(). For the “latest record per user” variant, it’s the identical shape with ORDER BY created_at DESC and WHERE rn = 1.

    3. Running totals / cumulative metrics

    Keywords: running total, cumulative, so far, progressive. The frame does the work: SUM(x) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

    WITH daily_revenue AS (
  SELECT DATE(created_at) AS order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE(created_at)
)
SELECT order_date,
       SUM(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_revenue;

    The follow-up: “now show each user’s running total and the global running total in the same query.” That tests whether you understand that two window functions can carry different PARTITION BY clauses side by side — a per-user frame partitioned by user_id, and a global frame with no partition — which usually means aggregating to user-day and day grains first, then combining.

    4. Event → state transformation

    Keywords: headcount over time, active subscriptions per day, “build the metric then track it.” Here the metric doesn’t exist in the data — you construct it from events. The classic is daily headcount: turn each hire into +1 and each termination into −1, then take a running sum over a date spine.

    WITH RECURSIVE date_spine AS (
    SELECT MIN(hire_date) AS dt FROM employee
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_spine
    WHERE dt < CURRENT_DATE()
),
changes AS (
    SELECT hire_date AS dt, +1 AS delta FROM employee
    UNION ALL
    SELECT termination_date AS dt, -1 AS delta
    FROM employee WHERE termination_date IS NOT NULL
),
daily_change AS (
    SELECT dt, SUM(delta) AS tdelta FROM changes GROUP BY dt
)
SELECT d.dt,
       SUM(COALESCE(c.tdelta, 0)) OVER (
         ORDER BY d.dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS headcount
FROM date_spine d
LEFT JOIN daily_change c ON d.dt = c.dt
ORDER BY d.dt;

    The “+1/−1 delta then running sum” trick generalizes to any state-from-events problem: concurrent sessions, active subscriptions, inventory on hand.

    5. Rolling / moving windows

    Keywords: 7-day moving average, 30-day active users, trailing metric. Same window machinery as running totals, but a bounded frame. The gotcha that separates candidates: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is 7 rows, not 6.

    SELECT sale_date, daily_sales,
       AVG(daily_sales) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_sales;

    Window frames are worth truly internalizing, because half these patterns are just different frame boundaries over the same OVER() skeleton:

    Every rolling metric is just a choice of frame boundaries relative to the current row. And the ROWS-vs-RANGE distinction at the bottom is a favorite senior-level probe — worth knowing why one is deterministic.

    The ROWS-vs-RANGE trap: the default frame for SUM() OVER (ORDER BY dt) is actually RANGE, which groups all rows sharing the same ORDER BY value into one step — so two orders on the same date both get the day-end total, not their individual accumulation. ROWS treats each physical row independently. For running totals and time series, prefer ROWS to avoid unintended grouping on ties; being able to explain that difference unprompted signals real depth.

    6. Cohort / retention analysis

    Keywords: retention, cohort, week 0 / week 1, signup behavior. Ubiquitous at product companies. The shape is always: assign each user a cohort (their first-activity period), then measure activity by offset from that cohort.

    WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('week', MIN(activity_date)) AS cohort_week
  FROM user_activity GROUP BY user_id
),
user_activities AS (
  SELECT a.user_id, c.cohort_week,
         DATE_TRUNC('week', a.activity_date) AS activity_week
  FROM user_activity a
  JOIN user_cohort c ON a.user_id = c.user_id
),
cohort_size AS (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS total_users
  FROM user_cohort GROUP BY cohort_week
)
SELECT ua.cohort_week,
       DATEDIFF('week', ua.cohort_week, ua.activity_week) AS weeks_since_signup,
       COUNT(DISTINCT ua.user_id) AS active_users,
       COUNT(DISTINCT ua.user_id) * 1.0 / cs.total_users AS retention_rate
FROM user_activities ua
JOIN cohort_size cs ON ua.cohort_week = cs.cohort_week
GROUP BY ua.cohort_week, weeks_since_signup, cs.total_users
ORDER BY ua.cohort_week, weeks_since_signup;

    The three-CTE structure — cohort assignment, activity-with-offset, cohort size for the denominator — is the reusable skeleton. Retention rate is just active-at-offset divided by the week-0 size.

    7. Self-join logic

    Keywords: compared to previous, more than their manager, bought A but not B. Any time you compare rows within the same table. The “A but not B” version is a clean anti-join:

    SELECT DISTINCT a.user_id
FROM purchases a
LEFT JOIN purchases b
  ON a.user_id = b.user_id AND b.product = 'B'
WHERE a.product = 'A' AND b.user_id IS NULL;

    The trap here is NULLs. The instinct is often WHERE user_id NOT IN (SELECT user_id FROM purchases WHERE product='B') — but if that subquery returns even one NULL, NOT IN yields zero rows, silently. The LEFT JOIN ... IS NULL anti-join above is immune. Many “compare to previous row” self-joins are also better expressed with LAG(), which is cheaper than a correlated subquery and reads more clearly.

    8. Time-series expansion (date spine)

    Keywords: daily trend, fill missing dates, continuous timeline, “even days with zero.” The fix for gaps in a report is to generate the complete calendar and LEFT JOIN your data onto it, so missing periods become explicit zeros instead of vanishing rows.

    WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(MIN(hire_date), '%Y-%m-01') AS month_start FROM employees
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM months
    WHERE month_start < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
)
SELECT m.month_start, COALESCE(SUM(x.metric), 0) AS metric
FROM months m
LEFT JOIN some_table x ON DATE_FORMAT(x.dt, '%Y-%m-01') = m.month_start
GROUP BY m.month_start
ORDER BY m.month_start;

    The production aside worth saying out loud: recursive date spines that recompute headcount by rescanning all employees every month are fine in an interview but expensive at scale. Mentioning that you’d back this with a precomputed monthly_headcount snapshot table in production — built by an incremental pipeline rather than recomputed each run — is exactly the kind of comment that separates a senior candidate, and it ties directly to not reprocessing what didn’t change.

    9. Percentiles / distribution

    Keywords: top 10%, percentile, ranking distribution. This is where NTILE(), PERCENT_RANK(), and DENSE_RANK() live. “Top 10% of employees by rating” has a naive form and a fair form, and the interviewer usually wants the fair one:

    -- Fair version: handles ties at the cutoff correctly
WITH latest_rating AS (
  SELECT emp_id, rating,
         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY review_date DESC) AS rnk
  FROM performance_reviews
),
ranked AS (
  SELECT *,
         DENSE_RANK() OVER (ORDER BY rating DESC) AS bucket,
         COUNT(*)     OVER () AS total_count
  FROM latest_rating WHERE rnk = 1
)
SELECT * FROM ranked WHERE bucket <= CEIL(0.10 * total_count);

    NTILE(10) forces exactly ten equal-sized buckets (take bucket 1); the DENSE_RANK approach is fairer when many people share the boundary rating. The killer follow-up is point-in-time correctness: “employees change departments over time — attribute each rating to the department they were in then.” That forces a slowly-changing-dimension join on a date range (start_date <= review_date < end_date) instead of a naive join to the employee’s current department — and getting that right is a strong signal.

    10. Detect overlapping date ranges

    Keywords: booking conflicts, overlapping subscriptions, double-booked, schedule clash. The elegant move is to reason about when ranges don’t overlap, then invert. Two ranges miss each other only if one ends before the other starts:

    -- Non-overlap:  a.end < b.start  OR  b.end < a.start
-- Invert (De Morgan) -> the standard overlap condition:
SELECT s1.customer_id,
       s1.subscription_id AS sub_1,
       s2.subscription_id AS sub_2
FROM subscriptions s1
JOIN subscriptions s2
  ON s1.customer_id = s2.customer_id
 AND s1.subscription_id < s2.subscription_id   -- avoid self- and duplicate pairs
 AND s1.end_date >= s2.start_date
 AND s2.end_date >= s1.start_date;

    Two details interviewers check: the s1.id < s2.id condition (so you don’t compare a row to itself or count each pair twice), and that you derived the overlap condition rather than memorized it — walking through the two non-overlap cases and applying De Morgan’s law is the move that earns the nod.

    Putting it together: the combined question

    Real interviews often stack two or three patterns. “Monthly revenue for completed 2025 orders, with the change vs the previous month, showing all 12 months even when revenue is zero” is three patterns at once: conditional filtering, a date spine (all 12 months), and LAG() for the month-over-month delta — over a base that first aggregates order_items to order grain before summing to month grain. If you can see it as “aggregate → spine → LAG” instead of one intimidating blob, you’ve already won. The recognition reflex is the whole skill; the syntax is just spelling.

    The gotchas nobody warns you about

    NOT IN with a NULL returns nothing. One NULL in the subquery and NOT IN silently yields zero rows. Use NOT EXISTS or a LEFT JOIN ... IS NULL anti-join for exclusions, every time.

    ROWS ≠ RANGE, and the default is RANGE. SUM() OVER (ORDER BY dt) groups tied values into one step. For a true row-by-row running total, spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    “6 PRECEDING” is a 7-row window. Off-by-one on frame bounds silently produces the wrong average. Count the current row.

    ROW_NUMBER vs RANK vs DENSE_RANK is a tie question in disguise. When the interviewer says “what if they tie,” they’re testing which one you’d swap to. Have the 1,2,3 / 1,1,3 / 1,1,2 distinction ready.

    Wrapping a date column in a function kills index/pruning use. Prefer hire_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) over TIMESTAMPDIFF(MONTH, hire_date, ...) <= 6 — same logic, but the range predicate can use metadata the function form throws away.

    The one principle

    You don’t pass a SQL interview by knowing more syntax — you pass it by recognizing that the strange-sounding question in front of you is one of about ten patterns you’ve already solved a dozen times. Learn the keyword that gives each pattern away, learn the one core trick behind it, and practice the recognition until it’s instant. Then the interview stops being a memory test and becomes what it should be: a conversation about the interesting variation, conducted in a language you already speak fluently.

    Related reading: Why senior engineers write SQL differently · Why SQL is still the most valuable skill · Don’t recompute what didn’t change · Making these queries fast in production

  • Optimizing dbt Models for Modern Warehouses: An Author & Reviewer’s Guide

    Optimizing dbt Models for Modern Warehouses: An Author & Reviewer’s Guide

    Two dbt models can be byte-for-byte identical in output, pass every test, and read cleanly in review — and one of them costs $50 a month while the other costs $5,000. The compiler already guarantees the SQL is correct. What it can’t tell you is how much data moved through the model to produce that correct answer, and that number is the entire ballgame. The trap is that a code review reads a model like prose — do the joins make sense, are the columns right — when the thing that actually determines the bill is invisible in the text and only shows up in the query profile.

    So this guide is about reading the profile, not just the SQL. The single skill that separates engineers who write cheap models from ones who write expensive ones is the ability to look at a query profile and see where data volume balloons or lingers — then trace that back to the one line of SQL responsible. Everything below hangs off one mental model, and I’ll show you what each fix looks like in an actual profile, because “trust me, it’s faster” is worth a lot less than “here’s the partition count before and after.”

    The one mental model: how much data survives each stage?

    Before any checklist, ask one question of every model: how much data survives each stage of this query? A healthy model’s volume shrinks, roughly monotonically, from raw input to final output. A broken one has a stage where volume grows — or stays huge longer than it needs to — and that stage is almost always where your money goes.

    Left: volume shrinks stage by stage — a well-shaped query. Right: the join ran before the filter, producing 12 TB of intermediate data from a 5 TB input. The SQL is syntactically perfect and still ruinous.

    Every tactic in this post is a specific instance of that one idea: find the stage where volume grows or stays too big for too long, and fix that stage. That’s it. The rest is knowing the four places it usually happens and what each looks like in the profile.

    Why this matters more on modern warehouses

    Snowflake, BigQuery, Redshift, and Databricks all ship genuinely capable optimizers. They handle predicate pushdown, join reordering, and parallel execution for you, which means the physical tuning you’d have obsessed over on a 2005-era database — index hints, manual join order, rewriting for a specific plan — mostly doesn’t apply. The engine owns that layer now. If you want the mechanics of how that execution layer actually works, I covered it in what really happens when you run a query.

    What the optimizer can’t fix is a logical mistake: joining before filtering, reading columns you don’t need, recomputing the same aggregation five times, or choosing ROW_NUMBER() when MAX() would do. Those decisions are baked into the SQL, and no optimizer can rewrite your intent. That’s why the highest-leverage review comments are almost never about syntax — they’re about which stage of the volume curve a change affects.

    1. Read less data

    The biggest lever, and the first thing to check. On a columnar warehouse, unused columns cost real I/O even though the query works either way:

    -- Bad: pulls every column off a 200-column table
SELECT * FROM customers

-- Better: only what's used downstream
SELECT customer_id, country FROM customers

    The one that quietly defeats people is partition pruning, because the query looks filtered but is structured so the engine can’t use the filter. Wrapping the filtered column in a function is the classic killer:

    -- Bad: the function hides order_date from the optimizer
WHERE YEAR(order_date) = 2026

-- Good: a plain range predicate the engine can prune on
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01'

    This is the single highest-value fix in the whole post, and it’s the one worth seeing rather than taking on faith

    The profile tells the story the SQL hides: the function-wrapped predicate scanned all 512 partitions (1.42 TB, 94s); the range predicate pruned to 3 partitions (9 GB, 3.4s). Same result, same rows out — a ~150x difference in data read.

    When a filter or join predicate isn’t reducing data the way you’d expect, check for a hidden function first — CAST(date AS DATE), UPPER(email), COALESCE(col, 0) all do the same damage. This is exactly why Snowflake maintains min/max metadata per micro-partition, and why a function over the column throws that metadata away; I unpacked that storage mechanism in how Snowflake stores data internally.

    2. Join wisely

    Joins are where a well-behaved query most often turns into a runaway one. The single question worth asking on every join in a review: is this actually the cardinality I think it is? A join you assumed was 1:1 becomes a many-to-many explosion the moment a source table has duplicate keys — 100M orders against 500M clicks on customer_id can produce tens of thousands of rows per customer, and it’s invisible until someone notices the output count is absurd.

    The highest-value structural fix is to aggregate before you join, not after:

    -- Bad: join the full 800M-row payments table, then aggregate
SELECT o.customer_id, SUM(p.amount)
FROM orders o
JOIN payments p ON o.customer_id = p.customer_id
GROUP BY o.customer_id

-- Better: reduce payments to 10M rows first, then join
WITH payments_agg AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM payments
    GROUP BY customer_id
)
SELECT o.customer_id, pa.total_amount
FROM orders o
JOIN payments_agg pa ON o.customer_id = pa.customer_id

    Same result — but the join now processes 10M rows instead of 800M, because the reduction happened before the join instead of after. The profile makes the difference impossible to miss — watch the row count going into the join, and the spill:

    Aggregating first shrinks the join’s input from 800M rows to 10M — which also eliminates the disk spill that was quietly dominating the runtime. Same output, ~11x faster.

    Two more join checks worth a glance: watch for skew (one dominant key value — a 90%-US country column, or a flood of NULLs — creates wildly unbalanced work even when the total row count looks fine), and verify every join has a real predicate (a missing condition turns a join into a cartesian product, where row counts don’t grow, they multiply).

    3. Don’t recompute what you already computed

    Is the same large table scanned more than once? If two CTEs both pull from big_table, ask whether one pass can derive both results. Is an expensive expression — a long CASE block, a repeated subquery — computed several times instead of once in a CTE? And watch for SELECT DISTINCT used as a band-aid: it’s very often papering over a join producing duplicate rows it shouldn’t. If a model “suddenly needs” DISTINCT, that’s a prompt to find the join that changed, not to accept the DISTINCT as the fix.

    4. Reduce before expensive operations — and question the tool itself

    Push filters ahead of window functions: running ROW_NUMBER() over 5 billion rows when the same logic could run over 100 million after an earlier filter is a common, easy-to-miss cost. But the most valuable and most overlooked review question isn’t about tuning what’s there — it’s whether the approach itself is right:

    -- Heavier than needed: full partition + sort to get "latest"
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC) AS rn
    FROM orders
) WHERE rn = 1

-- Often cheaper: if you only need the date, not the whole row
SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id

    If the goal is genuinely “the latest order date per customer,” MAX() with a GROUP BY does far less work than a full partitioned sort. ROW_NUMBER() earns its keep only when you need the entire row at the latest timestamp — a surprising number of “slow query” tickets are really “wrong tool for the job” tickets. In the profile, the tell is the WindowFunction node sorting billions of rows and spilling to disk, when the aggregate version never sorts at all:

    The window function sorts all 5 billion rows and spills 210 GB to disk; MAX() never sorts. Same answer, and it also runs on a smaller warehouse — an ~8x time win on top of a halved credit rate.

    For the reproducible-dedup case where you do need the whole row, the deterministic QUALIFY pattern is the right tool, which I covered in why senior engineers write SQL differently.

    5. Materialization and recomputation — the dbt-specific one

    This is where a lot of warehouse spend hides in plain sight, and it’s two questions. First: is this the right materialization? A very common finding is a model that’s been a plain table since day one, fully recomputed on every run, long after it grew large enough that full rebuilds stopped being free.

    Left: the materialization decision, which is really a “how often does this change vs how expensive is it to rebuild” question. Right: who should catch each class of issue — push everything mechanical left toward the author and CI.

    Second: is there a missed incremental opportunity? If a model recomputes five years of history every run when only yesterday’s data changed, that’s usually the single highest-value fix available — often bigger than every tactic above combined. The question to ask: does this model’s WHERE clause know about is_incremental(), or is it silently doing a full rebuild every time? The profile for a full-rebuild model is unmistakable — it scans the entire history on every run:

    The full-rebuild model reprocesses 3.2 billion rows every single run to change a sliver of data; the incremental version scans one pruned day and merges 1.8M rows. This is where the order-of-magnitude cost wins usually hide.

    The same “don’t reprocess what didn’t change” discipline is the whole premise of dbt state-based selection at the project level.

    Who should catch each of these

    Treating this whole list as “what the reviewer checks” is the wrong default — it makes review slow and contentious. Three owners share it. The author, before opening the MR, catches anything mechanically verifiable by running the query and looking at the output: SELECT *, an unfiltered scan, a repeated CTE. The CI pipeline catches what can be automated: pruning regressions, row-count guards, lint rules. The reviewer is left with what only a human can see — cross-model blast radius (“this join also feeds the finance mart”), organizational memory (“we already know this key is skewed”), and whether the approach fits the business need. The goal over time is to shrink the reviewer’s column: every item that graduates from “reviewer catches it” to “CI catches it” is a permanent win.

    The gotchas nobody warns you about

    A function on a filtered column silently disables pruning. YEAR(order_date), CAST, UPPER, COALESCE on the predicate column all throw away the partition metadata. The query looks filtered; the profile shows every partition scanned.

    DISTINCT is usually a symptom, not a fix. If a model started needing SELECT DISTINCT, a join is producing duplicates it shouldn’t. Fix the join; don’t dedupe the mess.

    The profile, not the SQL, tells you the truth. Two models with identical output can differ 100x in bytes scanned. If you’re optimizing without reading the profile, you’re guessing — check partitions scanned and bytes scanned before and after every change.

    Full-rebuild tables are the biggest silent cost. A table materialization recomputing history every run often dwarfs every other inefficiency combined. Check materialization strategy before micro-optimizing the SQL.

    ROW_NUMBER() is frequently the wrong tool. If you only need an aggregate, not the whole row, a GROUP BY is cheaper than a partitioned sort. Confirm the requirement before defaulting to a window function.

    The one principle

    Writing an efficient dbt model isn’t about SQL syntax — the compiler already guarantees correctness — it’s about mentally tracing the volume of data at each stage and asking whether that stage makes the data smaller or just makes more work for the next one. Modern warehouses optimize the physical layer for you; they can’t decide to aggregate before joining or reach for MAX() instead of ROW_NUMBER(). Those are logical choices made in the SQL, and the further upstream you catch them — author self-check, then CI, then reviewer — the cheaper they are. Learn to read the profile, and the order-of-magnitude wins stop being luck.

    Related reading: What really happens when you run a query · Micro-partitions and why pruning works · Why senior engineers write SQL differently · Stop recomputing unchanged models · Snowflake query profile docs

  • Building AI Agents: What Actually Works in Production

    Building AI Agents: What Actually Works in Production

    Ask ten teams if they’ve built an AI agent and nine will say yes. Look at what they actually shipped and most of it is a workflow with one LLM call in the middle — a fixed pipeline where a model fills in one step, not an autonomous system deciding its own path. That’s not a criticism. It’s the most important thing nobody says out loud about building AI products in 2026: the stuff that actually works in production is far less “agentic” than the demos, and the teams getting value are the ones who figured out which 20% of true agency is worth the risk and hard-coded the other 80%.

    There’s a clean mental model underneath the hype, though, and it’s worth having whether you’re building a real agent or an honest workflow. An agent is just a language model wrapped in five things: tools (what it can do), knowledge (what it knows), memory (what it remembers), a loop (how it acts), and guardrails (what it must not do). Answer “what tools, what knowledge, what memory” for your use case and the architecture mostly writes itself. This is that model, told from a data engineer’s chair — where “tools” means governed access to your warehouse, “knowledge” means your RAG and your data quality, and every one of these five parts fails in ways you’ve seen before under different names.

    TL;DR

    • → An AI agent is an LLM plus five components: tools, knowledge, memory, a reasoning loop, and guardrails — design those and the architecture follows.
    • → Most production “agents” are really workflows with one smart step, and that’s usually the correct, cheaper, safer choice — reach for true agency only when the path genuinely can’t be fixed in advance.
    • → What works: narrow single-purpose agents, curated tool sets, humans approving risky actions, and evals gating every release. What’s still hype: fully autonomous do-anything agents acting unsupervised.
    • → For a data engineer, “give the agent tools” means governed, least-privilege access to your systems — an agent with write access is a service account that makes its own decisions.
    • → “Knowledge” is a RAG-and-data-quality problem, not a model problem; most agent failures trace to bad retrieval or stale data, not a weak LLM.
    • → The reasoning loop needs a hard step cap, and the whole thing needs evals — an agent without evaluation is an untested pipeline with a mind of its own.

    The five parts of an agent

    Strip away the branding and every agent, simple or complex, is a model surrounded by the same five components. The value of the model is that it forces you to answer concrete questions before you write code — and each question maps onto infrastructure you already own.

    The whole model on one page: an LLM is just the reasoner. Tools, knowledge, memory, the loop, and guardrails are the parts you actually engineer — and the parts that actually break.

    Tools — what it can do. The functions the agent can call: query a table, hit an API, write a file. For a data engineer this is the highest-stakes component, because “give the agent a tool” means “grant a non-deterministic system access to your infrastructure.” A read-only tool against account-usage views is low risk; a tool that can suspend a warehouse or write to a table is a service account that makes its own decisions. Least privilege isn’t a nice-to-have here — it’s the whole safety model, which is why I’ve written separately on giving agents access without opening security holes. The emerging standard for exposing these tools cleanly is MCP, which I broke down in MCP explained in 3 levels.

    Knowledge — what it knows. The facts the agent needs that aren’t in the model’s training: your schemas, your docs, your current data. This is a retrieval problem, and it’s where most agent projects actually fail — not because the model is weak, but because the retrieval is bad or the underlying data is stale. If you’re wiring this up on your own data, the mechanics are in the Cortex Search RAG guide. The uncomfortable truth: your agent is only as good as the data platform underneath it.

    Memory — what it remembers. State that persists across steps or sessions, so a follow-up like “now do the same for last quarter” doesn’t start from zero. In practice this is checkpointing — the same durable-state thinking you apply to any pipeline that has to resume after a failure rather than restart.

    The loop — how it acts. The reason-act-observe cycle that separates an agent from a single call: the model reasons, calls a tool, reads the result, and decides what to do next. This loop is the source of an agent’s power and its danger, which is why it always needs a hard cap on steps.

    Guardrails — what it must not do. Input validation, output filtering, step limits, and human approval for risky actions. These are the AI equivalent of the constraints and tests you’d never ship a pipeline without — the difference between a demo and something you can leave running.

    The distinction that matters most: workflow vs agent

    Before you build anything, answer one question honestly: does the task need the model to decide the path, or just to do one hard step along a path you already know? Getting this wrong is the most common and most expensive mistake in the space.

    A workflow runs a path you defined; an agent decides the path at runtime. The runtime freedom is exactly what makes agents powerful — and exactly what makes them harder to test, secure, and cost-control.

    A workflow is a fixed sequence you designed, with a model doing the intelligent part of one or more steps — classify this ticket, extract these fields, draft this summary. You control the path; the model fills in the reasoning. An agent hands the model the wheel: it decides which tools to call and in what order, looping until the task is done. The agent is more flexible and can handle open-ended tasks a rigid pipeline can’t — but that same freedom is what makes it harder to test (the path changes every run), harder to secure (it can call tools in combinations you didn’t anticipate), and harder to cost-control (each loop is a billed call). The senior move is to default to a workflow and escalate to agency only where the task genuinely can’t be pre-planned — which is the same restraint I argued for in choosing tools over subagents.

    What actually works (and what’s still theater)

    Here’s the honest cut from teams actually running this in production, stripped of vendor optimism. The pattern is consistent: narrow beats broad, supervised beats autonomous, and boring beats clever.

    The consistent signal from production: narrow beats broad, supervised beats autonomous, curated beats sprawling. The right column is where budgets and pilots go to die.

    The single most reliable pattern is the narrow, single-purpose agent: one job, a small curated set of tools, and a human in the approval path for anything consequential. The fantasy that keeps failing is the autonomous do-anything agent with dozens of tools and no supervision — it demos beautifully and falls apart on the long tail of real inputs. Multi-agent “swarms” are especially over-applied; most problems that get a swarm proposal are better served by one well-scoped agent or, more often, a workflow. And underneath all of it: without evals gating releases, you have no idea whether a change helped or hurt, which means “it looked good in the demo” becomes your entire QA process. This connects to a deeper reliability problem I covered in why larger models give confidently wrong answers in production — more autonomy multiplies the blast radius of that failure mode.

    A pragmatic way to start

    If you’re building your first AI product, resist starting with “let’s build an agent.” Start with the three founding questions — what tools, what knowledge, what memory — and answer them for the narrowest useful version of the task. Then build the simplest thing that could work, which is almost always a workflow: a fixed path with one or two LLM-powered steps, real retrieval behind the knowledge step, and a guardrail on anything that touches production. Add evals before you add capability. Only when you hit a task where you genuinely can’t predetermine the path — where the model really does need to choose among tools dynamically — do you graduate to a true agent, and even then you keep the tool set tight and a human on the risky actions. The teams shipping working AI products aren’t the ones who built the most autonomous system. They’re the ones who were honest about how little autonomy the job actually required.

    The gotchas nobody warns you about

    Most “agents” should be workflows. If you can draw the path in advance, you don’t need an agent — you need a pipeline with a smart step. Building an agent for a workflow problem buys you cost, unpredictability, and a security surface you didn’t need.

    Every tool is an attack surface and a cost center. More tools means more ways for the agent to do something surprising, and more tokens spent deciding among them. Curate ruthlessly; a tight tool set outperforms a sprawling one.

    Your agent’s ceiling is your data platform. Bad retrieval, stale data, or missing metadata will sink a great model. Agent quality is a data-quality problem wearing a trench coat.

    The loop will run away if you let it. A missing step cap turns a confused agent into a runaway bill. Set a hard recursion limit before you set anything else.

    No evals means no engineering. If you can’t measure whether a change improved the agent, you’re not building a product, you’re tuning a slot machine. Evals — including credit for the agent saying “I can’t do this” — come before more features.

    The one principle

    An agent is a language model wrapped in tools, knowledge, memory, a loop, and guardrails — and “what actually works” is deciding, for each of those five, how little you can get away with. The instinct that ships working AI products isn’t reaching for maximum autonomy; it’s the engineer’s instinct to build the simplest system that solves the problem, grant the least access that does the job, and measure everything. You already have those instincts. Building agents is mostly the discipline of not abandoning them because the technology is exciting.

    Related reading: MCP: how agents get their tools · Giving agents access safely · Tools vs subagents: don’t over-build · Build the knowledge layer (RAG) · OpenAI: a practical guide to building agents · AI agent systems: architectures & evaluation

  • 20 AI Concepts Every Data Engineer Actually Needs

    20 AI Concepts Every Data Engineer Actually Needs

    There are a hundred “AI concepts explained for beginners” listicles, and most of them are written for people who will never build anything. This one isn’t. If you’re a data engineer, you already understand pipelines, storage, and cost better than most ML tutorials assume — what you actually need is a map of the AI vocabulary that keeps showing up in your Slack, your architecture reviews, and your on-call, with a straight answer to the only question that matters: what does this mean for the systems I build?

    So this is the 20-concept tour, but curated and framed for practitioners. No math derivations, no “imagine a neuron is like a brain cell” hand-waving. Each concept gets a plain definition and a one-line reason it matters in a data pipeline. They’re grouped into four tiers — foundations, language models, grounding, and production — because that’s roughly the order the ideas build on each other, and roughly the order you’ll hit them in real work.

    TL;DR

    • → For data engineers, the AI stack reduces to four tiers: foundations (how models learn), language models (how LLMs behave), grounding (how you make them use your data), and production (how you ship them safely).
    • → The three concepts you’ll argue about most are RAG, fine-tuning, and MCP — RAG is what the model knows, fine-tuning is what it is, MCP is what it can do.
    • → The 2026 default escalation is prompt → RAG → fine-tune → distill; you move right only when the cheaper option provably hits a wall.
    • → Most AI failures in production are data problems, not model problems — Gartner projected at least 30% of generative-AI projects would be abandoned after proof of concept, with poor data quality a leading cause.
    • → Embeddings and vector databases are just a new index type over your data; you already have the instincts to reason about them.
    • → Tokens and context windows are cost and correctness levers, not trivia — they decide your bill and your accuracy.
    • → Evals and guardrails are the AI equivalent of tests and constraints; a model without them is an untested pipeline.

    Tier 1: Foundations — how models learn

    1. Neural network. A stack of simple math functions with tunable weights that, together, learn to map inputs to outputs. For your purposes it’s a black box that turns numbers into numbers; the engineering interest is that it’s just a big parameterized function, not magic.

    2. Training. The process of adjusting those weights by showing the network examples and nudging it toward less wrong. Relevance: training is a batch job with a colossal input dataset — the same data-quality-in, garbage-out rule you already live by applies, at scale.

    3. Parameters (weights). The learned numbers inside the model; “7B” or “70B” refers to how many. More parameters means more capacity and more cost to run — model size is a compute-budget decision, not just an accuracy one.

    4. Tokens. The chunks (roughly word-pieces) that models read and write. This is the one every engineer underestimates: tokens are the unit you’re billed in and the unit context limits are measured in. A pipeline that sends 40,000 tokens per call has a cost and latency profile you must design for.

    5. Embeddings. A way to turn text (or images, or rows) into a fixed-length vector of numbers where “similar things are near each other.” Relevance: this is just a new kind of index over your data. If you can reason about a hash index, you can reason about an embedding — it’s a lookup keyed on meaning instead of exact match.

    Tier 2: Language models — how LLMs behave

    6. Large Language Model (LLM). A very large neural network trained to predict the next token over enormous text corpora, which turns out to be enough to answer questions, write code, and summarize. Treat it as a stochastic function from prompt to text — powerful, useful, and never guaranteed correct.

    7. Context window. The maximum tokens a model can consider at once — prompt plus retrieved data plus its own output. This is a hard architectural constraint: it caps how much of your data a single call can see, and, as I’ve written about in why larger models give wrong answers in production, accuracy often degrades long before you actually fill it.

    8. Temperature. A knob for randomness in the output. Low means consistent and predictable; high means varied and creative. For data pipelines you almost always want it low — but even at zero, output isn’t fully deterministic, which trips up a lot of teams.

    9. Prompting. The instructions you give the model. It’s the cheapest, fastest way to change behavior, and the first rung of the escalation ladder below. Underrated skill: a precise prompt often beats a fancier technique.

    10. Non-determinism. The same prompt can produce different answers on different runs. This breaks the mental model engineers bring from deterministic systems, and it’s why you can’t validate an LLM feature with a single spot-check — I dug into the mechanics in why LLMs give different answers to the same question.

    Tier 3: Grounding — making models use your data

    This is the tier that turns a generic model into something useful on your organization’s data, and it’s where the three most-argued-about concepts live. The distinction is worth getting exactly right, because teams routinely pick the wrong one and waste weeks.

    The distinction teams get wrong most often: RAG, fine-tuning, and MCP solve three different problems and combine freely — they’re not competing choices.

    11. RAG (Retrieval-Augmented Generation). At query time, you retrieve relevant documents from your own data and feed them into the prompt, so the model answers from current, private context instead of only its training. It’s the knowledge layer, the default starting point for most enterprise use cases, and the reason answers can carry citations.

    12. Vector database. The store that holds embeddings and answers “find the N most similar chunks to this query.” It’s the retrieval engine underneath RAG — conceptually, a similarity index you query with meaning. If you want the hands-on version, I walked through building this on Snowflake in the Cortex Search RAG guide.

    13. Fine-tuning. Actually retraining the model’s weights on your examples to change its behavior — tone, format, domain reasoning. It’s the behavior layer, not a way to teach the model new facts (RAG does that better). In 2026, small-model fine-tunes via LoRA/QLoRA are cheap; full fine-tuning rarely makes sense for a product team.

    14. Hallucination. When a model produces confident, fluent, wrong output. This is the single most important failure mode for anyone putting AI on data, because it fails silently — the answer looks right. RAG with citations is the most reliable mitigation; blind trust is the most common mistake.

    15. Context engineering. The umbrella discipline (formalized by Anthropic in its 2025 write-up on effective context engineering) of deliberately designing everything that goes into the model’s context — retrieved docs, tools, history, instructions. The 2026 reframing is that “RAG vs long-context vs MCP” is the wrong debate; they’re all tools inside context engineering.

    Tier 4: Production — shipping models safely

    16. Agents. An LLM in a loop that can reason, call tools, observe results, and repeat until a task is done. This is the agency layer — the shift from “answers questions” to “takes actions.” It’s also where the risk jumps, which is why guardrails below matter.

    17. MCP (Model Context Protocol). A standard way to expose typed tools — APIs, databases, systems — that an agent can invoke. If RAG is declarative memory, MCP is agency: the capacity to act. It’s fast becoming the interface layer between agents and your platform; I explained it at three depths in MCP explained in 3 levels.

    18. Evals. Systematic tests that measure whether a model’s output is good enough — accuracy, format, abstention, safety. Evals are to AI what unit and integration tests are to code: without them you’re shipping an untested pipeline and hoping. Critically, a good eval rewards a model for saying “I don’t know” instead of guessing.

    19. Guardrails. The bounds around a model in production — input validation, output filtering, step/recursion limits, human approval for risky actions. They’re the difference between a demo and something you can leave running, and they belong in the release gate, not as an afterthought.

    20. Distillation. Training a smaller, cheaper model to mimic a bigger one, to cut cost and latency once a use case is proven. It’s the last rung of the escalation ladder — reached rarely, and only after cheaper options are exhausted.

    The canonical 2026 sequence. Start at the bottom-left; move up and right only when the cheaper option provably fails — not because the next rung sounds more serious.

    How these fit together

    The concepts aren’t a flat glossary; they stack. Foundations explain why a model behaves like a probabilistic function. The language-model tier explains the levers you actually touch — tokens, context, temperature. Grounding is where you inject your data (RAG), reshape behavior (fine-tuning), and grant agency (MCP). Production is where evals and guardrails keep the whole thing honest. When someone proposes “let’s fine-tune a model on our data so it answers support questions,” you now have the vocabulary to catch the mistake: they want RAG (new facts), not fine-tuning (new behavior), and the actual bottleneck will be data quality — which is where Gartner expected many generative-AI efforts to stall: data problems dressed up as model problems.

    The gotchas nobody warns you about

    Fine-tuning is not how you add knowledge. The most common expensive mistake is fine-tuning to teach facts. Fine-tuning changes behavior; RAG adds knowledge. Reach for RAG first, almost always.

    Tokens are a cost and correctness lever, not trivia. Underestimating token usage blows budgets and, via context degradation, quietly lowers accuracy. Treat token count as a first-class number in any AI pipeline design.

    Your AI project is a data project. The model is rarely the bottleneck — retrieval quality, freshness, lineage, and governance are. If your data platform is shaky, no model choice will save the feature.

    “Agent” is often overkill. A single prompt or a RAG call solves many problems a full agentic loop is proposed for. Agents add capability and cost and risk; use them when the task genuinely needs multi-step tool use.

    No evals means no idea. Without evaluation you cannot tell whether a model change helped or hurt. Ship evals with the feature, and make abstention (“I don’t know”) a passing answer, not a failing one.

    The one principle

    Every one of these twenty concepts is, for a data engineer, a variation on problems you already know — indexing, cost, testing, data quality, and access — wearing new vocabulary. You don’t need to become an ML researcher to build serious AI systems. You need to map the new terms onto the engineering instincts you already have, know which layer a given concept lives in, and remember that in production the model is almost never the hard part — your data is.

    Related reading: MCP explained in 3 levels · Build RAG on your own data · Why bigger models still get it wrong · Why LLMs give different answers · It’s not AI — it’s automation

  • Why Larger LLMs Give Incorrect Answers in Production

    Why Larger LLMs Give Incorrect Answers in Production

    A team I worked with upgraded their support-ticket triage pipeline to a bigger, newer model expecting fewer mistakes. Benchmarks said it was smarter across the board. Two weeks in, the error rate on a specific ticket category had gotten worse, not better — and worse in a particular way: the new model was wrong just as often as the old one, but its wrong answers now came with confident, detailed justifications instead of the old model’s hedgy “I’m not entirely sure, but…” The support team started trusting the wrong answers more, because they sounded more certain. Nobody had budgeted for that.

    That’s the part the “AI hallucinates” conversation usually skips. Bigger models are not simply less wrong — in several measured respects they’re differently wrong, and the difference that matters most in production is confidence, not accuracy. There’s real, recent research behind this, and it points at three separate mechanisms: how models are trained to answer instead of abstain, how their reliability quietly degrades as the input grows even inside benchmarks-passing context windows, and how production conditions differ from the clean single-turn evals every leaderboard measures. None of that means larger models are worse. It means “larger” doesn’t buy you the thing production actually needs, which is knowing when to say “I don’t know.”

    TL;DR

    • → OpenAI’s 2025 research reframes hallucination as an incentive problem: next-token training and standard evals reward confident guessing over calibrated “I don’t know,” so models learn to bluff.
    • → Pretrained models are reasonably well-calibrated; RLHF alignment measurably degrades that calibration, making models more confident without making them more correct — an effect researchers call the alignment tax.
    • → Scaling model size doesn’t eliminate this; multiple studies describe larger models producing “confident nonsense” at a similar or greater rate, just more fluently.
    • → Chroma’s 2025 “Context Rot” study tested 18 frontier models and found every one degrades as input length grows — well before the context window is full, even on simple tasks.
    • → Distractors (content that’s topically close but doesn’t answer the question) hurt accuracy more than irrelevant filler, and the effect compounds as input grows.
    • → In Chroma’s tests, Claude models abstained more under ambiguity while GPT models more often produced confident, incorrect answers — model families differ meaningfully in this failure mode.
    • → Production adds failure modes benchmarks don’t test: retrieval quality in RAG, long accumulated agent context, and non-deterministic sampling — so a benchmark-topping model can still underperform in your actual pipeline.

    The training incentive: models are rewarded for guessing

    Start with the most fundamental mechanism, because it explains why this doesn’t go away as models get bigger. OpenAI’s 2025 paper, Why Language Models Hallucinate, argues that the standard training and evaluation setup structurally rewards confident guessing over calibrated uncertainty. A model is trained to predict the next token, and it’s evaluated on benchmarks that score right-or-wrong with no separate credit for a correct “I don’t know.” A model that always guesses will, on average, score higher than one that abstains when unsure — even if the abstaining model is more trustworthy. The behavior isn’t a bug that better data fixes; it’s the predictable output of an incentive structure, and scaling the model up scales the same incentive with it.

    This gets compounded at the alignment stage. Research tracing model calibration through the training pipeline — from Kadavath et al.’s foundational 2022 work through more recent studies on the “alignment tax” — has found that base, pretrained models are reasonably well-calibrated: when a pretrained model says it’s 80% confident, it’s right roughly 80% of the time. RLHF, the fine-tuning stage that makes a model helpful and fluent, measurably degrades that calibration. The model comes out more confident and more polished, but not more accurate — confidence and correctness get pulled apart at exactly the stage designed to make the model pleasant to use.

    Each stage of the standard training pipeline independently pushes toward confident answers. None of them individually looks like a bug — the compounding is the problem.

    Scaling doesn’t fix this because scaling doesn’t change the incentive. A survey published in Frontiers in AI in 2025 makes the point directly: larger models remain capable of “confident nonsense,” and model scaling alone amplifies rather than eliminates hallucination in certain contexts. A bigger model has more capacity to construct a fluent, internally consistent, wrong answer — which is a worse failure mode for a downstream system to catch than an obviously garbled one.

    Context rot: accuracy degrades long before the window fills

    The second mechanism is specific to production because it’s about what happens once you start feeding a model real, long inputs — RAG context, chat history, tool outputs, agent state — rather than the short, clean prompts most benchmarks use. A 2025 Chroma technical report, deliberately titled Context Rot, tested 18 frontier models including GPT-4.1, Claude 4, Gemini 2.5, and Qwen3, and found that every one of them degrades as input length increases — often well before the context window is close to full, and even on tasks as simple as finding one fact in a document or exactly repeating a string back.

    Chroma’s 2025 study found this pattern held across all 18 models tested — accuracy declines with input length even when the task itself doesn’t get harder.

    Three details from that report matter for anyone building production RAG or agent systems. First, ambiguity compounds the effect: when the needed fact doesn’t closely match the wording of the question — the realistic case, since users rarely phrase things the way a document does — performance degrades faster as input grows. Second, distractors hurt more than plain irrelevant filler; content that’s topically related but doesn’t actually answer the question pulls accuracy down non-uniformly, and the effect gets worse, not better, as more distractors are added. Third — and this is the one worth sitting with — the researchers found that structurally coherent context (well-organized, logically flowing text) degraded model performance more than shuffled, incoherent text did. That’s the opposite of the intuitive assumption that cleaner input is always easier for a model to use.

    There’s also a genuinely useful, model-specific finding buried in that report: under ambiguity, Claude models more often abstained — explicitly stating that an answer couldn’t be determined from the given context — while GPT models more often produced a confident, incorrect answer instead. That’s not a claim that one vendor is unconditionally more accurate; it’s evidence that how a model handles uncertainty under long, messy context is a real, measurable, model-specific property worth testing before you pick what runs in production, not something you can assume from a benchmark leaderboard alone.

    Why this specifically bites in production and not in your evaluation

    Put those two mechanisms together and the production gap makes sense. Most public benchmarks are short, clean, single-turn, and score right-or-wrong with no credit for calibrated abstention — which is exactly the setup that rewards confident guessing and doesn’t test context rot at all. Production is the opposite on every count: it’s long (RAG context, chat history, tool outputs), ambiguous (real user phrasing rarely matches document wording), and cumulative (an agent’s context grows with every tool call it makes). A model can genuinely top the leaderboard and still be the wrong choice for a pipeline that hands it 40,000 tokens of retrieved documents and expects a single correct fact back.

    This is also where architecture choices you already know matter start to compound the problem instead of solving it. Retrieving too many chunks “to be safe” in a RAG pipeline doesn’t just cost more — per the context rot findings, it actively degrades accuracy, especially when the extra chunks are distractors rather than clean irrelevant filler. An agent that accumulates tool results across a long-running task is accumulating exactly the kind of long, structurally coherent context Chroma found hurts performance most. And a model under uncertainty defaulting to a confident guess rather than an abstention is the last-mile version of the training-incentive problem — it’s not a separate bug, it’s the same one showing up downstream.

    What actually helps

    None of this is a reason to avoid larger models — it’s a reason to design around known failure modes instead of assuming scale solves them. A few things follow directly from the research above. Keep retrieved context tight rather than generous: fewer, more relevant chunks measurably outperform “retrieve broadly and let the model sort it out,” because more chunks means more distractors and distractors compound with length. Test for abstention behavior specifically, not just accuracy — a model that says “I can’t determine this from the given context” on an ambiguous case is behaving correctly even though a naive eval scores it as a miss; the more dangerous system is the one that never abstains. And treat long-running agent context as a liability to actively manage — summarizing or pruning accumulated state periodically rather than letting it grow unbounded, since the Chroma findings suggest that coherent, well-organized accumulated context degrades performance rather than protecting it.

    It’s also worth remembering that non-determinism sits underneath all of this: the same prompt can produce a different answer on a different run, for reasons rooted in how these models sample tokens — I’ve written separately about why LLMs give different answers to the same question, and that variance means a single spot-check of a prompt tells you less than it feels like it does. If you’re using AI to generate or review structured output like SQL, the same discipline applies: never trust a green run as proof of correctness, a point I’ve made about why passing tests still ship bad data. And if agents are reading your pipeline’s metadata to answer questions, the context rot findings are a direct argument for curating what reaches them rather than dumping everything and hoping — the same principle behind giving agents metadata access deliberately rather than broadly.

    The gotchas nobody warns you about

    A benchmark win doesn’t transfer to your pipeline. Public leaderboards are short, clean, single-turn tests. If your production input is long, ambiguous, or accumulated over many turns, the benchmark isn’t measuring the failure mode you’ll actually hit.

    More retrieved context is not a safety margin. The instinct to retrieve generously “in case the model needs it” directly works against the research: more chunks means more distractors, and distractors degrade accuracy faster as volume grows.

    A model that never says “I don’t know” is a red flag, not a feature. If your eval only scores right/wrong, you can’t distinguish a model correctly abstaining from one confidently guessing wrong — and the second one is more dangerous precisely because it’s harder to catch downstream.

    Well-organized context can hurt more than messy context. Counterintuitively, Chroma found coherent, logically flowing input degraded performance more than shuffled text with the same content. Don’t assume tidy prompt construction is automatically safer.

    Model families differ on this specific behavior. Whether a model abstains or guesses under ambiguity is a measurable, model-specific property. If reliability under uncertainty matters for your use case, test for it directly rather than assuming it from general capability scores.

    The one principle

    A larger model gives you more capability, not more honesty about its limits — those are trained in separately, and mostly not trained in at all. The fix isn’t a bigger model or a longer context window; it’s designing the system around the specific, now well-documented ways models fail — tight retrieval instead of generous, explicit testing for abstention, and active management of anything that accumulates context over time. Production doesn’t need a model that’s never wrong. It needs one — and a system around it — that’s honest about when it doesn’t know.

    Related reading: Why LLMs give different answers to the same question · Why passing tests still ship bad data · Giving agents metadata access safely · It’s not AI you should worry about — it’s automation · Chroma: Context Rot research report · Why Language Models Hallucinate (OpenAI, 2025)