select table_changes() return unexpected results

[hdu]

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?

[Ashwin_DSA]

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.

[hdu]

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