November 2022 Summaries

The Modern Data Stack Conference (MDSCon) is set to take place in San Francisco on April 4 and 5, 2023. This two-day event will feature educational sessions from over 40 speakers who are experts in data engineering, analytics, cloud, and AI. Attendees can expect hands-on technical training and opportunities for networking with industry professionals. The conference's theme is "Powering your data movement," focusing on the latest innovations that enable businesses to mobilize their data effectively. Notable speakers include former U.S. Chief Data Scientist DJ Patil, as well as leaders from Snowflake and Databricks. Attendees can save 25% off registration by using the discount code MDSCON-5REASONS before February 1, 2023.

Data analytics and business intelligence are increasingly important for businesses, leading to significant investments in data technologies like Snowflake, a preferred data warehousing solution enhancing SQL-heavy task development. While similar to other SQL platforms, Snowflake has unique aspects, such as its query processing engine, which can lead to specific errors like the "missing column specification" error. This error arises during SQL compilation, particularly when using Snowflake's DDL to create tables or views without explicitly naming all referenced columns with aliases. To resolve this, users must add column aliases for calculated fields. The article provides a detailed walkthrough on setting up a Snowflake environment, creating necessary database objects, and resolving this error using a practical example involving user payment data. Additionally, it highlights the role of Fivetran Activations, a reverse ETL tool that facilitates data synchronization between Snowflake and business applications, allowing businesses to focus on leveraging their data for actionable insights.

Data engineering is often perceived as complex and technical, but its core purpose remains to transform raw data into high-quality information for business use. Understanding the data engineering lifecycle, which encompasses stages like data generation, ingestion, transformation, and serving, is crucial for effective collaboration between data engineers and other stakeholders. Matt Housley emphasizes the importance of communication and holistic thinking in overcoming challenges in the field, highlighting that data engineering is foundational to data science success. Key concepts such as security, data management, DataOps, and orchestration are integral throughout the lifecycle to ensure data quality and reliability. By demystifying data engineering and fostering better communication, stakeholders can achieve more effective data-driven outcomes.

The Fivetran Prefect Collection is a new addition that enhances the integration between Fivetran and Prefect, allowing for more complex and automated responses to data syncs. This collection enables asynchronous monitoring of Fivetran connectors in data orchestration tools, improving flexibility and parallelism. It also provides unit tests for coverage not previously available for the FivetranSyncTask. The collection offers a variety of tasks and flows that can be added if needed, with each new task containing an example of its use. This makes it easier to create Prefect flows using Fivetran, allowing more time to focus on other tasks within the flow.

Fivetran introduces high-volume agent (HVA) connectors for Oracle, SQL Server, and Db2 for i to address the increasing demand for faster, high-volume database replication. These connectors use log-based change data capture and an agent to minimize latency and support large volumes of data. HVA connectors incorporate features such as incremental updates and automated schema-drift monitoring. Common high-volume database replication use cases include supply chain management, event data tracking, customer transactions, and healthcare records. Fivetran's HVA connectors offer multiple connection methods, change data capture methodology, automated schema drift handling, column blocking, masking, hashing, history mode, and support for all Fivetran-supported destinations. Benefits of using HVA connectors include ease of use, rapid integration, near real-time access to accurate data, and reduced total cost of ownership.

SQLAlchemy is a prominent Python library used for interfacing with relational databases, including Snowflake, through its object-relational mapper (ORM) and SQL engine capabilities. While traditionally utilized for operational databases, SQLAlchemy also facilitates running analytical queries in data warehouses like Snowflake, offering a unified, vendor-agnostic approach. The tutorial outlines different methods for setting up SQLAlchemy with Snowflake, highlighting the ease of generating pandas DataFrames from query results using SQLAlchemy over the default Snowflake connector. It addresses common errors encountered during setup, such as module and connection issues, and provides solutions to these problems. By integrating SQLAlchemy with Snowflake, data professionals can efficiently execute SQL queries and manage data within a familiar Python pandas environment, enhancing productivity and data accessibility.

IDC research shows that automated data movement enables organizations to improve insights and derive more value from cloud data warehouses and lakes. Automated ELT, the modern successor to traditional ETL (extract, transform, load), provides nearly instant, self-service access to data and allows data development teams to allocate engineering resources to important business-supporting activities. An automated data movement platform uses the ELT methodology alongside 200+ prebuilt connectors that automatically adapt as schemas and APIs change to ensure consistent and reliable access to data. This enables organizations to unify and continually synchronize data from disparate sources, including databases, applications, files and events.

The Fivetran Community is an online platform designed for data practitioners to ask and answer questions about Fivetran, learn from technical experts, and connect with industry peers. It offers discussion forums on using Fivetran, a developer forum for coding projects, and a working with data section for miscellaneous topics. Additionally, the community includes user groups for dbt™ and various events such as office hours, hands-on labs, and virtual networking opportunities. Existing customers can register using their product login, while non-users are encouraged to try Fivetran's 14-day free trial.

