In BigQuery SQL, FLOAT (FLOAT64) and NUMERIC data types both handle decimals but differ in precision and performance.

FLOAT (FLOAT64)

• Type: Double-precision floating-point number.
• Pros:
  • Can represent a wide range of values, both large and very small.
  • Uses 8 logical bytes.
  • Faster calculations.
  • Supports literals for NaN, minus/plus infinity.
• Cons:
  • Approximate data type, leading to potential rounding errors.
• Use Cases:
  • Suitable for queries tolerating small differences (e.g., average chocolate consumption per capita).
  • Ideal for scientific calculations involving very large numbers.

NUMERIC

• Type: Fixed-point decimal type, up to 38 digits and 9 decimal places (alias for DECIMAL).
• Pros:
  • Exact storage with no rounding errors.
• Cons:
  • Uses 16 logical bytes.
  • Slower calculations.
• Use Cases:
  • Essential where precision is critical (e.g., financial calculations, space missions).

Additional Note

• BIGNUMERIC (BIGDECIMAL): For an even larger range, using 32 logical bytes.