© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
A. MortonMastering Snowflake Solutionshttps://doi.org/10.1007/978-1-4842-8029-4_8

8. Programming

Adam Morton1  
(1)
Sydney, NSW, Australia
 

A book on a database wouldn’t be complete without an element of programming. However, I am not going to teach you how to write SQL; I assume you already know how. I am also not going to walk you through each and every feature Snowflake offers in this space. Rather, I am going to give you examples of the more useful elements of Snowflake, especially where it differs from the traditional relational database management systems (RDBMSs) you might be more familiar with.

Creating New Tables

There are a few ways to create new tables in Snowflake. It’s worth being aware of them as each one offers a solution to a different use case.

Create Table Like

The CREATE TABLE LIKE command creates a new table using the following from the existing table:

  • Column names

  • Default values

  • Constraints

Importantly, it doesn’t copy any data so it’s very fast. It creates an exact shell of the original table. This can be handy in a wide variety of use cases, but especially when you need to create a table in another schema quickly and easily.

You may have done this in the past using SELECT....INTO along with WHERE 1=2 (or similar). This would have carried out a similar operation in, say SQL Server, although the constraints would not be included.

The following code shows how to use the CREATE TABLE LIKE syntax :
//CREATE TABLE LIKE SAMPLE DATA
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER
LIKE SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER;

Create Table as Select

The CREATE TABLE AS SELECT command is a more flexible option. It allows you to create a table by using a SQL SELECT query to define the structure. This means you can

  • Create a table from an existing table with all columns and rows

  • Create a table from an existing table with a subset of columns and/or rows

  • Create a table by changing the names of columns and the data types of an existing table

  • Create a table by joining other tables together

The following code shows how to use the CREATE TABLE AS SELECT syntax. In this code, you simply limit the number of rows to 100, but you could join tables together and add where clauses to any valid SQL statement.
//CREATE TABLE AS SELECT SAMPLE DATA
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER
AS
SELECT C_CUSTOMER_ID
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER
LIMIT 100;

Create Table Clone

This command is used when you want to create a new table with the same column definitions and the same data from the source table. The important point here is that, although your new table will contain all the data, no data is actually copied because it leverages cloning behind the scenes.

This command also allows you to create a table at a particular point in time. This can be really useful for testing purposes. For example, you may need to recreate an error found in production, which requires you to have a set of tables as they were prior to the error occurring. Using the CREATE TABLE CLONE command is one way of doing this.

The following code shows how to use the CREATE TABLE CLONE syntax:
//CREATE TABLE CLONE
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER_V2
CLONE STAGE.LOAD_CUSTOMER;

Copy Grants

As part of the CREATE TABLE commands above you can optionally use the COPY GRANTS command . This will inherit any existing permissions on the table you are cloning from. However, it will not inherit any future grants. This can save a lot of time when recreating permissions on a cloned object, especially if the permissions need to be exactly the same.

Here are the same three examples again with the COPY GRANTS command added:
//CREATE TABLE LIKE SAMPLE DATA
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER
LIKE SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER
COPY_GRANTS;
//CREATE TABLE AS SELECT SAMPLE DATA
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER
COPY GRANTS
AS
SELECT C_CUSTOMER_ID
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER
LIMIT 100;
//CREATE TABLE CLONE
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER_V2
CLONE STAGE.LOAD_CUSTOMER
COPY GRANTS;

Stored Procedures

The use of stored procedures in traditional databases is a cornerstone of development and has been for years. Most database processes I’ve worked on over the years prior to the cloud database are underpinned by stored procedures and with good reason.

They encapsulate business logic in code and store it in one place, promoting a modular approach to development and repeatability. This eases maintenance if logic changes in the future. You change it in one place instead of hunting through your database for code. They are also more secure that just embedding SQL code in applications, as stored procedures can take input and output parameters, reuse query cache plans, and more.

