返回提示词目录第 1346 页

审计数据管道可靠性与恢复能力

检查数据管道的准确性、幂等性、重放和故障恢复,输出风险发现及验证要点。

完整提示词

# ETL AND DATA PIPELINE RELIABILITY AUDIT

I want an in-depth analysis of ETL, ingestion, sync, import/export, and data pipeline systems with a focus on correctness, idempotency, replay, ordering, schema evolution, lineage, and disaster recovery.

Main objective:

> Determine whether the pipeline can silently lose, duplicate, erroneously transform, skip, delay, or misattribute data across tenants, and verify whether it can safely replay and recover from partial failures.

## 1. PIPELINE INVENTORY

Inventory:

- source
- ingestion
- staging
- transform
- output
- schedule
- queue
- storage
- destination
- owner

For each:

```text
Pipeline:
Source:
Trigger:
Input format:
Checkpoint:
Transform:
Destination:
Idempotency:
Retry:
DLQ:
Freshness SLA:
```

## 2. SOURCE OF TRUTH

Establish which system holds authoritative state.

## 3. FULL LOAD

Full historical snapshot extraction mechanics.

## 4. INCREMENTAL LOAD

Incremental change extraction strategies.

## 5. CDC

Change Data Capture (CDC) stream processing.

## 6. POLLING

Periodic polling mechanisms.

## 7. WEBHOOK

Inbound webhook processing pipelines.

## 8. FILE IMPORT

Batch file import workflows.

## 9. SCHEDULE

Timezone handling in pipeline execution schedules.

## 10. WATERMARK

Watermarking via `updated_at`, sequences, or logical cursors.

## 11. WATERMARK GAP

Using strict inequality `>` can miss rows sharing identical timestamps.

## 12. WATERMARK OVERLAP

Replay with downstream deduplication.

## 13. CLOCK

Clock skew across source systems.

## 14. PAGINATION

Source records mutating during paginated extraction.

## 15. OFFSET

Offset-based pagination skipping or duplicating rows during active mutations.

## 16. CURSOR

Cursor-based extraction where stable cursors are supported.

## 17. CHECKPOINT

When are checkpoints persisted?

Prior to or following destination transaction commits?

## 18. EARLY CHECKPOINT

Writing checkpoints too early can cause permanent data loss upon failure.

## 19. LATE CHECKPOINT

Writing checkpoints too late can produce duplicate records upon failure.

## 20. IDEMPOTENCY

Destination upsert or deduplication mechanisms.

## 21. EVENT ID

Unique external event identifiers.

## 22. DUPLICATE SOURCE

Handling duplicate event emissions from source systems.

## 23. RETRY

At-least-once pipeline delivery semantics.

## 24. EXACTLY ONCE

Do not claim casually.

Prove end-to-end delivery and processing semantics.

## 25. OUT-OF-ORDER

Handling out-of-order event delivery.

## 26. LATE EVENT

Late-arriving events and historical corrections.

## 27. DELETE EVENT

How deletions are propagated across the pipeline.

## 28. TOMBSTONE

Tombstone records and purge policies.

## 29. UPDATE

Full state replacement versus partial patch application.

## 30. SCHEMA EVOLUTION

Source system introducing new attributes.

## 31. SOURCE REMOVES FIELD

Source system deprecating or removing attributes.

## 32. TYPE CHANGE

Handling data type mutations across versions.

## 33. ENUM VALUE

Unrecognized enum values introduced by upstream producers.

## 34. UNKNOWN FIELD

Forward compatibility for unmapped attributes.

## 35. REQUIRED FIELD

Upstream producers omitting newly mandated fields.

## 36. VERSIONED EVENT

Schema versioning for message contracts.

## 37. DEAD LETTER

Handling unparseable or rejected messages.

## 38. DLQ MONITORING

An unmonitored Dead Letter Queue represents silent data loss.

## 39. REPLAY

Can historical events be safely replayed?

## 40. REPLAY RANGE

Replaying bounded time or sequence ranges.

## 41. REPLAY ORDER

