SQL for Business Intelligence: The Silent Wrong Answers

In SQL queries for business intelligence, a query that fails is rarely the real problem. It announces itself, someone fixes it, and the report is correct by lunchtime.

The problem is the query that returns a number. It runs, it produces something plausible, and it goes into a board pack. Often nobody checks it, because there was nothing to prompt a check: no error, no warning, no null where a number should be. Some of the costliest SQL bugs in business intelligence are the ones that look exactly like success.

In business intelligence, SQL turns warehouse tables into the figures a dashboard shows, so its mistakes travel straight into decisions. This guide covers the patterns that fail silently, each tied to primary documentation (the vendor’s own, or the dimensional-modeling reference), because several differ between warehouses, which is itself one of the traps.

On this page

• Why does the last-value window function not return the last value?
• The NOT IN trap that empties your report
• How does a LEFT JOIN quietly become an INNER JOIN?
• Why does joining two fact tables inflate the totals?
• Date filters that drop the last day of the month
• Why last year’s numbers change
• What actually drives query cost?
• Write queries that fail loudly
• Frequently asked questions

Key Takeaways

• With ORDER BY and no explicit frame, LAST_VALUE returns the current row’s value (or that of its last tie), not the partition’s last value, on engines using the standard default frame, such as PostgreSQL, whose documentation calls this “likely to give unhelpful results.”
• Snowflake documents a different, non-ANSI default, so the identical query returns different answers on different warehouses. Snowflake recommends avoiding implicit window frames, and even an explicit frame gives a deterministic “last” only when the ORDER BY keys are unique.
• A WHERE … NOT IN (subquery) filter returns no rows at all once the subquery contains a single NULL, and raises no error. NOT EXISTS does not have this problem and is the standard fix.
• Moving a filter on the right-hand table from ON to WHERE turns a LEFT JOIN into an INNER JOIN in effect, with no syntax change to announce it. PostgreSQL documents it with a three-rows-versus-one-row example.
• BETWEEN is inclusive at both ends, so a month filter on a timestamp column drops everything after midnight on the final day.
• Query cost is a direct function of bytes processed on BigQuery on-demand ($6.25 per TiB) and a function of compute time on Snowflake and Databricks, so cost advice does not transfer between them.

Why does the last-value window function not return the last value?

This is the clearest example of the category, because the documentation admits it.

PostgreSQL’s window function reference states that first_value, last_value and nth_value consider only the rows in the window frame, which by default runs from the start of the partition through the last peer of the current row, and then says directly that this “is likely to give unhelpful results for last_value.”

In practice, LAST_VALUE(price) OVER (PARTITION BY category ORDER BY date) returns the price on the row you are looking at, or on the last row that shares its date. Not the last price in the category; PostgreSQL, DuckDB and SQLite all behave this way in our tests. The query is syntactically perfect and the answer is wrong in a way that is nearly invisible, because on the final date of each partition it is right.

The fix is to state the frame explicitly, and to make the order unique:

LAST_VALUE(price) OVER (
  PARTITION BY category ORDER BY date, price_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

The second sort key matters as much as the frame. If two prices share a category’s final date, ORDER BY date alone does not say which one is last. PostgreSQL warns that ROWS frames “can produce unpredictable results if the ORDER BY ordering does not order the rows uniquely”, and in our tests the same four rows, loaded in a different order, returned a different “last” price on all three engines. Add a column that is unique within the partition and whose order means “latest”, such as a record ID.

The default also differs between engines. Snowflake documents that for functions such as FIRST_VALUE, LAST_VALUE and NTH_VALUE, its default frame is the entire window, and states explicitly that “this behavior does not comply with the ANSI standard.”

So a query copied from a PostgreSQL tutorial into Snowflake changes meaning silently, and so does the reverse. Snowflake’s own recommendation is the right general rule: avoid implicit window frames and define the frame explicitly. Its LAST_VALUE page adds the tie rule: the result is “fully deterministic only if the keys in the ORDER BY clause make each row unique.”

A related divergence catches the “carry the last known value forward” pattern. Snowflake and Databricks both document IGNORE NULLS on LAG and LEAD, which skips null rows when counting the offset. PostgreSQL 18, the current release, documents that the option is not implemented; the development documentation for PostgreSQL 19, in beta in September 2026, adds it. Until then, the same fill-forward logic is one clause on one engine and a subquery on the other, and on both it needs the same unique ordering to give a stable answer.

The NOT IN trap that empties your report

PostgreSQL’s documentation on subquery expressions states that if there are no equal right-hand values and at least one right-hand row yields null, the result of NOT IN is null rather than true.

A query like this:

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM excluded_customers)

