Cloud-based data warehouses such as Snowflake are frequently used in analytics. Magpie can access data from Snowflake using a specialized connector. The Snowflake connector enables improved data throughput (compared to the standard JDBC connector) and advanced features like query pushdown from Magpie into Snowflake, which is enabled by default.
Interacting with Snowflake is similar to interacting with relational databases. By creating a Snowflake data source, you can use Magpie to access data within a Snowflake database and either import the data into Magpie for further analysis or execute queries directly against the data in Snowflake.
Snowflake sources have a few key attributes that need to be configured as part of their creation. Please view the Data Source JSON specification for more details. An example of such a command for Snowflake is shown below
create data source {
"name": "my_snowflake_source",
"sourceType": "snowflake",
"hostname": "xy12345.us-east-1.snowflakecomputing.com",
"user": "my_username",
"password": "********",
"database": "analytics",
"defaultSchema": "public"
}
Once a Snowflake source has been created, the tables within it can be used as the underlying storage for tables within Magpie. The commands used for JDBC sources are applicable to snowflake sources as well.
Security
To access Snowflake data sources, we recommend creating a dedicated service account for Magpie access. This account should be assigned a role with appropriate access in your Snowflake Environment. Please reach out to a member of the Silectis team with any questions or support requests.
Note: Database data sources should generally be created using a user with read-only permissions to the target database. This can prevent unwanted updates to the target database resulting from actions within Magpie. If you do need to write data back to the database, it may be useful to define a separate data source to be used for writing, with stricter permissions.