Het eerste contractenmodel dat ik schreef voor een BI-tool van een groot bedrijf had zo'n tachtig regels SQL en leek me prima. Eén contract, een begindatum, een einddatum, wat voorwaarden eraan. Het las schoon. Een jaar later had ik datzelfde model meer dan tien keer herschreven, inclusief één keer waarbij ik de naam veranderde omdat de naam loog.
Het was geen slechte planning. Het was dat je het domein pas ziet als de echte data binnenkomt.
Waarom het eerste model altijd juist lijkt
Omdat je het schrijft tegen het domein dat ze je verteld hebben, niet tegen het domein dat bestaat. Ze zeggen "een contract heeft een einddatum". Je modelleert dat. Het werkt met de twintig rijen waar je naar kijkt.
De eerste waarschuwing kwam met de naam. Het model heette kpi_02_20_revenue_contracts en ik hernoemde het naar kpi_02_20_rent_contracts. Het was geen revenue, het was rent: huurcontracten waar het bedrijf betaalt, niet waar het int. Omgekeerd teken. De naam kwam uit het ticket, niet uit de tabel. Een maand werken met een model dat het tegenovergestelde heette van wat het bevatte.
Verkeerd gekozen namen zijn geen cosmetica. Iemand leest ze en neemt iets aan.
Wat pas verschijnt als de echte data binnenkomt
De einddatum was de put. De tabel had twee kolommen: DT_FIRST_CONTRACT_END en DT_CONTRACT_END_NEXT. De eerste versie van de samengevoegde datum was dit:
case
when c.DT_FIRST_CONTRACT_END is null then c.DT_CONTRACT_END_NEXT
when c.DT_CONTRACT_END_NEXT is null then c.DT_FIRST_CONTRACT_END
when c.DT_FIRST_CONTRACT_END < CURRENT_DATE() then c.DT_CONTRACT_END_NEXT
else c.DT_FIRST_CONTRACT_END
end as dt_end_merged
Het lijkt uitputtend. Dat is het niet. Het geval dat beide null zijn ontbreekt, en het geval dat de eerste in het verleden ligt en de tweede niet bestaat ontbreekt. In de data betekenden beide gevallen hetzelfde: contract voor onbepaalde tijd. De case stuurde ze naar een toevallige null die daarna werd geteld als "verlopen". De definitieve versie voegt die twee takken expliciet toe plus een when dt_end_merged is null then True in de vlag is_unlimited, want null is daar informatie, geen afwezigheid van informatie.
En diezelfde vlag sleepte al een ander juweeltje uit de echte wereld mee:
when year_end_merged in (8909, 8899, 9999) then True
Drie verschillende sentineljaren om "geen einde" te zeggen. Geen enkele gedocumenteerd. Alle drie in productie.
De drie keren dat we een beslissing terugdraaiden
De meest illustratieve is het jaar 99. In de bron-Excels stonden einddatums waarvan het jaar als 99 binnenkwam, en iemand in het team zei dat dat "onbepaald" betekende. Ik voegde de afhandeling toe:
case
when {{ normalize_date('ew.WARUNEK_WA_NY_DO') }} like '%99%' then '2099-12-31'::date
else {{ normalize_date('ew.WARUNEK_WA_NY_DO') }}::date
end
En het bijbehorende filter, een or ... like '%99%' zodat die rijen niet door het datumvenster zouden vallen. Ik heb het drie dagen later weer weggehaald. De like '%99%' matcht 1999, matcht 2099 en matcht elke datum met een 99 in de dag of de maand. Ik stopte contracten die twintig jaar geleden waren afgesloten in de berekening van actieve revenue. De bedrijfsregel was misschien waar; de implementatie met een like over een datumstring was dat niet.
Twee maanden later kwam hetzelfde probleem via een andere deur terug, in het model voor heronderhandelingen: jaartallen van twee cijfers. Deze keer deed ik het op het al geëxtraheerde jaar, niet op de tekst:
case
when year(normalized_next_possible_termination_old_contract) < 100
then year(...) + 2000
else year(...)
end
Ook dat overleefde niet intact: twee weken later stond het uitgecommentarieerd in het bestand met een -- ! ervoor. Derde ronde over dezelfde kolom.
De andere grote rollback was de inner join tussen contracten en voorwaarden. Een contract zonder voorwaarde verscheen niet. Dat veranderen naar een left join veranderde de cijfers op het dashboard, en dat is precies het gesprek dat je moet voeren met degene die de KPI vraagt: telt een contract zonder voorwaarde mee of niet. De eerste keer besliste ik het alleen, met een inner, zonder door te hebben dat ik het aan het beslissen was.
Hoe ik weet wanneer een model stabiel is
Aan het soort commit. Het eerste jaar waren de commits structureel: joins die veranderen, filters die erin en eruit gaan, kolommen die worden hernoemd. Aan het eind van het project waren de commits van een ander soort: een vermenigvuldiging met 100 weghalen, twee case-blokken samenvoegen, de kolomdocumentatie schrijven. Dat is een model dat niet meer onder je voeten beweegt, alleen nog aan de bovenkant.
Het andere signaal is dat de documentatie-.yml van vier vage bullets naar het opsommen van de exacte selectiecriteria gaat: entiteiten, contracttypen, valuta, wat er wordt uitgesloten en waarom. Als je die lijst kunt schrijven zonder naar de SQL te kijken, bestaat het model echt. Daarvoor heb je een query.
Wat ik vandaag doe vóór de eerste CREATE TABLE
Vroeger schreef ik het model en keek daarna naar de data. Nu is het andersom, en in deze volgorde:
- De nulls tellen van elke kolom die in een voorwaarde terechtkomt. Een kolom met 40% nulls is geen datum, het zijn twee door elkaar gehusselde concepten.
- Zoeken naar sentinelwaarden.
select distinct year(...) order by 1 desc. Als er 9999, 8899 of 2099 uitkomt, zitten daar ongedocumenteerde bedrijfsregels verstopt. - Het filter in proza schrijven vóór in SQL. Als ik niet in één zin kan zeggen welke contracten meedoen, eindigt de
wheremet zes tegenstrijdige voorwaarden en twee uitgecommentarieerde. - Normaliseren op types, nooit op strings. Elke
likeover een datum is een tijdbom.
Een domeinmodel ontwerp je niet: je onderhandelt erover met de data, en de data geeft niet toe. Elke keer dat ik iets terugdraaide, was het omdat ik zelf had beslist wat ik had moeten vragen.