SNOW-669650: write_pandas is not atomic

[burkaygur]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.9.2

2. What operating system and processor architecture are you using?

   macOS-10.15.4-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

   snowflake-connector-python==2.8.0 snowflake-sqlalchemy==1.4.1 pandas==1.4.4 pyarrow==8.0.0

4. What did you do?

import pandas as pd
import snowflake.connector.pandas_tools as snowflake_pandas
import snowflake

d = {"col12": [1, 2], "col2": [3, 4]}
df = pd.DataFrame(data=d)

conn = snowflake.connector.connect(
    user="XXX",
    password="XXX",
    account="XXX",
    database="XXX",
    schema="XXX",
    warehouse="XXX",
)

conn.cursor().execute("USE ROLE ACCOUNTADMIN;")

snowflake_pandas.write_pandas(
    conn,
    df,
    table_name="testtable",
    database="XXX",
    schema="XXX",
    overwrite=True,
    auto_create_table=True,
    quote_identifiers=False,
)

# Add bad character to column name to make the function fail
d = {"col12%": [1, 2], "col2": [3, 4]} 
df = pd.DataFrame(data=d)

snowflake_pandas.write_pandas(
    conn,
    df,
    table_name="testtable",
    database="XXX",
    schema="XXX",
    overwrite=True,
    auto_create_table=True,
    quote_identifiers=False,
)

5. What did you expect to see? I expected that write_pandas is atomic. When my write_pandas call fails, I should still have the previous version of my table in place. I should only see it change if everything else is successful.

[sfc-gh-sfan]

overwrite is a new feature introduced recently but as you said it does not seem to handle atomicity well. This looks like something that should be fixed when overwrite is enabled. @iamontheinet : Do you want to take this up since you originally contributed overwrite?

[sfc-gh-achandrasekaran]

@burkaygur we will look at this issue in the next couple weeks.

[burkaygur]

Sounds good @sfc-gh-achandrasekaran! Thank you very much ❄️