SQL workbook: from first query to reconciliation

The setup and worked queries target Snowflake SQL in an approved disposable sandbox. Run statements individually. Do not paste setup statements into production. Temporary tables last for the session, so keep the same connection.

A SELECT reads data; INSERT adds rows; UPDATE changes rows; DELETE removes rows; CREATE defines an object; DROP removes an object; GRANT changes privileges. This workbook needs only temporary-table creation and inserts for setup, then reads.

Convention: these timezone-free timestamps represent UTC by agreement. Real systems need an explicit timezone contract.

CREATE TEMPORARY TABLE lab_accounts (
  account_id VARCHAR, customer_id VARCHAR
);
INSERT INTO lab_accounts VALUES ('A1','C1'), ('A2','C2'), ('A3','C1');

CREATE TEMPORARY TABLE lab_tx (
  tx_id VARCHAR, account_id VARCHAR, event_ts TIMESTAMP_NTZ,
  amount NUMBER(18,2), currency VARCHAR, status VARCHAR
);
INSERT INTO lab_tx VALUES
  ('T1','A1','2026-01-10 10:00:00', 100, 'SEK', 'BOOKED'),
  ('T2','A1','2026-02-01 00:00:00', 250, 'SEK', 'BOOKED'),
  ('T3','A2','2026-02-05 12:00:00', 400, 'EUR', 'BOOKED'),
  ('T4','A9','2026-02-06 12:00:00',  50, 'SEK', 'BOOKED'),
  ('T5','A3','2026-02-07 12:00:00', NULL, 'SEK', 'PENDING');

CREATE TEMPORARY TABLE lab_risk (
  version_id VARCHAR, customer_id VARCHAR, risk_rating VARCHAR,
  StartEffectiveDate TIMESTAMP_NTZ, FinishEffectiveDate TIMESTAMP_NTZ
);
INSERT INTO lab_risk VALUES
  ('V1','C1','LOW','2026-01-01','2026-02-01'),
  ('V2','C1','HIGH','2026-02-01',NULL),
  ('V3','C2','MEDIUM','2026-01-01',NULL);

Expected: T2 250, T1 100, T4 50. SQL does not promise row ordering without ORDER BY.

SELECT tx_id, amount, currency
FROM lab_tx
WHERE status = 'BOOKED' AND currency = 'SEK'
ORDER BY amount DESC;

Expected: EUR has 1 row, 1 amount and 400; SEK has 4 rows, 3 amounts and 400. COUNT(amount) excludes NULL. SUM ignores NULL, so a plausible total can conceal missing data. Do not replace missing amounts with zero unless the business rule permits it.

SELECT currency,
       COUNT(*) AS rows_total,
       COUNT(amount) AS amounts_present,
       SUM(amount) AS amount_total
FROM lab_tx
GROUP BY currency
ORDER BY currency;

Expected: T5. Use IS NULL, not = NULL. SQL conditions involving NULL often evaluate to unknown. NOT IN can behave unexpectedly if the comparison set contains NULL; use a carefully defined NOT EXISTS for anti-joins.

SELECT tx_id FROM lab_tx WHERE amount IS NULL;

Expected: T4/A9. An INNER JOIN would silently remove it. A WHERE condition on a right-side attribute can also remove unmatched rows from a LEFT JOIN.

SELECT t.tx_id, t.account_id
FROM lab_tx t
LEFT JOIN lab_accounts a ON t.account_id = a.account_id
WHERE a.account_id IS NULL;

Expected: C1/SEK 350 and C2/EUR 400. T4 is excluded because it has no account match. A complete report must disclose that exclusion rather than treating the result as the complete source population.

SELECT a.customer_id, t.currency, SUM(t.amount) AS booked_total
FROM lab_tx t
JOIN lab_accounts a ON t.account_id = a.account_id
WHERE t.status = 'BOOKED'
GROUP BY a.customer_id, t.currency
HAVING SUM(t.amount) > 300;

