# MVP: TiDB Cloud Essential Snapshot Export to TiDB Cloud Lake > **Historic note (superseded).** This MVP design informed the current config-driven > ingestion engine, but it is **not an active platform design document**. It predates two > settled decisions and reads against them: > - the extract/load split — code now lives under `ingestion/extract/tidbcloud/` and > `ingestion/load/s3_bulk_load/`, not the single `ingestion/tidbcloud/` tree shown below; > - the load stage materializes **only the raw layer** > ([ADR 0016](../../../docs/adr/0016-load-materializes-only-the-raw-layer.md)), so the > staging delta and current-state tables described here are transform-layer concerns, not > part of load, and the load no longer provisions a `*_staging` database. > > For the active design see > [ADR 0015](../../../docs/adr/0015-contracts-have-a-kind-and-the-producer-owns-the-contract.md), > [ADR 0016](../../../docs/adr/0016-load-materializes-only-the-raw-layer.md), and the > [config-driven clean structure principle](../../../docs/principles/config-driven-clean-structure.md). ## Purpose Define the smallest useful MVP for loading data from a TiDB Cloud Essential cluster into TiDB Cloud Lake without a streaming CDC path. For the immediate data volume, about 30 GB, periodic full snapshots are simpler than TiCDC, Kafka, and per-topic ingestion workers. The MVP should prove: ```text TiDB Cloud Essential export task -> immutable S3 snapshot prefix and manifest -> raw layer data product with source-faithful snapshot tables -> staging layer data product with daily delta and current-state tables -> Git-authored contracts and observable runtime signals ``` This is a batch ingestion slice of the existing lakehouse architecture. It keeps raw landing, staging, contracts, freshness, and observability explicit while avoiding unnecessary streaming infrastructure. It is intentionally a narrow vertical slice through the data product, contract, orchestration, and observability planes, not a general ingestion platform or production implementation spec. ## Facts - TiDB Cloud Starter and Essential can export data to external storage, including Amazon S3. - TiDB Cloud export supports SQL, CSV, and Parquet formats. - TiDB Cloud CLI supports export by table filter, SQL statement, and `WHERE` condition. - TiDB Cloud export is a consistent, lock-free snapshot using separate export compute resources. - The current maximum export size is 1 TiB. - TiDB Cloud Essential export speed is documented as up to 100 MiB/s. - TiDB Cloud exports are one-off tasks; periodic scheduling must be provided by our own scheduler. - For AWS-hosted clusters, S3 export can use an AWS role ARN with `s3:PutObject` and `s3:ListBucket`. - Databend engine context supports loading Parquet and other files from S3 using `COPY INTO`; the target TiDB Cloud Lake tenant must expose the needed equivalent behavior. ## MVP Decision Use scheduled TiDB Cloud Essential exports to S3 as the ingestion mechanism. Export snapshots in Parquet, preferably one task per ingestion run covering the selected table set. Use an external scheduler such as EventBridge Scheduler plus Lambda/ECS, Dagster, Airflow, or GitHub Actions to create and monitor export tasks through the TiDB Cloud CLI or API. Do not use TiCDC, Kafka, MSK, MSK Connect, or `bend-ingest-kafka` for the first MVP unless the freshness requirement changes from daily batch to near-real-time. ## MVP Shape The MVP is one scheduled batch path for a selected table set from one TiDB Cloud Essential cluster. It produces queryable Cloud Lake assets and enough metadata for a future runtime-served contract view, but it does not introduce a generic connector framework. The implementation follows the platform principle for config-driven clean structure: reusable engine code is separated from source-local configuration. ```text ingestion/tidbcloud/ engine/ sources/ ``` Source-specific decisions belong in YAML under `ingestion/tidbcloud/sources//`; connection strings and access keys belong in an ignored `secrets.env` file in that same source directory. This is a platform design principle, not merely an MVP convenience. Minimum data product boundaries: - Raw snapshot data product: a `raw` layer product for the selected source table set. Its assets are append-only snapshot tables and manifest references. Its contract promises source provenance, snapshot cadence, retention, access constraints, and known snapshot blind spots. - Staging state data product: a `staging` layer product for each source table or table group that exposes daily deltas or current-state tables to consumers. Its contract promises typed source-aligned assets, key assumptions, diff semantics, freshness, tests, caveats, and prohibited usage. The raw and staging layers are logical contract classes, not a mandatory lifecycle progression. This MVP uses both because it needs to preserve the source-faithful snapshot and expose a more convenient current-state shape. ## Assumptions - Daily batch freshness is acceptable for the first consumers. - The first source database is `nexuscrm` in the TiDB Cloud Essential cluster. - The first selected table set is about 30 GB and remains comfortably below the documented export size limit. - Full snapshots are affordable enough for the first run cadence; source-side SQL-filtered increments are deferred until a table has a reliable watermark and a consumer need that justifies the added semantics. - Each table used for delta publication has a stable primary key or natural unique key. - Insert-then-delete and multiple updates inside a snapshot interval do not need to be reconstructed for the MVP. - Schema drift is reviewed before promotion when it changes a published contract promise. - The scheduler can call the TiDB Cloud CLI or API, write S3 manifests, and load Cloud Lake tables with the required credentials. - The MVP has an initial owner, domain, governance classification, and retention policy for each published data product. ## Initial NexusCRM Answers Source inspection of the `nexuscrm` database gives enough information to answer the first MVP scoping questions. Inspection summary: - TiDB source version: TiDB Serverless v8.5.3. - Source parent cluster: `10996361222721502359` (`NexusCRM-PingCAP-prod`). - Source branch used by the SQL credentials: `bran-j6opcvj6ene7hdnih3q4viw5oy` (`nexuscrm-pingcap-staging2-20260522-r1`). - Base tables inspected: 197. - Non-empty base tables by `information_schema` estimate: 110. - Base tables with primary keys: 197. - Primary key shape: `Id` for 194 tables; `_System_BootstrapState` uses `StateKey`, `_System_Config` uses `KeyName`, and `_System_RequestCapture` uses `RequestId`. - Tables with `SystemModstamp` or `LastModifiedDate`: 196. - `leadhistory` is the only inspected base table without either watermark column. - Delete marker: many Salesforce-style objects include `IsDeleted`, so the current-state model should preserve soft-deleted state rather than assume physical deletion is the only delete signal. Initial decisions: - Domain: `crm`. - Initial owner: data platform, until business domain owners are assigned for curated or service products. - First raw data product: `crm.nexuscrm_raw_snapshots`. - First staging data product pattern: `crm.nexuscrm__state`. - First raw table set: all 197 base tables may be exported into a restricted raw product because every base table has a primary key. - First exposed staging set: start with consumer-needed CRM business tables. System/security tables can land in raw, but should remain raw-only and restricted until a platform/security owner approves exposure. - First freshness policy: daily snapshot, degraded after 30 hours without a successful run, blocked after 48 hours. - First retention policy: S3 snapshots for 90 days, raw snapshot tables for the latest 30 successful snapshots, delta tables and manifests for 1 year. - First orchestration state location: S3 manifest plus S3 JSONL runtime logs. The manifest is the immutable run artifact; runtime logs are the pipeline-scoped observation surface. - Cloud Lake `COPY INTO` from S3 Parquet is confirmed in the target tenant using `COLUMN_MATCH_MODE = CASE_SENSITIVE`. Snapshot metadata must be stamped after load because TiDB export Parquet files contain only source columns. - The current source config is `ingestion/tidbcloud/sources/nexuscrm/source.yaml`. - The generated contracts live under `ingestion/tidbcloud/sources/nexuscrm/contracts/generated/`. Initial governance classification: - Raw NexusCRM snapshot product: `restricted + customer_data`. - Field and table refinements: `pii`, `financial`, and `operational_sensitive`. - Obvious PII fields include email, phone, mobile, fax, first or last name, address, country, state, postal, birth date, and contact-person fields. - Obvious financial fields include amount, revenue, ARR/MRR/ACV, cost, budget, price, currency, payment, billing, invoice, forecast, quote, order, and contract value fields. - Obvious operational-sensitive fields include password, secret, token or code, consumer key or secret, session ID, source IP, storage key, auth/login, endpoint, URI/URL, connected app, OAuth, and `_System_*` operational tables. Detailed table inventory is captured in: ```text docs/design/nexuscrm-source-inventory.md ``` ## Snapshot Layout Use immutable, run-scoped S3 prefixes: ```text s3:///tidb-essential/snapshots/source=nexuscrm/ cluster=/ run_date=2026-05-25/ run_id=/ -schema-create.sql .
-schema.sql .
.0001.zstd.parquet ``` Keep a separate manifest prefix controlled by our scheduler: ```text s3:///tidb-essential/manifests/source=nexuscrm/ cluster=/ run_date=2026-05-25/ run_id=/ manifest.json ``` Keep a separate runtime-log prefix for pipeline observations: ```text s3:///tidb-essential/logs/source=nexuscrm/ cluster=/ run_date=2026-05-25/ run_id=/ events.jsonl ``` The manifest should include: - Manifest schema version. - Raw data product ID and, when applicable, staging data product ID. - TiDB Cloud cluster ID. - Scheduler run ID. - TiDB Cloud export task ID. - Requested databases and tables. - Export format and compression. - S3 URI. - Export created, started, completed, or failed timestamps. - Row counts or file counts when available. - Snapshot logical date. - Source schema file paths. - Loader status per table into Cloud Lake. - Test status summary. - Product status decision: `healthy`, `degraded`, `blocked`, or `unknown`. - Sanitized export metadata only; do not persist TiDB Cloud's returned S3 access key material in the manifest. Runtime events should include per-table load observations such as loaded rows, duplicate primary key rows, delta counts, previous run ID, status, and Cloud Lake data asset names. Use JSONL first so events are append-friendly and easy to replay; canal-json or Iceberg-compatible event tables can come later if the log needs table-format semantics. ## Export Command Shape The scheduler creates a one-off export task for each snapshot run. Example table-filtered export: ```bash ticloud serverless export create \ -c "$TIDB_CLUSTER_ID" \ --target-type S3 \ --s3.uri "s3://$BUCKET/tidb-essential/snapshots/source=nexuscrm/cluster=$TIDB_CLUSTER_ID/run_date=$RUN_DATE/run_id=$RUN_ID/" \ --s3.role-arn "$TIDB_EXPORT_ROLE_ARN" \ --filter "nexuscrm.*" \ --file-type parquet \ --parquet.compression ZSTD ``` The TiDB Cloud CLI supports `--filter`, `--sql`, and `--where`. Use `--filter` for full table snapshots and reserve `--sql` or `--where` for later source-filtered increments where a table has a reliable watermark column. If governance approval does not cover all raw tables, generate the `--filter` value from the inventory rather than exporting `nexuscrm.*`. ## Cloud Lake Schemas Run idempotent Databend/TiDB Cloud Lake setup outside the TiDB Cloud ingestion engine. Source config declares the setup files: ```yaml setup: databend: files: - setup/databend/tidbcloud/nexuscrm/001_databases.sql ``` The setup engine reads `source.yaml` and runs those files during boot. For NexusCRM, the configured Databend setup lives in: ```text setup/databend/tidbcloud/nexuscrm/ 001_databases.sql ``` The setup creates only data-asset databases for the source: ```sql CREATE DATABASE IF NOT EXISTS nexuscrm_raw; CREATE DATABASE IF NOT EXISTS nexuscrm_staging; ``` There is intentionally no Cloud Lake `control` database. Pipeline-scoped metadata belongs in the configured S3 runtime log so setup does not pollute the warehouse with per-pipeline namespaces. For each source table, create a raw snapshot table with the exported source columns plus metadata: ```text nexuscrm_raw.nexuscrm__snapshots ``` Use the source schema emitted by the TiDB export and the inspected primary key metadata from `docs/design/nexuscrm-source-inventory.md`. Primary keys are contract metadata for the MVP; Cloud Lake does not need to enforce them to run the snapshot diff. ## Raw Snapshot Model Load each exported table into an append-only raw snapshot table in TiDB Cloud Lake. The raw table should include the source columns plus snapshot metadata: ```text _snapshot_date _snapshot_run_id _snapshot_loaded_at _source_database _source_table _source_file ``` Logical table shape: ```text nexuscrm_raw.nexuscrm_account_snapshots ``` The raw snapshot table is an asset of the raw snapshot data product. Its contract layer is `raw`: it is source-faithful at a snapshot grain, but it does not claim business semantics or complete intra-day history. If a row is inserted and deleted between two snapshots, this MVP will not observe it. ## Delta Calculation For each table, derive daily deltas by comparing the current snapshot with the previous successful snapshot. Required source-table properties: - A stable primary key or natural unique key. - A deterministic row hash over non-metadata source columns. - A clear schema drift policy. Delta classification: ```text insert: key exists in current snapshot and not previous snapshot delete: key exists in previous snapshot and not current snapshot update: key exists in both snapshots and row_hash changed unchanged: key exists in both snapshots and row_hash did not change ``` Persist the delta table rather than recomputing it every time: ```text nexuscrm_staging.nexuscrm_account_daily_delta ``` The persisted table should contain changed rows only (`insert`, `update`, and `delete`). Count `unchanged` rows in the S3 runtime log for observability, rather than duplicating the whole snapshot into the delta asset. Minimum delta columns: ```text _snapshot_date _snapshot_run_id _change_type -- insert | update | delete _primary_key_hash _row_hash_before _row_hash_after _changed_at -- snapshot completion or load time ``` For deletes, source columns may come from the previous snapshot so consumers can identify what disappeared. The delta table is a staging asset. It is source-aligned, not a curated business event stream. Consumers and agents must read its contract caveats before using it for operational or reporting decisions. ## Current-State Table Build a current-state staging table from the latest successful snapshot: ```text nexuscrm_staging.nexuscrm_account_current ``` For the MVP, the current table can be: - Rebuilt from the latest snapshot after each successful load. - Updated by applying the persisted daily delta. Rebuild is simpler and likely acceptable at 30 GB. Delta apply becomes useful when tables grow or when downstream consumers need explicit change records. The current-state table is a staging asset. It should not be presented as a curated or service layer product until it has business semantics, stronger tests, and consumer-facing compatibility promises. ## Orchestration Shape The first scheduler should be boring and observable: 1. Create a TiDB Cloud export task for the selected table set. 2. Poll export status until success, failure, or timeout. 3. Write a manifest for the export run. 4. Load Parquet files from the run prefix into Cloud Lake raw snapshot tables. 5. Compare current and previous successful snapshots. 6. Publish daily delta tables. 7. Refresh current-state staging tables. 8. Run row-count, freshness, key-uniqueness, and schema-drift tests. 9. Mark the manifest and data product statuses healthy, degraded, blocked, or unknown. The scheduler owns workflow state. S3 owns immutable snapshot artifacts. Cloud Lake owns queryable raw, delta, and current-state tables. Pipeline observations are S3 JSONL runtime events. ## Contract Shape Authored contracts are YAML in Git and should use the universal authored contract schema with layer-specific validation profiles. For this MVP, contract YAML is generated from `information_schema` for the configured table set and then reviewed in Git. Runtime facts belong in the manifest, S3 runtime logs, or later runtime-served contract views; they should not be hand-authored as source-of-truth YAML. Minimum contract set: - Raw snapshot contract for the selected source table set. - Staging state contract for each daily delta or current-state table that is exposed to consumers, agents, or downstream workflows. If a staging table is purely internal to the MVP run, it can remain an implementation asset. Once it is exposed as a data product asset, it needs a staging contract. The first raw contract should describe: - Source TiDB Cloud Essential cluster. - Included databases and tables. - Snapshot cadence. - Export format and compression. - S3 prefix convention. - Expected maximum snapshot age. - Retention policy for S3 snapshots and raw Cloud Lake tables. - Primary key expectations per table. - Delta semantics and known blind spots. - Governance classifications and any retention, access, masking, and audit intent. - Agent permission intent using standard RBAC/ABAC-style resolution; no bespoke agent permission workflow is introduced by this MVP. The first staging contract should describe: - Upstream raw snapshot data product. - Delta and current-state assets. - Stable key and row-hash rules. - Freshness expectation. - Blocking and degrading tests. - Intended usage for source-aligned analysis or downstream validation. - Prohibited usage for authoritative curated reporting unless explicitly promoted later. Known blind spots are important contract text: - Intra-period changes are collapsed. - Insert-then-delete between snapshots is invisible. - Multiple updates between snapshots become one `update`. - Delete detection requires full snapshots or a source-side tombstone table. - Schema drift may block promotion until reviewed. ## Minimum Tests And Observability Minimum signals: - Export task status and duration. - Exported file count and total bytes. - Load status per table. - Loaded row count per table and snapshot. - Previous-versus-current row count delta. - Insert, update, delete counts per table. - Duplicate primary key count per snapshot. - Current snapshot freshness. - Failed or missing table exports. - Schema differences from previous snapshot. Minimum tests: - Blocking: requested table exported, requested table loaded, required snapshot metadata present, primary key uniqueness for diff-enabled tables, and current-state row count matches the latest raw snapshot for the first source table. - Degrading: row-count delta outside expected range, snapshot age approaching freshness limit, and schema difference that does not yet break loading. - Informational: bytes exported, files exported, export duration, load duration, insert/update/delete counts, and unchanged row count. These signals should feed the runtime-served contract view eventually. For the MVP, a manifest file plus S3 JSONL runtime events is enough. The important shape is that the run can explain why each data product is healthy, degraded, blocked, or unknown without creating pipeline-scoped warehouse namespaces. ## Acceptance Criteria - A scheduled job exports the selected TiDB Cloud Essential tables to S3 in Parquet. - Export artifacts are written to immutable run-scoped prefixes. - TiDB Cloud Lake loads the latest snapshot into raw snapshot tables. - A daily delta table classifies inserts, updates, and deletes for at least one source table. - A current-state staging table matches the latest source snapshot for that source table. - Freshness, row-count, key-uniqueness, and schema-drift test results are visible. - The raw snapshot data product has an authored contract in Git. - Any exposed staging state data product has an authored contract in Git. - Manifest and S3 runtime logs contain the minimum runtime facts needed to later project a runtime-served contract view. ## Non-Goals - Near-real-time replication. - Intra-day event history. - Exactly-once streaming semantics. - Automatic resolution of schema drift. - General CDC apply for every table shape. - Kafka, MSK, MSK Connect, or `bend-ingest-kafka` infrastructure. - Final scheduler or orchestration vendor selection. - Runtime-served contract API implementation. - Bespoke agent permission model or detailed approval workflow. - Curated or service layer business semantics. ## Open Questions - Does TiDB Cloud export support branch-specific snapshots, or does the export endpoint always snapshot the parent cluster? - Which CRM business tables should be exposed first as staging products? - Which `_System_*` and credential-bearing tables should be excluded from export versus exported into a separate restricted raw product? - Should `leadhistory` remain full-snapshot-only permanently, since it has no `SystemModstamp` or `LastModifiedDate`? - Which staging products need domain owners beyond the initial data-platform owner? - Which table-specific row-count or delta thresholds should mark the product degraded versus blocked? ## References - TiDB Cloud Starter and Essential export: - TiDB Cloud CLI export create: - TiDB Cloud Terraform export resource: - Databend `COPY INTO`: - NexusCRM source inventory: <../../../docs/design/nexuscrm-source-inventory.md> - Config-driven clean structure principle: <../../../docs/principles/config-driven-clean-structure.md>