June 2022 Summaries

Preset has announced its integration with dbt Core, enabling users to manage data transformation SQL queries within a source-controlled environment and synchronize them with Preset Cloud. This integration allows users to define databases, datasets, and metrics as version-controlled assets using dbt and sync them to Preset, facilitating a dataset-centric approach enriched with metadata for visualization purposes. The process involves using the Preset CLI to synchronize dbt's semantic layer, including sources, models, and metrics, into Preset workspaces, with the ability to push charts and dashboards back as exposures to dbt. The integration supports continuous deployment through automation in a deployment pipeline, ensuring assets are automatically published to Preset following updates in the dbt project. This collaborative effort aims to enhance data exploration and iteration, leveraging the strengths of both dbt and Preset in building a modern data stack, with further developments planned for integration with dbt Cloud.

CloudQuery is an open-source solution for creating a cloud asset inventory using SQL, which extracts, transforms, and loads cloud assets into PostgreSQL tables. This blog post details the process of setting up CloudQuery to build a cloud asset inventory in PostgreSQL and connecting it to Apache Superset or Preset Cloud for visualization, monitoring, and reporting. The architecture involves using CloudQuery for the ETL process, PostgreSQL for database management, and Apache Superset for data visualization and exploration, allowing users to access raw SQL views of their cloud assets. By integrating CloudQuery with existing Apache Superset setups, users can avoid the proliferation of unnecessary dashboards while maintaining the flexibility to select preferred visualization tools. The guide includes detailed steps for installing and deploying both CloudQuery and Superset, connecting to PostgreSQL, creating datasets, and generating interactive charts and dashboards, ultimately facilitating comprehensive cloud resource management and reporting without the need for additional analytics platforms.

This blog post explores the advanced customization of Apache Superset assets such as databases, datasets, charts, and dashboards using the Preset CLI and Jinja2 templates. Building on a previous post that introduced managing these assets as code, it delves into creating bespoke dashboards across multiple workspaces, each configured with unique data access via different database connections or tailored datasets. By employing templating logic, users can synchronize and customize Superset components to reflect distinct data needs while maintaining a unified visual structure across teams. The post also highlights the flexibility of using functions to simplify configuration management, ensuring each workspace connects to the appropriate data source. Additionally, it demonstrates the potential for further customization, such as dynamic chart titles and dashboard elements, facilitating a personalized experience tailored to specific user requirements. The post encourages experimenting with the provided examples from a GitHub repository or using personal data to understand the process better.

Dremio is a widely used lakehouse platform that integrates the benefits of data warehouses and data lakes, and when paired with Apache Superset, it enables the creation of robust data visualizations. As organizations increasingly use Dremio, it becomes critical to monitor and understand the workloads executed on the platform, which can inform users about potential failures, user challenges, and cost reductions in platform ownership. Key data sources like queries.json and query profiles provide insights into queries processed by Dremio, although queries.json lacks detailed execution information found in query profiles accessible via the Job UI. To facilitate querying, the queries.json files and query profiles must be stored in distributed storage accessible by all executors, such as S3 or HDFS, and may require some transformation. Visualizing these workloads in platforms like Preset Cloud offers insights into query distribution and performance, revealing important patterns like protocol usage, query acceleration issues, and resource contention, which can be analyzed further for optimizing system performance and ensuring compliance with service-level agreements. The article also notes the importance of proper workload analytics for maintaining a stable and reliable data platform and hints at future discussions on Dremio Monitoring.

In the exploration of a dataset-centric visualization approach using Apache Superset and Preset Cloud, the focus is on leveraging real-time and historical data from Citi Bike, New York City's primary bike share system. The dataset-centric approach emphasizes normalizing raw data to create derived datasets with additional semantics, allowing for enhanced analysis and visualization beyond historical limitations. By using dbt, a development framework that combines modular SQL with software engineering best practices, the data is transformed to incorporate real-time metrics from the General Bikeshare Feed Specification (GBFS), enhancing dashboard capabilities with up-to-date information. This method reduces issues related to change management and logic maintenance within visualization tools while providing reusable datasets across various data tools. The integration of real-time data with historical data enables more robust and timely visualizations, demonstrating the advantages of performing data transformations in the ETL/ELT process rather than solely in the visualization layer.

Apache Superset initially supported only Apache Druid using its native API, but as Druid evolved to support SQL, the need to extend Superset's compatibility with various databases grew. Today, Superset accommodates a wide array of SQL-speaking databases, enhancing its querying capabilities through SQLAlchemy dialects and Python DB-API libraries. The article details the necessary attributes and methods to develop custom database engines, such as defining time grain expressions for time-series visualizations, converting Python datetime objects to SQL expressions, and adapting SQLAlchemy connection strings. Additionally, it discusses recommended practices like mapping database-specific exceptions to Superset's exceptions, defining column type mappings, and handling epoch conversions to datetime formats. The document provides examples from databases like CrateDB and Postgres, illustrating how Superset can be customized for enhanced user experiences through features like query feedback, column name handling, and SQL Lab auto-completion. Overall, the guide offers an in-depth exploration of developing and optimizing database engines for seamless integration with Superset.

Preset has introduced its Managed Private Cloud (MPC) solution, allowing users to deploy Preset workspaces within their own AWS private cloud as a fully managed service. This offering is particularly beneficial for organizations that wish to maintain all their data, including datasets and query results, within their own network while benefiting from a modern cloud-based BI solution. Preset MPC takes over the management tasks such as deploying, configuring, upgrading, and scaling Superset, while the workspaces are hosted in the customer's AWS account. The solution includes a Preset Control Plane within Preset's AWS account and a Customer Workspace within the customer's AWS environment, maintaining data connections securely within the customer's network. Compliant with SOC2 Type 2 and PCI-DSS standards, the service ensures secure communication with full TLS encryption and uses third-party monitoring for log observability. Organizations can seamlessly migrate from the existing Preset Cloud with minimal downtime, facilitating easy setup and management in collaboration with the customer's infrastructure team.