September 2022 Summaries

Fivetran introduces a new API reference designed to enhance developers' experience with its platform. The updated documentation focuses on three key aspects: speed to solution, time to interaction, and readability. Developers can now interact directly with the API from the documentation itself by entering their API key and secret. Additionally, code snippets are provided in top languages such as Curl, Node.js, Python, Java, and Go. The new API reference is powered by OpenAPI, which automates documentation generation and allows users to create their own automations against Fivetran's API description.

Snowflake external functions enable users to enhance their data by integrating external code, offering a way to access API endpoints from platforms like AWS, GCP, or Azure directly within SQL queries in Snowflake. This approach eliminates the need for separate data pipelines and allows leveraging machine learning models, as demonstrated by enriching sales leads with ranking scores from an external API. Setting up these functions involves creating API integrations and trusted relationships between Snowflake and the chosen cloud platform, followed by defining the external function to process the data. While this method provides significant advantages such as direct data enrichment and flexibility in programming languages, it also poses challenges like the time investment in API creation and maintenance, platform-specific integration requirements, and potential issues with function call frequency and duplication. The enriched data can then be seamlessly transferred to CRM tools using Fivetran Activations, which simplifies reverse ETL processes, allowing teams to focus on deriving actionable insights from their data.

Fivetran offers a 14-day free trial to evaluate its ELT connectors risk-free. During the trial, users can connect their data sources and warehouse, set up transformations using dbt Core and Fivetran Data Models, create reports and analyses in BI tools, and view usage and billing estimates. The guide provides step-by-step instructions on how to get started with the free trial and highlights key features of Fivetran's product offering, such as automation, simplicity, reliability, and adaptability to API and schema changes.

Salesforce is a popular CRM platform used by 150,000 companies to manage customer relationships and sales data. One challenge with Salesforce is its flexibility in creating custom fields, such as formula fields, lookup fields or calculated fields. These derived values can cause issues for data analysts who rely on syncing raw data from Salesforce to their warehouse for analysis. Fivetran has developed a solution called "formula fields as views" that automatically replicates formula field values in the destination without manual translation of formulas from Salesforce SQL (SOQL) to Standard SQL. This feature includes two parts: the Fivetran_formula_model table and the transformation solution, Salesforce formula utils. The Fivetran_formula_model table automatically pulls custom formulas and displays relevant object names with SELECT statements for each synced object. Salesforce formula utils uses these SQL formulas to create and display custom field values as views in the warehouse. To get started, users need to sync their Salesforce data with Fivetran and then generate views in their destination using dbt Core.

Permissions play a critical role in data management systems and tools like dbt, Amazon Redshift, and Snowflake, ensuring that unauthorized users cannot access sensitive data. dbt, an open-source analytics engineering framework, facilitates data transformation by allowing users to write modular code and maintain data integrity through proper permissions configuration. It is crucial to assign appropriate permissions to both tools and users within dbt to protect against inadvertent data alterations or deletions. Permissions within dbt can be managed using the profiles.yml file for open-source usage or through license-based and role-based access control in dbt Cloud. License-based access restricts user actions based on account-wide license types, while role-based access offers more granular control, allowing users to inherit permissions from groups. Properly configuring these permissions can prevent errors such as unauthorized data modifications and ensure that data models are developed in appropriate environments. Overall, these permissions help maintain a secure and efficient data environment, enhancing the quality and reliability of datasets used for business operations.

The article discusses seven essential database schema best practices that help make your data more tractable and usable. These include upfront planning, data normalization, entity-relationship diagrams (ERDs), data standardization, naming conventions, security by design, and schema documentation. Good schema design requires a significant investment of time and expertise but is crucial for understanding the underlying data model and ensuring data integrity. Following these best practices can help analysts build dashboards, reports, and data models more effectively.

Data lakes and warehouses are essential tools for storing large amounts of enterprise data, which is crucial for analytics and decision-making. A data warehouse is a central repository of structured data that follows the ETL process and uses schema-on-write model. It's best suited for analytics and has built-in features or direct connection with business intelligence tools. On the other hand, a data lake stores structured and unstructured data using ELT method and schema-on-read approach. It is used for big data analytics, machine learning, predictive analytics, etc. Data lakes are not easily accessible or joinable using SQL or most BI platforms. Both technologies complement each other in the modern data stack, with a data warehouse being the primary repository for structured business data and a data lake serving as a central repository for both structured and unstructured data.

