When this skill is activated, always start your first response with the 🧢 emoji.

Data Warehousing

A practical framework for designing, building, and optimizing analytical data warehouses using dimensional modeling. This skill covers star and snowflake schema design, slowly changing dimension (SCD) patterns, and platform-specific guidance for Snowflake and BigQuery. The focus is on making the right modeling decisions that balance query performance, storage cost, and maintainability for downstream analytics consumers.

When to use this skill

Trigger this skill when the user:

• Designs a star schema or snowflake schema for analytical workloads
• Implements slowly changing dimensions (Type 1, 2, 3, or hybrid)
• Builds fact tables (transactional, periodic snapshot, or accumulating snapshot)
• Writes analytical SQL targeting Snowflake or BigQuery
• Plans ETL/ELT pipelines that load data into a warehouse
• Creates conformed dimensions shared across multiple fact tables
• Optimizes warehouse query performance (clustering, partitioning, materialized views)
• Chooses between Snowflake and BigQuery for a new project

Do NOT trigger this skill for:

• OLTP schema design or transactional database tuning (use database-engineering)
• Data pipeline orchestration tools like Airflow or dbt (those have their own skills)

Key principles

1. Model for the query, not the source - Warehouse schemas exist to make analytical queries fast and intuitive. Denormalize aggressively compared to OLTP. If analysts need to join seven tables to answer a basic question, the model is wrong.

2. Grain is the single most important decision - Every fact table must have a clearly declared grain (one row = one transaction, one day per customer, etc.). Mixing grains in a single fact table causes double-counting and broken aggregations that are extremely hard to debug.

3. Conformed dimensions enable cross-process analysis - A shared

   dim_customer

   or

   dim_date

   table used across all fact tables lets analysts drill across business processes without reconciliation headaches. Build conformed dimensions first.

4. Slowly changing dimensions must be an explicit design choice - Every dimension attribute changes over time. Decide upfront whether to overwrite (Type 1), track history (Type 2), or store previous value (Type 3). Defaulting to Type 1 and later needing history is a painful migration.

5. Partition and cluster for your access patterns - Cloud warehouses charge by data scanned. Partitioning by date and clustering by high-cardinality filter columns can reduce costs and query times by 10-100x. Design these at table creation time.

Core concepts

Dimensional modeling entities

fct_orders

fct_page_views

dim_customer

dim_product

dim_date

customer_sk

customer_id

order_number

fct_order_items

dim_date

dim_geography

Star schema vs snowflake schema