returns zero rows the moment excluded_customers.customer_id contains a single NULL, and no error is raised. Customers whose ID is on the exclusion list evaluate to false, every other customer to null, and WHERE keeps only rows that are true, so nothing survives.

The standard fix is NOT EXISTS. PostgreSQL documents that the result of EXISTS depends only on whether any rows are returned, not on their contents:

SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_customers e
  WHERE e.customer_id = c.customer_id
)

The pattern this breaks, “everyone except the people in that other table”, is a routine one in business reporting. A suppression list, an exclusion table, a set of test accounts: all are this shape, and any of them can acquire a NULL through a failed load or an optional field.

Two related null behaviors corrupt numbers rather than emptying reports. COUNT(column) counts only non-null values while COUNT(*) counts rows, so a tile built on COUNT(email) silently undercounts every customer without an email address. And AVG computes the mean of non-null values only: it is SUM(non-null) / COUNT(non-null), not SUM / COUNT(*). If your mental model is that a missing value counts as zero, the average you are reading is divided by fewer rows than you think, so it sits further from zero than the figure you meant: higher for revenue, more negative for losses.

Integer division is the dialect version of the same problem. PostgreSQL documents that dividing one integer by another “truncates the result towards zero”, so a conversion rate computed as orders / visits on integer columns comes out as 0 whenever there are fewer orders than visits. BigQuery returns a FLOAT64 and Snowflake a decimal for the same division, so a query that is right on one warehouse returns zeros on another. Cast one side to a decimal before you divide.

How does a LEFT JOIN quietly become an INNER JOIN?

PostgreSQL’s documentation puts this plainly: a restriction in the ON clause is processed before the join, a restriction in WHERE is processed after it. “That does not matter with inner joins, but it matters a lot with outer joins.”

The documented example is concrete. t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx' keeps all three rows of t1, filling nulls where t2 did not match. Move that same predicate to WHERE t2.value = 'xxx' and the result is one row, because the unmatched rows produced nulls, and the null rows fail the WHERE test.

The report symptom is specific and recognisable: a category that should appear with a zero simply vanishes from the chart, and a missing bar is easy to overlook. If your “all products including those with no sales this month” report only ever shows products with sales, this is one of the first things to check.

Why does joining two fact tables inflate the totals?

Oracle’s documentation for its Discoverer BI tool describes this as a fan trap: most commonly, a master table joined to two or more detail tables independently. The results are, in its words, relationally correct but obviously wrong.

The worked example is unambiguous. An account table joined to both a sales table and a budget table, then summed in one query, returns sales of 800 for an account whose correct total is 400, because the tables are joined into one intermediate result before the aggregation runs and each sales row is repeated once per matching budget row. Accounts with a single budget row still total correctly, which is part of why the error survives review.

There are two documented fixes and they are the same fix at different layers:

The second is what Kimball calls drilling across: separate queries whose row headers consist of identical conformed attributes, aligned by a sort-merge on those attributes. Kimball’s own guidance on joining fact tables directly is blunt: it is impossible to control the cardinality of the answer set, and incorrect results will be returned.

Where the same facts are combined in more than one report, both fixes belong in the model rather than in each query. When a semantic layer owns the grain and the conformed attributes, the fan trap stops being something every analyst has to remember, which is the practical argument for holding definitions in one place instead of in each report.

Date filters that drop the last day of the month

BETWEEN is documented as inclusive at both ends: a BETWEEN x AND y is equivalent to a >= x AND a <= y.

On a date column that is fine. On a timestamp column it is a silent data loss, because '2026-01-31' resolves to 2026-01-31 00:00:00. So this:

WHERE order_ts BETWEEN '2026-01-01' AND '2026-01-31'

captures exactly one instant of the 31st and discards the rest of the day. January is short by almost a full day of orders. The month looks slightly weak, and a slightly weak month rarely gets investigated.

The safe pattern is half-open: inclusive start, exclusive end:

WHERE order_ts >= '2026-01-01' AND order_ts < '2026-02-01'

PostgreSQL’s range types use the same convention: the two-argument range constructors build a range with an inclusive lower bound and an exclusive upper bound.

On a timestamp with time zone column the boundaries also need a time zone. PostgreSQL reads a literal with no zone in the session’s time zone, so in our test an order placed at 23:30 UTC on 31 January fell inside January in a UTC session and outside it in an Amsterdam one; typed TIMESTAMP literals did not change that. Write the offset into the boundary, as in '2026-02-01 00:00:00+00', when the report is defined in UTC.

