}>Snowflake

Snowflake is an independent cloud data warehouse with compute-based pricing. ## Features | Feature | Supported | |-----------------------------------------------|-----------| | [Batch Mode](#batch-mode) | ✅ | | [Stream Mode](#stream-mode) | ✅ | | [Deduplication](#deduplication) | ✅ | | [Queries Optimization](#queries-optimization) | ✅ | ## Configuration ## Advanced: Implementation Details ### Batch Mode [User Stages](https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage#user-stages) is used as a intermediate storage for batched events.

Algorithm

```sql -- Write to tmp file -- Load tmp file to user stage BEGIN -- start transaction COPY from stage to tmp_table INSERT into target_table select from tmp_table COMMIT -- commit transaction ```

### Stream Mode `INSERT INTO target_table (...) VALUES (..)` ### Deduplication For batch mode the following algorithm is used:

Algorithm

```sql -- Write to tmp file -- Deduplicate rows in tmp file -- Load tmp file to user stage BEGIN -- start transaction COPY from stage to tmp_table MERGE into target_table using (select from tmp_table) ... COMMIT -- commit transaction ```

For stream mode: - SELECT by primary key - Either INSERT or UPDATE depending on result ### Queries Optimization **Timestamp** connection setting is used to optimize SELECT queries. Jitsu sets [clustering key](https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html#what-is-a-clustering-key) to the month part of specified timestamp column values, e.g. `CLUSTER BY (DATE_TRUNC('MONTH', _timestamp))`