菜单
Data Warehouse: modélisation dimensionnelle (Kimball/Inmon/Data Vault), star schema, Snowflake, BigQuery, Redshift, SQL analytique avancé, optimisation, SCD types.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/JohnNuwan/EVA_CORE --skill data-warehouse命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
Concevoir et maintenir un watchdog auto-correcteur pour services HTTP — checks de santé, auto-restart, état persistant, rapports et pièges bash.
Serveur de messagerie sécurisé auto-hébergé (Signal-like) avec Flask + WebSocket + AES-256-GCM + pont EVA
ADAM-SENTINEL — Veilleur technologique 24h/24h. Scanne 10 domaines, cree des rapports, met a jour les skills, alerte sur les CVE et breaking changes.
基于 SOC 职业分类
正在显示 SKILL.md
| name | data-warehouse |
| description | Data Warehouse: modélisation dimensionnelle (Kimball/Inmon/Data Vault), star schema, Snowflake, BigQuery, Redshift, SQL analytique avancé, optimisation, SCD types. |
| version | 1.0.0 |
| author | EVA & The Hive |
| license | Privée EVA St-Étienne |
| platforms | ["linux","macos","windows"] |
| metadata | {"EVA":{"tags":["data-warehouse","star-schema","snowflake-schema","kimball","inmon","data-vault","snowflake","bigquery","redshift","scd","olap","mpp"],"homepage":"https://www.snowflake.com/","related_skills":["data-pipelines","data-lakes-lakehouse","dbt-analytics","data-quality-testing","airflow-orchestration"]}} |
| prerequisites | {"commands":["python3","pip"],"pip_packages":["dbt-core","dbt-snowflake","dbt-bigquery","dbt-postgres","sqlalchemy","psycopg2-binary","snowflake-connector-python","google-cloud-bigquery"]} |
Le Data Warehouse est le socle de la Business Intelligence et de l'analyse décisionnelle. Cette compétence couvre :
Activez cette compétence lorsque l'utilisateur :
-- TABLE DE FAITS : mesures quantitatives
CREATE TABLE fact_sensor_readings (
reading_sk BIGINT IDENTITY PRIMARY KEY, -- Surrogate key
machine_sk INT NOT NULL, -- FK vers dimension
date_sk INT NOT NULL, -- FK vers dimension temps
time_sk INT NOT NULL, -- FK vers dimension heure
temperature DECIMAL(6,2),
pression DECIMAL(6,2),
vibration (,),
cycle_time ,
created_at
);
dim_machine (
machine_sk ,
machine_id () ,
machine_type (),
site (),
zone (),
install_date ,
status (),
valid_from ,
valid_to ,
is_current
);
dim_date (
date_sk ,
,
,
quarter ,
,
month_name (),
week ,
day_of_week ,
is_weekend ,
is_holiday ,
fiscal_year ,
fiscal_quarter ,
()
);
dim_time (
time_sk ,
heure ,
,
hour_minute (),
shift (),
(heure, )
);
-- Analyse OEE par site et par mois
SELECT
m.site,
d.year,
d.month,
COUNT(DISTINCT f.machine_sk) AS machines_actives,
AVG(f.temperature) AS temp_moyenne,
MAX(f.temperature) AS temp_max,
COUNT(*) AS nb_releves
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
JOIN dim_date d ON f.date_sk = d.date_sk
WHERE m.is_current = TRUE
AND d.year = 2026
GROUP BY m.site, d.year, d.month
ORDER BY m.site, d.year, d.month;
| Type | Comportement | Usage |
|---|---|---|
| SCD 0 | Rétroactif fixe | Colonnes qui ne changent jamais |
| SCD 1 | Écrasement | Correction d'erreur, pas d'historique |
| SCD 2 | Nouvelle version | Historique complet (le plus courant) |
| SCD 3 | Colonne précédente | Besoin de l'ancienne valeur uniquement |
| SCD 6 | Hybride 1+2+3 | Tracking + current value |
-- Mise à jour SCD Type 2
MERGE INTO dim_machine AS target
USING (
SELECT machine_id, machine_type, site, zone
FROM staging_machine_updates
) AS source
ON target.machine_id = source.machine_id AND target.is_current = TRUE
-- Si les données ont changé : fermer l'ancienne version
WHEN MATCHED AND (
target.machine_type <> source.machine_type OR
target.site <> source.site OR
target.zone <> source.zone
) THEN UPDATE SET
valid_to = CURRENT_DATE,
is_current = FALSE
-- Nouvelle version
INSERT (machine_id, machine_type, site, zone, valid_from, valid_to, is_current)
VALUES (source.machine_id, source.machine_type, source.site, source.zone,
CURRENT_DATE, NULL, TRUE);
-- État des machines à une date donnée
SELECT f.*, m.machine_type, m.site, m.zone
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
WHERE m.valid_from <= '2026-06-15'
AND (m.valid_to IS NULL OR m.valid_to > '2026-06-15');
-- Rang et comparaison temporelle
SELECT
machine_id,
date,
temperature,
AVG(temperature) OVER (
PARTITION BY machine_id
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moyenne_7j,
temperature - LAG(temperature, 1) OVER (
PARTITION BY machine_id ORDER BY date
) AS variation_jour,
RANK() OVER (
PARTITION BY machine_id
ORDER BY temperature DESC
) AS rang_chaud
FROM sensor_readings;
-- Cumul mensuel
SELECT
site,
date,
nb_releves,
SUM(nb_releves) OVER (
PARTITION BY site, EXTRACT(YEAR_MONTH FROM date)
ORDER BY date
) AS cumul_mensuel
FROM daily_agg;
-- ROLLUP : hiérarchie (site → zone → machine)
SELECT
site, zone, machine_id,
AVG(temperature) AS temp_moyenne,
GROUPING(site) AS is_site_total,
GROUPING(zone) AS is_zone_total
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
GROUP BY ROLLUP(site, zone, machine_id);
-- CUBE : toutes les combinaisons
SELECT site, zone, machine_id, AVG(temperature)
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
GROUP BY CUBE(site, zone, machine_id);
-- GROUPING SETS : combinaisons spécifiques
SELECT site, zone, machine_id, AVG(temperature)
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
GROUP BY GROUPING SETS (
(site, zone),
(site),
(zone),
()
);
-- PIVOT : transformer des lignes en colonnes
SELECT *
FROM (
SELECT machine_type, month, temperature
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
JOIN dim_date d ON f.date_sk = d.date_sk
WHERE d.year = 2026
)
PIVOT (
AVG(temperature)
FOR month IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
) AS p;
-- UNPIVOT (BigQuery) : colonnes → lignes
SELECT machine_id, month, avg_temp
FROM `project.dataset.kpi_table`
UNPIVOT (
avg_temp FOR month IN (jan, feb, mar, apr)
);
-- HUB : clés d'entreprise (business keys)
CREATE TABLE hub_machine (
machine_hk CHAR(32) PRIMARY KEY, -- Hash MD5 de la business key
machine_id VARCHAR(20) NOT NULL,
load_date TIMESTAMP,
record_source VARCHAR(100),
UNIQUE(machine_id)
);
-- LINK : associations entre hubs
CREATE TABLE link_sensor_machine (
sensor_machine_lk CHAR(32) PRIMARY KEY,
sensor_hk CHAR(32) REFERENCES hub_sensor(sensor_hk),
machine_hk CHAR(32) REFERENCES hub_machine(machine_hk),
load_date TIMESTAMP,
record_source VARCHAR(100)
);
-- SATELLITE : attributs contextuels
CREATE TABLE sat_machine_details (
machine_hk CHAR(32) REFERENCES hub_machine(machine_hk),
load_date TIMESTAMP,
record_source VARCHAR(100),
machine_type VARCHAR(50),
site VARCHAR(100),
zone VARCHAR(50),
install_date DATE,
hash_diff CHAR(32), -- Hash de tous les attributs pour détecter les changements
PRIMARY KEY (machine_hk, load_date)
);
| Critère | Kimball (Star Schema) | Data Vault 2.0 |
|---|---|---|
| Complexité | Faible (compréhension métier) | Élevée (technique) |
| Audit trail | SCD type 2 limité | Complet (chaque changement) |
| Intégration multi-source | Difficile | Naturelle |
| Performance requêtes | Excellente (star) | Nécessite une couche de présentation |
| Cas d'usage | BI, tableaux de bord | Data Lakehouse, audit, conformité |
-- Clustering automatique
ALTER TABLE fact_sensor_readings
CLUSTER BY (date_sk, machine_sk);
-- Materialized view
CREATE MATERIALIZED VIEW mv_machine_daily AS
SELECT date_sk, machine_sk, AVG(temperature) AS avg_temp
FROM fact_sensor_readings
GROUP BY date_sk, machine_sk;
-- Time Travel (accès aux données historiques)
SELECT * FROM fact_sensor_readings
AT (TIMESTAMP => '2026-07-20 10:00:00'::TIMESTAMP);
-- Zero-copy cloning (environnement de test)
CREATE DATABASE dw_dev CLONE dw_prod;
-- Query profiling
SELECT * FROM TABLE(
INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION(
SESSION_ID => <session_id>,
RESULT_LIMIT => 100
)
);
-- Partitionnement et clustering
CREATE TABLE `project.dw.fact_sensor_readings`
PARTITION BY DATE(timestamp)
CLUSTER BY machine_sk
OPTIONS(
require_partition_filter = TRUE,
partition_expiration_days = 365
);
-- BI Engine (accélération en mémoire)
-- Réserver 10 Go pour les tables les plus interrogées
-- Scripting et procédures
DECLARE target_date DATE DEFAULT CURRENT_DATE();
CREATE TEMP TABLE temp_agg AS
SELECT machine_sk, COUNT(*) AS cnt
FROM fact_sensor_readings
WHERE DATE(timestamp) = target_date;
-- Slot estimation (optimisation des coûts)
SELECT
query,
total_slot_ms,
total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
-- Distribution style (clairé par machine jointe)
CREATE TABLE fact_sensor_readings (
...
) DISTKEY(machine_sk)
SORTKEY(date_sk, time_sk);
-- Compression
ANALYZE COMPRESSION fact_sensor_readings;
-- Materialized views
CREATE MATERIALIZED VIEW mv_daily_avg AS
SELECT date_sk, machine_sk, AVG(temperature) AS avg_temp
FROM fact_sensor_readings
GROUP BY date_sk, machine_sk;
-- Query plan
EXPLAIN
SELECT m.site, AVG(f.temperature)
FROM fact_sensor_readings f
JOIN dim_machine m ON f.machine_sk = m.machine_sk
GROUP BY m.site;
-- WLM (Workload Management) — queues
-- Configurer les queues pour séparer ETL et BI
| Technique | Snowflake | BigQuery | Redshift |
|---|---|---|---|
| Partitionnement | CLUSTER BY | PARTITION BY | SORTKEY |
| Distribution | Automatique | Automatique | DISTKEY |
| Compression | Automatique | Automatique | ENCODE manuel |
| Cache | Result set cache | BI Engine | Result cache |
| Tuning slots | Virtual warehouse | Slots réservés | WLM queues |
-- Identifier les goulots d'étranglement
-- 1. Full table scans
EXPLAIN SELECT ...; -- Vérifier l'utilisation des indexes/clés
-- 2. Jointures distribuées (Redshift)
-- Éviter les broadcasts de grandes tables
-- 3. Trop de partitions lues
SELECT table_name, SUM(row_count) AS rows
FROM fact_sensor_readings
WHERE date_sk BETWEEN 20250101 AND 20250722;
--> Si millions de lignes : partition filter manquant
-- 4. Compression inefficace
ANALYZE COMPRESSION dim_machine;
Trop de colonnes dans la table de faits.
SCD Type 2 sans clé de substitution (surrogate key).
machine_sk auto-incrémentée.Jointure sans index/clé de distribution.
DISTKEY (Redshift), clustering.Requêtes sans partition pruning.
Dimension date générée manuellement (incomplète).