In Snowflake there are useful commands like SHOW USERS (shows the users in your environment) or SHOW GRANTS TO ROLE (shows the privileges given to a certain role). The results of these commands are shown like tables however, you cannot filter them like SHOW USERS WHERE … You can however use the commands TABLE and…
“THE DATABASE MOST USED IN YOUR QUERIES WILL BE DELETED!” “But don’t you worry pal, we are gonna migrate the tables and views you use to another database!” “If you could just give me a list of the objects you use in the soon-to-be-history-database, that would be great.” Migrations are a common animal in any…
Want to append the data of several files into ONE table? You could do this using Power BI Dataflows. However, you can also do this in Snowflake using the COPY INTO command. Imagine you have an AWS S3 bucket with a folder containing a lot of files that need to be appended into a single…
Do you want to load data from AWS S3 bucket to Snowflake? Setting up access to an S3 bucket from Snowflake can be done in 5 simple steps. Want more details? Check out the link at the bottom for a detailed description of each step. Step 1: Create IAM policy in AWS that allows a…
OLAP (Online Analytical Processing) en OLTP (Online Transactional Processing) zijn twee verschillende soorten databases met elk hun eigen doel. OLAP-databases zijn bedoeld voor ingewikkelde vragen en data-analyse. Ze worden vaak gebruikt voor bedrijfsrapportages en slimme inzichten. In een OLAP-database lees je vaak veel gegevens en het is belangrijk dat dit snel gebeurt want niemand houdt…
Clustering depth indicates if micro-partitions are overlapping with one another. Ideally, micro-partitions are not overlapping and are contiguous. Being contiguous means that one micro-partition seamlessly connects with another micro-partition. Sometimes however, micro-partitions are overlapping. A reason for this could be a less than optimal clustering key. Whatever the reason, this is not ideal, because now…
Tables are stored within Snowflake as micro-partitions. Put simply, micro-partitions are basically horizontal slices of the table. These slices divide the table into subsets of rows. This process is called “clustering”. Clustering is done to avoid unnecessarily reading micro-partitions that are not needed for the query. This leads to more efficient, faster and cheaper results.…
A subquery is a query that is used in another query. Another example of subqueries can be as simple as the example below. A correlated subquery is a subquery where a reference is made to the outer query. An uncorrelated subquery is a subquery where no reference is made to the outer query. For the…
Imagine that you want to load semi-structured data in the form of JSON into Snowflake. If the JSON contains an outer array and if you did not strip it, the entire dataset would be loaded into a single row in the target table. Sometimes that is acceptable and you can process the data in a…
Tasks are an excellent scheduling tool in Snowflake. You can use tasks for a lot of different purposes. Below are some examples: The scheduling can be done using CRON (for example, run a task every hour, minimum schedule is one minute) or using a trigger. You could trigger a task to run after another task…
