Emit Reusable Data Quality Flags
Create failed, SLA-breach, and unowned 1/0 flags for every event.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Data quality engineer Downstream metrics need numeric flags that can later be summed without reinterpreting business rules.
Downstream metrics need numeric flags that can later be summed without reinterpreting business rules. Create failed, SLA-breach, and unowned 1/0 flags for every event.
Return
- Return event_id, failed_flag, sla_breach_flag, and unowned_flag.
- Order by event_id.
Constraints
- Use a separate CASE expression for each flag.
- Only count SLA breaches when target_ms is positive.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_events
event_idINTEGERpipeline_nameTEXTstatus_codeTEXTseverityTEXTactual_msINTEGERtarget_msINTEGERretry_countINTEGERprocessed_rowsINTEGERfailed_rowsINTEGERowner_teamTEXTis_customer_facingINTEGERsource_systemTEXTmaintenance_modeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Each independent attribute gets its own CASE WHEN condition THEN 1 ELSE 0 END.
Hint 2
Include target_ms > 0 in the breach flag.
Hint 3
Keep ELSE 0 so NULL predicates do not propagate NULL flags.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, CASE WHEN status_code = 'FAIL' THEN 1 ELSE 0 END AS failed_flag, CASE WHEN actual_ms > target_ms AND target_ms > 0 THEN 1 ELSE 0 END AS sla_breach_flag, CASE WHEN owner_team IS NULL THEN 1 ELSE 0 END AS unowned_flag FROM pipeline_events ORDER BY event_id;Why this works
Numeric flags are portable and composable. Explicit ELSE 0 turns false and unknown predicates into a stable metric input while preserving each rule as an independently testable expression.
Success check
All three flags are always 0 or 1 and invalid or missing targets do not count as breaches.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | failed_flag | sla_breach_flag | unowned_flag |
|---|---|---|---|
| 101 | 0 | 0 | 0 |
| 102 | 0 | 0 | 0 |
| 103 | 0 | 1 | 0 |
| 104 | 1 | 1 | 0 |
| 208 | 0 | 0 | 1 |
| 209 | 1 | 1 | 1 |
| 210 | 0 | 0 | 0 |
| 303 | 0 | 0 | 0 |
| 304 | 0 | 1 | 0 |
| 305 | 1 | 1 | 0 |
Previewing 10 of 14 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
Database Changes
Practice safe SQL data changes, schema evolution, retry-safe upserts, transactions, rollback, views, and indexes in isolated databases.
Open the interactive workspace and practice across SQL topics.