#2688 support orchestrator-controlled backfill Issue created by MeltyBot Migrated from GitLab: https://gitlab.com/meltano/meltano/-/issues/2738 Originally created by @kgpayne on 2021-04-30 120448 --- Problem to solve Meltano should provide tooling for intelligent backfills so that new users switching from other toolchains can easily onboard their data into the Meltano ecosystem. The process of backfilling data is often the most painful part of adopting a new pipeline tool or framework, and backfilling is not handled specifically by many common Taps and Targets. The default setup (adding a Tap, Target and single pipeline) provides almost the worst-case experience for backfilling of medium to large workloads (transferring one record at a time from the beginning of time), and more advanced patterns (such as 'sharding' a pipeline by creating multiple pipelines to transfer subsets of a Taps Streams) require planning and understanding that significantly raise the barrier to entry. Target audience Data Engineers and Analytics Engineers responsible for backfilling existing data from a source to a destination. This primarily applies to databases, but the challenges exist for other Tap types (e.g. Zendesk, where API throttling makes it especially slow to backfill). As we develop towards a DataOps future of disposable environments for staging and testing, the ability to backfill effectively becomes even more important. Further details In an ideal world: • Data would backfill in reverse order, with newest data arriving first (not oldest). • Backfilling would happen at the same time as scheduled incremental runs, ensuring new data arrives even whilst old data is backfilled. • Backfilling would be parallelised for large Streams. This would involve an assessment step to split a specific streams corpus into equal chunks before creating a Meltano job per chunk to replicate data. • Chunks and incremental changes would be combined in a final step performed by Meltano to create a coherent end result. I believe this is an orchestration challenge, rather than an integration challenge, and is therefore primarily a feature for Meltano. However, support for e.g. an

end_date

in addition to the

start_date

commonly supported by Taps would be needed to allow Meltano to coordinate the replication of chunks of time-stamped data. Proposal I propose a new Meltano command -

meltano backfill

. Given the need for up-front assessment, and to offer end-users the option to tweak a backfill operation, it would be advantageous to split the execution of backfills into two subcommands: •

meltano backfill plan ...

•

meltano backfill run --plan ./plan.json

plan.json

will contain all the details needed to run i) the first incremental load (either from an arbitrary start-point, or the oldest log available at source) and ii) to run one-off pipelines for the chunked backfill. What does success look like, and how can we measure that? The key measures of success include i) time to most-recent record available in target and ii) time to oldest record available in target. Regression test • Write adequate test cases and submit test results • Test results should be reviewed by a person from the team Links / references • Related to Resume ELT for migrations from other tools. meltano/meltano