SNOW-845443: Support for pythonic creation of tables with primary/foreign key relationships

[mjclarke94]

What is the current behavior?

Other than achieving this through SQL statements, there doesn't appear to be a particularly clean way of defining primary keys when instantiating tables, nor highlighting that a foreign key constraint exists dependant on another table. Copying the schema from an existing table when creating a Snowpark DataFrame and overwriting the contents results in a loss of these constraints.

What is the desired behavior?

It should be possible to replicate the following table structures using pythonic interfaces:

CREATE OR REPLACE TABLE TABLEA (
  AssetID BINARY(16) PRIMARY KEY,
  TS TIMESTAMP
);

CREATE OR REPLACE TABLE Mapping (
  AlternateID VARIANT PRIMARY KEY,
  AssetID BINARY(16),
  FOREIGN KEY (AssetID)
   REFERENCES TABLEA(AssetID)
    ON DELETE CASCADE
);

How would this improve snowflake-snowpark-python?

Allows for deterministic and declarative creation of tables and the relationship between these tables, as might be done using DBT or Terraform (or both!).

[sfc-gh-aling]

thanks for your feedback, we will consider adding the feature.

in the meanwhile, I think the project snowflake-sqlalchemy: https://github.com/snowflakedb/snowflake-sqlalchemy is an alternative for deterministic and declarative creation of tables and the relationship between these tables.