So, when Snowflake arrives as the new kid on the block, the first question database developers ask is, “Does it support stored procedures?” The sales guy says, “Yes, of course.” A collective breath of relief from the developers as they realize their database migration will be a doddle. “They are written in JavaScript,” the sales guy adds. “What? Sorry, I thought I misheard you. I thought you said JavaScript??!” the lead developer asks. “Yes, I did,” says the sales guy. The look of disbelief etched on the faces of every developer who thought JavaScript was the mainstay of web developers is worth remembering!

This was how I recall my first meeting with Snowflake regarding this point. Several people have told me a similar story when they first learned about stored procedures in Snowflake.

While the vast majority of the functionality Snowflake offers is easy to work with and use when compared to other database technologies, it’s really disappointing that stored procedures feel like a complete afterthought. Not only are they written in JavaScript, but they also have very limited debugging functionality, leaving you as a developer spending hours trying to ensure you have the correct number of quotes. I can only hope this is a candidate for improvement on the product development roadmap.

Looking to use stored procedure functionality comes up regularly when migrating from a more traditional RDBMS such as SQL Server, for example. Organizations have typically spent a lot of time and effort investing in their data warehouse and the associated stored procedures, so naturally they want to port their code over to Snowflake.

When it comes to JavaScript, you don’t really need to be concerned. I am by no means a JavaScript expert and you don’t need to be one either. You just need the recipe to work with. The JavaScript that Snowflake uses is bare bones. For example, you cannot reference third-party libraries within the stored procedures. Primarily, you just need the JavaScript elements to act as a wrapper around your SQL. The SQL is executed within this wrapper by calling functions in the JavaScript API. I hope this section will go some way to demystifying this topic for you!

Interestingly, stored procedures with the same name but with different input parameters are treated as different objects. Each input parameter you specify must have a data type associated with it.

Stored procedures allow for procedural logic, such as branching and looping. You can also create dynamic SQL statements and run them within the JavaScript API.

The following code snippet shows the minimal code elements required to wrap your SQL code within:
CREATE OR REPLACE PROCEDURE sample_stored_procedure()
returns float not null
language javascript
as
$$
var sql = `
<SQL GOES HERE>
`
var stmt = snowflake.createStatement({sqlText: sql});
var result = stmt.execute();
return result;
$$;
Let’s break this code down to understand it in more detail. As mentioned, a stored procedure can return one value. This line of code tells the procedure to return a float, which cannot be null:
returns float not null
Next is the language line, which currently can only ever be JavaScript:
language javascript
You set a variable called sql, which will contain the SQL Statement to be executed. It’s not always possible to fit a SQL statement on one line and JavaScript treats a new line as the end of a statement. To get around this, it is possible to use JavaScript techniques such as backticks (typically located at the top left of your keyboard beneath the ESC key).
`
<SQL GOES HERE>
`
Next, you pass the sql variable to the snowflake.createStatement() function. This is function is part of the JavaScript API.
var stmt = snowflake.createStatement({sqlText: sql});
You execute the SQL statement and assign the output to a variable called result.
var result = stmt.execute();
Finally, you return the result.
return result;

That is everything you need to create a basic stored procedure. It’s a lot easier if you use the JavaScript as a wrapper to begin with.

User-Defined Functions

This concept should be familiar as it’s very similar to other RDBMSs. There are two primary types of functions in Snowflake:

  • Scalar functions return one output value for each input value.

  • Tabular (table) functions return a table of zero, one, or many rows for each input row.

Functions can be written in SQL or JavaScript. Java was recently introduced as a third option. At the time of writing, it was in preview mode, so I won’t go into it in detail.

You’ll be typically working with SQL unless you wish to do any branching or looping in your function. This is when you can use JavaScript to provide greater flexibility.

Scalar Functions

An example of a scalar function is as follows. In this example, the function returns the value of PI:
//SIMPLE USER-DEFINED SCALAR FUNCTION
CREATE OR REPLACE FUNCTION PI_UDF()
  RETURNS FLOAT
  AS '3.141592654::FLOAT';
SELECT PI_UDF();

A typical example of a scalar function in the real world might be to add sales tax to a net sale value. This is a common way of ensuring you're not hard coding your sales tax rates into your code across your database.

