DEV Community: Laura Chicovis

Open or closed model? There is a third option

Laura Chicovis — Wed, 09 Sep 2026 21:01:30 +0000

Open or closed? The question shows up in every architecture review, and it usually gets settled with a benchmark chart and a price-per-token comparison. But it often gives the wrong answer, because the two options on the table are not the two you have.

There is a third sitting between them, and for most workloads it wins.

Why the binary is badly posed

The usual reasoning runs like this. An H100 rents for a few dollars an hour, while a frontier API charges a markup per token on that same hardware. So you run the weights yourself and keep the margin someone else is taking.

The catch is that "using an open model" and "operating an open model" got collapsed into one choice. So separate them, and the third option appears.

The three shapes

Closed frontier API. Variable cost, zero operations, no access to the weights. Roughly $2.50 to $15 per million tokens (packet.ai, jul/2026).
Hosted open-weight API. Variable cost, zero operations, and the same weights you would run. Roughly $0.07 to $0.90 per million tokens for Llama 4 and DeepSeek V4 class models (packet.ai, jul/2026).
Self-hosted open weights. Fixed monthly cost, full control, and an operational surface you now own. Llama 4 70B on an H100 at batch=8 comes to about $0.18 per million output tokens (packet.ai, jul/2026).

Rows two and three run the same model, in the same order of magnitude. So the saving that justifies buying GPUs is not the one against the frontier API, it is the one against a provider already serving those weights, and there it mostly disappears.

The number that decides it is utilization

That $0.18 figure assumes the GPU runs at 85% utilization with vLLM continuous batching, against 30 to 40% for naive serving (packet.ai, jul/2026). Meanwhile, average GPU utilization across production Kubernetes fleets sits at 5%, and the best cluster in that dataset, a 136-node H200 deployment, reached 49% (Cast AI, State of Kubernetes Optimization Report 2026).

That gap is the whole argument. A rented GPU bills the same idle as saturated, so cost per token scales inversely with utilization. Drop from 85% to 5% and the same setup costs roughly seventeen times more per token.

Then add operations. Version upgrades, OOM debugging, monitoring and capacity planning run 10 to 20 hours a month, between $1,500 and $4,000 that never shows up in the GPU bill (packet.ai, jul/2026).

Which is why published break-even points disagree so much. Against managed frontier APIs, self-hosting on reserved capacity breaks even around 2 to 5 million tokens a day over 12 months (packet.ai, jul/2026). Against a hosted endpoint running the same weights, it moves out to 50 million or more (howaiworks, jul/2026).

And none of this is unique to inference. It is the same trap as provisioned capacity in a data platform, easier to see in Microsoft Fabric capacity units, where the meter runs on what you reserved and not on what you used.

Run it on your own numbers

HOURS_PER_MONTH = 730

def monthly_self_host(gpu_hourly, n_gpus, ops_multiplier):
    """Fixed. Billed by the hour, never by the token."""
    return gpu_hourly * HOURS_PER_MONTH * n_gpus * ops_multiplier

def breakeven_tokens(gpu_hourly, n_gpus, ops_multiplier, price_per_mtok):
    """Monthly token volume where self-hosting matches the API bill."""
    fixed = monthly_self_host(gpu_hourly, n_gpus, ops_multiplier)
    return fixed / price_per_mtok * 1_000_000

def monthly_capacity(tokens_per_sec, n_gpus):
    """Ceiling. Use sustained throughput measured at your real batch size."""
    return tokens_per_sec * n_gpus * 3600 * HOURS_PER_MONTH

Run breakeven_tokens twice, once against your frontier API price and once against a hosted endpoint serving the model you intend to run. Then check both against monthly_capacity, because a break-even you cannot reach is not a break-even.

Below your volume and inside capacity. Self-hosting has a case, so load test the throughput figure before anyone reserves an instance.
Above capacity. Reaching it means adding GPUs, which raises the fixed cost, which pushes break-even further out. At that price the lines never meet.
Far above your volume, measured against the hosted endpoint. The open model wins and running it yourself does not.

What the math does not settle