Preserving event ordering during historical replays.

## 42. REPLAY SIDE EFFECT

Ensure rebuilding analytical datasets does not trigger duplicate real-world emails or payments.

## 43. STAGING TABLE

Decoupling raw ingestion from transformation pipelines via staging tables.

## 44. RAW EVENT RETENTION

Preserving raw input payloads to facilitate debugging and replays.

## 45. LINEAGE

Tracing transformed output rows back to exact source inputs.

## 46. TRANSFORMATION

Ensuring pure and deterministic transformation functions.

## 47. NON-DETERMINISTIC

Transformation dependencies on current timestamp, random values, or external lookups.

## 48. REPROCESS

Reprocessing producing divergent outputs.

## 49. REFERENCE DATA

Version control for lookup catalogs and reference dimensions.

## 50. JOIN

Late-arriving dimensions and missing join targets.

## 51. TENANT MAPPING

Critical verification:

mapping external account identifiers to internal tenant contexts.

## 52. CROSS-TENANT MISROUTING

High-severity data leak.

## 53. ID COLLISION

External IDs that are only unique within a specific account or source.

## 54. SOURCE ID NAMESPACE

Scoping composite identities with source namespaces.

## 55. PARTIAL FAILURE

Batch of 1000 items failing at row 500.

## 56. ATOMIC BATCH

Evaluating the cost and feasibility of fully atomic batches.

## 57. PER-ROW STATUS

Per-row status tracking and individual error recording.

## 58. POISON RECORD

Preventing single malformed records from halting the entire pipeline indefinitely.

## 59. RETRY STORM

Persistent poison records causing unbounded retry storms.

## 60. BACKOFF

Implementing exponential backoff with jitter on retries.

## 61. RATE LIMIT

Respecting source and destination API rate limits.

## 62. QUOTA

Monitoring operational API quotas.

## 63. PAGINATION RETRY

Retrying an interrupted pagination query causing duplicate ingestion.

## 64. TOKEN EXPIRY

Handling authentication token expiration during long-running pipelines.

## 65. NETWORK TIMEOUT

Handling ambiguous outcomes following network timeouts.

## 66. DESTINATION TRANSACTION

Enforcing transactional write boundaries at the destination.

## 67. BULK WRITE

Bulk database operations for high-throughput destinations.

## 68. UPSERT

Enforcing correct composite unique keys during upserts.

## 69. DELETE + INSERT

Delete-insert sequences creating temporary record absence.

## 70. MERGE

Engine-specific SQL MERGE semantics and locking behaviors.

## 71. DATA QUALITY

Automated checks:

- null rate
- duplicate rate
- valid ranges
- referential integrity
- row count comparisons
- checksums

## 72. SILENT TRUNCATION

Silent truncation of strings or numeric values.

## 73. ROUNDING

Discrepancies caused by floating-point rounding.

## 74. TIMEZONE

Normalizing mixed timezones into consistent UTC timestamps.

## 75. ENCODING

Character encoding issues in CSV or text imports.

## 76. CSV HEADER

Handling rearranged or omitted CSV header columns.

## 77. CSV FORMULA

Formula injection risks in exported CSV files opened by human operators.

## 78. JSON

Deeply nested or polymorphic JSON payloads.

## 79. XML

XML entity parsing vulnerabilities and validation.

## 80. FILE NAMING

Never rely on filenames as authoritative dates or sources without validation.

## 81. PARTIAL FILE

Processing files while upload or write operations are still active.

## 82. FILE ATOMICITY

Writing to temporary file paths before performing atomic renames.

## 83. DUPLICATE FILE

Deduplicating files via content hashing or unique file identifiers.

## 84. LARGE FILE

Streaming file processing.

## 85. MEMORY

Avoid reading multi-gigabyte files entirely into application memory.

## 86. BACKPRESSURE

Managing downstream backpressure when ingestion outpaces consumption.

## 87. QUEUE DEPTH

Queue depth monitoring.

## 88. OLDEST MESSAGE AGE