The modern data mindset is crucial for organizations to fully embrace the technical benefits and organizational changes that come with a modern data stack. This involves understanding the data hierarchy of needs, shifting from ETL to ELT architecture, and outsourcing and automating data operations. By adopting this mindset, companies can leverage automation, improve performance, resilience, and adaptability, and free up technical and analytical talent for more valuable tasks. The ultimate guide to data integration provides a detailed guide on both technical implementation and cultural integration and change management.

Database replication is the process of creating and storing copies of a database in different locations, such as cloud data warehouses or data lakes. It improves data availability and accessibility by allowing users to access copies of the same (up-to-date) data. There are two types of database replication: active/active replication, where every system can process changes and databases are synced in all directions; and read-only replication, where replicated databases only receive changes from the primary database. Database replication offers benefits such as improved disaster recovery, lower data latency, and reduced server load. However, it also presents challenges like ensuring data consistency and managing multiple servers and destinations. There are three main types of database replication: full-table, key-based incremental, and log-based incremental. To properly implement a database replication process in an organization, one should identify the data source, determine the scope of replication, decide on a replication frequency, choose a replication type and method, and use a database replication tool.

Fivetran has expanded its services by adding a data process center in Japan for Google Cloud users. This expansion aims to improve the company's ability to provide a global, highly available service infrastructure for customers requiring locally delivered data pipelines. The new instance will serve Fivetran's growing Japanese customer base and multinational corporations who do business in Japan and want to maintain data residency within the country. With this expansion, users in Japan can now keep up with both security and local regulation requirements while enjoying the benefits of automated data integration that saves data teams valuable time.

A data pipeline is a set of actions and technologies that route raw data from different sources to a destination like a data warehouse. It consists of three components: a data source, a transformation step, and a target destination. Data pipelines centralize data from disparate sources into one place for analysis and can ensure consistent data quality. There are three types of data pipeline architectures: batch processing, stream processing, and hybrid processing. ETL pipelines extract, transform, and load data into a target system, while data pipelines don't always involve data transformation or run in batches. Automated data connectors are the most effective way to reduce the programmer burden and enable data analysts and data scientists.

Encountering the "is not a valid group by expression" error in Snowflake often stems from common oversights, such as mismatching columns in the GROUP BY clause with those in the SELECT statement or improperly using Window functions. The article explores two primary scenarios leading to this error: accidentally including an aggregated column in the GROUP BY clause, and using unaggregated columns within Window functions without corresponding GROUP BY usage. To resolve these issues, it's crucial to ensure that the columns in the SELECT clause align with those in the GROUP BY clause, and understand the distinction between Window and aggregate functions. The article also suggests solutions like wrapping a nested SUM() around the aggregation or utilizing common table expressions (CTEs) to enhance query readability and functionality. Emphasizing the importance of SQL proficiency, it encourages readers to engage in further learning opportunities, such as workshops, to refine their SQL skills.

The article discusses the evaluation of alignment for managers of data teams. It highlights two primary goals for managers: ensuring that their team's skills are well-used and that the organization is satisfied, and ensuring that their team members' skills are developed and appreciated, with the team being satisfied. Metrics to evaluate these goals include department metrics, lead time, data adoption, trustworthiness, empowering data-driven decisions, standardizing processes and improving efficiency, and stakeholder satisfaction. The article also emphasizes the importance of external evaluation through career progression frameworks (CPF) and self-evaluation through surveys. It concludes by stating that success for a manager cannot exist without the success of their team, and that focusing on organizational alignment ensures each team member provides value to the broader team while focusing on team alignment ensures they have resources and feedback needed to provide that value.

Real-time data movement is crucial for improving supply chain visibility in the manufacturing industry. With global supply chain disruptions, labor shortages and rising economic pressures, maintaining visibility across the entire supply chain has become essential. Manufacturers need to operationalize large-scale data usage in near real-time to stay competitive. Fivetran's automated and scalable real-time data movement helps manufacturers transform their supply chains by integrating and centralizing data from disparate sources, providing real-time visibility and intelligence. This enables analytical models that can deliver both faster insights and valuable predictive insights through the use of AI/ML models.

Navigating the process of unloading data from Snowflake requires understanding the reasons behind the action, whether for use in other business tools or transferring data to different cloud environments like Amazon S3 or Google Cloud Storage. The process primarily involves using the COPY INTO command, which allows data to be copied to a Snowflake stage or directly to external storage, necessitating set-up of a storage integration for authorization. Key considerations include selecting the appropriate file type, such as CSV or Parquet, based on the data and destination's requirements, and ensuring data consistency and safety through backups. While manual or automatic unloading can be complex, utilizing services like Fivetran Activations, which offers numerous destination integrations and fast sync speeds, can simplify the task by operationalizing data without added frustration.