SNOW-912320: The behavior of save_as_table(mode='overwrite') is inconsistent between v1.5.1 and v1.6.1

[everpeace]

1. What version of Python are you using?

   3.8.17 (default, Jul 5 2023, 20:35:26) [GCC 11.2.0](python worksheet in snowflake console)

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

   Linux-5.4.181-99.354.amzn2.aarch64-aarch64-with-glibc2.17 (python worksheet in snowflake console)

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

   tested in python worksheet in snowflake console only with snowflake-snowpark-python dependency(anaconda)

4. What did you do?

   Run below python worksheet with snowflake-snowpark-python==1.5.1 and snowflake-snowpark-python==1.6.1 and see the result is inconsistent.

# The Snowpark package is required for Python Worksheets. 
# You can add more packages by selecting them using the Packages control and then importing them.

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, lit
from snowflake.snowpark.types import LongType


def main(session: snowpark.Session) -> snowpark.DataFrame: 
    TABLE_NAME = "_TEST"
    # clean the table first
    session.sql(f"DROP TABLE IF EXISTS {TABLE_NAME}").collect()

    # create table with dataframe
    df = session.create_dataframe([1,2]).to_df(["a"])
    df.write.save_as_table(TABLE_NAME)

    # adding column 'b' as LongType() with NULL values
    # note: lit(None) infers its datatype with StringType().
    #       So, adding dummy_column with None value first
    #       and then cast it to LongType()
    tbl = session.table(TABLE_NAME).select('*')
    tbl = tbl.with_column('b_none', lit(None))
    tbl = tbl.with_column('b', col('b_none').astype(LongType()))
    tbl = tbl.drop('b_none')

    # overwrite it
    tbl.write.save_as_table(TABLE_NAME, column_order='name', mode='overwrite')

    # The output of snowflake-snowpark-python v1.5.1:
    # --------------
    # |"A"  |"B"   |
    # --------------
    # |1    |NULL  |
    # |2    |NULL  |
    # --------------
    #
    # The output of snowflake-snowpark-python v1.6.1:
    # It expects to be the same with v1.5.1
    # -------------
    # |"A"  |"B"  |
    # -------------
    # |     |     |
    # -------------
    return session.table(TABLE_NAME).select('*')

5. What did you expect to see?

   consistent results between 1.5.1 and 1.6.1

6. Can you set logging to DEBUG and collect the logs?

   I could not extract log in python worksheet in snowflake console. sorry.

[sfc-gh-aalam]

There was a behavior change with version 1.6.1 where if the schema of the dataframe (either inferred or explicitly defined) is non-nullable, then we don't input NULL columns into table using save_as_table.
https://github.com/snowflakedb/snowpark-python/releases/tag/v1.6.1. As a part of this behavior change, save_as_table now creates table using a 2-step process, 1. create or replace table, and 2. insert into table.

When you are overwriting into the same database name, create or replace table overwrites the source data from column A which gives you an empty table as a result. Can you trying overwriting it into a different table name?