SQL & data contracts

See the system

Make business calculations reliable by defining grain, validating joins, and treating data assumptions as contracts.

Define the grain before writing a query

The grain is what one row represents: a ticket, an event, a document revision, or a daily snapshot. If you join ticket rows to event rows, a ticket may appear several times. Counting the result can exaggerate workload and cost.

State the primary key, business key, and timestamp semantics. A creation time is different from an ingestion time. Explicit grain and time definitions prevent dashboards and model features from telling incompatible stories.

Understand nulls and join cardinality

NULL represents an unknown or missing value, not zero or an empty string. Comparisons with NULL require appropriate SQL semantics. Use COALESCE only when the replacement has a defensible business meaning.

Before a join, establish whether each side has one or many rows per key. Check unmatched keys and unexpected multiplication. A left join preserves the left side’s unmatched rows, but a later filter on the right-hand table can accidentally remove them.

Use windows to preserve row detail

Window functions calculate across related rows without collapsing all rows into an aggregate. ROW_NUMBER can select the latest revision when ordering includes a deterministic tie-breaker. SUM over an ordered window can produce a running total.

Be precise about the time range and frame. “Latest” needs an ordering rule for identical timestamps, and a cumulative calculation needs a defined partition. Otherwise reruns may select different records.

Model changes explicitly

Operational records change. A current-state table answers what is true now; a history table can answer what was true when a decision happened. Slowly changing dimensions preserve selected attribute changes, but add complexity that needs a real analytical purpose.

For AI auditability, retain a stable document revision identifier with the answer evidence. If the source is later edited, an answer should remain explainable against the version actually retrieved.

Turn assumptions into quality rules

A data contract names fields, types, keys, allowed values, freshness expectations, and ownership. Quality checks need an action: reject, quarantine, alert, or tolerate within a defined threshold. A dashboard warning without an owner is not an operational response.

Test transformations against small fixtures with known results. Reconcile source and target counts carefully: deduplication or filtering can legitimately change counts, so explain the difference instead of requiring equality everywhere.

Worked scenario

A dashboard reports 240 tickets after joining 80 tickets to their three average status events. COUNT(*) measures joined rows. Aggregate events to one row per ticket before joining, or count distinct ticket IDs when that matches the intended metric. Verify the answer against a fixture with known ticket counts.

A small example

This example isolates one concept. Read its boundary conditions before adapting it to an application.

WITH ranked AS (
  SELECT document_id, revision_id, body,
         ROW_NUMBER() OVER (
           PARTITION BY document_id
           ORDER BY effective_at DESC, revision_id DESC
         ) AS rn
  FROM policy_versions
)
SELECT document_id, revision_id, body
FROM ranked
WHERE rn = 1;

Practical assignment

1. Create a small synthetic ticket/event dataset.
2. Document each table’s grain and keys.
3. Write join, orphan, duplicate, and null checks.
4. Calculate workload without event multiplication.
5. Version the schema and define quality failure actions.
6. Submit SQL plus fixture-based expected results.

What to submit

Submit the artifacts named above, a short explanation of your decisions, and evidence of the checks you performed. Distinguish measured results from estimates and designs from executed integrations.

Knowledge check

References & next step

Platform examples are environment-dependent. Start with the official documentation in the reference library and verify the exact cloud, region, privileges, and versions you use.

Open the official reference library

Editorial edition: 5 October 2026. The local reference lab is executed locally; this course does not claim a live Databricks deployment.