Communicating Query Assumptions — Communicate query assumptions and limitations (conceptual) - SQL

SECTION 1

Historical Context & Motivation

The practice of communicating query assumptions did not emerge from a single invention but rather from decades of hard-won lessons in database engineering, business intelligence, and data governance. Early relational database systems in the 1970s and 1980s treated SQL queries as self-contained artifacts—write a query, get results, ship a report. As organizations began to rely on these reports for strategic decisions, the gap between what a query actually computed and what stakeholders believed it computed grew into a critical source of organizational risk.

1970 • Codd's Relational Model

Edgar F. Codd publishes his seminal paper introducing the relational model. SQL's declarative nature means the how of data retrieval is abstracted away, inadvertently hiding assumptions about schema, NULLs, and join semantics from end users.

1990s • Rise of Data Warehousing

Ralph Kimball and Bill Inmon popularize dimensional modeling and enterprise data warehouses. As reporting layers grow, the distance between the analyst writing SQL and the business user reading a dashboard widens, making undocumented assumptions increasingly dangerous.

2003 • Sarbanes-Oxley & Data Governance

SOX compliance mandates auditable financial reporting. Organizations discover that undocumented query logic—filter choices, NULL handling, deduplication strategies—can lead to material misstatements, prompting formal data governance frameworks.

2010s • Self-Service BI & Data Democratization

Tools like Tableau, Looker, and dbt lower the barrier to writing analytical queries. Non-engineers produce reports, dramatically increasing the probability that assumptions go unstated. The dbt project introduces documentation-as-code practices for SQL.

2020s • Data Contracts & Semantic Layers

The industry coalesces around data contracts, metric layers, and formal metadata catalogs. Communicating assumptions transitions from a soft skill to an engineering discipline with tooling support.

The recurring pattern across these decades is clear: as data systems grow in complexity and the audience for query results broadens, the cost of hidden assumptions compounds. A query that silently excludes NULL revenue records or assumes a fiscal year starting in January can mislead an entire quarterly earnings call. The central question this lesson addresses is: how do we systematically identify, document, and communicate the assumptions and limitations embedded in every analytical SQL query?

SECTION 2

Core Principles & Definitions

Before we can communicate assumptions effectively, we need a shared vocabulary. A query assumption is any unstated belief about the data, schema, or business context that, if violated, would change the meaning or correctness of the query results. A query limitation is a known boundary on the query's applicability—cases it deliberately does not handle, edge conditions it ignores, or precision it sacrifices for performance. Both are forms of epistemic metadata: information about what you know and don't know, attached to the artifact that produces the result set.

Data Assumptions

Beliefs about data quality, completeness, and freshness. Examples: 'All orders have a non-NULL customer_id,' 'The events table is append-only with no late-arriving data,' or 'Timestamps are in UTC.'

Schema Assumptions

Beliefs about table structures and relationships. Examples: 'The users table has a unique constraint on email,' 'orders.user_id is a valid foreign key into users.id,' or 'The status column uses an ENUM with exactly five values.'

Business Logic Assumptions

Interpretive decisions about domain semantics. Examples: 'Active user means at least one login in the past 30 days,' 'Revenue is recognized at shipment date, not order date,' or 'A churned customer can reactivate.'

Temporal Assumptions

Beliefs about time windows, granularity, and recency. Examples: 'The fiscal year starts April 1,' 'Data is current as of last midnight,' or 'Weekly aggregations use ISO weeks (Monday start).'

Known Limitations

Explicit boundaries on query scope. Examples: 'Excludes internal test accounts,' 'Does not handle multi-currency conversion,' or 'Approximate count using HyperLogLog with ~2% error margin.'

✦

KEY TAKEAWAY

Think of a SQL query like a scientific paper's methodology section. The query itself is the experiment protocol, and the assumptions and limitations are the caveats and boundary conditions that any reviewer needs to evaluate whether the conclusions are valid. A paper that omits its limitations is unreliable; a query that hides its assumptions is no different.

SECTION 3

The Assumption Communication Pipeline

The following diagram illustrates the full lifecycle of assumption communication in analytical SQL work. On the left, the analyst encounters raw data and encodes decisions into a query. In the center, assumptions are surfaced and documented. On the right, stakeholders receive both the result and its epistemic context—enabling informed decision-making rather than blind trust.

