-- EXTRACT: Pull data from source systems (databases, flat files, APIs)
SELECT order_id, customer_id, product_id, amount, order_date
FROM sales_orders WHERE order_date = CURRENT_DATE - 1;

-- TRANSFORM: Apply business rules, cleanse, aggregate
-- Cleansing, business rules (tax = amount * 0.18),
-- aggregation, lookup mapping, type conversion, deduplication

-- LOAD: Insert transformed data into the target warehouse
INSERT INTO warehouse.fact_sales
  (order_id, customer_name, product_name, amount, tax, order_date)
VALUES (transformed_data);
-- Full load: Truncate + reload | Incremental: Only new/changed

-- Tester validates EACH phase:
-- Extract: All source data captured?
-- Transform: Business rules applied correctly?
-- Load: Target data matches expected output?

-- ETL: Transform BEFORE loading. Tools: Informatica, Talend, SSIS
-- Use when: Limited target compute, heavy cleansing, compliance
-- Flow: Source → Staging (transform) → Target warehouse

-- ELT: Load raw FIRST, transform in warehouse. Tools: dbt, Glue
-- Use when: Cloud warehouse, raw data for auditing
-- Flow: Source → Raw layer → Transform in warehouse → Target

-- Testing: ETL validates staging + target
--          ELT validates raw layer + transformed layer
-- ELT is standard in cloud; ETL dominates on-premise

-- 1. DATA COMPLETENESS: All source records reach target
SELECT COUNT(*) FROM source_table;
SELECT COUNT(*) FROM target_table;

-- 2. DATA TRANSFORMATION: Business rules applied correctly

-- 3. DATA QUALITY: NULLs, duplicates, referential integrity
SELECT * FROM fact_sales
WHERE customer_id NOT IN (SELECT customer_id FROM dim_customer);

-- 4. PERFORMANCE: Completes within batch window
-- 5. REGRESSION: After code changes, existing data intact
-- 6. INCREMENTAL LOAD: Only new/modified records loaded

-- Row count comparison
SELECT 'source' AS system, COUNT(*) AS row_count FROM source_db.orders
UNION ALL
SELECT 'target', COUNT(*) FROM warehouse.fact_orders;

-- NULL checks on critical columns
SELECT COUNT(*) AS total,
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer,
  SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_amount
FROM warehouse.fact_orders;

-- Duplicate detection
SELECT order_id, COUNT(*) AS dup_count
FROM warehouse.fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

-- Count by date to catch partial loads
SELECT order_date, COUNT(*) FROM source_db.orders GROUP BY order_date;
-- Compare with target — mismatch pinpoints where records dropped

-- Date format: VARCHAR '15-03-2024' → DATE 2024-03-15
SELECT s.order_id, s.order_date_str, t.order_date,
  CASE WHEN t.order_date = STR_TO_DATE(s.order_date_str, '%d-%m-%Y')
    THEN 'PASS' ELSE 'FAIL' END AS result
FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
WHERE t.order_date != STR_TO_DATE(s.order_date_str, '%d-%m-%Y');

-- Currency conversion: INR → USD
SELECT s.order_id, s.amount_inr, t.amount_usd,
  ROUND(s.amount_inr / r.exchange_rate, 2) AS expected_usd
FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
JOIN ref_exchange_rates r ON s.currency = r.currency_code
WHERE t.amount_usd != ROUND(s.amount_inr / r.exchange_rate, 2);

-- Lookup mapping: region_code → region_name
SELECT s.order_id, s.region_code, t.region_name, m.expected_region_name
FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
JOIN mapping_table m ON s.region_code = m.region_code
WHERE t.region_name != m.expected_region_name;

-- Column-level profiling
SELECT COUNT(*) AS total_rows,
  COUNT(DISTINCT customer_id) AS unique_customers,
  ROUND(SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END)
    * 100.0 / COUNT(*), 2) AS null_pct
