Tasy incremental loads: why rows go missing and how to prevent it

An incremental load is the right way to keep a BI on Tasy up to date without weighing on the database: instead of reading the whole table, the routine brings only rows changed since the last run, using the DT_ATUALIZACAO column. The problem is that, done the simplest way, it loses rows without warning. The BI shows 1,212 encounters for the month, Tasy's report shows 1,215, and nobody knows where the three went.

This article covers the five cases we find most often and the fix for each one.

How an incremental load works

On every run, the routine stores a watermark: the highest DT_ATUALIZACAO it read. On the next run, it asks Tasy only for rows with a later date.

SELECT nr_atendimento, ie_tipo_atendimento, dt_entrada, dt_alta, dt_atualizacao
  FROM atendimento_paciente
 WHERE dt_atualizacao > :last_watermark
 ORDER BY dt_atualizacao

Simple and light. The problems appear in situations where DT_ATUALIZACAO does not tell the whole story.

Case 1: the transaction that commits late

Tasy writes DT_ATUALIZACAO when it changes the row, but the change only becomes visible to other sessions when the transaction ends (the commit). If the transaction takes a while, a load can run in the middle and move the watermark past it.

Fix: a margin. Each load also re-reads the last minutes before the watermark (dt_atualizacao > watermark - 15 minutes). Rows read twice are resolved at the destination by the table's key, keeping the latest version. Fifteen minutes cover nearly all day-to-day transactions; very long batch routines call for a larger margin on that table.

Case 2: the deleted row

When a row is deleted in Tasy, there is no DT_ATUALIZACAO left for the load to find. An encounter registered twice and deleted by registration, or an item removed from an account, stays in the BI forever.

Fix: re-read windows. Periodically, the load re-reads a recent interval by the business date (DT_ENTRADA for encounters, the account period) and, at the destination, deletes whatever was in that interval and did not come back. In the Clic Health standard we use two windows on each transaction table: the last 7 days, every night, and the last 3 months, every Sunday. Deletions older than that are rare and show up in the monthly reconciliation.

Case 3: the table without DT_ATUALIZACAO

Some tables do not have the column, or have it and it does not change when the row changes. The most important case is CONTA_PACIENTE_RESUMO, which Tasy rebuilds entirely when the account is recalculated.

Fix: use the parent table's date and replace the children in full. The summary load filters by the DT_ATUALIZACAO of CONTA_PACIENTE and, at the destination, each changed account has its whole summary replaced with what just arrived. Adding or updating row by row keeps items Tasy has already removed. The full example, with amounts, is in CONTA_PACIENTE_RESUMO: why the summary can count twice.

Case 4: updates that do not touch the date

Some internal routines and fixes made directly in the database change values without updating DT_ATUALIZACAO. The incremental load has no way to notice.

Fix: the same windows as in case 2 pick up those changes within the re-read interval. For bulk fixes made by IT or by the vendor, agree on a heads-up: afterwards, a reload of the affected table solves it.

Case 5: clocks and time zones

If the watermark comes from the extraction server's clock rather than from the value read from the database, any difference between clocks becomes a gap. The same goes for servers in different time zones.

Fix: the watermark is always the highest DT_ATUALIZACAO read, exactly as it came from Tasy, never the time of the run.

The tests that warn before the dashboard does

Even with these fixes, automatic tests after each load are worth having. The ones we use in the Clic Health standard:

The column test catches a common problem after a Tasy upgrade: a column changed name or place, the query keeps running and the field arrives empty.

Incremental load checklist

1. The watermark comes from the data read, not from the clock.
2. There is a margin over the watermark, and the destination resolves duplicates by key.
3. Transaction tables have a short daily window and a long weekly window.
4. Tables without their own date use the parent's date and replace children in full.
5. Small master tables come in full once a day.
6. Each run logs time, rows and errors.
7. Freshness, gap, volume and column tests run after the load.
8. Every month, the BI total is checked against the Tasy report, as in how to reconcile Tasy figures with the BI.

For performance, see how to extract Tasy data without slowing the database.

Who writes this

The fixes in this article came from real reconciliations between Tasy and the BI in hospitals we work with. Domínio has worked for more than 20 years with hospitals that run Tasy; if your load has a difference and you cannot find the cause, talk to us on WhatsApp or at comercial@dominiocontabil.com.br.

Related questions

What margin should be used over the watermark?

Fifteen minutes cover normal routine. If the hospital runs long batch processes on a table, such as mass account recalculation, increase that table's margin.

Doesn't the margin bring duplicate rows?

It does, on purpose. The destination writes by the table's key and keeps the latest version of each row.

Why not reload everything every night?

For small tables, that is what we do. For large ones, re-reading years of history every day weighs on the database for no reason; 7-day and 3-month windows cover almost every case.

How do I know whether I am losing rows today?

Compare the count of a closed month in the BI with Tasy's, day by day. Differences spread across random days usually mean late transactions; extra rows in the BI usually mean deletions.