If the sales tax changes (and believe me, it can and does!) it can turn into a huge piece of detective work to track down where it exists in your database. It’s far better to keep it in one place, and a function is the ideal place for this. The following code shows an example:
//SIMPLE USER-DEFINED SCALAR FUNCTION WITH INPUT PARAMETER
CREATE OR REPLACE FUNCTION ADD_SALES_TAX(NET_SALES FLOAT)
  RETURNS FLOAT
  AS 'SELECT NET_SALES * 1.1';
//CREATE A SIMPLE TABLE TO STORE NET SALES VALUES
CREATE OR REPLACE TABLE SALES
(NET_SALES DECIMAL);
INSERT INTO SALES
SELECT 132.21
UNION
SELECT 21.00
UNION
SELECT 2837.33
UNION
SELECT 99.99
;
//CALL THE FUNCTION
SELECT NET_SALES, ADD_SALES_TAX(NET_SALES)
FROM SALES;

Table Functions

Table functions can be very powerful. As the name suggests, rather than just returning a single value like a scalar function, they return a result set in a tabular format.

As part of an application I am developing on top of Snowflake, I need to be able to pass a customer ID into my function and return the customer’s address.

One way of doing this is by using a table function, as the following code shows:
//CREATE TABLE FUNCTION
CREATE OR REPLACE FUNCTION GET_ADDRESS_FOR_CUSTOMER(CUSTOMER_ID VARCHAR(16))
RETURNS TABLE (STREET_NUMBER VARCHAR(10), STREET_NAME VARCHAR(60), CITY VARCHAR(60), STATE VARCHAR(2), ZIP VARCHAR(10))
AS 'SELECT CA_STREET_NUMBER, CA_STREET_NAME, CA_CITY, CA_STATE, CA_ZIP
    FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER CUST
    LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER_ADDRESS ADDR ON CUST.C_CURRENT_ADDR_SK = ADDR.CA_ADDRESS_SK
    WHERE C_CUSTOMER_ID = CUSTOMER_ID';
//CALL TABLE FUNCTION
SELECT STREET_NUMBER, STREET_NAME, CITY, STATE, ZIP
FROM TABLE(GET_ADDRESS_FOR_CUSTOMER('AAAAAAAAFMHIAGFA'));

SQL Variables

You don’t need me to tell you how integral variables are in programming! To work with variables in Snowflake, there are three specific DDL commands available:

  • SET: Used to initialize variables

  • UNSET: Used to drop or remove variables

  • SHOW VARIABLES: Used to view variables within the current session

To initialize variables, you can use the SET command in SQL as follows:
SET sales_schema = 'sales';
You can also set multiple variable values at the same time as follows:
SET (sales_schema, finance_schema) = ('sales', 'fin');
SET (sales_schema, finance_schema) = (SELECT 'sales', 'fin');

It is worth noting that if you’re connecting to Snowflake from an application, you can initialize variables on the connection string by passing them in as arguments.

To reference variables within your SQL code, you prefix the values with the $ symbol, like this:
SELECT $min;
You can use variables to replace constants, but also as identifiers, such as database names, schema names, and table names. To use variables in this way, you need to make it clear to Snowflake this is your intention. You need to wrap the variable name within the IDENTIFIER() as follows:
//USING AN IDENTIFIER
USE SNOWFLAKE_SAMPLE_DATA;
SET TPC_DATE_DIM = 'TPCDS_SF100TCL.DATE_DIM';
SELECT *
FROM identifier($TPC_DATE_DIM);

To view variables defined in the current session, you can use the SHOW VARIABLES command .

It is worth noting that variables are scoped to a session, so when a user ends their session, all variables are dropped. This means that nothing outside of the current session can access these variables. To explicitly drop a variable, you can run the UNSET command .

