SnowFlake universe, part#1

A frequently required application in recent job-offers.

Let's kick this story in with an me info-bomb: here you find a link collection that I read through while checking the functionality of the SnowFlake environment.

• SnowFlake Brand videos from which the starter SnowFlake introductory videos,
  SnowFlake 20 min explanation (text)
• Snowpark Developer Guide for Python and
• Snowpark Library for Python API Reference,
• Data visualization in Snowflake // SnowSight video from Michael Kahan.

---

Further posts in Snowflake topic
SnowFlake, part#2 SnowPark Notebook
SnowFlake, part#3 Initializing the system
SnowFlake, part#4 using 3rd party Python 
SnowFlake, part#5 ML - forecasting
Part#6 ML Forecast model (2)

---

As far as I checked the requested software skill lists in job offers on several web platforms, SnowFlake is more and more present in those. I became interested in this service and at first sight it seemed to me highly clean, well-organised and user friendly web-service. 

„A PLATFORM LIKE NO OTHER
Snowflake’s single platform eliminates data silos and simplifies architectures,
so you can get more value from your data.”

The motto and the short description suggests that this platform simplifies working with data starting from data loading and storing, through data handling and modification to demonstrations. Simple graphs as line or bar chart are optional output formats, however heat map is also an option but does not have the shiniest graphical representation. On the other hand this is not a problem as SnowFlake mainly serves as a background datastore for BI tools for example Power BI or Tableau. Additionally such full-service (or software as a service) platforms as Microsoft Fabric may also use SnowFlake as data source in its complex system.

SnowFlake is a cloud-based data storage and processing platform designed to manage and analyse large volumes of data. Although it is not the market leader, Snowflake is easily comparable to other similar applications, i.e. cloud-based data management and data analytics data warehouses, such as Amazon Redshift, Google BigQuery, and Microsoft Azure Synapse Analytics (formerly Azure SQL Data Warehouse) and ranks quite well in the comparison. 

On the very first sight it is clearly visible that the developers of SnowFlake had an intention to create a clear and user friendly outlook and they really did an amazing job.

SnowFlake’s services, beside data storing include Python and/or SQL based data manipulation both of which can be used in the platforms’ Notebooks. AI & ML applications can be also easily developed on the Cloud platform. It offers customable resources as storage volume, computational power, numbers of users, projects… in one word a scalable engine. Easy integration of 20+ external resources so other platforms can serve as data storage in the background or may be chosen as options for migrating data to other platforms and get further benefits from involving other services. Scalable, customizable and a real „pay what you use” type of system.

Advantages of SnowFlake

Scalability and performance

   - Snowflake uses a unique, distributed architecture, which means that compute and storage resources can be scaled completely separately, making the data warehouse more cost-effective to run. In similar applications, compute and storage are not separated to this extent, which sometimes limits the flexibility of scalability.

   - With auto-scaling, Snowflake can automatically scale its resources based on workload, enabling high-performance data analysis even under varying workloads.

Simple use and administration

 - Snowflake users do not need to manage separate servers or infrastructure, so the administration of data management is much simpler than at some other platforms.

 - It provides easy integration with other cloud services, which simplifies the consistent storage and access/management of different data. Snowflake supports multiple cloud platforms, such as AWS, Google Cloud, and Azure, giving users the freedom to choose between infrastructures and making it easier to build a multi-cloud strategy.

Data sharing and security

 - Snowflake's innovative data sharing capabilities enable secure and real-time data sharing with other Snowflake users without the need to create data copies. This makes data sharing more efficient and reduces problems related to managing redundant data.

Deficiencies

Costs

- Although flexible and cost-effective in terms of scalability, in some cases long-term costs may be higher. With some providers, large enterprise discounts may be more cost effective.

Limitations of integrations and tools

- Although well integrated with major cloud providers, the range of data integration and ETL tools may seem limited compared to some other platforms.

SQL support and custom functionality

- Snowflake has the SnowSQL engine that does not support all special statements outside the SQL standard. This may be a slight disadvantage for those who want to use the custom SQL syntax that is common in other SQL engines. But of course, this can also be seen as a standards-compliant approach.

