审计数据管道可靠性与恢复能力
检查数据管道的准确性、幂等性、重放和故障恢复,输出风险发现及验证要点。
完整提示词
# 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审计、数据完整性、故障恢复