calculation

gistfile1.txt

// Recalculating BigQuery costs with adjusted ratios for Silver and Gold layers
// Using industry high-end ratios instead of the conservative ratios

// BigQuery pricing constants
const STORAGE_PRICE_PER_TB = 20; // $20 per TB per month for active storage
const STORAGE_PRICE_PER_GB = STORAGE_PRICE_PER_TB / 1024; // ~$0.0195 per GB
const COMPUTE_PRICE_PER_TB = 5; // $5 per TB for on-demand queries
const COMPUTE_PRICE_PER_GB = COMPUTE_PRICE_PER_TB / 1024; // ~$0.0049 per GB

// Original layer sizes from input
const originalLayers = [
  { name: "Bronze Layer", storage: 9, unit: "TB" },
  { name: "Silver Layer", storage: 600, unit: "GB" },
  { name: "Gold Layer", storage: 50, unit: "GB" }
];

// Adjust Silver and Gold layer sizes to higher-end industry ratios
// Industry standard ratios (high-end):
// Bronze → Silver: 20% (high-end of 5-20% range)
// Silver → Gold: 15% (high-end of 5-15% range)

// Calculate new layer sizes
const bronzeLayerGB = originalLayers[0].storage * 1024; // Convert 9TB to GB
const newSilverLayerGB = bronzeLayerGB * 0.20; // 20% of Bronze
const newGoldLayerGB = newSilverLayerGB * 0.15; // 15% of Silver

// Create adjusted layer sizes
const adjustedLayers = [
  { name: "Bronze Layer", storage: originalLayers[0].storage, unit: "TB" },
  { name: "Silver Layer", storage: Math.round(newSilverLayerGB), unit: "GB" },
  { name: "Gold Layer", storage: Math.round(newGoldLayerGB), unit: "GB" }
];

// Display original vs adjusted layer sizes
console.log("LAYER SIZE COMPARISON: ORIGINAL VS ADJUSTED");
console.log("===========================================");
console.log("\nOriginal Layer Sizes:");
originalLayers.forEach(layer => {
  console.log(`${layer.name}: ${layer.storage} ${layer.unit}`);
});

console.log("\nAdjusted Layer Sizes (Higher-End Industry Ratios):");
adjustedLayers.forEach(layer => {
  console.log(`${layer.name}: ${layer.storage} ${layer.unit}`);
});

// Calculate transformation ratios for adjusted sizes
const adjustedBronzeToSilverRatio = newSilverLayerGB / bronzeLayerGB;
const adjustedSilverToGoldRatio = newGoldLayerGB / newSilverLayerGB;
const adjustedBronzeToGoldRatio = newGoldLayerGB / bronzeLayerGB;

console.log("\nAdjusted Transformation Ratios:");
console.log(`Bronze → Silver: ${adjustedBronzeToSilverRatio.toFixed(4)} (${(adjustedBronzeToSilverRatio * 100).toFixed(2)}%)`);
console.log(`Silver → Gold: ${adjustedSilverToGoldRatio.toFixed(4)} (${(adjustedSilverToGoldRatio * 100).toFixed(2)}%)`);
console.log(`Bronze → Gold (end-to-end): ${adjustedBronzeToGoldRatio.toFixed(6)} (${(adjustedBronzeToGoldRatio * 100).toFixed(4)}%)`);

// Recalculate storage costs with adjusted sizes
const adjustedStorageCosts = adjustedLayers.map(layer => {
  let costPerUnit = layer.unit === "TB" ? STORAGE_PRICE_PER_TB : STORAGE_PRICE_PER_GB;
  return {
    name: layer.name,
    size: layer.storage,
    unit: layer.unit,
    monthlyCost: layer.storage * costPerUnit
  };
});

// Calculate transformation costs for adjusted sizes
// Transformation processing ratios remain the same as before
const bronzeReadRatio = 0.8; // Reading 80% of bronze for transformation
const silverReadRatio = 0.9; // Reading 90% of silver for transformation
const daysInMonth = 30;

// Bronze to Silver transform costs (adjusted)
const bronzeLayerSizeTB = adjustedLayers[0].storage;
const bronzeToSilverReadCostPerDay = bronzeLayerSizeTB * bronzeReadRatio * COMPUTE_PRICE_PER_TB;
const bronzeToSilverReadCostPerMonth = bronzeToSilverReadCostPerDay * daysInMonth;

