November 2022 Summaries

Trino is a distributed SQL query engine designed for querying large datasets across heterogeneous data sources, supporting Online Analytical Processing (OLAP) workloads such as data warehousing and analytics rather than functioning as a general-purpose relational database. This guide, authored by Przemek Denkiewicz and Michiel De Smet from Starburst, details the setup of Trino with dbt for lakehouse ETL processes, emphasizing the use of Docker and Docker Compose for managing multiple containers needed for this configuration. Key components include Trino for executing distributed queries, along with PostgreSQL for a webshop database, MongoDB for clickstream data, and the Iceberg table format for the lakehouse, all orchestrated through a YAML file to streamline service configuration and management. The document highlights the introduction of the MERGE statement in Trino version 393, enhancing ETL/ELT operations, and notes that Starburst products like Starburst Galaxy and Starburst Enterprise support dbt-trino, enabling incremental models and snapshot features.

The series focuses on building data pipelines using dbt and Trino to process data directly from operational systems, ideal for analytics and data engineers seeking to enhance their workflows. It emphasizes the use of dbt's transformation workflow, which allows teams to develop analytics code using software engineering best practices such as modularity and CI/CD, leveraging SQL proficiency for production-grade pipelines. Trino's capability to federate data from various sources while supporting modern table formats like Iceberg enhances accessibility without data duplication. Iceberg adds ACID-like properties to the lakehouse architecture with features such as partitioning, schema evolution, and time travel, facilitating efficient data querying and management. The series, illustrated through a data-driven e-commerce business use case, offers tutorials on setting up dbt and Trino, utilizing incremental models for data refresh, and includes a GitHub repository for practical exploration.

In the blog post, the authors discuss integrating dbt with Trino to create a data pipeline that enhances data transformation capabilities by leveraging Trino's query federation across diverse data sources. They provide a detailed guide on setting up the dbt-trino adapter, configuring connection profiles, and building initial models. The process involves defining dbt sources for external data objects, which streamlines the management of data locations and facilitates changes. The post highlights the addition of extract and load (EL) functions to dbt's traditional transformation (T) role, enabled by Trino's capabilities, allowing seamless data integration without complex processes. The authors explain how dbt implicitly constructs a Direct Acyclic Graph (DAG) for managing data pipeline steps, and they delve into advanced SQL features like WINDOW operations for sessionization. They emphasize the importance of materialization choices for optimizing performance and introduce testing with dbt to ensure the accuracy of sessionization logic. The use of dbt macros, particularly from dbt-utils and trino-utils packages, is discussed for enhancing functionality. Additionally, the article covers the use of dbt seeds for incorporating static data and the potential for incremental refreshing to improve efficiency.

In the third part of the lakehouse ETL series with dbt and Trino, the article explores the use of incremental models to enhance data refresh speeds by limiting the amount of data loaded into target tables, thereby reducing both loading time and compute costs. Incremental models are initiated by transforming all source data during the first run and subsequently filtering only specific rows for updates, using configurations such as `materialized='incremental'` and unique keys to prevent duplicates. The article discusses several incremental strategies supported by the dbt-trino adapter, including append, delete+insert, and merge, which vary in effectiveness based on data volume and unique key reliability. Key examples include using the append strategy for a clicks dataset and the merge strategy for sessionized clicks, ensuring efficient data handling with dbt's SQL-based transformations. The article also highlights Starburst's role in enhancing Trino features and encourages community engagement through Slack channels for further learning and exploration.

Starburst and AWS Lake Formation are collaborating to tackle the complexities of building and managing data lakes by integrating AWS Lake Formation's capabilities with Starburst Enterprise. This integration, which is available in public preview, aims to enhance data mesh architectures by providing faster, more secure access to distributed datasets, thereby facilitating cost-effective and scalable analytics. AWS Lake Formation simplifies the creation of secure data lakes by offering tools for data import, cataloging, and fine-grained access control, while Starburst adds efficiency in extracting insights from distributed data. This partnership allows organizations to leverage AWS's robust security features and Starburst's data mesh architecture to improve collaboration and accelerate business insights, ultimately turning data into actionable results without compromising security or compliance. The joint effort is expected to benefit enterprises by enabling multi-region analytics and harnessing the full potential of multi-cloud investments.

Apache Iceberg's schema evolution capabilities within Trino allow for flexible and straightforward table modifications, accommodating changing business rules and source systems over time. The Iceberg connector in Trino supports alterations to table names, column additions or renames, and partition adjustments. For instance, while table names can be changed in the metastore without affecting storage locations, columns can be added or renamed using simple SQL commands. Partitioning, initially set by specific columns like month, can be altered to improve performance by changing to day-level granularity, enhancing query efficiency. These schema evolution features are praised for their ease of use and power, marking a significant advancement over previous limitations seen in Hive, and are part of the broader Apache Iceberg blog series that explores various optimizations and maintenance strategies within Starburst's data solutions.

Data products are central to achieving competitive differentiation in the digital landscape, as highlighted by a Gartner survey that underscores the importance of quick value realization from digital investments. These products streamline the delivery of tailored data to the right people, forming a crucial part of a broader Data Mesh strategy aimed at optimizing analytics. Success in the data product race is measured by delivery efficiency, consumer adoption, and business value, with sustained winners demonstrating agility in responding to data-driven opportunities. Optimization efforts should focus on four key levers: cost, performance, risk, and user experience, all integrated within a larger strategic framework involving technology, process, and people. This comprehensive approach ensures that data products not only meet current business needs but also adapt to changing market conditions, providing a competitive edge.

