This will colocate data often queried together into the same or adjacent micro-partitions. This will expedite retrieval and reduce warehouse credit consumption by being more effecient at retrieving data.
Search optimization is a premium product available with enterprise version of Snowflake and up. https://docs.snowflake.com/en/user-guide/search-optimization-service https://docs.snowflake.com/en/user-guide/search-optimization-service#label-search-optimization-maintenance-billing Search optimization expedites queries that can use exact matches in the predicates (where clause). Frequently used lookup table would be good candidates for search optimization. Search optimization is turned on at the table level. It works by pre-building, and …
copy into <table-name> from <stage-name> storage_integration = <integration-name> Here is a step by step on how to setup the storage integration object for the copy example above. Setting up the storage integration object is a pre-requisite to using the copy command above. https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration.html Another method not shown, (not as secure but works) is to use …
The major components of a modern Snowflake copy into command – best practice Read More »
To display a list of shortcut keys relevant to Windows do this: control-shift-? To display a list of shortcut keys relevant to Mac OS do this: command-shift-? The most important shortcut keystroke is to execute a selected SQL statement or group of SQL statements, and that would be; # Window snowflake execute shortcut control-enter # …
The really nice feature of Snowflake’s materialized views is that they are automatically kept in sync with their underlying SQL. Snowflake is doing this in the background for us. And even if it has not been done yet, Snowflake realizes that from you SQL query, and as quickly as possible updates the MV and uses …
Snowflake – Materialized Views – Limits and one huge advantage Read More »
alter session set use_cached_result = false;
The blog outlines one method for handling Slowly Changing Dimensions – probably the most popular method referred to as type 2. Could be applied to any data base product – but – this applies to Redshift and Snowflake and more… A type 2 slowly changing dimension enables you to track the history of updates to …
Data Warehouse design – Type 2 Slowly Changing Dimensions Read More »
// Use the table function “TASK_HISTORY()” select * from table(information_schema.task_history()) order by scheduled_time desc; // See results for a specific Task in a given time select * from table(information_schema.task_history( scheduled_time_range_start=>dateadd(‘hour’,-4,current_timestamp()), result_limit => 5, task_name=>’MYTASK’));
There are rules about the order or resumption, that make resuming parent and child task a pain, but here is a command that just resumes all tasks related to a parent. SELECT SYSTEM$TASK_DEPENDENTS_ENABLE(‘MYTASK’); Show tasks <database.schema>; show tasks analytics.dbt; BTW, you must suspend a tasks with parents/children before dropping them – the same restriction does …
Snowflake resuming all tasks including children of a parent task Read More »
Imperative (aggregated changes) versus Declarative (by declaring end state intention – e.g. Terraform Style) A couple of popular examples of Imperative Data Change Management are: Flyway – https://flywaydb.org/download schemachange – https://pypi.org/project/schemachange/ And here is an example of a popular build tool that is declarative: Terraform – https://www.terraform.io (by Hashicorp)