Dependence on cloud providers

 - Although it supports several cloud providers, being a fully cloud-based solution it is not available as a locally deployed version, which some companies prefer for security and privacy reasons.

If you got interested, see pricing or how to become a pro (unfortunately, I have no interest in sharing the links).

Updates

On 01 Dec 2024

How should I have started using and practicing?

My free trial period is over since a week, but it came to my knowledge - when I arranged learned things and these posts - that there are free online discussions organized by Snowflake quite often that may newbies in starting the excursion.

For example I would have watched these:

"ZERO TO SNOWFLAKE IN 90 MINUTES - Virtual hands-on-lab"

or

"SNOWFLAKE DISCOVER - Build the Right Data Foundation to Maximise Your Potential"

Check upcoming events on Virtual hands-on-lab WORKSHOP SERIES website.

As those are ahead of me, I will still attend to get a deeper understanding of the power and functionality of the services.

  

---

Continue with 

Part#2 Snowpark, the Notebook of SnowFlake 

Part#3 initializing SnowFlake environment

Part#4 using 3rd party Python 
Part#5 ML - forecasting
Part#6 ML Forecast model

Interactive Jupyter Notebook

How to create interactive Jupyter Notebook or JupyterLab plots.

Note1: related Notebook for testing: ipynb, html, pdf or GitHub (Notes2)

Note2: this post is a reflection to my 3D plotter GUI post

If a 3 dimensional (scatter) plot can be converted to a rotatable plot that enormously increases the understanding of the plotted dataset. When zooming / rescaling and saving options are added to the functions that is another positive advantage compared to simple plots achievable using "simple" matplotlib plots.

import matplotlib.pyplot as plt
%maptlotlib inline

The second code line is an iPython magic command, which is the first step in having plots at all in Notebooks. The inline flag allows notebook to display the generated plot in the Notebook, below the ending code.

# displaying the plot 
plt.show()

Note that in such situation matplotlib module creates static images of the 3D dataset at a specific angle that offers a one-angle viewpoint highly limiting proper understanding of the plotted data (see image above on the right).

Interactive plots in Notebooks

There are different ways to get an interactive plot. For example one of these code lines should replace the "%matplotlib inline"

%matplotlib notebook
%matplotlib widget
%matplotlib inline

widget reference

The result:

 

What if Notebook does not plot the data only creates the interactive part of the plot? With white or the 3D scale background?

 

These mean that matplotlib and related modules are not properly installed or enabled in the Notebook application. In my case JupyterLab and Jupyter Notebook are installed as a part of the Anaconda Python package.

Install Node.Js

If Node.Js is not installed yet... it is required to build the Notebook (JupyterLab) widgets extension package. Interactivity requires javascript functionality.

conda install nodejs

You could use -y flag which sets 'yes' for all questions. It is generally not recommended, because it means that either you are aware of possible consequences and then you don't do it, or you just do not care about what happens during installation, which is a dangerous attitude. :)

Upgrade JupyterLab

There may be several reasons for this faulty function, but the most probable is that the installed modules are outdated. It seems that Jupyter notebook version below 8.x can show this buggy behaviour.

conda install --upgrade jupyterlab

The above command run in Anaconda prompt window upgrades your local JupyterLab module.

It is important to note that on every modification the Jupyter server should be restarted, better to run updates/upgrades or additional module installation after shutting down the open Notebook application (and the running server in the background).

Install and allow Notebook extensions

Now you may fall on an error indicating that some Notebook extension ('nbextension') is not working properly. In this case run

conda install -c conda-forge jupyter_contrib_nbextensions
jupyter contrib nbextension install --user

These install the nbextension package as a Notebook server extension first then the second line copies the installed javascript and css files and edits some Jupyter config files for proper functionality.

To enable the extensions run

jupyter nbextension enable --py widgetsnbextension

Adding Matplotlib to Jupyter services

If from the beginning there was no matplotlib module installed for the Jupyter services then

jupyter labextension install jupyter-matplotlib
jupyter labextension install @jupyter-widgets/jupyterlab-manager

commands will allow you to use matplotlib for plotting.

IPYMPL module requested