Two related boundary problems are worth checking in any reporting layer. First, DATE_TRUNC on a timestamp with time zone truncates relative to the session’s time zone, so the same query run under two different session settings assigns events near midnight to different days, which produces two dashboards that disagree about yesterday. Second, PostgreSQL’s week fields follow ISO 8601, where weeks start on Monday, while some BI tools and retail calendars start the week on Sunday. Two correct systems then disagree about every weekly total by one day’s data, and the mismatch is worse than an obvious bug because the daily figures reconcile perfectly.

The deeper version of this is the column type itself. timestamp without time zone is documented to silently ignore any time zone indication on input. A pipeline that loads local event times with their offsets into that type keeps the clock time and drops the offset, so the UTC instant can no longer be recovered, and every downstream truncation inherits the loss.

Why last year’s numbers change

A report re-run six months later that shows different figures for a closed period can be a dimension problem rather than a data problem.

Kimball’s guidance on slowly changing dimensions describes the mechanism. A type 1 change overwrites the attribute, and the documentation is explicit that this destroys history, so a report grouping on it shows today’s values for old periods. A type 2 change instead adds a new dimension row with effective and expiry dates and a current-row flag, which means each fact row’s foreign key permanently points at the version of the dimension that was current when the fact occurred.

That is what makes point-in-time reporting correct by construction. A 2024 sale joined through its stored surrogate key lands on the 2024 version of the customer’s region, not today’s.

The failure happens when facts are joined to dimensions on a natural key (customer ID) against the current dimension row. Every historical fact then inherits today’s attribute value, and regional totals for a closed quarter quietly change whenever someone moves a customer between territories. No data was altered. The join simply asked the wrong question.

This is worth testing deliberately, because it is invisible in normal use: re-run a report for a period that closed a year ago and compare it against the version issued at the time. If they differ and no restatement occurred, check the dimension joins first. Late-arriving facts and correction loads can produce the same symptom, so compare the period’s fact row counts and totals as well before settling on a cause.

What actually drives query cost?

The patterns above return a wrong number; cost returns the right one at a price that went unchecked. Cost advice does not transfer between warehouses, and this is documented rather than folklore.

BigQuery on-demand bills on bytes processed (its capacity pricing bills slot-hours instead). The pricing page is explicit: $6.25 per TiB at the US list price in September 2026, with the first 1 TiB each month free per account; you are charged for the data in the columns you select, a LIMIT does not reduce it, and results served from cache are not charged. Partitioning and clustering can cut the bytes a query reads.

An illustration, with the assumptions stated: a tile whose query reads 500 GiB of columns and refreshes hourly processes about 352 TiB in a 30-day month, roughly $2,190 at list price, assuming this tile gets the account’s whole free TiB. If partitioning lets it read only the last week, say 25 GiB, the month falls to about 17.6 TiB, or roughly $104. It also assumes the table changes before each refresh, so the cache never applies; a rolling CURRENT_DATE filter would miss it anyway, because BigQuery does not cache queries that use non-deterministic functions.

Snowflake bills warehouse compute time. A standard warehouse is billed per second with a sixty-second minimum each time it starts, at a credit rate that doubles with each size step; the pricing page listed on-demand prices of $2.00 to $4.00 per credit on AWS US East in September 2026, depending on edition. Other compute types have other rules: interactive warehouses bill a one-hour minimum each time they resume, and their auto-suspend cannot be set below 24 hours. Scanning less data helps indirectly, by finishing sooner or allowing a smaller warehouse.

Databricks bills compute in Databricks Units (DBUs). Classic and pro SQL warehouses run in your own cloud account, so the cloud provider bills the virtual machines on top; serverless runs in Databricks’ account and is covered by the DBU rate. Data skipping, driven by per-file minimum and maximum statistics collected when data is written, reduces what a query reads.

That difference inverts a common piece of advice. BigQuery lists automatic reclustering among its free operations and documents that it has no effect on query capacity. Snowflake’s documentation says the opposite about its own equivalent: clustering keys “are not intended for all tables due to the costs of initially clustering the data and maintaining the clustering.” Databricks’ automatic liquid clustering changes keys only when predicted savings outweigh the clustering cost.

Any cost-optimization playbook should therefore name the engine it was written for.

Frequently asked questions

Should transformation logic live in SQL or in the BI tool?