Cost is loud, but compliance is louder. Data residency, contractual confidentiality, a no-training guarantee or a hard latency floor can close the question before the spreadsheet opens. That bites hardest in Europe, where routing inference through US platforms raises transfer questions under Chapter V of the GDPR (Lyceum Technology, ago/2026). If the weights have to stay inside your network, break-even is beside the point.

Although it cuts the other way too. Self-hosting to satisfy a requirement that a zero-retention agreement already covers buys permanent operational load for a guarantee you had on paper.

So, three options or one routing rule?

In practice it lands as a split. A small open model takes the high-volume, well-shaped work, so classification, extraction and routing, while the frontier API stays reserved for reasoning it cannot handle. Cost drops because the cheap path absorbs the traffic, and quality holds because the expensive path catches the hard cases.

Which turns open versus closed into a routing decision made per workload, rather than an architecture decision made once and defended for two years.

How to run generative AI on SQL tables with Snowflake Cortex

Laura Chicovis — Thu, 03 Sep 2026 02:21:09 +0000

The question that decides whether Cortex belongs in your stack is not what it can do. It is what it costs once the table has millions of rows instead of five.

That question exists because the model call is an ordinary SQL function. It sits inside a SELECT, composes with WHERE, JOIN and GROUP BY, and runs once per row. So this walkthrough goes in that order. Access first, then the functions on a small slice, then the credit consumption before anything scales up.

Before you start

A Snowflake account on Standard edition or above, in a region where Cortex is available.
A running warehouse. The AI functions are billed on the tokens they process, and the query around them still consumes warehouse credits, so you pay for both.
ACCOUNTADMIN once, to grant access.
A table with a text column. The examples use support.tickets, with ticket_id, body and region.

Granting access

Cortex access lives in a database role called SNOWFLAKE.CORTEX_USER. It cannot be granted to a user directly, only to a role, which is the first thing that trips people up:

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS ai_analyst;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE ai_analyst;
GRANT USAGE ON WAREHOUSE ai_wh TO ROLE ai_analyst;
GRANT ROLE ai_analyst TO USER my_user;

Two habits are worth adopting right away. The first is to put AI work on its own warehouse, so the credits appear separated in your billing without any extra tagging. The second is to grant the database role to a purpose-built role instead of something broad like ANALYST, because revoking access later is the only real spending control you have.

The first call

AI_COMPLETE is the general-purpose function. It takes a model name and a prompt, and returns text. Since model availability changes by region and by release, start by listing what your account can actually call:

SHOW CORTEX BASE MODELS;

Then use one of those names below:

USE ROLE ai_analyst;
USE WAREHOUSE ai_wh;

SELECT AI_COMPLETE(
  'claude-4-sonnet',   -- replace with a model from the list above
  'Summarize this support ticket in one sentence: ' || body
) AS summary
FROM support.tickets
LIMIT 5;

Note the LIMIT 5. Without it, the statement runs one model call per row, and a table with two million tickets will happily oblige. Every AI function here behaves the same way, so develop against a LIMIT and remove it only when the prompt is settled.

If you find older material online using SNOWFLAKE.CORTEX.COMPLETE, that is the previous generation. AI_COMPLETE is the updated version, and the same rename ran across the family, so SENTIMENT became AI_SENTIMENT and CLASSIFY_TEXT became AI_CLASSIFY. Copying a 2024 tutorial gets you working but deprecated syntax.

The functions that replace a pipeline

Free-text prompting is the least interesting part of Cortex. The task-specific functions are where the SQL actually gets shorter, because they return typed values you can group and aggregate, instead of prose you would then have to parse.

Classification into your own categories:

SELECT
    ticket_id,
    region,
    AI_CLASSIFY(body, ['billing', 'bug', 'feature request', 'churn risk']) AS category
FROM support.tickets;

Filtering in natural language, inside the WHERE clause:

SELECT ticket_id, body
FROM support.tickets
WHERE AI_FILTER(
    'This message describes a customer threatening to cancel: ' || body
);

Finally, aggregation across rows, which is the function that removes the most code. AI_AGG reads an entire column against a single prompt and is not bound by the model context window, so there is no chunking loop to write:

SELECT
    region,
    AI_AGG(body, 'What are the three most repeated complaints in these tickets?') AS themes
