Data Lake Partitioning

{ "metadata": { "name": "Data Lake Partitioning", "description": "Full factorial of partitioning strategy, file format, and compaction for query speed and storage cost" }, "factors": [ { "name": "partition_cols", "levels": [ "date", "date_hour", "date_hour_region" ], "type": "categorical", "unit": "" }, { "name": "file_format", "levels": [ "parquet", "orc" ], "type": "categorical", "unit": "" }, { "name": "target_file_mb", "levels": [ "64", "256" ], "type": "continuous", "unit": "MB" }, { "name": "z_order", "levels": [ "off", "on" ], "type": "categorical", "unit": "" } ], "fixed_factors": { "table_size_tb": "5", "retention_days": "90" }, "responses": [ { "name": "query_time_s", "optimize": "minimize", "unit": "s" }, { "name": "storage_cost_month", "optimize": "minimize", "unit": "USD" } ], "settings": { "operation": "full_factorial", "test_script": "use_cases/38_data_lake_partitioning/sim.sh" } }

============================================================ MULTI-OBJECTIVE OPTIMIZATION Method: Derringer-Suich Desirability Function ============================================================ Overall desirability: D = 0.7734 Response Weight Desirability Predicted Direction --------------------------------------------------------------------- query_time_s 1.5 0.6721 9.30 s ↓ storage_cost_month 1.0 0.9545 182.00 USD ↓ Recommended settings: partition_cols = date_hour_region file_format = orc target_file_mb = 64 MB z_order = on (from observed run #23) Trade-off summary: query_time_s: 9.30 (best observed: 2.90, sacrifice: +6.40) storage_cost_month: 182.00 (best observed: 182.00, sacrifice: +0.00) Model quality: query_time_s: R² = 0.1417 (linear) storage_cost_month: R² = 0.2973 (linear) Top 3 observed runs by overall desirability: 1. Run #23 (D=0.7734): partition_cols=date_hour_region, file_format=orc, target_file_mb=64, z_order=on 2. Run #20 (D=0.7635): partition_cols=date_hour_region, file_format=orc, target_file_mb=256, z_order=on 3. Run #1 (D=0.7469): partition_cols=date, file_format=orc, target_file_mb=64, z_order=on

=== Main Effects: query_time_s === Factor Effect Std Error % Contribution -------------------------------------------------------------- partition_cols 5.0875 1.1045 57.1% file_format 1.5000 1.1045 16.8% z_order -1.3333 1.1045 15.0% target_file_mb 0.9833 1.1045 11.0% === ANOVA Table: query_time_s === Source DF SS MS F p-value ----------------------------------------------------------------------------- partition_cols 2 114.0775 57.0388 1.976 0.1731 file_format 1 13.5000 13.5000 0.468 0.5045 target_file_mb 1 5.8017 5.8017 0.201 0.6603 z_order 1 10.6667 10.6667 0.370 0.5523 file_format*target_file_mb 1 32.2017 32.2017 1.116 0.3076 file_format*z_order 1 64.0267 64.0267 2.218 0.1571 target_file_mb*z_order 1 0.2017 0.2017 0.007 0.9345 Error 15 432.9242 28.8616 Total 23 673.4000 29.2783 === Interaction Effects: query_time_s === Factor A Factor B Interaction % Contribution ------------------------------------------------------------------------ file_format z_order -3.2667 56.6% file_format target_file_mb 2.3167 40.2% target_file_mb z_order -0.1833 3.2% === Summary Statistics: query_time_s === partition_cols: Level N Mean Std Min Max ------------------------------------------------------------ date 8 13.1375 5.9969 5.5000 23.5000 date_hour 8 14.2750 3.9326 8.6000 20.3000 date_hour_region 8 9.1875 5.3362 2.9000 20.5000 file_format: Level N Mean Std Min Max ------------------------------------------------------------ orc 12 11.4500 5.8520 2.9000 23.5000 parquet 12 12.9500 5.0739 4.9000 20.5000 target_file_mb: Level N Mean Std Min Max ------------------------------------------------------------ 256 12 11.7083 4.9471 2.9000 20.3000 64 12 12.6917 6.0181 4.9000 23.5000 z_order: Level N Mean Std Min Max ------------------------------------------------------------ off 12 12.8667 4.2455 6.1000 20.5000 on 12 11.5333 6.4980 2.9000 23.5000 === Main Effects: storage_cost_month === Factor Effect Std Error % Contribution -------------------------------------------------------------- partition_cols 31.8750 6.2977 54.4% file_format 13.0833 6.2977 22.3% z_order -11.0833 6.2977 18.9% target_file_mb 2.5833 6.2977 4.4% === ANOVA Table: storage_cost_month === Source DF SS MS F p-value ----------------------------------------------------------------------------- partition_cols 2 4073.2500 2036.6250 1.911 0.1823 file_format 1 1027.0417 1027.0417 0.963 0.3419 target_file_mb 1 40.0417 40.0417 0.038 0.8489 z_order 1 737.0417 737.0417 0.691 0.4187 file_format*target_file_mb 1 22.0417 22.0417 0.021 0.8876 file_format*z_order 1 2.0417 2.0417 0.002 0.9657 target_file_mb*z_order 1 1.0417 1.0417 0.001 0.9755 Error 15 15990.1250 1066.0083 Total 23 21892.6250 951.8533 === Interaction Effects: storage_cost_month === Factor A Factor B Interaction % Contribution ------------------------------------------------------------------------ file_format target_file_mb -1.9167 65.7% file_format z_order -0.5833 20.0% target_file_mb z_order -0.4167 14.3% === Summary Statistics: storage_cost_month === partition_cols: Level N Mean Std Min Max ------------------------------------------------------------ date 8 264.6250 25.0196 237.0000 312.0000 date_hour 8 232.7500 38.9129 182.0000 281.0000 date_hour_region 8 250.0000 20.1353 215.0000 271.0000 file_format: Level N Mean Std Min Max ------------------------------------------------------------ orc 12 242.5833 37.6430 182.0000 312.0000 parquet 12 255.6667 21.9061 220.0000 283.0000 target_file_mb: Level N Mean Std Min Max ------------------------------------------------------------ 256 12 247.8333 26.4638 185.0000 273.0000 64 12 250.4167 35.8645 182.0000 312.0000 z_order: Level N Mean Std Min Max ------------------------------------------------------------ off 12 254.6667 31.0698 182.0000 312.0000 on 12 243.5833 30.9500 185.0000 283.0000

=== Optimization: query_time_s === Direction: minimize Best observed run: #4 partition_cols = date_hour file_format = orc target_file_mb = 64 z_order = on Value: 2.9 RSM Model (linear, R² = 0.0752, Adj R² = -0.1195): Coefficients: intercept +12.2000 partition_cols +1.2000 file_format +0.9833 target_file_mb -0.3917 z_order -0.1750 RSM Model (quadratic, R² = 0.3533, Adj R² = -0.6526): Coefficients: intercept +2.7750 partition_cols +1.2000 file_format +0.9833 target_file_mb -0.3917 z_order -0.1750 partition_cols*file_format -0.0750 partition_cols*target_file_mb +0.4375 partition_cols*z_order +0.4875 file_format*target_file_mb +1.3083 file_format*z_order +2.1583 target_file_mb*z_order +0.7333 partition_cols^2 +1.6500 file_format^2 +2.7750 target_file_mb^2 +2.7750 z_order^2 +2.7750 Curvature analysis: target_file_mb coef=+2.7750 convex (has a minimum) z_order coef=+2.7750 convex (has a minimum) file_format coef=+2.7750 convex (has a minimum) partition_cols coef=+1.6500 convex (has a minimum) Notable interactions: file_format*z_order coef=+2.1583 (synergistic) file_format*target_file_mb coef=+1.3083 (synergistic) target_file_mb*z_order coef=+0.7333 (synergistic) partition_cols*z_order coef=+0.4875 (synergistic) partition_cols*target_file_mb coef=+0.4375 (synergistic) Predicted optimum (from linear model, at observed points): partition_cols = date_hour_region file_format = parquet target_file_mb = 64 z_order = off Predicted value: 14.9500 Surface optimum (via L-BFGS-B, linear model): partition_cols = date file_format = parquet target_file_mb = 256 z_order = on Predicted value: 9.4500 Model quality: Weak fit — consider adding center points or using a different design. Factor importance: 1. partition_cols (effect: 2.8, contribution: 47.9%) 2. file_format (effect: 2.0, contribution: 33.1%) 3. target_file_mb (effect: 0.8, contribution: 13.2%) 4. z_order (effect: -0.3, contribution: 5.9%) === Optimization: storage_cost_month === Direction: minimize Best observed run: #23 partition_cols = date file_format = parquet target_file_mb = 64 z_order = off Value: 182.0 RSM Model (linear, R² = 0.3041, Adj R² = 0.1576): Coefficients: intercept +249.1250 partition_cols +3.7500 file_format -15.6250 target_file_mb -2.6250 z_order +4.1250 RSM Model (quadratic, R² = 0.5619, Adj R² = -0.1195): Coefficients: intercept +63.3438 partition_cols +3.7500 file_format -15.6250 target_file_mb -2.6250 z_order +4.1250 partition_cols*file_format +7.1250 partition_cols*target_file_mb -2.6250 partition_cols*z_order -5.0000 file_format*target_file_mb +3.4583 file_format*z_order -7.2917 target_file_mb*z_order -10.2917 partition_cols^2 -6.3750 file_format^2 +63.3438 target_file_mb^2 +63.3438 z_order^2 +63.3438 Curvature analysis: target_file_mb coef=+63.3438 convex (has a minimum) z_order coef=+63.3438 convex (has a minimum) file_format coef=+63.3438 convex (has a minimum) partition_cols coef=-6.3750 concave (has a maximum) Notable interactions: target_file_mb*z_order coef=-10.2917 (antagonistic) file_format*z_order coef=-7.2917 (antagonistic) partition_cols*file_format coef=+7.1250 (synergistic) partition_cols*z_order coef=-5.0000 (antagonistic) file_format*target_file_mb coef=+3.4583 (synergistic) partition_cols*target_file_mb coef=-2.6250 (antagonistic) Predicted optimum (from linear model, at observed points): partition_cols = date_hour_region file_format = orc target_file_mb = 64 z_order = on Predicted value: 275.2500 Surface optimum (via L-BFGS-B, linear model): partition_cols = date file_format = orc target_file_mb = 256 z_order = off Predicted value: 223.0000 Model quality: Weak fit — consider adding center points or using a different design. Factor importance: 1. file_format (effect: -31.2, contribution: 56.9%) 2. partition_cols (effect: 10.1, contribution: 18.5%) 3. z_order (effect: 8.2, contribution: 15.0%) 4. target_file_mb (effect: 5.2, contribution: 9.6%)