Oldest message age provides a vastly superior freshness signal than raw queue depth.

## 89. WORKER CONCURRENCY

Sizing worker concurrency against destination capacity.

## 90. PARALLEL PARTITIONS

Preserving ordering across parallel processing partitions.

## 91. PARTITION KEY

Partitioning by entity key to preserve required chronological ordering.

## 92. HOT PARTITION

A single heavy tenant dominating an individual partition.

## 93. REBALANCE

Handling consumer group rebalance interruptions.

## 94. LEASE

Lease duration expiration leading to duplicate concurrent worker execution.

## 95. JOB LOCK

Distributed job execution locks.

## 96. SCHEDULE OVERLAP

Successive scheduled pipeline runs overlapping when previous executions stall.

## 97. CONCURRENCY POLICY

Configuring explicit skip, queue, or parallel policies for scheduled jobs.

## 98. SLA/FRESHNESS

If not defined:

**FRESHNESS REQUIREMENT NOT DEFINED**

## 99. STALENESS

Pipeline reporting healthy status while data is 12 hours out of date.

## 100. HEARTBEAT

Worker heartbeat monitoring.

## 101. ROW COUNT MONITOR

Alerting on unexpected zero-row or 10x row volume spikes.

## 102. SCHEMA MONITOR

Automated alerts on upstream schema variations.

## 103. QUALITY ALERT

Alerting on data quality rule violations.

## 104. OBSERVABILITY

Record per batch:

- source range
- rows read
- rows accepted
- rows rejected
- rows written
- duplicate count
- execution duration
- committed checkpoint

## 105. AUDIT

Immutable synchronization audit logs.

## 106. BACKFILL

Historical backfill procedures.

## 107. BACKFILL + LIVE

Concurrency races between historical backfills and live ingestion streams.

## 108. CUTOVER

Cutover procedures from legacy to modern pipelines.

## 109. NEW PIPELINE VERSION

Consistency between old and new transformation logic.

## 110. REPROCESS WITH NEW LOGIC

Historical values may intentionally shift under revised logic.

Document thoroughly.

## 111. DATABASE MIGRATION

Managing pipeline compatibility during destination database migrations.

## 112. SOURCE OUTAGE

Pipeline recovery and catch-up behavior following source outages.

## 113. DESTINATION OUTAGE

Buffering and retry mechanisms during destination downtime.

## 114. STORAGE OUTAGE

Interim storage outage resilience.

## 115. DLQ OUTAGE

Handling failures within the Dead Letter Queue infrastructure.

## 116. CREDENTIAL ROTATION

Managing credential updates across long-running background workers.

## 117. DISASTER RECOVERY

Can the destination datastore be reconstructed from raw source data?

## 118. SOURCE RETENTION

Verify source event retention windows do not expire before recovery completes.

## 119. RPO

Allowable data loss window.

## 120. RTO

Allowable catch-up and recovery time.

## 121. RECONCILIATION

Periodic automated reconciliation comparing source and destination systems.

## 122. COUNT ONLY

Row counts can match while individual field data diverges.

## 123. CHECKSUM/PER-ID

Cryptographic per-record checksum comparison for high-integrity pipelines.

## 124. GDPR/PII

Technical data handling and cleanup policies.

## 125. SECRET DATA

Ensuring pipeline diagnostic logs do not expose sensitive credentials or data.

## 126. MULTI-TENANT LOG

Preventing cross-tenant data leakage within operational logs and diagnostics.

## 127. EVIDENCE, STATUS AND FALSE POSITIVES

Evidence tiers:

```text
A - reproduced: run history, reconciliation results, checkpoint state or a safe replay shows the behavior
B - complete path: pipeline code, checkpoint logic, delivery semantics and sink writes fully show the failure
C - strong static evidence: code shows the path, but orchestrator, broker or connector settings are not verified
D - inference: depends on delivery guarantees, ordering or provider behavior not verified
E - hardening: stronger observability or controls without a current failure path
```

Status:

