Dr. Martin Loetzsch - ETL Patterns with Postgres

…eds to be exported. The right window depends on the source system. You will also need additional reconciliation jobs that ensures the integrity of historical data over time. For example, if you have a two hour window for one hour of data, two years down the lane, the source system may have a minor disruption and you lose data without noticing it. link to this video…

…ashboards. The application database serves hundreds of thousands of people every day. It's OK if it gets stale data in the DW, which is the worst we can get there should a sync fail, for example if a column is dropped. If a column is added that's also no problem, we'll just get it the next time we sync. We've setup the DW following this pattern: [video Basically data is read in through fdw to a schema suffixed `_next`. Once all tables we want are synced and pass our consistency checks we do a name swap and bring the new data into view of the analysts. > How many migrations are you doing in a day versus how often are you deep cloning this table? If having to deep clone a lot, and the tab…

>first i would version those schemas and fdw in dw-db. ie app\_v\_1\_0 has select a,b,c,d from fdw\_stuff... in fdw table x.. We're following the \`\_next\` pattern for the ETL into the DW as described here: link to this video I think it works well. > if you transformation in fdw , i think it is pushed to source db, so maybe not complex transformations there. Absolutely! Only simple selects are being done, no joins or other transforms. > Nothing prevent you to write procedure to sync max 10k or 100k on one select, there is example already here. have you considered logical replication and using that as fdw ? Thanks! Yes we did, I explained more about why we didn't choose it in my answer to Crack…