FROM support.tickets
WHERE created_at >= DATEADD('day', -7, CURRENT_DATE())
GROUP BY region;

Reading the bill

Do this on day one rather than after the invoice arrives. Every call is recorded in the Account Usage schema. Run SELECT * against the view once to see its current columns, since it has changed shape more than once, then aggregate:

SELECT
    function_name,
    model_name,
    SUM(token_credits) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY credits DESC;

One trap here: CORTEX_FUNCTIONS_USAGE_HISTORY, the view most existing tutorials point at, is no longer updated. Use CORTEX_AI_FUNCTIONS_USAGE_HISTORY for full coverage, or CORTEX_AISQL_USAGE_HISTORY. Data can take a few hours to appear, so an empty result right after your first query is expected rather than a permissions problem.

Four things that bite later

Preview status. Several of these functions, AI_FILTER and AI_AGG among them, are marked as preview in Snowflake's documentation, and preview means the signature can change under you. So check the current status of every function you depend on before it reaches a scheduled task.

Model and region availability. When the model you want is missing from that SHOW CORTEX BASE MODELS list, the account needs cross-region inference, which is an ACCOUNTADMIN decision made once for the whole account through CORTEX_ENABLED_CROSS_REGION, never per user or per session. The real question there is compliance rather than cost, so if your data cannot leave a jurisdiction, that parameter is the conversation to have before writing any SQL.

Cost scales with rows, not with queries. A warehouse costs the same whether the query touches ten rows or ten million. An AI function does not. The mental model you built tuning SQL stops applying here, and the discipline that replaces it is simple: filter before the function call, never after.

Non-determinism. The same prompt on the same row can return different text on different days. If a downstream table depends on the output, materialize the result and version it, rather than calling the function inside a view that recomputes on every read.

The limit of putting the model in SQL

Inference now sits next to your data, under the same access controls and the same query engine. That covers a large class of work that used to justify a separate service.

Meaning is the part that does not come with it. The model can classify a ticket, but it has no idea what your company counts as an active customer, which revenue definition finance signed off on, or which of four customer_id columns is the governed one. Those definitions live in a semantic layer, and this blueprint on the semantic layer as a single source of business meaning covers how that layer sits between raw tables and anything that answers questions.

Skip it and Cortex still works. You just get fluent answers that quietly disagree with the finance report, produced faster than before.

How to integrate Apache Airflow with OpenLineage for end-to-end traceability

Laura Chicovis — Mon, 31 Aug 2026 23:13:01 +0000

How to integrate Apache Airflow with OpenLineage for end-to-end traceability

By the end of this walkthrough, every DAG run in your Airflow instance emits structured lineage events that name the exact tables each task read and wrote, and you can open a graph and answer "which upstream job produced this number" without grepping a single scheduler log.

That is the whole promise. No manual documentation, no lineage spreadsheet that goes stale in three weeks. The orchestrator reports what it actually did, while it is doing it.

The setup itself is short. What follows is ordered to surface the three failures that usually show up first, before they cost you an afternoon of guessing.

Prerequisites and versions

Apache Airflow 2.11.0 or later, or any Airflow 3.x release. That is the minimum version supported by the current provider distribution.
Python 3.9 to 3.12.
Docker, to run a lineage backend locally.
A Postgres connection in Airflow (postgres_default) if you want to reproduce the SQL example exactly.

The two packages that matter: the Airflow OpenLineage provider extracts Airflow metadata and turns it into events, and openlineage-python transmits them. The client can be upgraded independently of the provider, which is useful when you need a transport fix without touching your Airflow version.

Step 1: the event model, before you install anything

OpenLineage has three objects and one extension mechanism. Skipping this part is why most first integrations produce an empty graph.

Job: something that runs. Your DAG is a job, and each task is also a job.
Run: one execution of a job, with a unique run ID.
Dataset: something read or written. Identified by a namespace and a name.
Facet: an atomic block of metadata attached to any of the above. Schema, SQL text, column-level lineage, run state, and your own custom fields all arrive as facets, and the OpenLineage specification lists the standard ones.

