I tried migrating data from TiDB Cloud to TiDB Cloud Lake

Hello, I'm sora from the Game Solutions Division.
This time, I'll write about my experience moving data from a TiDB Cloud cluster to TiDB Cloud Lake.

Inserting Dummy Data into TiDB Cloud

I created one TiDB Cloud Starter cluster and placed two tables, appdb.orders and appdb.app_logs, in the same database.
I'll move only the 500,000 rows of app_logs to Lake, while keeping the 50,000 rows of orders in TiDB.

The definition of app_logs is as follows.

CREATE TABLE appdb.app_logs (
    log_id     BIGINT PRIMARY KEY,
    level      VARCHAR(16) NOT NULL,
    service    VARCHAR(64) NOT NULL,
    message    TEXT,
    attributes JSON,
    logged_at  DATETIME NOT NULL,
    KEY idx_logged_at (logged_at)
);

Data was inserted using LOAD DATA LOCAL INFILE.
The 500,000 rows of app_logs took about 54 seconds, and after insertion the Row-based Storage for the entire cluster including orders was 199.24 MiB.

Exporting from TiDB Cloud to S3

Ingestion into TiDB Cloud Lake goes through S3.
The flow is: output files from TiDB Cloud to S3, then have the Lake side read them.

I used Dumpling for the export.
The TiDB Cloud console also has an export feature, but files exported from there could not be ingested.
I'll write about that in the latter half.

The usage of Dumpling itself is introduced in this article.
https://dev.classmethod.jp/articles/export-tidb-data-with-dumpling/

The command I executed is as follows.

tiup dumpling \
  -h gateway01.ap-northeast-1.prod.aws.tidbcloud.com -P 4000 \
  -u '<prefix>.root' -p '<password>' \
  --ca /etc/ssl/cert.pem \
  --filter 'appdb.app_logs' \
  --filetype csv \
  --escape-backslash=false \
  --csv-output-dialect snowflake \
  --csv-line-terminator $'\n' \
  -c no-compression \
  -o 's3://<bucket-name>/tidb-cloud-lake/dumpling/' \
  --s3.region ap-northeast-1

Since --filter can narrow down the tables, only app_logs is output and orders is not.
Since you can write the S3 URI directly in -o, there was no need to download it locally either.

The following four flags at the end are conditions for ingestion into Lake.

I also added --csv-output-dialect snowflake, but this wasn't about quoting.
According to the official documentation, it's an option that converts binary types to hexadecimal notation (removing the 0x prefix).

https://docs.pingcap.com/tidb/stable/dumpling-overview/

Since there are no binary columns in this table, the only flag that had a practical effect was --escape-backslash=false.

The dump of 500,000 rows and 140.1MB took 1 minute 57 seconds and produced 4 files.

$ aws s3 ls s3://<bucket-name>/tidb-cloud-lake/dumpling/ --recursive --human-readable
  133 Bytes tidb-cloud-lake/dumpling/appdb-schema-create.sql
  442 Bytes tidb-cloud-lake/dumpling/appdb.app_logs-schema.sql
  133.6 MiB tidb-cloud-lake/dumpling/appdb.app_logs.000000000.csv
  146 Bytes tidb-cloud-lake/dumpling/metadata

In addition to the data itself, a CREATE DATABASE statement and a CREATE TABLE statement are also output.
These will later be used for automatic table creation on the Lake side.

The contents of the CSV turned out like this.

"log_id","level","service","message","attributes","logged_at"
1,"DEBUG","search-api","span exported (req=204b5f50)","{""az"": ""ap-northeast-1a"", ""customer_id"": 3890, ""duration_ms"": 46.5, ""http"": {""method"": ""GET"", ""path"": ""/v1/inventory"", ""status"": 200}, ""trace_id"": ""11398b037a11a01e""}","2026-07-25 10:34:50"

With --escape-backslash=false, " becomes a doubled "".
I couldn't find explicit documentation for this behavior in the official docs, but I was able to confirm it empirically.

