Firebolt DB Release Roundup: Release versions 4.6 to 4.8-> New Functions, Friendlier SQL, and Enhanced Performance

Firebolt has rolled out an arsenal of features in releases 4.6 to 4.8, delivering a range of new functions, SQL extensions, and performance enhancements. With over 25 new functions, few operators, improved support for data ingestion, and performance optimizations, these updates are tailor-made to charge up your data engineering workflows. Here’s a breakdown of the top five updates that will make a real difference in your day-to-day work.

1. Improved Performance with Optimized S3 Reads

Performance is at the core of Firebolt’s design, and this update significantly enhances cold read speeds for managed data. By optimizing the number of Amazon S3 requests required to load data, this update delivers a notable 50% performance improvement for the initial cache warmup, particularly in scenarios involving many small DMLs, such as micro-batching streaming ingests, where the overhead of frequent requests can otherwise slow down query execution.

2. HIPAA Compliance: Unlocking New Opportunities in Healthcare and Beyond

HIPAA compliance is a milestone for Firebolt, opening the door to organizations in healthcare, life sciences, and other highly regulated industries. Firebolt’s adherence to HIPAA standards ensures that your data analytics platform meets stringent privacy and security requirements for any organization dealing with sensitive health data.

This update allows data engineers in healthcare and related sectors to leverage Firebolt’s high-performance analytics platform without compromising compliance or data security. From improving patient care insights to enhancing operational efficiency, HIPAA compliance ensures that regulated data is handled confidently.

3. Filtering by Source File Metadata: Enhanced Data Import Control

The COPY FROM feature continues to evolve with several exciting new capabilities. It now supports filtering by source file metadata, giving you granular control over data ingestion. This enhancement allows data engineers to selectively import data based on metadata criteria—such as file creation dates, sizes, or other file attributes—rather than relying solely on the data content.

It's not new, but one thing that is exciting about COPY FROM is that it also supports schema inference, meaning the target table doesn’t need to exist. Firebolt automatically analyzes the structure of your source files that fit the metadata predicate to infer the schema dynamically, enabling quick ingestion workflows for dynamic and varied datasets.

Example: Filtering by Source File Metadata
Here’s how you can use the new COPY FROM capabilities to import data from files modified within the last three years:

COPY tournament_results 
FROM 
's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/rankings/TournamentID=1/'
LIMIT 50 OFFSET 50
WHERE $source_file_timestamp > NOW() - interval '3 YEARS';

4. Friendlier SQL with “FROM First” Syntax

Firebolt has introduced the FROM first syntax, allowing you to place the FROM clause before the SELECT clause. This style makes SQL more intuitive and efficient to write, especially for complex queries. For example, you can now write:

FROM my_table SELECT column1, SUM(column2) GROUP BY column1;

You can even omit the SELECT clause, writing only:

FROM my_table;

With these friendlier SQL extensions, you can write cleaner code with fewer syntactic constraints.

5. New Functions Galore: 25 Ways to Level Up Your Queries

With 25 new key functions, a few important operators, Firebolt’s functionality has expanded further. This arsenal of functions simplifies complex calculations, data transformations, and parsing, empowering data engineers to perform advanced operations directly within SQL. Here’s a snapshot of what’s new:

• Array Functions: Two new functions, ARRAY_ALL_MATCH and ARRAY_ANY_MATCH, enable you to search arrays across element of the input arrays.
• Statistics Functions: Four new functions STDDEV_SAMP, STDDEV_POP, VAR_SAMP, VAR_POP introduced for calculating standard deviation and variance for both samples and populations. Whether analyzing data distributions or performance metrics, these functions bring added rigor to your work.
• Trigonometric and Mathematical Functions: New additions like RADIANS, SIN, ATAN2, and many more allow for trigonometric calculations. The function set includes essentials like ACOS, ATAN, ASIN, COS, COT, TAN, DEGREES, and PI—ideal for any work involving geometrical data.
• JSON Functions: Firebolt’s new JSON functions—JSON_POINTER_EXTRACT_KEYS, JSON_POINTER_EXTRACT_VALUES, and JSON_POINTER_EXTRACT_TEXT—simplify extracting data from complex JSON structures.
• Pattern Matching Operators: The new ~ and !~ operators, as aliases for REGEXP_LIKE and NOT REGEXP_LIKE, offer a PostgreSQL-style approach to the pattern-matching syntax for quick and accurate text analysis.
• UUID Generation: The new GEN_RANDOM_UUID_TEXT function - generates a random UUIDs.
• Bitwise Operations: BIT_SHIFT_LEFT and BIT_SHIFT_RIGHT provide bitwise shift operations for specific manipulation needs.
• Percentile and Median: Firebolt now supports the PERCENTILE_CONT and MEDIAN functions, making percentile calculations easier. This is great for ranking, scoring, or summarizing data distributions.

Conclusion:
Firebolt DB release versions 4.6, 4.7, and 4.8 introduce powerful enhancements that idata engineering faster, more flexible, and more secure. With many new functions, improved SQL usability, optimized performance, and regulatory compliance, Firebolt remains a top choice for modern data workloads. Explore these updates today and unlock new possibilities in data analytics.

For detailed information on these releases. Read here: https://docs.firebolt.io/Reference/release-notes/release-notes.html 

Call to Action:
Ready to see Firebolt in action? Try Firebolt today for free and experience these features for yourself!