--- name: fsharp-large-etl description: Design, implement, review, test, troubleshoot, and operate large deterministic ETL pipelines from Oracle through canonical TSV, typed Parquet, DuckDB validation, and versioned Elasticsearch projections. Use for F# functional-core/imperative-shell ETL work requiring SCN-pinned snapshots, exact type fidelity, lineage, bounded streaming, reconciliation, restartability, CFF-style evidence, batch/CDC convergence, and mathematically falsifiable requirements. --- # ETL Skill — Oracle → TSV → Parquet → DuckDB → Elasticsearch > *Source → Snapshot → Extract → Encode → Type → Verify → Project → Reconcile → Evidence.* > > Implementation is never the first proof. A pipeline stage exists only when > its contract, conservation law, failure policy, restart rule, evidence, and > truthful status exist. - **Primary language:** F#/.NET - **Architecture:** functional core, imperative shell - **Source of record:** Oracle - **Landing representation:** canonical TSV - **Typed analytical representation:** Apache Parquet - **Verification and reconciliation engine:** DuckDB - **Serving projection:** Elasticsearch 8.x - **Primary operator surface:** one F# CLI; shell scripts remain thin wrappers - **Review date:** 2026-07-15 ## Contents 1. Prime law and mission 2. Fixed architecture and authority 3. Functional core and imperative shell 4. Typed contracts and the single mapping specification 5. Oracle snapshot and extraction 6. Canonical TSV 7. Typed Parquet 8. DuckDB verification 9. Elasticsearch projection 10. Conservation, lineage, and fidelity 11. Partitions, checkpoints, restart, and atomic publication 12. Bounded execution and failure policy 13. Batch/CDC convergence 14. Testing and proof 15. Security, observability, repository, and CLI 16. Workflow, status, release, and landmines --- ## 0 · PRIME LAW ```text Oracle@SCN → CanonicalTSV → TypedParquet → VerifiedDataset(DuckDB) → SearchProjection(Elasticsearch) ``` ```text Oracle = authoritative business source TSV = canonical inspectable landing artifact Parquet = typed, columnar, replayable data artifact DuckDB = derived verification/reconciliation engine Elasticsearch = denormalized search projection Elasticsearch ≠ source of record DuckDB ≠ source of record TSV/Parquet ≠ permission to change source truth ``` The trusted form is: ```text TrustedRun = PinnedSource ∧ VersionedMapping ∧ DeterministicCore ∧ BoundedShell ∧ ConservedItems ∧ ClassifiedFidelity ∧ ReplayableArtifacts ∧ ReconciledProjection ∧ InspectableEvidence ``` If any required factor is false, the run is incomplete or experimental; it is not silently promoted to success. ### 0.1 · Transformation law ```text same(SourceSnapshot, MappingSpec, Context, ToolchainProfile) ⇒ same(CanonicalMeaning) ``` Byte identity is required for canonical manifests and explicitly canonical text. Parquet byte identity is required only when writer, format, compression, metadata, and ordering are pinned and agreement-tested. Otherwise record both: ```text FileDigest = identity of produced bytes SemanticDigest = identity of canonical schema + canonical logical rows ``` Never claim byte determinism when only semantic determinism is proven. ### 0.2 · Skill authority This skill guides ETL work. It does not amend a product constitution, mapping specification, ADR, source contract, Gold expectation, or release policy. Repository contracts and reviewed evidence outrank this skill. --- ## 1 · MISSION AND NON-GOALS The pipeline exists to create a reproducible search projection from a pinned Oracle state without losing, inventing, duplicating, or silently weakening meaning. ```text Primary outcome = searchable documents ⊕ traceable source membership ⊕ replayable intermediate artifacts ⊕ explained exceptions ⊕ proof of reconciliation ``` The pipeline is: ```text read-only at Oracle · deterministic in meaning · resumable · bounded typed · lineage-producing · evidence-producing · reindexable ``` The pipeline is never: ```text ✗ an Oracle replacement ✗ an Elasticsearch-first source model ✗ an ORM-driven CRUD application ✗ a collection of untracked scripts ✗ a best-effort file copier ✗ an unbounded in-memory transformation ✗ a silent schema coercer ✗ a dashboard that substitutes for evidence ✗ a claim that Parquet/DuckDB/CFF exists merely because it is documented ``` Work backward from search use cases and query behavior. One Elasticsearch document is not assumed to equal one Oracle row. --- ## 2 · FIXED ARCHITECTURE AND AUTHORITY ### 2.1 · Mandatory batch path ```text Oracle -- read-only AS OF SCN --> TSV partitions -- canonical decode ----> Parquet partitions -- declared SQL ---------> DuckDB checks and document-shaping inputs -- pure F# mapping ------> SearchDocument + Lineage -- bounded bulk API -----> versioned Elasticsearch index -- reconciliation -------> proof bundle -- atomic alias swap ----> active search projection ``` The physical implementation may fuse stages for performance only if it still emits the required logical artifacts, manifests, conservation evidence, and restart boundaries. ### 2.2 · Authority order ```text Oracle source contract > reviewed MappingSpec > independently derived fixtures/invariants > generated TSV/Parquet/DuckDB artifacts > Elasticsearch contents > dashboards and logs ``` The current Elasticsearch output is the weakest correctness oracle. Never save its current document as the expected result without independent derivation. ### 2.3 · Constraint disposition Classify every Oracle constraint or business rule: ```text Prevented = illegal output is unrepresentable or rejected before publication Inherited = guaranteed by the pinned source snapshot and preserved exactly Detected = checked after extraction and emitted as an exception Unsupported = known but outside the activated mapping/profile ``` Oracle primary keys, unique constraints, and foreign keys do not automatically exist in Elasticsearch. Preserve, re-express, detect, or explicitly disclaim each one. --- ## 3 · FUNCTIONAL CORE, IMPERATIVE SHELL ### 3.1 · Boundary law ```text FunctionalCore = Types + PureTransforms + Invariants + Decisions + EvidenceModel ImperativeShell = Oracle + Files + ParquetWriter + DuckDB + Elasticsearch + Clock + Cancellation + Logging + ProcessExit FunctionalCore ∩ InfrastructureEffects = ∅ ``` The core receives values, not connections, readers, files, database clients, environment variables, or loggers. ```fsharp type Transform = MappingSpec -> AggregateInput -> Result type ValidateRecord = MappingSpec -> SourceRecord -> Result ``` Core laws: ```text same explicit inputs ⇒ same result no wall-clock lookup · no random lookup · no network · no filesystem no hidden mutable global state · no exception as expected control flow ``` Use immutable records, discriminated unions, `option`, `Result`, private constructors, and total pattern matching. Keep errors local to the domain; map them to CLI, file, DuckDB, or Elasticsearch diagnostics in the shell. ### 3.2 · Shell law The shell owns effects and must declare: ```text input/output locations · credentials · timeouts · cancellation capacity · concurrency · ordering · retry · atomicity · cleanup · evidence ``` Conceptually: ```fsharp type EtlPorts = { OpenOracleSnapshot: SnapshotScn -> Async> ReadChunk: OracleSession * ExtractPlan * KeyRange -> IAsyncEnumerable WriteTsv: TsvPlan * IAsyncEnumerable -> Async> WriteParquet: ParquetPlan * IAsyncEnumerable -> Async> ExecuteDuckDb: DuckDbPlan * Artifact list -> Async> BulkWrite: IndexTarget * BulkBatch -> Async> CommitArtifact: PendingArtifact -> Async> AppendRunRecord: RunRecord -> Async> } ``` The exact syntax may change; effect ownership may not. ### 3.3 · Tooling preference For large extraction, prefer explicit parameterized SQL and a thin Oracle data access layer. Plain Dapper/raw ADO.NET-style access is preferable to a large ORM or CRUD abstraction. Dapper.FSharp may be used for small, clear query surfaces; it is not a reason to hide extraction SQL, fetch behavior, or lineage. PowerShell/Bash may start the F# CLI and supply operator convenience. They must not contain mapping, validation, checkpoint, reconciliation, or verdict logic. --- ## 4 · TYPES, STAGES, AND THE SINGLE MAPPING SPECIFICATION ### 4.1 · Type-state pipeline Use opaque identities and units at the core boundary: ```fsharp type SnapshotScn = private SnapshotScn of bigint type SourceScn = private SourceScn of bigint type DocumentId = private DocumentId of string type MappingDigest = private MappingDigest of string type SemanticDigest = private SemanticDigest of string ``` Do not expose the constructors until range, canonical representation, and identity rules pass. In particular, Oracle SCN and an Elasticsearch external version are different types even when a value can be converted between them. ```text OracleWireRow → ParsedSourceRecord → ValidatedSourceRecord → CanonicalTsvRow → TypedParquetRow → VerifiedAggregateInput → SearchDocument → BulkOperation → IndexedDocument → ReconciledDocument ``` Skipping a stage is forbidden when it would bypass parsing, validation, fidelity, trust, or evidence. ```fsharp type StageOutcome<'ok,'error> = | Accepted of 'ok | Rejected of 'error | Quarantined of 'error | Deferred of reason: string ``` ### 4.2 · MappingSpec law One versioned typed specification governs every representation: ```text MappingSpec = SourceQueries ⊕ SourceKeys ⊕ JoinsAndFilters ⊕ OracleTypePolicies ⊕ CanonicalFieldModel ⊕ TransformFunctions ⊕ Constraints ⊕ DocumentIdentity ⊕ ElasticsearchMapping ⊕ DeletionPolicy ⊕ Lineage ⊕ Compatibility ⊕ TestObligations ``` From one reviewed spec, emit or verify: ```text Oracle extraction contract TSV schema/profile Parquet schema DuckDB table/check plan F# refined types and codecs Elasticsearch mapping/template lineage catalog contract fixtures ``` Generated files are reviewed, diffable artifacts. No generator may silently discard precision, scale, nullability, units, time-zone meaning, identity, ordering, provenance, or unsupported constraints. ### 4.3 · Contract versions Version independently: ```text SourceContractVersion MappingSpecVersion TsvProfileVersion ParquetSchemaVersion DuckDbCheckPlanVersion SearchDocumentVersion ElasticsearchIndexVersion ProofBundleVersion ``` These versions may ship together but are never assumed equal. ### 4.4 · Document identity ```text DocumentId = stableCanonicalKey(BusinessKey, DocumentKind) ``` The ID must not depend on row order, extraction chunk, process ID, current time, or random GUID generation. If identity rules change, treat it as a migration and reindex event, not an ordinary code refactor. --- ## 5 · ORACLE SNAPSHOT AND EXTRACTION LAW ### 5.1 · Source consistency ```text RunSnapshot = Oracle@SnapshotScn ∀query ∈ ExtractPlan : query executes against SnapshotScn ``` Use SCN rather than independently evaluated timestamps when a cross-query consistent source image is required. Preflight must prove: ```text FlashbackPrivilege ∧ SnapshotScnAvailable ∧ UndoWindowSufficient ∧ ReferencedObjectsCompatible ∧ NoUnreviewedDDLConflict ``` If the required historical state cannot be reconstructed, fail with a typed `SnapshotUnavailable`; never fall forward to a different SCN silently. ### 5.2 · Extraction contract Each extract declares: ```text query ID and digest · bind variables · source objects/columns business and partition keys · ordering · expected type/nullability SCN · fetch size · command timeout · key-range semantics row and byte budgets · direct and indirect lineage ``` Indirect lineage includes columns used only in joins, filters, conditions, precedence, and document membership—not merely fields copied into output. ### 5.3 · Deterministic partitioning Prefer stable key ranges or source partitions: ```text SourceKeys = Range₁ ⊎ Range₂ ⊎ ... ⊎ Rangeₙ Rangeᵢ ∩ Rangeⱼ = ∅ for i ≠ j Σ RangeMembership = SourceMembership ``` Use keyset/range pagination with explicit ordering. Do not use offset pagination for a mutable or massive source. If a business key is not unique, add a stable tie-breaker to make the order total. ### 5.4 · Oracle type boundary Explicitly decide and test: ```text NUMBER precision/scale DATE and TIMESTAMP semantics TIMESTAMP WITH TIME ZONE CHAR padding empty string versus NULL RAW/BLOB/CLOB handling Unicode normalization identifier casing NaN/infinity where admitted sentinel/business-null distinctions ``` Time fidelity is field-specific: ```text TIMESTAMP WITH TIME ZONE → InstantUtc ⊕ SourceOffsetOrZoneEvidence, when source representation matters DATE → LocalDateTime ⊕ reviewed TimeMeaning policy ``` Converting a zoned timestamp to UTC may preserve the instant while losing the original offset/zone representation; classify those separately. Oracle `DATE` has no zone, so injecting a session time zone does not by itself establish the business meaning of the value. No value passes through locale-dependent string formatting. A value that cannot be represented under the target policy is rejected or classified as lossy; it is never truncated or rounded incidentally. --- ## 6 · CANONICAL TSV LAW TSV is an inspectable landing contract, not an informal debug dump. Default canonical profile unless the repository defines another version: ```text encoding = UTF-8 line ending = LF delimiter = TAB header = required, stable canonical column order null = \N escape = backslash first, then \t, \n, \r record = exactly one physical line after escaping number = invariant-culture canonical decimal/integer text time = explicitly declared ISO-8601 profile and zone policy ``` The TSV profile must distinguish: ```text NULL ≠ empty string ≠ whitespace ≠ missing column ``` Required laws: ```text decodeTsv(encodeTsv(x)) ≅ x with classified fidelity encodeTsv(x) contains no unescaped delimiter or record separator same row + same profile ⇒ same UTF-8 bytes ``` Every committed partition has: ```text schema/profile version · SCN · key range · row count · byte count content digest · min/max key · rejected/quarantined counts producer/toolchain version · source-query digest ``` `.partial`, header-only-after-failure, or manifest-less files are not committed inputs to Parquet conversion. ### 6.1 · Intake and quarantine ```text DecodedRow = Valid(ValidatedRecord) | Invalid(ValidationError list) DecodeResult = Decoded(DecodedRow) | Unparseable(ParseDiagnostic) Valid ⇒ canonical Parquet path Invalid ⇒ governed quarantine artifact + source key/pointer + evidence Unparseable ⇒ bounded raw evidence or protected raw locator + diagnostic ``` Normalization never overwrites the source representation. Preserve enough evidence to reproduce the failure without requiring every run to duplicate all Oracle bytes indefinitely. Retention, privacy, and size policy decide whether the evidence is inline bytes, an encrypted sample, or a digest plus durable source locator. --- ## 7 · TYPED PARQUET LAW Parquet is the replayable typed analytical artifact. ```text ParquetArtifact = typed replay evidence ParquetArtifact ≠ proof that source data or business interpretation is correct ``` Each column declares: ```text physical type · logical type · nullability · precision · scale time unit · time-zone adjustment semantics · field identity · schema version ``` Required laws: ```text readParquet(writeParquet(rows, schema)) ≅ rows with classified field-level fidelity ParquetSchema = projection(MappingSpec, ParquetProfileVersion) ``` Policies: - Preserve exact decimals using an explicit decimal logical type and approved precision/scale; never route exact values through binary floating point. - Pin writer library, format features, compression codec, row-group policy, and metadata policy in the run manifest. - Sort by the declared stable key when downstream semantic digests or merge behavior require order. - Do not enable schema union-by-name as an automatic production repair. A missing or new column is schema drift requiring a classified decision. - Record file digest, semantic digest, row groups, row count, schema digest, min/max keys, and writer profile. - Keep Parquet immutable. Correction creates a successor artifact with a reason and lineage; it never edits a released file in place. --- ## 8 · DUCKDB VERIFICATION AND RECONCILIATION LAW DuckDB executes declared relational checks over Parquet and derived tables. It does not invent business meaning. ```text DuckDbPlan = versioned SQL/checks derived from or linked to MappingSpec DuckDbDatabase = rebuildable derived state ``` Required checks, selected by mapping: ```text schema and logical types · row/byte counts · duplicate keys required/null constraints · range and code constraints join cardinality · orphan detection · filter membership aggregate reconciliation · document membership · partition overlap/gaps source-to-document and document-to-source traceability ``` Laws: ```text same(ParquetSet, DuckDbPlan, DuckDbVersion) ⇒ same(CanonicalCheckReport) canonical(report) sorts every unordered result before digest ``` Rules may be evaluated set-wise in DuckDB for scale, but normative meaning must remain in the MappingSpec or an explicitly reviewed check contract. Hidden SQL in an operator notebook is not a governed rule. Avoid `SELECT *` at governed boundaries. Name fields, casts, join keys, and expected cardinality. A DuckDB optimization must not change canonical results. --- ## 9 · ELASTICSEARCH PROJECTION LAW ### 9.1 · Governed index shape ```text SearchUseCases → SearchDocument → ExplicitMapping → VersionedIndex → Alias ``` Required policy: ```text explicit mappings · dynamic = strict · index templates under version control versioned physical indices · applications read through aliases writes target the governed write alias or explicit candidate index mapping change requiring incompatibility ⇒ new index + reindex ``` The mapping is designed from queries, filters, sorting, aggregations, highlighting, and document retrieval needs—not copied mechanically from Oracle tables. Field-level search fidelity is explicit: ```text SearchFieldFidelity = Exact | SearchDerived(analyzer or normalization) | Quantized(scale or precision) | Omitted(reason) | Unsupported(reason) ElasticsearchDocument = Project(VerifiedDataset, SearchMapping) ElasticsearchDocument ⊄ VerifiedDataset as a literal set relation ``` Analyzed text is a derived search surface, not the canonical string. When exact lookup and analyzed search are both required, model separate governed fields and test both. Exact financial or statutory reconciliation uses Oracle or the canonical Parquet/DuckDB proof path, never an analyzed or quantized search field. ### 9.2 · Bulk law ```text BulkRequest = bounded(actions, encodedBytes, inFlightRequests) ``` ```text HTTP success ≠ bulk success BulkSuccess ⇔ every item has an accepted terminal result ``` Parse and record every item result. Retry only eligible transient items; do not replay successful items unnecessarily. Bound request bytes as well as action count because document sizes vary. ### 9.3 · SCN ordering and idempotency ```text newer SourceScn wins for the same DocumentId same(DocumentId, SourceScn, PayloadDigest) ⇒ same indexed meaning ``` Define the document version causally: ```text BatchDocumentVersion = SnapshotScn RealtimeDocumentVersion = closed aggregate watermark publish(DocumentId, version) ⇒ every required dependency for that document is complete through version ``` Do not use the maximum SCN from one arrived row while other joined inputs are still behind that watermark. Use Elasticsearch external versioning only after proving a lossless monotonic mapping from Oracle SCN into the supported version range and defining equal-SCN behavior. Otherwise store SCN as governed metadata and enforce the comparison inside the controlled sink/state layer. ```text same DocumentId + same SCN + different payload digest ⇒ NondeterminismIncident ``` Deletes require an explicit tombstone/delete policy with SCN ordering. Absence from one partial extract is never sufficient proof of deletion. ### 9.4 · Candidate publication ```text CandidateReady = MappingInstalled ∧ BulkComplete ∧ ItemFailuresResolved ∧ CountsReconciled ∧ QueryRegressionPasses ∧ SampleMembershipProven ∧ ProofBundleCommitted ``` Only then perform the atomic alias update. The previous index remains immutable and available for rollback according to retention policy. A channel pointer may move; a versioned index identity and its proof may not. --- ## 10 · CONSERVATION, LINEAGE, AND FIDELITY ### 10.1 · Run conservation Every discovered unit occupies exactly one visible state: ```text Discovered = Accepted ⊎ Rejected ⊎ Quarantined ⊎ Deferred ⊎ Unprocessed Complete(run) ⇒ Deferred = 0 ∧ Unprocessed = 0 ``` For a one-to-many or many-to-one document model, do not compare row count to document count blindly: ```text SourceRows → AggregateMembership → SearchDocuments ``` Prove membership instead: ```text ∀accepted source key : mapped to declared document(s) or declared exclusion ∀search document : lineage resolves to admitted source membership duplicate document IDs within candidate = ∅ ``` ### 10.2 · Lineage grade ```text Direct = source value copied or exactly converted Derived = reproducibly calculated from named inputs and function version Conditional = source value controls filter, branch, or precedence Join = source value establishes membership or association Declared = reviewed constant/policy supplied by MappingSpec Opaque = origin cannot be established ``` An Elasticsearch field traces through: ```text ES field → F# transform/declared relational expression → canonical field → Oracle table.column(s) → join/filter/rule → fixtures and proof evidence ``` `Opaque` lineage cannot support a `PROVEN` projection claim. ### 10.3 · Fidelity ```fsharp type RepresentationFidelity = | Lossless | Widened of reason: string | Narrowed of reason: string | Unrepresentable of reason: string type SemanticFidelity = | Exact | Approximate of reason: string | Unsupported of reason: string | Unknown ``` Compute fidelity for every edge: ```text Oracle → F# types → TSV → Parquet → DuckDB → SearchDocument → ES mapping ``` High-risk narrowing, unrepresentable values, or unknown semantic fidelity block publication unless the activated profile explicitly excludes the affected data and records item-level evidence. ### 10.4 · Numeric semantics Do not impose one numeric type on every domain: ```fsharp type NumericSemantics = | ExactInteger | ExactDecimal of precision: int * scale: int | ScaledInteger of scale: int | ApproximateBinaryFloat of tolerance: decimal ``` ```text ExactInteger ∪ ExactDecimal ∪ ScaledInteger ⇒ no conversion through IEEE-754 float/double ``` `System.Decimal` is valid only when the admitted Oracle precision and scale fit its proven range. Oracle `NUMBER` can require a wider representation; use a validated big-integer/scaled representation or reject it explicitly rather than overflow, truncate, or pretend `System.Decimal ≡ NUMBER(p,s)` universally. Elasticsearch `scaled_float` is acceptable only when its scale, rounding, and range preserve the declared policy; otherwise retain the exact value in the canonical artifact and expose a separately classified search representation. --- ## 11 · PARTITIONS, CHECKPOINTS, RESTART, AND ATOMIC PUBLICATION ### 11.1 · Partition contract Each work unit has stable identity: ```text PartitionId = hash(RunId, Stage, SourceContract, SnapshotScn, KeyRange, MappingDigest) ``` Do not use process-local sequence numbers as durable identity. ### 11.2 · Commit protocol ```text write temporary → flush and close → validate schema/counts → compute byte and semantic digests → write manifest → durable atomic finalization → append success record → expose to next stage ``` On filesystems with reliable same-volume atomic rename, use it. Else use a versioned commit-marker protocol whose reader conformance tests prove that uncommitted outputs are invisible. ### 11.3 · Restart law ```text resume(run) = read append-only ledger → verify committed artifacts → reuse only matching pinned inputs and digests → restart incomplete units ``` ```text same pinned partition retry ⇒ same SemanticDigest different digest ⇒ blocking nondeterminism incident ``` Idempotence is defined at the data-effect boundary: ```text same pinned retry ⇒ same committed semantic artifacts + same search meaning ``` It does not mean `Run(Run(x)) = Run(x)` across every observable byte. A retry correctly appends a new attempt record, timestamps, and resource evidence to the run ledger while preserving the canonical data result. A restart never treats `.partial`, missing-manifest, failed-bulk, or unverified DuckDB output as committed. ### 11.4 · Run ledger Append one immutable record per material stage/unit: ```text run/stage/partition ID · status · predecessor IDs source SCN/query/mapping/schema/toolchain versions and digests input/output locators, counts, bytes, min/max keys, digests accepted/rejected/quarantined/deferred/unprocessed start/end/duration · capacity/resource use · retry history error classifications · target index/alias · proof locator correction/supersession reference ``` Dashboards and DuckDB tables may index the ledger; they never replace it as evidence. Correction creates a linked successor record. ### 11.5 · CFF discipline The evidence bundle is CFF-inspired, not automatically CFF-conformant: ```text EtlProofBundle = manifest ⊕ mapping/spec digests ⊕ source snapshot and query digests ⊕ artifact manifests ⊕ check/reconciliation reports ⊕ test/proof manifest ⊕ target index and alias disposition ``` Recommended logical layout: ```text manifest.json contracts/ # MappingSpec, schemas, query and mapping digests source/ # source pointers/manifests; raw samples only by policy landing/ # committed TSV manifests and optional partitions canonical/ # immutable Parquet manifests/files or durable locators checks/ # DuckDB plans and canonical check reports rejects/ # quarantine manifests, diagnostics, protected raw links projection/ # ES mapping, bulk/reconciliation and alias disposition proof/ # test, mutation, performance and restart evidence signatures/ # optional approvals/signatures under a defined trust policy ``` The container requires path safety, size/count limits, per-entry digests, canonical manifest rules, versioning, and safe extraction before it becomes a portable format rather than a directory convention. ```text EtlProofBundle ≠ CFF ``` Call it CFF only when a versioned ETL profile, canonicalization, safe archive, replay semantics, signatures, and conformance suite actually exist and pass. --- ## 12 · BOUNDED EXECUTION AND FAILURE POLICY ### 12.1 · Structured concurrency Every potentially large edge declares: ```text item/byte capacity · max concurrency · producer/consumer ownership overflow/backpressure · ordering · timeout · cancellation retry/quarantine · normal completion · fault propagation CPU/memory/disk/file-count budget · measured usage ``` Required laws: ```text every child task is awaited, supervised, or explicitly owned every fan-out and queue is bounded cancellation stops new work and accounts for in-flight work terminal completion is awaited before success concurrency changes timing, never canonical meaning ``` Forbidden for large input: ```text unbounded task lists · unbounded channels · whole-corpus materialization fire-and-forget writes · nested uncontrolled parallelism per-row logging without budget · silent queue drop · hidden retry loops ``` Do not claim `O(1)` memory merely because the full file is not loaded. State a real bound: ```text PeakMemory ≤ FixedRuntime + InputBufferBytes + OutputBufferBytes + MaxConcurrency × MaxItemBytes + BoundedWindowOrJoinState ``` Every term has a configured or measured maximum. If join/window state can grow with the dataset, partition or spill it under an explicit disk budget. ### 12.2 · Error algebra Distinguish at least: ```text SourceSnapshotUnavailable · SourceContractViolation · ParseRejected SchemaDrift · FidelityLoss · MappingViolation · DuplicateIdentity ResourceLimit · TransientDependency · PermanentDependency BulkItemRejected · ConservationViolation · ReconciliationMismatch Cancelled · InternalDefect ``` Retry law: ```text Retry(error, operation) ⇔ Transient(error) ∧ Idempotent(operation) ∧ attempts < ApprovedMaximum ∧ ¬Cancelled ``` Malformed input, schema drift, authorization, mapping, fidelity, deterministic bulk rejection, and reconciliation errors require disposition—not retries. ### 12.3 · Failure scope Declare per rule whether one bad item: ```text RejectsItem | QuarantinesPartition | AbortsRun ``` The choice depends on whether continued processing can preserve completeness, referential meaning, and publication safety. Never continue merely to make the throughput graph green. ### 12.4 · Planning scale profile Use the previously discussed envelope as a planning corpus, not as a proven production claim: ```text Oracle source surface ≈ 50 tables Source rows ≤ 1,000,000,000 Search documents ≤ 150,000,000 ``` Replace these bounds with measured per-profile values when the real source and document model are known. Prove peak disk, memory, Oracle load, Parquet size, DuckDB working set, Elasticsearch bulk rate, restart time, and end-to-end duration on a representative corpus. A benchmark on toy data cannot promote the large-volume capability to `PROVEN`. --- ## 13 · OPTIONAL BATCH/CDC CONVERGENCE PROFILE CDC is optional. The batch pipeline must work independently. ```text Batch: Oracle@S → TSV → Parquet → DuckDB → ES candidate CDC: Oracle GoldenGate → Kafka → keyed state → ES sink ``` Both paths consume the same MappingSpec and document identity rules. Before consuming changes above `S`, initialize every required keyed join/state store from the accepted batch snapshot at `S`. A realtime document may be emitted only at a closed per-document watermark. ```text BatchThenCdc(S,T) ≅ CdcReplayTo(T) ``` for the same admitted Oracle history, mapping version, deletion policy, and ordering rule. ```text highest SourceScn wins per DocumentId equal SCN + unequal payload ⇒ nondeterminism/conflict evidence ``` If workflow coordination is activated, use explicit Kafka topics such as: ```text workflow.raw = entry/inbox workflow.ready = runnable work workflow.completed = completion feedback workflow.state.compacted = latest dependency state per key workflow.deadletter = parked terminal failure ``` Topic names are replaceable configuration; state, ownership, compaction, partitioning, retry, and conservation laws are not. --- ## 14 · TESTING AND PROOF LAW ### 14.1 · Test layers ```text T0 repository/compile-order integrity T1 refined constructors and codecs T2 Oracle type and TSV boundary matrix T3 TSV ↔ typed row ↔ Parquet round-trip T4 pure transform examples and Gold documents T5 FsCheck properties and metamorphic relations T6 mapping/schema mutation tests T7 DuckDB set/cardinality/reconciliation checks T8 checkpoint, crash, cancellation, disk-full, and restart T9 Elasticsearch bulk partial-failure/idempotency/SCN ordering T10 candidate-index query regression and alias rollback T11 batch/CDC convergence when CDC is active T12 realistic volume/resource/performance proof ``` ### 14.2 · Required properties ```text same pinned input ⇒ same semantic output missing required value never becomes a valid default TSV and Parquet round-trips preserve classified meaning every discovered item has one visible disposition partitions neither overlap nor leave gaps reordering independent partitions does not change canonical output duplicate command/retry does not duplicate search effect older SCN cannot overwrite newer document state unknown field is rejected by strict ES mapping bulk partial failure cannot be reported as success restart cannot consume uncommitted output every ES document has resolvable lineage every public parser converts hostile input to a typed diagnostic ``` ### 14.3 · Mutation operators ```text drop source row · duplicate source row · change join type · remove filter swap NULL/empty · change decimal scale · change time zone · trim CHAR remove tie-breaker · overlap key ranges · skip manifest/digest mark partial bulk as success · accept unknown ES field allow older SCN overwrite · ignore tombstone · reorder precedence ``` The current implementation is not the Gold oracle. Expected results come from reviewed examples, hand-derived aggregates, source contracts, and approved invariants. ### 14.4 · Proof manifest ```text Proof = source/mapping/schema/query digests ⊕ corpus and generator seeds ⊕ test/mutation/reconciliation results ⊕ toolchain and dependency graph ⊕ artifact and candidate-index digests ⊕ performance/resource measurements ⊕ known gaps and reviewer disposition ``` `Contact ≥ 1` is required before a production claim: at least one independent consumer must successfully validate the projection on a real workflow. --- ## 15 · SECURITY, PRIVACY, OBSERVABILITY, REPOSITORY, AND CLI ### 15.1 · Security and privacy ```text OracleCredential ⇒ read-only, least privilege, scoped objects ElasticsearchCredential ⇒ candidate-index/write-alias scope only Secrets ∩ Logs/Manifests/Artifacts = ∅ ``` Use parameterized SQL, allowlisted extraction plans, encrypted transport, governed local permissions, explicit retention/wipe, and sanitized fixtures. Do not log full rows or PII by default. Record stable identifiers/digests and retrieve protected evidence through authorized tooling when needed. Dependencies, native libraries, CLI tools, containers, and CI actions are pinned and supply-chain reviewed. A DuckDB/Parquet/native crash is owned at its adapter boundary and cannot silently corrupt a committed artifact. ### 15.2 · Observability Record bounded stage-level telemetry: ```text rows/bytes read and written · records/sec · bytes/sec queue depth · in-flight work · latency · retries rejected/quarantined counts · memory/CPU/disk · Oracle/ES wait checkpoint age · SCN lag · bulk item failures · reconciliation delta ``` Metrics explain operation; manifests prove a run. Per-row logs are forbidden unless a bounded diagnostic sample is explicitly authorized. ### 15.3 · Repository shape ```text /src Etl.Domain # refined types, errors, laws Etl.Contracts # MappingSpec, schemas, lineage, manifests Etl.Core # pure validation/transformation/reconciliation model Etl.Application # use cases and orchestration contracts Etl.Oracle # read-only extraction adapter Etl.Tsv # canonical codec Etl.Parquet # typed reader/writer adapter Etl.DuckDb # declared set checks and analytical adapter Etl.Elasticsearch # strict mapping, bulk sink, alias operations Etl.Cli # composition root and operator interface /tests Unit · Boundaries · Properties · Mutations · Gold RoundTrip · Reconciliation · Restart · Security · Performance /contracts # mapping/source/document/schema profiles /sql # versioned Oracle and DuckDB plans /corpus # public synthetic, sanitized, regressions /evidence # schemas for run ledger and proof manifests ``` Respect F# file compile order. Dependency direction points inward toward domain, contracts, and core. No core project references an adapter. ### 15.4 · CLI contract The F# CLI is the primary product and reproducibility surface: ```text etl preflight # privileges, SCN/undo, schemas, disk, target policy etl plan # partitions, budgets, mapping and query digests etl extract # Oracle@SCN → canonical TSV etl parquet # TSV → typed Parquet etl verify # DuckDB checks and reconciliation inputs etl project # pure mapping → bounded ES bulk candidate etl reconcile # source/artifacts/candidate comparison etl publish # gated atomic alias change etl resume # ledger-driven restart etl replay # reproduce a partition/document/run etl proof # emit EtlProofBundle/proof manifest etl status # truthful stage/capability status ``` Each command declares inputs, output artifacts, stable JSON schema, exit codes, cancellation, and evidence. `publish` never performs an implicit extraction or unreviewed mapping change. --- ## 16 · WORKING PROTOCOL FOR AN AI OR HUMAN AGENT When this skill is invoked: 1. **Classify the task:** design, implement, review, diagnose, optimize, migrate, operate, or explain. 2. **Inspect before proposing:** repository tree, `.fsproj` compile order, MappingSpec, SQL, manifests, tests, mappings, aliases, and current status. 3. **State the affected law:** identify the invariant, boundary, or evidence obligation that motivates the change. 4. **Write the contract first:** types, function signature, failure algebra, resource budget, and falsifier. 5. **Create a red witness:** failing example, boundary, property, mutation, reconciliation mismatch, or restart case. 6. **Implement the smallest core change:** pure F# before adapter orchestration. 7. **Wire the shell explicitly:** effects, cancellation, capacity, retry, cleanup, and evidence. 8. **Run affected proof layers:** do not claim checks that were not executed. 9. **Update traceability and status:** implementation without evidence remains `IMPLEMENTED`, not `PROVEN`. 10. **Report outcome first:** changed behavior, evidence, known gaps, and the next blocked gate. For diagnosis, determine and explain the failure before modifying code unless the user also asks for a fix. Preserve unrelated work and existing decisions. ### 16.1 · Requirement normal form For every material feature, produce: ```text Requirement = Intent ⊕ TypedContract ⊕ Invariant ⊕ Falsifier ⊕ BoundaryFamily ⊕ Evidence ⊕ Owner ⊕ Status ``` ### 16.2 · Traceability matrix | Source intent | Law | F# core | Shell/adapter | Verification | Evidence/status | |---|---|---|---|---|---| | Preserve one Oracle snapshot | §5.1 | snapshot type | Oracle session | cross-query SCN case | run manifest | | No lost records | §10.1 | disposition algebra | stage ledger | conservation property | reconciliation report | | Exact representation | §§5.4, 6, 7, 10.3 | codecs/refined types | writers/readers | round-trip matrix | fidelity report | | Safe reindex | §9.4 | candidate decision | ES bulk/alias | query + rollback test | proof bundle | | Reliable restart | §11 | checkpoint decisions | files/ledger | crash/fault suite | restart report | --- ## 17 · TRUTHFUL STATUS AND RELEASE LAW | Status | Meaning | |---|---| | `DESIGNED` | contract/ADR exists; behavior is not claimed | | `EXPERIMENTAL` | works under displayed limitations | | `IMPLEMENTED` | code exists and focused tests pass | | `PROVEN` | falsifiers, reconciliation, restart, volume, clean build, evidence, and independent contact pass | | `DORMANT` | intentionally deferred with wake condition | | `REJECTED` | violates authority, fidelity, boundedness, or evidence law | ```text ReleaseReady = CleanCloneBuild ∧ LockedDependencies ∧ MappingAndSchemaDiffReviewed ∧ GoldAndPropertySuitesPass ∧ CriticalMutationsKilled ∧ RestartAndFaultSuitePass ∧ CandidateReconciled ∧ QueryRegressionPass ∧ RollbackRehearsed ∧ ProofBundleCommitted ∧ TruthfulDocs ``` Forbidden: ```text ✗ || true on a required gate ✗ skipped partition presented as complete ✗ partial bulk response presented as success ✗ schema drift accepted by inference ✗ alias swap before proof ✗ mutable/unpinned input inside a released run ✗ regenerated expected output auto-approved ``` --- ## 18 · LANDMINE REGISTER | Landmine | Mathematical countermeasure | |---|---| | Queries see different Oracle moments | one pinned `SnapshotScn` for the extract plan | | Undo/DDL invalidates snapshot | explicit SCN/undo/object preflight | | Offset pagination loses/duplicates rows | disjoint stable key ranges with total ordering | | TSV collapses null/empty/control characters | versioned canonical escaping and null profile | | Decimal or time meaning drifts | explicit precision/scale/unit/zone plus round-trip tests | | Oracle `DATE` is assigned meaning from session time zone alone | reviewed per-field `TimeMeaning` policy | | UTC conversion loses the original zone/offset | preserve instant and source zone evidence when required | | Oracle `NUMBER` is assumed to fit `System.Decimal` | range proof or validated wider exact representation | | Parquet bytes vary and are called deterministic | separate file digest from semantic digest | | Parquet existence is called proof of source correctness | typed replay evidence remains separate from source/business truth | | DuckDB SQL becomes hidden business logic | MappingSpec-linked, versioned declared check plan | | One Oracle row is forced into one ES document | explicit aggregate membership and document identity | | Elasticsearch invents fields | explicit mapping with `dynamic: strict` | | Analyzed/quantized ES field is used for exact reconciliation | reconcile from Oracle or canonical Parquet/DuckDB evidence | | HTTP 200 hides failed bulk items | per-item terminal accounting | | Retry overwrites newer data | SCN ordering and idempotent document identity | | Missing row is mistaken for deletion | explicit tombstone/delete evidence | | Queue or task count explodes | bounded channels, fan-out, bytes, and in-flight work | | Streaming is called `O(1)` while join/window state grows | explicit peak-memory equation plus partition/spill budget | | Crash exposes half-written artifact | temporary-write/manifest/commit protocol | | Restart reuses corrupt output | ledger plus digest verification | | Counts match while membership is wrong | stable-key lineage and bidirectional reconciliation | | Current ES output becomes Gold truth | independent oracle hierarchy | | CFF is claimed from a ZIP and manifest | named profile plus actual conformance suite | | PowerShell accumulates rules | F# CLI owns behavior; scripts remain thin wrappers | | Mapping change breaks consumers | versioned index, query regression, atomic alias swap | | Performance tuning changes meaning | canonical agreement before/after optimization | --- ## 19 · DEFAULT EXECUTION ORDER ```text Gate 0 Truth reset inventory tables, search use cases, current mappings, volumes, gaps Gate 1 Typed contract MappingSpec, source keys, document identity, lineage, fidelity policy Gate 2 Snapshot proof SCN/undo/DDL preflight and one deterministic Oracle partition Gate 3 Canonical landing TSV codec, manifest, conservation, restart Gate 4 Typed artifact Parquet schema, exact types, round-trip and semantic digest Gate 5 Set verification DuckDB checks, joins, duplicates, counts, membership reconciliation Gate 6 Search candidate strict mapping, versioned index, bounded bulk, per-item accounting Gate 7 Publication proof query regression, source/artifact/ES reconciliation, alias rollback Gate 8 Scale proof realistic volume, bounded resources, cancellation, crash and resume Gate 9 Optional CDC shared MappingSpec, SCN ordering, batch/CDC convergence ``` The sequence is gate-driven, not calendar-driven. A stage advances only when its exit evidence exists. --- ## 20 · FINAL FORM ```text LargeTrustedETL = Oracle@SCN ⊕ OneMappingSpec ⊕ PureFSharpCore ⊕ BoundedImperativeShell ⊕ CanonicalTSV ⊕ TypedParquet ⊕ DuckDBVerification ⊕ VersionedElasticsearchProjection ⊕ ConservationAndLineage ⊕ AtomicRestartableArtifacts ⊕ ReconciliationAndProof ``` ```text Oracle owns business truth. F# owns explicit meaning. TSV owns inspectable landing. Parquet owns typed replay. DuckDB owns set-based verification. Elasticsearch owns search projection. The run ledger owns operational history. The proof bundle owns reproducible evidence. ``` > **The moat is not Oracle, TSV, Parquet, DuckDB, Elasticsearch, Dapper, or > F#. The moat is the conserved and replayable chain from one pinned source > state to one governed search projection—with enough evidence to explain every > document, every exclusion, every retry, and every byte that was published.** --- ## 21 · OFFICIAL IMPLEMENTATION REFERENCES - Oracle Flashback Query and `AS OF SCN`: - Apache Parquet logical types: - DuckDB Parquet reading/writing and metadata: and - Elasticsearch Bulk API: - Elasticsearch mappings and index templates: and - Elasticsearch atomic alias operations: - Elasticsearch external version behavior: Pin the exact product and format versions in the repository. These references inform implementation; the reviewed MappingSpec, contracts, and evidence govern the pipeline.