Snowflake

Snowflake is a powerful, reliable, and flexible enterprise-level data cloud platform. This technical documentation provides a comprehensive guide to integrating Snowflake with Nected. You will learn how to configure the Snowflake integration, query the database via datasets, trigger Snowflake operations as rule actions, and troubleshoot common issues. By the end of this guide, you will have the knowledge to seamlessly connect Snowflake with Nected and leverage its capabilities for your applications.

Setting Up Snowflake Integration

To establish a connection between Nected and Snowflake for seamless data interaction, you need to configure a Snowflake integration. This section outlines the step-by-step process for setting up the integration effectively.

1. Adding Integration:

   Select SnowFlake from the integrations page. This choice initiates the process of setting up the integration for SnowFlake.

   
2. Configuring Connection Settings

   The configuration of connection settings is a critical step in establishing the connection between Nected and your Snowflake database. The following parameters need to be defined:

   • Environment Type: Choose whether you are configuring the integration for a "Staging" or "Production" environment. The selection of the environment type determines the context in which the connector will operate. By default, the environment type is set to “Staging”.

   • Integration Name: Assign a unique and meaningful name for the integration. It is essential that this name is distinct throughout the Nected platform and does not contain any spaces.

   • Host (Database IP Address): Specify the IP address or hostname of the SnowFlake database to which you want to connect.

   • Port Number: Enter the port number on which Nected should communicate with the SnowFlake database. The default port for SnowFlake is 443.

   • Username: Provide the username of the authorized user who has the necessary privileges to connect to the database and perform read and write operations.

   • Password (optional, encrypted): You may enter the password for the authorized user. Passwords are encrypted to ensure maximum security for your database. If the database user does not have a password, you can leave this field blank.

   • Database: Provide the name of the Snowflake database you want to connect to.

   • Account Identifier: Specify the Snowflake account identifier. This usually includes the account name and the region.

   • Warehouse: Specify the Snowflake warehouse to use for the connection.

   • Role: Provide the Snowflake role that should be used for the connection.

   • Region: Provide the Snowflake region that should be used for the connection.

   • Additional Params: allow you to add custom key-value pairs to fine-tune the functionality and behavior of your Snowflake connection.

   Note: If using Nected cloud, Nected IP address (43.205.43.45) must be added to the allow-list on your database server to secure your connections. Not required in on-premise setup.

Next Steps After Integration

1. Testing the Connection

   After configuring the connection settings, it's crucial to test the connection to ensure that Nected can successfully communicate with the Oracle database. This test verifies that the provided information is accurate and the connection is established without issues.

2. Publishing

   Before publishing an integration, it's essential to first conduct a connection test. This ensures that everything functions correctly and meets the required standards. Once the connection test is successful, you'll be able to proceed with publishing the integration. Depending on your needs, you can choose to publish in a "Staging" environment, typically used for development or testing, or in a "Production" environment for live deployment.

You need to publish staging and production connectors in their respective environments. If a connector is published only in staging, then it cannot be used in the production environment, and any call to a rule in the production environment will fail, giving a "connector not published in production" error.

Querying Snowflake Database via DataSet

Nected provides a powerful feature that allows you to query and interact with your Snowflake database through datasets. This section will guide you through the process of adding a new dataset, writing SQL code for querying the Snowflake database, and performing various operations, including creating rows and executing join operations.

Adding a New DataSet:

Before you can query the Snowflake database, you need to add a new dataset to your Nected environment. Follow these steps:

1. Navigate to Datasets: Click on "Datasets" in the left navigation panel to access the Datasets page.

2. Create a Dataset: Click the "+ Create Dataset" button on the Datasets page. Choose the database connector associated with the dataset you wish to create. Note that you can create multiple datasets for one connector.

3. Dataset Information: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), and the source for dataset parameters.

4. Finally, write the specific database query to retrieve data from your connected database. For example, to connect the complete database within your dataset, write:

Finally, write the specific database query to retrieve data from your connected database. For example, to connect the complete database within your dataset, write:

SELECT * FROM table_name; -- Change the table_name with your database table_name

Query Types

In addition to basic querying, you can perform various operations on the data retrieved from the Snowflake database. Here are some common tasks you can carry out:

1. Aggregations: You can use aggregations to summarize and analyze your financial data. For instance, to calculate the total deposit amount for a specific day:

   
   SELECT
       transaction_date,
       SUM(amount) AS total_deposit
   FROM
       financial_transactions
   WHERE
       transaction_type = 'Deposit'
   GROUP BY
       transaction_date;
   

   This query aggregates the total deposit amount for each distinct transaction date.