The pipeline shows how raw data flows through query authoring into a result set. The golden Assumption Documentation Layer intercepts implicit decisions and makes them explicit. Without this layer (shown in red), stakeholders receive results without context—a recipe for misinterpretation.

Notice that the documentation layer is not merely an afterthought appended to the query. It is a parallel artifact produced during query authoring, ideally embedded in the same version-controlled file. The dashed lines from "Raw Data" and "Result Set" into the documentation layer indicate that assumptions come from both directions: some are about the input (data quality, schema constraints), and others are about the output (interpretation of metrics, precision guarantees). A well-documented query delivers what the green box at the bottom represents—stakeholder context—where the consumer of the data understands not just the answer but the conditions under which the answer is valid.

SECTION 4

Taxonomy of Common SQL Assumptions

Although this lesson is conceptual rather than mathematical, it is useful to formalize the categories of assumptions that arise in analytical SQL. Every query encodes decisions at multiple layers, and a systematic taxonomy helps analysts avoid blind spots. The following breakdown organizes assumptions by where in the query lifecycle they originate and how they can be surfaced.

Layer 1 — Data Layer Assumptions

At the data layer, assumptions concern the state of the underlying tables before the query executes. Completeness asks: are all expected records present? A query computing monthly revenue assumes that all order records for the month have been loaded; if an ETL pipeline has a two-hour lag, the result for the current day is understated. Uniqueness asks: does a join key actually identify one record, or could duplicates inflate aggregates? Validity asks: are values within expected ranges? A negative quantity or a timestamp in the year 1970 may signal corrupt data. Freshness asks: when was the data last updated? Stale data can silently invalidate time-sensitive metrics.

Layer 2 — Query Logic Assumptions

At the query logic layer, assumptions are baked into the SQL itself. The choice between INNER JOIN and LEFT JOIN implicitly assumes whether unmatched rows matter. A WHERE status != 'cancelled' filter assumes that 'cancelled' is the only status to exclude—but what about 'refunded' or 'test'? The use of COALESCE(revenue, 0) assumes that NULL revenue means zero rather than unknown. Each of these decisions is a query logic assumption that should be documented.

Layer 3 — Business Interpretation Assumptions

The most dangerous assumptions live at the business interpretation layer because they exist at the boundary between code and semantics. What counts as an active user? Is revenue gross or net? Does monthly churn include voluntary and involuntary cancellations? Two analysts can write syntactically correct SQL against the same tables and produce different numbers simply because they encode different business definitions. Without explicit documentation, there is no way for a stakeholder to adjudicate which number is correct—or even to know they disagree.

⚠

The NULL Trap

One of the most pervasive hidden assumptions involves NULL values. SQL's three-valued logic means that NULL != 'active' evaluates to NULL (not TRUE), so a WHERE status != 'active' filter silently drops rows where status is NULL. If your report excludes 8% of users without mentioning it, your stakeholders are making decisions on incomplete data.

SECTION 5

Documentation Strategies & Patterns

Identifying assumptions is only half the challenge; the other half is encoding them in a form that reaches the right audience at the right time. Documentation strategies range from inline SQL comments—low-cost and co-located with the query—to formal data catalog entries that are searchable across an organization. The most effective approach is layered, using multiple channels simultaneously to ensure that both technical peers and non-technical stakeholders encounter the relevant caveats.

The documentation pyramid layers five strategies from most co-located (inline comments, closest to the code) to most discoverable (data catalog, searchable by any stakeholder). Effective teams operate on at least two layers simultaneously.

Strategy	Audience	Example
Inline SQL comments	Fellow analysts, future you	`-- Excluding test accounts (user_id < 1000)`
Query header block	Analyst team, code reviewers	Structured comment at top of file listing author, date, assumptions, known limitations
README / wiki	Cross-functional team	Markdown file in the same repository explaining business context, data lineage, and caveats
Semantic layer	Business users, BI consumers	LookML or dbt metric definition with built-in description and caveats
Data catalog	Entire organization	Searchable entry in tools like Atlan, DataHub, or Alation with tags, owners, and quality badges

