# Snowflake Client

Execute SQL queries and statements against Snowflake data warehouses with full type safety and runtime validation.

## Methods

| Method                                      | Description                                                                |
| ------------------------------------------- | -------------------------------------------------------------------------- |
| `query<T>(sql, schema, params?, metadata?)` | Execute a SELECT query and return validated, typed results                 |
| `execute(sql, params?, metadata?)`          | Execute a statement (INSERT, UPDATE, DELETE) and return affected row count |

## Usage

### Basic Query with Schema Validation

```typescript
import { api, z, snowflake } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_SNOWFLAKE = "a1b2c3d4-5678-90ab-cdef-snowflak0001";

// Note: Snowflake returns UPPERCASE column names by default
const UserSchema = z.object({
  ID: z.string(),
  NAME: z.string(),
  EMAIL: z.string().email(),
  CREATED_AT: z.string(),
});

export default api({
  integrations: {
    snowflake: snowflake(PROD_SNOWFLAKE),
  },
  name: "SnowflakeExample",
  input: z.object({
    status: z.string(),
  }),
  output: z.object({
    users: z.array(UserSchema),
  }),
  async run(ctx, { status }) {
    const users = await ctx.integrations.snowflake.query(
      "SELECT ID, NAME, EMAIL, CREATED_AT FROM USERS WHERE STATUS = ?",
      UserSchema,
      [status],
    );

    return { users };
  },
});
```

### Using Column Aliases for Lowercase Names

```typescript
// Use aliases to get lowercase property names
const UserSchema = z.object({
  id: z.string(),
  name: z.string(),
  email: z.string().email(),
});

const users = await ctx.integrations.snowflake.query(
  `SELECT
    ID as "id",
    NAME as "name",
    EMAIL as "email"
  FROM USERS
  WHERE STATUS = ?`,
  UserSchema,
  ["active"],
);
```

### Executing INSERT, UPDATE, DELETE Statements

```typescript
// INSERT
await ctx.integrations.snowflake.execute(
  "INSERT INTO USERS (NAME, EMAIL) VALUES (?, ?)",
  ["John Doe", "john@example.com"],
);

// UPDATE
const updateResult = await ctx.integrations.snowflake.execute(
  "UPDATE USERS SET LAST_LOGIN = CURRENT_TIMESTAMP() WHERE ID = ?",
  [userId],
);
console.log(`Updated ${updateResult.rowCount} rows`);

// DELETE
const deleteResult = await ctx.integrations.snowflake.execute(
  "DELETE FROM SESSIONS WHERE EXPIRES_AT < CURRENT_TIMESTAMP()",
);
console.log(`Deleted ${deleteResult.rowCount} expired sessions`);
```

### Query with Snowflake-Specific Functions

```typescript
const AnalyticsSchema = z.object({
  DATE: z.string(),
  TOTAL_SALES: z.string(), // NUMBER type returned as string
  AVG_ORDER_VALUE: z.string(),
});

const analytics = await ctx.integrations.snowflake.query(
  `SELECT
    DATE_TRUNC('day', ORDER_DATE) as DATE,
    SUM(TOTAL) as TOTAL_SALES,
    AVG(TOTAL) as AVG_ORDER_VALUE
  FROM ORDERS
  WHERE ORDER_DATE >= DATEADD('day', -30, CURRENT_DATE())
  GROUP BY DATE_TRUNC('day', ORDER_DATE)
  ORDER BY DATE DESC`,
  AnalyticsSchema,
);
```

### Working with VARIANT (JSON) Columns

```typescript
const EventSchema = z.object({
  EVENT_ID: z.string(),
  EVENT_TYPE: z.string(),
  // VARIANT columns are parsed as JSON
  PAYLOAD: z.object({
    user_id: z.string(),
    action: z.string(),
    metadata: z.record(z.unknown()).optional(),
  }),
});

const events = await ctx.integrations.snowflake.query(
  `SELECT
    EVENT_ID,
    EVENT_TYPE,
    PARSE_JSON(PAYLOAD) as PAYLOAD
  FROM EVENTS
  WHERE EVENT_TYPE = ?`,
  EventSchema,
  ["user_action"],
);
```

## Trace Metadata

