Spensara Spensara Knowledge Base & Documentation Repository
System Overview Brink & Borg Master Index (722 Docs) Spensara Platform Project Tracker

Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)
Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Categories

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.
branches/feature-fleet-import/etl/docs/features/ETL/fleet/fleet-s3-direct-import-operations.md 8,838 bytes · Updated Oct 23, 2026 10:44 AM EDT
Raw File

Fleet S3 Direct Import Operations

Scope And Ownership

This is a temporary, admin-triggered path for Fleet exports already uploaded to S3. It snapshots source objects, replaces one tenant/source slice in Redshift, then publishes source-scoped Parquet for Athena.

  • Owner: data platform/ETL operators.
  • Source folder: fleet/{sourceFolder}/year=YYYY/month=MM/day=DD/*.json.
  • Source identity: fleet_s3:{sourceFolder}. Folder matching is case-sensitive.
  • Promotion trigger: move Fleet into standard recurring ETL when a second customer needs it, recurring delivery starts, incremental ingestion is required, or temporary-path operations become routine.
  • Sunset requirement: remove this module after promoted Fleet ETL reaches parity and all retained workspaces/retry windows expire. Keep ingestion-batch audit history.

Start An Import

Admin endpoint:

http
POST /etl/fleet-s3/import
Content-Type: application/json

{
  "customerId": "<customer UUID>",
  "tenantId": "<tenant UUID>",
  "sourceFolder": "T002",
  "overrideExisting": true,
  "requestedBy": "operator@example.com"
}

Successful reservation returns 202 Accepted with jobId and batchId. Only IDs enter TickerQ. Immutable customer, tenant, warehouse, source, requester, and override context remains in batch details.

The request fails before S3 or Redshift access when folder syntax, customer, tenant ownership, or active/Succeeded warehouse mapping is invalid. 409 Conflict means same tenant/source is already reserved or warehouse state cannot accept work.

Retry Publication

http
POST /etl/fleet-s3/import/{batchId}/retry-publication

Use only when batch is Failed, phase is failed_publication, Redshift commit is durable, retry lifetime has not expired, and no newer batch superseded same tenant/source. Retry atomically reacquires source reservation and queues same batch. It never creates another batch or repeats committed Redshift replacement.

Decision tree:

  1. Failure before redshift_committed: fix source/configuration issue and submit new import. Do not use publication retry.
  2. failed_publication with redshiftCommitted=true: inspect journal and active objects, fix S3/UNLOAD/Athena issue, then call publication retry.
  3. Newer batch exists, phase is superseded, or retry expired: do not retry old batch.
  4. Unknown state or lineage mismatch: stop and preserve artifacts for investigation.

Source Consistency

Upload must be complete before request submission. Do not add, replace, or delete objects under source prefix until snapshot reaches snapshot_ready. Snapshot lists metadata, then conditionally copies each exact ETag/version into batch-owned workspace. Source mutation causes pre-commit failure; importer never downloads every source payload into process memory.

Expected operating envelope is at least 36,500 objects/year. Snapshot uses paginated metadata listing, bounded conditional-copy concurrency, per-date Redshift manifests, and external inventory/index documents. Durable batch details store per-date manifest summaries, not per-object payload metadata.

Configuration And IAM

Required server configuration:

  • FleetS3ImportOptions: source bucket/root, workspace/staging/stable roots, copy concurrency, object-size limit, command timeout, retention, retry lifetime, and lease duration.
  • AWS credentials or role configuration used by warehouse S3 services.
  • tenant Redshift connection and COPY/UNLOAD authorization.
  • TickerQ durable storage and worker execution.
  • deployed Glue/Athena entity tables whose location or partition projection recursively scans below entity={entity}/dt={date}/, including source_type=fleet/source_id={sourceFolder}/.

Least-privilege IAM must allow:

  • source: s3:ListBucket, s3:GetObject, and version reads when bucket versioning is used;
  • batch workspace and publication staging: list, read, write, copy, and delete only configured prefixes;
  • stable curated output: read/write/delete only exact Fleet active prefixes;
  • Redshift COPY/UNLOAD access to configured S3 bucket/prefixes;
  • live verification only: Athena start/get-results/get-execution in disposable workgroup and deletion of its own result objects.

Do not grant source-prefix delete permission to importer. Verify IAM in target environment before release.

Phases And Failures

PhaseMeaning
reservedDurable tenant/source reservation exists.
snapshottingSource metadata listed and immutable copies being created.
snapshot_readyInventory, index, and date manifests validated.
loading_redshift / validatingCandidate rows loaded and contract checks running.
replacing_redshiftTransactional exact tenant/source replacement in progress.
redshift_committedRedshift is durable; retries must not repeat replacement.
publishing_athenaDate/entity Parquet staging and activation journal in progress.
completedRedshift and active Parquet publication validated.
failed_pre_commitNo committed replacement; new import required after correction.
failed_publicationRedshift committed; publication-only retry may be allowed.
supersededNewer batch owns source slice; old publisher must not activate.
publication_expiredRetry lifetime ended; investigate and remove retained workspace after policy permits.

Diagnose Without Payload Exposure

Use batch detailsJson for phase timestamps, bounded diagnostics, Redshift counts, publication journal, and cleanup eligibility. Inspect snapshotIndexUri to see object count/bytes, min/max dates, per-date manifest URIs, and external inventory identity. Inspect manifest URLs and counts, not customer JSON bodies. Never put AWS credentials, SQL authorization clauses, or full source payloads into logs/tickets.

Imported Redshift rows are identified only by:

text
tenant_name = {tenantId}
AND _etl_source_type = fleet_s3:{sourceFolder}

Use _etl_batch_id for lineage inside that exact slice. Athena Parquet includes tenant_id, tenant_name, business_date, _etl_source_type, and _etl_batch_id.

Cleanup And Retention

Successful and failed workspaces remain until configured retention permits cleanup. Cleanup receives explicit batch-owned workspace/staging URIs and is idempotent. It must never delete fleet/{sourceFolder}/ or another batch/customer prefix. Publication retry window and audit needs take precedence over storage cleanup.

Measure production snapshot, COPY, validation, UNLOAD, and Athena query durations at expected volume. Set timeouts from observed upper bounds plus operational margin; do not treat unit-test wall-clock duration as capacity proof.

Remove One Fleet Source

Review IDs and source-folder case before executing. Take audit snapshot/counts first.

  1. Stop/reject new imports for exact tenant/source and wait for active lease/reservation to finish.
  2. Delete Redshift items first using exact boundary below.
  3. Delete Redshift orders second using same exact boundary.
  4. Delete only active Athena prefixes for exact company, tenant, both entities, affected dates, and source folder.
  5. Remove batch-owned workspace/staging prefixes only after retry/retention policy permits.
  6. Retain ingestion batches, details, phase timestamps, counts, and operator audit records.

Exact Redshift predicate:

sql
WHERE tenant_name = '<tenantId>'
  AND _etl_source_type = 'fleet_s3:<sourceFolder>'

Exact active object roots, narrowed further by each affected date:

text
curated/company_id={customerId}/tenant_id={tenantId}/entity=pos_order_items/dt={date}/source_type=fleet/source_id={sourceFolder}/
curated/company_id={customerId}/tenant_id={tenantId}/entity=pos_orders/dt={date}/source_type=fleet/source_id={sourceFolder}/

Never delete a whole tenant, entity, curated/, fleet/, or shared date prefix.

Live Verification

FleetS3EndToEndLiveTests is opt-in with KDSAI_RUN_LIVE_TESTS=1. It reads source folder T002 for 2026-07-08 through 2026-07-20, uses random customer/tenant/batch boundaries, compares Redshift and Athena counts per entity/date/source, and deletes only generated Redshift rows, workspaces, publication staging, active objects, and Athena query outputs. It never modifies source fleet/T002/.

Required live settings:

  • KDSAI_FLEET_REDSHIFT_CONNECTION_STRING
  • KDSAI_FLEET_REDSHIFT_AUTH_CLAUSE
  • KDSAI_FLEET_ATHENA_WORKGROUP
  • KDSAI_FLEET_ATHENA_DATABASE
  • KDSAI_FLEET_ATHENA_ORDERS_TABLE
  • KDSAI_FLEET_ATHENA_ITEMS_TABLE
  • KDSAI_FLEET_ATHENA_OUTPUT_LOCATION
  • AppHost AWS/S3 parameters or equivalent environment values

Failure to discover newly published fixed-key Parquet through deployed catalog is release blocker. Fix Glue table location/projection; do not change frozen active-key ordering.