I rewrote the same contracts model more than ten times

The first contracts model I wrote for a large company's BI tool was about eighty lines of SQL and I thought it was fine. One contract, a start date, an end date, some conditions hanging off it. It read clean. A year later I had rewritten that same model more than ten times, including once where I changed its name because the name was lying.

It wasn't bad planning. It's that you only see the domain once the real data shows up.

Why the first model always looks correct

Because you write it against the domain you were told about, not the one that exists. They tell you "a contract has an end date". You model it. It works with the twenty rows you're looking at.

The first warning came from the name. The model was called kpi_02_20_revenue_contracts and I renamed it to kpi_02_20_rent_contracts. It wasn't revenue, it was rent: lease contracts where the company pays, not where it collects. Opposite sign. The name came from the ticket, not from the table. A month working with a model named the opposite of what it held.

Bad names aren't cosmetic. Someone reads them and assumes.

What only shows up when the real data does

The end date was the pit. The table had two columns: DT_FIRST_CONTRACT_END and DT_CONTRACT_END_NEXT. The first version of the unified date was this:

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

Looks exhaustive. It isn't. It's missing the case where both are null, and the case where the first is in the past and the second doesn't exist. In the data, both meant the same thing: open-ended contract. The case sent them to an accidental null that was later counted as "expired". The final version adds those two branches explicitly plus a when dt_end_merged is null then True in the is_unlimited flag, because null there is information, not the absence of it.

And that same flag already carried another gem from the real world:

when year_end_merged in (8909, 8899, 9999) then True

Three different sentinel years to say "no end". None documented. All three in production.

The three times we reverted a decision

The most telling one is the year 99. The source Excel files had end dates whose year came through as 99, and someone on the team said it meant "open-ended". I added the handling:

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

And the matching filter, an or ... like '%99%' so those rows wouldn't drop out of the date window. I removed it three days later. The like '%99%' matches 1999, matches 2099, and matches any date with a 99 in the day or the month. I was pulling contracts closed twenty years ago into the active revenue calculation. The business rule may well have been true; a like over a date string was not.

Two months later the same problem came back through another door, in the renegotiations model: two-digit years. This time I did it on the year already extracted, not on the text:

case
  when year(normalized_next_possible_termination_old_contract) < 100
    then year(...) + 2000
  else year(...)
end

That didn't survive intact either: two weeks later it was commented out in the file with a -- ! in front. Third pass over the same column.

The other big rollback was the inner join between contracts and conditions. A contract with no condition didn't show up. Changing it to a left join changed the dashboard numbers, and that is exactly the conversation to have with whoever asks for the KPI: whether a contract without a condition counts or not. The first time I decided it alone, with an inner, without realising I was deciding it.

How I know when a model is stable

By the kind of commit. During the first year the commits were structural: joins changing, filters coming in and out, columns renamed. By the end of the project the commits were a different sort: drop a multiply by 100, merge two case blocks into one, write the column docs. That's a model that no longer moves underneath, only on top.

The other sign is that the documentation .yml goes from four vague bullets to listing the exact selection criteria: entities, contract types, currency, what's excluded and why. When you can write that list without looking at the SQL, the model really exists. Before that, what you have is a query.

What I do today before the first CREATE TABLE

I used to write the model and then look at the data. Now it's the other way round, and in this order:

• Count the nulls in every column that will go into a condition. A column that's 40% null isn't a date, it's two concepts mixed together.
• Look for sentinel values. select distinct year(...) order by 1 desc. If 9999, 8899 or 2099 come out, there are undocumented business rules hiding in there.
• Write the filter in prose before writing it in SQL. If I can't say in one sentence which contracts go in, the where will end up with six contradictory conditions and two commented out.
• Normalise over types, never over strings. Any like over a date is a bomb on a timer.

A domain model isn't designed: it's negotiated with the data, and the data doesn't give. Every time I reverted something it was because I had decided what I should have asked.