By the way, when running Dumpling against TiDB Cloud Starter, you'll get 3 permission errors for information_schema.cluster_info, mysql.tidb, and information_schema.placement_policies.
This is because Starter restricts access to system tables, and all three are just warnings — the dump itself succeeds.

https://docs.pingcap.com/tidbcloud/limited-sql-features/

Registering a Data Source in TiDB Cloud Lake

From here, the work is done in the TiDB Cloud Lake console.
There's a TiDB option in the Service field under Data > Data Sources > Create, so select that.

When you open it, there are no input fields for hostname, username, or password.
What was listed instead was S3 bucket and Role ARN.

Below Storage Provider, there's this line:

Where TiCDC / Dumpling stages the data that Databend loads.

In other words, the TiDB data source is not for connecting to a TiDB cluster — it points to the S3 location where TiCDC or Dumpling has placed files.
Lake does not look at TiDB.
It looks at the S3 where the files output by TiDB are placed.

The default for Authentication Method is Role ARN, and I was able to connect using an IAM role without using access keys.

Note that there is no TiDB page under Data Source Types in the official documentation.

https://docs.pingcap.com/tidbcloudlake/data-sources/

Only 6 types are listed: Amazon S3, Amazon SQS (S3), MySQL, PostgreSQL, FeiShuBot, and Kafka — TiDB is an option that only exists in the console.

Ingesting with an Integration Task in TiDB Cloud Lake

Create a task in Data > Data Integrations in the TiDB Cloud Lake console.

If you write only appdb.app_logs in Table Rules, only that table will be targeted.

Sync Mode had 3 options.

The options are Snapshot / CDC / Snapshot + CDC, where Snapshot receives Dumpling's output and CDC receives TiCDC's change logs.
I'll proceed with Snapshot mode this time.

Pressing Preview Matched Tables lets you check the match results before creating.

Only appdb.app_logs is matched, and orders is not included.
Since this button actually scans S3 to look for -schema.sql files, you can check whether the prefix and output format are correct without creating a task or starting a Warehouse.

In the details screen's Sync Configuration, there were 3 items that weren't in the creation form.

The three items are Allow Delete: Yes / Poll Interval: 10 s / Merge Interval: 3 s, and by default it checks S3 every 10 seconds.

Simply creating it won't make it run, so press Start.

Rows Synced became 500,000.
Since Auto Create Table defaults to Yes, the table was also automatically created on the Lake side.

500,000 rows at 18.5MB, with Engine being FUSE.
The ingestion of 133.61MB finished in about 4 seconds (115,727 rows/s).

Verifying the Ingested Data in TiDB Cloud Lake

I ran verification queries in TiDB Cloud Lake's Worksheet and compared them against expected values I had kept on the TiDB Cloud side.

SELECT COUNT(*) AS cnt, SUM(log_id) AS sum_id FROM appdb.app_logs;

COUNT(*) was 500,000 and SUM(log_id) was 125,000,250,000, both matching the TiDB Cloud side.
The breakdown by level and the aggregation by HTTP status also showed the same values.
Since the checksums match, the data was ingested with no missing or duplicate rows.

json becomes VARIANT

This is the table definition created on the TiDB Cloud Lake side.

CREATE TABLE app_logs (
  log_id     BIGINT   NULL,
  level      VARCHAR  NOT NULL,
  service    VARCHAR  NOT NULL,
  message    VARCHAR  NULL,
  attributes VARIANT  NOT NULL,
  logged_at  TIMESTAMP NOT NULL
) ENGINE=FUSE

Since json became VARIANT, you can directly traverse nested JSON.

SELECT attributes['http']['status'] AS status, COUNT(*) AS cnt
FROM appdb.app_logs GROUP BY status ORDER BY status;

200 had 356,519, 429 had 71,939, and 500 had 71,542 — these also matched the TiDB Cloud side.

However, the syntax changes.
While the TiDB Cloud side uses attributes->>'$.http.status', the Lake side uses attributes['http']['status'].
Both produce the same results, but SQL that touches JSON columns cannot be migrated as-is.