All methods accept an optional `metadata` parameter as the last argument for diagnostics labeling. See the [root SDK README](../../../README.md#trace-metadata) for details.

## Common Pitfalls

### Uppercase Column Names by Default

Snowflake returns column names in UPPERCASE unless you use quoted identifiers. This applies to all columns, including aggregate functions like `COUNT`, `SUM`, etc.

```typescript
// Query: SELECT id, name FROM users

// WRONG - Column names will be uppercase
const schema = z.object({
  id: z.string(),
  name: z.string(),
});

// CORRECT - Match Snowflake's uppercase column names
const schema = z.object({
  ID: z.string(),
  NAME: z.string(),
});

// CORRECT - Use aliases with quotes for lowercase
// SELECT id as "id", name as "name" FROM users
const schema = z.object({
  id: z.string(),
  name: z.string(),
});
```

### Aggregate Function Column Names Are Uppercase

Aggregate functions like `COUNT(*)` return columns with uppercase names. The column name is determined by Snowflake's default naming:

```typescript
// Query: SELECT COUNT(*) as count FROM orders

// WRONG - Snowflake returns 'COUNT' (uppercase), not 'count'
const schema = z.object({
  count: z.coerce.number(), // Fails validation - column doesn't exist
});

// CORRECT - Use uppercase column name
const schema = z.object({
  COUNT: z.coerce.number(),
});

// CORRECT - Use quoted alias for lowercase
// SELECT COUNT(*) as "count" FROM orders
const schema = z.object({
  count: z.coerce.number(),
});

// Example with multiple aggregates
const StatsSchema = z.object({
  TOTAL_ORDERS: z.coerce.number(),
  TOTAL_REVENUE: z.coerce.number(),
  AVG_ORDER_VALUE: z.coerce.number(),
});

const stats = await ctx.integrations.snowflake.query(
  `SELECT
    COUNT(*) as TOTAL_ORDERS,
    SUM(AMOUNT) as TOTAL_REVENUE,
    AVG(AMOUNT) as AVG_ORDER_VALUE
  FROM ORDERS
  WHERE CREATED_AT > ?`,
  StatsSchema,
  [startDate],
);
```

### NUMBER Type Returned as Strings

Snowflake's `NUMBER` type (especially with high precision) is returned as strings to preserve precision:

```typescript
// For column: AMOUNT NUMBER(38,2)

// WRONG - May lose precision
const schema = z.object({ AMOUNT: z.number() });

// CORRECT - Handle as string
const schema = z.object({ AMOUNT: z.string() });

// CORRECT - Transform if precision loss is acceptable
const schema = z.object({
  AMOUNT: z.string().transform((val) => parseFloat(val)),
});
```

### Schema Parameter is Required

Like PostgreSQL, the `query()` method requires a Zod schema:

```typescript
// WRONG - Missing schema
const users = await ctx.integrations.snowflake.query("SELECT * FROM USERS", [
  /* params */
]);

// CORRECT - Schema is required as second parameter
const users = await ctx.integrations.snowflake.query(
  "SELECT * FROM USERS WHERE ID = ?",
  UserSchema,
  [userId],
);
```

### Timestamp Handling

Snowflake timestamps have specific formats that may need parsing:

```typescript
const schema = z.object({
  // TIMESTAMP_NTZ - no timezone info
  CREATED_AT: z.string(),

  // Transform to Date object
  UPDATED_AT: z.string().transform((val) => new Date(val)),

  // For TIMESTAMP_TZ - includes timezone
  EVENT_TIME: z.string(),
});
```

### Case-Sensitive Object Names

Snowflake treats unquoted identifiers as uppercase. Be careful with case:

```typescript
// These are EQUIVALENT in Snowflake:
// SELECT * FROM users
// SELECT * FROM USERS
// SELECT * FROM Users

// To use lowercase table/column names, use quotes in Snowflake:
// SELECT * FROM "users"  -- lowercase table
// SELECT "name" FROM users  -- lowercase column
```

### NULL Handling

```typescript
// WRONG - Will fail if column is NULL
const schema = z.object({
  NAME: z.string(),
  DESCRIPTION: z.string(),
});

// CORRECT - Handle nullable columns
const schema = z.object({
  NAME: z.string(),
  DESCRIPTION: z.string().nullable(),
});
```

### Working with Semi-Structured Data

When querying VARIANT, ARRAY, or OBJECT columns:

```typescript
// For VARIANT column containing JSON
const schema = z.object({
  DATA: z.unknown(), // Accept any JSON structure
});

// Or with specific structure
const schema = z.object({
  DATA: z.object({
    key: z.string(),
    value: z.number(),
  }),
});

// Use Snowflake's JSON functions in query
const result = await ctx.integrations.snowflake.query(
  `SELECT
    DATA:key::STRING as KEY,
    DATA:value::NUMBER as VALUE
  FROM MY_TABLE`,
  z.object({ KEY: z.string(), VALUE: z.string() }),
);
```

## Error Handling

### QueryValidationError

Thrown when query results fail schema validation:

```typescript
import { QueryValidationError } from "@superblocksteam/sdk-api";

try {
  const users = await ctx.integrations.snowflake.query(
    "SELECT * FROM USERS",
    UserSchema,
  );
} catch (error) {
  if (error instanceof QueryValidationError) {
    console.error("Row index:", error.details.rowIndex);
    console.error("Validation errors:", error.details.errors);
    console.error("Actual row data:", error.details.row);
  }
}
```

## API Reference

- [Snowflake Documentation](https://docs.snowflake.com/)
- [Snowflake SQL Reference](https://docs.snowflake.com/en/sql-reference)
- [Snowflake Data Types](https://docs.snowflake.com/en/sql-reference/data-types)