It may happen that running the plotting code block (cell) in the Notebook returns with an ipympl error. In such case run (as defined in conda install) the i-python-matplotlib installer

conda install conda-forge::ipympl

IPYWIDGETS module requested

install -U ipywidgets

-U flag stands for upgrade (only), in case there was no module preinstalled remove the flag. for complete install

The 3D bar plot code

original code source, modified to use ipympl extension

# creating 3d bar plot using matplotlib  
# in python 
  
# to interact with plot 
%matplotlib ipympl
  
# importing required libraries 
from mpl_toolkits.mplot3d import Axes3D 
import matplotlib.pyplot as plt 
import numpy as np 
  
# creating random dataset 
xs = [2, 3, 4, 5, 1, 6, 2, 1, 7, 2] 
ys = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] 
zs = np.zeros(10) 
dx = np.ones(10) 
dy = np.ones(10) 
dz = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] 
  
# creating figure 
figg = plt.figure() 
ax = figg.add_subplot(111, projection='3d') 
  
# creating the plot 
plot_geeks = ax.bar3d(xs, ys, zs, dx,  
                      dy, dz, color='blue') 
  
# setting title and labels 
ax.set_title("3D bar plot") 
ax.set_xlabel('x-axis') 
ax.set_ylabel('y-axis') 
ax.set_zlabel('z-axis') 
  
# displaying the plot 
plt.show()
  

---

Related sites visited while the final solution was reached: https://stackoverflow.com/questions/49647705/jupyter-nbextensions-does-not-appear
https://stackoverflow.com/questions/65357800/i-am-using-matplotlib-widget-but-it-is-not-printing-plots-in-the-output
https://stackoverflow.com/questions/73715821/jupyter-lab-issue-displaying-widgets-javascript-error
https://stackoverflow.com/questions/49647705/jupyter-nbextensions-does-not-appear
https://github.com/jupyterlab/jupyterlab/issues/14270

Animated gifs created with
https://ezgif.com/video-to-gif
https://cloudconvert.com/mp4-to-gif

Interactive 3D scatter plot (GUI)

A 3-dimensional data plotting software for demonstrational purposes. 

Note1: GITHUB 3Dplot repository

Note2: Interactive Notebook bug fix post (8 Sep 2024)

The original idea came from a clustered data plot in which a part of the full dataset should have been shown only and verified from different angles. This is not doable with a simple Matplotlib plot which creates a still image from a specific angle of view. There is no option to zoom in or to rotate. There are solutions online for interactive plotting with Python line magic (link1, link2, link3) or ipympl (link1, link2, link3) Python widgets involved. I had no success with Anaconda Jupyter notebook (7.0) or JupyterLab (4.0) as applying the widgets I could not get a properly working plot.
This triggered me to create a simple code of a graphical software in which the dataset can be visually reorientated to be able to see the points from any angle of interest. Also, a restricting the plot view to defined parts of the dataset was planned and a simple solution was created.

The software is plotting 3 dimensional (X-Y-Z) data using matplotlib 3D scatter plot. Two datasets are available on pressing the corresponding button:

1) randomized data in the range of 0-100 in all axis, which varies on each button pressing; [Test Random] button

2) iris (flower) dataset from Python scikitLearn package; [Test Iris] button

With the help of this (fixed window size) GUI 3 dimansional data can be (scatter) plotted and rearranged 

- by limiting the range along one or all axis,
- by rotating the plot with mouse gestures.

Key Features

1)  Buttons

A) The Test Random and Test Iris buttons load the corresponding dataset and plot it.

B) Apply Button
when clicked, it retrieves the values from the editable fields (see below) and applies them to the plot, updating the axis limits.

No field should remain empty!

C) Reset Button
the axis limits are reset to their optimal values (which are the minimum and maximum of the actual data points along each corresponding axis).

This ensures that all points become visible. Use this button if any plot axis range has been changed previously.

D) Clear button
all loaded data is cleared from the plot.

2) Editable Fields

Each axis (X, Y, Z) has a pair of fields for the minimum and maximum values to limit the related axis to a certain range.

These fields are editable, allowing users to input custom values and consequently exclude some parts of the full dataset from the plot.

Push "Apply" button after limits have been modified.

Rescale data (gif, download)