- **CONFIRMED** - tier A or B evidence shows the failure or exploit path.
- **LIKELY** - tier C evidence.
- **NOT VERIFIED** - depends on runtime state, settings or versions that could not be checked (tier D). Never present tier D as confirmed.
- **NOT APPLICABLE** - the component or pattern is not used.
- **CONTROLLED** - the risk exists but another control contains it.
- **HARDENING** - improvement without a current failure path (P4).

False-positive rules:

- At-least-once delivery is not a defect when every sink write is idempotent or deduplicated.
- Latency or eventual consistency within the stated freshness requirement is not a finding.
- A full reload instead of incremental processing is a cost or duration concern, not a correctness defect, unless it causes gaps, duplicates or overload.
- Row count differences explained by documented filters, deduplication or late-arriving data are not data loss; prove the difference per ID before reporting loss.
- Tolerating schema drift by ignoring unknown fields is intentional unless it silently drops data that consumers need.

Do not report a missing best practice as a confirmed defect unless there is a concrete failure, exploit, correctness, reliability, or operational path.

## 128. FINDING FORMAT

```text
ID:
Severity:
Status:
Evidence tier:
Pipeline:
Source:
Destination:
Checkpoint:
Idempotency:
Ordering:
Failure scenario:
Lost/duplicated data:
Tenant impact:
Detection:
Recovery:
Evidence:
Root cause:
Fix:
Replay test:
```

## 129. SEVERITY

P0:
- silent global data corruption or loss lacking recovery mechanisms
- systematic cross-tenant pipeline misrouting
- financial or identity pipeline corruption with catastrophic impact

P1:
- repeatable data loss or duplication on critical pipelines
- checkpoint flaws causing data gaps
- unsafe replay workflows causing severe duplicate side effects
- large-scale stale or inaccurate production data persisting undetected

P2:
- meaningful localized data integrity or freshness defects

P3:
- limited quality or reliability weaknesses

P4:
- maturity and observability hardening suggestions

## 130. OUTPUT

`ETL_DATA_PIPELINE_RELIABILITY_AUDIT.md`

## 131. PIPELINE MATRIX

| Pipeline | Source | Checkpoint | Idempotent | Ordering | DLQ | Replay |
|---|---|---|---|---|---|---|

## 132. SECOND PASS

For every pipeline simulate:

- receiving the same batch twice
- crash prior to checkpoint persistence
- crash following checkpoint persistence
- out-of-order event delivery
- source API timeout
- destination database timeout
- single malformed record in a batch
- upstream schema introducing a new field
- upstream record deletion
- processing a 100x backlog spike
- replaying a 24-hour historical window
- tenant mapping mismatches
- concurrent execution of old and new pipeline versions

## 133. FINAL QUALITY GATE

Verify:

- source authority
- checkpoint mechanics
- idempotency controls
- event ordering guarantees
- retry policies
- schema evolution resilience
- tenant scoping accuracy
- batch partial failure handling
- Dead Letter Queue operation
- replay safety
- data freshness
- automated quality checks
- observability instrumentation
- automated reconciliation
- disaster recovery procedures

# FINAL RULE

Looking for issues such as:

```text
poller:
fetch rows WHERE updated_at > last_checkpoint
↓
three source rows receive identical updated_at timestamp
↓
page 1 contains first two
↓
checkpoint is set to that timestamp
↓
page 2 query uses >
↓
third row with same timestamp is permanently skipped
↓
pipeline reports success
↓
silent data loss
```

or:

```text
batch writes 1000 rows
↓
destination commits successfully
↓
process crashes before checkpoint update
↓
same batch runs again
↓
destination uses INSERT without unique/idempotency key
↓
1000 duplicates created
```

If exactly-once processing is not conclusively proven:

**AT-LEAST-ONCE / DELIVERY SEMANTICS NOT VERIFIED.**

If pipeline freshness requirements are not defined:

**FRESHNESS REQUIREMENT NOT DEFINED.**

分类与标签

编程开发、ETL审计、数据完整性、故障恢复

前往 AI 图片创作