The following example brings a lot of these concepts together:
//SET VARIABLES
SET (min, max) = (30, 70);
SELECT $min;
CREATE OR REPLACE TABLE EMPLOYEE
(EmploymentID number,
 AGE INT,
Salary number);
INSERT INTO EMPLOYEE
SELECT 1234, 18, 15000
UNION
SELECT 432, 28, 30000
UNION
SELECT 7462, 23, 17500
UNION
SELECT 8464, 37, 32000
UNION
SELECT 7373, 52, 44000;
//RUN A SELECT STATEMENT USING THE VARIABLES
SELECT AVG(salary)
FROM EMPLOYEE
WHERE AGE BETWEEN $min AND $max;
//DISPLAY THE VARIABLES
SHOW VARIABLES;
//DROP THE VARIABLES
UNSET (min, max);
//DISPLAY THE VARIABLES
SHOW VARIABLES;

Transactions

Let’s conclude this topic with a short section on transactions. A transaction is a statement or, more typically, a collection of SQL statements executed against the database as a single unit. This allows you to structure your code within transactions to ensure the integrity of your database.

A classic example is transferring funds between two bank accounts. Logically the process is as follows:
  1. 1.
    Check if the customer has enough funds to make the bank transfer.
    1. a.

      If not, abort the transaction and return an error to the customer.

       
    2. b.

      If so, subtract the funds from the customer’s account.

       
     
  2. 2.

    Credit the receiving account with the funds from the customer’s account.

     

If the credit to the receiving account returned an error, you wouldn’t want to subtract the funds from the customer’s account. To prevent this, you include the statements for points 1 and 2 within a transaction. This provides the opportunity to roll back and undo the transaction if anything within it fails.

By default, Snowflake has the AUTOCOMMIT setting set to On. This means that each and every SQL statement that contains DDL or DML will automatically commit or roll back if it fails. This is known as an implicit transaction.

In the bank account example above and many other scenarios, you will want groups of SQL statements to either succeed or fail as a whole, thereby ensuring that the integrity of the data within your database is maintained. In this case, you’ll want to use explicit transactions. This means encapsulating the SQL commands that make up your transaction within a BEGIN TRANSACTION and COMMIT or ROLLBACK TRANSACTION block, as the following pseudo-code demonstrates:
BEGIN TRANSACTION
      SQL STATEMENTS GO HERE
IF SUCCESSFUL
      COMMIT TRANSACTION
ELSE
      ROLLBACK TRANSACTION

Transactions Within Stored Procedures

A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction. However, you can’t (and probably wouldn’t want to) start a transaction in one stored procedure and finish it with another.

It helps to keep in mind that a transaction is specific to the connection it is running within. Executing a stored procedure will create its own transaction, so you cannot begin a transaction and then call a stored procedure that attempts to commit and roll back the same transaction within it.

You can, however, selectively choose what SQL statements to include within a stored procedure, as the following pseudo-code demonstrates:
CREATE PROCEDURE STORED_PROC_NAME()
      AS
$$
      ...
      SQL STATEMENT HERE;
      BEGIN TRANSACTION;
      SQL STATEMENTS HERE;
      COMMIT;
$$
You can also wrap a call to a stored procedure within a transaction as shown here:
CREATE PROCEDURE STORED_PROC_NAME()
      AS
$$
      ...
      SQL STATEMENT A;
      SQL STATEMENT B;
      SQL STATEMENT C;
$$;
BEGIN TRANSACTION;
CALL STORED_PROC_NAME();
COMMIT;

If the commit is run following the execution of the stored procedure, then all statements within the stored procedure are also committed. If you roll back the transaction, this will also roll back all statements.

The example above is the equivalent of running a bunch of SQL statements wrapped in a transaction like this:
BEGIN TRANSACTION;
      SQL STATEMENT A;
      SQL STATEMENT B;
      SQL STATEMENT C;
COMMIT TRANSACTION;

Locking and Deadlocks

It is also worth being aware that the use of transactions, either implicit or explicit, can acquire locks on a table. This can occur with UPDATE, DELETE, and MERGE statements. Thankfully, most INSERT and COPY statements write into new partitions, meaning existing data is not modified. This is really handy as it allows parallel table loading without needing to be concerned with transactions blocking each other.