3) Title (Label) a text indicating the title of the current plot.

Initialization and run

On startup, the editable fields are filled in with the optimal limits (i.e., the min and max of the loaded data values).

Calculation of axis limits: The get_optimal_limits method in the Plot3DWidget class calculates the min and max values for each axis, which are initially populated in the editable fields.

Dynamic plot updates: The set_axes_limits method allows dynamic updates to the axis limits based on user input from the editable fields.

Updating the Title (Label):

Whenever plot_random_data or plot_clusters_data is called ("TEST random", "TEST iris" buttons), a (QT Event) signal is emitted that updates the text in the MainWindow label.

Prerequisites: Python modules required
- sys (python default)
- random (python default)
- PyQt5
- matplotlib, mpl_toolkits
- numpy
- sklearn

Notes: Possible functionality developments

- resizable window (fixed size at the moment, the current version is a proof of concept only)
- editable fields value check, user defined number should fall in the min-max range
- determining clusters for the Iris data using SciKitLearn
- UX design development (e.g. inactive/active buttons and editable fields)

---

Created on Thu Sep 5 20:58:00 2024
Animated gif images created from mp4 https://ezgif.com/

Style guides and cheat sheets

I do not like this English term "cheat sheet" but what can I do, I call those excerpts for myself. 

I do not consider as cheating the use of a clean knowledge extract while working, except in such a situation when it is not allowed, for example on a final / entrance exam. Nowadays the technical knowledge of employees who use computers for a significant part of their worktime to solve their tasks, like IT people / Data Scientist / Data engineers / ... is so wide and deep that I believe no one can have it all in mind. 

Python

Different DataCamp Python CSs

Pandas

Pandas module's own CS

Datacamp Pandas CS 

Jupyter Notebook 

Jupyter style guide

Jupyter overall guide

Codecademy

GitHub

General Readme styling

Customizong profile readme 

OnlineRetail - PowerBI analysis #3.2 - clustering