The disappearance of the primary key and secondary index is reasonable for an analytical store, but log_id was NOT NULL yet became NULL, and attributes was the opposite — it was nullable but became NOT NULL.
It seems the system is inferring from actual data rather than directly reflecting the DDL from -schema.sql, but I wasn't able to confirm that.

Storage was reduced to about 1/11

The 199.24 MiB on the TiDB Cloud side is the value for the entire cluster including orders, but most of it belongs to app_logs.
Since neither the primary key nor secondary indexes are carried over to the Lake side, that likely accounts for a large portion of the difference.

Trial and Error: Could Not Ingest Using TiDB Cloud's Export Feature

From here I'll step off the main track, but this is a record of what happened before I pulled out Dumpling.
TiDB Cloud's console also has an export feature, and initially I was trying to ingest files exported from there.

The interface is easier to use.
You can check tables individually from the Exported Data tree, so you can select only app_logs and deselect orders.
Authentication also uses Role ARN, and you can create the role from a CloudFormation link.

The format options are SQL / CSV / Parquet, and compression can be chosen from Zstd / Gzip / Snappy / None.
I tried Parquet and CSV both with and without compression, but none of them could be ingested.

Parquet Was Not Ingested

First I exported as Parquet and ran the task.

Status was Success and Last Message was empty.
However, Rows Synced was 0, Data Synced was 0 B, and Chunks was 0/0.
Even though a 26.4 MB Parquet file was placed in S3, not a single row was ingested.

It was treated as a success with the result "0 target files found," not as an error.

The important takeaway here is that whether data was actually ingested cannot be determined by Status alone — you need to check Rows Synced.
If you run it on a schedule and only monitor for Success, you might not notice that nothing was actually ingested.

For troubleshooting I checked Data > Databases on the TiDB Cloud Lake side, and found that only the table had been created.

Rows was 0 and Bytes was 0B.
The -schema.sql was readable, but only the data files were excluded from the target.

The result was the same even without compression.
It doesn't seem that the .zst in the filename was the cause.
Since chunks were only recognized with CSV, it appears that Snapshot tasks don't target Parquet files.
Indeed, there is no field for selecting file format in the creation form — only CSV-specific settings are listed, such as CSV Separator / Skip Header Rows / Export Escaped Backslashes.

Note that in the same Lake, Amazon S3 Integration Tasks do support CSV / Parquet / NDJSON.
https://docs.pingcap.com/tidbcloudlake/integrate-with-amazon-s3/

Only CSV worked with TiDB tasks.

Compression Also Compresses Schema Files with .zst

I re-exported as CSV.
When I left Compression as Zstd, it was rejected at the Preview Matched Tables stage this time.

These rules matched no table under the S3 prefix.
No database was found under the prefix at all. Check the S3 prefix.

Looking at the files in S3 makes the reason clear.

appdb-schema-create.sql.zst              131 B
appdb.app_logs-schema.sql.zst            303 B
appdb.app_logs.0000000010000.csv.zst    22.5 MB

Even the schema SQL files are compressed with .zst.

With Parquet, compression was an internal codec, so the .sql files remained uncompressed.
With CSV, compression becomes an outer wrapper, which also catches the .sql files.
Since the database definition itself cannot be found, you get a message saying "no DB found."

Uncompressed CSV Has Mismatched Escaping and Line Endings

Re-exporting with Compression: None got past Preview, and after running it the result was as follows.

Chunks changed from 0/0 to 0/1.
It recognized 1 chunk, tried to read it, and failed.

Last Message reads 1 table(s) failed during full sync: ap... and is cut off, so the full text cannot be read.
This is where TiDB Cloud Lake's Monitoring > SQL History proved useful.

BadBytes. Code: 1046, Text = Number of columns in file (12) does not match that of the corresponding table (6)
at file 'tidb-cloud-lake/appdb.app_logs.0000000010000.csv', line 1.

Integration Task failures are truncated in Run History, but since the actual work runs as SQL on the Warehouse, everything is preserved in SQL History.

Now, app_logs has 6 columns.
The file is seen as having 12 columns, which is a difference of 6.

