I can teach you Snowflake analytics! I have never seen a database do analytics better than Snowflake. This week we are working on the Lead. You use a Lead to place the value from the next row on the current line of the answer set. You can then see today’s value, and on the same line, see tomorrow’s value.
In each example, you will see an ORDER BY statement, but it will not come at the end of the query. The ORDER BY keywords is always within the Lead calculation. It is the ORDER BY statement that runs first, and once the system orders the data, the Lead will calculate. It is the initial ordering of the data set that gives these analytics the name “Ordered Analytics.” The other name is “Window Functions,” because they calculate within a certain window of rows.
Let me summarize the Lead in the picture below. Order the data first by the column Sale_Date and after the data sorts, then begin with row one and ask, “Can we get the Daily_Sales value from the next row, and add it to the current line?” If the answer is “Yes,” then do it, but if the answer is “No,” then put in a Null.
The LEAD above allows you to see the Daily_Sales for today next to tomorrow’s value.
Check out the next picture below. Notice we have two leads, and one has a lead of one row, and the other has a lead of two rows. Now, row one has the Daily_Sales for today and the Daily_Sales for the next two days.
For the Next_Value column, the moving window is one. For the Two_Days column, the moving window is two. The moving window in Lead establishes by the numbers after LEAD (Daily_Sales, 1) or LEAD(Daily_Sales,2). You can put any number (n) in the moving window, and that tells Snowflake to get the value n rows down.
Now, we are going to take the next example even farther by adding a third day to the answer set, and we are also going to add the PARTITION BY statement and change our WHERE clause. The PARTITION BY resets the calculation and acts much like a GROUP BY statement. Notice that each Product_ID calculates within the Product_ID only. When a new Product_ID appears, the calculation starts over.
Now, we are going to take the next example even farther by adding a third day to the answer set and change our WHERE clause. The PARTITION BY resets the calculation and acts much like a GROUP BY statement. Notice that each Product_ID calculates within the Product_ID only. When a new Product_ID appears, the calculation starts over.
In the picture above, notice the null values. They appear because there are no rows after available. We have run out of future row values to display. In another sense, we have all of the values needed on the first row of each Product_ID in the answer set. The first row in the answer set for each Product_ID gives us the full picture. Watch how clever this report gets when you add the QUALIFY statement. Compare the picture above with the picture below. Notice that the QUALIFY statement removes the additional rows not needed.
The QUALIFY statement is to Ordered Analytics much like a HAVING statement is to aggregation. QUALIFY is a filter that works on the calculations after they calculate.
If you want to move data from any system to Snowflake, you should use the Nexus and NexusCore Server for the data movement. You can move these systems to Snowflake:
- SQL Server
- Azure SQL Data Warehouse
Below is an example of how pretty and easy-to-use the NexusCore Server is to move data from another system to Snowflake. Below is an example of how pretty and easy-to-use the NexusCore Server is to move data from another system to Snowflake. You can run this job immediately, or you can schedule it daily, weekly, monthly, yearly, or custom.
If you want to move data to snowflake or you want to use the greatest query tool known to humankind, then use the Nexus and NexusCore Server. Download your free Nexus trial at www.CoffingDW.com or contact me for a demo.
To be able to query from every data source no matter how new is now achievable with the Nexus Universal ODBC. We are proud to offer Nexus Universal ODBC – our new connection method that allows users to connect to and interact with any current or future ODBC data source in the universe. If there is a data source you want to query, and it has an ODBC driver, you can connect and query immediately.
Check out the picture below to see the new tree with the Universal ODBC connection.
I hope you enjoyed today’s Snowflake analytic lesson. See you next week.
CEO, Coffing Data Warehousing
Direct: 513 300-0341
YouTube channel: CoffingDW