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.
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?
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
Schema Assumptions
Business Logic Assumptions
Temporal Assumptions
Known Limitations
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.
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.
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.
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.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.
| 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 |
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.
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.
/*
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;
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 |
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 |
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.
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.Practice Problems
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.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.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.Lesson 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.