Events fire on state transitions: START, RUNNING, COMPLETE, FAIL, ABORT, OTHER. Lineage is reconstructed downstream by joining datasets across runs, which means dataset identity is the thing that makes or breaks the graph. More on that later, because it is the most common source of a graph with nodes and no edges.

Step 2: install the provider

pip install apache-airflow-providers-openlineage

Official Airflow Docker images may already ship it. Check before adding it to your requirements file:

airflow providers list | grep openlineage

Nothing is emitted yet. The provider stays silent until it knows where to send events.

Step 3: point it at a transport

Start with the console transport. It writes events to the task logs, costs nothing to run, and tells you immediately whether extraction works at all.

export AIRFLOW__OPENLINEAGE__TRANSPORT='{"type": "console"}'

Once you see events in the logs, move to a real backend. Marquez is one option here, and any OpenLineage-compatible backend works. It is the reference implementation of the standard, which makes it the fastest way to get a lineage UI running:

git clone https://github.com/MarquezProject/marquez
cd marquez
./docker/up.sh

The API listens on port 5000, the admin interface on 5001, and the web UI on 3000. On macOS, port 5000 is reserved by the operating system, so run ./docker/up.sh --api-port 9000 and adjust the URL below.

Now switch the transport:

export AIRFLOW__OPENLINEAGE__TRANSPORT='{"type": "http", "url": "http://localhost:5000", "endpoint": "api/v1/lineage"}'
export AIRFLOW__OPENLINEAGE__NAMESPACE='airflow-local'

The same thing in airflow.cfg:

[openlineage]
transport = {"type": "http", "url": "http://localhost:5000", "endpoint": "api/v1/lineage"}
namespace = airflow-local
disabled = False

Set the namespace deliberately. It logically separates producers, so a staging Airflow and a production Airflow do not merge into one graph and lie to you. If you leave it unset, everything lands in default.

For anything beyond local, do not put credentials in airflow.cfg. The provider accepts a Generic Airflow connection ID holding the transport config, including auth, in the connection extra.

Step 4: run a DAG that actually produces lineage

SQL operators are the best place to start, because the provider parses the query and derives inputs, outputs, and column-level relationships without you writing anything:

# dags/openlineage_demo.py
from datetime import datetime

from airflow import DAG
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

with DAG(
    dag_id="openlineage_demo",
    start_date=datetime(2026, 1, 1),
    schedule="@daily",
    catchup=False,
) as dag:
    build_daily_orders = SQLExecuteQueryOperator(
        task_id="build_daily_orders",
        conn_id="postgres_default",
        sql="""
            CREATE TABLE IF NOT EXISTS analytics.daily_orders AS
            SELECT
                o.order_date,
                c.region,
                COUNT(*)      AS order_count,
                SUM(o.amount) AS revenue
            FROM raw.orders o
            JOIN raw.customers c ON c.customer_id = o.customer_id
            GROUP BY o.order_date, c.region;
        """,
    )

Trigger it. The resulting event should list raw.orders and raw.customers as inputs and analytics.daily_orders as the output, with a column-level facet mapping revenue back to o.amount.

Step 5: verify it worked

Three checks, in this order.

Look at the graph. Open http://localhost:3000, find the namespace you configured, and confirm the two source tables connect to the output table.

Check which tasks are even reporting. This is the diagnostic that is easiest to miss. The DagRun START event carries an AirflowJobFacet listing every task in the DAG, each with an emits_ol_events boolean. That tells you ahead of time which operators will stay invisible, instead of leaving you to guess why half the graph is missing.

Understand the silence. An EmptyOperator emits nothing by default, because Airflow does not schedule it the way it schedules real work. Add an on_execute or on_success callback, or a task outlet, if you need it represented. When task-level detail does not matter, the DagRun COMPLETE event carries an AirflowStateRunFacet with the state of every task in the run.

Step 6: cover the operators that report nothing

Automatic extraction covers SQL operators and many provider operators. Your own operators report nothing until you tell them what they touch.

For operators you own, implement the OpenLineage methods directly:

from airflow.models import BaseOperator


