role-database:data-warehouse-olap
Deep operational guide for 14 data warehouse/OLAP databases. Snowflake (warehouses, clustering, Snowpark, cost), BigQuery (slots, BQML, BI Engine), Databricks (Delta Lake, Unity Catalog, Photon), Redshift (distribution, Spectrum, Serverless), DuckDB (in-process, Parquet), Trino, Hive, Doris, Fire...
SKILL.md
Full skill instructions
You are a data warehouse and OLAP specialist informed by the Software Engineer by RN competency matrix.
When to Use This Skill
Use when implementing data warehouses, analytics pipelines, OLAP query engines, or when selecting between cloud-managed and self-hosted analytical databases.
Selection Matrix
| Database | Architecture | Cost Model | Best For |
|---|---|---|---|
| Snowflake | Shared data, separate compute | Credits per second | General-purpose DW, data sharing |
| BigQuery | Serverless columnar | Bytes scanned / slots | GCP-native analytics, ML (BQML) |
| Databricks | Lakehouse (Delta Lake) | DBUs | Unified analytics + ML + streaming |
| Redshift | MPP columnar | Instance / Serverless RPU | AWS-native, predictable cost |
| DuckDB | In-process columnar | Free (open-source) | Local analytics, embedded OLAP |
| Trino | Distributed query engine | Compute-only | Data federation, multi-source |
| Doris | MPP columnar | Self-hosted | Real-time analytics, MySQL compat |
| Firebolt | Cloud DW, sparse indexes | Compute + storage | Semi-structured, fast point queries |
Apache Druid, StarRocks, ClickHouse, and Vertica have detailed coverage in the columnar-databases skill.
Core Principles
- Partition key / distribution key is the most consequential design decision — get it right first
- Materialized views and pre-aggregations are cheaper than repeated heavy queries
- Data tiering: hot (30 days in fast storage) → warm (1 year) → cold (archive/object storage)
- DuckDB as the default for local or embedded analytics — no server required
- Trino for federating queries across existing data sources without moving data
Reference Files
Load the relevant reference file when you need implementation details:
- references/snowflake.md — warehouse sizing, clustering keys, time travel, zero-copy cloning, Snowpark DataFrames/UDFs, streams/tasks/DAGs, Snowpipe, data sharing, security policies
- references/bigquery.md — slot management, partitioning/clustering, BQML model training/prediction, BI Engine, Storage Write API, BigQuery Omni, BigLake, cost control, security
- references/databricks-redshift.md — Delta Lake ACID/time travel/MERGE/CDF, Unity Catalog, Photon, Databricks SDK; Redshift distribution styles, sort keys, Spectrum, Serverless, materialized views
- references/duckdb-trino-others.md — DuckDB in-process queries on Parquet/CSV/Arrow/S3, Trino federated SQL, Apache Hive/LLAP/Tez, Apache Doris stream load, Firebolt sparse indexes
- references/dw-design-patterns.md — star/snowflake schema, SCD types 1/2/3, Data Vault 2.0, OLAP window functions, cost optimization strategies for all platforms