Documentation strategies with their primary audiences and examples

SECTION 6

Worked Example — Documenting a Revenue Query

Consider a common analytics task: computing total monthly revenue. Below, we walk through the process of writing the query, identifying its hidden assumptions, and producing a documented version that communicates those assumptions to both technical and non-technical stakeholders.

Documenting Monthly Revenue Assumptions

1
Step 1 — Write the Initial Query

A first-draft query might look like this:SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_revenue FROM orders WHERE status = 'completed' GROUP BY 1 ORDER BY 1;This query appears straightforward, but it encodes at least six implicit assumptions.
2
Step 2 — Enumerate Hidden Assumptions

Systematically scan each clause for implicit decisions. FROM orders: assumes the orders table is complete and deduplicated. order_date: uses order date rather than shipment or payment date for revenue recognition. status = 'completed': excludes pending, refunded, and partially fulfilled orders. SUM(amount): assumes amount is in a single currency and that NULL amounts should be excluded (SQL's SUM ignores NULLs). DATE_TRUNC: assumes order_date is stored in the desired timezone and that calendar months are the correct granularity.

Six assumptions identified across data, logic, and business layers.
3
Step 3 — Add Inline Documentation

Embed assumptions directly into the SQL using a structured header block and inline comments:/* Monthly Revenue Report Author: data-team@example.com Last updated: 2025-01-10 ASSUMPTIONS: 1. Revenue is recognized at order_date (not shipment/payment date). 2. Only 'completed' orders are included; refunded and pending orders are excluded. 3. The `amount` column is in USD. Multi-currency orders are not converted. 4. NULL amounts are excluded by SUM() — these represent ~0.3% of rows. 5. order_date is stored in UTC; no timezone conversion is applied. LIMITATIONS: - Does not account for partial refunds applied after completion. - Late-arriving orders (ETL lag ~2 hrs) may undercount the current day. - Test accounts (org_id = 1) are NOT filtered; ~$12K/month impact. */ SELECT DATE_TRUNC('month', order_date) AS month, -- Calendar month, UTC SUM(amount) AS total_revenue -- USD only, NULLs excluded FROM orders WHERE status = 'completed' -- Excludes: pending, refunded, cancelled GROUP BY 1 ORDER BY 1;

Query now communicates five assumptions and three limitations within the file itself.
4
Step 4 — Create Stakeholder-Facing Documentation

For non-technical consumers (executives, product managers), translate the SQL-level assumptions into business language. A wiki entry or data catalog description might read: 'This metric shows completed-order revenue in USD, recognized at the time the order was placed. It does not include refunds, pending orders, or non-USD transactions. Current-day figures may be understated by up to 2 hours due to data pipeline latency.' This plain-language summary ensures that a VP reading a dashboard does not assume the number includes refunded orders.

Stakeholders receive context alongside data—enabling informed interpretation.

✦

KEY TAKEAWAY

Think of assumption documentation like a software API's contract. The query is the function, the result set is the return value, and the assumptions are the preconditions and postconditions. Just as calling a function without satisfying its preconditions produces undefined behavior, consuming a query result without understanding its assumptions produces undefined decisions.

SECTION 7

Strengths & Limitations of Documentation Approaches

No single documentation strategy is perfect. Each involves a tradeoff between cost (the effort to create and maintain the documentation), reach (how many stakeholders encounter it), and durability (how long it remains accurate as the underlying data and query evolve). The following comparison helps analysts choose the right combination of approaches for their organizational context.

Approach	Strengths	Limitations
Inline SQL comments	Low friction; travels with the code; version-controlled automatically; zero tooling required	Invisible to non-technical stakeholders; can become stale if query evolves but comments are not updated; clutters complex queries
Structured header blocks	Standardizable via templates; easy to parse programmatically (linting); surfaces all assumptions in one place	Still code-level; requires team discipline to maintain; no enforcement mechanism unless linted
README / wiki pages	Accessible to non-technical readers; supports rich formatting, screenshots, and examples	Detached from query file—can drift out of sync; requires manual maintenance; discoverability depends on wiki organization
Semantic / metric layer	Single source of truth for metric definitions; automatically surfaces in BI tools; enforces consistency across reports	Requires significant infrastructure investment; may not capture ad-hoc query assumptions; governance overhead
Data catalog	Organization-wide discoverability; supports tagging, lineage, quality scores; integrates with governance workflows	Expensive to implement and maintain; adoption requires cultural change; metadata can become stale without active stewardship

Comparative analysis of documentation approaches for query assumptions

✦

KEY TAKEAWAY

The best documentation strategy is the one your team will actually maintain. Start with structured header blocks (high signal, low cost) and expand outward as your organization's data maturity grows. A query with a five-line header block is infinitely more trustworthy than an undocumented query feeding a data catalog nobody reads.

SECTION 8

Connection to Data Contracts & Observability

Communicating query assumptions is a foundational skill that connects directly to two advanced disciplines in modern data engineering: data contracts and data observability. A data contract is a formal agreement between a data producer and a data consumer that specifies the schema, semantics, freshness guarantees, and quality expectations of a dataset. In essence, a data contract codifies assumptions at the organizational level so that individual query authors do not have to rediscover them each time. Data observability, on the other hand, provides runtime monitoring of the assumptions you have documented—alerting you when data freshness degrades, row counts deviate from expectations, or NULL rates spike.

Concept	Query Assumptions (This Lesson)	Advanced Practice
Scope	Single query or report	Data contracts span entire pipelines; observability monitors all datasets
Enforcement	Human discipline—comments, reviews	Automated: schema validation, SLA alerts, CI/CD checks (e.g., dbt tests)
Audience	Query author, direct stakeholders	Cross-team: producers, consumers, platform engineers, compliance
Failure mode	Misinterpretation of results	Pipeline breakage, SLA violations, downstream cascade
Tooling	SQL comments, README files	dbt tests, Great Expectations, Monte Carlo, Soda

Query-level assumptions vs. organizational-level data contracts and observability

The key insight is that learning to communicate assumptions at the query level is prerequisite training for participating in data contract and observability workflows. If you cannot articulate what your query assumes, you cannot specify what a data contract should guarantee. The discipline of writing assumption blocks in your SQL today is the same discipline that will let you define schema expectations, freshness SLAs, and quality thresholds in a production data platform tomorrow.

💡

Looking Ahead

In future coursework, you will encounter tools like dbt test (which lets you write executable assertions like unique, not_null, and accepted_values directly in your project), and Great Expectations (a Python library for data validation). These tools automate assumption verification—but the critical first step is always identifying and communicating the assumptions in human-readable form.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain the difference between a query assumption and a query limitation. Provide one example of each in the context of a query that counts daily active users.

Show answer

A query assumption is an unstated belief that, if violated, would change the correctness of the results—for example, assuming that each row in the events table corresponds to a unique user session (when in reality one session might generate multiple rows, inflating the count). A query limitation is a known boundary that the query author acknowledges—for example, stating that the query only counts users who triggered at least one 'page_view' event and excludes users who only used the mobile API. The assumption is implicit and potentially dangerous if undocumented; the limitation is explicit and deliberately scoped. Both must be communicated, but assumptions carry higher risk because stakeholders may not even know to ask about them.

Question 2: BASIC CALCULATION

Consider the following query: SELECT department, AVG(salary) AS avg_salary FROM employees WHERE hire_date >= '2020-01-01' GROUP BY department; List at least four assumptions embedded in this query that should be documented.

Show answer

First, the query assumes that the salary column is non-NULL for all relevant employees; if some employees have NULL salaries, AVG() silently ignores them, which could skew the result without the consumer knowing. Second, it assumes salary is stored in a single currency—if the company operates internationally, comparing dollar salaries to euro salaries is meaningless. Third, the hire_date filter assumes that '2020-01-01' is inclusive and uses the database's default date comparison semantics (typically midnight UTC), which may not align with the business definition of 'hired in 2020.' Fourth, it assumes the employees table reflects current department assignments; if employees have transferred departments, their salary might be attributed to the wrong group. A well-documented version would state each of these assumptions in a header comment block.

Question 3: INTERMEDIATE

You are asked to produce a monthly churn report. Your query uses LEFT JOIN between this month's active users and last month's active users to find users who disappeared. Write a structured header block (in SQL comment format) that documents at least three assumptions and two limitations of this approach.

Show answer

A well-structured header block might read: /* Monthly Churn Report Author: analyst@example.com | Date: 2025-01-15 ASSUMPTIONS: 1. 'Active' is defined as at least one login event in the month; users with API-only activity are not counted. 2. The LEFT JOIN assumes user_id is unique per month in both CTEs; duplicates would produce a fan-out and incorrect churn counts. 3. Data in the events table is complete for both months; if the ETL for last month was partially loaded, churn will be overestimated. LIMITATIONS: - This query does not distinguish voluntary churn (user cancelled) from involuntary churn (payment failure). Both appear the same. - Users who were inactive last month but active the month before are not captured as 'returned'—this is a simple month-over-month comparison, not a cohort analysis. */ This block gives a reviewer everything they need to evaluate whether the metric is fit for their purpose. The three assumptions address data quality, join semantics, and completeness. The two limitations scope the metric to prevent overinterpretation.

Question 4: APPLIED

Your product manager sees a dashboard showing '15,342 active users this week' and plans to include it in an investor presentation. You know the underlying query uses COUNT(DISTINCT user_id) on an events table that includes internal test accounts and bot traffic. The query also uses APPROX_COUNT_DISTINCT for performance. Draft a concise stakeholder-facing caveat (3–5 sentences, plain business language) to accompany this number.

Show answer

A suitable stakeholder-facing caveat might read: 'This figure represents an approximate count of distinct user IDs that generated at least one event in the past seven calendar days (Monday through Sunday, UTC). The count includes internal test accounts and automated bot traffic, which our engineering team estimates at roughly 2–3% of total activity; filtering these out would reduce the number by approximately 300–450 users. Additionally, the metric uses an approximate counting algorithm for performance, which introduces a margin of error of about ±2%. For investor materials, we recommend using the filtered, exact-count version of this metric and noting that the definition of "active" is based on any event type, not logins specifically.' This caveat translates technical details (APPROX_COUNT_DISTINCT, test accounts) into business impact (directional magnitude of the error) and recommends a more rigorous alternative.

Question 5: CRITICAL THINKING

A colleague argues that documenting assumptions is unnecessary overhead because 'the SQL speaks for itself—anyone can read the WHERE clause to see what's filtered.' Construct a rigorous counterargument with at least three distinct reasons why this position is flawed, drawing on concepts from this lesson.

Show answer

First, SQL's declarative syntax obscures implicit behavior that even experienced engineers can miss. The most prominent example is NULL handling: a WHERE clause like 'status != cancelled' silently drops NULL rows without any visible indication in the code. The SQL does not 'speak' about what it excludes by omission. Second, many stakeholders who consume query results—product managers, executives, compliance officers—cannot read SQL at all. The assumption that 'anyone can read the WHERE clause' conflates the query's technical audience with its decision-making audience. A number on a dashboard carries no WHERE clause with it; only documentation bridges that gap. Third, even for technical readers, the SQL cannot express business interpretation assumptions. The code can show that you filtered on 'order_date >= 2024-01-01', but it cannot explain whether this date represents a fiscal year boundary, a product launch date, or an arbitrary cutoff. Context is metadata, not syntax. A fourth bonus argument: SQL is read far more often than it is written. The marginal cost of a five-line header block is trivial compared to the hours of confusion saved when a future analyst—or your future self—needs to understand why the numbers look unexpected.

SUMMARY

Lesson Summary

Summary

Every analytical SQL query encodes hidden assumptions about data quality, schema structure, and business semantics. These assumptions fall into a taxonomy spanning data-layer concerns (completeness, uniqueness, validity, freshness), query-logic concerns (join types, NULL handling, filter choices), and business-interpretation concerns (metric definitions, temporal boundaries, scope exclusions). Failing to identify and communicate these assumptions leads to misinterpretation, erroneous decisions, and eroded trust in data-driven reporting.

Effective communication uses a layered documentation strategy—from inline SQL comments and structured header blocks (co-located with the query) to semantic layers and data catalogs (discoverable across the organization). The discipline of articulating assumptions at the query level is the foundation for advanced practices like data contracts and data observability. Start with structured header blocks on every query you write; expand outward as your team's data maturity grows.