feat: ibis-framework: Snowflake backend should pass `force_microsecond_precision=True`

[kbzsl]

Is your feature request related to a problem?

When using the Snowflake backend, to_polars() can fail with a schema mismatch error on timestamp columns. The root cause is that ibis.backends.snowflake.Backend._make_batch_iter calls cursor.fetch_arrow_batches() without passing force_microsecond_precision=True.

ArrowInvalid: Schema at index 6 was different: 
timestamp_column: timestamp[ns]
vs
timestamp_column: timestamp[us]

The ibis Snowflake backend fetches Arrow data in two places:

• _make_batch_iter — called internally by expr.to_polars() and expr.to_pyarrow_batches()
• Backend.to_pyarrow — called internally by expr.to_pyarrow()

What is the motivation behind your request?

The Snowflake connector already has a built-in fix: cursor.fetch_arrow_batches(force_microsecond_precision=True), which forces all timestamp columns to timestamp[us] across all batches.

Connector docstring (from snowflake-connector-python, cursor.py, SnowflakeCursorBase.fetch_arrow_batches):

force_microsecond_precision: When True, all timestamp columns are converted to microsecond precision, ensuring consistent schema across all batches. This is useful when your data contains timestamps outside the nanosecond range (1677-2262), such as '9999-12-31' or '0001-01-01'. When False (default), precision is determined per-batch based on the data, which may cause pyarrow schema mismatch errors when combining batches.

Note: the force_microsecond_precision parameter is exposed in fetch_arrow_all(), fetch_arrow_batches(), fetch_pandas_all() and fetch_pandas_batches().

Describe the solution you'd like

Implement the force_microsecond_precision parameter.

1. add a new force_microsecond_precision parameter to ibis.snowflake.connect(). This gives users explicit control and preserves backward compatibility by defaulting to False. The parameter is stored on the backend instance at connect time and should be threaded through to both fetch methods.

# user-facing API
ibis_con = ibis.snowflake.connect(
    **params,
    force_microsecond_precision=True,  # new parameter
)

2. as an alternative quick fix: to pass force_microsecond_precision=True* in the in places where ibis fetches Arrow data from Snowflake:

# ibis/backends/snowflake/__init__.py

# in _make_batch_iter (line 522):
for t in cur.fetch_arrow_batches(force_microsecond_precision=True)  # was: fetch_arrow_batches()

# in to_pyarrow (line 450):
res = cur.fetch_arrow_all(force_microsecond_precision=True)  # was: fetch_arrow_all()

This would immediately fix this issue, but it always forces microsecond precision. Therefore it is not a desirable solution as introduces a behavioral change.

What version of ibis are you running?

Environment

• ibis-framework: 12.0.0
• snowflake-connector-python: 4.3.0
• Python: 3.12
• OS: Windows

What backend(s) are you using, if any?

Snowflake

Code of Conduct

• I agree to follow this project's Code of Conduct

[deepyaman]

@kbzsl Thanks for the detailed feature request! I agree that Option 1 sounds better of the two you suggest. As force_microsecond_precision was only added to snowflake-connector-python a couple months ago, it wouldn't hurt to also ensure the argument doesn't get passed for older snowflake-connector-python versions; perhaps default force_microsecond_precision: bool | None = None for the Ibis connection, and explicitly pass the value if the user provided a boolean value (and let it error out if the user passes a boolean value and the snowflake-connector-python version doesn't accept it)—just thinking out loud.

But, since you've also already raised snowflakedb/snowflake-connector-python#2834, maybe that's better still? That was, Ibis doesn't need to handle this specially at all, and doesn't need to consider alignment with what Snowflake eventually decides to do on that issue. It didn't sounds like the maintainers were explicitly opposed to it; do you plan to try and implement it, else I'm open to trying to find some time to put together a PR for it?

[kbzsl]

@deepyaman
Thank you for the feedback. We agree that Option 1 (adding force_microsecond_precision to the Ibis connector) is the best approach.

Since this involves inter-working between two packages, I recommend contacting the Snowflake team to agree on a robust and future-proof solution. I do not feel I have enough expertise in this area.

[kbzsl]

Sharing the workaround I used here, in case anyone else runs into the same issue.

# 1. snowflake connector
sf_con = snowflake.connector.connect(**connector_parameters)

# 2. ibis connector
ibis_con = ibis.snowflake.from_connection(sf_con, create_object_udfs = False

# 3. compose the query with ibis
my_view = ibis_con.table('MY_VIEW')
my_expr = my_view.select('some_date_field')
my_qry = ibis.to_sql(my_expr)

# 4. execute through the native (snowflake) connector
cursor = sf_con.cursor()
cursor.execute(my_qry)
df = pl.from_arrow(cursor.fetch_arrow_all(force_microsecond_precision = True))
cursor.close()

Please note, that the simple cast is not a solution in this case, even though it is compiled to a proper SQL query.

my_view = ibis_con.table('MY_VIEW')
my_expr = my_view.select(my_view.some_date_field.cast('timestamp("us")))
df = my_expr.to_polars()