If a query is blocked by another transaction running against a table, known as a deadlock, the most recent transaction will wait for a certain period of time. This is guided by a parameter setting called LOCK_TIMEOUT , which can be amended at a session level if required.

If the LOCK_TIMEOUT duration is exceeded, the most recent transaction is selected as the “deadlock victim” and is rolled back.

Transaction Tips

In practice, it is best to encapsulate related statements that need to complete (or fail) as a unit of work within explicit transactions. This makes it easier for other developers to identify them.

Breaking a process down into discrete transactions not only makes it easier to read, maintain, and manage, but it also reduces the likelihood of locking a table for longer than required. This helps to prevent any unwanted deadlocks on resources. It also means that, should a transaction fail, it gives you the flexibility to only roll back those elements of a process that absolutely need to be rolled back.

Another key point is to avoid very large transactions. Again, one reason for this is that it could lock the table out for long periods unnecessarily. If you can, break the process down into batches of transactions. This might be on numbers of rows, days, categories—essentially whatever works for you and your process.

Bringing It All Together

Let’s explore a practical example. You’ll make use of a stored procedure along with a task to show how to use stored procedures to solve your own business problems.

The Example Scenario

In this example, you want to load staging tables with some customer data from a source system. For your purpose, the Snowflake sample database will fulfil this role. You want to populate different staging tables with data relating to different US states. You then want to add this information to a task so you can run it on a schedule. You create a stored procedure to handle this for you. It’s worth noting that tasks can execute one SQL statement but, if you want to run the insert command along with logging to a control table, for example, then calling a stored procedure from a task is a common pattern.

Steps

Step 1: Create the database along with the associated database objects.
//CREATE DATABASE
CREATE OR REPLACE DATABASE RAW;
//CREATE SCHEMA
CREATE OR REPLACE SCHEMA STAGE;
//CREATE INITIAL TABLE FOR STATE 'CA' FROM SAMPLE DATA USING //CREATE TABLE AS SELECT
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER_CA
AS SELECT C_SALUTATION,
            C_FIRST_NAME,
            C_LAST_NAME,
            TO_DATE(CUST.C_BIRTH_YEAR || '-' || CUST.C_BIRTH_MONTH || '-' || CUST.C_BIRTH_DAY) AS DOB,
            CUST.C_EMAIL_ADDRESS,
            DEM.CD_GENDER,
            DEM.CD_MARITAL_STATUS,
            DEM.CD_EDUCATION_STATUS
    FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER CUST
    LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER_DEMOGRAPHICS DEM ON CUST.C_CURRENT_CDEMO_SK = DEM.CD_DEMO_SK
    LIMIT 100;
//USE CREATE TABLE LIKE TO CREATE TABLES FOR STATES NY AND DE
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER_NY
LIKE STAGE.LOAD_CUSTOMER_CA;
CREATE OR REPLACE TABLE STAGE.LOAD_CUSTOMER_DE
LIKE STAGE.LOAD_CUSTOMER_CA;
Step 2: Next, create the stored procedure to load customers. It accepts three input parameters, a schema and table name to define the table to load along with a state name that is used to filter the query that executes to populate the staging table. You also add a try/catch error block to your stored procedure template (which was introduced earlier in the chapter).
CREATE OR REPLACE PROCEDURE STAGE.LOAD_CUSTOMERS(VAR_SCHEMA VARCHAR, VAR_TABLE VARCHAR, VAR_STATE VARCHAR)
returns string
language javascript
as
$$
var sql =
    `INSERT OVERWRITE INTO RAW.` + VAR_SCHEMA + `.` + VAR_TABLE
 + ` SELECT C_SALUTATION,
            C_FIRST_NAME,
            C_LAST_NAME,
            TO_DATE(CUST.C_BIRTH_YEAR || '-' || CUST.C_BIRTH_MONTH || '-' || CUST.C_BIRTH_DAY) AS DOB,
            CUST.C_EMAIL_ADDRESS,
            DEM.CD_GENDER,
            DEM.CD_MARITAL_STATUS,
            DEM.CD_EDUCATION_STATUS
    FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER CUST
    LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER_DEMOGRAPHICS DEM ON CUST.C_CURRENT_CDEMO_SK = DEM.CD_DEMO_SK
    LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CUSTOMER_ADDRESS ADDR ON CUST.C_CURRENT_ADDR_SK = ADDR.CA_ADDRESS_SK
    WHERE ADDR.CA_STATE = '` + VAR_STATE + `';`;
