November 2022 Summaries

Snowflake's query processing engine has a distinctive way of evaluating and parsing SQL queries, which can lead to "missing column specification" errors when creating tables or views using SELECT statements with calculated fields. These errors occur due to Snowflake's requirement for explicit column aliases in such cases, unlike other database platforms that allow implicit aliasing. To resolve this error, developers must add a column alias to the calculated field, as demonstrated by an example query that fixes the issue and executes successfully. Understanding this nuance is crucial for businesses moving towards being data-driven, as it highlights the importance of explicit aliasing in Snowflake's SQL syntax.

Because it’s too far to walk.` is a humorous remark about birds flying south in the fall, and has no relevance to the actual topic of the text which discusses new releases, partnerships, content, connectors, events, and hiring. The partnership with Prefect aims to improve data engineering efficiency, while Sync Sequences allow for more control over syncing data to objects with nested dependencies. The emerging data warehouse is seen as a single source of truth for customer data, enabling marketers to do their jobs better and faster. New connectors have been added, including Pinterest Ads, and events are announced, such as a panel discussion on creating powerful customer profiles and a workshop on building a composable CDP with Google Cloud. The company is also hiring.

The data engineering lifecycle is a method for overseeing data engineering processes, including data acquisition, integration, storage, processing, and analysis. This lifecycle aims to consistently deliver high-quality data sets that can aid in business decision-making. Understanding the lifecycle helps stakeholders better collaborate with data engineers to deliver outstanding data products. Data engineers face two major challenges: communication and holistic thinking. Effective communication ensures that data engineers understand what data is needed, while holistic thinking considers the big picture and maintaining quality throughout the pipeline. The lifecycle stages include generation, ingestion, transformation, and serving, each with its unique challenges and requirements. Six foundational concepts flow across these stages, including security, data management, DataOps, data architecture, orchestration, and software engineering. Ultimately, data engineering is not a mystical art but a mechanism that enables data science to produce insights, requiring collaboration between stakeholders and engineers.

SQLAlchemy is a popular Python library for interfacing with relational databases, offering an object-relational mapper (ORM) that allows developers to map classes to databases automatically. It supports various operational databases and data warehouses like Snowflake, providing a unified way to run queries on both types of databases. SQLAlchemy excels in ORM use cases due to its high performance and accuracy, making it a flexible solution for most applications. The library also provides a SQL engine that allows users to translate database query results into pandas DataFrames. To interface with Snowflake, developers can use either the Snowflake connector or SQLAlchemy's built-in engine, which offers a simpler and more straightforward approach. Despite some common issues, using Snowflake and SQLAlchemy together enables data professionals to work within their familiar pandas setup, making it easier to integrate Snowflake data into sales and marketing tools.

Scaling rapidly in data, especially with minimal guidance, can be overwhelming for individuals. Specialization helps narrow focus and avoid feeling overwhelmed as a company scales fast. Emily Hawkins, Drizly's data platform lead, shares her experiences navigating rapid growth, making decisions that impact her career and the company, and building trust with colleagues through clear communication. The key to effective use of business intelligence is getting relevant insights into the hands of business users quickly, while maintaining data integrity. Emily emphasizes the importance of educating users, becoming an expert in technology, and explaining improvements to achieve success.

The data industry has a homogeneity problem, with many teams lacking diversity in terms of gender, racial, and ethnic makeup. However, there is growing recognition that diversity is crucial for innovation, financial returns, and revenue. To foster diversity, companies need to start from the C-suite level and take action to recruit, retain, and mentor diverse employees. Key recommendations include looking beyond usual candidate pools, encouraging people from other career fields to transition into tech, and removing gatekeeping requirements from job descriptions. By taking these steps, companies can create a more inclusive and innovative data team, leading to better business outcomes.

The Data Warehouse Toolkit, written by Ralph Kimball and Margy Ross, has been the mainstream method of data modeling for 25 years, providing a framework for dimensional models, star schemas, and fact and dimension tables. The basic organization of separate tables makes sense to most people, and it's easy to join them together, but some argue that this approach is no longer relevant in modern cloud data warehouses. Modern tools like dbt make it easy to create fact and dimension tables, which can then be easily converted into Wide Tables or Activity Schemas as needed, providing a flexible and easy-to-use solution for modern data modeling. The traditional Kimball approach has its advantages, such as providing a blueprint and being a common standard, but also has limitations, including the need for joins that can make it hard to navigate for business users.

The text discusses the importance of operational analytics in powering growth for B2B SaaS companies. It shares the author's experience with building a data stack from scratch, highlighting key takeaways such as the need to prioritize value process over tooling, and the importance of contextualizing data to make it actionable for business users. The author emphasizes that dashboards are not enough, and instead, teams should focus on activating data through reverse ETL, which enables syncing data with third-party tools. Additionally, the text touches on the challenges of growing a small data team and the need to invest in data quality, sharing knowledge across the company, and using notification engines to enable product and growth teams to move fast without relying on data teams.

Server-side tag managers and reverse ETL are both used for data processing, but they serve different purposes and have distinct characteristics. A server-side tag manager collects data from a website in real-time and sends it to predefined destinations, offering more control over the data due to its centralized approach. However, this method has limitations, such as not being able to send data to all ad platforms or supporting complex authentication. Reverse ETL, on the other hand, loads data from an analytical database into destinations like CRMs, customer service, and other databases, allowing for more extensive data processing and synchronization of historical data. While server-side tag managers are suitable for real-time tracking and event monitoring, reverse ETL is better suited for synchronizing extensive data regularly to a destination, ensuring data sync and delivery, and handling complex data enrichments. By understanding the differences between these two solutions, businesses can choose the most appropriate approach to meet their performance and privacy needs.

You were deep in an analytical flow, firing off every question that you think Snowflake DB can answer – and then, it happened. You got the unnecessarily terse and unexpectedly confusing error, “blah blah blah is not a valid group by expression.” This error occurs when there's something in your query's GROUP BY clause that's not supposed to be there, typically due to omitted or incorrectly added code, leading to either a slip or a mistake. Two common causes of this error are adding an aggregated column to the GROUP BY function and referring to unaggregated columns in a Window function without using them in the GROUP BY clause. The Snowflake dialect supports combining both GROUP BY and Window functions, but there's a warning about compatibility issues with PARTITION BY and GROUP BY. To resolve these errors, you can either match up the column names or use an aggregation function to introduce compatibility, or use a CTE to calculate the sum of revenue per customer ID and order status without needing a GROUP BY clause.

Figma's success in product-led growth is attributed to its focus on centering the product around the business strategy, which led to a massive acquisition by Adobe for $20B. The company built a single source of truth for its GTM data through iteration and investing in quality systems early on. Product usage data played a key role in revenue forecasting, while empathy and understanding of the product were crucial for sales teams. To avoid headaches, Figma implemented automation into manual processes, and avoiding silos enabled them to manage reliable data across workflows and applications. By following these strategies, organizations can learn from Figma's journey and accelerate their own business growth.

The author attended the Coalesce conference and reflects on their favorite moments from the event, focusing on business applications of analytics engineering and dbt. They highlight talks by David Jayatillake, Sydney Burns, Josh Devlin, John Kennedy, Lindsay Murphy, Emilie Schario, Kshitij Aranke, Boris Jabes, Julia King, Marc Stone, Jake Thomas, Emily Hawkins, and Dylan Hughes, who discussed topics such as the benefits of analytics engineering, the relevance of Kimball's work, the importance of teaching stakeholders how to fish, the limitations of "data-led" thinking, data change management, event-driven dbt models, and more. The author notes that the conference was one of the best they've attended and highlights the growth and diversity of the dbt community, as well as the need for data leaders to focus on operational analytics and teaching tech skills to stakeholders.

Today, we’re excited to announce the release of Sync Sequence Triggers.` This new kind of trigger allows you to run a sync based on the successful run of another sync, helping to eliminate race conditions and inconsistencies in data. Previously, syncing data across objects with nested dependencies would lead to weird states where data was not properly associated, but with Sync Sequence Triggers, you can now pick the order you want to create or update these objects, ensuring associations are correct as they're being created and updated in the right order. This feature also opens up new use cases such as scheduling payment syncs after contact creation and updating, and creating events in analytics tools after user creation. With this release, Census aims to help users orchestrate their business object representations across multiple tools, taking them a step closer to a more seamless data synchronization experience.

