🏢 Warehouse Setup & Configuration¶

-- Bad: Over-provisioned types
CREATE TABLE players_bad (
    player_id VARCHAR(500),        -- Too large
    balance DECIMAL(38, 10),       -- Excessive precision
    last_login VARCHAR(100)        -- Should be datetime
);

-- Good: Right-sized types
CREATE TABLE players_good (
    player_id INT,                 -- Appropriate for ID
    balance DECIMAL(18, 2),        -- Currency precision
    last_login DATETIME2(0)        -- Proper type, second precision
);

-- Create statistics on specific column
CREATE STATISTICS stat_player_id
ON fact_daily_slot_performance (player_id);

-- Create with full scan for accuracy
CREATE STATISTICS stat_machine_id
ON fact_daily_slot_performance (machine_id)
WITH FULLSCAN;

-- Update statistics after large data loads
UPDATE STATISTICS fact_daily_slot_performance;

-- Update specific statistics
UPDATE STATISTICS fact_daily_slot_performance (stat_player_id)
WITH FULLSCAN;

-- List all statistics on a table
SELECT
    s.name AS statistics_name,
    c.name AS column_name,
    s.auto_created,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats s
JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
JOIN sys.columns c ON sc.column_id = c.column_id AND sc.object_id = c.object_id
WHERE s.object_id = OBJECT_ID('fact_daily_slot_performance');

-- View statistics details
DBCC SHOW_STATISTICS ('fact_daily_slot_performance', '_WA_Sys_00000001');

-- Good: Filter before join
SELECT f.*, d.full_date
FROM fact_daily_slot_performance f
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.date_key >= 20240101  -- Filter on fact table
  AND f.total_bets > 1000;

-- Less optimal: Filter after join
SELECT f.*, d.full_date
FROM fact_daily_slot_performance f
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.full_date >= '2024-01-01';  -- Filter on dimension

-- Good: Specific columns
SELECT machine_id, SUM(total_bets), SUM(total_wins)
FROM fact_daily_slot_performance
GROUP BY machine_id;

-- Bad: SELECT *
SELECT *
FROM fact_daily_slot_performance;  -- Avoid in production

-- Ensure data type parity in joins
SELECT f.*, m.machine_type
FROM fact_daily_slot_performance f
JOIN dim_machine m ON f.machine_id = m.machine_id  -- Both INT
-- Avoid: ON CAST(f.machine_id AS VARCHAR) = m.machine_id

-- Use COPY INTO for bulk loads
COPY INTO dbo.fact_daily_slot_performance
FROM 'https://storage.blob.core.windows.net/data/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

-- Keep transactions short
BEGIN TRANSACTION;

-- Perform quick operations
INSERT INTO fact_daily_slot_performance
SELECT * FROM stg_daily_performance
WHERE load_date = CAST(GETDATE() AS DATE);

COMMIT TRANSACTION;

-- Update statistics after bulk load
UPDATE STATISTICS fact_daily_slot_performance WITH FULLSCAN;

-- Active queries
SELECT
    session_id,
    command,
    status,
    total_elapsed_time,
    cpu_time,
    logical_reads
FROM sys.dm_exec_requests
WHERE status = 'running';

-- Query history (use DMVs)
SELECT TOP 100
    query_hash,
    total_elapsed_time,
    execution_count,
    total_worker_time,
    total_logical_reads
FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;

-- Current locks
SELECT
    request_session_id,
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

UPDATE dim_player
SET email = source.email,
    phone = source.phone
FROM dim_player d
JOIN stg_player_updates source ON d.player_id = source.player_id;

-- End current record
UPDATE dim_player
SET is_current = 0, end_date = GETDATE()
WHERE player_id IN (SELECT player_id FROM stg_player_changes)
  AND is_current = 1;

-- Insert new record
INSERT INTO dim_player (player_id, email, phone, start_date, is_current)
SELECT player_id, email, phone, GETDATE(), 1
FROM stg_player_changes;

-- MERGE for upsert
MERGE INTO fact_daily_slot_performance AS target
USING stg_daily_performance AS source
ON target.performance_id = source.performance_id
WHEN MATCHED THEN
    UPDATE SET
        total_bets = source.total_bets,
        total_wins = source.total_wins,
        updated_at = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (performance_id, date_key, machine_id, total_bets, total_wins)
    VALUES (source.performance_id, source.date_key, source.machine_id,
            source.total_bets, source.total_wins);