// Silver to Gold transform costs (adjusted)
const silverLayerSizeGB = adjustedLayers[1].storage;
const silverToGoldReadCostPerDay = silverLayerSizeGB * silverReadRatio * COMPUTE_PRICE_PER_GB;
const silverToGoldReadCostPerMonth = silverToGoldReadCostPerDay * daysInMonth;

// Transformation logic costs (additional processing beyond just reading)
const transformationComplexityFactor = 0.3; // 30% additional cost for transformation logic
const bronzeToSilverTransformCost = bronzeToSilverReadCostPerMonth * transformationComplexityFactor;
const silverToGoldTransformCost = silverToGoldReadCostPerMonth * transformationComplexityFactor;

// Total transformation costs (adjusted)
const adjustedTransformationCost = bronzeToSilverReadCostPerMonth + bronzeToSilverTransformCost + 
                                  silverToGoldReadCostPerMonth + silverToGoldTransformCost;

// Basic compute costs (unchanged)
const basicComputeCosts = [
  { name: "Bronze Layer", size: 1, unit: "TB", monthlyCost: 1 * COMPUTE_PRICE_PER_TB },
  { name: "Silver Layer", size: 600, unit: "GB", monthlyCost: 600 * COMPUTE_PRICE_PER_GB },
  { name: "Gold Layer", size: 10, unit: "GB", monthlyCost: 10 * COMPUTE_PRICE_PER_GB }
];

// Update basic compute costs for Silver and Gold
const adjustedBasicComputeCosts = [
  basicComputeCosts[0], // Bronze remains the same
  { name: "Silver Layer", size: silverLayerSizeGB * 0.9, unit: "GB", monthlyCost: silverLayerSizeGB * 0.9 * COMPUTE_PRICE_PER_GB }, // Adjust Silver compute
  { name: "Gold Layer", size: newGoldLayerGB * 0.2, unit: "GB", monthlyCost: newGoldLayerGB * 0.2 * COMPUTE_PRICE_PER_GB } // Adjust Gold compute
];

// Power BI costs - adjusted for new Gold layer size
const powerBIUsers = 120;
const workingDaysPerMonth = 22;

// 1. Base ingestion - daily refresh of reports
const powerBIIngestionRatio = 0.5; // 50% of gold layer
const adjustedGoldLayerSizeGB = adjustedLayers[2].storage;
const adjustedPowerBIIngestionSizeGB = adjustedGoldLayerSizeGB * powerBIIngestionRatio;
const adjustedPowerBIIngestionCostPerDay = adjustedPowerBIIngestionSizeGB * COMPUTE_PRICE_PER_GB;
const adjustedPowerBIIngestionCostPerMonth = adjustedPowerBIIngestionCostPerDay * daysInMonth;

// 2. User query impact
const activeUsersRatio = 0.8; // 80% of users active daily
const activeUsersPerDay = powerBIUsers * activeUsersRatio;
const reportsPerUserPerDay = 5; // Average reports per user per day
const avgDataScannedPerReportRatio = 0.2; // Each report scans ~20% of gold layer
const adjustedAvgDataScannedPerReportGB = adjustedGoldLayerSizeGB * avgDataScannedPerReportRatio;
const adjustedDailyQueryDataGB = activeUsersPerDay * reportsPerUserPerDay * adjustedAvgDataScannedPerReportGB;
const adjustedMonthlyQueryDataGB = adjustedDailyQueryDataGB * workingDaysPerMonth;
const adjustedUserQueryCostPerMonth = adjustedMonthlyQueryDataGB * COMPUTE_PRICE_PER_GB;

// Total PowerBI costs (adjusted)
const adjustedTotalPowerBICost = adjustedPowerBIIngestionCostPerMonth + adjustedUserQueryCostPerMonth;

// Ad-hoc query costs (unchanged)
const adhocQuerySize = 3; // 3TB
const adhocQueryCost = adhocQuerySize * COMPUTE_PRICE_PER_TB;