A star schema has fact tables at the center with denormalized dimension tables radiating outward - one join from fact to any dimension. A snowflake schema normalizes dimensions into sub-dimensions (e.g.,

dim_product

dim_category

dim_department

Fact table types

fct_orders

fct_daily_inventory

fct_order_fulfillment

fct_student_attendance

Slowly changing dimensions (SCD)

Common tasks

Design a star schema

Model a retail sales domain with conformed dimensions.

-- Date dimension (conformed - used by all fact tables)
CREATE TABLE dim_date (
  date_sk       INT         PRIMARY KEY,  -- YYYYMMDD integer
  full_date     DATE        NOT NULL,
  day_of_week   VARCHAR(10) NOT NULL,
  month_name    VARCHAR(10) NOT NULL,
  quarter       INT         NOT NULL,
  fiscal_year   INT         NOT NULL,
  is_weekend    BOOLEAN     NOT NULL,
  is_holiday    BOOLEAN     NOT NULL
);

-- Customer dimension
CREATE TABLE dim_customer (
  customer_sk   INT         PRIMARY KEY,  -- surrogate key
  customer_id   VARCHAR(50) NOT NULL,     -- natural key
  name          VARCHAR(200),
  segment       VARCHAR(50),
  region        VARCHAR(100),
  -- SCD Type 2 tracking
  effective_date DATE       NOT NULL,
  expiry_date    DATE       NOT NULL DEFAULT '9999-12-31',
  is_current     BOOLEAN    NOT NULL DEFAULT TRUE
);

-- Product dimension
CREATE TABLE dim_product (
  product_sk    INT         PRIMARY KEY,
  product_id    VARCHAR(50) NOT NULL,
  product_name  VARCHAR(200),
  category      VARCHAR(100),
  subcategory   VARCHAR(100),
  brand         VARCHAR(100),
  unit_cost     DECIMAL(12,2)
);

-- Sales fact table (transaction grain: one row per line item)
CREATE TABLE fct_sales (
  sale_sk       BIGINT      PRIMARY KEY,
  date_sk       INT         NOT NULL REFERENCES dim_date(date_sk),
  customer_sk   INT         NOT NULL REFERENCES dim_customer(customer_sk),
  product_sk    INT         NOT NULL REFERENCES dim_product(product_sk),
  quantity      INT         NOT NULL,
  unit_price    DECIMAL(12,2) NOT NULL,
  discount_amt  DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_amount    DECIMAL(12,2) NOT NULL,
  order_number  VARCHAR(50) NOT NULL  -- degenerate dimension
);

Declare the grain explicitly in a comment or documentation: "One row per order line item per day." Every team member must agree on the grain before building downstream reports.

Implement SCD Type 2 in Snowflake

Track full history of customer attribute changes using MERGE.

-- Snowflake MERGE for SCD Type 2
MERGE INTO dim_customer AS target
USING staging_customers AS source
  ON target.customer_id = source.customer_id
     AND target.is_current = TRUE

-- Existing row where attributes changed: expire it
WHEN MATCHED
  AND (target.segment  != source.segment
    OR target.region   != source.region)
THEN UPDATE SET
  target.expiry_date = CURRENT_DATE - 1,
  target.is_current  = FALSE

-- No match: brand new customer
WHEN NOT MATCHED THEN INSERT (
  customer_sk, customer_id, name, segment, region,
  effective_date, expiry_date, is_current
) VALUES (
  dim_customer_seq.NEXTVAL,
  source.customer_id, source.name, source.segment, source.region,
  CURRENT_DATE, '9999-12-31', TRUE
);

-- Second pass: insert the new current row for changed records
INSERT INTO dim_customer
SELECT dim_customer_seq.NEXTVAL,
       s.customer_id, s.name, s.segment, s.region,
       CURRENT_DATE, '9999-12-31', TRUE
FROM staging_customers s
JOIN dim_customer d
  ON s.customer_id = d.customer_id
  AND d.expiry_date = CURRENT_DATE - 1
  AND d.is_current = FALSE;

Implement SCD Type 2 in BigQuery

BigQuery lacks MERGE with multiple actions on the same row, so use a MERGE + INSERT pattern.

-- BigQuery SCD Type 2 using MERGE
MERGE `project.dataset.dim_customer` AS target
USING `project.dataset.staging_customers` AS source
  ON target.customer_id = source.customer_id
     AND target.is_current = TRUE

WHEN MATCHED
  AND (target.segment != source.segment
    OR target.region  != source.region)
THEN UPDATE SET
  expiry_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),
  is_current  = FALSE

WHEN NOT MATCHED BY TARGET THEN INSERT (
  customer_sk, customer_id, name, segment, region,
  effective_date, expiry_date, is_current
) VALUES (
  GENERATE_UUID(),
  source.customer_id, source.name, source.segment, source.region,
  CURRENT_DATE(), DATE '9999-12-31', TRUE
);

-- Insert new current rows for changed records
INSERT INTO `project.dataset.dim_customer`
SELECT GENERATE_UUID(), s.customer_id, s.name, s.segment, s.region,
       CURRENT_DATE(), DATE '9999-12-31', TRUE
FROM `project.dataset.staging_customers` s
INNER JOIN `project.dataset.dim_customer` d
  ON s.customer_id = d.customer_id
  AND d.expiry_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Optimize Snowflake performance

-- Cluster keys for large fact tables (order matters: most filtered first)
ALTER TABLE fct_sales CLUSTER BY (date_sk, customer_sk);

-- Monitor clustering depth
SELECT SYSTEM$CLUSTERING_DEPTH('fct_sales');
-- Values close to 1.0 = well clustered; > 5 = recluster needed

-- Use result caching and warehouse sizing
ALTER WAREHOUSE analytics_wh SET
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- Materialized views for expensive aggregations
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_sk,
       SUM(net_amount) AS total_sales,
       COUNT(*)        AS transaction_count
FROM fct_sales
GROUP BY date_sk;

Snowflake charges per second of compute. Use

AUTO_SUSPEND = 60

to avoid paying for idle warehouses. Separate warehouses for ETL and analytics to prevent contention.

Optimize BigQuery performance

-- Partition by date (reduces bytes scanned = lower cost)
CREATE TABLE `project.dataset.fct_sales`
(
  sale_sk       STRING      NOT NULL,
  sale_date     DATE        NOT NULL,
  customer_sk   STRING      NOT NULL,
  product_sk    STRING      NOT NULL,
  quantity      INT64       NOT NULL,
  net_amount    NUMERIC     NOT NULL
)
PARTITION BY sale_date
CLUSTER BY customer_sk, product_sk;