Expected: T1 LOW, T2 HIGH, T3 MEDIUM, T4 NULL, T5 HIGH. T2 falls exactly on the new interval boundary and matches only HIGH. Joining by customer_id alone yields two matches for each C1 transaction. Using today's risk answers a different question.

SELECT t.tx_id, a.customer_id, r.risk_rating
FROM lab_tx t
LEFT JOIN lab_accounts a ON t.account_id = a.account_id
LEFT JOIN lab_risk r
  ON a.customer_id = r.customer_id
 AND t.event_ts >= r.StartEffectiveDate
 AND (t.event_ts < r.FinishEffectiveDate
      OR r.FinishEffectiveDate IS NULL)
ORDER BY t.tx_id;

Expected: C1 HIGH and C2 MEDIUM. The version_id tie-breaker makes ordering deterministic in this fictional data. In real data, contradictory records at the same effective time require a business precedence rule or quarantine, not an arbitrary winner.

SELECT customer_id, risk_rating
FROM lab_risk
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY StartEffectiveDate DESC, version_id DESC
) = 1;

Expected: zero rows. If transaction IDs are only unique within a source, group by source and transaction ID.

SELECT tx_id, COUNT(*) AS copies
FROM lab_tx
GROUP BY tx_id
HAVING COUNT(*) > 1;

Expected: zero rows. This assumes version_id is unique and non-null. Also test that every closed interval has finish strictly after start, and that each entity has at most one open interval.

SELECT a.customer_id, a.version_id, b.version_id
FROM lab_risk a
JOIN lab_risk b
  ON a.customer_id = b.customer_id
 AND a.version_id < b.version_id
 AND (b.FinishEffectiveDate IS NULL
      OR a.StartEffectiveDate < b.FinishEffectiveDate)
 AND (a.FinishEffectiveDate IS NULL
      OR b.StartEffectiveDate < a.FinishEffectiveDate);

Expected: T3 VALUE_DIFFERENCE and T4 SOURCE_ONLY. IS DISTINCT FROM gives null-safe comparison in Snowflake. This example checks only amount and currency; a real reconciliation must enumerate every material attribute. Verify key uniqueness on both sides first.

CREATE TEMPORARY TABLE lab_target AS
SELECT * FROM lab_tx WHERE tx_id <> 'T4';

UPDATE lab_target SET amount = 401 WHERE tx_id = 'T3';

SELECT COALESCE(s.tx_id, t.tx_id) AS tx_id,
       CASE WHEN s.tx_id IS NULL THEN 'TARGET_ONLY'
            WHEN t.tx_id IS NULL THEN 'SOURCE_ONLY'
            ELSE 'VALUE_DIFFERENCE' END AS issue
FROM lab_tx s
FULL OUTER JOIN lab_target t ON s.tx_id = t.tx_id
WHERE s.tx_id IS NULL OR t.tx_id IS NULL
   OR s.amount IS DISTINCT FROM t.amount
   OR s.currency IS DISTINCT FROM t.currency;

Expected: A1 reaches 100 then 350; A2 reaches 400; T5 is excluded as PENDING. Add a deterministic tie-breaker (tx_id) so equal timestamps cannot reorder the running total. Windows compute over the rows the query returns — filters applied after the window change the result.

SELECT tx_id, account_id, event_ts, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY event_ts, tx_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM lab_tx
WHERE status = 'BOOKED'
ORDER BY account_id, event_ts;

• Date and timestamp functions differ between engines — check each one.
• QUALIFY is not universal; the portable pattern uses ROW_NUMBER in a CTE.
• NULL handling in NOT IN, joins and comparisons differs — prefer explicit IS NULL and NOT EXISTS.
• String case sensitivity, implicit casts and integer division differ by engine.
• Run a small behavioural fixture set on both engines and compare results, not syntax alone.

• The grain is stated and each join is at that grain.
• Filters that remove rows are deliberate and disclosed.
• NULLs are handled explicitly, never silently dropped.
• ORDER BY is present wherever order matters.
• The expected result is written down before running.