// Calculate total costs with adjusted sizes
let adjustedTotalStorageCost = 0;
adjustedStorageCosts.forEach(item => { adjustedTotalStorageCost += item.monthlyCost; });

let adjustedTotalBasicComputeCost = 0;
adjustedBasicComputeCosts.forEach(item => { adjustedTotalBasicComputeCost += item.monthlyCost; });

const adjustedTotalComputeCost = adjustedTotalBasicComputeCost + adjustedTransformationCost + 
                                adjustedTotalPowerBICost + adhocQueryCost;
const adjustedTotalMonthlyCost = adjustedTotalStorageCost + adjustedTotalComputeCost;

// Display cost breakdown with adjusted sizes
console.log("\nBIGQUERY DATA LAKE COST BREAKDOWN (ADJUSTED LAYER RATIOS)");
console.log("========================================================");

console.log("\nSTORAGE COSTS:");
adjustedStorageCosts.forEach(item => {
  console.log(`${item.name} (${item.size} ${item.unit}): $${item.monthlyCost.toFixed(2)}`);
});
console.log(`Total Storage: $${adjustedTotalStorageCost.toFixed(2)}`);

console.log("\nBASIC COMPUTE COSTS:");
adjustedBasicComputeCosts.forEach(item => {
  console.log(`${item.name} (${item.size.toFixed(1)} ${item.unit}): $${item.monthlyCost.toFixed(2)}`);
});
console.log(`Total Basic Compute: $${adjustedTotalBasicComputeCost.toFixed(2)}`);

console.log("\nTRANSFORMATION COSTS:");
console.log(`Bronze to Silver - Data Reading (${(bronzeLayerSizeTB * bronzeReadRatio).toFixed(1)} TB × ${daysInMonth} days): $${bronzeToSilverReadCostPerMonth.toFixed(2)}`);
console.log(`Bronze to Silver - Processing Logic: $${bronzeToSilverTransformCost.toFixed(2)}`);
console.log(`Silver to Gold - Data Reading (${(silverLayerSizeGB * silverReadRatio).toFixed(1)} GB × ${daysInMonth} days): $${silverToGoldReadCostPerMonth.toFixed(2)}`);
console.log(`Silver to Gold - Processing Logic: $${silverToGoldTransformCost.toFixed(2)}`);
console.log(`Total Transformation: $${adjustedTransformationCost.toFixed(2)}`);

console.log("\nPOWER BI COSTS:");
console.log(`Data Ingestion (${adjustedPowerBIIngestionSizeGB.toFixed(1)} GB/day × ${daysInMonth} days): $${adjustedPowerBIIngestionCostPerMonth.toFixed(2)}`);
console.log(`User Queries (${activeUsersPerDay.toFixed(0)} active users × ${reportsPerUserPerDay} reports/day × ${adjustedAvgDataScannedPerReportGB.toFixed(1)} GB/report × ${workingDaysPerMonth} days): $${adjustedUserQueryCostPerMonth.toFixed(2)}`);
console.log(`Total Power BI: $${adjustedTotalPowerBICost.toFixed(2)}`);

console.log("\nAD-HOC QUERY COSTS:");
console.log(`Data Scanning - Adhoc (${adhocQuerySize} TB): $${adhocQueryCost.toFixed(2)}`);

console.log("\nTOTAL COSTS:");
console.log(`Monthly: $${adjustedTotalMonthlyCost.toFixed(2)}`);
console.log(`Yearly: $${(adjustedTotalMonthlyCost * 12).toFixed(2)}`);

// Compare original vs adjusted costs
console.log("\nCOST COMPARISON: ORIGINAL VS ADJUSTED RATIOS");
console.log("===========================================");

// Original costs from previous calculations
const originalStorageCost = 192.70;
const originalTransformCost = 1506.83;
const originalPowerBICost = 519.29;
const originalAdhocCost = 15.00;
const originalBasicComputeCost = 7.98;
const originalTotalCost = 2241.79;