OnlineRetail dataset - MS Power BI, clustering (analysis #2)

Dataset reference: the analyzed dataset - mentioned on DataCamp website - was downloaded from the site of Online retail dataset sharing/owner.

In the previous two projects, I analyzed the dataset with Python and SQL with the help of the in-built visualization of the Notebook offered on DataCamp website and also using matplotlib for special purposes.

Basic analysis by Power BI of Online Retail dataset revealed simple facts. In an another additional Power BI project the frequency of well defined purchased amounts (1, 2, 3, ... pieces) versus the purchased amounts (pieces) on the horizontal axis, while different countries were presented (Frequency-Amount-Country plots).

The complete dataset and the BI solution is zipped. The package sizes 26 MB and requires installed Microsoft Power BI software (ver 2.130+, made wtih ver 2.131.1203.0).

As a simple step Microsoft (MS) Power BI software is able to load data easily from Excel files. The DataCamp project defined simple questions that could be easily answered in a short time.

Here I demonstrate a clustering visualization that cannot be created neither by DataCamp (or any) online Notebook application even if those regularly have an in-built data visualization tool, nor by offline analysis in Python using the matplotlib module. A more advanced python module is required for clustered plots.

It is easy to plot 2 datasets ("columns") on a 2D graph's X and Y axis rather the advantage of the Power BI software is adding a 3rd layer (not axis) of some additional context, strongly connected to the previous datasets. In such a case, a completely new viewpoint comes into the picture. Using another advantage of Power BI, the plotted data can be clustered in subsets based on the X-Y axis values and the clusters highlighted or specifically selected for visualization. Consequently, a confusingly large dataset can be easily analyzed.

Note: clustering by Power BI can be made in an automated or semi-automated way, where the latter means that the number of requested (provisory) clusters may be defined, but still the real data analysis is done automatically by the software (AI in the background).

At this time, the analysis of Online retail sales data was made, clustered based on key metrics such as Quantity, Total Price (Quantity*Unit price). The third layer is the Product Code. The clustered-plot as outcome offers valuable insights into Customer purchasing patterns (or Sales patterns, depending on the point-of-view). By grouping similar sales transactions together, clustering tries to reveal distinct customer behaviors or product demand trends. For instance, certain clusters may represent high-volume, low-cost purchases, indicating bulk buying of simple goods, while others might reflect high-cost, low-quantity purchases, typical of premium or otherwise specific products. Visualizing these clusters through plotted graphs helps in recognizing these patterns easily, enabling the company to tailor their marketing strategies, optimize inventory management, and enhance customer targeting for better sales performance.

Using fully automated clustering in Power BI the transaction cases were split into two domains:

This I found oversimplified as visually I could define at least one more domain, so I declared to create 3 clusters (still an automatic AI analysis runs on the data):

The following three domains were defined:

• low price, low amounts
• low price high amounts
• high price, low amounts.

I could go further with 4 clusters ... but surely make a more precise clustering another (Python or other adjustable AI) tool is required.

There are 2-3 dots above the blue lines which I would consider the member of "high price, low amounts" cluster. The decision between light or dark blue borderlines is a matter of ... the Management  of the company. The orange line's position is fine to define the edge between "low price, low amounts" and "low price, high amounts" domains.

I would split the points above 30k in Quantity into a 4th cluster, see the circled dots on the right, as those are the "low price, extremely high amounts" = bulk products, which create the lowest relative profit but require the most work if the Sales+Logistics processes are not well organized... and may turn out to be lossmaking goods. Oh yes, the most outlier in the bottom-right corner is a product for sure to be double checked from all viewpoints of Sales+Logistics and probably an item to neglect in the future!

Google track modification - python software

Modify Google Timeline/Maps track

KML editor - GITHUB

We have been on summer holidays in the mountains and as always Google (my phone) tracked our excursions. More precisely it tried to track, but in the middle of high mountains the GPS signal is not available always and leads to missing or unprecise geo-locations in the Timeline that would be neccessary for smooth and realistic path matching.

I considered to optimize some of the paths to better match the real track that we drove or hiked.

As the simplest solution the software was planned to:

1. load a kml file downloaded from my Google Map Timeline (of the selected day)
2. graphically represent the data on a 2D surface, so to plot all LineString points in a GUI window
3. the points should be movable by Drag&Drop method (left click on a selected point, holding the mouse left click pressed, dragging and moving the point... and then release the point/mouse at the required position)
4. in case any point of the track is modified then the new path should be saved
5. ... as a copy, in the simplest, but Google Map kml compatible format, not to overwrite the original data. The Google Map compatibility means that the resulting kml file should properly work when imported in a layer of Google My Maps service, meaning that the modified track should properly appear on the map.
6. no map background is required as that would highly complicate the functionality, requiring a Google Map API key and a continuous internet connection to update the map after each modification. The original concept was to modify the track (files) offline.

Simple KML editor

The code was written in Python3 language, the graphical user interface and the interactivity were made by tkinter modules, while kml file was loaded and data were extracted with fastkml module (after failing with simplekml module that is not a proper module to modify only to create kml files).

The original path on the map shows unprecise points (off-the road), not required points (driving to the shop, the right part from Unterkrimml) and big jumps in space-and-time, due to missing GPS signal (see the horizontal line from most left point to Unterkrimml).

The same curve was stretched in a fix (800x600 px) sized software window in such a way as to have a 10 pixels padding around the curve. The software window colours were not modified as prettyfying was not planned this time.

I highlighted in yellow those points that were not required/precise and which of those I modified.

Note: colors were modified only in an image editor.

This software does not allow adding or removing points and as consequence off-road points may be precised in position (see the yellow point), missing points to avoid cut off hairpin turns cannot be added, finally not required points cannot be deleted, but I grouped them at our starting point (see the blue spots):

Note: colors were modified only in an image editor.

At that point the first kml file of the modified track was not properly compatible with Google map, in the second step the above points showed up on Google My Maps as individual geo-locations, not as part of an excursion track, but in the final version a <LineString>... </LineString> tag was inserted before the <coordinates>... </coordinates> wrapper in the kml file.

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
  <Placemark> 
    <LineString>  
      <coordinates>17.2012735,47.8064775,0 </coordinates>
    </LineString>  
  </Placemark>  
</Document>  
</kml>

After modification the file was imported in a new layer of a newly created Google My Maps map:

Read more details in the KML editor - GITHUB readme. 

This is a simple aim-oriented software that can be developed to overcome the above mentioned weaknesses, such as

• removing points would be a key point and easy-to-make option,
• in contrast adding track points is a bit more tricky because the points should be added at the right place in the order of points (LineString) to avoid dizzy lines,
• highlighting a point by clicking is an easy request but I consider it does not add much to the track modification functionality, rather it serves fanciness only,
• adding real maps (with continuous updates) in the background would be the best development as a positive outcome but has a high coding time demand, but either the software should use a common Google API key that due to economical reasons Google does not offer, or each user should use their own API key which I consider to be an overcomplication of the task and functionality.

OnlineRetail dataset, Analysis#3.1 - Power BI

OnlineRetail dataset - MS Power BI, additional analysis #1

Dataset reference: the analyzed dataset - mentioned on DataCamp website - was downloaded from the site of Online retail dataset sharing/owner.

Basic analysis by Power BI of Online Retail dataset revealed simple facts.

In another two projects, I analyzed the dataset with Python and SQL with the help of the in-built visualization of the Notebook offered on DataCamp website and also using matplotlib for special purposes.

The complete dataset and the BI solution is zipped. The package sizes 26 MB and requires installed Microsoft Power BI software (ver 2.130+, made wtih ver 2.131.1203.0).

As a simple step Microsoft (MS) Power BI software is able to load data easily from Excel files. The DataCamp project defined simple questions that could be easily answered in a short time.

Here I demonstrate a visualization that cannot be created neither by DataCamp (or any) online Notebook application even if those regularly have an in-built data visualization tool, nor by offline analysis in Python using the matplotlib module.

The advantage of Power BI software is to plot 2 datasets ("columns") on a 2D graph's X and Y axis but to add a 3rd layer (not axis) of some additional information, strongly connected to the previous datasets. In such case, a completely new viewpoint comes to the picture. Using another advantage of Power BI, the plot can be made dynamic so that selecting some value of the 3rd layer subset of data can be highlighted or specifically selected for visualization and consequently a confusingly large dataset can be easily analyzed according to our needs depending on our selection.

The plot(s) below visualize the frequency of quantities of purchased product amounts with the additional information of purchaser's country. The vertical axis (Y) represents the frequency of well defined purchased amounts (1, 2, 3, ... pieces) versus the purchased amounts (pieces) on the horizontal axis, while different countries are presented with different colors (automatically set by Power BI). Note that both axis are in logarithmic representation therefore the 1-100 distance is the same as the distance of 100 - 10 000 units, but for first understanding it is not important.

Frequency of purchased quantities by Countries (default view)

This is a bit crowded therefore it would be great to have some selector with what we can define subsets in what we are interested in. Power BI has a Slicer option in which the selection categories may be loaded by a simple drag&drop method. In this case 30+ countries appear in the slicer, each countries in a separate box.

Frequency of purchased quantities - all countries plot, with an additional slicer.

In deed, the slicer takes quite a large part, but there is no option to rearrange or minimize internal margin in the boxes. Still it is a really good opportunity to select for example United Kingdom (UK) related data: 

Frequency of purchased quantities - UK

UK purchases has a well defined triangle, which seems to have a sharp lower edge. It is due to the fact thet UK customers has at least 100 times more data entries then other countries, so it is evident that the frequency values cannot take low numbers for low purchased quantities.

Let's see the non-UK purchase quantities:

Frequency of purchased quantities - non-UK countries

Note: based on these plots above the DataCamp 3rd question "is non-UK purchased amounts significantly higher than UK purchsed amounts?" cannot be precisely answered, however we may have an impression. For significant difference analyses statistical t-probe should be utilized (not presented here).

Countries of our interest can be selected for specific analyses:

Purchases as income and refunds related losses on maps

Country-related total values of purchases or returns expressed in quantities or money may be plotted using Power BI maps.

Heat map would be more useful to express the differences of countries' contribution to the whole amounts. Keeping mind that UK entries are extremely higher in the dataset compared to non-Uk countries relative numbers should be plotted. Map not presented.

Additional analysis #2 - Clustering with Power BI