Hoe houd je data tussen twee systemen gelijk?
Kort antwoord
Er zijn vier manieren: alles opnieuw laden, alleen ophalen wat sinds de vorige keer is gewijzigd (incrementeel), het logboek van de database meelezen (change data capture) of de bron een seintje laten sturen (webhooks). Geen van de vier is in zijn eentje waterdicht. In de praktijk combineer je een snelle methode met een periodieke volledige controle.
Elke koppeling en elk rapportageproject begint met dezelfde stap: data uit het ene systeem naar het andere krijgen. In de eerste week is dat eenvoudig. Je haalt alles op, zet het neer en het klopt. Het wordt pas interessant op dag twee, als er in de bron iets is veranderd. Een factuur is betaald, een klant is verhuisd, een order is geannuleerd. Hoe weet het andere systeem dat?
In het artikel over datawarehouses voor het MKB beschreef ik waar de data terechtkomt en hoe je die daarna bewerkt. Dit artikel gaat over de stap daarvoor: het ophalen zelf. Die keuze bepaalt hoe actueel je cijfers zijn, hoe zwaar je de bron belast en, belangrijker dan de meeste mensen denken, welke wijzigingen je ongemerkt mist.
Vier manieren om wijzigingen over te halen
1. Alles opnieuw laden
De eenvoudigste methode: je haalt bij elke ronde de volledige dataset op en vervangt wat je had. Er is geen boekhouding nodig over wat al binnen is, en je krijgt verwijderingen er gratis bij. Wat niet meer in de bron staat, staat na de verversing ook niet meer bij jou.
Het nadeel is het gewicht. Een tabel met een paar duizend klanten haal je zonder moeite elke nacht opnieuw op. Bij een paar miljoen orderregels duurt het uren, en bij een cloudpakket loop je tegen de limieten van de API aan lang voordat je klaar bent. Volledig laden is daarom de juiste keuze voor kleine, trage tabellen (productgroepen, medewerkers, grootboekrekeningen) en de verkeerde voor alles wat groot is of snel verandert.
2. Incrementeel laden
Bij incrementeel laden onthoud je tot waar je was en vraag je alleen wat daarna is veranderd. Meestal gaat dat op basis van een wijzigingsdatum: geef mij alle facturen die zijn aangepast sinds gisteren 02:00 uur. Dat is licht voor de bron, werkt via vrijwel elke API en is daarmee het werkpaard van bijna elke koppeling.
Sommige systemen bieden een nettere variant met versienummers. Moneybird heeft bijvoorbeeld per soort gegeven een synchronisatie-endpoint dat van alle facturen alleen het id en het versienummer teruggeeft. Je vergelijkt die lijst met wat je zelf hebt en haalt daarna alleen de records op waarvan de versie afwijkt, volgens de documentatie met maximaal honderd tegelijk. Dat heeft een prettige bijwerking: een id dat in de lijst ontbreekt, is verwijderd.
3. Change data capture
Elke serieuze database houdt een logboek bij van alle wijzigingen, in de eerste plaats om na een storing te kunnen herstellen en om kopieën bij te houden. Change data capture, kortweg CDC, gebruikt dat logboek als bron. In plaats van de tabellen te bevragen, lees je mee met wat de database toch al opschrijft.
PostgreSQL biedt dit als logische replicatie: wijzigingen worden per rij doorgegeven op basis van de primaire sleutel. Bij SQL Server heet de functie letterlijk change data capture en is het transactielogboek de bron. Een veelgebruikte open source-tool hiervoor is Debezium, dat wijzigingen uit verschillende databases opvangt en doorgeeft aan andere toepassingen.
Het voordeel is volledigheid. Je krijgt elke toevoeging, wijziging en verwijdering, in de juiste volgorde, met weinig vertraging en zonder zware query op de tabellen. Het nadeel is dat je toegang tot de database zelf nodig hebt. Bij een eigen applicatie kan dat. Bij een boekhoudpakket of CRM in de cloud niet.
4. Webhooks
Bij een webhook draai je de richting om. Jij vraagt niets; de bron stuurt een bericht zodra er iets gebeurt. Dat is snel en zuinig, want er gaat alleen verkeer over de lijn als er iets te melden is. Betaalproviders, webshops en steeds meer boekhoudpakketten bieden ze aan.
Maar een webhook is een signaal en geen waarheid. Stripe is daar in zijn eigen documentatie eerlijk over: de volgorde van berichten is niet gegarandeerd en hetzelfde bericht kan meer dan eens aankomen. En was jouw kant net onbereikbaar tijdens een update, dan hangt het van de afzender af of en hoe lang die het opnieuw probeert.
| Kenmerk | Alles opnieuw | Incrementeel | Change data capture | Webhooks |
|---|---|---|---|---|
| Actualiteit | Per ronde, meestal per nacht | Per ronde, minuten tot uren | Seconden | Seconden |
| Belasting van de bron | Hoog | Laag | Laag | Zeer laag |
| Ziet verwijderingen | Ja | Nee, tenzij de bron ze bijhoudt | Ja | Alleen als de bron ze meldt |
| Werkt via een API | Ja | Ja | Nee, vraagt databasetoegang | Ja, als de bron ze aanbiedt |
| Grootste risico | Duurt te lang bij groei | Stil gemiste wijzigingen | Beheer van het logboek | Gemiste of dubbele berichten |
Wat incrementeel laden ongemerkt mist
Incrementeel laden op wijzigingsdatum lijkt waterdicht en is het niet. Er zijn vier plekken waar het lekt, en geen ervan geeft een foutmelding. De synchronisatie draait gewoon door, alleen klopt het resultaat niet meer.
- Verwijderde records: een record dat niet meer bestaat, heeft ook geen wijzigingsdatum meer. De vraag "wat is er veranderd?" levert het dus nooit op. Bij jou blijft de geannuleerde order voor altijd in de omzet staan.
- Wijzigingen buiten de applicatie om: een wijzigingsdatum wordt bijgewerkt door de applicatie, niet door de database. Een bulkcorrectie rechtstreeks in de database of een import door de leverancier past duizend records aan zonder dat één datum verspringt.
- Het randje van het tijdvenster: een wijziging die om 01:59:59 is gestart maar pas om 02:00:03 is vastgelegd, kan nét buiten je ronde van 02:00 uur vallen en bij de volgende ronde al te oud zijn. Haal daarom altijd een stuk overlap op, bijvoorbeeld het laatste kwartier of uur opnieuw.
- Verschuivende pagina's: haal je resultaten per pagina op terwijl er tussendoor records bijkomen, dan schuift de lijst op en sla je een record over of krijg je er een dubbel.
Overlap betekent dat je records dubbel binnenkrijgt. Dat is geen probleem zolang het verwerken ervan geen kwaad kan. In PostgreSQL doe je dat met een INSERT … ON CONFLICT: bestaat het record al, dan wordt het bijgewerkt in plaats van nog een keer toegevoegd. Hetzelfde principe geldt voor webhooks die dubbel aankomen. Meer daarover in idempotency bij API-koppelingen.
Een synchronisatie die stopt met een foutmelding is een goede dag. De dure fout is de synchronisatie die doorloopt en elke dag een paar records mist.
Wanneer is change data capture de moeite waard?
CDC lost de vier lekken hierboven in één keer op. Toch is het zelden de eerste keuze, om twee redenen.
De eerste is bereik. Het werkt alleen op een database waar je zelf bij kunt. De meeste data in een MKB-bedrijf zit in pakketten van anderen, en daar eindigt het verhaal bij de API.
De tweede is beheer. Wie meeleest met het logboek, moet ook blijven lezen. PostgreSQL houdt per lezer bij tot waar die gekomen is, in een zogeheten replication slot. De documentatie waarschuwt dat zo'n slot het opruimen van benodigde gegevens tegenhoudt, ook als er niemand verbonden is. Valt de ontvangende kant een week stil, dan bewaart de database een week aan logboek en loopt de schijf van je productieserver vol. Je haalt dus een nieuw onderdeel in huis dat bewaakt moet worden.
CDC verdient zijn plek in drie situaties:
- Vertraging doet schade: voorraad, beschikbaarheid of fraudedetectie, waar een stand van een uur oud tot verkeerde beslissingen leidt.
- De tabellen zijn te groot om te bevragen: elke ronde een query op tientallen miljoenen rijen belast de applicatie meer dan het meelezen van het logboek.
- Je wilt elke tussenstand: een order die binnen een uur drie keer van status wisselt, zie je bij incrementeel laden alleen in de laatste stand. Het logboek bevat alle drie.
Herken je er geen, dan is incrementeel laden met een goede controle eromheen de verstandiger keuze. Minder elegant, veel minder om te beheren.
De combinatie die in de praktijk werkt
Geen enkele methode dekt alles. Wat wel werkt, is twee methodes die elkaars zwakke plek opvangen.
- Kies per tabel, niet per koppeling: kleine stamgegevens laad je elke nacht volledig. Grote, snel veranderende tabellen laad je incrementeel.
- Gebruik webhooks als seintje: behandel het bericht als "er is iets veranderd aan factuur 1042" en haal daarna de actuele stand op bij de bron. Dan maakt het niet uit of berichten dubbel of door elkaar binnenkomen.
- Zet er een periodieke volledige controle naast: vergelijk elke nacht of elke week de ids, en waar mogelijk de versies, van de bron met die van jou. Wat ontbreekt haal je op, wat bij de bron verdwenen is markeer je als verwijderd.
- Markeer verwijderingen in plaats van ze uit te voeren: zet een datum van verwijdering bij het record. Je rapportages filteren erop, en je kunt achteraf nog zien wat er wanneer verdween.
- Bewaar tot waar je was, per tabel: sla het moment of de versie van de laatste geslaagde ronde op, en werk dat pas bij als de ronde helemaal is verwerkt. Een halve ronde draai je dan gewoon opnieuw.
- Laat zien hoe oud de data is: toon op elk dashboard en in elk scherm wanneer er voor het laatst is gesynchroniseerd, en stuur een melding als dat langer geleden is dan afgesproken. Meer over die bewaking lees je in observability.
Eén ding staat los van de techniek: spreek af welke kant leidend is. Synchroniseren in twee richtingen, waarbij een klant zowel in het CRM als in het boekhoudpakket aangepast mag worden, is een andere orde van moeilijkheid. Dan moet je bij elke botsing bepalen wie wint. Vaak is de beste oplossing dat te voorkomen door per gegeven één systeem aan te wijzen waar het gewijzigd wordt. Hoe je dat aanpakt, staat in het artikel over master data management.
Mijn kijk op realtime
"Het moet realtime" hoor ik in bijna elk eerste gesprek over een koppeling of een dashboard. Als ik doorvraag welke beslissing er anders uitvalt wanneer een cijfer vijf minuten oud is in plaats van vijf seconden, blijft het meestal stil. Een directie die op maandag de omzet van vorige week bekijkt, heeft niets aan seconden. Een magazijnmedewerker die een pakbon print wel.
Ik bouw daarom zelden met de zwaarste methode. Bijna alles wat wij koppelen draait op incrementeel laden met overlap, webhooks waar de bron ze heeft, en een nachtelijke controle die opruimt wat er overdag doorheen is geglipt. Dat is geen spannende architectuur. Het is wel een opzet waarvan ik na twee jaar nog kan uitleggen waarom een getal is wat het is, en die niet omvalt als een leverancier zijn API een middag uit de lucht haalt.
Waar ik wel streng in ben, is de controle. Een koppeling zonder periodieke vergelijking met de bron is een koppeling waarvan je hoopt dat hij klopt. Dat verschil merk je niet in de eerste maand. Je merkt het bij de jaarafsluiting, als het rapport en de boekhouding een paar facturen uit elkaar liggen en niemand weet sinds wanneer.
— Jasper
Zo helpt Coding Agency hierbij
Wij bouwen koppelingen die niet alleen data overzetten, maar ook bijhouden of het nog klopt: incrementeel laden met overlap, verwerking die dubbele berichten verdraagt, een periodieke vergelijking met de bron en een melding als een ronde uitblijft. Of het doel nu een rapportagedatabase is of twee systemen die dezelfde klanten moeten kennen. En waar een eigen database en een echte behoefte aan snelheid samenkomen, zetten we change data capture in.
Liggen jouw rapportage en je bronsysteem af en toe een paar records uit elkaar, of twijfel je welke methode bij jouw situatie past? Neem contact op voor een vrijblijvend gesprek.