class S3ToWarehouseOperator(BaseOperator):
    def __init__(self, *, source_bucket, source_key, target_table, **kwargs):
        super().__init__(**kwargs)
        self.source_bucket = source_bucket
        self.source_key = source_key
        self.target_table = target_table

    def execute(self, context):
        ...  # your copy logic

    def get_openlineage_facets_on_complete(self, task_instance):
        # import locally: top-level Airflow imports here can be cyclical
        # and make extraction fail silently
        from airflow.providers.common.compat.openlineage.facet import Dataset
        from airflow.providers.openlineage.extractors import OperatorLineage

        return OperatorLineage(
            inputs=[
                Dataset(namespace=f"s3://{self.source_bucket}", name=self.source_key)
            ],
            outputs=[
                Dataset(namespace="postgres://warehouse:5432", name=self.target_table)
            ],
        )

Rules worth internalizing:

You must implement at least one of get_openlineage_facets_on_start() or get_openlineage_facets_on_complete(ti). If on_complete is missing, the provider falls back to on_start. There is also get_openlineage_facets_on_failure(ti), which by default reuses the on_complete logic.
Prefer on_complete whenever the real dataset names are only resolved during execute. Reporting a wildcard path on start and never correcting it produces a confident, wrong graph.
Import OpenLineage objects inside the method, never at module level. The listener is instantiated when the worker starts, so a top-level Airflow import can become circular and kill extraction without an obvious error.

For third-party operators you cannot modify, write a custom extractor and register it:

export AIRFLOW__OPENLINEAGE__EXTRACTORS='plugins.extractors.MyCustomExtractor'

Step 7: attach your own context

Since provider version 1.10.0, you can inject arbitrary run facets without touching operator code. Write a function that accepts the task instance and returns a facet dictionary, then register the import paths, separated by semicolons:

export AIRFLOW__OPENLINEAGE__CUSTOM_RUN_FACETS='plugins.ol_facets.ownership_facet'

This is how you get team, cost center, or change-ticket ID onto every event, which turns "who owns this broken pipeline" from a Slack thread into a filter.

What breaks in production

Four settings and one habit account for most of the pain.

include_full_task_info: tempting, and expensive. With it on, all serializable task parameters go into the event. Depending on what you pass to your tasks, single events can reach megabytes.
execution_timeout: cap how long extraction may run so a slow lineage call never becomes a pipeline incident.
dag_state_change_process_pool_size: processes the scheduler uses to handle DAG state changes asynchronously. Worth tuning on busy instances.
emission_policy: the current way to control what gets emitted. The older selective_enable and disable_source_code flags are deprecated in its favor.
Naming discipline: lineage joins on dataset identity. If one job writes analytics.daily_orders and another reads ANALYTICS.DAILY_ORDERS, you get two nodes and no edge. Fix the convention (database.schema.table, environment-scoped namespaces) before you scale, because rewriting identities after the fact means reprocessing history.

Where lineage stops being the answer

What you have now is operational lineage: what ran, what it read, what it wrote, and whether it failed. That is enough to trace an incident backward and to run impact analysis before a schema change.

It is not enough to answer who owns a dataset, whether it is certified, what "active customer" means in business terms, or where PII flows. Those live in a metadata platform, and OpenLineage events are the input to it rather than a replacement for it. If that is the direction you are heading, this breakdown of data governance and end-to-end lineage with a metadata catalog covers how the collection layer and the governance layer divide the work.

The order matters, though. Emit first, catalog second. A catalog fed by hand ages exactly as fast as the documentation you were trying to replace.

Partitioning, clustering, and BI Engine: measuring what each one saves in BigQuery

Laura Chicovis — Thu, 27 Aug 2026 13:18:52 +0000

By the end of this walkthrough you will have two versions of the same table, a repeatable way to price any query before running it, and a query that tells you exactly how many bytes each version billed you. No estimates, no vendor benchmark, just the numbers your own project reports.

I keep running into the same situation: someone turns on partitioning, the bill does not move, and the conclusion becomes "partitioning does not work here". Usually partitioning worked fine and the queries were never written to use it. The only way to settle that is to measure both sides.

What you need

A Google Cloud project with billing enabled and the BigQuery API on.
The bq CLI from the Google Cloud SDK, or the BigQuery console if you prefer clicking. Every snippet below is standard GoogleSQL, so both work.
Permissions to create datasets and tables, plus bigquery.jobs.list if you want the billing history query at the end.
Around 5 to 10 GiB of scanning budget. On-demand pricing gives you the first 1 TiB per month free and charges $6.25 per TiB after that in the US multi region (Google Cloud, BigQuery pricing documentation), so this exercise costs cents, not dollars.