FROM source_db.orders;

-- Value distribution
SELECT region, COUNT(*) AS frequency,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct
FROM source_db.orders GROUP BY region ORDER BY frequency DESC;

-- Outlier detection
SELECT * FROM source_db.orders WHERE amount <= 0 OR amount > 1000000;

-- Profiling reveals quality issues before ETL runs, defines
-- NULL thresholds, identifies edge cases for test scenarios

-- Basic comparison
SELECT 'source' AS system, COUNT(*) FROM source_db.sales_orders
UNION ALL
SELECT 'target', COUNT(*) FROM warehouse.fact_sales;

-- Better: Compare by date to find partial load failures
SELECT COALESCE(s.order_date, t.order_date) AS check_date,
  COALESCE(s.cnt, 0) AS source_count,
  COALESCE(t.cnt, 0) AS target_count,
  COALESCE(s.cnt, 0) - COALESCE(t.cnt, 0) AS difference
FROM (SELECT order_date, COUNT(*) AS cnt
  FROM source_db.sales_orders GROUP BY order_date) s
FULL OUTER JOIN (SELECT order_date, COUNT(*) AS cnt
  FROM warehouse.fact_sales GROUP BY order_date) t
ON s.order_date = t.order_date
WHERE COALESCE(s.cnt, 0) != COALESCE(t.cnt, 0);
-- Total count match can hide offsetting errors

-- Duplicates on primary key
SELECT order_id, COUNT(*) AS occurrence
FROM warehouse.fact_sales
GROUP BY order_id HAVING COUNT(*) > 1;

-- Duplicates on composite business key
SELECT customer_id, product_id, order_date, COUNT(*)
FROM warehouse.fact_sales
GROUP BY customer_id, product_id, order_date HAVING COUNT(*) > 1;

-- Summary: total vs unique vs duplicate
SELECT COUNT(*) AS total,
  COUNT(DISTINCT order_id) AS unique_orders,
  COUNT(*) - COUNT(DISTINCT order_id) AS duplicates
FROM warehouse.fact_sales;
-- Causes: missing dedup step, job retry, source dupes, wrong join

-- Find records where transformation is incorrect
SELECT s.customer_id, s.first_name, s.last_name,
  t.full_name AS actual,
  CONCAT(s.first_name, ' ', s.last_name) AS expected
FROM source_db.customers s
JOIN warehouse.dim_customer t ON s.customer_id = t.customer_id
WHERE t.full_name != CONCAT(TRIM(s.first_name), ' ', TRIM(s.last_name))
   OR s.first_name IS NULL OR s.last_name IS NULL;

-- Summary: count pass vs fail
SELECT COUNT(*) AS total,
  SUM(CASE WHEN t.full_name = CONCAT(TRIM(s.first_name), ' ', TRIM(s.last_name))
    THEN 1 ELSE 0 END) AS passed,
  SUM(CASE WHEN t.full_name != CONCAT(TRIM(s.first_name), ' ', TRIM(s.last_name))
    THEN 1 ELSE 0 END) AS failed
FROM source_db.customers s
JOIN warehouse.dim_customer t ON s.customer_id = t.customer_id;

-- New records loaded? (result should be EMPTY)
SELECT s.order_id FROM source_db.orders s
LEFT JOIN warehouse.fact_orders t ON s.order_id = t.order_id
WHERE s.modified_date > '2024-01-15 00:00:00' AND t.order_id IS NULL;

-- Updated records reflect latest values?
SELECT s.order_id, s.amount AS source, t.amount AS target
FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
WHERE s.modified_date > '2024-01-15 00:00:00' AND s.amount != t.amount;

-- Old records untouched?
SELECT t.order_id FROM warehouse.fact_orders t
WHERE t.load_timestamp > '2024-01-15 00:00:00'
  AND t.order_id NOT IN (SELECT order_id FROM source_db.orders
    WHERE modified_date > '2024-01-15 00:00:00');

