topic select table_changes() return unexpected results in Data Engineering

select table_changes() return unexpected results

hdu — Wed, 08 Jul 2026 15:39:25 GMT

Hi Folks,

My understanding is

SELECT *
FROM table_changes('mytable', x)

should return all changes from version x through the latest available version, regardless of when I run it. However what happen to me is:

When immediately after a streaming table update it only returns changes for version x, this is as expected.
but after some times, it returns all versions 1, 2, ..., x-1, x, x+1, ..., this is not as expected. It should only return versions x, x+1, ....

in my example below, I expect the second query only return one row: 2026-04-08 8676 from version 25 only, but it return other versions as well. Any thoughts why?

Re: select table_changes() return unexpected results

Ashwin_DSA — Wed, 08 Jul 2026 20:32:13 GMT

Hi @hdu,

When you call table_changes('mytable', 25), the start argument is inclusive, and if you omit end, Databricks should return changes from version 25 through the latest available version only.

So if you are literally seeing rows whose CDF metadata shows _commit_version < 25, that would not match the documented behaviour.

That said, one important nuance is that table_changes returns row-level change events, not "new business dates." The rows include CDF metadata columns such as _commit_version, _commit_timestamp, and _change_type, and those are the fields to use when checking whether older table versions are leaking into the result. A row with date_extraction = 2026-04-05 can still legitimately appear in commit version 25 if version 25 updated or re-emitted that row.

From your screenshot, DESCRIBE HISTORY shows versions 22, 23, 24, and 25 all as STREAMING UPDATE, each with the same numOutputRows = 8676. That pattern suggests the pipeline may be rewriting or updating the entire set of rows on each refresh, rather than appending a single new date_extraction value. In that case, seeing multiple date_extraction values in the output of table_changes(..., 25) would not necessarily mean older commit versions are being returned.

Can you check the CDF metadata directly with something like this...

SELECT
  _commit_version,
  _change_type,
  _commit_timestamp,
  date_extraction,
  count(*) AS n
FROM table_changes('mytable', 25)
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 4;

If that query shows only _commit_version >= 25, then table_changes is behaving as documented, and what you’re seeing is most likely older business dates being touched again by commit 25 rather than old table versions being returned.

If it shows any _commit_version < 25, then that would look inconsistent with the documented semantics, and it would be worth opening a support case or filing a bug with a minimal repro. For reference, the public docs for table_changes and Change Data Feed both describe the start version as inclusive and the no-end form as "from start to latest."

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

Re: select table_changes() return unexpected results

hdu — Thu, 09 Jul 2026 14:46:34 GMT

Thank you, Ashwin, for looking into my question.

My table is appended whenever a new file arrives. Each file contains the same number of records (8,676), but with a different extraction date.

Regarding the results of your query above, two additional files have been appended since version 25.

_commit_version _change_type _commit_timestamp date_extraction n
25 insert 2026-07-08T15:01:20.000+00:00 2026-04-09 8676
26 insert 2026-07-08T19:49:47.000+00:00 2026-04-10 8676
27 insert 2026-07-09T12:51:10.000+00:00 2026-04-11 8676

When I run query below, I expect to see two rows, without extraction_date = '2026-04-09', however it returns three rows, including version 25.

SELECT date_extraction, count(*) AS n FROM table_changes('duh_dev_bronze.sibl.cup_rate', 26) GROUP BY 1 ORDER BY 1; date_extraction n 2026-04-09 8676 2026-04-10 8676 2026-04-11 8676

Re: select table_changes() return unexpected results

binlogreader — Tue, 21 Jul 2026 03:16:49 GMT

Perhaps you try running one more variation?

Your second query groups by date_extraction only which is a business column, so it can't tell us which commit those rows came from. Seeing the 2026-04-09 rows doesn't yet prove the result contains version 25. It could equally mean a commit at or after version 26 wrote rows carrying that older date. The same query with the version column visible would settle it:

```sql
SELECT _commit_version, _change_type, date_extraction, count(*) AS n
FROM table_changes('duh_dev_bronze.sibl.cup_rate', 26)
GROUP BY ALL
ORDER BY 1;
```

If the 04-09 rows come back with a _commit_version of 26 or higher, the feed is fine and something wrote that older date again after your first check. Worth asking then is whether the source file was re-uploaded or modified (a stream with allowOverwrites picks it up again), whether a backfill ran, or whether the streaming table was full refreshed, which rewrites every row under new versions and would also explain your original observation that after some time "all versions" seem to come back.