<?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 Ripu Jain on Medium]]></title>
        <description><![CDATA[Stories by Ripu Jain on Medium]]></description>
        <link>https://medium.com/@ripu.jain?source=rss-547ec9a9374c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*DQyr1xupH03htVoO</url>
            <title>Stories by Ripu Jain on Medium</title>
            <link>https://medium.com/@ripu.jain?source=rss-547ec9a9374c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 03:35:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ripu.jain/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[Lean Analytics with Snowflake Cortex and dbt Cloud — the OpenSpace.ai way]]></title>
            <link>https://medium.com/snowflake/lean-analytics-with-snowflake-cortex-and-dbt-cloud-the-openspace-ai-way-1360fad0e763?source=rss-547ec9a9374c------2</link>
            <guid isPermaLink="false">https://medium.com/p/1360fad0e763</guid>
            <category><![CDATA[mistral]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[dbt]]></category>
            <dc:creator><![CDATA[Ripu Jain]]></dc:creator>
            <pubDate>Wed, 27 Mar 2024 17:46:42 GMT</pubDate>
            <atom:updated>2024-03-27T17:46:42.494Z</atom:updated>
            <content:encoded><![CDATA[<h3>Lean Analytics with Snowflake Cortex and dbt Cloud — the OpenSpace.ai way</h3><p><em>Authors: Ripu Jain (Snowflake), Jonathan Paquette (OpenSpace.ai)</em></p><p>Thank you for stopping by to read this, but let us save you some time and tell you what this blog is NOT about:</p><ul><li>It’s not a write up on how to solve Netflix scale data engineering and analytics problems</li><li>It’s not a best practices document or a write up on data architecture patterns</li></ul><p>Instead, we hope that this blog conveys that:</p><ul><li>It’s possible for a lean data team of 3 to serve the analytical needs of a ~ 200 employee, Series D, ~ $1B valuation company, without the need for expensive data architects or consultants</li><li><strong>Working with the right tech stack, even if perceived expensive, can bring joy in work and empower you to focus on business impacting analyses and decision making</strong>, rather than working and spending time maintaining your own infrastructure.</li><li>Keeping it simple should be the goal; introducing new tech or features to optimize for automation or observability capabilities, or some other single-dimensional functionality — be cautious about them if you are striving to remain a lean team.</li></ul><h3>Great! So what is OpenSpace.ai? And what problems is the data team addressing?</h3><p>OpenSpace.ai is <a href="https://www.youtube.com/watch?v=85dK9v9JCIY">Reality Capture software</a>. It’s most similar to Google Maps Street View — the main difference is that OpenSpace.ai works indoors, and you control where the 360 Imagery happens.</p><p>I know what you must be thinking — OpenSpace must generate insane amounts of spatial and geometric data! And while that is true, and OpenSpace does have phenomenal AI and ENG teams working on that, <strong>the data team focuses primarily on solving the needs of our business units</strong>, which involves:</p><ul><li>Generating standardized and up-to-date facts and metrics used by entire company — from execs to field level</li><li>Analyzing product usage data and determining ways to improve. What features are popular? What are common usage patterns? Who are outliers?</li><li>Deliver sales and marketing analyses, like pipelines and efficiency.</li><li>Quantify sales enablement — propensity to expand, to churn, gross margin etc.</li></ul><p>So, what are the <strong>primary data sources</strong> to address the above needs:</p><ul><li>PostgreSQL production database, hosted on AWS, generating about 15 million rows per month</li><li>Salesforce for CRM</li><li>Marketo for Marketing</li><li>Amplitude for engagement tracking — clickstream, engagement metrics, user segmentation</li><li>Jira for internal issue tracking</li><li>SAGE INTACCT (financial data source)</li></ul><p>For context, it’s good to mention the <strong>constraints the data team works under</strong>:</p><ul><li>The data team comprises of 3 individulas, with not-too-heavy background in core data engineering, or cloud computing, or big data technologies</li><li>The team wants to spend minimal time maintaining and integrating and upgrading data-stack. We want the tech to just work and disappear behind the scene, and be an enabler and a decision-making accelerator for our company</li></ul><p>At a high level, this is what openspace data landscape looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*K7OX5hF6jUaJDN2H" /></figure><p>With this context in mind…</p><h3>Enter Snowflake — the analytics accelerator</h3><p>Stealing a <a href="https://omni.co/blog/what-makes-snowflake-successful#:~:text=Recently%2C%20I%20was%20asked%20what%20makes%20Snowflake%20so%20successful%20as%20a%20business%2C%20and%20my%20answer%20is%20a%20pretty%20simple%20one%20%2D%20they%20make%20life%20easy%20for%20data%20people.">quote from Collin, the CEO of Omni</a> — [Snowflake makes] life easy for data people.</p><p>Snowflake’s ease of setup, use and administration got us addressing our business needs and goals right away — all we needed was our data to work with. Snowflake became our organization’s trusted, governed, and secure source of truth.</p><p>Here are some of the specific Snowflake features have tremendously helped our team:</p><ol><li><a href="https://docs.snowflake.com/en/sql-reference/sql/select#:~:text=EXCLUDE%20col_name%20EXCLUDE%20(col_name%2C%20col_name%2C%20...)"><strong>SELECT * EXCLUDE</strong></a><strong><br></strong>This one is small but mighty, and it shows up in almost every single query and dbt model that we write. We have embraced denormalization, which means that we generally include all columns from upstream models in their downstream dependencies. This has massively improved our ability to generate analytics fast, and it would not be possible with SELECT * EXCLUDE.</li><li><a href="https://docs.snowflake.com/en/user-guide/queries-cte#recursive-ctes-and-hierarchical-data"><strong>Recursive CTE</strong></a>:<br>As a B2B business, our customers come in all shapes and sizes. Some of them can be defined by one record in a table, and others have complex hierarchical structures. The traditional SQL for unraveling complex hierarchies is notoriously complicated, but Snowflake lets us do it with a single recursive CTE.<br>Here’s how we use it:</li></ol><pre>with<br>accounts as (<br>   select<br>       account_id,<br>       account_parent_id<br>   from {{ ref(&quot;stg_salesforce__account&quot;) }}<br>),<br><br>accounts_cte as (<br>   select<br>       account_id,<br>       account_parent_id,<br>       account_id as root_account_id,<br>       1               as level<br>   from accounts<br>   where account_parent_id is null<br>   union all<br>   select<br>       a.account_id,<br>       a.account_parent_id,<br>       cte.root_account_id as root_account_id,<br>       level + 1                as level<br>   from accounts as a<br>   inner join accounts_cte as cte on a.account_parent_id = cte.account_id<br>),<br><br>final as (<br>   select<br>       account_id              as sfdc_account_id,<br>       account_parent_id       as sfdc_parent_account_id,<br>       root_account_id         as sfdc_root_account_id,<br>       max(level) over (partition by root_account_id) - level + 1 as sfdc_account_hierarchy_level,<br>       max(level) over (partition by root_account_id)             as sfdc_root_account_hierarchy_level<br>   from accounts_cte<br>)<br><br>select * from final</pre><p>And here’s the output. You can see how we can immediately determine the parent and root accounts for any child account — super helpful!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*srgydy1ckA97VbuX" /></figure><p>3. <a href="https://docs.snowflake.com/en/user-guide/json-basics-tutorial-flatten"><strong>Lateral flatten</strong></a>:<br>JSON and arrays are great for some things, but they aren’t ideal for analytics. Before Snowflake, the SQL to extract information from them was a bit of a nightmare. Snowflake’s lateral flatten join gave us a simple, scalable way to manage JSON, and has allowed us to scale faster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mzituhVfwfm_v57a" /></figure><p>4. <a href="https://docs.snowflake.com/user-guide/snowflake-cortex/llm-functions">Snowflake <strong>Cortex</strong></a>:<br>Snowflake Cortex, now in Public Preview, gives us instant access to industry-leading large language models (LLMs) like Mistral and llama2.</p><p>We just deployed our first Cortex powered dbt model! (<em>And coincidentally, as of writing this blog, </em><a href="https://docs.getdbt.com/blog/dbt-models-with-snowflake-cortex"><em>dbt Labs just published</em></a><em> their own use case of using Cortex in analytics workflow.)</em></p><p>Our first use case is to generate a summary report of trends in Customer Support tickets. This allows us to keep a finger on the heartbeat of our customers with very little effort. Reading 500 cases is very hard for a human, but very easy for an AI. Reading the AI summary is a great way to quickly understand high level trends of customer complaints.</p><pre>{{ config(materialized=&#39;table&#39;) }}<br><br>with<br>support_cases as (select * from {{ ref(&#39;sfdc_case_lookup&#39;) }}),<br><br>base as (<br>select<br>    ROW_NUMBER() OVER ( ORDER BY SFDC_CASE_CLOSED_DATE DESC ) as row_number,<br>    truncate(row_number, -2)::varchar as bin,<br>    COALESCE(SFDC_CASE_ORIGIN, &#39;NULL&#39;)                      as CASE_ORIGIN,<br>    COALESCE(SFDC_CASE_SUBJECT, &#39;NULL&#39;)                     as CASE_SUBJECT,<br>    COALESCE(SFDC_CASE_SUPPORT_COMPONENT, &#39;NULL&#39;)           as CASE_SUPPORT_COMPONENT,<br>    COALESCE(LEFT(SFDC_CASE_DESCRIPTION, 3000), &#39;NULL&#39;)     as TRUNCATED_CASE_DESCRIPTION,<br>    COALESCE(LEFT(SFDC_CASE_CLOSURE_COMMENTS, 500), &#39;NULL&#39;) as TRUNCATED_CASE_CLOSURE_COMMENTS,<br>    CONCAT(<br>        &#39;Read the following customer support case and generate a clear, concise summary in 50 words that highlights the main issue and resolution (if applicable). &#39;,<br>        &#39;&lt;case_origin&gt;&#39;, CASE_ORIGIN, &#39;&lt;/case_origin&gt;&#39;,<br>        &#39;&lt;case_subject&gt;&#39;, CASE_SUBJECT, &#39;&lt;/case_subject&gt;&#39;,<br>        &#39;&lt;case_support_component&gt;&#39;, CASE_SUPPORT_COMPONENT, &#39;&lt;/case_support_component&gt;&#39;,<br>        &#39;&lt;truncated_case_description&gt;&#39;, TRUNCATED_CASE_DESCRIPTION, &#39;&lt;/truncated_case_description&gt;&#39;,<br>        &#39;&lt;case_closure_comments&gt;&#39;, TRUNCATED_CASE_CLOSURE_COMMENTS, &#39;&lt;/case_closure_comments&gt;&#39;<br>    )                                                       as CORTEX_PROMPT,<br>    SNOWFLAKE.CORTEX.COMPLETE(<br>        &#39;mixtral-8x7b&#39;,<br>        cortex_prompt<br>    ) as cortex_summary<br>from support_cases<br>where SFDC_CASE_CLOSED_DATE is not null<br>and CASE_SUPPORT_COMPONENT not in (&#39;Spam/Other Dept&#39;)<br>order by SFDC_CASE_CLOSED_DATE desc<br>limit 499<br>),<br><br>array_of_case_summaries as (<br>select<br>    bin,<br>    array_agg(cortex_summary) as array_of_case_summaries<br>from base<br>group by 1<br>),<br><br>summary_of_summaries as (<br>select<br>    bin,<br>    SNOWFLAKE.CORTEX.COMPLETE(<br>        &#39;mixtral-8x7b&#39;,<br>        concat(&#39;Below are summaries of 100 customer support cases. Please read and provide a comprehensive summary that captures the prevalent issues and notable patterns observed in these cases. &#39;,<br>        &#39;&lt;array_of_case_summaries&gt;&#39;, array_of_case_summaries::varchar, &#39;&lt;/array_of_case_summaries&gt;&#39;)<br>    ) as summary_of_summaries<br>from array_of_case_summaries<br>),<br><br>array_of_summary_of_summaries as (<br>select<br>    array_agg(summary_of_summaries) as array_of_summary_of_summaries<br>from summary_of_summaries<br>),<br><br>summary_of_summary_of_summaries as (<br>select<br>    SNOWFLAKE.CORTEX.COMPLETE(<br>        &#39;mixtral-8x7b&#39;,<br>        concat(&#39;Here is a collection of summaries that represent overarching themes and patterns from 500 customer support cases. Based on these, generate a final, detailed summary that distills the core insights and prevalent customer issues. &#39;,<br>        &#39;&lt;array_of_summary_of_summaries&gt;&#39;, array_of_summary_of_summaries::varchar, &#39;&lt;/array_of_summary_of_summaries&gt;&#39;)<br>    ) as summary_of_summary_of_summaries<br>from array_of_summary_of_summaries<br>)<br><br>select<br>    base.*,<br>    array_of_case_summaries.* exclude bin,<br>    summary_of_summaries.* exclude bin,<br>    array_of_summary_of_summaries.*,<br>    summary_of_summary_of_summaries.*<br>from base<br>left join array_of_case_summaries on base.bin = array_of_case_summaries.bin<br>left join summary_of_summaries on base.bin = summary_of_summaries.bin<br>left join array_of_summary_of_summaries<br>left join summary_of_summary_of_summaries</pre><p>Report Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Xqyr24FV_WbKdGX5" /></figure><h3>dbt Cloud — the scale multiplier</h3><p>As mentioned in the beginning of the post, our lean team did not have extensive experience with traditional software development practices. We were used to writing SQL in a browser tab and Python in a Jupyter notebook (usually a Jupyter notebook that was also in a browser tab).</p><p>dbt Cloud allowed us to get started, and we have continued to use it because of its ease of use and seamless job orchestration. Their <a href="https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview">best practices</a> documentation are amazing guides that got us started the right way. Since we started using dbt last year, our codebase (~ 450 dbt models) has grown to prepare data for every corner of the business. You can see the contribution history in our GitHub here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/969/0*XdbVs7xdIhjU1IQH" /></figure><h3>… in conclusion…</h3><p>We hope this writeup gives you some confidence in our philosophy that the right data stack should disappear in the background, and empower the data team to focus on solving problems that help the business grow. For us, the combination of fivetran, Snowflake, and dbt cloud is that right stack, that’s allowing our team of 3 to serve an entire company’s analytics and decision making needs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1360fad0e763" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/lean-analytics-with-snowflake-cortex-and-dbt-cloud-the-openspace-ai-way-1360fad0e763">Lean Analytics with Snowflake Cortex and dbt Cloud — the OpenSpace.ai way</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Snowflake Cortex ML + Matillion = Low Code ML + ELT = new skill unlocked for data teams]]></title>
            <link>https://medium.com/snowflake/snowflake-cortex-ml-matillion-low-code-ml-elt-new-skill-unlocked-for-data-teams-d46fa16bf983?source=rss-547ec9a9374c------2</link>
            <guid isPermaLink="false">https://medium.com/p/d46fa16bf983</guid>
            <category><![CDATA[anomaly-detection]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[forecasting]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[machine-learning]]></category>
            <dc:creator><![CDATA[Ripu Jain]]></dc:creator>
            <pubDate>Tue, 06 Feb 2024 13:01:33 GMT</pubDate>
            <atom:updated>2024-02-06T16:25:28.321Z</atom:updated>
            <content:encoded><![CDATA[<p>Snowflake’s <a href="https://www.snowflake.com/blog/ml-based-forecast-anomaly-detection-cortex/">Cortex ML</a> capabilities are low-code SQL functions that empower data engineers, who traditionally dont have the depth in statistical or ML building and ops knowledge, to generate ML-based insights out of their data, such as forecasting, anomaly detection, classification and more. Snowflake provides the appropriate ML algorithm for each function, so you don’t have to be a machine learning developer to take advantage of them. All you need is your data.</p><p>So what happens when you combine the power of these engineer friendly SQL functions, with a code-optional data pipeline platform like Matillion Data Productivity Cloud (DPC)? This blog explores it, so read on!</p><h3>But wait… what is the Matillion Data Productivity Cloud?</h3><p>The goal of the platform is to make data teams more productive, utilizing a single application for any Extract, Load, and Transform process. In addition to being a GUI-based ELT Platform, Data Productivity Cloud offers data engineer-friendly features such as:</p><ul><li>Component-based Orchestration, allowing for a visual component of the end-to-end pipeline</li><li>Notification capabilities like <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/send-email/">Send Email</a>, <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/webhook-post/">Webhook Posting</a>, and the posting of messages to <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/sqs-message/">AWS SQS queues</a>.</li><li>High-code capabilities such as the <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/sql-script/">SQL Script</a>, <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/python-script/">Pushdown Python</a>, and <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/commands-for-dbt-core/">Commands for dbt Core</a></li><li>Team collaboration and version control tooling using <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/git-overview/">Git</a></li><li>Integration of AI functionality like Auto-Generated Documentation, Amazon Bedrock Prompt, and OpenAI prompt</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*U_rzxYBBeKvUjBej" /></figure><p>Data Productivity Cloud utilizes pushdown SQL to make this whole process work, which makes it a natural fit for users looking for a platform to incorporate Cortex ML functions. <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/components-overview/">Components</a> are the tools in the Data Productivity Cloud users’ toolkit; with them, users can define a step-by-step process of potentially loading data from a variety of sources, transforming data from a raw state to production-readiness, then creating and calling native Cortex functions to enable Machine Learning capabilities.</p><h3>Cortex ML + Matillion DPC in action</h3><p>The quickest way to get started and see the joint benefits is by following along with the existing <a href="https://quickstarts.snowflake.com/guide/ml_forecasting_ad/index.html?index=..%2F..index#0">quickstart guide about Cortex ML functions</a>. The guide covers a fictional company Tasty Bytes and the build-out of predictive and anomaly detection capabilities on the company’s sales data. This article will follow the quickstart as a team developing a Data Productivity Cloud pipeline.</p><p><a href="https://quickstarts.snowflake.com/guide/ml_forecasting_ad/index.html?index=..%2F..index#1">Section 2</a> of the guide lays out how users are to set up their Snowflake environment. Please complete Section 2 of the guide which will achieve:</p><ul><li>Access to the Tasty Bytes dataset, found by searching for the Cybersyn Government Essentials dataset on Snowflake Marketplace</li><li>Creation of a warehouse (<strong>quickstart_wh</strong>) and database objects (<strong>quickstart.ml_functions.tasty_byte_sales</strong>) which will be used in Matillion.</li></ul><p>Once Section 2 of the Quickstart is complete, proceed to the <strong>Getting Started with Matillion</strong> section.</p><h4>0. Getting Started with Matillion</h4><p>Any journey in Data Productivity Cloud can be started by first <a href="https://docs.matillion.com/data-productivity-cloud/hub/docs/registration/">registering</a> an account from the <a href="https://hub.matillion.com/">Matillion Hub</a>. All that is needed is a valid email address and password to sign up. Upon signing in as the created user, the account will be on a <a href="https://docs.matillion.com/data-productivity-cloud/hub/docs/free-trial/">14-day, 500 credit trial</a>.</p><p>Once registered for the Matillion Hub, <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/designer-overview/">enter the Designer</a> and <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/projects/">create a project</a>. We will name ours <strong>CortexQuickstart.</strong> When setting up the project, an <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/environments/">Environment</a> will also be created which represents the connection between Matillion and Snowflake. Set the following defaults in how Matillion will interact with Snowflake:</p><ul><li><strong>ROLE</strong>: ACCOUNTADMIN</li><li><strong>WAREHOUSE</strong>: QUICKSTART_WH</li><li><strong>DATABASE</strong>: QUICKSTART</li><li><strong>SCHEMA</strong>: ML_FUNCTIONS</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/978/0*yQR-EQhK9_ma6xV9" /></figure><h4>1. Launching Data Productivity Cloud Designer</h4><p>The <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/designer-overview/">Data Productivity Cloud Designer</a> is where design of the end-to-end pipeline will take place. To begin, let’s prepare the git branch that our team will be working against. Data Productivity Cloud embeds <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/git-overview/">git</a> as a core piece of functionality, to allow teams to collaborate and institute version control.</p><p>The <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/branches/">Branches</a> screen will communicate that there is one branch in existence — <strong>main</strong>. Let’s treat this branch as the Production branch, which we will later merge code from a development branch. Speaking of that development branch, create a new one using the <strong>Add new branch</strong> button and naming it <strong>dev</strong>.</p><p>Once created, the <strong>dev</strong> branch should now be present on the <strong>Branches</strong> screen. Click into it to begin designing your pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Wzz-OvHJSspiz-PE" /></figure><p>The first step of Designer is usually the <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/pipelines/#adding-a-pipeline">creation of an Orchestration</a> pipeline. This can be done by clicking the <strong>Add </strong>button in the Project Explorer and selecting <strong>Orchestration Pipeline</strong>. Let’s create an Orchestration pipeline and call it <strong>CortexOrchestration</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/531/0*8tEJJSvmZ0YMuVna" /></figure><h4>2. Preparing datasets via DPC Transformation Pipeline</h4><p>Now that our project in Data Productivity Cloud is set up, we will move on to <a href="https://quickstarts.snowflake.com/guide/ml_forecasting_ad/index.html?index=..%2F..index#2">Step 3 in the Cortex Quickstart Guide</a>. Here, the Data Engineering (DE) team creates views and tables based on data derived from the original Tasty Bytes dataset. As opposed to the hand-writing of SQL in the Quickstart guide, Matillion gives users the option of dragging and dropping components which provides better clarity and maintainability of the pipeline.</p><p>The DE team creates a <a href="https://docs.matillion.com/metl/docs/8347111/#transformation-jobs">Transformation pipeline</a> and drags out as the first step in the Orchestration pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*IUbWX2qzydJ4AvSP" /></figure><p>Within the Transformation pipeline, three different datasets will be created, all minor derivations from the original <strong>Tasty Bytes</strong> dataset. They all include only the latest years’ worth of data and the <strong>date</strong> field also needs a conversion to a specific kind of timestamp.</p><p>The DE team will complete this step of the pipeline with four components:</p><ul><li><strong>Table Input </strong>to read the <strong>TASTY_BYTE_SALES</strong> table</li><li><strong>Filter</strong> to filter the DATE field for only dates greater than May 28, 2022</li><li><strong>Calculator</strong> to make the data type conversion</li><li><strong>Rename</strong> to retitle the DATE column as TIMESTAMP</li></ul><p>The DE team has also made use of the Notes feature, explaining each step of the Transformation pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/904/0*2nWy7dIMMYsR8bDH" /></figure><p>Using the Sampling feature in a Matillion pipeline the DE team is happy with the state of the data and number of rows, and is ready to progress with the Transformation pipeline.</p><p>The DE team creates three views needed for the Forecast and Anomaly Detection tasks:</p><ul><li><strong>lobster_sales</strong> to train the forecasting function; dataset to consist of only Lobster Mac &amp; Cheese items, and only the TIMESTAMP and TOTAL_SOLD fields</li><li><strong>vancouver_anomaly_analysis_set </strong>consisting of only the latest months’ worth of data</li><li><strong>vancouver_anomaly_training_set</strong> consisting of the latest years’ worth of data, without the last month included</li></ul><p>These three views will be created in 3 separate branches and concurrently-running branches. Once complete, the DE team clicks <strong>Run Pipeline</strong> to complete the creation of the 3 new views derived from the TASTY_BYTES_SALES dataset. This materializes 3 new views in the Snowflake database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6yx6vfzjYtiv93Lw" /></figure><h4>3. Create and Execute Cortex ML Forecast function</h4><p>Now that the 3 views have been created via a Matillion Transformation pipeline, now is time for the Data Science (DS) team to get to work. The DS team wishes to:</p><p>1) create a 10-day Forecast which will calculate the next 10 days worth of Lobster Mac &amp; Cheese sales</p><p>2) detect any anomalies in sales for all items sold in the latest month</p><p>To do so, the DS team will leverage Cortex ML in Data Productivity Cloud, due to its native SnowSQL pushdown capabilities. Returning back to the <strong>CortexOrchestration</strong> pipeline, the DS team will set up two SQL components for both flows, and set up the process so it runs concurrently.</p><p>For the Forecast Lobster Mac &amp; Cheese flow, one SQL Script component will be used to create the Forecast function, and another to call the function to predict the next 10 days of sales. Results will be saved to a new table called <strong>macncheese_predictions</strong>. Both components will use the code from <a href="https://quickstarts.snowflake.com/guide/ml_forecasting_ad/index.html?index=..%2F..index#2">Section 3 Step 2 and Step 3</a> of the Quickstart, respectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*X6tCgIj9Vdk4ksD6" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*w0IpH87PkOU_Crqv" /></figure><h4>4. Create and Execute Cortex ML Anomaly Detection function</h4><p>The DS team also wishes to train and detect anomalies on the past years’ worth of all Vancouver sales. They also wish to do so concurrently, as the Mac &amp; Cheese Forecasting functions run. The code used here will be derived from <a href="https://quickstarts.snowflake.com/guide/ml_forecasting_ad/index.html?index=..%2F..index#4">Section 5 of the Quickstart guide</a> to first create the <strong>vancouver_anomaly_model </strong>trained on the first 11 months of data, then to call the trained model to detect anomalies on the latest month of sales data. Any anomalies detected will be saved in the <strong>vancouver_anomalies</strong> table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*g7ktChDERwmxO33B" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*CyurnFUV-sGeeSez" /></figure><p>Upon completion of the setting of the <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/sql-script/">SQL Script</a> components, the <strong>CortexOrchestration </strong>pipeline<strong> </strong>appears as the following. The DS team clicks the <strong>Run Pipeline</strong> button to initiate the pipeline to execute recreation of the views as well as the Cortex ML processes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*u21MKsmLuGMHGTN8" /></figure><h4>5. Trigger Email Notifications</h4><p>While the DS team is confident in their pipelines, they do not want to assume everything will run perfectly, especially as new data is introduced to further train the Cortex functions. As the pipelines run on a regular basis, the team wishes to set up a process by which emails will be sent in <em>either</em> the event of success or failure. Data Productivity Cloud contains robust notification methods including the <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/send-email/">Send Email<strong> </strong>component</a> which can accomplish this task.</p><p>But how to create the logic of either: a) Send Success email detailing the most frequent anomaly, when both the Mac &amp; Cheese Forecast and Anomaly Detection succeed, or b) Send Failure email if either process were to fail?</p><p>Luckily for the team, Data Productivity Cloud has <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/and/">And</a> and <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/or/">Or</a> components to be used to make this very evaluation. Using these components, the DS team has successfully created a process by which a Success or Failure email will be dynamically sent based on the performance of the job.</p><p>As a final cherry on top, the DS team also wants the success email to communicate the most frequent anomaly. The <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/query-result-to-scalar/">Query Result to Scalar component</a> can be used to query the <strong>vancouver_anomalies</strong> table with the following statement, which will map the result to a Matillion variable included in the Success email:</p><p><em>WITH TOP_ANOMALY AS (</em></p><p><em>SELECT series, is_anomaly, count(is_anomaly) AS num_records</em></p><p><em>FROM vancouver_anomalies</em></p><p><em>WHERE is_anomaly =1</em></p><p><em>GROUP BY ALL</em></p><p><em>ORDER BY num_records DESC</em></p><p><em>LIMIT 1)</em></p><p><em>SELECT CONCAT(SERIES, ‘ : ‘, NUM_RECORDS) AS TOP_ANOMALY_COUNT</em></p><p><em>FROM TOP_ANOMALY</em></p><p>The resulting Matillion pipeline looks like the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Ia_iUea6_5qWuAhl" /></figure><p>Upon running, our team has received the following email which dictates that the process has run successfully, and that Hot Ham &amp; Cheese represents the food item with the most anomalies at 18.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/884/0*ZMWmVxIVBd-jm1Eu" /></figure><p>Success! The DE and DS team’s collaboration has led to an end-to-end process of sourcing and building training datasets, to then build and call native Cortex functionality. And as the process will run on a regular basis, the team has also built notification methods to communicate success or failure. There are still a couple more steps the team can take to promote their pipelines to production and run on a schedule.</p><h4>6. Productionizing the pipeline</h4><p>The first step of the <strong>1. Launching the Data Productivity Cloud Designer</strong> section involved the creation of a new branch that the Data Engineering and Data Science teams were working against. But how can the team: 1) promote this code to a production branch, and 2) ensure that it automatically runs on a somewhat frequent basis?</p><p>First step will be to first Commit the changes on the <strong>dev</strong> branch, then push to remote. Within the Designer UI, the user has Git Action buttons at their disposal; the group will <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/git-commit/">commit</a> the changes to the branch, then <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/git-push/">push local changes</a> to make the latest update of the pipeline available to other branches.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GdcdP-lYjqE3cfqW" /></figure><p>Next, the team will want to promote the code to the <strong>main</strong> branch, which serves as the production environment. Returning back to the <strong>Branches</strong> screen, the team clicks into their Production branch — <strong>main</strong> — and <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/git-merge/">merges</a> from the <strong>dev</strong> branch. The changes should immediately take place and the team can now find the pipelines in the Project Explorer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*4Hzv7wse_9dmwjYW" /></figure><p>Finally, the team will place the main branch on a <a href="http://v/">schedule</a> to run at an automated frequency set by the team. Any future runs will be initiated at a frequency directly set up by the team, and the team should now expect an email to be sent at the conclusion of any run.</p><h3>So, to recap…</h3><p>Here are some key takeaways from this article:</p><ul><li>Cortex ML functions are a powerful, fully managed service that enables you to quickly generate ML-based insights — all you need is your data</li><li>When used in tandem with a GUI-based ELT product Data Productivity Cloud, these functions allow users to build end to end, orchestrated, data transformation + ML pipelines, in a visual and low-code manner</li><li>Data Productivity Cloud’s developer centric capabilities involving Git-flows, segregation of environments, and easy CI/CD enhances the productivity of data teams</li><li>Data Productivity Cloud allows for the build-out of pipeline logic (<a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/and/">And</a>, <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/or/">Or</a> components) and notifications (<a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/send-email/">Send Email</a>), in addition to ELT actions.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/225/0*GjpVcbUX4G3Cs8-4" /></figure><h3>How can I replicate this job to my Data Productivity Cloud environment?</h3><ol><li>Fork this <a href="https://github.com/MatillionPartnerEngineering/MatillionDPC_SnowflakeCortexQuickstart">public Github repository</a> containing the end-to-end pipeline</li><li>Set up a Data Productivity Cloud Project with Advanced settings</li><li>Connect Data Productivity Cloud to the forked repository in the Project setup menu. <a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/connect-github-repository-prerequisites/">This article</a> details any prerequisites needed to be completed for the process.</li><li><a href="https://docs.matillion.com/data-productivity-cloud/designer/docs/troubleshooting-github-repository-connection/">Here is a FAQ</a> for the connection between Data Productivity Cloud and your Github account.</li><li>Once the project has been set up, you should be able to find the available jobs ready to use.</li></ol><p><strong><em>Authors:</em></strong></p><p><em>Ripu Jain, Sr Partner Solutions Engineer, Snowflake</em></p><p><em>Karey Graham, Partner Engineering Manager , Matillion</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d46fa16bf983" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/snowflake-cortex-ml-matillion-low-code-ml-elt-new-skill-unlocked-for-data-teams-d46fa16bf983">Snowflake Cortex ML + Matillion = Low Code ML + ELT = new skill unlocked for data teams</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Informatica SuperPipe leverages Snowpipe Streaming for scalable and real-time ingestion into…]]></title>
            <link>https://medium.com/snowflake/how-informatica-superpipe-leverages-snowpipe-streaming-for-scalable-and-real-time-ingestion-into-ca40be68d8cb?source=rss-547ec9a9374c------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca40be68d8cb</guid>
            <category><![CDATA[snowpipe-streaming]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sap]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[informatica]]></category>
            <dc:creator><![CDATA[Ripu Jain]]></dc:creator>
            <pubDate>Tue, 16 Jan 2024 18:01:33 GMT</pubDate>
            <atom:updated>2024-01-16T18:01:33.171Z</atom:updated>
            <content:encoded><![CDATA[<h3>How Informatica SuperPipe leverages Snowpipe Streaming for scalable and real-time ingestion into Snowflake</h3><p>All businesses can benefit by having low latency access to operational data stored in application databases or ERP systems or SaaS applications, for real-time analytical and decision making purposes. Snowflake’s <a href="https://docs.snowflake.com/en/user-guide/data-load-snowpipe-streaming-overview">Snowpipe Streaming </a>API, now Generally Available, enables the low-latency ingestion of streaming rows directly into Snowflake tables with ordered ingestion and high scalability. The challenge still remains how do you configure your data producers and sources to efficiently capture changes, to push and stream the data to Snowpipe Streaming API. Enter Informatica.</p><p>Informatica recently announced General Availability of Informatica SuperPipe for Snowflake included in Informatica’s <a href="https://www.informatica.com/platform/intelligent-data-platform.html">Intelligent Data Management Cloud</a>’s (IDMC) Cloud Mass Ingestion service, that enables customers to, replicate, and stream both initial and incremental data changes up to 3.5x faster than standard change-data-capture (CDC) approaches. Customers will also be able to query the replicated data in Snowflake in near-real time and benefit from the overall cost reduction due to serverless nature of Snowpipe Streaming, which doesn’t require any Snowflake compute running to ingest data.</p><h3>Informatica SuperPipe Overview</h3><p>Informatica SuperPipe for Snowflake achieves ground-breaking performance due to deep integration between IDMC’s Cloud Mass Ingestion service, Snowflake’s Snowpipe Streaming and Snowflake’s ‘Deferred Merge’ pattern, developed in close collaboration with Snowflake.</p><p>Before we dive into Informatica SuperPipe, let’s get familiar with the solution components:</p><ul><li><a href="https://www.informatica.com/content/dam/informatica-com/en/collateral/data-sheet/cloud-mass-ingestion_data-sheet_3716en.pdf"><strong>Informatica Cloud Mass Ingestion</strong></a><strong> (CMI)</strong> is one of the data integration services in the IDMC platform. CMI offers source connectivity to cloud and on-premises Databases, Applications, streaming &amp; IoT sources and files along with efficient log-based CDC capture and automatic handling of source schema changes (schema drift). It provides a wizard driven experience that simplifies ingestion and replication of data either in batches or in real-time .</li><li><strong>Snowpipe Streaming</strong> enables low-latency streaming data pipelines to support ingesting data directly into a Snowflake table.</li><li><strong>Snowflake’s ‘Deferred Merge’ </strong>pattern allows for querying of data that has not yet been merged into a primary table while ensuring consistency of the query result.</li><li><strong>The IDMC </strong><a href="https://docs.informatica.com/cloud-common-services/administrator/current-version/runtime-environments/secure-agents.html"><strong>SecureAgent</strong></a><strong> </strong>is an executable agent that runs data ingestion and integration jobs and enables secure communication across the firewall between your organization and the IDMC SaaS platform. When the Secure Agent runs a task, it connects to an IDMC control plane (IDMC is available in multiple global locations on AWS, Azure, Google Cloud and Oracle Cloud) to access task instructions and job configuration data. The Secure Agent connects directly and securely to sources and targets, transfers data between them, orchestrates the flow of tasks, runs processes, and performs any additional task requirement.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*62VzdQu8P0aMK7I-" /><figcaption>Logical representation of Informatica SuperPipe components</figcaption></figure><h3>Benefits of Informatica SuperPipe</h3><p>With Informatica SuperPipe, you get the following four benefits:</p><ul><li><strong>Improved Performance</strong>: The new approach offers a substantial performance boost, up to 3.5 times traditional CDC methods.</li><li><strong>Unified Data Ingestion</strong>: This solution provides a unified approach for handling multiple data ingestion patterns. It can handle initial bulk loads, continuous replication of incremental changes from the source, and even allow near real-time querying of the streamed changes within Snowflake Data Cloud.</li><li><strong>Reduced Resource Consumption</strong>: By eliminating infrequent merges to target table, the solution reduces performance degradation. This, in turn, enhances resource availability for user queries, ultimately leading to a reduction in the overall Total Cost of Ownership (TCO).</li><li><strong>User-Friendly Interface</strong>: The solution simplifies the user experience by offering a guided and straightforward user interface for configuring ingestion tasks.</li></ul><h3>Step-by-Step guide to configure and use Informatica SuperPipe for Snowflake</h3><p>This section provides a high-level overview of the steps required for both bulk loading and incrementally replicating data to Snowflake Data Cloud using Informatica SuperPipe. For this guide, we are using Oracle as a data source, however, you can bring data from various <a href="https://knowledge.informatica.com/s/article/618516?language=en_US#MassIngestionApp">application </a>and <a href="https://knowledge.informatica.com/s/article/618516?language=en_US#MassIngestionDB">database </a>sources including enterprise applications such as SAP ECC, S/4 HANA, Salesforce etc. We will use the following steps to showcase SuperPipe in action:</p><ol><li>Configure Informatica connector for source (Oracle)</li><li>Add sample data to Oracle source table</li><li>Configure Informatica SuperPipe connector for target Snowflake</li><li>Create and configure a Database Ingestion Task</li><li>Query the data at target (Snowflake Data Cloud)</li><li>Insert new data in source for Incremental Load, and observe it get replicated to Snowflake</li><li>Recap</li></ol><h4>0. Prerequisites</h4><ol><li>An Informatica Intelligent Cloud Data Management (IDMC) account with a configured runtime environment. Sign up for <a href="https://www.informatica.com/in/trials/data-ingestion.html">Informatica Cloud Mass Ingestion trial</a>.</li><li>Install &amp; configure the secure runtime agent for Informatica Mass Ingestion. For more details on how to install refer to <a href="https://docs.informatica.com/integration-cloud/mass-ingestion/current-version/getting-started/getting-started-with-mass-ingestion/installing-secure-agents.html">Installing Secure Agent</a>.</li><li><a href="https://docs.informatica.com/integration-cloud/mass-ingestion/current-version/getting-started/getting-started-with-mass-ingestion/installing-secure-agents/secure-agent-installation-on-windows/secure-agent-requirements-on-windows.html">Secure Agent Requirement for Windows</a></li><li><a href="https://docs.informatica.com/integration-cloud/mass-ingestion/current-version/getting-started/getting-started-with-mass-ingestion/installing-secure-agents/secure-agent-installation-on-linux/downloading-and-installing-the-secure-agent-on-linux.html">Secure Agent Requirement for Linux</a></li><li>An Oracle database with log level replication enabled. Please refer to the <a href="https://docs.informatica.com/integration-cloud/mass-ingestion/current-version/mass-ingestion-databases/mass-ingestion-databases/mass-ingestion-databases-sources---preparation-and-usage/oracle-sources/oracle-privileges.html">Oracle Privileges for Mass Ingestion</a> to configure the required permission on the Oracle database for both incremental and CDC capture. For the supported Oracle versions please refer <a href="https://knowledge.informatica.com/s/article/618516?language=en_US&amp;type=external">link</a>.</li><li>A Snowflake Data Cloud account. If you don’t have a Snowflake account you can sign up for a trial account using the <a href="https://www.googleadservices.com/pagead/aclk?sa=L&amp;ai=DChcSEwiOxv-g7d6BAxVOC60GHZkfAPgYABAAGgJwdg&amp;ae=2&amp;gclid=CjwKCAjwvfmoBhAwEiwAG2tqzJM9ss5_iJ-eYC_tBExV2fgXmG8KkmuMfPS0gWJWmRax8oJiY2DWIhoCG9gQAvD_BwE&amp;ohost=www.google.com&amp;cid=CAESVuD2Jzw1EIqCMrlVtstmoZfSkrVmOE8diDP9i6qOLN-i5maCtBLq3S9BID6874dpM7Fh5PrVG9cAX0QDz7HtW8RVDT5nNTd2ihx6y2VFhw-AB2m7wJRF&amp;sig=AOD64_23ljFc5lCiB6IPLsWbh5P31jMtWg&amp;q&amp;adurl&amp;ved=2ahUKEwjJq_ig7d6BAxX_MjQIHS5aD5IQ0Qx6BAgGEAE&amp;nis=2&amp;dct=1">link</a>.</li></ol><h4>1. Configure Informatica connector for source (Oracle)</h4><ol><li>Log in to IDMC console using your credentials.</li><li>Click on <strong>Administrator</strong> from My Service page</li><li>Click on <strong>Connections </strong>in the navigation panel on the left</li><li>Click <strong>New Connection</strong> on the top right</li><li>Choose <strong>Oracle Database Ingestion </strong>(Informatica) in the <strong>Type</strong> under connection details</li><li>Under <strong>Oracle Database Ingestion Properties</strong> select the <strong>Runtime Environment</strong> that you created as part of the prerequisites</li><li>Enter the other configuration details (e.g., database credentials, host name, service name etc.) for connecting to the Oracle table that you want to replicate to Snowflake under <strong>Common Properties. </strong>For more details on the connection configuration details for Oracle, please refer <a href="https://docs.informatica.com/content/dam/source/GUID-E/GUID-ED760B0A-0811-4BFC-8DAA-F5DF3538800C/21/en/CDI__OracleConnector_en.pdf">Connecting to Oracle Database Guide</a>.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/0*30EjRkP2LbV-EnAF" /></figure><p>Click on <strong>Test Connection</strong> from top right and ensure that the connection was successful. Click <strong>Save.</strong></p><h4>2. Add sample data to Oracle source table</h4><p>Run the following scripts to create a table in the source Oracle database and load sample data for incremental bulk load. Optionally, you can bring your own sample data. In this post we will create a SALES_RECORDS table and insert sample records containing global sales information.</p><ul><li>DDL for creating <a href="https://superpipes3.s3.amazonaws.com/cr_superpipe.sql">SALES_RECORDS</a></li><li>Sample <a href="https://superpipes3.s3.amazonaws.com/dml_superpipe.sql">INSERTS</a></li></ul><h4>3. Configure Informatica connector for Snowflake Data Cloud</h4><ol><li>Click on <strong>Connections </strong>in the navigation panel on the left</li><li>Click <strong>New Connection</strong> on the top right</li><li>Choose <strong>Snowflake Data Cloud (Informatica)</strong> in the <strong>Type</strong> under connection details.</li><li>Under <strong>connection details </strong>select the <strong>Runtime Environment</strong> that you created as part of the prerequisites</li><li>Select the “<a href="https://docs.snowflake.com/en/user-guide/data-load-snowpipe-streaming-configuration#required-properties"><strong>keypair</strong></a><strong>” authentication </strong>mode, as this is the currently supported authentication mechanism for Snowpipe Streaming by Snowflake.</li><li>Enter the other configuration details (e.g., Snowflake account credentials, account name, warehouse name etc.) for connecting to the Snowflake target table under <strong>Common Section. </strong>For more details on connection configuration to Snowflake Data Cloud based on the authentication type (Standard, Authorization Code, Key pair, Client Credentials) please refer to the <a href="https://docs.informatica.com/integration-cloud/data-integration-connectors/current-version/snowflake-data-cloud-connector/part-1--getting-started-with-snowflake-data-cloud-connector/connections-for-snowflake-data-cloud/prepare-for-authentication.html">link</a>.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OD_FcsGUoezUvAHH" /></figure><h4>4. Create and configure a Database Ingestion Task</h4><ol><li>Click <strong>Mass Ingestion</strong> on the My Services page</li><li>Click <strong>New </strong>on the top-left part of the page.</li><li>Click <strong>Database Ingestion Task.</strong></li><li>On the <strong>Definition </strong>page:</li></ol><p>a) In the <strong>Name </strong>field, enter a name for the task.</p><p>b) In the <strong>Location </strong>field, enter the project location.</p><p>c) In the Runtime Environment field, select the runtime environment you configured.</p><p>d) In the Load Type field, select Initial and Incremental Loads. Click <strong>Next</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Mc0cDKt4uSj5dOLn" /></figure><p>5. On the <strong>Source</strong> page:</p><p>a) For Connection, select the Oracle Database Ingestion source connection that you previously defined.</p><p>b) For Schema field, select the schema that contains the source objects that needs to be replication</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GFqzoyKS59gDaQ4h" /></figure><p>c) Under <strong>Table Selection</strong>, select the <strong>Select All</strong> check box only if you want to select all source objects in the schema. For rule-based selection use the add <strong>(+)</strong> to define the Include / Exclude rules. The default “Include *” rule selects all objects in the schema. Note: The rules are processed in the order they are listed. After you apply the rules, you have the option to select or deselect tables individually under <strong>Table View</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0HC1Ycv9aOXPG1eM" /></figure><p>6. On the <strong>Target</strong> page:</p><p>a) For <strong>Connection</strong>, select the Snowflake target connection that you previously defined.</p><p>b) For <strong>Target Creation</strong>, select <strong>Create Target Tables</strong>. This will create a new table if the Snowflake table does not exist.</p><p>c) For <strong>Schema</strong>, select the target schema</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/0*uBh-q4VPODOKwfLX" /></figure><p>d) In the <strong>Apply Mode</strong> field, select from the different apply modes Standard, Soft Delete or Audit. Standard replicates the data as-is, soft-delete replicates all the DMLs other than deletes, and finally audit mode inserts all the changes to be merge-applied later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*C3WehxZGZdR3wU73" /></figure><p>e) Keep the <strong>Stage </strong>field blank/empty — iit disappears as soon as you select the <strong>Superpipe</strong> option in the next step.</p><p>f) Under <strong>Advanced</strong>, enable the <strong>Superpipe </strong>option and the Snowflake <strong>Deferred Merge </strong>frequency in seconds. Note that the merge frequency depends on your business need, however, for analytics use cases, you can choose to merge every 1–2 hours or greater.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/637/0*wHsEbZJ6UiUbW9EU" /></figure><p>7. On the <strong>Schedule and Runtime Options</strong> page, retain the following default values for <strong>Schema Drift Options</strong>:</p><p>a) Add Column: Replicate</p><p>b) Modify Column: Replicate</p><p>c) Drop Column: Ignore</p><p>d) Rename Column: Ignore</p><p>For more details on how Mass Ingestion handles schema drift and details regarding the options, refer to this <a href="https://docs.informatica.com/integration-cloud/mass-ingestion/current-version/mass-ingestion-databases/mass-ingestion-databases/configuring-a-database-ingestion-task/configuring-schedule-and-runtime-options.html">link</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Ebr1vYvERyjQ1zCi" /></figure><p>8. After you are done configuring the task, click <strong>Save </strong>and then click <strong>Deploy </strong>to create a job instance.</p><p>9. To run the job, go to the <strong>My Jobs</strong> page in Mass Ingestion and find the row for the job instance. In the Actions (…) menu on the right end of row, click <strong>Run</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/881/0*llOt_54-mgCr9Uwi" /></figure><p>10. After the job has the status of <strong>Up and Running</strong>, you can view the processing status of the job and each source object and the data flow in <strong>Mass Ingestion</strong> &gt; <strong>My Jobs</strong> or Operational Insights &gt; <strong>Mass Ingestion</strong>. Example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5Q2M-XFBSwI0bWAw" /></figure><h4>5. Query the Snowflake target table</h4><p>Log into the Snowflake, and run the following query against the view created as part of Superpipe configuration</p><p><em>select REGION,</em></p><p><em>count (REGION) as record_count,</em></p><p><em>sum (units_sold) as units_sold_per_region,</em></p><p><em>sum(total_revenue) as revenue_per_region,</em></p><p><em>sum (total_profit) as profit_per_region</em></p><p><em>from TBL_DEMO_SALES_RECORDS</em></p><p><em>Group by REGION order by record_count desc;</em></p><p>Note: The results will be like the screenshot below, reflecting the data replicated as part of initial load.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vOkhobvrpAHfg0-F" /></figure><h4>6. Insert new data for Incremental Load, and observe it get replicated to Snowflake</h4><ol><li>Insert new records to the Oracle source table to simulate incremental load. Make a note of the unique identifier (e.g., RECORD_ID to uniquely identify sales record) to verify the data has replicated to the Snowflake target table in near real time.</li></ol><p><em>INSERT INTO TBL_DEMO_SALES_RECORDS(REGION, COUNTRY, ITEM_TYPE, SALES_CHANNEL, ORDER_PRIORITY, ORDER_DATE, ORDER_ID, SHIP_DATE, UNITS_SOLD, UNIT_PRICE, UNIT_COST, TOTAL_REVENUE, TOTAL_COST, TOTAL_PROFIT, RECORD_ID) VALUES (‘DEMO_REGION’, ‘Mali’, ‘Baby Food’, ‘Online’, ‘L’, ‘11/3/2005’, 290227735, ‘12/7/2015’, 3691, 255.28, 159.42, 942.85, 126301.67, 62217.18, 1205);</em></p><p><em>INSERT INTO TBL_DEMO_SALES_RECORDS(REGION, COUNTRY, ITEM_TYPE, SALES_CHANNEL, ORDER_PRIORITY, ORDER_DATE, ORDER_ID, SHIP_DATE, UNITS_SOLD, UNIT_PRICE, UNIT_COST, TOTAL_REVENUE, TOTAL_COST, TOTAL_PROFIT, RECORD_ID) VALUES (‘Asia’, ‘Sri Lanka’, ‘Fruits’, ‘Online’, ‘L’, ‘11/7/2011’, 830192887, ‘12/18/2011’, 1379, 9.33, 6.92, 12866.07, 9542.68, 3323.39, 1206);</em></p><p><em>INSERT INTO TBL_DEMO_SALES_RECORDS(REGION, COUNTRY, ITEM_TYPE, SALES_CHANNEL, ORDER_PRIORITY, ORDER_DATE, ORDER_ID, SHIP_DATE, UNITS_SOLD, UNIT_PRICE, UNIT_COST, TOTAL_REVENUE, TOTAL_COST, TOTAL_PROFIT, RECORD_ID) VALUES (‘Sub-Saharan Africa’, ‘Seychelles’, ‘Beverages’, ‘Online’, ‘M’, ‘1/18/2013’, 425793445, ‘2/16/2013’, 597,47.45, 31.79,28327.65, 18978.63, 9349.02, 1207);</em></p><p>2. The secure agent reads the new records written in source, and <strong>SuperPipe makes the changes available immediately in snowflake</strong> via a view, which is available for consumption by downstream systems. Run the following query in Snowflake to see the replicated sales data as part of incremental load.</p><p><em>SELECT * from CMIUSER01.PUBLIC.TBL_DEMO_SALES_RECORDS where RECORD_ID = 1205</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dITEVeclx4BkG9oi" /></figure><h4>7. Simplified diagram to recap how source data gets SuperPipe’d to Snowflake</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_25SJp747I6mzlJA" /><figcaption>Logical data flow diagram of this guide</figcaption></figure><h3>Summary and Next Steps</h3><p>This guide demonstrated how with Informatica SuperPipe, one can quickly create a replication task via a simple and guided user interface, and load data (both bulk &amp; incremental load) into Snowflake Data Cloud from various sources. Informatica and Snowflake customers can now leverage Informatica SuperPipe, and its integration with Snowpipe Streaming and ‘Deferred Merge’ to load data up to 3.5x faster and enable their real-time analytics use cases on Snowflake.</p><p><a href="https://www.informatica.com/in/trials/data-ingestion.html">Sign up for a trial of Informatica SuperPipe — with Cloud Mass Ingestion</a></p><h4><em>Authors</em></h4><p><em>Ripu Jain, Partner Solution Architect, Snowflake</em></p><p><em>Dhirendra Sinha, Vishwa Belur, Product Management, Informatica</em></p><p><em>Rajeev Srinivasan, Ecosystem Solutions, Informatica</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca40be68d8cb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/how-informatica-superpipe-leverages-snowpipe-streaming-for-scalable-and-real-time-ingestion-into-ca40be68d8cb">How Informatica SuperPipe leverages Snowpipe Streaming for scalable and real-time ingestion into…</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Boosting Data Team Productivity with Ascend and Snowpark for Python]]></title>
            <link>https://medium.com/snowflake/boosting-data-team-productivity-with-ascend-and-snowpark-for-python-54cf7ba6101b?source=rss-547ec9a9374c------2</link>
            <guid isPermaLink="false">https://medium.com/p/54cf7ba6101b</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[snowpark]]></category>
            <category><![CDATA[data-transformation]]></category>
            <dc:creator><![CDATA[Ripu Jain]]></dc:creator>
            <pubDate>Tue, 25 Oct 2022 12:45:59 GMT</pubDate>
            <atom:updated>2022-10-25T12:45:59.256Z</atom:updated>
            <content:encoded><![CDATA[<p>Snowflake announced <a href="https://www.snowflake.com/blog/snowpark-python-innovation-available-all-snowflake-customers/?_ga=2.223250587.1288067583.1661727319-1975464197.1639685171">Snowpark for Python available for public preview</a> at Summit 2022, after an extensive private preview phase that included Ascend.io. With more than 90% of Ascend.io customers already using Python, it’s in the interest of our mutual customers to integrate this new capability in the <a href="https://www.ascend.io/snowflake/">Ascend experience</a> — and empower users with the best that Snowflake has to offer.</p><p>In this blog, we will step through a tutorial that summarizes what makes the partnership between Snowflake and Ascend such an advantage for data teams using Snowpark for Python. The tutorial also highlights key collaboration features that are unique to Ascend, with which all data experts — from data analysts to engineers to scientists — can link up their pipeline logic.</p><h3>Quick Introductions</h3><h4>What is Snowpark?</h4><p><a href="https://www.snowflake.com/snowpark/">Snowpark</a> is a library with an API that developers can use for querying and processing data in the Snowflake Data Cloud. Snowpark’s key benefit is that it enables data teams to code in languages other than SQL — such as Scala, Java, and <a href="https://www.snowflake.com/blog/snowpark-python-innovation-available-all-snowflake-customers/?_ga=2.253117257.1326421473.1657633822-1274125998.1646910486">now Python</a> — without moving data out of Snowflake and, therefore, take full advantage of its powerful capabilities through code.</p><h4>What is Ascend?</h4><p>Ascend is a data automation platform that boosts productivity by unifying ingestion, transformation, orchestration, and delivery of data for any business use case. Data and analytics engineering are mission-critical processes that are hampered by loosely connected tools, and are ripe for consolidation. Ascend’s seamless automation spans all the steps of building and operating workflows end-to-end, and is available on Snowflake in AWS, GCP, and Azure.</p><h4>Better Together: Snowflake, Python, and Ascend</h4><p>The combination of Snowflake and Ascend.io has resulted in an integrated, high-performance platform to automate your data processing workflows, regardless of scale or complexity. When you use native Snowpark for Python libraries within the full Ascend automation context, the resulting transformations are seamlessly orchestrated by Ascend, which assures that your Snowflake-based data pipelines benefit from free ingestion and validation, guaranteed sequencing and lineage, deep job monitoring, and powerful pause/resume functions that save headaches, time, and money.</p><p>Ascend also augments Snowflake’s own data-sharing capabilities to bring the efforts of individual developers together like no other platform can. With Ascend’s unique pipeline sharing controls, developers can link the processing logic and orchestration of each others’ pipelines across teams, assuring end-to-end pipeline integrity with no additional code.</p><p>In today’s tutorial, one developer is responsible for creating and publishing clearly defined weather data, while an analytics team needs this weather data as input to perform analytics on taxi cab rides. Since both use Ascend to automate their data pipelines, the taxi analytics team can subscribe to the weather data pipeline with a simple mouse click. Not only does one receive the data from the other, but Ascend also assures that the two are orchestrated together, data updates cascade correctly through both, logic changes are propagated end-to-end, and error notifications reach all affected parties.</p><p>Here is the sequence of our tutorial today:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ETIi9gARWhMBucPs" /></figure><h3>Quick Setup</h3><p>For those who play along:</p><ol><li>Let’s make sure you have a working Snowflake account and can log in with user credentials. Have your credentials ready for a later step. If you don’t have a Snowflake account, you can create a free trial account by <a href="https://signup.snowflake.com/">signing up here</a>. Then follow <a href="https://developer.ascend.io/docs/snowflake-data-plane">these instructions</a> to properly provision your Snowflake account for Ascend to be able to interoperate fully and automate your Snowflake workloads on your behalf.</li><li>This tutorial is based on the Ascend Cloud Developer tier, for which you can create a free <a href="https://cloud.ascend.io/">account here</a>. When you log into a new account, a brief onboarding process will automatically prompt you to enter your Snowflake credentials and connect your Snowflake account to your Ascend account. Configuring this connection is similar to the process described in <a href="https://developer.ascend.io/docs/snowflake">these instructions</a>. The onboarding process includes linking to the Snowflake tables containing your data, similar to loading external data <a href="https://developer.ascend.io/docs/snowflake-read">described here</a>.</li><li>Go ahead and load the data for this tutorial into your Snowflake database directly as <a href="https://docs.snowflake.com/en/user-guide/data-load-bulk.html">described by Snowflake here</a>, or from your own file system like Amazon S3 as <a href="https://developer.ascend.io/docs/aws-s3">described by Ascend here</a>. In our example, the taxi analytics team uses the public taxi cab ride datasets published by the New York Taxi &amp; Limousine Commission at <a href="https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page">this link</a>, and loads it into a table called “CLEAN_GREEN_CAB.” The weather data developer uses data published by the NOAA at <a href="https://www.ncdc.noaa.gov/cdo-web/datasets">this link</a>, and loads it into a table called “Raw Sensor Data.”</li></ol><p>Let’s get going!</p><h3>Step 1: Augmenting Raw Taxi Cab Data With A Simple Data Pipeline</h3><p>Let’s begin our example with the taxi analytics team, and build a simple data pipeline to prepare the taxi cab data for the analytics. We’ll start the pipeline with the initial dataset containing 4.68M rows. In Ascend, each data pipeline is called a Dataflow, and when displayed on the UI, the initial taxi analytics data pipeline looks something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/0*e-9yupWcV3tBhNqV" /></figure><p><em>A simple dataflow viewed in the Ascend UI</em></p><p>In our example, the “CLEAN_GREEN_CAB” component on the UI is a Read Connector that represents the linked table with the initial datasets, for which a sample can be inspected directly in the Ascend UI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Jrew1pOEy2s9TIWa" /></figure><p><em>Sample records of the taxi cab data viewed in the Ascend UI</em></p><p>The first processing step is a Transform component, where you can use Snowpark for Python to clean the data. You can add this component by selecting “Snowpark Python” from the left-side panel of the canvas, or by clicking the three vertical dots on the top right corner of the “CLEAN_GREEN_CAB” Read Connector.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*UIb5ry7X28rAq-DQ" /></figure><p><em>Adding processing steps to a pipeline in the Ascend UI</em></p><p>This action lands you in the configuration screen to create this new component, prepopulated with “CLEAN_GREEN_CAB” as an input. In our example, we name the component “Augmented_CLEAN_GREEN_CAB”, and since we won’t need to partition the data, you can select “Reduce all partitions to One” as the partition strategy. This simply instructs Ascend to generate a single physical table for the results of the transformation step.</p><p>Unlike other platforms that only provide their own generalized ETL dialect, specifying the component as a “Snowpark” transform unlocks native Snowpark for Python and all of its features. In our simple example, we add a new column called “pickup_date” by converting “pickup_datatime” from string to a date data type, and we add a new column named “cab_type” with “Super Green” as the value.</p><p><em>def transform(session: Session, inputs: List[DataFrame]) -&gt; DataFrame:</em></p><p><em>table = inputs[0]</em></p><p><em>return table.withColumn(‘cab_type’, F.lit(‘Super Green’)) \</em></p><p><em>.withColumn(‘pickup_date’, F.date_trunc(‘day’, F.to_timestamp(‘PICKUP_DATETIME’)))</em></p><p>The next screenshot shows the actual configuration screen for our example.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BwBCG6miCN7Qc3od" /></figure><p><em>The configuration panel of a Snowpark for Python Transform in the Ascend UI</em></p><p>The configuration screen also shows a “Component Pausing” option below the code (not visible in this screenshot). Should you want to keep the automation from immediately processing the code when you click the “CREATE” button, set this option to “Paused.” With the option set to “Running,” the Ascend automation immediately incorporates your code into the pipeline and creates the incremental Snowflake queries that are needed to update it. Ascend automation seamlessly issues these queries to Snowflake to create a new table and load its records, monitor the process, and update the UI to the “UP TO DATE” state once execution is finished.</p><p>The result is the simple, two-step pipeline we saw earlier:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/0*Y2otAHD6IROLT-Y1" /></figure><p><em>The first Transform in the taxi analytics dataflow viewed in the Ascend UI</em></p><p>Click on the middle of the new Transform component to get a popup, then select the “Records” tab to see the new columns and sample data from the new underlying Snowflake table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/594/0*XAjpI4VnQakxg07i" /></figure><p><em>Validating a Transform in the Ascend UI</em></p><p>Since all of the data for Transform operations stays in Snowflake, you can also directly query the table in Snowsight to verify that it matches the Ascend view:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/591/0*G0VmnLlFPjq9ZcAn" /></figure><p><em>Validating data in the taxi analytics dataflow viewed in the Snowsight UI</em></p><p>It is that easy! You have successfully created your first data pipeline with Snowpark for Python in Ascend. With the components set to “Running,” Ascend’s built-in orchestration constantly monitors the source tables for any new data arrivals, and automatically processes them through the pipelines with no further action required from you.</p><h3>Step 2: Sharing Data in Ascend Between Teams</h3><p>Now let’s get into how multiple developers and data teams use Ascend to connect their data pipelines.</p><p>In our example, the weather data developer is responsible for cleaning up the raw weather data from NOAA, and then sharing it with other analytics teams, including the taxi analytics team. The weather data developer must share data in a way that</p><ol><li>provides controls over who gets access to the data,</li><li>is always updated with new data arriving,</li><li>is always updated per new data cleansing rules, and</li><li>it is easily discoverable by other data teams</li></ol><p>The weather data developer is also required to</p><ol><li>report on which teams are using the cleaned weather data,</li><li>notify them of any errors,</li><li>version all the cleansing logic in the central code repository, and</li><li>resume data updates as soon as updates are applied without generating any duplicate records.</li></ol><p>Good thing they’re using Ascend, which automates every one of these requirements!</p><h3>Cleaning Up and Sharing Weather Data</h3><p>To get started, our weather data developer creates a separate Dataflow in Ascend and encodes the cleansing logic in a Transform called “Max Sensor Reading By Day” using Snowflake SQL and similar configurations as you used for the taxi cab data earlier in this example. They then terminate it with a special Data Feed component, so that it looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/0*YR32J1K1f-p-5fuA" /></figure><p><em>The weather data Dataflow viewed in the Ascend UI</em></p><p>The Data Feed component is for sharing the outputs of the data pipeline. To add that component, click the “Create Data Feed” button on the left-side panel of the UI, or click on the dots on the top right corner of the final Transform component, like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1hPoJc6frU9WdC2z" /></figure><p><em>Creating a Data Feed sharing component in the Ascend UI</em></p><p>This action lands you in the configuration screen for this new component, prepopulated with “Max Sensor Reading By Day” as an input. Here you can name the Data Feed with an intuitive name that other teams will recognize, configure the access you would like others to have, and click “Create”. With that, the platform instantly publishes the Data Feed to the Ascend Data Feed Catalog, where other teams or users can discover it and subscribe to it to use as a source in their own data pipelines.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Y4YAVIIGjtL72MaV" /></figure><p><em>Configuring a Data Feed sharing component in the Ascend UI</em></p><p>That is all that is needed to share datasets between Dataflows! Ascend automatically fulfills all the requirements that were laid out for our thrifty weather data developer, so they can move on to the next project and deliver new business value!</p><h3>Step 3: Connecting to Weather Data for Taxi Cab Ride Analysis</h3><p>Let’s switch back to the taxi analytics data pipeline that the taxi analytics team was building, and use the cleaned weather data in the taxi cab ride analysis. We do this by subscribing to the weather Data Feed from inside your own taxi cab data Dataflow. To find the weather data, click “Connect to Data Feed” on the left side panel to access the Ascend Data Feed Catalog. You can search or browse the catalog, and if it was configured by the weather data developer with the right permissions for you, you can connect to the weather Data Feed. Note the Subscribe button, as well as the visible metadata by which to confirm the right dataset for your analytics:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*q9MEkYzFYu4frr_s" /></figure><p><em>Subscribing to a Data Feed in the Ascend UI</em></p><p>The Data Feed is a virtual construct that connects the automation logic of the two data pipelines and points to the actual table from the weather data pipeline in Snowflake. It does not make a physical copy or replicate it into another table. This way you are guaranteed to always have the latest data in your data pipeline, running in the right order with all the other linked data pipelines. Ascend automation handles all this coordination behind the scenes.</p><h3>Step 4: Analyzing Taxi Cab Rides in Rainy Weather</h3><p>Let’s wrap up our analysis of the impact of rainy weather on taxi cab ride tips in two more steps. We could have probably used Snowflake SQL for this, but let’s keep the spotlight on Snowpark for Python to join the “Augmented CLEAN_GREEN_CAB” dataset with the clean weather Data Feed.</p><p>Here is the Transform with the code that filters out the taxi cab rides on rainy days:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JEHw7owLItoWbAfV" /></figure><p><em>Using Snowpark for Python to join a Transform and a Data Feed in Ascend</em></p><p>The last step in the sequence is a Transform that analyzes the tip percentage by rider count, for these taxi cab rides on rainy days:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YokKVOnr5r3LVBhd" /></figure><p><em>Using Snowpark for Python to add a filter to a data pipeline in Ascend</em></p><p>Using the records preview in the configuration panel for this final Transform, the data looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/672/0*Eqg5ulD5sLKTjFhF" /></figure><p><em>Record view of the output of the tip % analysis in the Ascend UI</em></p><p>Return to the overview of your taxi analytics data pipeline by closing the configuration panel, and the Ascend DataFlow UI looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_P_v02xjxOx3HLId" /></figure><p><em>Dataflow with rainy day taxi cab ride tip analysis pipeline in the Ascend UI</em></p><p>Note the record counts for each of the components as the data is processed through the pipeline. Any new data arriving in the “CLEAN_GREEN_CAB” data source in your data pipeline will automatically be processed incrementally, and increase the records counts handled by those components.</p><p>Similarly, any new data arriving in the “Raw Sensor Data” data source in the weather data pipeline will be processed there, then trigger the “Taxi Rides on Rainy Days” in yours through the Data Feed. However, it will NOT cause a reprocessing of the taxi cab data — Ascend only creates Snowflake jobs to handle changes and avoids reprocessing wherever possible. In addition, if the result of “Taxi Rides on Rainy Days” is the same as before, it will stop the sequence and NOT rerun “Tip % by Rider Count.” In our example, this could happen if weather data arrived for yesterday, but no taxi cab ride data for yesterday has arrived yet. The join would yield no new data for yesterday, so no downstream update is needed.</p><p>So what does all this look like in Snowflake? Behind the scenes, Ascend has created the objects in Snowflake and is constantly pushing the right jobs in the right order so that Snowflake updates the records correctly. Your Snowflake console view might look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/353/0*ennZoECIeWdrR29F" /></figure><p><em>Ascend-generated objects in the Snowflake UI</em></p><p>You’ve just completed a data pipeline in Ascend, and Ascend is running it correctly for all new data arrivals and through all the dependencies!</p><h3>Final Thoughts on Snowpark for Python and Ascend</h3><p>All the data engineers, analytics engineers, developers, data analysts, and data scientists in your organization can now harness the full power of Snowpark for Python in the context of Ascend’s unified, fully integrated <a href="https://www.ascend.io/product/">data automation platform</a>.</p><p>The combination of Ascend automation and Snowpark for Python avoids many seams in technology and data that result from piecemeal data pipeline architectures. Instead of writing lots of custom code papering over the gaps, you leapfrog into deep data workload automation with out-of-the-box functionality. Your developers and data teams can not only share data in Snowflake where it resides, but also collaborate easily on organizing and sequencing their pipeline logic along business principles instead of IT silos or technology boundaries. Behind the scenes, Ascend orchestrates the workloads within Snowflake, all while increasing security, governance, and observability.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54cf7ba6101b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/boosting-data-team-productivity-with-ascend-and-snowpark-for-python-54cf7ba6101b">Boosting Data Team Productivity with Ascend and Snowpark for Python</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>