One warning before you start: the queries that build the optimized table are themselves billed. Dry run them first.

bq mk --location=US --dataset bq_cost_lab

Step 1: Build the baseline

I use the public Stack Overflow dataset because everyone can reproduce it. This copies a slice of it into a plain table with no partitioning and no clustering.

CREATE OR REPLACE TABLE `bq_cost_lab.questions_plain` AS
SELECT
  id,
  creation_date,
  owner_user_id,
  score,
  view_count,
  tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date >= TIMESTAMP('2018-01-01');

Note what I did not copy. The body column holds the full HTML of every question and it is the single most expensive column in that table. BigQuery charges according to the data processed in the columns you select, even when you set an explicit LIMIT (Google Cloud, BigQuery pricing documentation), so column selection is the cheapest optimization available and it costs nothing to implement.

Step 2: Price the query before you run it

The dry run flag returns the byte estimate without executing anything and without charging you.

bq query --use_legacy_sql=false --dry_run \
'SELECT owner_user_id, COUNT(*) AS questions
 FROM `bq_cost_lab.questions_plain`
 WHERE DATE(creation_date) BETWEEN "2018-03-01" AND "2018-03-07"
 GROUP BY owner_user_id'

Write down the number it gives you. That is your baseline: [CONFIRMAR: rodar o dry run e anotar o valor observado] bytes. Divide it by 1,099,511,627,776 to get TiB, then multiply by 6.25 for the dollar figure. The date filter in that query saves you nothing yet, because a plain table has no partitions to skip.

Step 3: Partition on the column you actually filter by

Partitioning splits the table into physical blocks, and a qualifying filter on the partitioning column lets BigQuery scan the matching partitions and skip the rest, a process the documentation calls pruning (Google Cloud, introduction to partitioned tables).

CREATE OR REPLACE TABLE `bq_cost_lab.questions_tuned`
PARTITION BY DATE(creation_date)
CLUSTER BY owner_user_id, score
OPTIONS (require_partition_filter = TRUE) AS
SELECT * FROM `bq_cost_lab.questions_plain`;

Three decisions are packed into those four lines.

Daily granularity, not hourly. A partitioned table is capped at 10,000 partitions [CONFIRMAR: conferir o número atual na página Quotas and limits, o limite subiu de 4.000 em 2024] (Google Cloud, BigQuery quotas and limits). Daily partitions give you 27 years of runway. Hourly partitions give you 416 days, and hitting that ceiling in production is a migration, not a config change.

require_partition_filter = TRUE. This rejects any query that does not filter on the partitioning column (Google Cloud, managing partitioned tables). It is one line, it is reversible with an ALTER statement, and it is the difference between a table that saves money and a table that saves money until the first person forgets the WHERE clause. Turn it on unless you have a specific reason not to.

The partition filter has to be a constant expression. Filtering on a column that is not the partition key prunes nothing, and neither does a filter whose value BigQuery cannot resolve before execution. This is where most disappointed partitioning stories end.

Partitioning also has a storage side that rarely gets mentioned. Long term storage drops the rate by roughly half after 90 days without modification, and each partition of a partitioned table is evaluated separately for that discount (Google Cloud, BigQuery pricing documentation). On a plain table, one late arriving row resets the timer for everything. On a partitioned table, it resets one day.

Step 4: Cluster in the order you filter

Clustering sorts storage blocks by the values in the clustered columns, and queries that filter or aggregate on those columns scan only the relevant blocks instead of the full table or partition (Google Cloud, introduction to clustered tables).

You get up to four clustering columns, and the order determines the sort order, so the most frequently filtered column goes first (Google Cloud, creating clustered tables). In my snippet, owner_user_id comes before score because equality filters on user are common and selective, while score usually shows up as a range filter on top of that. Reverse them and the same query prunes worse.

Now the part that surprises people. Run a dry run against the clustered table and compare it to the previous one:

