Snowflake integration

The SnowflakeConnection class enables seamless connectivity and data operations on Snowflake within the Datalake infrastructure. It is designed for AI agents and developers who need to read/write structured data securely and efficiently from Snowflake databases.

---

Requirements

• A valid Snowflake account

• A user with appropriate read/write access

• The groclake.datalake.connection module

---

Class: SnowflakeConnection

Handles secure connection establishment, data querying, and writing to Snowflake.

---

Constructor

SnowflakeConnection(config: dict)

Parameters:

• config (dict): Connection configuration dictionary containing:

  Key	Description	Example Value
  user	Snowflake username	"your_user"
  password	Snowflake password	"your_password"
  account	Snowflake account identifier	"xyz12345.us-east"
  warehouse	Compute warehouse name	"COMPUTE_WH"
  database	Database to connect to	"your_database"
  schema	Schema within the database	"your_schema"

---

Method: connect()

Establishes a live connection to the Snowflake instance using the provided credentials.

Example:

conn = SnowflakeConnection(config)
conn.connect()

---

Method: read(query: str) → list[dict]

Executes a SELECT query and returns the result as a list of dictionaries.

Example:

result = conn.read('SELECT * FROM "your_database".your_schema.employee;')
print(result)

---

Method: write(query: str, data: tuple)

Executes an INSERT, UPDATE, or DELETE query with parameterized values.

Example:

insert_query = """
INSERT INTO "your_database".your_schema.employee (id, name, age, department, salary)
VALUES (%s, %s, %s, %s, %s);
"""
data = (3, 'John', 52, 'Electrical', 60000)
conn.write(insert_query, data)

---

Method: close()

Closes the active Snowflake connection.

Example:

pythonCopyEditconn.close()

---

✅ Full Example

from groclake.datalake.connection import SnowflakeConnection

config = {
    "user": "your_user",
    "password": "your_password",
    "account": "your_account_id",
    "warehouse": "COMPUTE_WH",
    "database": "your_database",
    "schema": "your_schema"
}

conn = SnowflakeConnection(config)
conn.connect()

# Read from table
print(conn.read('SELECT * FROM "your_database".your_schema.employee;'))

# Insert into table
insert_query = """
INSERT INTO "your_database".your_schema.employee (id, name, age, department, salary)
VALUES (%s, %s, %s, %s, %s);
"""
data = (3, 'John', 52, 'Electrical', 60000)
conn.write(insert_query, data)

print("Data inserted successfully!")

conn.close()

---

🛡️ Best Practices

• Always store credentials securely using environment variables or secrets management tools.

• Use parameterized queries to avoid SQL injection.

• Limit access privileges for the user specified in the config for safer operation