Snowflake for Data Engineers

Snowflake is a managed cloud data warehouse. As a Data Engineer you will load data into it, model it with SQL, keep queries fast and keep compute costs under control.

1. Architecture

Storage, compute and cloud services are separate layers. Data is stored once; many independent virtual warehouses query it.

Read: Architecture and virtual warehouses · Practise: Virtual warehouses

2. Loading data

Stage files in object storage and load with COPY INTO (which skips already loaded files), or use managed connectors and CDC for databases and SaaS sources. Keep raw schemas unchanged so you can rebuild.

3. Modelling with ELT

Transform with SQL in layers (staging, intermediate, marts), often managed by a tool such as dbt, with tests on every model.

Read: ETL vs ELT · dbt cheat sheet · Star schema

4. Performance

Queries are fast when they prune micro-partitions. Filter on raw columns, select only needed columns, load data in a sensible order, and add clustering keys only to large tables that need them.

Read: Micro-partitions, clustering and pruning · Practise: Micro-partitions and performance

5. Safety nets

Time Travel to query or restore earlier data, and zero-copy cloning for safe testing and backfills.

6. Governance

Role-based access control, masking policies for sensitive columns, and separate roles for loading, transforming and reading.

7. Cost

Auto-suspend on every warehouse, a warehouse per workload, scale out for concurrency and fix pruning before scaling up, resource monitors and regular review of expensive queries.

Choosing a platform

Compare against your workloads: Snowflake vs Databricks.

Design a full platform in the cloud data warehouse case study and revise with the Snowflake cheat sheet.