try {
    snowflake.execute (
        {sqlText: sql}
        );
    return "Succeeded."; //Return a success
    }
catch (err) {
    return "Failed: " + err; //Return error
}
$$;
Step 3: Execute the stored procedure three times and pass in the parameters for the each of the states you want to load your staging tables with before you validate the results.
//TEST THE STORED PROCS WITH THE SCHEMA, TABLE AND STATE CODE
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_CA', 'CA');
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_NY', 'NY');
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_DE', 'DE');
//CHECK THE TABLES
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_CA;
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_NY;
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_DE;
Step 4: Next, create three tasks, one for each of the states you need to populate data for, which call the same stored procedure but with different parameters.
//CREATE THE TASKS
CREATE OR REPLACE TASK STAGE_LOAD_CUSTOMER_CA_TABLE
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '5 MINUTE'
AS
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_CA', 'CA');
//CREATE THE TASKS
CREATE OR REPLACE TASK STAGE_LOAD_CUSTOMER_NY_TABLE
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '5 MINUTE'
AS
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_NY', 'NY');
//CREATE THE TASKS
CREATE OR REPLACE TASK STAGE_LOAD_CUSTOMER_DE_TABLE
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '5 MINUTE'
AS
CALL STAGE.LOAD_CUSTOMERS('STAGE', 'LOAD_CUSTOMER_DE', 'DE');
Step 5: Next, test the execution of your tasks. Remember to clear out your staging tables before resuming the tasks. Finally, check the results in the staging tables.
//CLEAR THE TABLES
TRUNCATE TABLE STAGE.LOAD_CUSTOMER_CA;
TRUNCATE TABLE STAGE.LOAD_CUSTOMER_NY;
TRUNCATE TABLE STAGE.LOAD_CUSTOMER_DE;
//RESUME THE TASKS
USE ROLE ACCOUNTADMIN;
ALTER TASK STAGE_LOAD_CUSTOMER_CA_TABLE RESUME;
ALTER TASK STAGE_LOAD_CUSTOMER_NY_TABLE RESUME;
ALTER TASK STAGE_LOAD_CUSTOMER_DE_TABLE RESUME;
//AFTER 5 MINS THE TABLES SHOULD BE POPULATED
USE ROLE SYSADMIN;
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_CA;
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_NY;
SELECT COUNT(*) FROM STAGE.LOAD_CUSTOMER_DE;

You could build on this example by adding a stream to the source table, as discussed in Chapter 2. The task could then query the stream to check for any records before deciding to run the task and execute the stored procedure. This is the same technique you used in Chapter 2.

Summary

In this chapter, you explored different ways to create tables and when you should consider each one. You looked at user-defined functions, which, in SQL form, are pretty close to traditional RDBMSs.

Stored procedures are the biggest departure from what you might be accustomed to if you’re coming from a T-SQL or PL/SQL background from SQL Server or Oracle, respectively. One reason for this is the use of JavaScript to support branching and looping. I hope this chapter helped demystify some of this for you. If you “just” need to execute SQL within your stored procedure, you only need to consider using a basic JavaScript wrapper around your SQL code.

You also touched upon SQL variables and how to set and unset them. Using variables can become important when considering how to make your code portable between different environments. For example, you can use it to contain the values to source databases and schemas depending on the environment you are working within (e.g., development, test, or production).

Finally, you explored transactions. If you’re coming from a relational database world, these concepts should be familiar.

In the next chapter, you will examine what you need to do if you start to suffer performance issues in Snowflake. Sure, you can increase the sizes of your virtual warehouses, but adopting that approach isn’t always sustainable or the best way to cater to poorly written code or badly designed tables.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset