Intern
ADF
3️⃣ CHECKLIST PRÊTE POUR AUDIT
🔹 ASSESSMENT (1.1)
☐ Rapport besoins métier : pourquoi migrer/ingérer (reporting,
performance, coût, conformité, fréquence de refresh).
☐ Paysage applicatif : Oracle, schémas, tables, dépendances, outils BI,
autres applis.
☐ Volumétrie journalière : volumes (ex : 100 tables, X lignes/jour), fenêtres
batch (C’est le créneau horaire réservé pour faire tourner les traitements
sans impacter les utilisateurs), latence attendue.
☐ SLA défini (Service Level Agreement): Un SLA répond à la question :
Quel niveau de service est garanti ?
Et que se passe-t-il si ce niveau n’est pas respecté ?
EX : Le service d’ingestion Oracle vers Azure SQL garantit une disponibilité
mensuelle de 99,5 %, une latence maximale de 4 heures après clôture
métier, un RPO de 24 heures et un RTO de 1 heure.
☐ Analyse sécurité : RBAC, chiffrement
☐ Analyse réseau : VPN/ExpressRoute, SHIR, firewall, ports.
☐ Plan de montée en compétences : compétences ADF/SQL/Azure à
acquérir côté client
🔹 DESIGN (2.1)
☐ Diagramme architecture :
☐ Stratégie FULL vs INCR
☐ Watermark documenté (date de mise à jour)
☐ STG + MERGE expliqué
☐ Sécurité détaillée
☐ Logs & monitoring
🔹 WELL-ARCHITECTED (2.2)
☐ Export Security pillar
☐ Export Reliability pillar
☐ Plan d’amélioration
🔹 POC (2.3)
Intern
e
☐ Objectifs
☐ Critères succès
☐ Résultats documentés
☐ Ajustements de design
🔹 DEPLOYMENT (3.1)
☐ SOW signé
☐ HLD / LLD
☐ As-built documentation
☐ Paramètres PROD
☐ CI/CD opérationnel
🔹 VALIDATION (4.1)
☐ Plan de test
☐ Rowcount Oracle vs SQL
☐ Test performance
☐ Validation client signée
🔹 RUNBOOK (4.2)
☐ Procédure relance
☐ Procédure reset watermark
☐ Procédure incident
☐ Monitoring documenté
☐ RPO / RTO définis
1.1 Assess / Analytics Portfolio Assessment (obligatoire)
Ce que tu fais sur ton projet Oracle → Azure SQL
Intern
e
Besoin métier : pourquoi migrer/ingérer (reporting, performance,
coût, conformité, fréquence de refresh).
Paysage applicatif : Oracle, schémas, tables, dépendances, outils BI,
autres applis.
Benchmarks : volumes (ex : 100 tables, X lignes/jour), SLA, fenêtres
batch, latence attendue.
Personas : qui consomme (BI, data engineers, métier), comment.
Réseau : VPN/ExpressRoute, SHIR, firewall, ports.
Sécurité/Conformité : RBAC, chiffrement, données sensibles, RGPD.
Disponibilité/DR : RPO/RTO, sauvegardes Azure SQL, résilience.
Skilling plan : compétences ADF/SQL/Azure à acquérir côté client.
Preuves à produire (acceptées)
Assessment Report (Word/PPT/PDF) + annexes (inventaire tables,
volumétrie, SLA)
Checklist/questionnaire rempli
Plan de montée en compétences (1 page suffit si clair)
V2_8_Analytics_on_Azure_Spec_Au…
2.1 Solution Design (obligatoire)
Ce que tu fais (design cible)
Conçois l’architecture complète :
Connectivité : Oracle on-prem → Self-Hosted Integration Runtime →
ADF → Azure SQL
Approche de chargement :
Intern
e
o FULL pour petites tables (truncate + reload)
o INCR pour grosses tables (watermark/date ou ID) + STG +
MERGE
Modèle de zones : stg (brut) → dbo (curated/serving)
Sécurité :
o secrets dans Key Vault
o compte Oracle read-only
o RBAC Azure + accès Azure SQL
Observabilité : logs ADF + tables de logs SQL + alerting
DevOps : séparation DEV/TEST/PROD + paramètres + versioning
Preuves à produire
Diagrammes d’architecture (logique + flux)
Spécification technique :
o stratégie FULL/INCR par table (métadonnées)
o règles de watermark(date de mise à jour ) / clés / rejets
Mapping (au moins pour les tables critiques)
Stratégie réseau & sécurité (1–2 pages)
(Option) estimation coût + budgets/alertes
V2_8_Analytics_on_Azure_Spec_Au…
2.2 Well-Architected Review (obligatoire)
Ce que tu fais
Tu exécutes une Well-Architected Review sur ce workload “ingestion ADF +
Azure SQL”, et tu choisis 2 piliers minimum (exemples pertinents) :
Security : secrets, RBAC, réseau, chiffrement, logs
Reliability : reprise sur incident, retries, HA SHIR, DR Azure SQL
(ou) Cost Optimization : scaling, batch windows, monitoring des
coûts
(ou) Operational Excellence : runbooks, alerting, CI/CD
Intern
e
Ce que tu dois pouvoir montrer
A. Secrets (Key Vault)
Screenshot Key Vault
Rôle Key Vault Secrets User pour ADF
Linked Service utilisant référence Key Vault
B. RBAC (Contrôle des accès)
Ce que tu dois faire
Data Engineers → Data Factory Contributor
Exploitation → Reader
ADF → accès limité au SQL (write STG uniquement)
Séparer DEV / TEST / PROD
Ce que Microsoft veut voir
Principe du Least Privilege appliqué.
C. Sécurité Réseau
Pour Oracle on-prem
Connexion via Self-Hosted IR
Flux sortant contrôlé
Firewall configuré
Pour Azure SQL
Firewall Azure activé
Optionnel : Private Endpoint
Pas d’accès public ouvert sans restriction
Intern
e
D. Chiffrement
En transit
TLS activé (par défaut Azure)
Oracle → ADF → SQL en HTTPS
Au repos
Azure SQL chiffré (TDE activé par défaut)
Key Vault chiffré
E. Logs & Audit
Tu dois activer :
Logs ADF
SQL auditing
Table etl_run_log
2️⃣ RELIABILITY (Fiabilité)
Objectif : le système continue à fonctionner même en cas d’incident.
🔹 A. Reprise sur incident
Ton mécanisme :
Watermark pour INCR
STG + MERGE
Rejeu possible d’une journée
👉 Ça prouve que ton ingestion est rejouable.
🔹 B. Retries
Dans ADF :
Retry = 3
Retry interval = 30 sec ou 1 min
Évite qu’un micro problème réseau casse tout le batch.
🔹 C. HA Self-Hosted IR
Intern
e
Idéalement :
2 nœuds SHIR sur 2 VM
Même Integration Runtime
Si 1 VM tombe → l’autre continue.
🔹 D. Disaster Recovery Azure SQL
Selon l’offre :
Geo-replication
Backup automatique
RPO/RTO documenté
Tu dois définir :
RTO = 1h
RPO = 24h (ou moins selon besoin)
💰 3️⃣ COST OPTIMIZATION
Objectif : ne pas gaspiller Azure.
🔹 A. Scaling intelligent
Data Flow compute adapté (pas surdimensionné)
Auto-terminate Debug
Pas de cluster allumé inutilement
🔹 B. Batch Windows
FULL la nuit
INCR en batch contrôlé
batchCount maîtrisé (ex : 5–10)
🔹 C. Monitoring des coûts
Intern
e
Budgets Azure configurés
Alertes si dépassement
Revue mensuelle des coûts
⚙️4️⃣ OPERATIONAL EXCELLENCE
L’exploitation est industrialisée via :
Séparation DEV / TEST / PROD
Git activé dans ADF
Déploiement CI/CD via ARM templates
Runbooks documentés
Alerting automatique en cas d’échec pipeline
Objectif : exploitation simple, contrôlée et automatisée.
🔹 A. Runbooks
Tu dois documenter :
Comment relancer une table
Comment reset watermark
Où voir les logs
Procédure incident
🔹 B. Alerting
Alertes ADF sur échec
Alertes durée anormale
Notification mail / Teams
🔹 C. CI/CD
Git activé
ARM template généré
Intern
e
Déploiement DEV → TEST → PROD automatisé
Pas de modification manuelle en PROD
🧠 Résumé stratégique pour ton audit
Pilier Ce que tu dois prouver
Accès contrôlés + secrets
Security
protégés
Reliability Rejeu + retry + HA + DR
Cost Scaling contrôlé + budgets
Operation
Runbook + alerting + CI/CD
al
Intern
e
Preuves à produire
Export des résultats Well-Architected (captures/exports) + plan
d’actions (même simple)
V2_8_Analytics_on_Azure_Spec_Au…
2.3 PoC / Pilot (obligatoire)
Ce que tu fais
Avant de généraliser à 100 tables, fais un pilot sur un périmètre :
2–5 tables simples (FULL)
1 grosse table (INCR watermark)
tests perf + latence + validation règles (doublons, types)
Définis :
objectifs du PoC, critères de succès, résultats, décisions prises (ex :
“on garde STG+MERGE”, “partitionnement source”,
“batchCount=5”, etc.)
Preuves à produire
PoC Pack : objectifs, scope, critères, résultats, leçons apprises,
design ajusté
diagramme PoC + logs d’exécution ADF
V2_8_Analytics_on_Azure_Spec_Au…
3.1 Deployment (obligatoire)
Ce que tu fais
Tu déploies en production (ou environnement client final) avec des preuves
“end-to-end”.
Inclure au moins 2 éléments par projet (et idéalement plus) :
SOW signé / CR de lancement / validation client
plan de déploiement + séquence migration
diagrammes, HLD/LLD
as-built documentation
Intern
e
paramètres ADF (sanitisés) + structure des pipelines
Preuves à produire
Deployment Pack : plan, check de go-live, as-built, captures ADF
(sans secrets)
V2_8_Analytics_on_Azure_Spec_Au…
4.1 Validation & Testing (obligatoire)
Ce que tu fais
Tu montres que tu as validé :
fonctionnel : données attendues, complétude, règles de gestion
technique : performance, temps de traitement, charge, stabilité
comparaison : rowcounts Oracle vs Azure SQL, contrôle doublons,
contrôle NULL
sign-off : validation par le client (mail/CR/PV)
Preuves à produire
Plan de tests + résultats (tableau)
preuves de contrôles (rowcount, logs, watermark)
PV de recette / validation client
V2_8_Analytics_on_Azure_Spec_Au…
4.2 Post-deployment documentation (SOP / Runbooks) (obligatoire)
Ce que tu fais
Tu fournis une doc d’exploitation utilisable :
comment lancer/relancer un pipeline
que faire en cas d’échec (retries, relance table, reset watermark)
où voir les logs (ADF Monitor + tables SQL)
procédures d’accès / RBAC
sauvegarde/restauration Azure SQL, RPO/RTO
gestion incidents + escalade (qui contacter)
Preuves à produire
Intern
e
Runbook (2–6 pages) + SOP d’exploitation quotidien/hebdo
checklist de support & monitoring
V2_8_Analytics_on_Azure_Spec_Au…
Comment “transformer” ton projet en preuve Microsoft en 1 phrase
Tu dois rendre ton ingestion répétable, sécurisée, monitorée, testée,
documentée, et conserver les artefacts qui le prouvent (rapport
d’assessment, design, export WAF, PoC pack, pack de déploiement, tests,
runbooks).
V2_8_Analytics_on_Azure_Spec_Au…
Pour respecter la règle “3 clients / 24 mois”
Ton action immédiate :
Fais ce projet client au format ci-dessus (Client 1).
Duplique exactement la structure pour Client 2 et Client 3 (même
techno possible, mais clients distincts).
V2_8_Analytics_on_Azure_Spec_Au…
Si tu veux, je te fournis maintenant (sans te poser de questions) :
1. un template Assessment Report 1.1 (sommaire prêt à remplir),
2. un template Design 2.1 (sections + contenu attendu),
3. un template PoC 2.3,
4. un template Test Plan 4.1,
5. un template Runbook 4.2
Intern
e
avec ADF Mapping Data Flow, ~100 tables, ~200 000 lignes /
table / jour, et un mix full + incrémental, voilà ce que tu dois mettre en
place pour que ce soit industrialisé, performant, sécurisé, et “audit-
ready”.
1) Architecture cible recommandée
Ne fais pas 100 data flows “à la main”. Fais une approche metadata-
driven :
1 pipeline maître “Ingestion_Oracle_to_SQL”
1 table de configuration (dans SQL ou dans un fichier JSON) avec,
pour chaque table :
o nom table source / cible
o type de chargement (FULL / INCR)
o colonne watermark (ex: LAST_UPDATE_DATE)
o clé(s) métier / clé primaire
o stratégie (append / upsert)
o partition key si utile
Intern
e
Le pipeline lit la config → ForEach → exécute le même Data Flow
paramétré.
👉 Résultat : tu maintiens la logique une seule fois, et tu ajoutes/modifies
une table par config.
2) Connectivité Oracle on-prem (le point critique)
Pour Oracle on-prem vers Azure Data Factory :
Utilise un Self-Hosted Integration Runtime (SHIR) sur une VM
on-prem (ou DMZ) :
o HA si possible (2 nœuds) pour éviter le SPOF
Sécurise les identifiants via Azure Key Vault
Réseau : idéalement VPN S2S / ExpressRoute si exigences fortes
(sinon au minimum flux sortant contrôlé)
3) Stratégie FULL (pour les petites/moyennes tables)
Pour un FULL propre :
TRUNCATE + LOAD (si acceptable fonctionnellement)
Mets le sink en mode qui évite les lignes une par une :
o écriture par lots (batch)
o table lock si possible
Ajoute systématiquement :
o rowcount source vs cible
o log d’exécution (voir section 7)
⚠️Si tu fais du FULL sur beaucoup de tables, planifie les FULL en “fenêtre
batch” (nuit) et garde l’incrémental en journée.
4) Stratégie INCR (pour les “grosses tables”)
Option A (la plus fréquente) : incrémental par watermark
Pré-requis : une colonne type LAST_UPDATE_DATE ou un ID croissant
fiable.
Dans ton pipeline :
Intern
e
Stocke, par table, le dernier watermark chargé (last_success_value)
dans une table de contrôle
Requête source : WHERE last_update_date > :last_success_value
En sortie :
o charge dans une table STG
o puis exécute un MERGE vers la table finale (UPSERT)
Pourquoi STG + MERGE ?
C’est robuste (rejouable)
C’est audit-friendly (tu peux prouver ce qui a été chargé)
Ça gère inserts + updates
Option B : CDC Oracle (plus complexe)
Possible seulement si tu as une techno CDC (GoldenGate, LogMiner, etc.).
À envisager si tu dépasses les limites du watermark.
5) Performance (sinon ça va exploser en temps de run)
Avec 100 tables * 200k/jour, la performance dépend surtout de 3 choses :
a) Partitionnement Data Flow
Partitionne côté source (par clé numérique ou date) sur les
grosses tables
Partitionne côté sink pareil
Évite une seule partition (sinon tu n’utilises pas le cluster)
b) Taille de compute du Data Flow
Ajuste le compute (généralement “General Purpose” / taille
moyenne à grande pour gros volumes)
Évite Debug activé en prod
c) Évite Data Flow si c’est juste de la copie
Si tu fais uniquement “copie Oracle → SQL” sans transformations, Copy
Activity est souvent plus rapide et moins cher que Mapping Data Flow.
Garde Mapping Data Flow pour transformations/cleansing
Utilise Copy Activity pour ingestion brute
Intern
e
6) Identifier si ton “SQL Sage cloud” est Azure (sans te faire
perdre du temps)
Tu peux le deviner en regardant le serveur dans la connexion :
Si tu vois [Link] → c’est Azure SQL Database
Si c’est un nom “géré” Sage (ex : domaine sage/hosting) → ça peut
être du SQL managé non-Azure, mais ça reste SQL Server
compatible
Impact :
Si c’est Azure SQL : tu peux utiliser Private Endpoint, MI, Azure
Monitor, etc.
Si ce n’est pas Azure : focus sur sécurité réseau + perf SQL standard
+ sauvegardes côté provider.
7) Fiabilité + observabilité (obligatoire en prod)
Tu dois mettre en place :
a) Table de logs d’exécution (dans SQL)
Une table etl_run_log avec :
pipeline_name, table_name
start_time, end_time, duration
rows_read, rows_written
status (SUCCESS/FAILED)
error_message
watermark_used
run_id (correlation)
b) Alerting
Alertes ADF (échecs, durée anormale)
Centralisation logs si possible (Log Analytics / SIEM selon contexte)
c) Gestion des rejets
Table etl_rejects pour lignes invalides (types, contraintes, etc.)
Tu ne dois pas “perdre” des lignes silencieusement.
Intern
e
8) Sécurité (minimum attendu)
Secrets dans Key Vault
Identités gérées (Managed Identity) si possible
Comptes Oracle en lecture seule
Séparer les droits :
o compte ingestion (write STG)
o compte merge (write final)
o compte BI (read)
9) Ce que tu dois produire comme livrables (si tu vises une
validation/audit)
Même si tu ne me l’as pas demandé explicitement, c’est ce qui fait la
différence quand on vous “challenge” :
1. Schéma d’architecture (Oracle on-prem → SHIR → ADF → SQL
cloud)
2. Table de configuration ingestion (100 tables) + stratégie
FULL/INCR
3. Procédure de reprise (comment relancer, watermark, rejouer une
journée)
4. Exemples de preuves :
o logs ADF + extrait de etl_run_log
o exemple de MERGE (ou SP) + preuve de rowcounts
o runbook d’exploitation (SOP)
Si tu veux, je te donne un modèle prêt à l’emploi
Je peux te générer :
le schéma SQL des tables etl_config, etl_watermark, etl_run_log,
etl_rejects
une structure de pipeline ADF (pas à pas) pour le mode
metadata-driven
la stratégie STG + MERGE type (SQL Server)
Intern
e
Dis-moi juste : tu veux que je parte sur un MERGE par clé primaire (1
clé) ou tu as souvent des clés composites ?
oui donne moi tout stp
OK — je te mets un pack complet “prêt à implémenter” : modèle de
tables SQL (config + watermark + logs + rejets), structure de pipeline ADF
metadata-driven (100 tables), pattern STG + MERGE (upsert), +
runbook d’exploitation.
Hypothèse raisonnable : ton SQL “Sage cloud” est SQL Server
compatible (Azure SQL Database / Managed Instance / SQL Server
hébergé). Tout ce qui suit marche dans tous ces cas (avec quelques
options spécifiques Azure).
1) Modèle de données côté SQL (contrôle + logs)
1.1 Table de configuration etl_config_table
Une ligne par table source.
CREATE TABLE dbo.etl_config_table (
table_id INT IDENTITY(1,1) PRIMARY KEY,
is_enabled BIT NOT NULL DEFAULT (1),
-- Source
source_schema SYSNAME NOT NULL,
source_table SYSNAME NOT NULL,
-- Cible
target_schema SYSNAME NOT NULL DEFAULT ('dbo'),
target_table SYSNAME NOT NULL,
-- Stratégie de chargement
load_type VARCHAR(10) NOT NULL CHECK (load_type IN
('FULL','INCR')),
incr_strategy VARCHAR(10) NULL CHECK (incr_strategy IN
('WM','CDC')), -- WM = watermark
watermark_column SYSNAME NULL, -- ex: LAST_UPDATE_DATE
watermark_type VARCHAR(20) NULL, -- 'datetime','int','bigint'...
primary_key_columns NVARCHAR(1000) NOT NULL, -- 'ID' ou 'ID1,ID2'
-- Options
use_staging BIT NOT NULL DEFAULT (1),
staging_schema SYSNAME NOT NULL DEFAULT ('stg'),
Intern
e
staging_table SYSNAME NULL, -- si NULL -> généré
stg_<target_table>
allow_deletes BIT NOT NULL DEFAULT (0), -- si vous gérez les deletes
(soft delete)
soft_delete_column SYSNAME NULL, -- ex: IsDeleted
-- Performance
source_partition_col SYSNAME NULL, -- ex: ID ou date
source_partition_num INT NULL, -- ex: 8, 16
sink_batch_size INT NULL, -- ex: 10000
sink_pre_copy_script NVARCHAR(MAX) NULL, -- ex: TRUNCATE TABLE ...
(FULL)
sink_post_copy_script NVARCHAR(MAX) NULL,-- ex: EXEC merge proc ...
-- Auditable
business_owner NVARCHAR(200) NULL,
comments NVARCHAR(1000) NULL,
created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
updated_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
Pour primary_key_columns, mets une liste séparée par virgule :
CustomerId ou CustomerId,CompanyId.
1.2 Table watermarks etl_watermark
Une ligne par table, mise à jour après succès.
CREATE TABLE dbo.etl_watermark (
table_id INT NOT NULL PRIMARY KEY,
last_success_value_str NVARCHAR(200) NULL, -- stocke la valeur sous
forme texte (simple)
last_success_time_utc DATETIME2 NULL,
last_run_status VARCHAR(10) NULL, -- SUCCESS/FAILED
last_error NVARCHAR(2000) NULL,
updated_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
CONSTRAINT FK_etl_watermark_config FOREIGN KEY (table_id)
REFERENCES dbo.etl_config_table(table_id)
);
GO
Intern
e
1.3 Logs d’exécution etl_run_log
Pour la traçabilité et l’audit.
CREATE TABLE dbo.etl_run_log (
run_log_id BIGINT IDENTITY(1,1) PRIMARY KEY,
pipeline_run_id NVARCHAR(64) NOT NULL,
table_id INT NOT NULL,
load_type VARCHAR(10) NOT NULL,
start_time_utc DATETIME2 NOT NULL,
end_time_utc DATETIME2 NULL,
duration_seconds INT NULL,
watermark_used NVARCHAR(200) NULL,
rows_read BIGINT NULL,
rows_written BIGINT NULL,
rows_merged BIGINT NULL,
status VARCHAR(10) NOT NULL, -- STARTED/SUCCESS/FAILED
error_message NVARCHAR(4000) NULL,
CONSTRAINT FK_etl_run_log_config FOREIGN KEY (table_id)
REFERENCES dbo.etl_config_table(table_id)
);
GO
1.4 Table rejets etl_rejects
Optionnel mais très utile (si vous validez des règles ou types).
CREATE TABLE dbo.etl_rejects (
reject_id BIGINT IDENTITY(1,1) PRIMARY KEY,
pipeline_run_id NVARCHAR(64) NOT NULL,
table_id INT NOT NULL,
reject_time_utc DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
reject_reason NVARCHAR(1000) NOT NULL,
payload_json NVARCHAR(MAX) NULL
);
GO
1.5 Schémas STG
Crée le schéma stg :
Intern
e
IF NOT EXISTS (SELECT 1 FROM [Link] WHERE name = 'stg')
EXEC('CREATE SCHEMA stg');
GO
2) Pattern “STG + MERGE” (UPSERT) prêt à copier
2.1 Principe
Data Flow charge dans stg.<table>
Ensuite on exécute un MERGE de stg vers dbo (table finale)
On met à jour le watermark après succès
2.2 Procédure MERGE générique (base)
Comme les colonnes changent selon table, on fait une procédure
génératrice (SQL dynamique). C’est la méthode la plus réaliste pour 100
tables.
CREATE OR ALTER PROCEDURE dbo.usp_merge_from_stg
@target_schema SYSNAME,
@target_table SYSNAME,
@stg_schema SYSNAME,
@stg_table SYSNAME,
@pk_columns NVARCHAR(1000) -- 'ID' ou 'ID1,ID2'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @fullTarget NVARCHAR(300) = QUOTENAME(@target_schema)
+ '.' + QUOTENAME(@target_table);
DECLARE @fullStg NVARCHAR(300) = QUOTENAME(@stg_schema) +
'.' + QUOTENAME(@stg_table);
-- colonnes (hors computed/identity)
DECLARE @cols NVARCHAR(MAX) = (
SELECT STRING_AGG(QUOTENAME([Link]), ',')
FROM [Link] c
JOIN [Link] t ON c.object_id = t.object_id
JOIN [Link] s ON t.schema_id = s.schema_id
WHERE [Link] = @stg_schema AND [Link] = @stg_table
AND c.is_computed = 0
);
-- clause ON (PK)
Intern
e
DECLARE @on NVARCHAR(MAX) = (
SELECT STRING_AGG('T.' + QUOTENAME(LTRIM(RTRIM(value))) + ' =
S.' + QUOTENAME(LTRIM(RTRIM(value))), ' AND ')
FROM STRING_SPLIT(@pk_columns, ',')
);
-- update set (toutes colonnes sauf PK)
DECLARE @set NVARCHAR(MAX) = (
SELECT STRING_AGG('T.' + QUOTENAME([Link]) + ' = S.' +
QUOTENAME([Link]), ',')
FROM [Link] c
JOIN [Link] t ON c.object_id = t.object_id
JOIN [Link] s ON t.schema_id = s.schema_id
WHERE [Link] = @stg_schema AND [Link] = @stg_table
AND c.is_computed = 0
AND [Link] NOT IN (SELECT LTRIM(RTRIM(value)) FROM
STRING_SPLIT(@pk_columns, ','))
);
DECLARE @sql NVARCHAR(MAX) = N'
MERGE ' + @fullTarget + N' AS T
USING ' + @fullStg + N' AS S
ON ' + @on + N'
WHEN MATCHED THEN
UPDATE SET ' + @set + N'
WHEN NOT MATCHED BY TARGET THEN
INSERT (' + @cols + N')
VALUES (' + REPLACE(@cols, '[', 'S.[') + N');';
EXEC sp_executesql @sql;
END
GO
Notes :
Ça fait un upsert complet.
Si tu veux gérer “delete”, on peut ajouter une clause WHEN NOT
MATCHED BY SOURCE THEN ... (mais c’est risqué si tu ne charges
que l’incrémental).
3) Structure ADF “metadata-driven” (100 tables)
3.1 Pipelines à créer
Intern
e
1. PL_Master_Ingestion (orchestrateur)
2. PL_Load_One_Table (charge une table, appelée en boucle)
3. (Option) PL_Merge_One_Table si tu veux séparer merge
3.2 Datasets / Linked Services
Linked Service Oracle (via SHIR)
Linked Service SQL (ton Sage cloud)
Dataset paramétré Oracle : schema, table, (optionnel) query
Dataset paramétré SQL : schema, table
3.3 Activités dans PL_Master_Ingestion
1. Lookup sur SQL : lire les tables actives
SELECT *
FROM dbo.etl_config_table
WHERE is_enabled = 1;
2. ForEach sur la sortie du Lookup
batchCount (ex: 5 à 10 selon capacité)
Appeler Execute Pipeline → PL_Load_One_Table
Passe en paramètres toutes les colonnes utiles (load_type,
watermark_column, pk, etc.)
3.4 PL_Load_One_Table (logique)
Activités conseillées :
1. Stored Procedure : insert log STARTED
2. Si load_type = FULL :
o (Option) exécuter TRUNCATE sur la cible ou sur la STG
3. Récupérer watermark si INCR :
o Lookup etl_watermark.last_success_value_str
o Si NULL → fallback : date ancienne / 0
4. Data Flow (Mapping Data Flow) paramétré
o Source Oracle : table paramétrée + filtre si INCR
o Sink SQL : vers stg.<table>
5. Stored Procedure : usp_merge_from_stg (si use_staging=1)
Intern
e
6. Stored Procedure : update watermark (si INCR) + log SUCCESS
7. On Failure :
o update log FAILED + last_error dans etl_watermark
4) Mapping Data Flow : paramètres & règles (FULL + INCR)
4.1 Paramètres du Data Flow
Crée ces paramètres :
p_source_schema (string)
p_source_table (string)
p_load_type (string)
p_watermark_col (string)
p_watermark_value (string) (ou datetime selon choix)
p_target_schema (string)
p_target_table (string)
p_stg_schema (string)
p_stg_table (string)
4.2 Source Oracle
Source = table
Si INCR : filtre dynamique
o si watermark type datetime :
toTimestamp(byName($p_watermark_col)) >
toTimestamp($p_watermark_value)
o sinon (int/bigint):
toLong(byName($p_watermark_col)) >
toLong($p_watermark_value)
Si tu n’arrives pas à faire du “byName” selon version, alternative :
construire une query dans pipeline (paramètre query) au lieu du filtre
dans dataflow.
4.3 Sink vers SQL (STG)
Mode : Insert (append) dans la table STG
Intern
e
Pré-SQL (FULL) : TRUNCATE TABLE stg.<table>
Post-SQL : rien (le merge sera une activité séparée en pipeline)
4.4 Calcul du nouveau watermark
Dans le pipeline (pas dans le dataflow), après succès :
requête sur STG : SELECT MAX(<watermark_col>) FROM
stg.<table>
stocker dans etl_watermark.last_success_value_str
5) Création des tables STG & cibles (approche simple)
Pour chaque table cible dbo.X, crée stg.X même structure (sans
contraintes lourdes au début) :
SELECT TOP 0 *
INTO stg.X
FROM dbo.X;
Si tu n’as pas encore dbo.X, fais l’inverse : crée d’abord stg à partir de
l’extract (ou script de création fourni par votre modèle).
6) Contrôles qualité indispensables (sans complexifier)
Dans PL_Load_One_Table, ajoute 2 checks “cheap” :
6.1 Compte lignes STG
SELECT COUNT(1) AS cnt FROM stg.<table>;
6.2 Compte lignes merged (option)
Après MERGE, tu peux logger un “rows_merged” approximatif, ou
simplement logger rows_written côté Data Flow + succès merge.
Le must : conserver une preuve (log + rowcount + watermark) pour
chaque run.
7) Gestion des erreurs & reprise
Politique de reprise recommandée
Si un run échoue :
o le watermark n’avance pas
Intern
e
o tu peux relancer la table → elle rejoue la même fenêtre (ok)
Pour FULL :
o si truncate + load échoue, table partiellement chargée :
préfère charger en STG puis swap/merge.
Timeout / Retry
Active des retries sur activités (ex: 3 retries, backoff)
Sur Oracle on-prem : surveille surtout SHIR (CPU/RAM/latence)
8) Sécurité minimale (concrète)
Mettre identifiants Oracle + SQL dans Key Vault
ADF utilise Managed Identity si possible (surtout pour Key Vault)
Compte Oracle = read-only
Côté SQL : compte ingestion = write STG + exec merge proc (ou
séparer en 2 comptes)
9) Runbook d’exploitation (SOP) prêt à coller dans un doc
SOP – Exécution quotidienne
1. Vérifier que le SHIR est “Online”
2. Lancer PL_Master_Ingestion (horaire batch)
3. Contrôler etl_run_log : pas de FAILED
4. Si FAILED :
o lire error_message
o relancer seulement la table (via paramètre / pipeline unitaire si
tu prévois)
5. Valider le watermark a avancé pour les tables INCR
SOP – Relancer une table sur une journée
Remettre etl_watermark.last_success_value_str à la valeur J-1 (ou 0)
Relancer la table
Contrôler rowcount + résultat
SOP – Incident data (doublons / valeurs manquantes)
Intern
e
Bloquer la table (is_enabled=0)
Extraire un échantillon STG
Corriger règle ou mapping
Rejouer
10) Ce que tu peux faire dès maintenant (plan 2 jours)
Jour 1
Créer les 4 tables (config/watermark/log/rejects)
Paramétrer 1 pipeline + 1 dataflow générique
Tester sur 2 tables : 1 FULL + 1 INCR
Jour 2
Renseigner etl_config_table pour 100 tables
Créer les STG automatiquement
Mettre batchCount + alerting + runbook
Bonus : comment reconnaître si ton SQL Sage est Azure (en 10
secondes)
Si le serveur ressemble à [Link] → Azure SQL
Database.
Sinon, c’est probablement SQL Server hébergé (toujours OK pour
tout ce pack).
Key vault creation :
Méthode recommandée : Managed Identity (sans mot de passe)
1) Activer l’identité managée d’ADF
Dans ADF : Manage → Managed identities
Active la System assigned managed identity.
2) Donner le droit à ADF de lire les secrets dans Key Vault
Dans Key Vault :
Va dans Access control (IAM) (ou “Access policies” selon ton mode),
Donne à l’identité ADF le rôle :
Intern
e
o Key Vault Secrets User (souvent suffisant),
o ou Key Vault Secrets Officer (plus large, à éviter si pas
nécessaire).
(L’objectif : autoriser ADF à faire “Get” sur les secrets.)
3) Créer un Linked Service Key Vault dans ADF
ADF : Manage → Linked services → New → Azure Key Vault
Authentification : Managed Identity.
4) Utiliser le secret dans un Linked Service (Oracle/SQL)
Exemple : Linked Service SQL / Oracle → champ mot de passe → sélection
Azure Key Vault
Tu choisis :
le Key Vault Linked Service
le nom du secret (ex: oracle-password-prod)
👉 Résultat : ton Linked Service n’a pas le mot de passe, seulement une
référence.
----------------------- Injection des données de l'année N-1 dans l'année N
-----------------------
;WITH DataN AS (
SELECT DISTINCT
CAST([Link] AS DATE) AS DateN,
[Link],
P.[Name],
Intern
e
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM [dw].[dim_Product] P
),
DataN_1 AS (
SELECT DISTINCT
DATEADD(YEAR, +1, CAST([Link] AS DATE)) AS DateN,
[Link],
P.[Name],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM [dw].[dim_Product] P
INSERT INTO [dw].[dim_Product_MAJ]
Intern
e
CreatedOn,
InitialRowGuid,
[Name],
ProductCategory,
ProductKey,
RowGuid,
ProductTypeRowGuid,
DeletedOn,
Price,
Price2,
ProductCategoryRowGuid,
ProductOrigin,
ProductTypeKey,
ShortName,
SoftModifiedOn,
[Description]
SELECT DISTINCT
[Link] AS CreatedOn,
[Link],
req2.[Name],
[Link],
[Link],
[Link],
[Link],
[Link],
CAST(0 AS DECIMAL(18,2)) AS Price,
CAST(0 AS DECIMAL(18,2)) AS Price2,
Intern
e
[Link],
[Link],
[Link],
[Link],
NULL AS SoftModifiedOn,
NULL AS [Description]
FROM (
SELECT DISTINCT
[Link],
[Link],
L.[Name],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM DataN_1 L
EXCEPT
SELECT DISTINCT
[Link],
[Link],
T.[Name],
[Link],
Intern
e
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM DataN T
) req2;