<?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[Dataform - Medium]]></title>
        <description><![CDATA[Dataform is the simplest way to manage your data - Medium]]></description>
        <link>https://medium.com/dataform?source=rss----3ef182b8ea8f---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Dataform - Medium</title>
            <link>https://medium.com/dataform?source=rss----3ef182b8ea8f---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 22:06:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/dataform" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Using Javascript to Create an Acquisition Funnel Dataset]]></title>
            <link>https://medium.com/dataform/using-javascript-to-create-an-acquisition-funnel-dataset-88accc13ae66?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/88accc13ae66</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Emilywilson]]></dc:creator>
            <pubDate>Tue, 10 Nov 2020 18:25:54 GMT</pubDate>
            <atom:updated>2020-11-11T11:36:48.248Z</atom:updated>
            <content:encoded><![CDATA[<p>I work as a data analyst on the growth team at <a href="https://www.echo.co.uk/">Echo</a>, where a key goal is to improve signup conversion rates. We use acquisition funnel datasets to get visibility into where users are dropping off in the sign up process. To manually write all of the SQL queries needed for this would be time consuming and difficult to manage. Using a combination of SQL and JavaScript makes this process much easier.</p><p>I use <a href="https://dataform.co/">Dataform</a> to do this, which provides a place to write and save the queries, and runs them on schedules which publish up to date data every hour to a table in our warehouse (BigQuery). This table can then be queried for analyses and to create visualisations.</p><p>This post aims to explain the steps to create this dataset.</p><h3>Step 1: Define the Funnel Steps</h3><p>Firstly, we need to set up a data structure that defines each stage of the funnel.</p><p>In a file called funneldata.js, a list is created that will contain one entry for each funnel. Each funnel has a name (e.g. signup_funnel), and a list of steps (e.g. landing, start_signup, verify_email). Each step has a name, and a list of tracks that indicate the user has reached that stage of the funnel. In our case, we use specific frontend tracks that we get from Segment. Some steps have multiple tracks because there are multiple ways a user could reach that step - for example on the landing step, users can either reach it through landing on the homepage, on web, or installing the app on mobile.</p><pre>const funnels = [<br>  {<br>    name: &quot;signup_funnel&quot;,<br>    steps: [<br>      {<br>        name: &quot;landing&quot;,<br>        events: [&quot;application_installed&quot;, &quot;home_page&quot;]<br>      },<br>      {<br>        name: &quot;start_signup&quot;,<br>        events: [&quot;signup_started&quot;]<br>      },<br>      {<br>        name: &quot;verify_email&quot;,<br>        events: [&quot;email_verified&quot;]<br>      },<br>      {<br>        name: &quot;signup_complete&quot;,<br>        events: [&quot;signup_complete&quot;]<br>      }<br>    ]<br>  }<br>];</pre><pre>module.exports = funnels;</pre><h3>Step 2: Create One Table per Step</h3><p>Next, we create another file called funnel_steps.js. This creates one table per funnel step, which has one row per user, and records when they first reached that step of the funnel.</p><ol><li>First, we reference the funneldata.js file that we created in the previous step.</li></ol><pre>const funnels = require(&quot;./funneldata.js&quot;);</pre><p>2. Then we use the ForEach JavaScript function to loop through each funnel (we only have one for now, but we may add more later) and then loop through each step in the funnel.</p><pre>funnels.forEach(funnel =&gt; {<br>    funnel.steps.forEach((step, i) =&gt; {</pre><p>3. For each step, we use the publish and config functions (Dataform-specific functions) to create a table in our data warehouse with a unique name (the funnel name &amp; step name combined).</p><pre>funnels.forEach(funnel =&gt; {<br>    funnel.steps.forEach((step, i) =&gt; {</pre><p>4. We add a variable to the SQL query which means for every step after the first, the where clause will have an additional filter on the user id being in the previous step. This ensures that the funnel is sequential, i.e. users have to have hit the first step to be recorded in the second step, and so on. The ${ctx.ref( )} syntax is another Dataform-specific feature, which ensures that the previous step’s table is published before the current table, to ensure data is up to date.</p><pre>let where = &quot;&quot;;<br>if (i &gt; 0) {<br>  let previousStep = `${funnel.name}_${funnel.steps[i - 1].name}`;<br>  where = `and user_id in (SELECT user_id from ${ctx.ref(previousStep)})`;<br>}</pre><p>5. Lastly we write the SQL query, plugging in the variables. The bulk of the query is hard-coded as it’s the same for each funnel step. The variables are the parts that can change for each step — the step name, step number, the frontend tracks for the step that we defined in funneldata.js, and the optional additional filter using the where clause.</p><pre>return `<br>            SELECT * EXCEPT(row_number) FROM (<br>                SELECT<br>                user_id,<br>                &quot;${step.name}&quot; as step_name,<br>                ${i} as step_number,<br>                timestamp as step_started_at,<br>                row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number<br>                FROM ${ctx.ref(&quot;segment&quot;)}<br>                where lower(event) in (&quot;${step.events.join(&#39;&quot;, &quot;&#39;)}&quot;)<br>                ${where}<br>                GROUP BY 1,2,3,4<br>                  )<br>            where row_number = 1<br>                `<br>            }<br>        );<br>    });<br>}</pre><p>This is the SQL that the JavaScript generates for the first step:</p><pre>SELECT * EXCEPT(row_number) FROM (<br>  SELECT<br>    user_id,<br>    &quot;landing&quot; as step_name,<br>    0 as step_number,<br>    timestamp as step_started_at,<br>    row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number<br>  FROM `api.segment`<br>  WHERE lower(event) in (&quot;application_installed&quot;, &quot;home_page&quot;)<br>  GROUP BY 1,2,3,4<br>  )<br>where row_number = 1</pre><p>This is an example of the SQL generated by the JavaScript for the subsequent steps:</p><pre>SELECT * EXCEPT(row_number) FROM (<br>    SELECT<br>      user_id,<br>      &quot;start_signup&quot; as step_name,<br>      1 as step_number,<br>      timestamp as step_started_at,<br>      row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number<br>    FROM `api.segment`<br>    where lower(event) in (&quot;signup_started&quot;)<br>        and user_id in (SELECT user_id from `funnel_data.signup_funnel_landing`)<br>    GROUP BY 1,2,3,4<br>    )<br>where row_number = 1</pre><h3>Step 3: Combine the Data Into One Funnel Table</h3><p>Finally, we combine the data from each of the step tables into one table that summarises the funnel — this is the one we query for analyses. We do this in a file called funnel_combine.js.</p><p>Again, we start by referencing the funneldata.js file. Then we use the ForEach function to loop through each funnel (we only have one for now). A select * statement for each step is created, and they&#39;re joined with union all. The publish and config functions are again used to create a table in the warehouse.</p><pre>const funnels = require(&#39;./funneldata.js&#39;);</pre><pre>funnels.forEach(funnel =&gt; {<br>    publish(`${funnel.name}`, {<br>        type: &quot;table&quot;,<br>        schema: &quot;funnel_test&quot;<br>    })<br>    .query(ctx =&gt;<br>      funnel.steps<br>      .map(step =&gt; `select * from ${ctx.ref(`${funnel.name}_${step.name}`)}`)<br>      .join(`\n union all \n`)<br>    );<br>})</pre><p>This is the SQL that the Javascript generates:</p><pre>select * from `funnels.signup_funnel_landing`<br>  union all<br>select * from `funnels.signup_funnel_start_signup`<br>  union all<br>select * from `funnels.signup_funnel_verify_email`<br>  union all<br>select * from `funnels.signup_funnel_signup_complete`</pre><p>Running the Javascript in the final file publishes a table that looks like this to the data warehouse:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TOEmq05_Uuwx5jke.png" /></figure><p>This dataset can be used to quickly get insights on activity during sign up, such as what % of users that start the funnel get to each step, and how long users spend on each step. Future funnels can be created easily too — all that needs to be done is to define the steps in the funneldata.js file, and to run the funnel_combine.js script. Anyone that wants to create a funnel dataset can do so now - no prior SQL experience is needed!</p><p>Dataform makes it super easy to use JavaScript to define simple scripts, constants or macros that can be reused across your project, saving you time writing out long SQL queries. Sign up for a <a href="https://dataform.co/">free Dataform account here</a>.</p><p><em>Originally published at </em><a href="https://dataform.co/blog/using-js-acquisition-funnel"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=88accc13ae66" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/using-javascript-to-create-an-acquisition-funnel-dataset-88accc13ae66">Using Javascript to Create an Acquisition Funnel Dataset</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Switching to a better data warehouse naming convention]]></title>
            <link>https://medium.com/dataform/switching-to-a-better-data-warehouse-naming-convention-20c95535e8f3?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/20c95535e8f3</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Daniel Lee]]></dc:creator>
            <pubDate>Thu, 05 Nov 2020 18:34:38 GMT</pubDate>
            <atom:updated>2024-03-12T08:53:47.869Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G6JehNPOlgtaJ3VkdKpGLw.png" /></figure><p><em>Dan is the Founder of Outlier, </em><strong><em>your fractional data team </em></strong><em>| </em><a href="https://weareoutlier.com/"><em>weareoutlier.com</em></a></p><p>Dataform is still a relatively young company, and so doesn’t yet have a dedicated “data” team. That said, most people within the company have an interest in data and are proficient in SQL — unsurprisingly, given the product we build. Because of these two things, ownership of our analytics code (managed in Dataform) is distributed across the team.</p><p>Our goal is for everyone to feel comfortable both contributing to the data modelling code, and using the data. But last week we realised we weren’t meeting this goal.</p><p>Lewis, our CTO, mentioned in a conversation we were having that he often found it difficult to know where new additions to our data model belonged. More than once, this has led to him building something from scratch, which we later realised was already covered somewhere else.</p><p>Josie, who leads marketing at Dataform, experienced the problem from the other side. As a growth marketer, she’s keen to track the impact of all her efforts. But finding the right tables to use, and being confident that she was looking in the right place, was not easy.</p><p>As the person who spends most time in our project, I was somewhat blind to these issues. The majority of the code was either written, or reviewed, by me — so I usually know where things belong, and how to get what I want. But on hearing the challenges Lewis and Josie were having, we decided it was time for a change.</p><h3>Identifying the issue: names</h3><p>As a first step in addressing the issue, the three of us got together to try and understand the problem a bit more deeply.</p><p>For our CTO, the issue was with development: they wanted to make some changes to our code, but weren’t quite sure where those changes should go. They were recently conducting an analysis to understand how many schedule runs each of our customers do each day, but it wasn’t clear where that metric should fit into our existing tables.</p><p>For our marketing manager, the issue was more in consuming the data: of all the 100s of tables, which was the “right” one to use for an analysis? They’re very focused on user acquisition, but there are at least ten tables that look like they might be relevant: user_stats, user_attribution_stats, users, daily_user_stats, …</p><p>After a brief discussion, we hypothesised that most of our problems could be solved if we structured our data warehouse more clearly and had a clearer naming convention for tables. Our analytics codebase is broken into modular, self-contained chunks, and each chunk is a dataset (table or view). If the name of each dataset fully described its contents, it would make both development and consumption much simpler.</p><h3>Setting a naming convention</h3><p>Our hypothesis was that if we named our datasets more clearly, it’d both be easier for developers to contribute to our data model, and users to consume our data.</p><h4>Properties of a good name</h4><p>Ideally, you’d be able to see the name of a dataset and have a very clear idea of what data it contains — what it’s purpose is.</p><ul><li><em>Domain</em>: We analyse data from many different sources, and so it’s important first to understand the domain of the data. We use Stripe, Intercom, Segment and more, and each contain information about <em>users</em>. The name should tell us which <em>flavor</em> of users that data describes: stripe users, pipedrive users etc</li><li><em>Grain</em>: One of the most important things to know about a dataset is what an individual row (or record) represents — this if often called the <em>grain</em> of the dataset.</li><li><em>transformation stage</em>: The data modelling logic we process in Dataform takes raw data, cleans it up, and finally presents it in a way that is optimized for analytics. The name should clearly indicate whether a dataset is intended to be used for analytics or if it is a stepping stone along the way.</li></ul><h4>Structure of a data warehouse object (i.e. table) name</h4><p>Most data warehouses follow a similar pattern for the names they allow you to give to a dataset:</p><pre>database_name.schema_name.table_name</pre><p>The next step was to decide where in this structure to include the <em>domain</em>, <em>grain</em> and <em>stage</em> information. After some discussion, we came up with a plan:</p><ul><li>database<em>name: should differentiate between _environments</em>. For us, this means we have dataform_corp (our production environment) and dataform_corp_dev (our development environment, more on environments <a href="https://docs.dataform.co/dataform-web/scheduling/environments">here</a>).</li><li>schema_name: differentiates between domains, e.g. stripe, intercom, segment.</li><li>table_name: describes the grain of the dataset.</li></ul><p>So, we might have a table called dataform_corp.segment.users. This is a table in our production environment that has one row per Segment user.</p><h4>Treating transformation stages as domains</h4><p>The naming convention described above still misses one piece: the “transformation stage”. Looking at the existing datasets in our warehouse, we realised that they fit into one of three buckets:</p><ul><li>Group 1: Tables that are created as building blocks along the way to build something else. The are essentially a by-product of the data modelling process, and (in general) shouldn’t be used elsewhere.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MMLlJ5oCFDep0tZR.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><ul><li>Group 2: Tables that are not intended to be used for analytics, but are a core piece of the data model, and are used by many downstream tables</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Be7a0aQjkxfSEeq5.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><ul><li>Group 3: Tables that are outputs expressly designed for consumption in analytics tasks. These are generally at the end of the dependency graph</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OtGhznpUA-_v8rgo.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>After considering a few approaches, we decided that using the schema to clearly differentiate between the three groups made the most sense:</p><ul><li>Ephemeral: A schema for all tables in group 1. These can essentially be ignored by everything apart from the Dataform pipeline.</li><li>Core: A schema for all tables in group 2. These will form the backbone of our data modelling, but are generally not intended for consumption by BI tools, analytics etc.</li><li>Reporting: Group 3 tables. These are the primary outputs of the data modelling process. Anyone wanting to do analytics in our data will probably find what they&#39;re looking for in this schema.</li></ul><p>One important part of this structure is that datasets in the core schema should never depend on datasets in the reporting schema. This simple rule enforces structure on our data model, and that structure ultimately helps to minimise the length of our dependency graph and reduce the likelihood of getting tripped up by circular dependencies.</p><h3>Rolling out the change</h3><p>Now that we’d agreed on a naming convention, it was time for me to refactor our data modelling code. Because we use Dataform, all of our data modelling code is in one place — in fact it’s all in one GitHub repository — so I only have to make updates in one place.</p><p>The first step of course was to create a new branch. Because I was making such a big change, it was important that I could isolate my changes from our production data modelling code, so that people could continue using our data for analytics whilst I was in progress.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/0*57CWyvGHfjgoqVbd.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>Given the ephemeral schema is the one that most users of our data won&#39;t see, I decided this was a good choice for the default schema. If someone creates a new model, they&#39;ll need to actively assign a different schema if they think it should be core, reporting etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/474/0*MWONZgoEGQy8o2bZ.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>I then worked my way down the set of files and folders in our project, swapping out filenames (which are used as the table names in Dataform) and schema names (set in the config {} block) for their replacement in the new convention. Each time you do this, there&#39;s a good chance that you&#39;ll break a link in your dependency graph. Fortunately, Dataform immediately alerts you to the issue, and which file you can find the problem in, so you can go and correct it. More often than not, I needed to update a ${ref(&quot;table_name&quot;)} to use the new name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/676/0*1z3rERHAl87jyHp2.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>After roughly two hours, I’d been through all of the files in our project (over 100), renamed everything to meet the new naming convention, and resolved all compilation errors.</p><p>The final remaining step was to kick off a deployment of our entire graph — again in a <a href="https://docs.dataform.co/dataform-web/scheduling/environments">development environment</a> so that our production data isn’t affected — to ensure there were no failures:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*oCaJjEmox2s1QtQ4.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>With that, I created a PR and sent it to one of my colleagues for review.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/779/0*2vmI11hSOFSk4dbi.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><h3>Summary</h3><p>It’s early days for this new structure, but so far we’re happy with the results. It’s much clearer what the contents of a dataset is, which makes it much easier for users of the data, and also a lot easier for developers to understand how to contribute.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/309/0*xWaph0p83BZ7i4ew.png" /><figcaption>Source: <a href="https://dataform.co/?utm_medium=medium&amp;utm_campaign=updating-warehouse-naming-convention">Dataform</a></figcaption></figure><p>I don’t imagine that this naming convention will work for everyone. If your warehouse is simpler than ours, this may all seem a little over the top. On the other hand, if you’re a significantly larger and more complex organization than Dataform, you may need more layers of <em>transformation stages</em>, or may need an ephemeral schema for each domain.</p><p>In fact, I wouldn’t be surprised if we need to go through this process again in a year’s time. But I’m OK with that! Because all of our code is version controlled, managed in one place, and Dataform automatically checks for consistency across the entire dependency graph, making the update was painless.</p><p>Have you been through a similar process at your organization? Where did you end up? Have you got suggestions for a better naming convention? If so, I’d love to hear from you! Come and join our <a href="https://dataform.co/slack">community of data practitioners in Slack</a> and share your thoughts and ideas.</p><p><em>Originally published at </em><a href="https://dataform.co/blog/updating-warehouse-naming-convention"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=20c95535e8f3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/switching-to-a-better-data-warehouse-naming-convention-20c95535e8f3">Switching to a better data warehouse naming convention</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introducing the Modern Analytics Academy]]></title>
            <link>https://medium.com/dataform/introducing-the-modern-analytics-academy-b9c5a638fc40?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/b9c5a638fc40</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-transformation]]></category>
            <category><![CDATA[data-quality]]></category>
            <dc:creator><![CDATA[Josie Hall]]></dc:creator>
            <pubDate>Thu, 01 Oct 2020 14:38:14 GMT</pubDate>
            <atom:updated>2020-10-01T14:36:27.930Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F3At5ytZfDhVT_y7B0iR4g.png" /></figure><p>With data being such an important piece of organisational strategy, the data team is a key stakeholder at the decision-making table. But satisfying the entire organization’s demand for data-driven strategy and decision making, alongside the increasing scale and complexity of data collected and available for analysis, puts data teams in a difficult position.</p><p>The situation data teams find themselves in calls for a new way of working: we call this Modern Analytics.</p><p>Modern Analytics uses the technologies and workflows of software engineering to deliver the analytical outcomes demanded by the modern organisation. Modern analytics is:</p><ul><li>Scaleable</li><li>Agile</li><li>Collaborative</li></ul><p>Over the last two years we’ve learnt a lot from working so closely with the Dataform community. There are some clear patterns to how the most effective data teams work, and we wanted to share what we’ve learnt with a wider audience. We’ve worked with some of the pioneers of Modern Analytics to create the <a href="https://dataform.co/academy?utm_source=medium&amp;utm_campaign=ma">Modern Analytics Academy</a>.</p><p>Our aim is to help you get started with Modern Analytics principles as quickly and smoothly as possible, so that you can ensure you’re delivering maximum value to your organisation.</p><p>We’ve started with some of the fundamentals, and plan over the next few weeks and months to cover more advanced topics. We’re also running a series of webinars alongside the academy to dig a bit deeper into some of the topics. We hope you find it useful!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b9c5a638fc40" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/introducing-the-modern-analytics-academy-b9c5a638fc40">Introducing the Modern Analytics Academy</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cut data warehouse costs with run caching]]></title>
            <link>https://medium.com/dataform/cut-data-warehouse-costs-with-run-caching-71b29efea408?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/71b29efea408</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[pipeline]]></category>
            <category><![CDATA[etl]]></category>
            <dc:creator><![CDATA[Ben Birt]]></dc:creator>
            <pubDate>Thu, 24 Sep 2020 11:21:56 GMT</pubDate>
            <atom:updated>2020-09-24T11:21:56.767Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5dLVMrfQgzN9zCJX.png" /></figure><p>As we’ve mentioned before, one of the core design goals of Dataform is to make project compilation <strong>hermetic</strong>. The idea is to ensure that your final ELT pipeline is as reproducible as possible given the same input (your project code), with a few tightly-controlled exceptions (like support for ‘incremental’ tables).</p><p>Being able to reason this way about the code in Dataform pipelines gives us the opportunity to build some cool features into the Dataform framework. An example is our <a href="https://docs.dataform.co/guides/configuration#enable-run-caching-to-cut-warehouse-costs">“run caching” feature</a>.</p><h3>Don’t waste time and money re-computing the same data</h3><p>Most analytics pipelines are executed periodically as part of some schedule. Generally, these schedules are configured to run as often as necessary to keep the final data as up-to-date as the business requires.</p><p>Unfortunately, this can lead to a waste of resources. Consider a pipeline that is executed once an hour. If its input data doesn’t change between one execution and the next, then the next execution will result in no changes to the output data, but it’ll still cost time and money to run.</p><p>Instead, we believe that the pipeline should automatically detect if it’s not going to change the output data — and if so, then the affected stage(s) should be skipped, saving those resources.</p><p>We’ve built this feature into Dataform.</p><h3>Run caching in Dataform</h3><p><a href="https://app.dataform.co/#/5682186490478592/overview?utm_source=medium&amp;utm_campaign=run_caching"><strong>Try out an example project with run caching here!</strong></a><strong> (click “incidents_by_date.sqlx” on the left-hand side)</strong></p><p>You can turn run caching on in your project with a few small changes which are described <a href="https://docs.dataform.co/guides/configuration#enable-run-caching-to-cut-warehouse-costs">here</a>. Once enabled, run caching skips re-execution of code which cannot result in a change to output data.</p><p>For example, consider the following SQLX file, which configures Dataform to publish a table age_count containing the transformed results of a query reading a people relation:</p><pre>config { name: &quot;age_count&quot;, type: &quot;table&quot; }</pre><pre>select age, count(1) from ${ref(&quot;people&quot;)} group by age</pre><p>Dataform only needs to (re-)publish this table if any of the following conditions are true:</p><ul><li>The output table age_count doesn&#39;t exist</li><li>The output table age_count has changed since the last time this table was published (i.e. it was modified by something other than Dataform itself)</li><li>The query has changed since the last time the age_count table was published</li><li>The input table people has changed since the last time the age_count table was published (or, if people is a view, then if any of the input(s) to people have changed)</li></ul><p>Dataform uses these rules to decide whether or not to publish the table. If all of the tests fail, i.e. re-publishing the table would result in no change to the output table, then this action is skipped.</p><h3>Building in intelligence so you don’t have to</h3><p>At Dataform we believe that you shouldn’t have to manage the infrastructure involved in running analytics workloads.</p><p>This philosophy is what drives us to build out features like run caching, which automatically help to manage and operationalize analytics workloads, so that you don’t have to. All you need to do is define your business-logic transformations, and we’ll handle the rest.</p><p>If you’d like to learn more, the Dataform framework documentation is <a href="https://docs.dataform.co/?utm_source=medium&amp;utm_campaign=run_caching">here</a>. Join us on <a href="http://dataform-users.slack.com/">Slack</a> and let us know what you think!</p><p><em>Originally published at </em><a href="https://dataform.co/blog/run-caching?utm_source=medium&amp;utm_campaign=run_caching"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=71b29efea408" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/cut-data-warehouse-costs-with-run-caching-71b29efea408">Cut data warehouse costs with run caching</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What do Analytics Engineers Actually Do?]]></title>
            <link>https://medium.com/dataform/what-do-analytics-engineers-actually-do-dc05ea18c400?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/dc05ea18c400</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <dc:creator><![CDATA[Josie Hall]]></dc:creator>
            <pubDate>Mon, 03 Aug 2020 15:46:36 GMT</pubDate>
            <atom:updated>2020-08-04T10:28:38.032Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2R3_3w7kz-wnE1UDjyasDg.png" /></figure><p>The move towards cloud data warehouses, the evolution of self service BI tools, and introduction of data ingestion tools has led to some big shifts in data tooling. These shifts have meant that the roles and responsibilities that existed in traditional data teams are also changing. Enter the Analytics Engineer.</p><p>At <a href="https://dataform.co/?utm_source=medium&amp;utm_campaign=ae">Dataform</a> we know that a lot of our users are doing the work of analytics engineers but aren’t necessarily calling themselves this yet. So what does it actually mean to be an analytics engineer? What skills do you need? And how does the role differ from traditional data roles? We sat down with several Analytics Engineers to discuss what the role means to them.</p><h3>Where does an Analytics Engineer sit in the data team?</h3><p>Before we begin, it’s useful to define what roles exist in a traditional data team and where an Analytics Engineer sits amongst these:</p><ul><li><strong>Data Engineers:</strong> manage core data infrastructure, ensuring data is available and accessible across the organisation.</li><li><strong>Data Analysts:</strong> partner with business stakeholders to answer questions with data, build dashboards and reporting, and carry out exploratory analysis.</li><li><strong>Data Scientists:</strong> use statistics and machine learning to extract value from data: solving optimization problems, building prediction models, running A/B experiments and more.</li></ul><p>The shift to an <a href="https://dataform.co/blog/etl-vs-elt">ELT</a> approach means that data now lands in the warehouse before it has been transformed. This presents an opportunity for super technical analysts who both know the business really well <strong>and</strong> have the technical skills to model the raw data into clean, well defined datasets to step up (analytics engineers). If you wanted this kind of analyst when using the ETL paradigm they would need to be a software engineer and a data analyst — and that would be a lot harder to find. Finding a technical analyst is a lot more achievable.</p><p>The Analytics Engineer acts as the bridge between data engineers and data analysts. It is their job to build well tested, up to date and documented datasets that the rest of the company can use to answer their own questions. They are technical enough to easily be able to apply software engineering best practices like version control and CI/CD but also need to be able to communicate effectively with stakeholders to teach them about these things. Saira Barles, Analytics Engineer @ Hubspot describes the differences between the 3 roles using the analogy of a cupboard:</p><blockquote>“Data engineers build the cupboard, they gather together the wood and the tools and put it together. The Analytics Engineers open the cupboard and start putting in the plates, mugs, bowls, and arrange them in a certain order. This could be arranging them into particular colours, shapes or sizes. Data analysts then go into the cupboard and they know where everything lives as it is arranged nicely. They can then grab the small blue mug they were looking for and go make a cup of tea!”</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Ao9CR-qWymBDP4FS.jpg" /></figure><h3>What are the responsibilities of an analytics engineer?</h3><p>Although the exact responsibilities of the role differ from company to company, fundamentally, it is the job of the Analytics Engineer to <a href="https://dataform.co/blog/great-data-teams-build-products">build data products</a> that enable stakeholders to do their jobs effectively and answer their own questions. Mitchell Silverman, Analytics Engineer at Spotify breaks down how he sees his responsibilities:</p><blockquote>“50–60% of the job is building out datasets and setting up monitoring, testing and automation around those. 20% is more of a consultant role, so how to enable analysts and data scientists to do their jobs better e.g. Cleaning up scripts/queries they’ve written and showing them how they can improve. The final 15–20% is miscellaneous. Analytics engineers come from all different backgrounds so it kind of depends where your skill set lies”</blockquote><p>Sometimes analytics engineers are responsible for ingesting data into the warehouse, however more often it is the data engineer who does this (especially at larger companies). The analytics engineer then picks it up from there and makes the data usable. They are in charge of modelling raw data into datasets that enable analytics across the company. These datasets act as a centralised source of truth.</p><p>A big part of the job is making sure that these datasets are maintained. The datasets need to be up to date, well tested and documented . The Analytics Engineer should be the first person to be notified if something in the pipeline breaks so they can fix it and should be empowered to own that whole workflow to ensure the data models are running reliably.</p><p>They are also responsible for enabling data analysts and data scientists to be more effective. Whether that’s sharing tips for writing better SQL, updating a dataset to contain a new metric or dimension, or coaching them on how to adopt software engineering best practices.</p><h3>How do you become an Analytics Engineer?</h3><p>We spoke to various different Analytics Engineers from a variety of backgrounds and found some common skills and experience that they considered to be important:</p><ul><li>Comfortable adopting software engineering best practices like version control and using Git. Teaching others how to adopt these best practices.</li><li>Be a SQL whiz! They know how to write SQL which is easy to understand, simple to troubleshoot and that is highly performant.</li><li>Knowledge of another programming language e.g. Python is a plus.</li><li>Experience working as part of a data team; preferably as either a data analyst or data engineer.</li><li>Able to build and maintain multi-functional relationships with various teams across the business.</li></ul><p>People can go directly into analytics engineering from a variety of backgrounds. Mitchell from Spotify says that on his team he typically works with people who <em>“used to be analysts or data scientists who for whatever reason ended up getting more into software engineering and doing some backend work, and over time have built up the skill set.”</em></p><p>Kadu, Analytics Engineer at Liv Up, describes most people who go into analytics engineering as coming from a few different backgrounds. Either they come from a software engineering background who focus more on data pipelines — performance and reliability above all — or they come from a data science background and focus more on getting people the correct data they need, even if pipelines end up breaking sometimes.</p><p>One common thread was that everyone we spoke to agreed that having a healthy mix of technical and stakeholder management skills was crucial. Saira at Hubspot describes why she loves the role <em>“I love data. I love SQL. I love transforming data and reducing 20 lines of code into 5! But I also want to solve problems for the various stakeholders at my company and make their lives easier”</em></p><h3>What tools do Analytics Engineers use?</h3><p>This varied slightly from company to company, but all analytics engineers we spoke to used:</p><ul><li>A cloud data warehouse for storing data (<a href="https://www.snowflake.com/">Snowflake</a>, <a href="https://cloud.google.com/bigquery">Bigquery</a>, <a href="https://aws.amazon.com/redshift/">Redshift</a>)</li><li><a href="https://fivetran.com/">Fivetran</a> or <a href="https://www.stitchdata.com/">Stitch</a> for data extraction and loading</li><li><a href="https://dataform.co/?utm_source=medium&amp;utm_campaign=ae">Dataform</a> or another data modelling tool for data transformation</li><li>A modern BI tool (<a href="https://looker.com/">Looker,</a> <a href="https://mode.com/">Mode</a> or <a href="https://chartio.com/">Chartio</a>)</li></ul><p>Traditionally the “first data hires” at a company were probably a data engineer and a data analyst. The data engineer would be tasked with setting up infrastructure, getting data flowing into the warehouse and ready for analytics. The data analyst would then build reports and charts on this data that they could share with the business.</p><p>The introduction of the tools mentioned above means that the “first data hire” can now own that whole workflow. Tools like Fivetran and Stitch allow people who may not have the skillset to do complex data ingestion tasks to call themselves “data engineers”. Looker has made it easy for stakeholders to answer their own questions and create their own charts.</p><p>Traditionally analysts would build a pipeline but wouldn’t know if it had broken until an end user spotted an error in their dashboards and reported it. Nowadays that isn’t acceptable and data teams need to know immediately if something breaks. Mitchell at Spotify says that the biggest value-add that data transformation tools like Dataform bring is <em>“monitoring and testing”.</em></p><p>Dataform is championing the analytics engineer’s mission. Anyone who is comfortable writing SQL can own the transformation piece of the pipeline. Kadu at Livup says that they have <em>“15 analysts all managing their own ELT pipelines using Dataform”.</em></p><h3>How will the role evolve?</h3><p>Nobody really knows what the future of this role is. Most companies don’t have a big analytics engineering department and there are no Head of Analytics Engineering roles (yet), so there’s no set career path. Opportunities to move sideways into Data Engineering, Analytics or Data Science, possibly in leadership roles, may present themselves. A path into Product Management could also be possible.</p><p>In order for Analytics Engineering to become a recognized field the first step is for more companies to acknowledge it. Mitchell says that at Spotify there are <em>“only 10–15 Analytics Engineers amongst 100s of data analysts and scientists”</em>. It is their job to show to the rest of the company the value they bring and why Spotify should invest in setting up a talent pipeline for this field.</p><p>Kadu from Liv Up explains that in Brazil most companies expect Data Engineers to perform both roles, even in larger companies. <em>“Many people are being called Data Engineers when they are actually doing the job of Analytics Engineers”.</em> Each role requires a very different skillset.</p><p>Therefore, in order to build a cohesive cupboard with your plates and mugs arranged correctly, it’s important that companies understand the differences between the two roles and hire accordingly. Despite this, more job postings for analytics engineers are being posted everyday, communities around this role are rapidly growing, and tools which help their workflows are evolving faster than ever before.</p><p>It’s an exciting time to be an Analytics Engineer!</p><p><em>Originally published at </em><a href="https://dataform.co/blog/what-do-analytics-engineers-do"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dc05ea18c400" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/what-do-analytics-engineers-actually-do-dc05ea18c400">What do Analytics Engineers Actually Do?</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using environments with SQL pipelines]]></title>
            <link>https://medium.com/dataform/using-environments-with-sql-pipelines-767c65b3a4e4?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/767c65b3a4e4</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[development-environment]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <dc:creator><![CDATA[Josie Hall]]></dc:creator>
            <pubDate>Thu, 30 Jul 2020 09:24:15 GMT</pubDate>
            <atom:updated>2020-07-30T09:24:06.804Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4YAHQrtSWt57VbzWIZi1kA.png" /></figure><p>A recommended practice for any software development process is the use of multiple deployment environments. Using multiple environments ensures that your code is well tested before it is deployed to production and made available to the end user.</p><p>An example setup would have three main environments:</p><ul><li>Development: The development environment is your local computer. Here, is where you deploy code and test any newly implemented features. Any bugs found are dealt with before re-deploying for further testing.</li><li>Staging: The staging environment is as similar to the production environment as it can be. You’ll have all of the code on a server this time instead of a local machine. Any configuration changes or major version updates can be tested here.</li><li>Production: Every time you talk about making your project live, this is the environment you are talking about. The production environment is where users access the final code after all of the updates and testing.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/437/0*XxdmQKSitH0g7Xeb.png" /></figure><p>At <a href="https://dataform.co/">Dataform</a>, we believe that analytics should follow software engineering best practices and therefore SQL based pipelines should be subject to the same rigorous testing process.</p><h3>Environments in Dataform</h3><p>By default, Dataform runs all of your project code from your project’s master Git branch. Configuring environments allows you to control this behaviour, enabling you to run multiple different versions of your project code and run schedules against them.</p><p>An environment is effectively a wrapper around a version of your project code. Once you have defined an environment, and added some schedules to that environment, Dataform runs all of those schedules using the environment’s version of the project code.</p><h4>Using separate databases for development and production data</h4><p>Some teams may not be at the stage where they require a staging environment, but still would like to keep development and production data separated. A clean and simple way to do this is to use a different database for each environment. This can be done using a &lt;configOverride&gt; in the production environment.</p><p>In the example below:</p><ul><li>any code deployed during development will use &lt;defaultDatabase&gt; from the dataform.json file</li><li>schedules are defined in the production environment, and so use the &lt;defaultDatabase&gt;from that environment&#39;s &lt;configOverride&gt;</li><li>the production environment specifies the master Git branch, so all of its schedules will run using the latest version of the code</li></ul><p>dataform.json:</p><p>environments.json:</p><p>Note that the &lt;defaultDatabase&gt; setting is only supported for BigQuery and Snowflake. For other warehouses, we recommend overriding schema suffixes (read the docs <a href="https://docs.dataform.co/dataform-web/scheduling/environments#per-environment-schema-suffixes">here</a>).</p><h4>Managing a production release process using environments</h4><p>For more advanced Dataform projects a common use-case for environments is to run a staged release process. All changes to project code go into a staging environment which is intentionally kept separate from production. Once the code in staging has been verified to be sufficiently stable, that version of the code is then promoted to the production environment.</p><p>Read more about how to configure environments in our <a href="https://docs.dataform.co/dataform-web/scheduling/environments">docs</a>.</p><p>As always if you have any feedback on features please message us on <a href="https://dataform.co/slack">Slack</a> or email us at team@dataform.co</p><p><em>Originally published at </em><a href="https://dataform.co/blog/environments"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=767c65b3a4e4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/using-environments-with-sql-pipelines-767c65b3a4e4">Using environments with SQL pipelines</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Kaleva Media used Dataform to leverage the power of Snowflake and scale their processes]]></title>
            <link>https://medium.com/dataform/how-kaleva-media-used-dataform-to-leverage-the-power-of-snowflake-and-scale-their-processes-d2cad1a65f74?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/d2cad1a65f74</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[dataform]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Josie Hall]]></dc:creator>
            <pubDate>Thu, 30 Jul 2020 08:57:21 GMT</pubDate>
            <atom:updated>2020-07-30T08:57:10.027Z</atom:updated>
            <content:encoded><![CDATA[<h3>The situation</h3><p><a href="https://www.kalevamedia.fi/en/">Kaleva</a> is a Finnish online &amp; print media company. They started as a local newspaper covering northern Finland but soon wanted to expand their offering. They started buying smaller newspapers and now own multiple newspapers across northern Finland including digital news sites &amp; online marketing platforms.</p><p>The company is over 120 years old and has over 800 employees! Kaleva Media therefore has a huge amount of historical data. They work with clickstream data to measure events on their website, financial data, CRM data and marketing data .</p><p>A few years ago, Kaleva made a conscious decision to become more data driven. They hired a data team whose vision was to create a self-serve BI culture within Kaleva and they started designing their data architecture, essentially from scratch.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/0*c0YBIjNLEXVIxv0x.png" /></figure><p>The first step was to centralise all their data in a warehouse. For this, they chose Fivetran for data ingestion and Snowflake as their data warehouse. The next step was modelling and making sense of all their raw data. They tried a few products but either found that the learning curve was too steep or they required a lot of maintenance.</p><p>As they were a small team supporting a large company they knew they wanted a tool which could help accelerate the powers of the data team. They wanted a tool which:</p><ul><li>Helped the team be more productive, minimising time spent on maintaining infrastructure and menial tasks.</li><li>Helped the team to collaborate and work in a unified way.</li><li>Allowed them to leverage the power of their Snowflake warehouse.</li></ul><h4>Improving productivity of the data team</h4><blockquote><strong><em>“We’ve never really compromised on data quality but assertions do give us more time. They allow us to do more with the same number of people.”</em></strong></blockquote><p>Kaleva works with huge amounts of historical data, which is great for analysis but also means setting up pipelines is complex and time consuming. The data team never wanted to compromise on data quality which meant they would often spend hours checking back on pipelines to see if they were failing and trying to debug them if they were.</p><blockquote><strong><em>“If you look at Harvard Business Review, one thing they raise is about measuring the quality of your data and making it visible. That’s definitely something we can say we can do better with Dataform than without”</em></strong></blockquote><p>With <a href="https://dataform.co/">Dataform</a>, Kaleva was able to use assertions to make sure their pipelines ran smoothly. Assertions run as part of a schedule and check the data doesn’t contain any errors — alerts automatically notify the team if any errors are detected. Once the assertion is added, they no longer have to worry about maintaining the dataset. This gives the team more time to spend on value-add work, like analysing data.</p><p>Being able to automate data quality tests has increased the output of the data team. Nowadays they have set up a process which means that no dataset can be published unless it has an assertion. In just 6 months of use, Kaleva Media has created 243 data sets that have 72 assertions and the gap is closing fast.</p><h4>Collaboration that scales</h4><blockquote><strong><em>“It’s been quite delightful having all four of us working intensely using the same processes”</em></strong></blockquote><p>The data team at Kaleva is made up of four people of different backgrounds and expertise with one distinct shared knowhow: SQL. Each member of the team had different ways of working and slightly different development cultures. This sometimes made collaboration hard. They decided to adopt Dataform as it helped enforce best practises for data-ops that helped lift the teams productivity even more.</p><p><strong><em>“Using the built in version control has made everything more open and visible. It’s getting people to work in the same way with data.”</em></strong></p><p>With Dataform, Kaleva Media now has one platform where the whole team can collaborate, following a single unified process. For example, all updates to the data pipeline must include documentation, ensuring the project remains easy to understand for the entire team.</p><p>Now they have a fixed way of working, onboarding new members of the team is far easier. The diagram linking all of the transformations (the dependency graph) means new team members can easily get up to speed with where all the tables live and how they relate to each other.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*R4zWI4864F8vAbpk.png" /><figcaption>Kaleva’s dependency graph</figcaption></figure><h4>Leveraging the power of Snowflake</h4><p>Kaleva chose Snowflake as the place where they wanted to centralise all their data. Snowflake is a SQL based warehouse with a unique architecture that is specifically designed for the cloud. Compute usage is billed per second, so you only pay for what you use. Kaleva needed a data modelling solution that could work well with their Snowflake warehouse.</p><p>Dataform sits on top of Snowflake and allows Kaleva to manage all data processes happening in their warehouse. Using Dataform’s powerful features, Kaleva can keep their warehouse up to date and all their code in Snowflake version controlled. All transformations happen in the warehouse itself, allowing Kaleva to harness the power of their Snowflake warehouse and do the heavy lifting.</p><h3>Conclusion</h3><p>Kaleva needed a tool which could help them become more data driven, transitioning from a printing press culture to the digital age. Dataform has allowed Kaleva to improve the output of their data team and collaborate more effectively as a team. After initially setting up their Snowflake warehouse in November 2019, they now manage 243 (and growing) separate datasets: all documented, and automatically tested using assertions.</p><p>With the help of the Dataform customer success team, the whole team at Kaleva Media were able to get up and running with Dataform in a matter of weeks.</p><blockquote><strong><em>“The way the Dataform team respond so quickly to feedback and feature requests means that their customers stay faithful to them.”</em></strong></blockquote><p>Now that their data models are built on solid foundations, the team can spend less time debugging failing pipelines, and more time delivering valuable insights to the business.</p><p><em>Originally published at </em><a href="https://dataform.co/blog/kaleva-case-study"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d2cad1a65f74" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/how-kaleva-media-used-dataform-to-leverage-the-power-of-snowflake-and-scale-their-processes-d2cad1a65f74">How Kaleva Media used Dataform to leverage the power of Snowflake and scale their processes</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Converting Looker PDTs to the Dataform framework]]></title>
            <link>https://medium.com/dataform/converting-looker-pdts-to-the-dataform-framework-1475287bfeb5?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/1475287bfeb5</guid>
            <category><![CDATA[dataform]]></category>
            <category><![CDATA[sessions]]></category>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Daniel Lee]]></dc:creator>
            <pubDate>Thu, 30 Jul 2020 08:45:58 GMT</pubDate>
            <atom:updated>2024-03-12T08:54:23.371Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Dan is the Founder of Outlier, </em><strong><em>your fractional data team </em></strong><em>| </em><a href="https://weareoutlier.com/"><em>weareoutlier.com</em></a></p><p>Over the last few years Looker, probably more than any other BI tool, has changed the way that analyst teams approach reporting and business intelligence. Looker encourages you to describe the relationships between your data (using <em>explores</em>) and allow business users to then freely explore data themselves: the self-serve analytics we’ve been talking about for years.</p><p>On top of this, Looker forces analysts to define those relationships with LookML: a simple markup language for describing dimensions, metrics and relationships between datasets. Instead of using drag-and-drop GUIs, the BI environment is defined as code (using LookML). Analyst teams can then version control their work, and focus on <a href="https://dataform.co/blog/great-data-teams-build-products">building a product</a> as opposed to responding to ad-hoc requests.</p><p>The final piece of the transformational puzzle that Looker has brought to the world of data are PDTs (persistent derived tables). PDTs can be used to quickly and easily set up SQL-based transformations on your raw data. This gives analysts the ability to create their own datasets, removing the dependency on other teams (for example data engineers).</p><p>These three changes — enabling self-serve analytics, collaboration through version control, and the reduced dependence on other teams — mean that teams using Looker significantly outperform teams working with BI tools that lack these features.</p><p>Whilst this toolkit that Looker offers data teams an initial step-change to their productivity, <a href="https://dataform.co/blog/echo-case-study">many growing teams</a> find that PDTs are no longer fit for purpose.</p><ul><li>Managing performance — both in terms of speed and cost — is difficult, with no visibility into which PDTs are most resource intensive.</li><li>When there are data quality issues created by PDTs, it’s hard to find the root cause.</li><li>Tables created by PDTs are poorly optimised for outside of Looker (with obtuse names like tmp.LB_faa_e_flights_pdt), making data discoverability for teams that don&#39;t access data through Looker challenging.</li><li>Visibility into which PDTs already exist and how they’re related is lacking, making it difficult for new team members to get up to speed and start contributing</li></ul><p>To address the challenges described above (and more), we built <a href="https://dataform.co/?utm_medium=organic&amp;utm_source=dataform_blog&amp;utm_campaign=converting-looker-pdts-to-dataform">Dataform</a>: an open source framework specifically designed for managing data modelling and transformation with SQL. We share a similar philosophy to Looker: namely that data &amp; analytics teams have lessons to learn from software engineering on how to collaboratively build complex products. We also believe, similarly to Looker, that the most natural environment for analysts to work is a web-based one, so we also offer a fully-integrated web based IDE for managing data transformations with the Dataform framework.</p><h3>Sessionization with Dataform and Looker</h3><p>For the rest of this post, I’ll walk through the steps of moving a sessionization model from a Looker PDT to the Dataform framework. By the end, we’ll have a Dataform project running the sessionization pipeline on a regular schedule, and a Looker <em>view</em> pointing to the output of the pipeline.</p><p>I won’t go into too much detail on sessionization (there’s a good post about it <a href="https://mode.com/blog/finding-user-sessions-sql">here</a>). But, very roughly, we’re taking a large table of user events (a user performing a certain action at a certain time) and clustering them into groups of events ( <em>sessions</em>) by a particular user that all happen in one continuous period.</p><p>Here’s the sessionization logic written as a PDT:</p><p><a href="https://gist.github.com/dwl285/cca564669e52c8495e112e647fa2f35f#file-sessions-view-lookml">sessions.view.lookml</a></p><pre>view: sessions {<br>  derived_table: {<br>    sql_create:<br>with events_cleaned as (<br>-- filter out invalid / old events and remove duplicates<br>select distinct<br>  timestamp,<br>  user_id,<br>  name as event_name,<br>  id as event_id<br>from<br>  source.events_raw<br>where<br>  event_name not in (&#39;test_event&#39;, &#39;clicked_logout_button_old&#39;)<br>),</pre><pre>session_starts as (<br>-- label the event that starts the session<br>select<br>  *,<br>  coalesce(<br>    (<br>      timestamp_diff(timestamp,<br>                     lag(timestamp) over (partition by user_id order by timestamp asc),<br>                     second) &gt;= 30*60, -- 30 minute session timeout<br>    true<br>  ) as session_start_event<br>from<br>  events_cleaned<br>),</pre><pre>session_index as (<br>-- add a session_index (users first session = 1, users second session = 2 etc)<br>select<br>  *,<br>  sum(if (session_start_event, 1, 0)) over (<br>    partition by user_id<br>    order by<br>      timestamp asc<br>  ) as session_index<br>from<br>  session_starts<br>),</pre><pre>session_id as (<br>-- add a unique session_id to each session<br>select<br>  *,<br>  farm_fingerprint(<br>    concat(<br>      cast(session_index as string),<br>      &quot;|&quot;,<br>      cast(user_id as string)<br>    )<br>  ) as session_id<br>from<br>  session_index<br>)</pre><pre>select<br>  session_id,<br>  user_id,<br>  session_index,<br>  min(timestamp) as session_start_timestamp,<br>  max(timestamp) as session_end_timestamp,<br>  count(distinct event_id) as events_in_session<br>from<br>  session_id<br>group by<br>  session_id, session_index<br>    ;;<br>  }</pre><pre>  dimension: session_id {<br>    type: string<br>    sql: ${TABLE}.session_id ;;<br>  }<br>  <br>  dimension: user_id {<br>    type: string<br>    sql: ${TABLE}.user_id ;;<br>  }</pre><pre>  dimension: session_index {<br>    type: number<br>    sql: ${TABLE}.session_index ;;<br>  }<br>  <br>  dimension: events_in_session {<br>    type: number<br>    sql: ${TABLE}.events_in_session ;;<br>  }</pre><pre>  dimension_group: session_start_timestamp {<br>    type: time<br>    sql: ${TABLE}.session_start_timestamp ;;<br>    timeframes: [<br>      date,<br>      time,<br>      raw<br>    ]<br>  }<br>  <br>  dimension_group: session_end_timestamp {<br>    type: time<br>    sql: ${TABLE}.session_end_timestamp ;;<br>    timeframes: [<br>      date,<br>      time,<br>      raw<br>    ]<br>  }</pre><pre>  measure: sessions {<br>    type: count<br>  }<br>  <br>  measure: users {<br>    type: count_distinct<br>    sql: ${user_id} ;;<br>  }<br>  <br>  measure: events {<br>    type: sum<br>    sql: ${events_in_session} ;;<br>  }<br>}</pre><h3>Step 1: Splitting sessionization into 3 steps</h3><p>The sessionization logic is relatively complex: this example has ~70 lines of SQL, but in many cases it can amount to hundreds of lines of code. We’re also doing several different things in one go:</p><ul><li>Cleaning up our lists of events</li><li>Finding the session_id and session_index for each event</li><li>Aggregating the list of events into a sessions table</li></ul><p>Long SQL queries doing many things at once are hard to understand and hard to debug. We’ve designed the Dataform framework to make it easy to split your SQL into logically separate pieces that can be chained together. So, let’s split the sessionization SQL into those three steps: cleaning, adding session details, and aggregating.</p><p>events_cleaned.sqlx:</p><pre>config {<br> type: &quot;view&quot;,<br> schemaSuffix: &quot;prelim&quot;<br>}<br> <br>select distinct<br> timestamp,<br> user_id,<br> name as event_name,<br> id as event_id<br>from<br> source.events_raw<br>where<br> name not in (&#39;test_event&#39;, &#39;clicked_logout_button_old&#39;)</pre><p>sessionized_events.sqlx:</p><pre>config {<br> type: &quot;view&quot;,<br> schemaSuffix: &quot;prelim&quot;<br>}<br> <br>with session_starts as (<br>select<br> *,<br> coalesce(<br>     timestamp_diff(timestamp,<br>                    lag(timestamp) over (partition by user_id order by timestamp asc),<br>                    second) &gt;= 30*60, -- 30 minute session timeout<br>   true<br> ) as session_start_event<br>from<br> ${ref(&quot;events_cleaned&quot;)}<br>),<br> <br>session_index as (<br>-- add a session_index (users first session = 1, users second session = 2 etc)<br>select<br> *,<br> sum(if (session_start_event, 1, 0)) over (<br>   partition by user_id<br>   order by<br>     timestamp asc<br> ) as session_index<br>from<br> session_starts<br>)<br> <br>-- add a unique session_id to each session<br>select<br> *,<br> farm_fingerprint(<br>   concat(<br>     cast(session_index as string),<br>     &quot;|&quot;,<br>     cast(user_id as string)<br>   )<br> ) as session_id<br>from<br> session_index</pre><p>sessions.sqlx:</p><pre>config {<br> type: &quot;table&quot;,<br>}<br><br>select<br> session_id,<br> user_id,<br> session_index,<br> min(timestamp) as session_start_timestamp,<br> max(timestamp) as session_end_timestamp,<br> count(distinct event_id) as events_in_session<br>from<br> ${ref(&quot;sessionized_events&quot;)}<br>group by<br> session_id, session_index</pre><p>By breaking the SQL up, we’ve achieved a few things:</p><ul><li>We’ve created datasets for each step in the process. This will help with debugging, and also allow us to test each step (more on that later)</li><li>It’ll be easier for new team members to understand and contribute to the code</li><li>We can give the output of each step specific table names, and use schemas to separate intermediate tables (dataform_prelim.table_name) from our final tables (dataform.table_name).</li></ul><p>After splitting things up, the ref() function (part of the Dataform framework) allows you to express dependencies between transformations. It also allows Dataform to generate a dependency graph, helping you visualise your data modelling layer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VxLj3uzY508C1RGZ.png" /></figure><h3>Step 2: Improve performance with incremental table builds</h3><p>The PDT is set up to rebuild the entire sessions table from scratch each time it is updated — this means rescanning the entire events_raw table each time. For small companies the event table can be small, so each refresh can be relatively quick. But as organizations grow, the events tables can become very large. Rescanning the entire table can get really slow (and, if you&#39;re using BigQuery or Snowflake, expensive too).</p><p>Given that we know event data doesn’t change retroactively, we’d rather only read the data that’s changed since our last refresh. Once we’ve processed the new data and generated new records, we can append these new records to the existing table. By reducing the amount of data processed, the processing time (and processing cost) can be drastically reduced.</p><p>The Dataform framework supports this optimised table update methodology with <em>incremental</em> tables. The changes to make events_cleaned incremental are shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/0*xAhQPT1ICxlmkfjo.png" /></figure><p>Incremental logic for sessionization can be relatively complex: you need to ensure that session indexes increment correctly, and make sure you don’t split sessions up in the wrong place. I won’t go into the details in this post, but here are the examples of incremental versions of <a href="https://github.com/dataform-co/pdt_to_dataform_sessionization/blob/master/definitions/sessionized_events.sqlx">sessionsed_events</a> and .</p><h3>Step 3: monitor data quality with assertions</h3><p>Now that we’ve converted the models to be incrementally built, they’ll update faster and cost less each time they update. But we have paid a small price in complexity. To give confidence that the sessionization is doing what we think it should, we should write some tests that check the output data meets our expectations.</p><p>The Dataform framework makes it easy for analysts to write tests on output data using <em>assertions</em>. Once we’ve added assertions to our transformation pipeline, Dataform will check that the assertions are valid on every update. If an assertion fails at any step, an alert (via email or slack) is sent to the team. This means that analyst teams are the first to find out when there are data quality issues — rather than finding out from business stakeholders!</p><p>In the example below we’ve added assertions to check that there is only one row per event_id, and that each of the fields are non-null.</p><p>sessionized_events.sqlx:</p><pre>config {<br>  type: &quot;incremental&quot;,<br>  schema: &quot;prelim&quot;,<br>  uniqueKey: [&quot;event_id&quot;],<br>  assertions: {<br>    uniqueKey: [&quot;event_id&quot;],<br>    nonNull: [&quot;timestamp&quot;, &quot;user_id&quot;, &quot;event_name&quot;, &quot;event_id&quot;, &quot;session_index&quot;, &quot;session_id&quot;]<br>  }<br>}<br><br>with session_starts as (<br>select<br>...</pre><p>Because we’ve split the transformation into 3 steps, and added assertions after each step, debugging data quality issues is far easier: assertion failures pinpoint the exact point in the pipeline that something went wrong.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*u8zifBhu7mRd9jQo.png" /></figure><p>Now that the tested and incremental sessionization pipeline is set up in Dataform, we can schedule it to be updated at a regular interval. In this example, we’ll schedule the entire pipeline to run every hour. This can be configured in the Dataform web UI, with the configuration version controlled in the schedules.json file:</p><p>schedules.json</p><p>The last thing to do is update the LookML for the sessions view to point at the sessions table we are now updating each hour:</p><p><a href="https://gist.github.com/dwl285/cca564669e52c8495e112e647fa2f35f#file-sessions_new-view-lookml">sessions.view.lookml</a></p><pre>view: sessions {<br>  sql_table_name: dataform.sessions ;;<br>  }<br>  <br>  dimension: session_id {<br>    type: string<br>    sql: ${TABLE}.session_id ;;<br>  }<br>  <br>  ...</pre><h3>Dataform + Looker</h3><p>Looker is a fantastic business intelligence tool that allows analysts to move away from ad-hoc reporting, instead collaboratively developing a data product to enable self-serve analytics across the organization.</p><p>PDTs are a great starting point for analysts looking to quickly transform data. There are, however, a few features they’re lacking which may hold your team back as it starts to scale. With the <a href="https://dataform.co/?utm_medium=organic&amp;utm_source=dataform_blog&amp;utm_campaign=converting-looker-pdts-to-dataform">Dataform</a> framework, we’re combining the ease-of-use of Looker, with data engineering best practices for managing transformation pipelines. Combining Dataform and Looker gives your data team the best of both worlds: a best in class BI platform, as well as a best in class data modelling framework.</p><p><em>Originally published at </em><a href="https://dataform.co/blog/converting-looker-pdts-to-dataform"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1475287bfeb5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/converting-looker-pdts-to-the-dataform-framework-1475287bfeb5">Converting Looker PDTs to the Dataform framework</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exporting BigQuery usage logs to… BigQuery]]></title>
            <link>https://medium.com/dataform/exporting-bigquery-usage-logs-to-bigquery-e1133b43ff9?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/e1133b43ff9</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[dataform]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Daniel Lee]]></dc:creator>
            <pubDate>Thu, 30 Jul 2020 08:45:27 GMT</pubDate>
            <atom:updated>2024-03-12T08:54:39.195Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KVLTzTrzTB3O2O2RrriVcQ.png" /></figure><p><em>Dan is the Founder of Outlier, </em><strong><em>your fractional data team </em></strong><em>| </em><a href="https://weareoutlier.com/"><em>weareoutlier.com</em></a></p><p>BigQuery makes detailed usage logs available through <a href="https://cloud.google.com/bigquery/docs/reference/auditlogs#stackdriver_logging_exports">Cloud Logging exports</a>, but before you can start analysing them you probably want to start exporting them to BigQuery. It’s really simple to turn the export on but unfortunately Google’s documentation is pretty vague, so I thought I’d write this blog post to help.</p><h3>Setting up the export</h3><p>Open the <a href="https://console.cloud.google.com/logs/viewer">Logs Viewer</a> and make sure you’ve chosen the correct GCP project in the drop down at the top left.</p><p>Expand the advanced filter box.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/0*_131BQZEdOoBu96t.png" /></figure><p>Add the following advanced search into the filter box:</p><pre>resource.type=&quot;bigquery_project&quot; OR resource.type=&quot;bigquery_dataset&quot;</pre><p>You should see something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/0*43sMBqdFtp6OtLVT.png" /></figure><p>Click Create Sink, enter a &quot;Sink Name&quot;, choose BigQuery as the &quot;Sink Service&quot; and select a BigQuery dataset to write the logs into (you can also create a new dataset through the dropdown interface).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/549/0*bi9bFKWynz-VhWQW.png" /></figure><p>That’s it! All future logs relating to BigQuery usage in your project will be written to this dataset. Note, it’s not possible to backfill historical data.</p><h3>Modelling the data</h3><p>You now have a table in BigQuery with details of every single activity relating to your BigQuery project. However, the table isn’t particularly user friendly. Answering simple questions like “which user spent the most on BigQuery yesterday” requires complex JSON transformations of difficult to remember fields:</p><pre>select<br>  protopayload_auditlog.authenticationInfo.principalEmail<br>  sum(5.0 * cast(<br>    json_extract_scalar(<br>      protopayload_auditlog.metadataJson,<br>      &quot;$.jobChange.job.jobStats.queryStats.totalBilledBytes&quot;<br>    ) as int64<br>  )) as spend<br>from<br>  bigquery_logs.cloudaudit_googleapis_com_data_access_*<br>where<br>  resource.type = &#39;bigquery_project&#39;<br>  and date(timestamp) = date(timestamp_sub(current_timestamp, interval 1 day))<br>group by 1<br>order by 2 desc<br>limit 1</pre><p>To help with this, we’ve created a Dataform package (documentation <a href="https://docs.dataform.co/packages/dataform-bq-audit-logs?utm_medium=organic&amp;utm_source=medium&amp;utm_campaign=exporting_bigquery_usage_logs">here</a>) that creates a clean and easy-to-use table with all the fields you’d expect. Adding a Dataform package to your project is as simple as adding a few lines of code.</p><p><a href="https://www.loom.com/embed/fdfa25dcdc8544e38fe844199b970f87">https://www.loom.com/embed/fdfa25dcdc8544e38fe844199b970f87</a></p><p>“How much did we spend yesterday” can then be answered with the much simpler query:</p><pre>select<br>  principal_email,<br>  sum(cost_usd) as spend<br>from<br>  dataform.bigquery_compute_logs<br>where<br>  date(timestamp) = date(timestamp_sub(current_timestamp, interval 1 day))<br>group by 1<br>order by 2 desc<br>limit 1</pre><p>If you’d like to learn more about Dataform, check out <a href="https://docs.dataform.co/?utm_medium=organic&amp;utm_source=medium&amp;utm_campaign=exporting_bigquery_usage_logs">our documentation</a> and <a href="https://app.dataform.co/?utm_medium=organic&amp;utm_source=medium&amp;utm_campaign=exporting_bigquery_usage_logs">sign up for a free account</a>.</p><p><em>Originally published at </em><a href="https://dataform.co/blog/exporting-bigquery-usage-logs"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1133b43ff9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/exporting-bigquery-usage-logs-to-bigquery-e1133b43ff9">Exporting BigQuery usage logs to… BigQuery</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Invest in your analysts, now!]]></title>
            <link>https://medium.com/dataform/invest-in-your-analysts-now-81d32f730188?source=rss----3ef182b8ea8f---4</link>
            <guid isPermaLink="false">https://medium.com/p/81d32f730188</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Daniel Lee]]></dc:creator>
            <pubDate>Thu, 30 Jul 2020 08:44:46 GMT</pubDate>
            <atom:updated>2024-03-12T08:54:07.844Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h9TZs6GBlr8H4RNP0vfeLg.png" /></figure><p><em>Dan is the Founder of Outlier, </em><strong><em>your fractional data team </em></strong><em>| </em><a href="https://weareoutlier.com/"><em>weareoutlier.com</em></a></p><p>One of the things I love about my role at Dataform is that I get to speak to people working in data teams at other companies almost every day. Each of these conversations helps me broaden my knowledge — and I’m meeting people and making friends in the process!</p><p>But all too often, these conversations make me sad. One thing I repeatedly hear is that data analysts are expected to do their jobs with unfit tools, slowing them down and limiting the potential of what they can achieve.</p><p>Full disclosure: I have a vested interest in this issue, as I work for a company that makes and sells software for analysts. You’ll have to trust me when I say that I’m not writing this post for selfish reasons. I’m writing this post because it makes me <em>sad and angry</em> that there are smart, passionate analysts out there that are striving to do great things for their organisation, but spend far too much time on menial tasks because of the tools they’re being forced to work with.</p><h3>Analysts: tools of the trade</h3><p>There are of course many things that analysts need to do their job properly: a good laptop, supportive managers, a comfortable chair… but there are two things that are specific to analysts that seem to be commonly underestimated that I’d like to go into more detail about here.</p><p>Analysts <em>use data</em> to help organisations make informed decisions, <em>quickly</em> (if you’d like to read more about what an analyst is, I strongly recommend reading <a href="https://hbr.org/2018/12/what-great-data-analysts-do-and-why-every-organization-needs-them">this article</a>). So clearly analysts need two things: access to data, and the ability to move fast with it.</p><h3>Access: the data warehouse</h3><p>I won’t go into detail in this post about why a data warehouse is the optimal place for storing and accessing data (more on that <a href="https://dataform.co/blog/modern-data-stack">here</a>), but I’ll point out two key things: modern, cloud based data warehouses are <em>fast</em> and <em>SQL based</em>. Fast, like you can scan terabytes of data in seconds. And SQL based — SQL being a language that all good analysts are familiar with.</p><p>Too many analysts I’ve spoken to are expected to access data from some other system: MySQL, Grafana, Elastic Search, or even Excel (!). These options are not all as bad as each other, but they all fail at one (or both) of the key requirements: speed, and ease of access.</p><p>Expecting analysts to work with data outside of a data warehouse is like asking software engineers to serve their application from a machine sat under your desk. It could be done, sure. And on the surface it may seem a little cheaper than using AWS or GCP. But it’s a bad decision in so many ways, and by the way, who are you to tell the engineers how they should set up their infrastructure?</p><h3>Development experience: working with data</h3><p>Step one to setting up your analysts for success is letting them work with data in a place that’s built for purpose: a cloud data warehouse. BigQuery and Snowflake are good places to start. But once they’ve got access to data in a warehouse, they need a way to access and manage the data within the warehouse.</p><p>Going back to the software engineering analogy: they need a way to write code, test it, deploy it, and version control all code changes.</p><p>It turns out that <em>analysts need to do all of those things too</em>. They need to write SQL, test datasets, send SQL queries to the data warehouse as part of complex pipelines, and version control their SQL code.</p><p>At any company I’ve been at, software engineering teams have been left to make their own choices about what text editor they use, which CI/CD platform, the right programming language for each project, and where they should store their code. But for some reason, there are many organisations that don’t let analyst teams make these decisions themselves.</p><p>Let me describe three (real life) scenarios that I’ve encountered. They represent how a disappointingly large number of organisations work today.</p><h3>Scenario one: no data warehouse</h3><p>One analyst I know is expected to work with data in a MySQL database. MySQL can’t handle the queries that he needs to run, so for each analysis he has to download tens of gigabytes of data into CSVs, spend tedious hours loading it into a Jupyter notebook (hoping his notebook server doesn’t crash), and then spend more tedious hours running simple aggregation queries. When a new request comes along, the process starts again.</p><h3>Scenario two: no analyst tools</h3><p>Another team of analysts is expected to use the same tools as software engineers, using the command line and a text editor to work with their code and version control it. But those text editors aren’t built for analysts, and don’t have the suite of plugins available to engineers. Analysts don’t want to write code in a different environment to the data. We want to be able to write SQL and run it all in the same place, because seeing the results of the SQL is really important to our development workflow. It doesn’t make sense to make analysts work with tools not built for them.</p><h3>Scenario three: in-house analyst tools</h3><p>A third analyst at a well known US multi-channel retailer was “lucky” enough to have an internal engineering team volunteer to build something for them. “We’re ACME INC, we don’t buy software, we build it, we can do this”. If you’re lucky enough to work at Facebook or Google, this may end well. But in this case, the result is that analysts are forced to work with substandard tooling <em>every single day</em>. Saved files have no revision history, meaning queries are commonly accidentally overwritten leading to hours of wasted time rewriting them. There is a version control system, but it is completely separate to the query runner, meaning most analysts forget to use it. And the web based editor has none of the useful functionality like autocomplete or multi-line editing that are the norm in dedicated text editors. The best analysts at this company will only put up with this for so long before they head elsewhere (I know this, the analyst told me so).</p><p>Going back to the software engineering example: how many software engineering teams build their own text editor? How many build their own version control system? Not many, because building these things well, better than the (often paid for) alternatives out there, is almost certainly not something your engineering team has the resources to do for an internal tool. So please don’t do this for analysts tooling.</p><p>Which tools would I recommend for analyst teams working with data? <a href="https://dataform.co/?utm_source=medium&amp;utm_campaign=invest_in_your_analysts">Dataform</a> was built to solve all of the problems discussed at the start of this section, so I’d recommend you start there — there are several other options worth investigating too.</p><h3>But, this sounds expensive?</h3><p>Hopefully I’ve convinced you by this point that it’s important that analysts’ needs are treated with respect, and that they shouldn’t be given sub-standard tools. The problem, however, is that cloud data warehouses and data modelling platforms (like Dataform) cost money! At this point, it seems that many organisations get scared and revert to asking analysts to cobble something together with cardboard and sellotape.</p><p>But, hang on a minute. Are these tools really that expensive? Let’s do a back of the envelope calculation (remember, I’m an analyst, speed is important).</p><h3>Warehouse cost</h3><p>I’ll use BigQuery, because their <a href="https://cloud.google.com/bigquery/pricing">pricing is transparent</a> and easy to understand. Let’s say that you’re storing 1TB of data and your analyst is querying 100GB of that data each day. This will cost you ~$2/month for data storage and ~$10/month for data processing. So, $12 total.</p><p>Granted, data volumes vary wildly so this cost estimate may not apply to your organisation, but for a team that only one analyst supports, this is probably a reasonable order of magnitude estimate.</p><h3>Tooling cost</h3><p>Dataform packages all of the tooling an analyst’s needs into one platform: a SQL IDE, query scheduler, testing framework, integrated git version control and documentation. For our basic package (which will satisfy all but the largest organisations), this costs $30/analyst/month.</p><p>So, $20/month for the warehouse and $30/month for high quality tooling. For $50/month, we can equip analysts with all of the tools they need, built for their needs, built and supported by organisations <em>dedicated to making analysts successful</em>. This seems… pretty reasonable?</p><p>But even if it turned out it <em>was</em> expensive to get this right, let’s say $500/month. It’s still worth it! This is about more than appeasing grumpy analysts.</p><p>If you asked your software engineers to use sub-standard tools, you’d do more than just make your engineers angry. They’ll spend more time on <a href="https://landing.google.com/sre/sre-book/chapters/eliminating-toil/">toil</a>, you’d release fewer features, the ones you do release would have more bugs, and your users would stop using your product. <a href="https://en.wikipedia.org/wiki/Brooks%27s_law">Brooks’ law</a> suggests that hiring more engineers may not solve your problems.</p><p>If analysts are working with sub-standard tooling, your entire data strategy suffers. Analysts are slowed down by menial work, fewer decisions are supported by data, where data is used it’s often inconsistent or just wrong, and your organisation will consistently make suboptimal decisions.</p><h3>Please help</h3><p>Hopefully the analysts at your organisation are already using the right tools for their job. But if they’re not, and you care about your organisation using data to inform decision making, can you please pass this along to someone that can help resolve the problem? And if you’re one of the people telling analysts that they don’t need a cloud data warehouse, or that the internally built SQL IDE at your company should be fine, have I made you reconsider?</p><p>BigQuery, Snowflake and Dataform all offer free trials and rolling monthly payment (no need to commit to a contract upfront). Can I suggest you at least let your analyst team experiment with improving their development stack? 10 years of experience tells me you are unlikely to regret it, and the potential rewards for your organisation — a happy, performant and thriving analytics function — is surely worth it?</p><p><em>Originally published at </em><a href="https://dataform.co/blog/invest-in-your-analysts"><em>https://dataform.co</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=81d32f730188" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dataform/invest-in-your-analysts-now-81d32f730188">Invest in your analysts, now!</a> was originally published in <a href="https://medium.com/dataform">Dataform</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>