Logic that more than one report depends on belongs in one governed layer that every report reads from: a modeled SQL layer in the warehouse, or a semantic layer or shared model on top of it. Logic specific to one view can live in the tool. The test is whether a second team would need the same calculation; if so, copying it into two dashboards leaves two definitions that someone has to keep in step.

Do these problems go away with a semantic layer?

A semantic layer that generates drill-across queries handles the fan trap well, because it queries each fact table separately and merges the results. It does not protect you from NOT IN with nulls, BETWEEN on timestamps, or current-dimension joins: those live in the SQL underneath it. It moves the risk rather than removing it.

How do you test for these bugs?

Assertions on the data rather than review of the query. The table under “Write queries that fail loudly” maps one check to each pattern, from row counts across a join to a closed period re-run against its issued figures, and all of them can run automatically on every load.

Is QUALIFY available everywhere? No. In standard SQL, window functions are not allowed in WHERE, GROUP BY or HAVING (PostgreSQL’s documentation says they are “forbidden elsewhere”), so filtering on a window result normally needs a subquery. Snowflake, BigQuery and Databricks all document a QUALIFY clause that does this directly; PostgreSQL’s SELECT reference, including the PostgreSQL 19 development version, has no QUALIFY clause.

Which of these matters most?

There is no measured ranking, so this is a judgment: start with NOT IN on a nullable subquery, because its failure is total and silent, then WHERE-versus-ON filter placement, because a missing category in a chart is the kind of error readers rarely report. After that, prioritize by how often each pattern appears in your own queries.

Write queries that fail loudly

The unifying property of everything above is that the wrong answer arrives looking like a right one.

That suggests a working rule that has nothing to do with SQL skill: make correctness testable rather than reviewable. Each check below turns one silent failure from this article into a loud one:

Most of these checks are quick to write and tedious to repeat by hand, so they belong in the pipeline rather than in a reviewer’s memory. The harder part is deciding which numbers are authoritative, and what has to reconcile to what; that is data foundation work rather than reporting work. If your warehouse and your reports already disagree, a current-state review of where the SQL and the dashboards part company is the shortest published starting point: a two-to-four-week assessment of the current state and its gaps, ending in a roadmap with prioritized steps.

The queries in this article are not difficult to fix once seen. The difficulty is that they never ask to be looked at.

Independence note: Veralytiq has no commercial relationship with any vendor named in this article.

Related Articles

• Data Visualisation for BI: Encodings That Hold Up — Choosing the right visual for each question
• Business Intelligence Exercises: Practice Examples & Templates — Exercises to practise business intelligence skills

Sources

Primary vendor and standards documentation

• PostgreSQL 18, window functions, window function tutorial and SELECT reference: default frame behaviour, the last_value caveat, the warning on ROWS frames over a non-unique ordering and where window functions are allowed. PostgreSQL 19 development documentation for IGNORE NULLS.
• PostgreSQL, subquery expressions: NOT IN null semantics and NOT EXISTS.
• PostgreSQL, aggregate functions (count and avg null handling) and mathematical operators (integer division).
• PostgreSQL, table expressions: the documented ON versus WHERE example.
• PostgreSQL, date/time functions, date/time types, comparison functions and range types.
• Snowflake, window function syntax and LAST_VALUE (the documented departure from the ANSI default, and the rule on ties), LAG, QUALIFY and arithmetic operators.
• Snowflake, warehouse billing, interactive warehouses, pricing options (list prices as of September 2026), Service Consumption Table, micro-partitions and clustering and clustering keys.
• Google Cloud, BigQuery pricing (list prices as of September 2026), cached results, QUALIFY, partitioned tables, clustered tables and operators (division result types).
• Databricks, SQL warehouse types, data skipping, liquid clustering, lag and QUALIFY.
• Oracle, Discoverer administrator’s guide: fan traps: definition and the 800-versus-400 worked example.

Dimensional modelling

• Kimball Group, type 1 and type 2 slowly changing dimensions, drilling across and multipass SQL.

A note on what is not here

As of 3 October 2026, we looked for credible survey data on how analysts divide their time between SQL and BI-tool modelling. The figures we found came from vendor-run surveys by companies selling SQL-centric tooling, so none is cited. The argument in this article does not depend on one.

The examples were run on small test tables in PostgreSQL 16, DuckDB 1.5 and SQLite 3.45 in September 2026 (the time-zone and week examples on PostgreSQL only). Snowflake, BigQuery and Databricks behaviour is taken from their documentation, not from test runs.