Postgres cache rounds large numeric-string IDs: `pd.read_json` re-types string columns on load

Open Beginner friendly
  • Is something wrong?
#1,261 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

@HootRock is already working on this.

Since Nov 2, 2026.

  • #1436 by @HootRock — open

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
82/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
mysql, pandas, postgresql, python
Domain
data-engineering, databases

Research direction

Start at SqlProcessorBase._write_files_to_new_table and inspect the pandas read_json call, then review the processor-level regression-test approach referenced in #1163. Done means numeric-string IDs remain unchanged through the generic Postgres/MySQL cache path while numeric and timestamp columns retain their existing behavior.

Written by the indexing model from the issue text.

Description

Summary

SqlProcessorBase._write_files_to_new_table reads each JSONL batch back with
pd.read_json(file_path, lines=True). With pandas' default dtype=True, a column
the stream schema declares as string is converted to float64 (then int64)
whenever every value in that batch is all digits. The converted value is then
inserted into the VARCHAR column the same schema created, so nothing fails.

Any ID above 2^53 is silently rounded. Facebook Marketing IDs are 18 digits and
documented as numeric strings, so every one of them is affected:

Emitted by the source Stored (batch without nulls) Stored (batch with a null in the column)
"120248952188490650" 120248952188490656 120248952188490660
"23724252360" 23724252360 23724252360.0
"0012" 12 12.0

Two different IDs within ±8 of each other collapse to the same value, which also
corrupts merge keys. A batch containing any non-numeric value in the column is
left alone, so the result depends on batch contents.

Affects caches on the generic pandas load path (Postgres, MySQL). DuckDB,
BigQuery and Snowflake override _write_files_to_new_table. Present in 0.53.3,
0.71.0 and 1.0.0-rc.5; pandas 2.2.3.

Reproduction

import io
import pandas as pd

batch = '{"ad_id": "120248952188490650"}\n{"ad_id": "120248952188490650"}\n'
print(pd.read_json(io.StringIO(batch), lines=True)["ad_id"].tolist())
# [120248952188490656, 120248952188490656]
print(pd.read_json(io.StringIO(batch), lines=True, dtype=False)["ad_id"].tolist())
# ['120248952188490650', '120248952188490650']

End to end: build a PostgresCache, get a record processor for a stream with
schema {"ad_id": {"type": ["null", "string"]}}, process one record with
ad_id = "120248952188490650", and SELECT ad_id returns 120248952188490656.

Proposed fix

Pass dtype=False, so the declared schema, not pandas' inference, decides types:

-dataframe = pd.read_json(file_path, lines=True)
+dataframe = pd.read_json(file_path, lines=True, dtype=False)

dtype=False returns every column as parsed from JSON: strings stay strings, and
JSON numbers, booleans and nested objects already have their types from the JSON
parser. Date parsing is controlled separately by convert_dates, so this is
independent of, and composes with, #1163. We verified against a real Postgres
that numeric and timestamp columns load identically with the change.

It is the same class of bug as #1163 (pandas inferring by name or value instead
of the declared schema), for strings instead of dates. A per-column dtype dict
would hit the raw-vs-normalized name mismatch noted in #1163; dtype=False needs
no column names.

Happy to open a PR with a processor-level regression test in the style of #1163.

Dominant language
Python
Stars
342
Forks
82
Avg merge
1d 7h
Merged PRs (30d)
74

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from airbytehq/PyAirbyte

All issues in airbytehq/PyAirbyte

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.