Data Types: Intermediate

Where pennies vanish and NULLs defy

Where pennies vanish and NULLs defy

Category

SQL

Difficulty

intermediate

Duration

19 minutes

Challenges

0 hands-on challenges

Topics covered: BOOLEAN and NULL logic, DECIMAL precision and scale, TIMESTAMP vs TIMESTAMP WITH TIME ZONE, Time zones and UTC handling, TRY_CAST and implicit casts

Lesson Sections

1. BOOLEAN and NULL logic

   Truth Table Surprises Boolean Filtering Patterns

2. DECIMAL precision and scale (concepts: sqlDecimalType)

   Common Configurations Avoiding Numeric Pitfalls Integer division and floating-point approximation cause subtle bugs in calculations. Which type would you use for a financial total column? Choosing the right numeric type is critical for financial accuracy.

3. TIMESTAMP vs TIMESTAMP WITH TIME ZONE (concepts: sqlTimestampType)

   SQL provides two primary temporal types that seem similar but solve fundamentally different problems. Choosing the wrong one causes bugs that only appear when users span multiple time zones or when daylight saving time transitions happen. Choosing between these types depends on whether your value represents a calendar label or a specific moment in time. Choosing Temporal Types Select the right temporal type based on whether you need time precision and timezone awareness.

4. Time zones and UTC handling (concepts: sqlTimezones)

   Time zones are one of the most error-prone areas in data engineering. A timestamp without timezone context is ambiguous. Is "2025-03-15 14:00:00" in UTC? Eastern Time? The server's timezone? Incorrect assumptions lead to data shifted by hours, causing wrong aggregations and confused stakeholders. Timezone Best Practices Following these guidelines helps avoid timezone-related bugs in global applications. Always use full IANA timezone names like America/New_York rather than abbreviations like EST,

5. TRY_CAST and implicit casts (concepts: sqlTryCast)

   Implicit Type Coercion SQL automatically converts between compatible types in certain situations. This "implicit conversion" happens silently, which can be both convenient and dangerous. Validating and casting data at ingestion time is far more efficient than casting at every query. Store data in the correct type once during ETL so all subsequent reads pay no conversion cost.