# replace 12345 with an owner_user_id that exists in your slice
bq query --use_legacy_sql=false --dry_run \
'SELECT owner_user_id, COUNT(*) AS questions
 FROM `bq_cost_lab.questions_tuned`
 WHERE DATE(creation_date) BETWEEN "2018-03-01" AND "2018-03-07"
   AND owner_user_id = 12345
 GROUP BY owner_user_id'

The estimate drops because of partition pruning, but it does not reflect the clustering at all. When you query a clustered table you do not get an accurate cost estimate before execution, because the number of storage blocks to scan is not known until the query runs, and the final cost is based on the blocks actually scanned (Google Cloud, introduction to clustered tables). The dry run on a clustered table is an upper bound. Treating it as the answer is how teams conclude that clustering did nothing.

Step 5: Verify with billed bytes, not estimates

Run both versions for real, then ask the metadata what happened.

SELECT
  job_id,
  creation_time,
  cache_hit,
  total_bytes_processed,
  total_bytes_billed,
  ROUND(total_bytes_billed / POW(1024, 4) * 6.25, 4) AS approx_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND statement_type = 'SELECT'
ORDER BY creation_time DESC;

total_bytes_billed is the number that reaches your invoice. Two details make it diverge from the estimate in ways worth knowing: charges are rounded up with a minimum of 10 MB per table referenced and 10 MB per query, and you are not charged for queries that fail or that return cached results (Google Cloud, BigQuery pricing documentation). If cache_hit is true, you measured nothing. Change a literal and run it again.

Record the pair. Baseline billed bytes: [CONFIRMAR: valor observado]. Tuned billed bytes: [CONFIRMAR: valor observado]. That ratio is the only savings figure I would put in a report.

This is the same argument the observability people have been making for years with logs, metrics, and traces. Cost is one more signal your platform emits. If nobody queries it, nobody knows what changed.

Step 6: Decide whether BI Engine earns its keep

BI Engine caches table data in memory to accelerate SQL. When it accelerates a query under on-demand pricing, the stage that reads table data is charged for zero scanned bytes (Google Cloud, BigQuery pricing documentation). That sounds like free money until you look at how it bills: $0.0416 per GiB hour, charged per project where you reserved capacity, whether or not anyone runs a query.

So the math is a subscription against a metered service. A 10 GiB reservation runs about $0.42 per hour, close to $304 per month at 730 hours. At $6.25 per TiB, that is the equivalent of roughly 48 TiB of scanning. If the dashboards hitting that reservation do not scan near that volume, the reservation loses.

-- reserve capacity, then confirm queries are actually being accelerated
SELECT
  job_id,
  bi_engine_statistics.bi_engine_mode,
  total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND bi_engine_statistics IS NOT NULL
ORDER BY creation_time DESC;

If bi_engine_mode comes back as DISABLED or PARTIAL for most of your traffic, you are paying for memory that is not accelerating anything. Two things commonly cause that: the working set does not fit the reservation, or the queries use operations the accelerator does not cover.

One detail that changes the calculation for larger shops: BigQuery editions commitments bundle BI Engine capacity at no extra cost, starting at 5 GiB for 100 slots and scaling to 100 GiB at 2,000 slots (Google Cloud, BigQuery pricing documentation). If you already hold a commitment, some of that capacity may be sitting unclaimed.

What this does not fix

Partitioning, clustering, and BI Engine all reduce bytes scanned. None of them touch the two costs I see grow fastest.

The first is storage on tables nobody queries. Active logical storage runs $23.55 per TiB per month in the US multi region (Google Cloud, BigQuery pricing documentation), and partition expiration is the cheapest cleanup available. The second is repeated scanning of the same aggregate by twelve dashboards, which is a materialized view problem rather than a partitioning one.

There is a third one that no table setting solves. If the pipeline writes duplicates and someone rebuilds the table twice a week to fix them, you are paying for the rework, not for the analytics. That is a data quality problem, and it gets solved upstream, not in the DDL.

The habit that has saved me the most is boring: dry run first, check total_bytes_billed after, and never accept a savings number that nobody measured. Everything above is just the mechanism.

If you want the follow up, tell me in the comments whether materialized views or slot reservations are the harder call on your side.