Prefect and Census partner to help data engineers extend the impact of their work and automate with confidence. Data engineers often face challenges in pushing data to SaaS tools, but Census solves the "last mile" data activation problem and easily syncs data to over 100 popular business tools. The partnership enables data engineers to trigger Census syncs based on upstream tasks and events, reduce time to resolution for failed pipelines, communicate data status to end users, and see the status of Census syncs in Prefect Coordination Plane. With this integration, data engineers can enjoy fresher, more reliable data, fewer support tickets, faster issue resolution, and significantly less development time and maintenance, ultimately leading to more effective and efficient data engineering and analytics.

The study surveyed 15 go-to-market stakeholders at Hubspot's INBOUND conference to understand their perceptions of data teams. Stakeholders rated their data team an average rating of 7/10, with a median rating of 8. The most common complaint was that the data team is "too slow." However, this response may be due to a lack of communication between stakeholders and the data team, rather than actual slowness. To address this issue, data teams can prioritize regular feedback from stakeholders, master the basics of self-serve data tools, communicate better with stakeholders, and establish consistent meetings with them. By doing so, data teams can improve their relationship with stakeholders and drive business outcomes.

There's so much you can do with data, but it can be frustrating and overwhelming when you can't process it into useful information. This is where services like Snowflake come into play, allowing data analysts and engineers to process and manage their data efficiently without the fuss. Cloud-based data computing services like Snowflake enable data folks to collaborate both locally and globally, making it easier to access data from anywhere. However, most customers were initially thinking of using Snowflake solely for business intelligence and dashboards. Now, Snowflake is becoming more of a center for business operations instead of "just a data warehouse." To really capitalize on its power, we need to connect it to the apps that people use every day, like Slack and Zapier. Workflow automation with tools like Zapier makes developers' work easier by automating repetitive processes and integrating web applications of choice. This allows for faster response times, reduced error, proper communication, and avoiding repetitive tasks. Creating a new Snowflake-Zapier integration can be set up manually or through the use of reverse ETL tools like Census, which provides a faster and more efficient way to create integrations.