Apache Iceberg, in combination with Trino, enhances data lakehouse capabilities by facilitating database-type updates, deletes, and merges on data stored in immutable object storage like AWS S3. This functionality addresses the challenges of modifying data at the row level, which was problematic in the Hadoop era. Iceberg's DML (Data Manipulation Language) support allows for full transaction logging, ensuring changes are reflected in subsequent queries. Inserts are commonly executed, while updates and deletes maintain read integrity by using snapshots. Merges provide logical operations for updating and inserting data, enabling complex operations like Slowly Changing Dimension (SCD Type 2) and Change Data Capture (CDC). Optimizing the Iceberg tables periodically is recommended to enhance performance by consolidating small files and cleaning up metadata, which is crucial for maintaining efficient query execution in active tables. These features significantly expand the use cases and reliability of data lakehouses across various cloud platforms.

Starburst has introduced full query passthrough via polymorphic table functions, enhancing its query engine's capabilities by allowing queries to utilize the syntax and performance characteristics of underlying systems without migration. This advancement, available from SEP 393, significantly boosts query performance by enabling users to execute queries directly within the source systems, as demonstrated with Teradata where passthrough is twice as fast as the traditional connector pushdown approach. To operationalize this feature with access controls, users need to configure Ranger or SEP Built-In-Access Controls, granting execute privileges or enabling user impersonation. Full query passthrough is particularly advantageous in scenarios requiring specific data source functions or complex data processing, though it is not recommended for all use cases, such as low latency workloads or when write operations are involved. The functionality is supported by multiple connectors, including Teradata JDBC, Snowflake, and Oracle, among others, offering users the opportunity to enhance data performance and flexibility across diverse data environments.

A data lakehouse architecture merges the benefits of data lakes and data warehouses, offering a flexible, cost-effective solution for managing large volumes of data across various formats. It is built on object storage and utilizes the separation of storage and compute principles, which helps prevent issues like data swamps common in traditional data lakes. Starburst's data lakehouse enables organizations to overcome scalability and vendor lock-in constraints by incorporating open table formats, optionality, and native security, allowing for seamless integration with multiple data sources and cloud environments. This architecture supports performance efficiency through features like cluster autoscaling and compatibility with ANSI SQL for data transformation, making it suitable for both interactive and long-running queries. The structure of a data lakehouse involves a three-layer approach—land, structure, and consume—which helps in organizing data efficiently for reporting purposes. Starburst Galaxy, built on the Trino engine, enhances the data lakehouse experience by providing scalable and secure data management solutions while integrating with tools like Great Expectations, dbt, Airflow, and Dagster to ensure robust data pipelines.

Apache Iceberg offers significant performance optimizations for data querying in Trino by improving how data partitioning is handled compared to Hive, particularly through its ability to partition data using simpler and more intuitive methods, such as directly on the timestamp column. This allows for more efficient querying as it reduces the volume of data that needs to be read by narrowing down the scope through partition pruning and metadata utilization, enhancing the performance dramatically. Iceberg's incorporation of file management features like the optimize command helps consolidate numerous small files into larger ones, thus improving query speed, especially in cloud storage systems where performance can be hindered by the presence of many small files. Additionally, Iceberg supports schema evolution, allowing for dynamic changes to partitioning schemes without recreating tables, and includes maintenance features that help manage and clean up old table snapshots and orphan files to ensure optimal system performance. Overall, these enhancements and features make Apache Iceberg a robust choice for managing and querying large datasets efficiently within the Trino ecosystem.

Kamil Bajda-Pawlikowski, Co-Founder and CTO of Starburst, discusses the transformative potential of big data, emphasizing its role as a regenerative resource that fuels innovation and strategic decision-making within organizations. He reflects on his experience with large-scale data projects, acknowledging both successes and failures, and highlights the challenges associated with engineering complex data solutions. Bajda-Pawlikowski argues that the true value of data lies in its ability to be refined and analyzed to drive profitable activities, comparing it to natural resources like gold. He also notes the cultural shift big data has inspired, allowing for greater analytical freedom and removing biases in decision-making. Contributions from Starburst team members further underline the importance of empowering data-driven decisions and improving business intelligence in a cloud-first environment. The article concludes with an invitation to join Starburst's global team of engineers in redefining data analytics.

Starburst Galaxy provides a compelling alternative to Amazon Athena by offering a fully managed, scalable analytics platform built on open-source Trino, which ensures it stays up-to-date with new functionalities, performance enhancements, and security patches. While Athena, based on a combination of Trino and Presto, faces challenges like limited visibility into query execution, potential high costs from its data-scanned pricing model, and the "noisy neighbor" problem due to shared compute resources, Starburst Galaxy simplifies deploying Trino with intuitive user experience and pre-configured connectors, allowing users to manage compute resources with features like auto-scaling and idle shutdowns. This platform supports federated queries across multiple data sources and provides detailed query execution insights, facilitating performance tuning and reducing query time compared to Athena. Designed by the creators of the original Presto project, Starburst Galaxy offers a robust solution for organizations aiming to enhance their analytics capabilities and achieve faster insights while minimizing infrastructure management complexities.

Data Mesh is an organizational approach that decentralizes data management by reallocating data responsibilities from a central data team to various specialized roles, thereby fostering efficiency and empowerment within business-focused domain teams. This transition involves several key roles: the Executive Sponsor, who aligns data strategy with business objectives; the Domain Owner, who sets federated governance standards; the Data Product Manager, who oversees the lifecycle of data products; and the Domain Data Architect, who designs data flow and models. Additionally, roles such as Data Product Developer, Infrastructure Engineer, and Domain SME/Analyst are crucial for creating and maintaining data products, while Legal/InfoSec/Audit experts ensure compliance and risk management. Organizations adopting Data Mesh must consider design principles that encourage autonomy and efficient management of data responsibilities, supported by external advisors as needed, to successfully evolve and expand their data domains and products.