-- Duplicates after load? (common: insert instead of upsert)
SELECT order_id, COUNT(*) FROM warehouse.fact_orders
GROUP BY order_id HAVING COUNT(*) > 1;

-- INFORMATICA POWERCENTER: Enterprise ETL, large organizations
-- Testing: Validate mapping logic, check session logs

-- TALEND: Open-source, drag-and-drop interface
-- Testing: Validate tMap transformations, check reject files

-- SSIS: Microsoft ETL, common in .NET environments
-- Testing: Validate package execution, check error outputs

-- AWS GLUE: Serverless ETL, uses PySpark
-- APACHE SPARK: Big data processing, Spark SQL

-- Pick 1-2 tools, describe a specific project:
-- "I validated 200+ transformation rules in Informatica
--  using automated SQL scripts" beats "I used Informatica"

-- 1. SQL SCRIPTS: Store queries, run after each load
CREATE TABLE etl_test_results (
  test_id INT AUTO_INCREMENT PRIMARY KEY,
  test_name VARCHAR(200),
  source_count INT, target_count INT,
  status VARCHAR(10),
  run_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO etl_test_results (test_name, source_count, target_count, status)
SELECT 'fact_sales row count',
  (SELECT COUNT(*) FROM source_db.orders),
  (SELECT COUNT(*) FROM warehouse.fact_sales),
  CASE WHEN (SELECT COUNT(*) FROM source_db.orders)
    = (SELECT COUNT(*) FROM warehouse.fact_sales)
    THEN 'PASS' ELSE 'FAIL' END;

-- 2. PYTHON: pandas/SQLAlchemy for programmatic validation
-- 3. SCHEDULING: Airflow DAG or cron after ETL completion
-- 4. FRAMEWORKS: Great Expectations, dbt tests, QuerySurge

-- Lineage: Source → Staging → Transform → Target → Dashboard
-- MySQL orders → staging.raw_orders → staging.transformed_orders
--   → warehouse.fact_sales → Tableau/Power BI

-- Why it matters:
-- TRACEABILITY: Wrong numbers → trace to transformation
-- IMPACT ANALYSIS: Source change → find affected tables
-- TEST COVERAGE: Reveals untested transformations
-- DEBUGGING: Failed records traced back to source

-- Tools: Apache Atlas, Collibra, dbt lineage graph
-- Each row in source-to-target mapping = one test case

-- STEP 1: Quantify the mismatch
SELECT COUNT(*) AS mismatched FROM source_db.orders s
JOIN warehouse.fact_orders t ON s.order_id = t.order_id
WHERE s.amount != t.amount;

-- STEP 2: Check ETL job logs for errors, rejected records

-- STEP 3: Check staging — isolate which phase failed
SELECT s.order_id, s.amount AS source, stg.amount AS staging
FROM source_db.orders s
JOIN staging.orders stg ON s.order_id = stg.order_id
WHERE s.amount != stg.amount;
-- Correct in staging but wrong in target = Load issue
-- Wrong in staging = Extract or Transform issue

-- STEP 4: Validate transformation logic on sample records
-- STEP 5: Check timing — source changing during extraction?
-- STEP 6: Check types — FLOAT vs DECIMAL, VARCHAR truncation

-- STEP 1: Check scheduler — which task failed? Upstream OK?

-- STEP 2: Read error logs — common categories:
-- Connection: Source DB unreachable, credentials expired
-- Data: Constraint violation, type mismatch
-- Resource: Out of memory, disk full, timeout
-- Permission: User lacks access to source/target

-- STEP 3: Check source — maintenance? Schema change? Network?
-- STEP 4: Check target — table locked? Disk space?
-- STEP 5: Check volume — unexpected spike causes timeouts
-- STEP 6: Fix root cause, rerun, validate data
-- STEP 7: Add monitoring alerts to prevent recurrence