The Fivetran Metadata API enables governed data movement by providing enhanced visibility into where the data came from, who accessed it and what changes have occurred in the pipeline. This allows customers to integrate Fivetran into their existing governance tools and processes, retrieve Fivetran’s metadata about data moving through the pipeline, visualize where the data comes from in lineage graphs, and share information about the data being loaded by Fivetran into a data catalog. The API also helps data analysts understand the origin of data, ensures secure handling of data for data stewards, assists security and legal teams with compliance checks, and supports data architects and engineers in understanding upstream schema changes.

Data integration is the process of combining data from various sources into a single, unified view for analysis and management. It involves extracting data from disparate sources, transforming it into a standard format, and loading it into a target data warehouse or database. The most common techniques are ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform). Data integration solutions include specialized platforms like Fivetran that automate the process, allowing analysts to focus on analysis and reporting. Benefits of data integration include improved accuracy, collaboration, time-saving, and resource optimization. Businesses can leverage customer data integration for better customer experience and higher profits.

Sales analytics can accelerate a company's sales pipeline by blending the art of sales with a data-driven strategy. Three strategies to build a strong relationship between sales and data teams include taking a holistic approach to sales funnel analysis, committing to customer tracking, and adopting a data-driven sales culture. By implementing these strategies, companies can unlock pipeline-accelerating insights that salespeople can leverage as they continuously improve their engagement strategy and pitches. This involves using CRM, product, database, and customer engagement tool data to identify trends in the market and get a pulse on the overall health of the sales funnel.

Amazon Ads offer a significant opportunity for marketers to reach potential customers, especially during times of economic uncertainty. With over 60% of online shoppers in the U.S. starting their product search on Amazon and high conversion rates for sponsored ads, it's crucial to leverage data-driven strategies to optimize campaigns. Two main types of ad formats available are pay-per-click (PPC) ads and demand-side platform (DSP) ads. Keyword targeting, product targeting, and negative targeting are popular ad targeting strategies that can help improve return on ad spend (ROAS). To effectively increase conversions and use marketing budgets wisely, marketers should focus on data-driven insights and unify advertising data across channels using a modern data stack.

Fivetran has been recognized in the "Gartner® Magic Quadrant™ for Data Integration Tools" for the third consecutive year due to its strong focus on simplifying and automating data movement. The company's growth is driven by supporting more hybrid, enterprise-scale use cases and transitioning customers from legacy data integration architectures to a modern data stack. Fivetran has also made significant enhancements in security and data governance capabilities for business-critical production workflows and sensitive data.

The majority of organizations are struggling with their AI maturity due to a lack of automated data integration, broad trust in AI, and the right expertise. While 85% of organizations are using AI models for strategic insights, only 14% consider themselves advanced in AI capabilities. Key challenges include manual data processes, limited IT resources, and insufficient stakeholder education on AI benefits. To advance AI initiatives, organizations should automate their data pipeline, free up data scientists to focus on high-value models and insights, and educate stakeholders to increase trust in AI. Automation is essential for handling the volume of data necessary for building and running AI and ML models, improving data quality, and achieving buy-in from stakeholders.

Fivetran and Snowflake offer a solution to gain real-time insights on SAP ERP data, helping businesses avoid stale data that can lead to poor outcomes and decision-making. By using high-volume data replication with log-based change data capture (CDC) and a data cloud platform, companies can scale their data pipelines, integrate data with unstructured systems, and create real-time insights. Fivetran's high-volume data replication solution provides efficient data extraction, load process from source to target, automation, performance optimization, security compliance, and support for various industries. Snowflake's Data Cloud platform offers flexibility in managing, integrating, and analyzing SAP ERP data while ensuring a secure and resilient environment. The combined power of Fivetran and Snowflake can support numerous beneficial use cases across multiple industries, such as real-time access to ERP data for manufacturers and retailers.

Fivetran has released a major update to its Ad Reporting package, which includes six new data models for ad reporting aggregated on the most common granularities for ad-related metrics. The new models are designed to help marketing analysts view their data at various levels of granularity and hit the ground running with their reporting needs. Additionally, Fivetran has updated its data models for all ad sources aggregated in this rollup, added a brand new Apple Search Ads package release, and introduced passthrough metrics to allow users to extend the models to meet specific reporting requirements. The company also plans to add consistent testing across all packages that roll up to Ad Reporting and freshness testing to all platform packages.

A recent survey by Foundry reveals that IT decision-makers are focusing on modernizing their infrastructure, automating data integration, and adopting lakehouse technology to leverage the massive amounts of data they collect. The survey found that companies spend an average of 41% of their time on data analytics projects dedicated to data integration and preparation. Modernization can help reduce operational risk, ensure high performance, and simplify ongoing data integration management. However, challenges such as expertise among teams, expediting time to market, and ensuring security and data governance must be addressed for successful modernization. Managed data pipelines can resolve these issues by providing prebuilt data models and industry-leading change data capture replication.