2. Filtering: Filtering is essential for retrieving specific subsets of data. To find all transactions above a certain amount, you can use:

   
   SELECT
       transaction_date,
       amount,
       description
   FROM
       financial_transactions
   WHERE
       amount > 1500;
   

   This query filters and selects transactions with amounts greater than $1500.

3. Sorting: Sorting data can be useful for visualizing trends or identifying the most significant transactions. To sort transactions by amount in descending order:

   
   SELECT
       transaction_date,
       amount,
       description
   FROM
       financial_transactions
   ORDER BY
       amount DESC;
   

   This query returns transactions sorted by the amount in descending order.

4. Grouping and Aggregating: Combining grouping and aggregation is helpful for summarizing data based on certain criteria. To find the total transaction amount by transaction type:

   
   SELECT
       transaction_type,
       SUM(amount) AS total_amount
   FROM
       financial_transactions
   GROUP BY
       transaction_type;
   

   This query groups transactions by type and calculates the total amount for each type.

5. Joins: If your database has multiple related tables, joins can be used to combine data from different sources. For instance, to link transactions with account details:

   
   SELECT
       t.transaction_id,
       t.transaction_date,
       t.amount,
       a.account_name
   FROM
       financial_transactions t
   JOIN
       accounts a
   ON
       t.account_id = a.account_id;
   

   This query joins the financial_transactions table with an accounts table to retrieve transactions along with their corresponding account names.

These SQL operations and queries can help you extract valuable insights and generate reports from your financial data stored in the financial_transactions table. The principles and techniques demonstrated here can be adapted and expanded upon to suit specific analysis and reporting needs in the fintech industry or any database scenario.

Tip: Always ensure that your SQL code is well-tested in the "Staging" environment before deploying it to the "Production" environment to prevent potential data integrity issues and unexpected consequences.

Next Steps After Adding Datasets

1. Integration to Rule or Workflow: Once you've established your dataset, you can integrate it directly into rules and workflows. This integration allows you to create various conditions that define your business logic. To learn how to integrate a published dataset into rules, refer to the Rule documentation. For workflows, refer to the database node documentation.

2. Querying: Once you've integrated the dataset into your rule or workflow, you can apply queries according to your specific requirements. Customize your queries by referring to supported and unsupported query types listed below. This customization allows you to tailor your data interactions precisely to your business needs, ensuring efficient and effective utilization of your integrated dataset.

Supported Queries for Creating a Dataset:

When creating a dataset in Nected, you have the flexibility to retrieve and filter data using a variety of SQL queries. These queries help you shape your dataset to meet your analytical needs. Below are the supported queries for creating a dataset:

Query Type	Description	Example
Selection	Retrieve all columns and rows from a table.	SELECT * FROM table_name;
Filtered Query	Retrieve specific rows based on a condition.	SELECT * FROM table_name WHERE column_name = 'value';
Join Query	Combine rows from two or more tables based on a related column.	SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.common_column = t2.common_column;
Aggregated Query	Perform aggregation functions like SUM, COUNT, AVG, etc., on grouped data.	SELECT column_name, SUM(amount) FROM table_name GROUP BY column_name;
Ordered Query	Retrieve rows sorted by a specific column in ascending or descending order.	SELECT * FROM table_name ORDER BY column_name DESC;
Join with Filter	Combine rows from multiple tables with a filter condition applied.	SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.common_column = t2.common_column WHERE t1.column_name = 'value';
Grouped Query	Group rows based on a specific column and apply aggregation functions.	SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
Complex Query	Combine multiple SQL operations such as joins, filters, and aggregations to create advanced views.	SELECT t1.column1, SUM(t2.amount) FROM table1 t1 JOIN table2 t2 ON t1.common_column = t2.common_column WHERE t1.column_name = 'value' GROUP BY t1.column1 ORDER BY SUM(t2.amount) DESC;
Subquery	Use a subquery (nested query) to perform more complex operations and retrieve specific data.	SELECT column1, (SELECT COUNT(*) FROM table2 WHERE table2.common_column = table1.common_column) AS count FROM table1;
Conditional Query	Use conditional statements to apply different criteria within the same query.	SELECT column_name, CASE WHEN condition THEN 'value1' ELSE 'value2' END AS new_column FROM table_name;

Non-Supported Queries for Creating a Dataset

While Nected offers extensive support for data retrieval and shaping, some certain queries and operations are not supported when creating a dataset:

Query Type	Description	Example
Delete Queries	Removing records from a table is not supported when creating a dataset.	DELETE FROM products WHERE price < 10;
Truncate Table	Truncating a table to remove all records is not supported.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported.	CREATE INDEX idx_customer_name ON customers (customer_name);@
Stored Procedures	Creating stored procedures and functions within the database is not supported.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported.	GRANT SELECT ON products TO user1;

Trigger SnowFlake Operations as Rule Actions

In Nected, rule actions provide a way to automate SnowFlake operations when specific conditions are met. While datasets are typically used for read-only queries, rule actions allow you to modify the dataset and trigger specific operations within your SnowFlake database. Below, we'll explore some SQL queries that can be used as rule actions to write or edit data within the financial_transactions table:

1. Inserting New Financial Transactions:

1. Static Query

   To insert new financial transactions into the SnowFlake database as a rule action, you can use the INSERT INTO statement with static values. For example, if a rule is triggered to log a new deposit:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('2023-10-21', '09:30:00', 106, 'Deposit', 1500.00, 'USD', 'Client Payment', 'Completed');
   

   This query adds a new deposit transaction to the database with the specified details.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to insert a new transaction with attributes from either of those:

   INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status)
   VALUES ('{{.dataSet.transaction_date}}', '{{.dataSet.transaction_time}}', {{.customInput.account_id}}, '{{.dataSet.transaction_type}}', {{.outputData.amount}}, '{{.outputData.currency_code}}', '{{.outputData.description}}', 'Completed');
   

   This query fetches the transaction details from the rule's output data, custom input and details from dataset attributes.

2. Updating Transaction Status:

1. Static Query

   You can use the UPDATE statement with static values as a rule action to modify the status of specific transactions. For instance, if a rule action is set to change the status of a transaction:

   UPDATE financial_transactions
   SET status = 'Completed'
   WHERE transaction_id = 21;
   

   This query updates the status of a transaction with a specific transaction_id to 'Completed'.

2. Tokenized Query Using Output Data, Custom Input and/or Data from Dataset

   To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to update the status of a transaction based on either of these:

   UPDATE financial_transactions
   SET status = '{{.outputData.new_status}}'
   WHERE transaction_id = {{.customInput.transaction_id}};
   

   This query adjusts the status of a transaction using data from the rule's output, custom inputs. If needed, you can also use token to extract data from dataset attributes.

These SQL queries demonstrate how you can use rule actions with both static and tokenized queries to perform data modification operations within the SnowFlake database in response to specific triggers or conditions. Rule actions offer flexibility in automating data updates, ensuring data accuracy, and enhancing the functionality of your fintech application or database.

Supported Queries for Writing an Action

Nected allows you to perform various actions on your data, such as inserting, updating, and merging records, all while ensuring data integrity. Here are the supported queries for writing an action:

Query Type	Description	Example
Insert Queries	Add new records to a table.	INSERT INTO products (product_name, price) VALUES ('New Product', 50);
Update Queries	Modify existing records in a table.	UPDATE customers SET email = '[e-mail]' WHERE customer_id = 123;
Merge Queries	Combine data from source table(s) into a target table, typically used for data synchronization.	MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.value = source_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source_table.id, source_table.value);
Transactional Queries	Execute a sequence of queries within a transaction to ensure consistency.	BEGIN; INSERT INTO orders (order_id, customer_id, order_date) VALUES (789, 123, '2023-11-15'); UPDATE customers SET total_orders = total_orders + 1 WHERE customer_id = 123; COMMIT;

Non-Supported Queries for Writing an Action

When it comes to writing actions in Nected, there are limitations on the types of queries and operations that can be performed to ensure data consistency and security. Here are the queries that are not supported for writing an action:

Query Type	Description	Example
Truncate Table	Truncating a table to remove all records is not supported as actions.	TRUNCATE TABLE orders;
Create, Alter, Drop Queries	Queries for creating, altering, or dropping tables and database schema are not supported as actions.	CREATE TABLE new_table (column1 INT, column2 VARCHAR);
Indexing Queries	Creating or managing indexes on tables is not supported as actions.	CREATE INDEX idx_customer_name ON customers (customer_name);
Stored Procedures	Creating stored procedures and functions within the database is not supported as actions.	CREATE PROCEDURE calculate_total_sales() BEGIN SELECT SUM(sale_amount) FROM sales; END;
Grant and Revoke Permissions	Managing user privileges and permissions on database objects is not supported as actions.	GRANT SELECT ON products TO user1;