<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Rivery on Medium]]></title>
        <description><![CDATA[Stories by Rivery on Medium]]></description>
        <link>https://medium.com/@rivery.io?source=rss-aa3d201b51f3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*xdxXm0PU2IxPrhDdQyvL-Q.png</url>
            <title>Stories by Rivery on Medium</title>
            <link>https://medium.com/@rivery.io?source=rss-aa3d201b51f3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 01:21:07 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rivery.io/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[How to Improve Your Customer Support with Rivery & Snowflake Cortex]]></title>
            <link>https://medium.com/rivery-blog/how-to-improve-your-customer-support-with-rivery-snowflake-cortex-41ad029e3456?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/41ad029e3456</guid>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[salesforce]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[snowflake-cortex]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Tue, 23 Jul 2024 00:31:33 GMT</pubDate>
            <atom:updated>2024-07-30T13:02:15.105Z</atom:updated>
            <content:encoded><![CDATA[<p>At Rivery, we pride ourselves on <a href="https://rivery.io/blog/g2-summer-2024/">delivering exceptional customer support </a>. To maintain and improve our service, we continuously seek innovative ways to understand better and respond to our customers’ needs.</p><p>One of our latest initiatives involves leveraging the power of generative AI to analyze support tickets via and <a href="https://www.snowflake.com/en/data-cloud/cortex/">Snowflake Cortex </a>.</p><p>Rivery is a complete data integration platform that simplifies and speeds up data pipeline creation by seamlessly integrating hundreds of data sources. This eliminates the complexities of building and maintaining connections between structured and unstructured data sources. By linking Rivery with Snowflake, we can efficiently extract data from different support systems, load it into Snowflake, and transform it so it’s ready for large language models (LLMs) usage.</p><p>This enables us to utilize <a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/overview">Snowflake’s Cortex </a>functions to perform analysis using LLMs on top of pertinent and current data from any source via simple SQL queries. With this capability, generative AI can utilize the retrieved information to answer specific questions regarding your data. This blog post delves into why we are undertaking this initiative and how our engineering team utilized Rivery and Snowflake Cortex to summarize long text inputs from support tickets.</p><h3>Why Analyze Support Ticket Data With AI?</h3><p>One of the core differentiators of our product is the dedicated customer support we provide to our customers. This involves multiple support channels, often resulting in a ticketing system that handles customer tickets in a reasonable time.</p><p>Support tickets are a rich source of information about the challenges our customers face. By analyzing this often unstructured data with AI, we can identify common issues, recurring themes, and areas where our product may need enhancement. This understanding helps us proactively address problems before they escalate, ensuring our customers have a smoother experience.</p><p><strong>2 . Enhancing Response Times and Efficiency:</strong></p><p>Smarter support ticket analysis allows us to track metrics such as average response and resolution times. By identifying bottlenecks and inefficiencies, we can streamline our support processes, allocate resources more effectively, and ultimately reduce the time customers spend waiting for resolutions.</p><p><strong>3. Improving Product Development:</strong></p><p>Insights gained from support ticket data can directly influence our product development roadmap. By understanding the features and functionalities that generate the most support queries, we can prioritize improvements and new features that address the real needs of our users.</p><p><strong>4. Personalized Customer Support:</strong></p><p>With detailed analysis, we can offer more personalized support. Understanding the history of a customer’s interactions with our support team enables us to provide tailored solutions and build stronger, more trusting relationships with our users.</p><h3>Rivery and Snowflake Cortex enabling AI functionality</h3><p>Without Rivery and Snowflake Cortex, this process could have been a hassle for data teams to complete. It would involve a data engineer having to set infrastructure and code to extract data out of the ticketing data source. Prepping the data and then running it via a dedicated workflow for generative AI would have additional another large undertaking. The reality is that most data teams don’t have this luxury, and need a fast way for someone that knows SQL to perform all of this without relying on additional resources. Performing this process with Rivery and Snowflake is as simple as writing any SQL query.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*Myt1IJjI0u29-sgH.png" /></figure><h3>Step 1: Extract and load the Data into Snowflake</h3><p>The first step in our data workflow is to get the raw data from our support ticketing system to Snowflake to run further analyses on the text data. In this example the featured support ticketing system is <a href="https://rivery.io/integration/salesforce/">Salesforce </a>but it could also be , or any other system.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*MZu9PZ8Dh0WIAsPS.jpg" /></figure><p>We do this by creating a <a href="https://docs.rivery.io/docs/using-source-to-target-variables-in-logic-river">Source to Target River </a>(Data pipeline) in Rivery that loads the data from Salesforce to our target which is Snowflake. The steps are very simple:</p><ol><li>Configure a connection to your data source</li><li>Configure a connection to your data-target</li><li>Select what data to bring from the source</li></ol><p>Here is a short video of how to create such a data pipeline.</p><p>In this case, we selected the following support ticket tables to be replicated from Salesforce to Snowflake. These tables contain free-form text fields such as the case comments and responses captured within email exchanges:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*Yk3CtJB1Ueg-wltO.jpg" /></figure><h3>Step 2: Prepping the data &amp; Running Cortex Functions</h3><p>Next, we have to transform our data inside Snowflake which we can easily orchestrate via Rivery.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*qJoScXyBiQTMwIbo.jpg" /></figure><p>In the above process, we aggregate and concatenate the full history of case comments for each Case ID from the relevant tables into a single record instead of having separate records for each exchange related to the same support ticket (Case ID). This results in one unified set of all comments made on a specific ticket, which we can then use to run the cortex function.</p><p>From there, we can run a step that executes the Cortex functions to generate insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*K58kh1sb-5ESA432.png" /></figure><p>Let’s enlarge the SQL Query and preview the results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*V4ZNMT1UIxOt0So4.png" /></figure><p>For each ticket, we generate a summary to identify if the issue was resolved, what the resolution was, and whether the sentiment with the customer was positive, neutral, or negative. One thing to note is that running Cortex functions has a maximum limit on the number of tokens you can send as input variables. You can think of tokens as the equivalent of words, and most algorithms have a 32,000 token limit. Some of the cases we had in our tickets were going over that limit and we had to define our business logic to deal with this limit.</p><p>Let’s quickly break down the various Cortex functions we used and what they are accomplishing.</p><p><strong>Snowflake.Cortex.Summarize: </strong>Gets the full history of the ticket and summarizes it. <strong>Snowflake.Cortex.Complete: </strong>A versatile function in Snowflake that lets you specify its tasks. In our case, we used it to analyze support tickets to determine if each ticket was resolved and to provide a summary of the resolution type. We also required the result to be a valid JSON containing one of three options with no additional text. Finally, we consolidated this information into a single field named “case resolution and sentiment” and loaded it into a final table in Snowflake.</p><p>Here is the full SQL Query we used in this case:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/300/0*uWUBlpwkTnuGID70.jpg" /></figure><h3>Moving Forward</h3><p>By utilizing Rivery’s modern data integration platform, which seamlessly connects to hundreds of data sources, along with the LLM capabilities of Snowflake Cortex, we have effortlessly created a streamlined process to gain deeper insights into our customer support tickets. This enables us to provide superior support to our customers and continuously enhance our product to meet the ever-evolving needs of our users.</p><p>This process can be easily applied on top of any free-form unstructured text. Watch this short video to see a similar flow we put together on top of our product review data.</p><p>Ready to integrate and run generative AI on top of your data in Snowflake?</p><p><em>Originally published at </em><a href="https://rivery.io/blog/how-to-improve-your-customer-support-with-rivery-snowflake-cortex/"><em>https://rivery.io</em></a><em> on July 23, 2024 by Ariel Pohoryles.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=41ad029e3456" width="1" height="1" alt=""><hr><p><a href="https://medium.com/rivery-blog/how-to-improve-your-customer-support-with-rivery-snowflake-cortex-41ad029e3456">How to Improve Your Customer Support with Rivery &amp; Snowflake Cortex</a> was originally published in <a href="https://medium.com/rivery-blog">rivery-blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to build your personalized AI chatbot with Rivery & Amazon Q]]></title>
            <link>https://medium.com/@rivery.io/how-to-build-your-personalized-ai-chatbot-with-rivery-amazon-q-b4d3d83c98dd?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/b4d3d83c98dd</guid>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[ai-chat-bot]]></category>
            <category><![CDATA[genai]]></category>
            <category><![CDATA[amazon-q]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Fri, 28 Jun 2024 08:59:03 GMT</pubDate>
            <atom:updated>2024-07-30T12:58:00.600Z</atom:updated>
            <content:encoded><![CDATA[<p>Creating AI applications is now more accessible than ever, even if you’re not an AI expert. By leveraging tools like Rivery and Amazon Q, you can set up powerful AI-driven solutions without waiting for full data analytics maturity or hiring a team of AI specialists.</p><p>The process doesn’t require complex analytics infrastructure or advanced AI expertise. This guide demonstrates how to integrate <a href="https://rivery.io/">Rivery </a>and <a href="https://aws.amazon.com/q/">Amazon Q</a> to build your first AI application effectively.</p><h3>Providing Context for AI Applications</h3><p>A critical aspect of creating useful AI applications is providing the right context to the AI engine. Without the correct context, AI can produce inaccurate or misleading responses, a phenomenon known as <a href="https://cloud.google.com/discover/what-are-ai-hallucinations">AI hallucinations.</a> For instance, asking ChatGPT about building a data pipeline in Rivery might yield generic or incorrect information.</p><p>A popular way to avoid such issues is to employ a <a href="https://aws.amazon.com/what-is/retrieval-augmented-generation/">Retrieval Augmented Generation (RAG) workflow.</a> Conceptually, this involves loading specific, relevant data into your AI application to ensure that it generates accurate and contextually appropriate answers. Amazon Q, which is powered by Amazon Bedrock, is designed to <a href="https://docs.aws.amazon.com/amazonq/latest/qbusiness-ug/how-it-works.html#app-flow">seamlessly manage RAG workflows</a> to prevent these issues.</p><p>Amazon Q is a generative AI-powered assistant designed to interact with data from your enterprise systems. It helps in answering questions, providing summaries, generating content, and completing tasks securely and efficiently. Think of it as a personalized version of ChatGPT tailored to your enterprise data.</p><h3>Example Prompt in Amazon Q</h3><p>In this example, we will use Amazon Q to help us analyze customer support cases via an AI chatbot. The underlying LLM foundation models used by Amazon Q will enable users to get summarized answers using data from different support cases. This would easily answer questions like:</p><ul><li>What are the steps to solve a certain error?</li><li>What is our most common issue with a certain feature?</li><li>Which support documentation article needs to be improved?</li></ul><p>Here is an example of such chatbot in action:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FbWkXLpp3-uM%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DbWkXLpp3-uM&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FbWkXLpp3-uM%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/c9ad38bb173638d98b2dccde6344f687/href">https://medium.com/media/c9ad38bb173638d98b2dccde6344f687/href</a></iframe><p>This type of chatbot can help customer support agents quickly and accurately answer customer cases, including automatic responses. It can help you increase customer activation, satisfaction, reduce churn and ultimately save you time and effort.</p><p>It’s important to remember that Amazon Q not only provides answers but also includes citations linking to the sources it used to generate the answer. This feature is particularly useful for verifying responses and delving deeper into the provided information. In the example below, you can see the links to 3 different support cases Amazon Q used to construct the answer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/848/0*FPK3GmLz7yJVO3FK.png" /></figure><h3>The Set Up Process</h3><p>To utilize Amazon Q, you need to load data into the platform. Of course, this is where a platform like Rivery comes into play. Here is a sample data workflow for such process:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*v26SSqA9W3PfgJwj.png" /></figure><h3>Step 1 — Create an Amazon Q Application and Data Source</h3><p><strong>Create an Amazon Q Application</strong></p><p>Navigate to Amazon Q in your Amazon console, select Applications in the left menu, and choose Create application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*H944CzrDrXn8C12o.png" /></figure><p>Provide an application name and create a new or select an existing AWS Identity and Access Management (IAM) service role that Amazon Q can assume. Then, choose Create.</p><p><strong>Create a Retriever</strong></p><p>A retriever pulls data from the index in real time during a conversation. You can choose between two options: use the Amazon Q native retriever or use an existing Amazon Kendra retriever. The native retriever can connect to the Amazon Q supported data sources. Select the native retriever option. Then, choose Next.</p><p><strong>Connect Data Source to Amazon Q</strong></p><p>Amazon Q comes with built-in data source connectors. For this setup to work in tandem with any Rivery source, choose Amazon S3 and configure the data source by pointing to the S3 bucket to which you will load data via Rivery pipelines.</p><p>In addition to naming the source, identify the S3 bucket to pull from, and set the sync mode.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LEpA-ljU8mZOOu4c.png" /></figure><p>Once the initial configuration is set, click ‘Add data source.’</p><h3>Step 2 — Load Data with Rivery</h3><p>Rivery simplifies the process of building data pipelines into <a href="https://rivery.io/integration/s3/">Amazon S3</a>. You can create a source-to-target pipeline, or “River”, using <a href="https://rivery.io/integration/">Rivery’s extensive integrations</a> with numerous data sources using a few clicks.</p><p>For instance, you might choose <a href="https://rivery.io/integration/salesforce/">Salesforce</a> for support tickets but could also use <a href="https://rivery.io/integration/zendesk/">Zendesk</a>, <a href="https://rivery.io/integration/intercom/">Intercom</a>, or others. After defining the source and target (e.g., S3 or a data warehouse like <a href="https://rivery.io/integration/redshift/">Redshift </a>or <a href="https://rivery.io/integration/snowflake/">Snowflake</a>), you set up the data load frequency.</p><p>If your data can be extracted from the source and uploaded to S3 for Amazon Q to use it as is, you can simply setup your target to use S3.</p><p>Alternatively, if you need the data for other purposes, you can also set a different target such as Snowflake, Redshift or other and configure Rivery to use an <a href="https://docs.rivery.io/docs/custom-file-zone">S3 custom file zone</a> to ensure a copy of data is maintained in S3 along the way. This will ensure your data is uploaded to your final target via your own S3 buckets and will also serve as the Amazon Q data source.</p><p>Finally, in the case where you do need to prepare the data before you drop it in S3 as a data source, you could either load the int Amazon Athena, Snowflake, Redshift or other and then use a Rivery logic step to transform the data to your needs.</p><p>For example, in our scenario we knew that support cases were logged as separate records in our data, each representing a different email exchange as part of the case. To ensure each case is summarized together including all of the emails it involved, we built a Rivery <a href="https://docs.rivery.io/docs/logic-overview">logic river</a> where we looped over the different cases and generated one file export per case.</p><ol><li>Creating the Case IDs list variable and looping over it:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/0*i_3Rg7Xns08TXC84.png" /></figure><p>2. Running a query to union all the case details per case ID into a single query result and exporting the query result as a file to an Amazon S3 bucket so it can serve as the Amazon Q data source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/0*7nLvjszow1uNIjtD.png" /></figure><h3>Step 3 — Syncing Amazon Q with the latest Data</h3><p>To keep Amazon Q updated with the latest data, you can use Rivery to automate the sync process. This involves adding a step in your logic river to trigger an Amazon Q sync using a simple Python script. To make it even easier for you, we packaged this Python script in a <a href="https://rivery.io/kits/amazon-q-data-sync/">Rivery kit</a>. By using the kit within your account, you can easily add a step to your logic river in order to trigger the Amazon Q sync.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/0*AGjCQMAPFhhfoJp1.png" /></figure><p>Running this logic river step ensures that Amazon Q always uses the most current data from your S3 bucket.</p><h3>Moving forward</h3><p>As you can see, setting up a personalized AI chatbot doesn’t have to be complicated. With the simplicity offered by both Rivery and Amazon Q, it’s really a matter of choosing what data you will be loading into Amazon Q.</p><p>WIth Rivery’s 200+ data integrations, <a href="https://rivery.io/solutions/custom-data-integration/">custom connections</a>, and now <a href="https://rivery.io/product/blueprint/">Rivery Blueprint AI</a> to complete this process without needing to code, you should be able to bring any data with ease.</p><p>By combining Rivery and Amazon Q, you can set up secured and personalized AI applications with your data in just a few steps. It’s time to embrace the power of AI and start building innovative solutions today.</p><p><em>Originally published at </em><a href="https://rivery.io/blog/how-to-build-your-personalized-ai-chatbot-with-rivery-and-amazon-q/"><em>https://rivery.io</em></a><em> on June 28, 2024 by Ariel </em>Pohoryles</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b4d3d83c98dd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to orchestrate dbt Core jobs in 3 steps]]></title>
            <link>https://medium.com/@rivery.io/how-to-orchestrate-dbt-core-jobs-in-3-steps-53a369c36d2c?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/53a369c36d2c</guid>
            <category><![CDATA[dbt-cloud]]></category>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[dbt-labs]]></category>
            <category><![CDATA[data-transformation]]></category>
            <category><![CDATA[dbt-core]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Tue, 09 Jan 2024 07:31:40 GMT</pubDate>
            <atom:updated>2024-01-25T13:35:23.888Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7TKQS1zxXdP-3UofNhWA8A.png" /></figure><h4>In the realm of data transformation, <a href="https://docs.getdbt.com/">dbt</a> stands out as an exceptionally popular tool among modern data teams.</h4><p><strong>dbt </strong><a href="https://rivery.io/product/data-transformation/"><strong>data transformation tool</strong></a><strong> offers two versions:</strong></p><ul><li><strong>dbt Core</strong>: the open source version which is free to use, but requires setup and infrastructure maintenance to run it</li><li><strong>dbt Cloud™</strong>: the hosted version that offers Git-integrated code editing, job orchestration, APIs, and data quality controls on top of dbt Core’s transformation engine</li></ul><p>And guess which version is best? The one that fits with your business needs. From a professional standpoint, many of our customers choose two methods to transform their data. They either let us fully-manage their end-to-end pipelines and complete their entire ingestion, transformation, orchestration and even <a href="https://rivery.io/product/reverse-etl/">Reverse ETL</a> processes, or they opt to integrate with dbt for transformation.</p><p>When that happens, usually Rivery’s Logic Rivers, (rivers are our trademark name for pipelines) orchestrate a process where the data ingestion is executed by Source to Target Rivers. Once the ingestion steps are completed, dbt jobs are triggered to execute transformations.</p><p>To make this integration even more seamless, we created a <a href="https://rivery.io/kits/job-trigger-for-dbt-cloud/">kit to trigger dbt Cloud™ jobs</a> through its API. A Rivery kit is a ready-made workflow template that literally takes your data flow from point A to Z in a few clicks. <strong>So the question you might be asking yourself is: what about dbt Core?</strong> How can Rivery integrate with dbt Core if dbt Core doesn’t offer a native API to trigger dbt jobs?</p><p>Confusing right? Ok, well that’s what this article is all about. It’s about making dbt Core jobs happen efficiently.</p><h3>Rivery + dbt + GitHub Actions</h3><p>I recently spoke with a client who is using dbt Core and they asked me how Rivery could help orchestrate their data workflows. They currently use GitHub to manage their code but still trigger the jobs manually. That’s so time-consuming and it’s just not scalable.</p><p>So, our <a href="https://github.com/features/actions">GitHub Actions</a> offer the perfect solution. GitHub Actions make it so easy to automate a workflow execution on any GitHub event. They’re a popular choice for running dbt Core jobs.</p><p>Let’s take a closer look at how you can do this with a real-life example.</p><p>I created a dbt-labs sandbox project called “<a href="https://github.com/dbt-labs/jaffle-shop">The Jaffle Shop</a>“. I set up an MVP to use Rivery to ingest data, and then trigger a dbt Core job via GitHub Actions.</p><h3>3 steps to orchestrate dbt Core jobs</h3><h3>1. Set up the dbt project</h3><p>First thing I did was fork the dbt project in our Rivery Github account: <a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop">https://github.com/RiveryIO/dbt-demo-jaffle-shop</a></p><p>The Jaffle shop data is located directly in the repo, but in order to make the project a bit more realistic, copy the CSV file data onto a Google sheet. I used Rivery’s <a href="https://rivery.io/integration/gspreadsheet/">Google Sheet integration</a> to ingest my data. The data can also be saved to an SFTP, or a Cloud Bucket workflow in the same way.</p><p><strong>I then made a few configuration edits to ensure a smooth dbt job run:</strong></p><ul><li>In the profiles.yml, I edited the Snowflake piece to connect with a username/password instead of an external browser: <a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/profiles.yml#L11-L33">https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/profiles.yml#L11-L33</a></li><li>I also changed the default schema name from “jaffle_shop_raw” to “dbt_demo_raw” (<a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop/commit/0530b76ab292862cdaaba9b772831485de43a9c3">https://github.com/RiveryIO/dbt-demo-jaffle-shop/commit/0530b76ab292862cdaaba9b772831485de43a9c3</a>)</li></ul><h3>2. Create a GitHub Action workflow</h3><p>Once my dbt project was ready and working fine on my local environment, I moved on to use Github Actions to run the dbt job. I created this GitHub Action workflow: <a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/.github/workflows/dbt_run_job.yml">https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/.github/workflows/dbt_run_job.yml</a></p><p>The workflow is defined as follows:</p><ul><li>It can be triggered manually in GitHub (workflow_dispatch, L5) or via API call (repository_dispatch, L4)</li><li>It will set the environment variables required by the dbt project: <a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/.github/workflows/dbt_run_job.yml#L7-L16">https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/.github/workflows/dbt_run_job.yml#L7-L16</a></li><li>Then, in the GitHub Action Container, it will install Python and any required packages before running the commands “dbt run” and “dbt test”</li></ul><p>I ran my workflow manually to confirm the setup was correct:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*KZCg1QDTsVVkYVzaqgq3-g.png" /></figure><h3>3. Automate an API call to trigger the Action</h3><p>The last step was to use a REST API call to trigger this run. When a GitHub Action is defined to run on “repository_dispatch”, it means that we can make a <a href="https://docs.github.com/en/actions/using-workflows/events-that-trigger-workflows#repository_dispatch">POST request to the GitHub API</a> to trigger the action.</p><p>An API call made to <a href="https://api.github.com/repos/%7Brepos_owner%7D/%7Brepo_name%7D/dispatches">https://api.github.com/repos/{repos_owner}/{repo_name}/dispatches</a> with the body {“event_type”: my_event_type} will trigger the Action to run. The event_type can be used to filter which specific Github Action should be run — quite useful for bigger projects that may be hosted in the same repository!</p><p>If you want to do so, change your workflow to define the “types” of incoming events that should trigger the action (<a href="https://github.com/RiveryIO/dbt-demo-jaffle-shop/blob/main/.github/workflows/dbt_run_job.yml#L5">L5 of the workflow.yaml</a>):</p><p>on:</p><p>repository_dispatch:</p><p>types: [dbt_core_demo_rivery]</p><p>This API call in Rivery is easily configured via an <a href="https://docs.rivery.io/docs/action-rest-api-use-case">Action River </a>with the following inputs:</p><ul><li>API URL POST call: <a href="https://api.github.com/repos/RiveryIO/dbt-demo-jaffle-shop/dispatches">https://api.github.com/repos/RiveryIO/dbt-demo-jaffle-shop/dispatches</a></li><li>A request body (populated via a variable that will be set later on under the Logic River):<br>{“event_type”: “{event_type}”}</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*H2_KNCI2GJt4YKTfX0fA0Q.png" /></figure><p>In addition, we also handle the authorization using a bearer connection token under our Action River variables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*etQfns0u5GtTwtTVEyBkhw.png" /></figure><h3>The complete data workflow</h3><p>It’s time to put it all together — we are now ready to build our complete data workflow using a <a href="https://riveryio.wistia.com/medias/fbgof7mpqa">Logic River</a>:</p><p>First, ingest data from my data sources (i.e. Google Sheets) using Source to Target Rivers, then trigger my dbt job to transform my data in Snowflake using an Action River.</p><p><strong>A couple of notes:</strong></p><ul><li>The ingestion rivers are grouped within a <a href="https://docs.rivery.io/docs/logic-container">Logic River Container</a>. Using a Container allows us to ensure all the ingestion steps are occurring in parallel before the dbt transformation kicks off. In addition, it’s easier to read and modify the workflow by expanding and collapsing Containers.</li><li>The Action River {event_type} variable is set to the specific event (“dbt_core_demo_rivery”) we want to run in this case.</li></ul><h3><strong>Short summary</strong></h3><p>To sum up, combining River’s ingestion and orchestration abilities with dbt can yield a tightly integrated process for sophisticated data pipelines. The outcome is low data latency for the business with zero time wasted between ingestion and advanced transformation.</p><p><a href="https://rivery.io/free-trial/"><strong>TRY IT NOW FOR FREE</strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=53a369c36d2c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Cost of Migrating from One Data Warehouse to Another]]></title>
            <link>https://medium.com/@rivery.io/the-cost-of-migrating-from-one-data-warehouse-to-another-f033962f2b01?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/f033962f2b01</guid>
            <category><![CDATA[data-integration]]></category>
            <category><![CDATA[google-bigquery]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data-warehousing]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Mon, 27 Nov 2023 15:21:02 GMT</pubDate>
            <atom:updated>2023-11-27T15:21:02.239Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Cost of Migrating from One Data Warehouse to Another" src="https://cdn-images-1.medium.com/max/1024/1*wwSOA3HUwQeFFDxRTLjtxw.jpeg" /></figure><h4>More companies are starting to realize the importance of migrating from one data warehouse to another. This strategic shift is driven by various factors, all aimed at optimizing data infrastructure and unlocking its full potential. But successfully navigating the challenges of data warehouse migration requires careful planning and the right tools.</h4><h3>Why do companies migrate from one data warehouse to another?</h3><p>Companies may migrate data warehouses for a variety of reasons as technology and business needs evolve over time. The reason for these migrations is usually one or more of the following:</p><h3>Performance improvement</h3><p>As data volumes grow and query complexity increases over time, the existing data warehouse may struggle to deliver the required performance and meet SLAs. Migrating to a more modern and scalable solution can help improve query time and overall system performance.</p><h3>Cost reduction</h3><p>As data needs expand in the cloud, companies may find that their current data warehouse solution is becoming cost-prohibitive. Migrating to a more cost-effective alternative can lead to significant savings depending on the use case. The possible cost savings may be performance related, ease-of-use related to save on expensive technical skill sets, or licensing related by switching to a solution that is consumption-only. Beyond just reducing immediate costs, companies may consider the long-term total cost of ownership (TCO) when migrating due to factors like maintenance, training, and future scalability.</p><h3>Scalability</h3><p>As a company grows, its data requirements grow with it. Scalability may mean data volume, data velocity, or data variety. Beyond just the data itself, types of use case needs may scale out as well — what starts as a business intelligence and reporting need may eventually become a machine learning need for another data engineering team. In these cases, it’s important for companies to have the technology to power many different types of use cases across the organization.</p><h3>Data quality and governance</h3><p>Migrating to a new platform may allow for better data governance practices and improved data quality controls. In regards to data warehouses with consumption-based pricing models, this is essential for cost and usage tracking as well, so that companies can control and manage their spend at granular levels.</p><h3>Ecosystem</h3><p>The availability of a supportive user community and a rich ecosystem of tools and integrations are important factors in deciding to migrate data warehouses. In addition, integration needs will scale as businesses do. What worked for a maturing startup in terms of integrations may not work at scale, and may not be flexible enough to keep up with changes in the company’s data ecosystem. Changes in business strategy, partnerships, or acquisitions may require better integration between different data sources and systems. A migration can align the data warehouse with the company’s new integration requirements and ensure agility for future needs and changes.</p><h3>What are the challenges of performing a data warehouse migration?</h3><p>Once a company has decided to migrate from one data warehouse to another, the migration process itself can be complex and time consuming.</p><p>Data warehouse migrations are complicated endeavors that require careful planning, significant resources, and thorough testing. The costs and pains associated with the migration process can vary widely depending on the complexity of the data environment, the scale of the migration, and the chosen destination platform. Companies should conduct a comprehensive assessment and develop a detailed migration plan to mitigate these challenges and ensure a successful transition.</p><h3>Top 3 challenges of completing a data warehouse migration</h3><h4>1. Financial costs</h4><p>Depending on the destination data warehouse platform, you may incur licensing or subscription costs. Provisioning and maintaining the necessary compute and storage resources in the new data warehouse environment can also be a significant expense, as well as the physical cost to transfer data in the cloud. In addition to the technology, hiring external experts for planning, executing and optimizing the migration can be costly. Moreover, if any downtime or disruption occurs due to the migration, negative business impact can result in loss of revenue or productivity.</p><h4>2. Data Complexity Challenges</h4><p>As part of the migration, data may need to be cleaned, transformed, or restructured to fit the new data warehouse’s schema or data model. This process can be time-consuming and error-prone. For data validation, extensive testing may be necessary to ensure data integrity after the migration.</p><h4>3. Technical Challenges</h4><p>Ensuring that existing data and applications are compatible with the new data warehouse may require code modifications and custom ETL development. Migrating ETL pipelines, data connectors, and integrations with other systems can be challenging, especially if the existing connectors are written in custom code or processes are currently carried out manually to load data. Data Security and Compliance</p><h3>What approaches can you take to migrate data warehouses?</h3><p>First, let’s divide the migration process into parts.</p><h4>1. Replicating the ingestion loads from sources to point to the new DWH</h4><p>In order to minimize any downtime, it’s recommended to run both the ‘old’ and ‘new’ architectures in parallel to conduct appropriate quality checks and end user testing. First, you’ll need to replicate the existing source loads into the new target data warehouse. This is getting through the ‘EL’ of the full ELT replacement to a new data warehouse. This requires understanding how your existing extract and load processes work, which sources and entities you need to replicate, and the tools and technologies available to make the replication. If the existing extract and load processes are manual and rigid, this could be a crucial point for exploring alternative solutions that would allow for faster and easier data ingestion for not just the migration, but all future ingestion needs.</p><h4>2. Migrating the ETLs to leverage the new DWH’s syntax and functionality</h4><p>In addition to the source data replication to the new data warehouse, the data transformations, or business logic, will need to be migrated. Most of the time data transformations are written in SQL, or exist in the logic of an ETL tool. This part of the migration is usually the most taxing, as migrating SQL from one data warehouse to another can require changes in syntax and query structure. In addition, this can open a great opportunity for restructuring and optimizing existing data processes and ETLs in the new data warehouse. For example, imagine you are migrating from Redshift to Snowflake. This is a chance to assess if a lift-and-shift of existing data transformations make the most sense, or if there is a way to optimize based on Snowflake’s strength, such as handling semi-structured data.</p><h4>3. Switch downstream (data consumer) systems to point to the new DWH</h4><p>The final piece of the puzzle is to make the production ‘switch’ for end users. This requires updating the connections in downstream systems, such as Business Intelligence tools, to point to the new data warehouse instead of the old.</p><p>Note — a quick way for end users to see value (and not wait for the end of the entire migration process) is to replicate directly from the final tables produced in the ‘old’ data warehouse into the new one, so that this switch can happen before steps 1 and 2 are completed. This would require replicating data sets directly from the old data warehouse to the new one (such as Redshift to Snowflake). Some considerations in this case are tables versus views — if views are most commonly used by downstream systems, it may make the most sense to replicate the resulting data of the view itself temporarily, rather than recreate the logic of the view itself in the new warehouse, which would mean waiting for parts 1 and 2 of the migration above to be complete.</p><h3>How Rivery can help</h3><p>If we take into consideration the parts of a migration listed above, all three can be sped up by an easy-to-use and flexible ELT platform.</p><p>First, the ingestion processes to replicate can be self-service and simple with a tool like Rivery. In just a few clicks, you have your source data in your new data warehouse.</p><p>Check out this video to see how easy it is to <strong>replicate Google BigQuery data into your target destination:</strong></p><p><a href="https://demo.arcade.software/7VHAE0bEs4d4XV26gNm8?embed">Rivery.io Replicate Google BigQuery Data</a></p><p><a href="https://rivery.io/blog/google-bigquery"><strong>You can learn all about the benefits of Google BigQuery as a source here.</strong></a></p><p>In addition, as noted above, you can replicate your existing tables and views from the ‘old’ data warehouse directly into the new one, to speed up time to value for end users while you finish out the rest of the migration process and rebuild the transformations, etc., in the new data warehouse logic.</p><p>Ideally the migration and ongoing orchestration once migrated can happen all in the same place, so that your extract and load processes can coexist with the downstream transformations that are dependent on these loads.</p><p><strong>If you’re just starting out or even in the midst of migrating from one data warehouse to another and need assistance, </strong><a href="https://rivery.io/book-call/"><strong>feel free to consult with us anytime</strong></a></p><pre>Author: Taylor McGrath, VP Solutions Engeering, Rivery. </pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f033962f2b01" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CDC Methods: Laying the Groundwork for Effective Change Data Capture]]></title>
            <link>https://medium.com/@rivery.io/cdc-methods-laying-the-groundwork-for-effective-change-data-capture-72601a3f346d?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/72601a3f346d</guid>
            <category><![CDATA[etl-pipeline]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[etl-tool]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Thu, 10 Aug 2023 13:08:18 GMT</pubDate>
            <atom:updated>2023-08-10T13:08:18.032Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="CDC" src="https://cdn-images-1.medium.com/max/1024/1*tWWB9S9-2871sGrx6cjG8A.jpeg" /></figure><p>In the ever-evolving terrain of data management, replicating incremental database changes is critical to preserving data integrity and ensuring real-time availability. But that’s not enough. For Change Data Capture (CDC) to be effective it needs to be implemented differently in different types of databases. Not all CDC engines are born equal, which is exactly why we built our own.</p><p>We re-architectured, re-coded and designed our <a href="https://rivery.io/blog/change-data-capture-must-haves/">CDC engine to work for every type of database</a>. In just five clicks, we make CDC replication happen. We’ve been in the trenches and we want to save you going through an overly complicated route. In this blog, we’ll guide you on everything you need to know about the different types of CDC methods for each type of database. Let’s dive in!</p><h3>MySQL Binary Log Overview</h3><p>Capturing incremental changes from a MySQL database using the Binary Log is a reliable method. The Binary Log is a built-in feature of MySQL designed to provide a durable and consistent record of all changes made to the database. Here are a few aspects that contribute to the reliability of this approach:</p><p><strong>Transactional Consistency:</strong> The Binary Log records changes at the transaction level, ensuring that all modifications within a single transaction are captured as a unit. This guarantees consistency and maintains data integrity when capturing and applying changes.</p><p><strong>Durability:</strong> The Binary Log is typically stored on disk, making it persistent and resistant to data loss. As long as the Binary Log files are retained, you can rely on them to capture changes accurately, even in the event of system restarts or failures.</p><p><strong>Replication Protocol:</strong> The protocol used to stream Binary Log events from the MySQL server to the CDC application is designed for reliability and fault tolerance. It ensures that events are transmitted reliably over the network and can handle intermittent network issues or temporary interruptions.</p><p><strong>Point-in-Time Recovery:</strong> The Binary Log allows you to perform point-in-time recovery, which is crucial for data consistency and reliability. In case of database restoration or disaster recovery scenarios, you can use the Binary Log to restore the database to a specific point in time, applying the captured changes to achieve consistency.</p><h4>While capturing changes from the Binary Log is generally reliable, it’s important to consider a few factors:</h4><p><strong>Binary Log Configuration:</strong> Ensure that binary logging is properly configured on the MySQL server and that the appropriate settings are in place to meet your reliability requirements.</p><p><strong>Monitoring and Maintenance:</strong> Regularly monitor the Binary Log files to ensure they are being generated and written correctly. Implement proper backup and archiving strategies to avoid data loss or corruption.</p><p><strong>Error Handling and Monitoring:</strong> Implement robust error-handling mechanisms in your CDC application to handle any issues that may arise during the capture process. Monitor the CDC process for any potential errors or failures and have appropriate alerting mechanisms in place.</p><p><strong>Testing and Validation:</strong> Thoroughly test and validate your CDC implementation to ensure that changes are accurately captured and applied. Perform tests against various scenarios, including system failures, network interruptions, and high-load situations, to verify the reliability of your solution.</p><p>By considering these factors, closely monitoring the process, and following best practices, capturing incremental changes from a MySQL database using the Binary Log can be a reliable and effective method for Change Data Capture.</p><h3>Postgres WAL Overview</h3><p>Capturing incremental changes from a Postgres database using the Write Ahead Log (WAL) is considered a highly reliable method. The WAL is a core component of Postgres designed to ensure durability, consistency, and recoverability. Here are some factors that contribute to the reliability of capturing changes using the WAL:</p><p><strong>Transactional Consistency:</strong> The WAL records changes at the transaction level, guaranteeing that modifications within a transaction are captured atomically. This ensures that captured changes maintain data consistency and integrity.</p><p><strong>Durability:</strong> The WAL is stored on disk, making it durable and resistant to data loss. It provides a persistent and reliable record of all changes made to the database, even in the event of system failures or crashes.</p><p><strong>Write-Ahead Logging:</strong> Postgres follows a write-ahead logging approach, where changes are written to the WAL before being applied to the main data files. This guarantees that changes are captured in the log before they are persisted to the database, ensuring that they are available for CDC purposes.</p><p><strong>Point-in-Time Recovery:</strong> The WAL allows for point-in-time recovery, which is crucial for maintaining data consistency and reliability. By replaying the WAL, you can restore the database to a specific point in time, applying the captured changes to achieve consistency.</p><p><strong>Streaming Replication:</strong> Postgres provides a built-in streaming replication mechanism that uses the WAL to replicate changes to standby servers. This replication mechanism is highly reliable and can be leveraged for capturing changes for CDC purposes as well.</p><p><strong>Crash Recovery:</strong> In the event of a crash or unexpected shutdown, Postgres has a crash recovery mechanism that ensures the integrity of the database. The WAL is used during recovery to bring the database back to a consistent state, including applying any unprocessed changes.</p><h4>While capturing changes from the WAL is generally reliable, it’s important to consider the following factors:</h4><p><strong>WAL Archiving:</strong> Ensure that WAL archiving is properly configured and that you have appropriate strategies for archiving and retaining WAL files. This is crucial for maintaining a complete and reliable history of changes.</p><p><strong>Monitoring and Maintenance:</strong> Regularly monitor the WAL files to ensure they are being generated and stored correctly. Implement proper backup and retention strategies to avoid data loss or corruption.</p><p><strong>Error Handling and Monitoring:</strong> Implement robust error-handling mechanisms in your CDC application to handle any issues that may arise during the capture process. Monitor the CDC process for potential errors or failures and have appropriate alerting mechanisms.</p><p><strong>Testing and Validation:</strong> Thoroughly test and validate your CDC implementation to ensure that changes are accurately captured and applied. Perform tests against various scenarios, including system failures, network interruptions, and high-load situations, to verify the reliability of your solution.</p><p>By considering these factors, closely monitoring the process, and following best practices, capturing incremental changes from a Postgres database using the Write Ahead Log can be a highly reliable method for Change Data Capture.</p><h3>SQL Server CDC Overview</h3><p>Capturing incremental changes from a SQL Server database using Change Data Capture (CDC) is considered a reliable method. CDC is a native feature of SQL Server specifically designed for capturing and tracking changes made to tables. Here are several factors that contribute to the reliability of CDC:</p><p><strong>Transactional Consistency:</strong> CDC captures changes at the transaction level, ensuring that modifications within a transaction are captured as a single unit. This guarantees data consistency and integrity when capturing and applying changes.</p><p><strong>Built-in Support:</strong> CDC is a native feature provided by SQL Server, meaning it is developed and maintained by Microsoft, the creators of SQL Server. As a built-in feature, it is designed to work reliably and seamlessly with the database engine.</p><p><strong>Log Scanning:</strong> CDC leverages transaction log scanning to identify and capture changes made to tables. The transaction log is a durable and reliable component of SQL Server that records all changes to the database. CDC uses this log to accurately capture the changes without impacting the performance of the database.</p><p><strong>Low Overhead:</strong> CDC is designed to have minimal impact on the performance of the database. The log scanning process is optimized to efficiently extract and capture changes, ensuring that the CDC operation does not significantly impact the overall system performance.</p><p><strong>Replication Support:</strong> CDC provides built-in support for replicating the captured changes to other databases. SQL Server’s replication mechanisms are well-established and proven to be reliable, ensuring that changes are reliably propagated to the target databases.</p><p><strong>Change Tracking Tables:</strong> CDC creates change tracking tables that store the captured changes. These tables are part of the system and are managed by SQL Server, ensuring their reliability and consistency.</p><p><strong>Resilience:</strong> CDC includes mechanisms to handle failures and ensure the reliability of the change capture process. It can handle interrupted or failed operations and provides methods for managing and recovering from errors.</p><p><strong>Point-in-Time Recovery:</strong> CDC allows for point-in-time recovery by tracking the changes made to the database. This enables you to restore the database to a specific point in time and apply the captured changes, ensuring data consistency and reliability.</p><h4>While CDC is generally reliable, it’s essential to consider the following factors:</h4><p><strong>Proper Configuration:</strong> CDC requires appropriate configuration and setup within the SQL Server instance. It is crucial to follow the recommended guidelines and best practices provided by Microsoft to ensure the reliability of CDC.</p><p><strong>Monitoring and Maintenance:</strong> Regularly monitor the CDC process and ensure that it is functioning correctly. Implement proper backup and maintenance strategies to safeguard the change tracking tables and related metadata.</p><p><strong>Error Handling and Monitoring:</strong> Implement robust error handling mechanisms in your CDC application to handle any issues that may occur during the capture and replication process. Monitor the CDC process for potential errors or failures and have appropriate alerting mechanisms in place.</p><p>By considering these factors, closely monitoring the process, and following Microsoft’s guidelines, capturing incremental changes from a SQL Server database using Change Data Capture can be a reliable method for Change Data Capture.</p><h3>SQL Server Change Tracking Overview</h3><p>Capturing incremental changes from a SQL Server database using Change Tracking is considered a reliable method. Change Tracking is a native feature provided by SQL Server to track changes made to user tables. Here are several factors that contribute to the reliability of Change Tracking:</p><p><strong>Transactional Consistency:</strong> Change Tracking captures changes at the transaction level, ensuring that modifications within a transaction are tracked as a single unit. This guarantees data consistency and integrity when capturing and processing changes.</p><p><strong>Built-in Support:</strong> Change Tracking is a built-in feature of SQL Server, developed and maintained by Microsoft. As a native feature, it is designed to work reliably and seamlessly with the database engine.</p><p><strong>Low Overhead:</strong> Change Tracking is designed to have minimal impact on the performance of the database. It operates by scanning the transaction log for changes, without significantly affecting the overall system performance.</p><p><strong>Change Tracking Tables:</strong> SQL Server creates change tracking tables that store the tracked changes. These tables are managed by SQL Server and ensure the reliability and consistency of the captured data.</p><p><strong>Granular Tracking:</strong> Change Tracking allows you to track specific columns of interest, providing granular control over the changes you capture. This flexibility ensures that only relevant changes are tracked, reducing unnecessary overhead.</p><p><strong>Incremental Tracking:</strong> Change Tracking operates in an incremental manner, only capturing and storing the changes that occur after the last synchronization. This approach improves efficiency and reduces the processing overhead for capturing subsequent changes.</p><p><strong>Resilience:</strong> Change Tracking includes mechanisms to handle failures and ensure the reliability of the change capture process. It can handle interrupted or failed operations and provides methods for managing and recovering from errors.</p><p><strong>Point-in-Time Recovery:</strong> Change Tracking, combined with other database backup and recovery mechanisms, enables point-in-time recovery. By leveraging the tracked changes, you can restore the database to a specific point in time and apply the captured changes to achieve data consistency and reliability.</p><h4>While Change Tracking is generally reliable, it’s important to consider the following factors:</h4><p><strong>Proper Configuration:</strong> Change Tracking requires proper configuration and setup within the SQL Server instance. Ensure that Change Tracking is enabled for the relevant tables and follow the recommended guidelines provided by Microsoft to ensure reliability.</p><p><strong>Monitoring and Maintenance:</strong> Regularly monitor the Change Tracking process to ensure it is functioning correctly. Implement appropriate backup and maintenance strategies to safeguard the change-tracking metadata and tables.</p><p><strong>Error Handling and Monitoring:</strong> Implement robust error-handling mechanisms in your application to handle any issues that may occur during the capture and processing of changes. Monitor the Change Tracking process for potential errors or failures and have appropriate alerting mechanisms in place.</p><p>By considering these factors, closely monitoring the process, and following Microsoft’s guidelines, capturing incremental changes from a SQL Server database using Change Tracking can be a reliable method for Change Data Capture.</p><h3>MongoDB Change Streams Overview</h3><p>Capturing incremental changes from a MongoDB database using Change Streams is generally considered a reliable method. Change Streams is a feature introduced in MongoDB version 3.6 that allows you to track changes in real-time at the collection level. Here are several factors that contribute to the reliability of Change Streams:</p><p><strong>Real-Time Capture:</strong> Change Streams provide real-time change capture, allowing you to receive notifications of changes as they happen in the database. This ensures timely and accurate capture of incremental changes.</p><p><strong>Event-Driven Architecture:</strong> Change Streams use an event-driven architecture to propagate changes. The MongoDB server pushes change events to subscribing applications, minimizing the delay between the occurrence of a change and its capture.</p><p><strong>Resilience:</strong> MongoDB provides built-in mechanisms for handling network interruptions and failover scenarios. Change Streams can automatically reconnect and resume tracking changes when connectivity is restored or in the event of server failover, ensuring the reliability of the capture process.</p><p><strong>Scalability:</strong> Change Streams can be distributed across replica sets or sharded clusters in MongoDB, allowing for high availability and scalability. This enables capturing changes reliably even in large-scale and distributed environments.</p><p><strong>Efficient and Low Overhead:</strong> Change Streams are designed to have low overhead on the MongoDB server. They utilize the underlying replication infrastructure and oplog (operation log) to efficiently track changes without impacting the performance of the database.</p><p><strong>Consistent Ordering:</strong> Change Streams guarantee the order of captured changes based on their occurrence in the oplog. This ensures that changes are captured and processed in the same order as they were applied to the database, maintaining data consistency.</p><p><strong>Failure Handling:</strong> Change Streams provide mechanisms to handle failures and ensure reliability. Applications can acknowledge and process changes, ensuring that changes are not lost if the subscribing application encounters temporary errors or issues.</p><p><strong>Change Event Structure:</strong> Change Streams provide detailed change events that include information such as the type of change (insert, update, delete), the affected document, and other relevant metadata. This struc^[[Oture enhances the reliability and flexibility of capturing and processing changes.</p><h4>While Change Streams are generally reliable, it’s important to consider the following factors:</h4><p><strong>Proper Configuration:</strong> Ensure that Change Streams are properly configured on the MongoDB server and that the necessary permissions are granted to subscribing applications. Follow the MongoDB documentation and best practices for optimal configuration.</p><p><strong>Monitoring and Error Handling:</strong> Monitor the Change Streams process and implement appropriate error handling mechanisms in your application to handle potential issues or failures. Have proper alerting and monitoring in place to detect and address any problems that may occur.</p><p><strong>Compatibility and Versioning:</strong> Keep in mind that Change Streams were introduced in MongoDB version 3.6. Ensure that you are using a compatible version of MongoDB that supports Change Streams and consider any version-specific considerations or limitations.</p><p>By considering these factors, closely monitoring the process, and following MongoDB’s guidelines, capturing incremental changes from a MongoDB database using Change Streams can be a reliable method for Change Data Capture.</p><h3>Oracle Redo Logs Overview</h3><p>Capturing incremental changes from an Oracle database can be done using various methods. This includes Oracle XStreams (requires a GoldenGate licensing) as well as Oracle Redo Logs.</p><p><strong>Redo Logs:</strong> Using Oracle’s Redo Logo for CDC purposes, involves reading and analyzing the redo log files with the help of Oracle’s built-in utility LogMiner. LogMiner helps to capture and convert real-time data changes written to the Redo Logs at the transaction level, providing a low-overhead method for real-time data capture and replication.</p><p><strong>Streaming Replication: </strong>Originally, the Redo Log files enable the Server or DBA to re-do transactions in case a recovery operation is needed.</p><h4>While capturing changes from the Redo Logs is generally reliable, it’s important to consider the following factors:</h4><p><strong>Data Availability: </strong>Since reading the changes is happening from the archived Redo Logs, the target database can take up to 5 min to be updated with the recent changes.</p><p><strong>Table and Column Names: </strong>LogMiner requires table and column names that are 30 characters or less. Therefore, one must ensure that table and column names adhere to this limit to avoid errors when using CDC via LogMiner.</p><p>By considering these factors, closely monitoring the process, and following best practices, capturing incremental changes from an Oracle database using the Redo Logs can be a reliable and effective method for Change Data Capture.</p><h3>Let’s recap</h3><p>Change Data Capture (CDC) is a powerful and reliable method for incremental replication from various databases, such as MySQL, Postgres, SQL Server, Oracle, and MongoDB. Each database system needs to use a different CDC method to handle incremental replication. Whether using Binary Log in MySQL or Change Streams in MongoDB, an efficient CDC method is required for transactional consistency, durability, point-in-time recovery, and low overhead.</p><p>By adhering to best practices, organizations can confidently utilize CDC for <a href="https://rivery.io/data-learning-center/best-data-integration-tools/">data integration</a> and synchronization, ensuring data consistency and integrity across distributed systems. Check out our <a href="https://rivery.io/blog/change-data-capture-must-haves/"><strong>6 Musts for Headache-Free Change Data Capture (CDC)</strong></a>– it covers just what you need.</p><p>Want to see how you can conduct incremental replication from any database in just a few clicks? <a href="https://rivery.io/free-trial/"><strong>Start your free trial here</strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=72601a3f346d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Migrate to Google Analytics 4 in 3 Steps]]></title>
            <link>https://medium.com/@rivery.io/how-to-migrate-to-google-analytics-4-in-3-steps-e016d43d6c13?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/e016d43d6c13</guid>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-analytics-4]]></category>
            <category><![CDATA[universal-analytics]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Wed, 28 Jun 2023 09:52:16 GMT</pubDate>
            <atom:updated>2023-06-28T14:12:23.296Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*19VAQOaolFVFFdrn34Emgg.jpeg" /></figure><p>GA4 day (Google Analytics 4) is almost here. Are you ready?</p><p>On July 1st, Google will be sunsetting Universal Analytics (UA). All UA accounts will stop pulling in new data from your website. <a href="https://support.google.com/analytics/answer/11583528?hl=en)">According to Google</a>, you’ll still be able to access processed data in your Universal property for at least six months and all your legacy data will be available until July 1st, 2024.</p><p>So what does this mean for your data pipelines? If you don’t want to risk losing your analytics metrics, this overview is for you. It’ll guide you through what you need to know about Google Analytics 4 and the exact steps you need to take to make the switch.</p><p>Let’s dive in.</p><h3>Understanding the transition to Google Analytics 4</h3><p>In a cookie-less, app-connected world, Universal Analytics can’t keep up anymore. There is a growing need to handle analysis from multiple platforms and utilize data that is modeled as anonymously as possible, prioritizing privacy and transparent analytics practices. UA can’t effectively track different platforms at the same time and meet these new demands for data processing.</p><p>Google Analytics 4 and Universal Analytics have coexisted for the past two and a half years. Truth is, most companies had UA, yet few have migrated to GA4 alone. Time’s up and Google is forcing everyone to make the transition. Google will automatically migrate properties to GA4 on July 1st. It’s highly recommended you configure the migration by yourself. Keep reading to ensure you follow the necessary steps to make the switch.</p><h3>What is Google Analytics 4?</h3><p>In Google’s own words, here’s the most accurate description: Google Analytics 4 is “the next generation of Analytics which collects event-based data from both websites and apps.”</p><h3>What are the main differences between GA4 and UA?</h3><p>The event-based part is the major difference with Universal Analytics, as UA heavily relied on third-party cookies and was session-based. In today’s hyper-connected world with more stringent data ownership and privacy regulations over data processing, like GDPR, Google is ditching third-party cookies and changing how it’s collecting data and implementing analytics.</p><h3>Here are key differences between GA4 and UA:</h3><ul><li>GA4 uses event-based tracking. It’s designed to work without third-party cookies unlike UA. It only uses first-party cookies combined with AI to fill in the gapsGA4 dashboards are more customizable but have fewer out-of-the-box reports</li><li>GA4’s event-based reporting is much more suited for app tracking and cross-platform tracking, and not just web tracking</li><li><a href="https://support.google.com/analytics/answer/11986666?hl=en#zippy=%2Cin-this-article">GA4 and UA metrics are slightly different</a>, but we’ll dive into how they compare in the further on</li><li><a href="https://support.google.com/analytics/answer/11198161?hl=en&amp;sjid=3491764827697840564-EU">GA4 can take up to 24+ hours to process data</a>, whereas UA was more instantaneous</li><li>GA4 has new features that aren’t available in UA, such as the ability to track user engagement across devices and platforms, and use AI to generate predictive metrics like purchase/churn probability and predicted revenue</li></ul><h3>Your UA to GA4 migration checklist</h3><p>If you have Universal Analytics data, <a href="https://support.google.com/analytics/answer/10762548?sjid=7409083331254805442-EU#zippy=%2Cin-this-article">Google</a> will automatically try to migrate your properties but it won’t necessarily match what you want. Here’s how you can get proactive about it:</p><h3>Step 1: Create your GA4 reports</h3><p>In order to create your GA4 reports, it’s best to follow Google’s official walkthrough video or their migration guide for beginners.</p><h3>In a nutshell, you will need to:</h3><ul><li>Create a GA4 property using the GA4 Setup Assistant and create your data streams</li><li>Install Google Tags on your website to allow GA4 to collect data</li><li>Turn on Google Signals to collect additional data about users who have turned on ads personalization and who have signed in to their Google Accounts</li><li>Finally, <a href="https://support.google.com/analytics/topic/13012887?hl=en&amp;ref_topic=10737980&amp;sjid=12393844989017555415-EU">migrate your goals, users, audiences, and ad links from Universal Analytics</a></li></ul><h3>Step 2: Compare your UA and GA4 data</h3><p>It’s a bit tricky to compare UA and GA4 data because GA4 is based on events and UA is not. it’ is interesting to note some of the differences regarding the UA and GA4 metrics, which are explained in this <a href="https://support.google.com/analytics/answer/11986666?hl=en#zippy=%2Cin-this-article">article</a>. To sum it up, here’s a high-level overview of the metrics:</p><p><strong>Users</strong></p><p>The primary metric in UA was the total number of users (Total Users), that exists in GA4 but GA4 uses <strong>Active users </strong>as a primary metric now, which represents users having a higher interaction (longer session, conversion events or more page views).</p><p><strong>Pageview</strong></p><p>Similar metric in UA and GA4, but in GA4 it combines both web and app data in the same property so make sure to include UA app data when comparing the two.</p><p><strong>Purchases</strong></p><p>Should match closely in UA and GA4 if the transaction_id parameter is unique and consistent.</p><p><strong>Sessions</strong></p><p>Similar metrics in UA and GA4 but in UA a new session was always at midnight — it no longer is in GA4 and can have an impact depending on your timezone. In any case the new value in GA4 should be more accurate.</p><p><strong>Conversions</strong></p><p>You need to define similar conversion events if you want the metric to match. UA counts 1 conversion per session, while GA4 counts every instance of the conversion event, even if the same conversion event is recorded multiple times during the same session.</p><p><strong>Bounce Rate</strong></p><p>In UA, bounce rate is the percentage of all sessions on your site in which users viewed only one page and triggered only one request to the Analytics server, whereas in GA4 the bounce rate is the inverse of Engagement rate (number of <a href="https://support.google.com/analytics/answer/9143382#engaged-sessions&amp;zippy=%2Cuser%2Cevent%2Csession">sessions that are <em>not engaged</em></a>).</p><p><strong>Event Count</strong></p><p>This is a major difference and no comparison is possible. Events could be set up in UA, but in GA4 all actions are events and GA4 events have no notion of Category, Action, or Label.</p><h3>Step 3: Export your historical data and your new data</h3><p>After July 1st, 2023, you’ll be able to access your previously processed data in your Universal Analytics property only until July 1, 2024. If you want to be able to access your data later, you need to export your historical reports during this time.</p><h3>Google offers several export methods:</h3><ol><li>Manually export individual reports into files (csv, tsv, excel, google sheets, pdf)</li><li>Use the <a href="https://developers.google.com/analytics/devguides/reporting/core/v4">Google Analytics Reporting API</a> to connect and export data</li><li>Google Analytics 360 customers can also <a href="https://support.google.com/analytics/answer/3437618">export to BigQuery</a></li></ol><h3>How to export using Rivery</h3><p>If you want to export your data into a cloud data warehouse or data lake, and can’t use the GA360 feature with BigQuery, Rivery offers you a turnkey kit to export all your Universal Analytics data without any code.</p><p>To get started, create a <a href="https://rivery.io/">Rivery</a> account and grab our <a href="https://rivery.io/kits/google-analytics-to-snowflake/">Google Analytics kit</a> for free.</p><p>When you use the kit, it will automatically download a set of data pipelines into your account, that you can then customize. Each one of these pipelines imports <a href="https://docs.rivery.io/docs/google-analytics-predefined-reports">predefined reports</a> from Universal Analytics to your target destination.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*h3E-IDqtidrQ8dGs.png" /></figure><h3>Here’s how it works:</h3><ol><li>Add the database and schema in which you want your data to land</li><li>Select the date range of the extraction and chunk size</li><li>Once all the Google Analytics — Entity rivers are set up, launch the Google Analytics Logic River</li></ol><p>That’s it ! All your data will land into your target smoothly.</p><h3>How to export your GA4 data using Rivery</h3><p>Now that you have saved your UA data, it’s time to get your GA4 data replicated in your data warehouse too!</p><p>Rivery integrates seamlessly with Google Analytics to retrieve your GA4 Standard Reports and GA4 Cohort Reports. Simply select the desired metrics and dimensions and get the river running. We also support custom dimensions and metrics, that are personally created by you, to enable you to examine data that caters to your unique business requirements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*FSxZP6R5RoNQaWuO.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BclvvtCjgHglGpAo.png" /></figure><h4>Have more accounts or properties that need specific configurations?</h4><p>No worries, you won’t need to duplicate your work thanks to Rivery’s sub rivers. They enable you to use the same river to get your reports data for multiple configurations, whether you need to update the source parameters like account ID or properties, or the target configuration like the database, schema name and table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Jx30oz_uJHPYbtU5.png" /></figure><h3>Closing the GA4 loop</h3><p>There is not much time left to migrate, so start the migration process now and don’t forget to export your Universal Analytics data if you want to access it in six months time. Rivery offers you a fast and easy solution.</p><p>We also offer advanced export features with GA4, such as pulling custom dimensions and metrics, and we will soon be releasing predefined reports too, so stay tuned!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TsczaEMqxLPMs5si.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e016d43d6c13" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Reverse ETL, ELT, Data Activation: not confusing at all right?!]]></title>
            <link>https://medium.com/@rivery.io/reverse-etl-elt-data-activation-not-confusing-at-all-right-59af28b1a931?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/59af28b1a931</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[reverse-etl]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-activation]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Mon, 13 Mar 2023 17:17:31 GMT</pubDate>
            <atom:updated>2023-03-17T14:06:46.423Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s1_QB7kThzki6UjI2XR42A.jpeg" /></figure><p>When I hear the varying terminologies for data integration, the first thing that crops up in my mind is the scarecrow from the Wizard of Oz, “This way, that way, all the way…” It perfectly epitomizes the rapidly evolving data industry with its ever-changing terminology.</p><p>Let’s take Reverse ETL, it’s based on the idea that a System of Record (SoR) can receive updates to its data which was altered somewhere else. It used to be called a “data synchronization process”, but that doesn’t have the heft of an acronym. Both born out of the <em>expectation </em>that a “System of Record” should house the most accurate representation of its data. In reality, that’s rarely the case.</p><p>To be fair, this is a changing trend as platforms respond from demand and provide more flexibility by offering “miscellaneous” fields or expandable objects. <a href="https://rivery.io/data-management-glossary/reverse-etl/">Reverse ETL</a> has gained more popularity as a result. It removes the need to bounce between platforms such as Salesforce and a Data Warehouse such as Snowflake to get to all the data required. With Reverse ETL, it’s possible to retrieve the calculated NPS score from the warehouse and store that value on the customer record in your CRM. This helps to make the end user’s experience much more palpable.</p><p>Not too long ago, we used to printouts from varying systems and make do with what we had.. That’s no longer the case. As the demand for evidence-based decisions has increased, so has our reliance on systems to create and maintain data that fuels those decisions.</p><p>This imperative, combined with the transformative and expansive capabilities of the Cloud has exploded our expectations. We’ve discovered that we need to enable <a href="https://rivery.io/blog/what-is-reverse-etl-guide-for-data-teams/">Reverse ETL</a> everywhere. The old batched legacy server ecosystem that once got us through the dotcom bubble ETL tool, but rather an entirely new way of thinking about how data <em>ought</em> to flow in the enterprise. Enter data activation.</p><h3>What is data activation?</h3><p>If Reverse ETL is data flowing up-river (pun intended), then we can think of data activation as the swelling of the Nile, providing sustenance to an entire geographic region. Data activation is the realization that a SoR can’t possibly manage all of the contexts and usages of a data entity; nor can it derive potential actions for given scenarios.</p><p>Take a “Customer” for example. We all know what a customer is, but the context by which we interact with customers varies according to our role or situation. A point of sale simply needs to know cash or credit or an address to deliver the refrigerator. A marketing system needs to know when you last purchased or your favorite color. A support system needs to establish if you are a happy customer or not.</p><p>All these systems have specific purposes for interacting with customers, and as such have the potential to impact the customer in a SoR kind of way. The combined contexts and perspectives of all the systems can be combined (yet another term: insights) to inform the ideal <em>action</em> that we should take to meet our objectives and goals, like not to lose customers and increase retention.</p><p>Suddenly, an event triggers a sudden dip in NPS — this is time sensitive and outreach should happen quickly to avoid churn. This can’t happen with the data warehouse all alone. CRM systems are purpose-built for this precise motion. Additionally, the CRM system could automatically ask for feedback when the NPS changes, and respond using AI or other <em>activated</em> technologies. The possibilities are endless.</p><p>This is a basic example, but it demonstrates how we rely on data in ways that we have never been so acutely aware of before. We <strong>need</strong> data to be accessible and accurate, according to our contextual realities, and for us to be effective and efficient. Data activation is the application of the composite insight(s) that is the result of blended, constantly changing contexts.</p><p>Essentially, Reverse ETL is a point-to-point gap-fill, where data activation democratizes data, and also takes the next step of enabling action.</p><h3>What we really need</h3><p>For action, we need insights to be automatic. This implies (contentious term coming) <em>real-time</em> data availability — everywhere. It isn’t that we are performing “real-time analytics”, but rather applying analytics in real time — taking action on insights quickly, which precludes a certain responsiveness, or integration. Businesses know that this is a competitive advantage.</p><p>The varying platforms also need to encompass our perspectives, not just our data. The only way to do that within these systems is to integrate them. This form of system integration is focused on data, with insights built-in, making the most of all of our data, across all of our contexts and perspectives. Platform developers are taking note of these demands and are making their systems more “open”.</p><p>The resultant ease of programmatic integration has created the welcome byproduct of reducing the demand and burden on legacy ETL systems and teams. This has a further knock-on as well; reduced overall load and dependency on the monolithic Data Warehouse, while enabling a broader audience to take action. Work smarter, not harder so they say.</p><h3>That’s nice, but HOW?</h3><p>The simplest, most obvious solutions tend to be the ones that stick. Data activation is typically implemented using APIs. Application Programming Interfaces have been around for a very long time (in computer terms), and their varying protocols and scope have been the topic of debate for just as long. REST APIs, however, have proven to be the preferred standard to system integration in our Internet-enabled world.</p><p>REST APIs offer a consistent, yet flexible way to interact with applications securely. As such, every application that makes use of the Internet or browser will likely have already embraced APIs to some extent. Facebook, Google, and even banks are making more and more data activities available to programmers and continue to evolve. Some even offer “bulk” interfaces in addition to an already verbose library.</p><p>Composition of APIs is not a panacea any more than a Data Warehouse is, but it does open the door to a much more connected and insights-driven future. This interface standardization ultimately takes much of the complexity and time out of integrating systems. Because the systems are allowed to retain their business context (not repeating in ETL or warehouse), the context is allowed to change with predictable consequence, which creates more relevance and capability of the whole enterprise.</p><h3>The Future is Nigh</h3><p>So here we are, in the future and all of our data is integrated. Our time is spent contemplating strategy, analyzing insights and building value as the result. Maybe Reverse ETL isn’t becoming extinct yet. It’s possibly part of a growing expectation that data does need to be shared and that there shouldn’t be friction between systems. Data Activation tools are on the rise, but what we should be considering is Data Operations.</p><p>Not only are we going to need to compose and orchestrate data integrations, but we also need to maintain the rest of our data ecosystem (data operations and analytics) at the same time. We are demanding a lot from our data, and only getting started. A platform that is flexible enough to integrate any systems, combined with the ability to orchestrate and monitor data beyond just a data warehouse — hybrid-cloud included — is now imperative for companies that want to crush the competition.</p><p><strong>Article author</strong>:<strong> </strong><a href="https://www.linkedin.com/in/asegesman/"><strong>Aaron Segesman</strong></a>, Senior Partner Solutions Architect, Rivery</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=59af28b1a931" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Reducing the Pain of Employee Attrition]]></title>
            <link>https://medium.com/@rivery.io/reducing-the-pain-of-employee-attrition-fb29e22b7f0d?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/fb29e22b7f0d</guid>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-engineer]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Thu, 01 Dec 2022 19:51:01 GMT</pubDate>
            <atom:updated>2022-12-01T19:56:38.123Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Reducing the pain of employee attrition" src="https://cdn-images-1.medium.com/max/625/1*_b-MhrJ89lj5oRyLq9zsLQ.jpeg" /></figure><h3>“So I guess you’ll be leaving me now …”</h3><p>If you are of a certain age then you will recognize the lyrics of the famous Level 42 song, “Leaving Me Now” from 1985. For reference, here’s the<a href="https://www.youtube.com/watch?v=hqRlRsAIjm0"> song</a> for those lucky not to be old enough. The lyrics are about a romantic relationship that comes to an end.</p><p>In times of economic uncertainty and a whirlwind of budget cuts, bootstrapped companies with smaller data teams are hanging by the thread. With more companies investing in cloud technology and migrating databases to the cloud, designing the right data infrastructure becomes paramount.</p><p>The pressure to modernize legacy data stacks and adopt cloud-based analytics in order to remain competitive is immense. As business needs and use cases continually grow in their complexity — legacy data stacks and the old way of doing things just doesn’t cut it anymore.</p><p>To survive and thrive in the present and future, smaller data teams need to reevaluate their data pipelines and workflows to scale efficiently and keep up. Unlike Enterprises, who have the luxury to train, experiment in learning, and specialize in one domain — smaller data teams must be agile and have all hands on deck. They need to ramp up quickly with different tooling, take on multiple roles, and get creative in order to build out solid data processes and frameworks that yield quick results.</p><p>The struggle for data engineers is real! <a href="https://f.hubspotusercontent10.net/hubfs/2866765/Whitepapers/White%20Paper%20-%202021%20Data%20Engineering%20Survey.pdf">According to this survey</a>: <strong>97% of data engineers experience burnt out </strong>due to manual repetitive tasks, poor workflows and incoherent ad-hoc project requests.</p><p>It’s no surprise that data engineers are quietly quitting in search of a more sustainable work-life balance. So don’t be surprised if one random Tuesday afternoon, your best data engineer blind sides you with a resignation. After absorbing the initial shock, you start putting a job description together for your next hire. But with the shortage of data engineers in the market chances are slim the role will be filled in anytime soon. It’s only then, during that moment of realization that it hits you — there’s no replacement or match to what’s going to be“Leaving you now”.</p><p>Unfortunately, there’s no way around it. The reality is many data teams have too many moving parts and applications for:</p><ul><li><a href="https://rivery.io/blog/etl-vs-elt/"><strong>Extract and Load</strong></a></li><li><strong>Orchestration</strong></li><li><strong>Transformation</strong></li><li><strong>Cloud Functions for Custom APIs</strong></li><li><strong>Cloud Function for complex transformations</strong></li></ul><p>As the number of applications increases so does the cost of the engineers needed to create, organize and maintain the stack. It’s no simple task and for those smaller data teams losing a vital cog in the team leads to major disruption in planned work and also extra time needed to solve inevitable problems.</p><p>Hiring great data engineers is not a trivial or inexpensive task. Even when they are hired, there will be an inevitable ramp time. Learning one tool is difficult enough but the five needed to complete the Modern Data Stack is verging on painful.</p><p>So it’s time for a rethink. It’s time to heal those broken wounds and face the facts. Data engineers are in short supply and they’re quitting at a faster pace than ever. After dealing with plenty of emotional breakups ourselves, we decided to build an in-house solution to accommodate the inevitable heartache.</p><p>Rivery was created from the ground up as an easy to use application which included all the pieces of the Modern Data Stack wrapped up in one license, one support contract and most importantly one learning curve. Although we cannot stop your data engineers from leaving, we can provide you with the ice-cream, chocolates and comfort blanket to assure life will go on. We’ll handle the complex stuff to ensure your data workflows will continue to flow and scale with whichever project you’re currently running.</p><p>Article author:<strong> Elesh Mistry</strong>, <a href="https://uk.linkedin.com/in/eleshmistry">Senior Solution Architect </a>, Rivery</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fb29e22b7f0d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Change Data Capture (CDC)]]></title>
            <link>https://medium.com/rivery-blog/change-data-capture-cdc-64389841c18b?source=rss-aa3d201b51f3------2</link>
            <guid isPermaLink="false">https://medium.com/p/64389841c18b</guid>
            <category><![CDATA[data-processing]]></category>
            <category><![CDATA[batch-processing]]></category>
            <category><![CDATA[golang]]></category>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[change-data-capture]]></category>
            <dc:creator><![CDATA[Rivery]]></dc:creator>
            <pubDate>Tue, 16 Aug 2022 17:26:44 GMT</pubDate>
            <atom:updated>2022-08-16T17:39:16.492Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Rivery Change Data Capture: Lessons learned from building a CDC solution from scratch" src="https://cdn-images-1.medium.com/max/1024/1*OgxN3GPbp627slqUuVahUA.png" /></figure><h4>Lessons learned building a solution from scratch</h4><h4>By Alon Reznik, Co-Founder &amp; Chief Architect at <a href="https://rivery.io/">Rivery</a></h4><h3>Once upon a time, in a data pipeline not so far away</h3><p>At Rivery, we’re all about data, specifically managing the data pipeline lifecycle.</p><p>We move data from point A to point B, but we also focus on moving updates and changes from point A to point B in the most efficient and fastest way possible.</p><p>To optimize this process, we chose Change Data Capture (CDC) as the best solution to help our customers take care of these updates and changes.</p><p>Initially, we selected a well-regarded, open-source CDC solution, but it ended up not working out. There were a number of hurdles that we had to overcome, which ultimately made this option a non-starter.</p><p>If you’re looking to deploy a CDC platform, then it’s worthwhile to read through some of the challenges we faced and learn from our experience, so you can be more confident in the solution you choose to support your specific needs.</p><p>First, let’s take a quick look at why CDC is so important. Then, we’ll dive into why the platform we chose wasn’t optimal for our needs, and what we did to make CDC work for us and our customers.</p><h3>Why CDC in the first place?</h3><h4>The need for replication speed</h4><p>As data engineers and analysts, we need to move data from our relational databases (point A), such as SQL Server or MySQL, to data warehouses, data lakes, or other target databases (point B).</p><p>Whenever there’s a change or update in the database, we also need to sync between the two in as close to real time as possible, and with as little friction and complexity as possible.</p><p>Traditionally, doing this has been achieved through batch data replication; executing once or several times a day. But batch data pulling requires additional computing, provides insufficient inputs on the history of deleted rows, and entails higher latencies.</p><p>When it comes to data replication, data engineers and analysts need a different approach.</p><h4>In comes a different approach</h4><p>To reduce overhead, eliminate latency, and enable real-time analytics, organizations have moved away from batch or bulk load updating to incremental updating with change data capture.</p><p>Basically, CDC speeds up data processing by eliminating the need for full-scale database replication in the ETL/ELT pipeline, and creating an analytics database as a separate copy of the production database.</p><p>This process entails identifying and capturing any data change (e.g. insert, updates, deletes) to the database logs in real time at point A, using the database engine’s native API, and then delivering changes to point B.</p><p>Because it only deals with changes to the logs, it eliminates the need for ongoing database replication using the database engine, thereby minimizing the resources required for ETL/ELT processes.</p><p>And since it deals with new database events as they occur, it enables real-time or near-real-time data movement.</p><p>As such, CDC is ideal for near-real-time business intelligence as well as for cloud migrations.</p><h4>The Debezium path to CDC</h4><p>At first, we decided to go with Debezium. Among the different CDC tools out there, with 7K GitHub stars and 1.8K GitHub forks, Debezium is one of the most popular.</p><p>This is an open-source distributed platform that’s built on top of Apache Kafka, where you can:</p><blockquote>“Start it up, point it at your databases, and your apps can start responding to all of the inserts, updates, and deletes that other apps commit to your databases.” (<a href="https://debezium.io/">Debezium</a>)</blockquote><p>The advantages of Debezium are said to include:</p><ul><li>Durability and speed</li><li>Scalability in handling large volumes of data</li><li>Incubating connectors for MySQL, SQL Server, Postgres, and Oracle</li><li>Community sink connectors for ElasticSearch</li><li>Sink connectors for MySQL and SQL Server</li><li>MongoDB connector maturity</li><li>Fault tolerance</li></ul><p>And, of-course, the fact that it’s open-source was an additional advantage for us. So, we went with Debezium.</p><h4>Our short affair with Debezium</h4><p>We were feeling good. Optimistic. Debezium is known for being great and we want nothing less than the best for our customers.</p><p>We implemented Debezium, which we were planning to manage on the backend as our customers were to enjoy its benefits when CDC was required by processes executed by our platform on the front end.</p><p>But, sadly, Debezium just didn’t work for our needs. Some of the main issues we faced include:</p><p><strong>Running after open source version updates</strong></p><p>Embedding a core solution which is open source into your own product is challenging. You need to run over any new version, track the changes, and make sure your product is stable in light of all these changes, all while reviewing other prioritizations, fixing velocity, and implementing security patches. So, while Debezium is a well-regarded solution for some cases, for us, the overhead from version upgrades and the impact on our customers, made the effort greater than expected.</p><p><strong>Error messages</strong></p><p>With Debezium, as with any solution that’s built on Kafka Connect, error messages provide the full Java stack, without the option to search for the root cause of the underlying message. This lack of visibility made remediation very difficult, and sharing with our customers what the real error was behind the message, was quite challenging.</p><p><strong>Scale</strong></p><p>On Debezium and Kafka Connect, all pipelines run on the same connectors. The result is a round robin among the pipelines that makes scale complex and often out of reach as the number of sources grows.</p><p><strong>Code transparency</strong></p><p>Debezium uses Kafka Connect to move data into Kafka. The issue with Kafka Connect is that it doesn’t come with the required level of code transparency, which also complicates the maintenance of CDC processes for hundreds of different types of configurations that our customers have executed in their own databases.</p><p><strong>Logging</strong></p><p>Debezium logs aren’t aligned with our formatting, nor did they provide the essential information that we needed. To illustrate, every row in the error stack is provided as one row in the logs. This makes tracking very challenging. Some logs are even hidden in certain use cases, while in other cases, there’s over-logging.</p><p><strong>Topologies support</strong></p><p>While Debezium does offer support for certain topologies, it doesn’t support the hundreds of different types of topologies that are used by our customers.</p><p><strong>SSH tunnels</strong></p><p>Whenever a customer organization was using SSH tunnels for connectivity, Debezium couldn’t support it.</p><p>We needed (and ultimately created) a solution that would connect to our client’s databases using different topologies, such as SSH tunnel, SSL, and VPN tunnels. SSH tunnels are commonly used to connect remote databases in internal VPCs and unfortunately, this isn’t embedded out of the box in Debezium.</p><p><strong>Use cases</strong></p><p>The biggest issue we experienced is that Debezium use cases couldn’t be tailored to our customers’ needs. For example, we received regular feedback that the open platform simply wasn’t working with specific data types and tables from our customers.</p><h4>What should you evaluate when considering a CDC platform?</h4><ul><li><strong>Frequent version updates:</strong> will you be able to handle regular merges and heavy maintenance?</li><li><strong>Error messages: </strong>does the platform deliver clarity for quick and efficient remediation?</li><li><strong>Scale:</strong> does the platform work with all of your database types?</li><li><strong>Code transparency:</strong> can you easily get into the code to make changes when needed?</li><li><strong>Logging:</strong> do the log files provide the information you need?</li><li><strong>Topologies support:</strong> does it support your topology?</li><li><strong>SSH tunnels:</strong> does it support SSH connectivity and other connectivity best practices?</li><li><strong>Use cases:</strong> can it handle all your use cases, tables, etc.?</li></ul><p>Unfortunately, the answer to all of the above was <em>‘no’</em> for us. As a result, we decided to take things into our own hands and develop our own CDC solution.</p><h3>A new approach to CDC</h3><p>Our goal was to create another solution, our own platform, which we could intuitively know how to maintain and debug. When the code is yours, you have access to it, you have visibility, and you know how to handle any issue that might arise.</p><p>But building your own CDC platform is no simple task. It requires a lot of knowledge about so many different possible specifications that need to be applied to just about every database.</p><p>It’s not just about knowing your own data, database, tables, and topology. It’s about knowing everything about them, and how to get data from point A to point B seamlessly, quickly, and efficiently.</p><p>But for us, and for our customers, the advantages of creating our own CDC platform far outweighed the challenges.</p><p><strong>So, what did we do?</strong></p><p>We mapped out all the issues we were experiencing, aggregated insights, assembled our best and brightest for the task, and built our own CDC platform as follows.</p><p>For the programming language, we evaluated and considered Python and Go.</p><p>In our consideration, we quickly realized that Python doesn’t support multithreading well enough, nor does it offer a robust solution for Kafka. Since multithreading is so important for processing many events at the same time (a pillar of CDC), and since our solution is on Kafka, we chose not to go with Python as our programming language.</p><p>This led us to choose Go, which offers many advantages:</p><ul><li>Multithreading out of the box (using <a href="https://go.dev/tour/concurrency/1">Goroutines</a>).</li><li>Task queues using Go channels for <a href="https://go.dev/tour/concurrency/2">concurrency</a> out of the box.</li><li>Great support for I/O.</li><li>Stability</li><li>Flexibility</li><li>Very Light.</li></ul><h4>The architecture</h4><p>We built an architecture that drives high rates of CDC efficiency. For example, the consumer pushes data log entries to queues and everything is orchestrated by the manager, which also performs validation for MySQL and creates a status report if something doesn’t fit or connect.</p><p>This also drives great flexibility, enabling a new connector to be deployed in less than two weeks, as opposed to the three weeks that are typically required.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttGNuwGRGXZdx66MYdn2UQ.png" /></figure><h4>SSH connectivity and other topologies</h4><p>To support our customers’ connectivity needs, we created our own SSH tunnel as an external service that we embedded as a side car.</p><p>This new solution provides us with the ability to align our clients’ connecting topologies and offers better, higher performance, and more stable solutions for their specific needs.</p><h4>Centralized management</h4><p>The consumer in this architecture can push data to Kafka. It’s a system that can get messages at a very high capacity; understanding how to bring a lot of different data types quickly from a lot of different sources, all with the ease-of-use and clarity of central management.</p><h4>A transactional process against Kafka</h4><p>When it comes to pushing data to Kafka, the execution needs to be transactional through the Kafka API. So, we needed to create a transactional process that would collect all the data that we needed to push, ensuring the process would also let the user know where this collection started and where <em>exactly</em> it would be pushed.</p><p>This was the hard part, but it’s exactly what we did!</p><p>Having such visibility into the process helps you to avoid delays. When events interrupt the consuming, it’s hard to know where you started, where you stopped, and where you need to pick up again. Ultimately, it can cause a lot of problematic delays.</p><p>Going back and forth enables us to manage any change in our customers’ databases and make sure we’re catching all of the messages in a quick and easy way.</p><h4>The flip/flop functionality</h4><p>Knowing where you started and where you left off is also key to the flip/flop functionality that we designed and implemented.</p><p>We created two queues instead of one, so that we could make sure that messages are accepted by Kafka, while at the same time, we could fetch more data from the source without causing any of the processes to be blocked.</p><p>Whenever one queue gets filled up, the data coming in goes into the second empty queue instead of creating a backlog. Only once the full queue has emptied out, would we get back to using it for new data coming in.</p><p>This flip/flop functionality is only possible when you know where you started, where you are, and where you left off, so that you only fetch the data you need from the database without any unnecessary replications.</p><p>The benefit of this approach is not only about avoiding delays. Although this was a fantastic benefit as we reduced delays down to about 10 seconds vs. the 2–3 minutes that we had with Debezium when we opened it for scale.</p><p>Additional benefits include:</p><ul><li>Eliminating the need for redundancy</li><li>Greater stability</li><li>Scalability out of the box</li></ul><p>And the biggest benefit of all was that we could finally focus on the business logic and address our customers’ needs, instead of expending tons of energy on database replication.</p><p>No overhead. Just value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*28wENUO-uQvLOpEiKZQCWQ.png" /></figure><h3>Closing thoughts on Change Data Capture and choosing the best CDC platform for your data needs</h3><p>If you need CDC to help streamline, accelerate, and drive efficiency in synchronizing your data, you could go with a popular platform, but it may not be able to meet all of your needs out of the box.</p><p>You could design your own, but keep overhead front of mind. Would you really want to take all that on? Especially when we’ve already done the heavy lifting for you? 🏋️‍♂️</p><p>With Rivery’s CDC solution, you get some of the toughest challenges pre-solved for you:</p><ul><li><strong>A language that’s very light</strong> and supports multithreading out of the box with compatibility for multiple channels, I/O support, and stability</li><li><strong>A flexible architecture</strong> that drives higher rates of efficiency</li><li><strong>Built-in SSH connectivity</strong></li><li><strong>A better transactional process</strong> that offers more visibility and minimizes delays</li><li><strong>Flip/flop functionality</strong> <strong>with two queues</strong>, which eliminates the need for redundancy, provides greater stability, and delivers scalability out of the box</li><li><strong>Frees up valuable time and resources</strong> to focus on business logic instead</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64389841c18b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/rivery-blog/change-data-capture-cdc-64389841c18b">Change Data Capture (CDC)</a> was originally published in <a href="https://medium.com/rivery-blog">rivery-blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>