July 2022 Summaries

Hex has introduced new UI-driven cells - Pivot, Filter, and Writeback - to complement its existing code-based data science and analytics workflows. These "no-code" cells allow users to switch between code and UI for common data manipulation tasks. Although these cells are written in code by Hex, they can be converted into code for users who want more control or wish to learn new functions. The Pivot cell enables aggregation and pivoting of dataframes, while the Filter cell allows users to add individual filters or groups. The Writeback cell simplifies writing dataframes back to the database from logic. These features aim to make data work more accessible for a wider range of users without imposing artificial constraints on their learning and growth.

CTEs (Common Table Expressions) are temporary, named result sets that can be referenced within the same query. They were introduced in SQL in 2005 and have become popular due to their modularity, reusability, recursion, and referenceability. However, they can only be referenced in the same query where they were created, making debugging complex queries difficult. Chained SQL is a feature that allows breaking up long CTE-based queries into separate chunks that can reference each other, improving readability and debuggability. Hex's Chained SQL automatically turns references to upstream results into CTEs behind the scenes, utilizing caching for efficient query execution.

Computational notebooks, despite being around for decades, have not kept up with the changes in the data world and feel outdated. The main issues with these tools are their inability to handle large amounts of data (scale), confusing and frustrating compute models (state), and lack of collaboration features (sharing). Despite these shortcomings, notebooks can be useful for iterative, exploratory data work but require users to overcome various challenges.

This tutorial guides users through connecting to an Amazon Redshift data warehouse from Python, running SQL queries, and reading data into pandas DataFrames. It covers installing the redshift_connector package, setting up environment variables for authentication credentials, creating a connection and cursor, executing SQL queries, and using fetch_dataframe() method to read query results directly into pandas DataFrames. The tutorial also mentions alternative methods of connecting to Redshift such as psycopg2 and SQLAlchemy, but recommends the redshift_connector package for its ease of use and built-in features.