Loading and change data capture

Required

• Carry deletes into the warehouse

  DEN-9

  Copy deletes from every source into the warehouse, along with inserts and updates. Within 24 hours of a delete in the source, the row and every row built from it are gone.

• Cap and watch replication slots

  DEN-11

  Cap how much log each replication slot may hold, and alert when a slot falls behind. Drop a slot within a day of retiring the consumer that used it.

Recommended

• Make every load safe to rerun

  DEN-7

  Write each load so that running it twice for the same window gives the same rows. Merge on the source key or replace whole partitions, never append without a key.

• Load only what changed since the last run

  DEN-8

  Load only rows changed since the last successful run, by a change column or a log position. Save that position after the load commits, and re-read an overlap of at least 15 minutes.

• Read changes from the database log

  DEN-10

  For tables that change often or must reach the warehouse within 15 minutes, read changes from the database's write-ahead log or binlog. Do not poll those tables with queries.

• Treat each source schema as a contract

  DEN-12

  Write down the columns and types the pipeline reads from each source. Stop the load and alert the owner when a source drops, renames or retypes one of them.

• Run pipeline steps by their dependencies

  DEN-13

  Start each step when the steps it reads from have finished, not at a fixed clock time. Retry a failed step at most 3 times, then stop the steps after it and alert.