Understanding Transaction Boundaries
Understanding Transaction Boundaries helps analysts define which statements succeed or fail together between BEGIN and COMMIT or ROLLBACK.
What & Why
The Understanding Transaction Boundaries pattern is used to define which statements succeed or fail together between BEGIN and COMMIT or ROLLBACK. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants one stable pre-transaction snapshot before campaign and lead changes are treated as a single unit.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
-- BEGIN; starts the boundary. COMMIT; keeps all work; ROLLBACK; keeps none.
SELECT
TIMESTAMPTZ '2024-04-30 00:00:00+00' AS snapshot_at,
(SELECT COUNT(*) FROM marketing.campaigns) AS campaign_count,
(SELECT COUNT(*) FROM marketing.leads) AS lead_count;RESULT — fixed transaction-boundary snapshot
| snapshot_at | campaign_count | lead_count |
|---|---|---|
| 2024-04-30 00:00:00+00 | 4 | 4 |
The explicit timestamp and both fixture counts make this transaction-boundary example fully deterministic.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario