Differences Between FLOAT (FLOAT64) vs NUMERIC Data Types in BigQuery Explained

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.