Let's look at the actual CSV.

1,"DEBUG","search-api","span exported (req=204b5f50)","{\"az\": \"ap-northeast-1a\", \"customer_id\": 3890, \"duration_ms\": 46.5, \"http\": {\"method\": \"GET\", \"path\": \"/v1/inventory\", \"status\": 200}, \"trace_id\": \"11398b037a11a01e\"}","2026-07-25 10:34:50"

The double quotes in attributes are escaped with backslashes.
This is exactly Dumpling's default --escape-backslash=true (MySQL dialect).

And the difference of 6 matches the number of commas inside the JSON in attributes.
Because the \" escaping is not interpreted, the value doesn't close as a single field, and the commas inside the JSON are treated as column delimiters.

The SQL being issued by the task was also visible in SQL History.

COPY INTO `appdb`.`app_logs` (`log_id`, `level`, `service`, `message`, `attributes`, `logged_at`)
FROM 's3://<bucket-name>/tidb-cloud-lake/appdb.app_logs.0000000010000.csv'
CONNECTION=(external_id='la***xl', role_arn='ar***le')
FILE_FORMAT=(field_delimiter=',', null_display='\\N', record_delimiter='\n', skip_header=1, type=CSV)
PURGE=false FORCE=false DISABLE_VARIANT_CHECK=false ON_ERROR=abort RETURN_FAILED_ONLY=false

Since FILE_FORMAT has neither escape nor quote, it's read using Databend's defaults (RFC 4180's "").
Furthermore, record_delimiter='\n' is specified, but the export outputs with Dumpling's default \r\n.
There were two mismatches: the escape method and the line ending.

There Are No Options in the Console to Re-export Correctly

I thought setting the task's Export Escaped Backslashes to Yes would fix the reading, but a red warning appeared.

Re-export with --escape-backslash=false --csv-output-dialect=snowflake, then select No.
Existing export settings must not be changed without re-exporting.

This is not a setting that says "I can also read escaped files" — it's an instruction saying "that file cannot be read, so re-export it and then select No."
Moreover, setting it to Yes disables the Update button, so it cannot be saved in the first place.

Then the question is whether you can just re-export — but TiDB Cloud's export feature does not have those options.
The only things you can change in the console's Edit CSV Configuration are Separator, Delimiter, Null value, and Skip header — you cannot touch the escape method or line endings.

The CLI was the same.

https://docs.pingcap.com/tidbcloud/ticloud-serverless-export-create/

The only specifiable options are --csv.delimiter, --csv.separator, --csv.null-value, --csv.skip-header, and --parquet.compression — there is no --escape-backslash or --csv-output-dialect.

This is where the reasons for adding --escape-backslash=false and --csv-line-terminator $'\n' to Dumpling in the first half come together.
To re-export as instructed on the screen, the only option was to run Dumpling manually.

Incidentally, comparing the -schema.sql files from TiDB Cloud's export and from self-run Dumpling with diff shows no differences — they match byte for byte.
The export feature is also backed by Dumpling under the hood; the only difference is the flags.

Converting the Files Works

If you want to avoid running Dumpling yourself, you can also fix the exported CSV.
There are only 2 things to fix.

$ LC_ALL=C sed 's/\\"/""/g' original.csv | tr -d '\r' > converted.csv

It simply replaces \" with "" and \r\n with \n.
The conversion of 134MB took 4.9 seconds.

Uploading this back to S3 and running the same task worked.

Since the previous run had Rows Synced 0, success vs. failure came down to nothing but the file contents, with no changes to the task configuration whatsoever.

However, for data that contains backslashes in the values themselves, this simple substitution will break things.
I verified in advance using grep that the data contained neither backslashes nor NULL representations before doing this.

Closing Thoughts

This time, I moved data from a TiDB Cloud cluster to TiDB Cloud Lake via S3 and verified that the ingested data matched all the way through.
The TiDB data source is built with the assumption that you run Dumpling yourself, and to be honest, the TiDB Cloud console's export feature alone is not sufficient to complete the process.
I hope this article is helpful to someone.