Comprehensive comparison for Database technology in Software Development applications

-- Production-ready Redshift data warehouse schema and ETL pattern
-- Use case: Software development metrics tracking system
-- Tracks deployments, code commits, build performance, and developer productivity

-- Create schema for software development metrics
CREATE SCHEMA IF NOT EXISTS dev_metrics;

-- Dimension table for repositories
CREATE TABLE IF NOT EXISTS dev_metrics.dim_repository (
    repository_id INTEGER IDENTITY(1,1) PRIMARY KEY,
    repository_name VARCHAR(255) NOT NULL,
    repository_url VARCHAR(500),
    team_name VARCHAR(100),
    tech_stack VARCHAR(100),
    created_date TIMESTAMP DEFAULT GETDATE(),
    is_active BOOLEAN DEFAULT TRUE
)
DISTSTYLE KEY
DISTKEY (repository_id)
SORTKEY (repository_name);

-- Dimension table for developers
CREATE TABLE IF NOT EXISTS dev_metrics.dim_developer (
    developer_id INTEGER IDENTITY(1,1) PRIMARY KEY,
    developer_email VARCHAR(255) NOT NULL UNIQUE,
    developer_name VARCHAR(255),
    team_name VARCHAR(100),
    hire_date DATE,
    seniority_level VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE
)
DISTSTYLE ALL;

-- Fact table for deployments
CREATE TABLE IF NOT EXISTS dev_metrics.fact_deployment (
    deployment_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    repository_id INTEGER NOT NULL,
    developer_id INTEGER NOT NULL,
    deployment_timestamp TIMESTAMP NOT NULL,
    environment VARCHAR(50) NOT NULL,
    deployment_status VARCHAR(50) NOT NULL,
    build_duration_seconds INTEGER,
    lines_of_code_changed INTEGER,
    files_changed INTEGER,
    deployment_type VARCHAR(50),
    rollback_flag BOOLEAN DEFAULT FALSE,
    error_message VARCHAR(5000),
    FOREIGN KEY (repository_id) REFERENCES dev_metrics.dim_repository(repository_id),
    FOREIGN KEY (developer_id) REFERENCES dev_metrics.dim_developer(developer_id)
)
DISTSTYLE KEY
DISTKEY (repository_id)
SORTKEY (deployment_timestamp);

-- Materialized view for deployment success rates
CREATE MATERIALIZED VIEW dev_metrics.mv_deployment_metrics AS
SELECT 
    r.repository_name,
    r.team_name,
    d.developer_name,
    DATE_TRUNC('day', f.deployment_timestamp) AS deployment_date,
    f.environment,
    COUNT(*) AS total_deployments,
    SUM(CASE WHEN f.deployment_status = 'SUCCESS' THEN 1 ELSE 0 END) AS successful_deployments,
    SUM(CASE WHEN f.rollback_flag = TRUE THEN 1 ELSE 0 END) AS rollback_count,
    AVG(f.build_duration_seconds) AS avg_build_duration,
    SUM(f.lines_of_code_changed) AS total_loc_changed,
    ROUND(100.0 * SUM(CASE WHEN f.deployment_status = 'SUCCESS' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct
FROM 
    dev_metrics.fact_deployment f
    INNER JOIN dev_metrics.dim_repository r ON f.repository_id = r.repository_id
    INNER JOIN dev_metrics.dim_developer d ON f.developer_id = d.developer_id
WHERE 
    f.deployment_timestamp >= DATEADD(month, -6, GETDATE())
GROUP BY 
    r.repository_name,
    r.team_name,
    d.developer_name,
    DATE_TRUNC('day', f.deployment_timestamp),
    f.environment;

-- Stored procedure for incremental ETL load
CREATE OR REPLACE PROCEDURE dev_metrics.sp_load_deployment_data(
    p_start_date TIMESTAMP,
    p_end_date TIMESTAMP
)
AS $$
BEGIN
    -- Create temporary staging table
    CREATE TEMP TABLE staging_deployments (
        repository_name VARCHAR(255),
        developer_email VARCHAR(255),
        deployment_timestamp TIMESTAMP,
        environment VARCHAR(50),
        deployment_status VARCHAR(50),
        build_duration_seconds INTEGER,
        lines_of_code_changed INTEGER,
        files_changed INTEGER
    );
    
    -- Insert data with error handling and validation
    INSERT INTO dev_metrics.fact_deployment (
        repository_id,
        developer_id,
        deployment_timestamp,
        environment,
        deployment_status,
        build_duration_seconds,
        lines_of_code_changed,
        files_changed
    )
    SELECT 
        r.repository_id,
        d.developer_id,
        s.deployment_timestamp,
        COALESCE(s.environment, 'UNKNOWN'),
        COALESCE(s.deployment_status, 'UNKNOWN'),
        CASE WHEN s.build_duration_seconds < 0 THEN NULL ELSE s.build_duration_seconds END,
        CASE WHEN s.lines_of_code_changed < 0 THEN NULL ELSE s.lines_of_code_changed END,
        CASE WHEN s.files_changed < 0 THEN NULL ELSE s.files_changed END
    FROM 
        staging_deployments s
        INNER JOIN dev_metrics.dim_repository r ON s.repository_name = r.repository_name
        INNER JOIN dev_metrics.dim_developer d ON s.developer_email = d.developer_email
    WHERE 
        s.deployment_timestamp BETWEEN p_start_date AND p_end_date
        AND r.is_active = TRUE
        AND d.is_active = TRUE;
    
    -- Refresh materialized view
    REFRESH MATERIALIZED VIEW dev_metrics.mv_deployment_metrics;
    
    -- Vacuum and analyze for performance
    VACUUM dev_metrics.fact_deployment;
    ANALYZE dev_metrics.fact_deployment;
    
    DROP TABLE staging_deployments;
END;
$$ LANGUAGE plpgsql;