November 2022 Summaries

Hex and dbt Labs have announced a partnership aimed at transforming data workflows for thousands of users. The integration between Hex's analytics workspace and dbt's data transformation tool allows users to tackle more end-to-end workflows, improving trust in data sources and reducing mental load. Many shared customers are already benefiting from the integration, including Bluecore and Whatnot, which have reported increased efficiency and faster time-to-market for their data teams.

Funnel analysis is a method used by businesses to analyze the chain of events that customers traverse on their way to completing a desired goal, such as making a purchase. The AIDA model, consisting of awareness, interest, desire, and action, is often used to represent these steps. By tracking user events, actions, and drop-off points, companies can identify areas for improvement in their customer journey. Funnel health is determined by comparing the actual customer pass-through rate with an expected pass-through rate set by business goals. Drop-off points should be investigated further to determine possible reasons for customers not completing the desired action. Time to purchase is another important metric that helps track how long it takes users to move through a funnel, allowing businesses to build revenue models and forecast pipeline.

On November 7, 2022, Hex Cloud experienced its longest-ever service interruption due to an ETL configuration issue that caused their production Postgres database to fill up disk space. The incident lasted for about 2 hours and 40 minutes, affecting thousands of users globally. The company has published a detailed post-mortem report on the incident, highlighting the steps taken to resolve it and prevent similar issues in the future. Key takeaways from the incident include: 1. Monitoring alerts were missed for free space in the database and Fivetran failures, which could have prevented the outage. 2. The company is working on improving its monitoring systems and adding specific alerts to prevent similar issues in the future. 3. Postgres 13 has a feature that limits the size of the WAL (write-ahead log), preventing larger outages, and Hex plans to upgrade from Postgres 12. 4. The company is exploring Teleport for Fivetran as an alternative solution to reduce risks associated with ETL integration. 5. Maintaining incident runbooks and conducting quarterly fire drills are crucial in preparing engineers for crisis situations. 6. Redundancy, such as the read replica that saved Hex during this incident, is essential for mitigating potential outages. 7. Taking a moment to reflect before diving into solutions can help avoid unexpected failure modes and identify alternative approaches. 8. All systems that touch production should have the same level of reliability, monitoring, and alerting as critical systems.

The text provides a detailed guide on connecting to a MySQL database using Python, highlighting its persistent popularity among developers and major companies like Facebook and Netflix. It describes two primary methods for establishing this connection: using mysql-connector-python for straightforward queries and SQLAlchemy for more complex analyses, particularly when integrating with Pandas. Key steps include obtaining and securely storing necessary credentials such as host address, username, password, and database name, and configuring security settings for cloud databases like those on AWS RDS. The text emphasizes avoiding direct storage of credentials in code by using environment variables instead. It explains the use of mysql-connector-python for executing SQL queries and retrieving data, but notes limitations when integrating with Pandas, recommending SQLAlchemy for a more seamless experience. The guide also touches on the installation of necessary Python libraries and efficiently utilizing Pandas to handle and analyze data, with a suggestion to consider using tools like Hex for direct SQL integration with dataframes.

Creating dbt packages involves understanding the distinction between internal and external packages, and the process includes defining package types, building and testing code, and eventually sharing the package with the community. Internal packages are tailored for specific organizational needs, promoting efficiency and best practices, while external packages are designed for broader use, requiring a more generalized approach. The guide provides a step-by-step approach to building a dbt package, from initializing a new dbt project and configuring the dbt_project.yml file to defining variables and using built-in dbt macros for cross-database compatibility. Testing locally is crucial before deploying the package publicly, which involves creating a GitHub repository, writing a README, and eventually releasing the package on dbt hub. The process encourages contributions and feedback from the community, leveraging open-source collaboration to refine and enhance the package's utility.

This tutorial guides users through connecting to a Google BigQuery warehouse using Python. The process involves installing the official Google Cloud Python Client (google-cloud-bigquery), authenticating and connecting to the data warehouse, running queries, reading data from a BigQuery query into a pandas DataFrame, and writing a DataFrame back into a BigQuery table. The tutorial also provides tips on setting up virtual environments and using public datasets for testing purposes.