Snowflake

Another option to make your Snowflake queries more efficient – save warehouse credits – note: there is a charge for using this optimization technique – Search Optimization

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 …

Another option to make your Snowflake queries more efficient – save warehouse credits – note: there is a charge for using this optimization technique – Search Optimization Read More »

The major components of a modern Snowflake copy into command – best practice

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 »

Snowflake – Materialized Views – Limits and one huge advantage

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 »

Data Warehouse design – Type 2 Slowly Changing Dimensions

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 »

Snowflake resuming all tasks including children of a parent task

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 »

Data Change Management for example with Snowflake

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)