nsivarajan opened a new issue, #65422: URL: https://github.com/apache/doris/issues/65422
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description Modern analytical workloads require querying data **as it existed at a specific point in time** — for audit trails, data debugging, incremental pipeline recovery, and regulatory compliance. Apache Doris cloud/decoupled mode currently has no mechanism to read historical versions of a table. Once data is compacted or overwritten, earlier states are unrecoverable from SQL. **Requested syntax:** ```sql SELECT * FROM orders FOR TIME AS OF '2024-01-15 10:00:00'; CREATE TABLE snapshot AS SELECT * FROM orders FOR TIME AS OF '2024-01-15 10:00:00'; ``` Supported on all DML table models: Duplicate Key, Unique Key (MoW), and Aggregate Key. **Solution** Key design points Per-table opt-in — zero overhead for non-TT tables: ```sql CREATE TABLE orders (...) PROPERTIES ( "enable_time_travel" = "true", "time_travel_retention_days" = "7" ); ``` **Two-level FDB index (cloud mode):** - Level 1: sparse version index — one FDB key per commit per partition (T → V mapping) - Level 2: compaction checkpoint — pre-compaction rowset manifests written before each cumulative compaction, so historical reads survive compaction One batch RPC resolves all partitions and fetches all manifests in a single FDB transaction — consistent snapshot across all partitions, one network round trip. Post-compaction correctness — the critical test: a query at timestamp T returns identical results before and after compaction of the rowsets that covered T. **Scope** - Cloud/decoupled mode only (this PR) - Coupled/local storage mode: same architecture, separate follow-up (~40% effort) - SHOW TIME TRAVEL ON <table>: listed as future work **Test coverage** - Unit tests: Nereids optimizer timestamp propagation, property parsing - Regression tests: DUP table, MoW table, post-compaction correctness **PR** Single PR with 4 logical commits for reviewability — metadata layer, meta service,recycler, FE+BE query path. ### Use case Apache Doris cloud/decoupled mode has no built-in mechanism to query a table as it existed at a specific point in the past. Once data is modified, compacted, or overwritten, earlier states are unrecoverable via SQL. This blocks several real-world use cases that rely on historical data access: **Audit and compliance** — regulations (GDPR, SOX, HIPAA) require proving what data looked like at a specific date. Today this requires maintaining separate snapshot tables manually, duplicating storage and ETL complexity. **Data debugging** — when a pipeline produces unexpected results at 2 AM, engineers need to inspect what the source table contained at that exact moment. Without time travel, they must restore from backup or replay the entire pipeline from scratch. **Incremental pipeline recovery** — streaming ingestion pipelines that fail mid-batch need to re-read source data from the point of failure. Without historical access, the only option is full re-scan. **Accidental data correction** — `DELETE FROM orders WHERE id IN (...)` with a wrong predicate has no undo. Users must restore from external backup, which is slow and loses data written after the backup. ### What other systems provide Snowflake, Databricks Delta Lake, Apache Iceberg, and Databend all support time travel syntax: ```sql -- Snowflake / Delta Lake SELECT * FROM orders AT (TIMESTAMP => '2024-01-15 10:00:00'::TIMESTAMP); -- Iceberg SELECT * FROM orders FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-15 10:00:00'; -- Databend SELECT * FROM orders AT (SNAPSHOT => '<snapshot_id>'); Apache Doris has no equivalent. Users of cloud/decoupled Doris who migrate from any of the above systems lose this capability. ### Related issues [TimeTravel for Cloud:Decoupled Mode.pdf](https://github.com/user-attachments/files/29846403/TimeTravel.for.Cloud.Decoupled.Mode.pdf) ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