console.log(`Storage: $${originalStorageCost.toFixed(2)} → $${adjustedTotalStorageCost.toFixed(2)} (${adjustedTotalStorageCost > originalStorageCost ? "+" : ""}${(adjustedTotalStorageCost - originalStorageCost).toFixed(2)})`);
console.log(`Transformations: $${originalTransformCost.toFixed(2)} → $${adjustedTransformationCost.toFixed(2)} (${adjustedTransformationCost > originalTransformCost ? "+" : ""}${(adjustedTransformationCost - originalTransformCost).toFixed(2)})`);
console.log(`Power BI: $${originalPowerBICost.toFixed(2)} → $${adjustedTotalPowerBICost.toFixed(2)} (${adjustedTotalPowerBICost > originalPowerBICost ? "+" : ""}${(adjustedTotalPowerBICost - originalPowerBICost).toFixed(2)})`);
console.log(`Basic Compute: $${originalBasicComputeCost.toFixed(2)} → $${adjustedTotalBasicComputeCost.toFixed(2)} (${adjustedTotalBasicComputeCost > originalBasicComputeCost ? "+" : ""}${(adjustedTotalBasicComputeCost - originalBasicComputeCost).toFixed(2)})`);
console.log(`Ad-hoc Queries: $${originalAdhocCost.toFixed(2)} → $${adhocQueryCost.toFixed(2)} (${adhocQueryCost > originalAdhocCost ? "+" : ""}${(adhocQueryCost - originalAdhocCost).toFixed(2)})`);
console.log(`TOTAL: $${originalTotalCost.toFixed(2)} → $${adjustedTotalMonthlyCost.toFixed(2)} (${adjustedTotalMonthlyCost > originalTotalCost ? "+" : ""}${(adjustedTotalMonthlyCost - originalTotalCost).toFixed(2)})`);
console.log(`Percentage Change: ${(((adjustedTotalMonthlyCost - originalTotalCost) / originalTotalCost) * 100).toFixed(2)}%`);


// Result

// LAYER SIZE COMPARISON: ORIGINAL VS ADJUSTED
// ===========================================
// 
// Original Layer Sizes:
// Bronze Layer: 9 TB
// Silver Layer: 600 GB
// Gold Layer: 50 GB
// 
// Adjusted Layer Sizes (Higher-End Industry Ratios):
// Bronze Layer: 9 TB
// Silver Layer: 1843 GB
// Gold Layer: 276 GB
// 
// Adjusted Transformation Ratios:
// Bronze → Silver: 0.2000 (20.00%)
// Silver → Gold: 0.1500 (15.00%)
// Bronze → Gold (end-to-end): 0.030000 (3.0000%)
// 
// BIGQUERY DATA LAKE COST BREAKDOWN (ADJUSTED LAYER RATIOS)
// ========================================================
// 
// STORAGE COSTS:
// Bronze Layer (9 TB): $180.00
// Silver Layer (1843 GB): $36.00
// Gold Layer (276 GB): $5.39
// Total Storage: $221.39
// 
// BASIC COMPUTE COSTS:
// Bronze Layer (1.0 TB): $5.00
// Silver Layer (1658.7 GB): $8.10
// Gold Layer (55.3 GB): $0.27
// Total Basic Compute: $13.37
// 
// TRANSFORMATION COSTS:
// Bronze to Silver - Data Reading (7.2 TB × 30 days): $1080.00
// Bronze to Silver - Processing Logic: $324.00
// Silver to Gold - Data Reading (1658.7 GB × 30 days): $242.97
// Silver to Gold - Processing Logic: $72.89
// Total Transformation: $1719.87
// 
// POWER BI COSTS:
// Data Ingestion (138.0 GB/day × 30 days): $20.21
// User Queries (96 active users × 5 reports/day × 55.2 GB/report × 22 days): $2846.25
// Total Power BI: $2866.46
// 
// AD-HOC QUERY COSTS:
// Data Scanning - Adhoc (3 TB): $15.00
// 
// TOTAL COSTS:
// Monthly: $4836.09
// Yearly: $58033.04
// 
// COST COMPARISON: ORIGINAL VS ADJUSTED RATIOS
// ===========================================
// Storage: $192.70 → $221.39 (+28.69)
// Transformations: $1506.83 → $1719.87 (+213.04)
// Power BI: $519.29 → $2866.46 (+2347.17)
// Basic Compute: $7.98 → $13.37 (+5.39)
// Ad-hoc Queries: $15.00 → $15.00 (0.00)
// TOTAL: $2241.79 → $4836.09 (+2594.30)