-- Check bytes scanned before running expensive queries
-- Use dry run: bq query --dry_run --use_legacy_sql=false 'SELECT ...'

-- Materialized view with automatic refresh
CREATE MATERIALIZED VIEW `project.dataset.mv_daily_sales`
AS
SELECT sale_date,
       SUM(net_amount) AS total_sales,
       COUNT(*)        AS transaction_count
FROM `project.dataset.fct_sales`
GROUP BY sale_date;

BigQuery charges per TB scanned. Always partition by the primary date filter column and cluster by up to four frequently filtered columns. Use

INFORMATION_SCHEMA.JOBS

to monitor cost per query.

Build a date dimension

Every warehouse needs a pre-populated date dimension. Generate it once.

-- BigQuery: generate a date spine
CREATE TABLE `project.dataset.dim_date` AS
WITH date_spine AS (
  SELECT date
  FROM UNNEST(
    GENERATE_DATE_ARRAY('2020-01-01', '2030-12-31', INTERVAL 1 DAY)
  ) AS date
)
SELECT
  CAST(FORMAT_DATE('%Y%m%d', date) AS INT64) AS date_sk,
  date                                        AS full_date,
  FORMAT_DATE('%A', date)                     AS day_of_week,
  FORMAT_DATE('%B', date)                     AS month_name,
  EXTRACT(QUARTER FROM date)                  AS quarter,
  EXTRACT(YEAR FROM date)                     AS fiscal_year,
  EXTRACT(DAYOFWEEK FROM date) IN (1, 7)      AS is_weekend
FROM date_spine;

Anti-patterns / common mistakes

dim_date

Gotchas

1. Mixed grain in a fact table is the hardest bug to find - Adding a row-level sale and a daily summary row into the same fact table looks harmless until someone sums

   net_amount

   across a date range and gets 2x the correct revenue. Grain violations produce numerically plausible but wrong answers. Declare the grain in a comment on the

   CREATE TABLE

   statement and enforce it with a uniqueness test.

2. SCD Type 2 MERGE leaving orphaned "current" rows - A MERGE statement that expires old rows but fails (network timeout, transaction rollback) after the UPDATE but before the INSERT leaves the dimension in an inconsistent state: the old row is expired but no new current row exists. Always wrap SCD Type 2 updates in a transaction and verify row count after each MERGE + INSERT pair.

3. Natural keys as fact table foreign keys break on source changes - Using

   customer_email

   instead of

   customer_sk

   as the foreign key on a fact table means a customer who changes their email address breaks all historical joins. Surrogate keys are immune to natural key changes. Always generate surrogate keys for dimension foreign key references.

4. BigQuery partition filter not used in JOIN conditions - BigQuery only prunes partitions when the partition column appears in a

   WHERE

   clause, not in a

   JOIN ON

   condition. A query joining a partitioned table only on

   customer_id

   performs a full table scan on the partitioned table, negating the cost benefit. Always include the partition column in the

   WHERE

   clause.

5. Snowflake warehouse not auto-suspending in development - A development or analytics warehouse left running with

   AUTO_SUSPEND

   disabled (or set to a long interval) accumulates compute costs continuously. Set

   AUTO_SUSPEND = 60

   on all non-production warehouses, and use separate warehouses for ETL loads vs. analyst queries to prevent compute contention.

References

For detailed implementation patterns and platform-specific guidance, load the relevant file from

references/

• references/snowflake-patterns.md

  - Snowflake-specific features: stages, streams, tasks, time travel, zero-copy cloning, and warehouse sizing strategies

• references/bigquery-patterns.md

  - BigQuery-specific features: nested/repeated fields, federated queries, BI Engine, slots vs on-demand pricing, and scheduled queries

• references/scd-patterns.md

  - Deep dive on all SCD types with complete SQL implementations, hybrid patterns, and migration strategies between types

Only load a references file if the current task requires it - they are long and will consume context.

Companion check

On first activation of this skill in a conversation: check which companion skills are installed by running

ls ~/.claude/skills/ ~/.agent/skills/ ~/.agents/skills/ .claude/skills/ .agent/skills/ .agents/skills/ 2>/dev/null

. Compare the results against the

recommended_skills

field in this file's frontmatter. For any that are missing, mention them once and offer to install:

npx skills add AbsolutelySkilled/AbsolutelySkilled --skill <name>

Skip entirely if

recommended_skills

is empty or all companions are already installed.