<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Austin Gibbons on Medium]]></title>
        <description><![CDATA[Stories by Austin Gibbons on Medium]]></description>
        <link>https://medium.com/@AustinBG?source=rss-5358551e83a3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*WnBWhCk3CPRS-EVz_sd1xw.jpeg</url>
            <title>Stories by Austin Gibbons on Medium</title>
            <link>https://medium.com/@AustinBG?source=rss-5358551e83a3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 06:41:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@AustinBG/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com" rel="hub"/>
        <item>
            <title><![CDATA[Anatomy of a Redshift Query]]></title>
            <link>https://medium.com/@AustinBG/anatomy-of-a-redshift-query-bf7433aca5b9?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/bf7433aca5b9</guid>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Fri, 30 Jul 2021 15:52:54 GMT</pubDate>
            <atom:updated>2021-07-30T18:19:42.685Z</atom:updated>
            <content:encoded><![CDATA[<h4>Or, Why is My Query Slow?</h4><p>At Plaid a key goal of the Data Analytics Infrastructure team is to enable our users to ask business questions in SQL and get timely answers.</p><p>We use AWS Redshift as the data warehouse that powers BI tools like Mode Analytics, our Atlan Data Catalogue, and internal Retool applications. With hundreds of users running thousands of queries, we’ve hit a lot of the edges in the Redshift architecture and want to share our performance experience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gO6eQR6Gv2bbpzxxB9ix7A.png" /><figcaption>System overhead can make a query seem slow even if the execution is fast</figcaption></figure><h3>Redshift Cursory Overview</h3><p>If you’re new to Redshift or data warehouses in general, let me set the stage. Your Data Science friend comes to you with an innocent request: “Can you help me verify the A/B experiment results across different customer verticals on iPhone and Android?” They want to verify what they are seeing in their experimentation tool matches even when comparing to the customer segments the sales team uses in Salesforce. But what a Data Engineer really hears is</p><blockquote>How do we efficiently query <strong>16 different tables </strong>and<strong> 10 billion rows</strong>?</blockquote><p>Enter the Data Warehouse. We need a database to do big aggregations, not row level operations.</p><p>To answer our users question, we take data from across the organization: backend server logs from our application, javascript events, production application data, Salesforce data, and put everything into Redshift.</p><h4>Redshift Data Warehouse Table Design</h4><p>In order to efficiently query this data, data is stored in Redshift differently from how it works in a database like MySQL, Postgres, or Mongo. The data is stored <em>by-column</em> instead of <em>by-row</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*LmwbaI3eF5vIavaH" /><figcaption>Redshift is a Columnar store Data Warehouse</figcaption></figure><p>Additionally, Redshift is distributed. Rather than all the data being on a single computer, the data is spread across multiple different computers called <em>nodes</em>, and kept in 1 MB <em>blocks</em> that are aggregated into <em>slices</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/993/0*tv0zHJynkgARi5Zs" /><figcaption>Redshift is a distributed system, with multiple computers making a redshift “cluster”</figcaption></figure><p>There are two crucial parameters that a user sets when storing data on the cluster. The Distribution strategy and the Sort style, with their accompanied distkey and sortkeys.</p><p>First, you must distribute the data. This controls onto which node in the cluster each row is stored for your table. By default it uses an even round-robin distribution, but if you know what tables are related to this table, common join patterns in your queries, you can pick a specific column as the distribution key.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fpxMegQcRMbBM91A" /><figcaption>A Redshift table is distributed on the cluster either round-robin, copied to all tables, are sharded by a column in the table</figcaption></figure><p>Then you also pick a sorting strategy that applies to each slice of data within the nodes. Generally, picking a single sort key and using it in your query will already significantly boost your performance, but Redshift also supports multiple keys, with either a compound or interleaved sorting strategy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/631/0*uNOSB7kkUwnRx-lC" /><figcaption>Data is sorted on disk</figcaption></figure><p>There are a lot more details around table design: ensuring you vacuum analyze your data, picking an encoding (compression) type, the size of varchar columns, and more. AWS has <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_designing-tables-best-practices.html">documentation</a> and <a href="https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-techniques-for-amazon-redshift/">blog</a> <a href="https://aws.amazon.com/blogs/big-data/optimizing-tables-in-amazon-redshift-using-automatic-table-optimization/">posts</a> that can help you understand and setup your tables for maximum performance.</p><p>It’s still easy to run into “gotchas”, for example if your distkey has null values, all of the null valued rows are sent to the same node, even though you likely don’t want to join the null values together. Fortunately, the Redshift console has recommendations that can help surface, understand, and resolve this issues:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XBEfVdc1CSHGfkp8EfoREA.png" /><figcaption>The Redshift Console surfaces recommendations to improve your table layouts</figcaption></figure><p>But I want you to take away this thought: <strong>How you store data in Redshift is configurable and important.</strong></p><p>At Plaid, there are over 4,000 tables in our Redshift cluster. Broadly, we always sort on the principal timestamp (e.g., <em>created_at</em>) whenever possible. We choose the most popular join key, <em>item_id</em>, as the distkey for about 95% of the tables that have this column. Our goal in this ubiquitous strategy was to make it easy for data analysts to “do the right thing”, rather than try to over-engineer for granular use-cases.</p><p>Applying a pervasive strategy helps users understand and chance into performant queries, but it’s still important to be able to performance debug a query and take our understanding of the table design and map it into runtime analysis. Prependingexplain to your query allows you to see how Redshift will run your query, called the query plan</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UxDxDL6Qh6xGGpCBhnNLnQ.png" /><figcaption><em>explain shows the query plan for a query</em></figcaption></figure><p>The query plan can feel mysterious, with a mixture of large numbers, unfamiliar SQL jargon, and an odd indentation structure. Learning to scan a query plan for key items matching to your tables will help you recognize where your query is taking a performance hit.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tMPPEHsIYpa2mb9xzApDSg.png" /><figcaption>Learn about the <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_data_redistribution.html">styles of redistribution of data across the network</a> when running your query</figcaption></figure><h4>Query Run Time Walkthrough</h4><p>Let’s look at a real example of the impact this can have at Plaid.</p><p>A data analyst wanted to answer this business question:</p><blockquote>What ratio of API calls were made for a user with the auth product enabled?</blockquote><p>This query can be solved by looking at two key tables in our data warehouse: our primary api route hit log, and a copy of the production application data for users.</p><pre>select<br>  date_trunc(&#39;day&#39;, logs.published_at)<br>  , users.auth_enabled<br>  , count(1)<br>from<br>   logs join users on<br>     logs.user_id = users.id<br>group by<br>  1, 2<br>order by<br>  1</pre><p>However, these tables are huge! The logs table is many billions of rows, and the users table many millions. This query might not run very quickly in its current format. If we look at the query plan for this query, we’ll see some good things and some trouble spots that will hurt our performance:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*Y8SZiNndsWKCE3w9Mjjjnw.png" /><figcaption>The query plan highlights parts of the query that cause slow performance</figcaption></figure><ul><li>DS_BCAST_INNER We’re not leveraging the distkeys, so Redshift sends the smaller table to each node of the cluster</li><li>Seq ScanWe’re not filtering by the the sortkeys, so we’re doing an entire table scan on both tables</li><li>Sort Key We are grouping by and ordering by the sort keys, which is preferable (but relatively minor)</li></ul><p>First, let’s fix the query to use the distkeys. Both of these tables have the distribution key item_id, but we joined our table on user_id . However, for our data model we know logically that “if the user_id is the same, the item_id is the same”. Redshift doesn’t have a way to infer this automatically, but we can define it in the query:</p><pre>-- redundantly add our dist key to the join<br>logs join users on <br>  logs.user_id = users.id<br>  and logs.item_id = users.item_id</pre><p>When we run explain on our query again, we see DS_BCAST_INNER has become DS_DIST_NONE . Because this table is hundreds of millions of rows, that’s a huge amount of savings. We avoid having to send all the data across the network:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GGTWdLnkUndfupKEY3qH1w.png" /><figcaption>We can see Redshift create a better query plan with the improved join keys</figcaption></figure><p>We made a great change that didn’t impact the result of the query at all, just by leveraging the table design. We can make this query even faster by recognizing to solve our business question, we don’t need to look at the entire history of data at Plaid: we can get the insight we need by only looking at the last month of data.</p><pre>where<br>  logs.published_at &gt; getdate() - interval &#39;30 day&#39;</pre><p>This will help our query a lot. Instead of doing a sequential scan on the entire table, we our filtering on our sort key:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7_DrpyCe08PfRRTYx9QUUQ.png" /><figcaption>Filtering a table by the sort key allows Redshift to skip over irrelevant data blocks</figcaption></figure><p>We can even take it one step further. If we’re willing to change our business question to <em>what ratio of API calls were made for a </em><strong><em>new </em></strong><em>user with the auth product enabled?</em> , we will likely get a similar insight and can filter on both tables</p><pre>where<br>  logs.published_at &gt; getdate() - interval &#39;30 day&#39;<br>  and users.created_at &gt; getdate() - interval &#39;30 day&#39;</pre><p>When looking at the total improvement, we can see the progress in the top-line number in the query plan:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v6Ia_8joQGWD0PI7t63vvw.png" /><figcaption>Changes in order of magnitude help us know when we’ve likely improved query runtimes.</figcaption></figure><p>In our example, migrating from the initial query to one designed to leverage table layout improved the query to run in about 140 seconds to around 35 seconds. While this is a good improvement, we’re still looking at a lot of data, working with data that is continually being ingested, and is highly variable subject to Redshift contention.</p><p>At Plaid, we maintain a system of materialized views that pre-aggregate results for common queries. This both reduces the amount of data a query needs to access, and lets a user share smaller datasets across many charts and dashboards.</p><h3>Queues and System Contention</h3><p>The story doesn’t end with a single query. It is important to understand the query patterns across the cluster as a whole, and how a “noisy neighbor” can impact queries for many users.</p><p>Sometimes, opaque to a user running a query, system overhead can be the dominating factor in query runtime.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gO6eQR6Gv2bbpzxxB9ix7A.png" /><figcaption>System overhead can make a query seem slow even if the execution is fast</figcaption></figure><p>In addition to the query plan and actual execution of a query, a query is subjected to several steps that add to the runtime:</p><h4>Workload Management Queues</h4><p>Redshift runs a limited number of queries concurrently, by default it runs 5 queries at a time. You can configure <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_workload_mngmt_classification.html">Workload Management Queues</a> and <a href="https://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-queue-assignment-rules.html">assign queries to queues</a> through query groups or user groups, and run up to 50 queries concurrently.</p><p>One of the first things you should do as a Redshift DBA is analyze your workload and define appropriate WLM Queues. You can either<a href="https://docs.aws.amazon.com/redshift/latest/dg/automatic-wlm.html"> let Redshift automatically define the settings</a>, or to better tailor your experience you can define rules around the behavior you want to encourage.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KBlb8UZtVBbE39d5" /><figcaption>Workload Management Queues are the basis of resource prioritization in Redshift</figcaption></figure><p>For example, in our cluster the top priority is queries where a user is present and interacting with the query. While other metrics like background ETL throughput are important to us, we over-index our cluster’s resources to the interactive users query group.</p><p>So in aggregate, our cluster observes a lot of queueing (blue in this chart)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Vl-Dz5KI49b_evQq" /><figcaption>blue bars are time spent queueing, gold is active query runtime</figcaption></figure><p>but we’re willing to tolerate this as long as the background queues occasionally hit inbox-zero, and we keep up with the pace of updates. Our primary goal is to be spending most of our time running user queries (gold bars in this chart). When users refresh a large dashboard with dozens of charts, we want them to queue in the cluster but then get quickly processed, bringing the queue back down to zero.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cWEeDyHqsQGI8_6G" /><figcaption>in our user interactive queue, we try to minimize queueing (blue)</figcaption></figure><p>You can get a helping hand from Redshift with <a href="https://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html">Query Monitoring Rules</a>, enabling you to set things like timeouts for queries in queues, and I recommend creating the recommended <a href="https://docs.aws.amazon.com/redshift/latest/dg/tutorial-wlm-understanding-default-processing.html">WLM_QUEUE_STATE_VW</a>.</p><h4>Table Locks</h4><p>The greatest devil lies within the locks. When the cluster accesses a table, it <a href="https://aws.amazon.com/premiumsupport/knowledge-center/prevent-locks-blocking-queries-redshift/">creates a lock on the table</a>:</p><ul><li>AccessExclusiveLock: only this query can access this table, common to operations that modify the table itself, like adding or removing a column or changing the table name.</li><li>AccessShareLock : when reading the table and for some operations that modify data, this lock is secured, which blocks another query from getting AccessExclusiveLock</li><li>ShareRowExclusiveLock: when the table is adding, removing, or modifying rows in a table it will acquire this lock for the row. Other queries can still be granted AccessShareLock on the table.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Enwo35WWTaqAAQhz" /><figcaption>We monitor locks and tables, and alert if a query is long-running</figcaption></figure><p>If you’re familiar with operating systems or distributed programming, you might recognize this “Reader-Writer” lock pattern. When a “writer”, AccessExclusiveLock wants to modify the table, it first has to (1) prevent new readers, AccessShareLock and ShareRowExclusiveLock , from accessing the table (2) wait for all existing readers to finish reading (3) begin it’s actual work</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kYhWjUJa6_4dD3lFLWGvfQ.png" /><figcaption>The Writer lock blocks new readers until it gets a chance to run</figcaption></figure><p>This can be hugely impactful to a reader that is being blocked by the writer. A query like Reader-6 that should have only taken 30 seconds can end up waiting for hours as Reader-1 is holding a lock and Writer-5blocks new readers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s4akfu3Jv-D-e9edwTpEvg.png" /><figcaption>Read-Write-Read lock serialization can incur a long delay for new reader queries</figcaption></figure><p>This is very painful, and completely disrupts someone’s workflow when they are exposed to this, through no fault of their own. At Plaid, we’ve redesigned several ETL pipelines just around this problem, changing the sequence of create, update and delete operations to minimize the number of AccessExclusiveLocks needed. We still observe long-held locks, and have also moved query schedules into nights and weekends to prevent interrupting working hours.</p><p><em>Edit: Mike Weinberg has written some great </em><a href="https://github.com/mike-weinberg/dreddshift/blob/master/views/admin/blocked_locks.sql"><em>Redshift Lock Analysis SQL</em></a><em> utilities that are a great help to understanding and resolving this issue!</em></p><h4>Schema Updates</h4><p>If a query is modifying the schema of a table, it also can incur friction in accessing the systems tables that control the schema.</p><p>Initially, a majority of our ETL pipelines followed this pattern:</p><ul><li>create a new table with a date suffix, e.g. create table users_2021_07_26(...)</li><li>copy data into the table copy users_2021_07_26 from &#39;s3://...</li><li>switch out the tables: alter table users rename to users_old ; alter table users_2021_07_26 rename to users</li><li>If everything looks good, drop table users_old</li></ul><p>We never used temp tables, preferring to always create “real” tables. This helped us recover from various errors and validate data more easily in our ETL pipelines. However, this came with a downside we learned about much later: Redshift does not garbage collect old schema artifacts! Using select pg_size_pretty(sum(pg_total_relation_size(oid))) from pg_class, we found that our schema was far, far too large.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kWbmPE8RGr2I-eB0mttKuw.png" /><figcaption>Even one gigabyte would be “too much” and we were over 10 gigabytes!</figcaption></figure><p>Unfortunately, the only way to garbage collect is to restart the cluster. We migrated many of our ETL pipelines to use static tables when possible, rather than a new table every time, to help control this problem.</p><h4>Commit Queue</h4><p>Finally, after every other part of the query is complete the query is confirmed completed by the cluster. In order to complete, the cluster checks for any competing modifications that would violate <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html">Serializable Isolation</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PJmC2jJga118HKOzAOGozw.png" /><figcaption>The resulting data from two queries run in parallel must be the same as if they had run in serial</figcaption></figure><p>which is just a fancy way of saying “no race conditions”. Redshift guaranteers that partial assignments from different queries can never happen to a table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*buLZWBDwQmT6YstwPkOPbQ.png" /><figcaption>Observing a data state from two separate queries is not allowed</figcaption></figure><p>For a cluster operating with a normal, batch cadence this usually isn’t a problem. However, in our cluster we have hundreds of kinesis streams writing data into our cluster, and during a large data pipeline migration the p90+ commit time became unexpectedly long</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yK6UC2u1R0s6pi0F" /><figcaption>p90+ query commit time was exceeding execution time</figcaption></figure><p>We accidentally flooded the commit queue by having kinesis write very frequently. We reduced the update cadence from 10 MiB to 128 MiB to batch more updates together, and resolved our commit queue contention.</p><p>The starting point for commit queue analysis is in <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_STL_COMMIT_STATS.html">stl_commit_stats</a>. Still, all these issues together, working as a Data Engineer on Redshift sometimes feels daunting</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vwifdUahEdu8g-RkJ1W3Xw.png" /></figure><p>fortunately, AWS has been a great partner helping us investigate issues, and they continue to build and improve the product.</p><h3>What’s New at AWS for Scalability</h3><p>With the advent of competitive alternatives like Snowflake and BigQuery, AWS has pushed some new features out to improve Redshift scalability.</p><h4>Ra3 Nodes</h4><p>In late 2019, Redshift announced a successor to the dc2 node series called Ra3. First and foremost, as is to be expected for any newer computer it <a href="https://aws.amazon.com/blogs/apn/amazon-redshift-benchmarking-comparison-of-ra3-vs-ds2-instance-types/">straight outperforms</a> the old node types, with both hardware improvements and software improvements like AWS Nitro.</p><p>More importantly, it also changed the model of data storage. With the older model, data lived directly on disk, and if you needed to scale either the size of your disk or the computational resources (memory, I/O, and CPU) you had to scale them together. This made it expensive to retain old but infrequently used data sets, especially if they consumed a lot of disk space. With the Ra3 model type data lives in S3 and scales separately from the computational cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7CcKwDjioTS9dHGn" /><figcaption>Ra3 node types also rearchitected the storage model on top of S3</figcaption></figure><h4>Concurrency Scaling</h4><p>This architecture has paid huge dividends for us, resolving many disk capacity issues and also enabling us to use <a href="https://docs.aws.amazon.com/redshift/latest/dg/concurrency-scaling.html">concurrency scaling</a>. When the cluster detects that it is under load for a specific query queue, it will dynamically provision additional cluster resources</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*nNFa6Q4cL9U69Uy_" /><figcaption>concurrency scaling dynamically provisions more computational power in response to query load</figcaption></figure><p>Concurrency scaling has greatly boosted our ability to have consistency performance for queries, resolving both query queueing and general resource constraints. We have been using concurrency scaling only on “read” queries, with “write” concurrency scaling being a newer feature.</p><p>The caveat is that unlike a Redshift cluster, which has a deterministic monthly price, concurrency scaling charges dynamically. You can set a maximum (as we have above, limiting to 5x capacity boosting), but if you scale for queries needlessly it’s a sunk cost. Currently, we spend about $40k/mo. on our Redshift cluster and $6k/mo. on concurrency scaling, so it’s a monetary win for us over scaling the cluster.</p><h4>Redshift Spectrum</h4><p>Spectrum has been around for a few years, and sits between “Data Warehouse” and “Data Lake” solutions. With Spectrum, you store data yourself in S3, such as in CSV, Parquet, or Hudi format, and use Redshift as a front-end to query the data. At Plaid we have run a few Spectrum experiments to some success, but haven’t taken the initiative to heavily invest into configuring data tables for Spectrum.</p><h4>Data Sharing</h4><p>Redshift leaned into the Snowflake model in early 2021 with Data Sharing. With the advent of managed storage putting data in S3, data can now be accessed from multiple clusters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/994/0*ZoeKAWPnHcN9rgX6" /><figcaption>Data Sharing allows separate clusters to query the same data</figcaption></figure><p>However, creating a new secondary cluster is not a turnkey access to all the same data in your primary redshift cluster. You have to create a <a href="https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html">Data Share</a> and manage tables and permissions within the data share. We explored this direction but ran into a “cold-start” problem. Data isn’t cached onto disk from S3 until the first time you access the table, and we were observing that the first time a query was run from the consuming cluster it would take about twice as long to execute, so we decided to re-evaluate later rather than risk paying a trailblazer tax.</p><h3>Future of Redshift at Plaid</h3><p>Plaid is growing rapidly, so we’re continuing to evaluate the right data stack for our user base. Currently, we’re trialing DBT as a replacement for our home-grown toolchain of Airflow DAGs, building out a data catalogue in Atlan, and re-architecting data storage for geographic optimization.</p><p>As we continue to scale the number of users and data, we’re considering building more analytics on top of our Hudi data lake, as we build our more Spark functionality on EMR and investigate tools like Presto for analytics friend queries on large datasets.</p><p>If you’re interested in helping us build the next generation of analytics at Plaid I’d love to get in touch — reach out to austin@plaid.com</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bf7433aca5b9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Performance Debugging Golang at Scale]]></title>
            <link>https://medium.com/hackernoon/performance-debugging-golang-at-scale-8d8fab580bbe?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/8d8fab580bbe</guid>
            <category><![CDATA[parallel-computing]]></category>
            <category><![CDATA[go]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[airflow]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Wed, 21 Aug 2019 21:01:42 GMT</pubDate>
            <atom:updated>2019-08-22T23:44:31.231Z</atom:updated>
            <content:encoded><![CDATA[<h3>Performance Debugging Mongo ETL at Scale</h3><h4>A real-world investigation into long-running mgo processes</h4><p>At <a href="https://plaid.com">Plaid</a> we are building the world’s largest understanding of financial information, and it’s crucial that we have the right set of tools internally to process, understand, and track changes and growth of our datasets over time.</p><p>We use AWS Redshift to facilitate analytic analysis on internal data, which allows us to bring in data from many different sources and query, join, and merge the data all in one place. One of the datasets we expose is a copy of our production Mongo cluster, which houses 10s of terabytes of data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/689/1*6Nd-wUwMx-2oWKQ5DkcX3w.png" /><figcaption>Airflow schedules a golang process to move data into S3. We then COPY it into Redshift.</figcaption></figure><p>There are some large collections in Mongo for which we run a daily ETL script that exports data to S3 so that we can pick it up on the other side and load into Redshift. The “Mongo Dump” jobs were written over a year ago, and the volume of data that it’s responsible for moving from Mongo to S3 had grown dramatically as the business grew. We began to notice that export job had grown to take a long wall-clock time: when we were only running it once a day no one much minded if it took ten minutes or two hours, but in recent weeks we had observed a more interesting set of characteristics:</p><ol><li>It had grown to be so long-running as to make it difficult to respond to short-term user requests (for example, to dump a new field from the collection).</li><li>The runtime was becoming highly variable, with the maximum running time being almost twice as long as the minimum run time.</li><li>In some instances, the export script would timeout and the entire process would fail, leaving only a partial data dump.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TQ7DNaYNhfAj2jQm" /><figcaption>Running times had grown ~30%, and would sometimes fail once, before working upon retrying</figcaption></figure><p>We set out to improve the process. First and foremost we wanted to ensure that it succeeded every time, but we also wanted to investigate performance. To improve the long-term health we needed to understand why it had slowed down and what we could do about it. Being able to receive an internal request for data, write any necessary code, and run the updated script in a single day was a crucial benchmark to ensure engineers at Plaid felt empowered with data access, not that they were being blocked by long delays in access to their product’s usage patterns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hvPCjthHpXxGfsHXlqLMCg.png" /><figcaption>Our goal is to ensure that engineers like Carver feel empowered with analytical data access</figcaption></figure><p>Mongo Dump uses <a href="https://godoc.org/github.com/globalsign/mgo">mgo</a> to talk to Mongo. Mongo Dump sets up <em>N</em> threads that each create an Iterator, where empirically N is either 16 or 32. It then partitions the workload across the different threads and lets them iterate through a range of IDs. The goal is for each thread to do 1/Nth of the work.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/0*PMMVm_27Cbx48iJP" /><figcaption>What we wanted to happen — each thread should handle an equal distribution of the workload.</figcaption></figure><p>The original implementation did this in a clever way that looked at the data. Mongo BSON Object Ids are not auto-incrementing counters: they encode a timestamp within the id.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JW7nbqge1UyMeOhxVE6x7g.png" /><figcaption><a href="https://docs.mongodb.com/manual/reference/method/ObjectId/">https://docs.mongodb.com/manual/reference/method/ObjectId/</a></figcaption></figure><p>Because of this, if there is a temporal pattern to the data, splitting the data mathematically between the range (max-min) can lead to huge skew. To get around this, we were taking a <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/sample/index.html">sample</a> of ids, ordering them, and creating ranges of work between the samples. Namely, we were taking a sample of size 3*N, and then taking every third sample id and using them as range boundaries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UbQlhfn5ZChH0-TC2ysOAg.png" /><figcaption>Our original implementation would dynamically sample <em>k</em>*N IDs from the mongo collection, and then keep every <em>kth ID as a partition boundary</em></figcaption></figure><p>This setup worked well in the beginning, but as our processes became less efficient we discovered a few issues:</p><ol><li>All of the threads were sharing the same database connection.</li><li>The version of the mgo library we were using had some known issues working with long-running database connections, and calling Refresh() on the session didn’t seem to work as intended.</li><li>The sampling methodology could (and frequently did) result in an uneven workload. In the most extreme cases, the largest chunks were 8x as large as the small chunks, and our performance was dominated by the runtimes of the slowest 4 threads.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7egdewJXr0xTi_bf9nisGw.png" /><figcaption>Our sampling methodology was creating ID ranges that were untenably disproportionate.</figcaption></figure><p>We set about addressing these issues. We decided to focus on architectural issues in the Mongo Dump architecture:</p><ol><li>Replace the shared database connection with a per-thread connection</li><li>Whenever the thread encounters an error related to timeouts (the tell-tale failure), we have it close the existing Database connection and establish a new one.</li><li>Instead of sampling, create a static, known partitioning of the workload space that leverages the temporal growth of the dataset.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zNsuiXPJi059hkeaefphnw.png" /><figcaption>Given a known start date and bucket width, create ranges of mongo IDs for threads to iterate through.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/730/0*iHgJQ_noqWQ32xBm" /></figure><p>The big change was to migrate to small, statically partitioned workloads and have the thread pull tasks from a thread-pool, instead of having <em>N</em> workloads and <em>N </em>threads, we create around <em>N*5 </em>tasks by splitting the id space into buckets of 14 days, and letting the threads pull from the queue when they’re done with the last job. This structure both mitigates unbalanced workloads and allows the threads to organically correct for skew in size by having some threads do a few big ID ranges and some do many small batches.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LzSccvTSvi3r0QO4qE30XA.png" /><figcaption>Exporting hundreds of millions of rows of data in under an hour, achieving our goal.</figcaption></figure><p>Our changes worked! We recovered a lot of the lost parallelism, and more effectively recovered from errors related to long-running mgo connections. Being able to dump the entire dataset in a “short-enough” time frame helps our team push new code and see the results of their changes at scale without having to wait for over-night ETL to run.</p><p>Plaid is the bedrock of the entire fintech ecosystem, and we have a world of data challenges ahead of us. You should <a href="https://plaid.com/engineering">check out our career page</a> or <a href="https://twitter.com/austinbg">tweet me</a> if you’re excited about building a generational company that has an impact on the financial life of (almost) everyone in America.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8d8fab580bbe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hackernoon/performance-debugging-golang-at-scale-8d8fab580bbe">Performance Debugging Golang at Scale</a> was originally published in <a href="https://medium.com/hackernoon">HackerNoon.com</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PagerDuty Analytics with Python & Periscope]]></title>
            <link>https://medium.com/hackernoon/pagerduty-analytics-with-python-periscope-d47dde9e780e?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d47dde9e780e</guid>
            <category><![CDATA[pagerduty]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[pagerduty-analytic]]></category>
            <category><![CDATA[periscopedata]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Wed, 23 Jan 2019 16:21:01 GMT</pubDate>
            <atom:updated>2019-05-22T06:57:59.167Z</atom:updated>
            <content:encoded><![CDATA[<h4>How we replaced manual review of PagerDuty alerts with an automatic weekly summary dashboard.</h4><p>I’m a software engineer at <a href="https://plaid.com/">Plaid</a> on the <a href="https://hackernoon.com/tagged/data-science">Data Science</a> &amp; Infrastructure team. We support a myriad of services for both external clients and internal employees: we enable the product, growth, and <a href="https://hackernoon.com/tagged/engineering">engineering</a> teams with tools like Redshift for large analytics queries and the <a href="https://www.elastic.co/products">ELK stack</a> for real-time investigation across the many services powering Plaid.</p><p>Supporting these analytics &amp; monitoring services is mission-critical: if they go down dozens of workflows across the company break; it’s important that we catch any degradation of service before it becomes it an issue. Over the course of 2018 we’ve added dozens of new features to the services we support: new datasets, faster analytics capabilities, and more self-service data portals. We need to find any issues before they are realized by our users:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6-NU88bCsJVRzMGeYNDizg.png" /><figcaption>The failure mode: our internal users complaining before we notice something is wrong.</figcaption></figure><p>We use <a href="https://www.pagerduty.com">PagerDuty</a> to send real-time alerts when there is a degradation in service performance. Our team has an on-call rotation: between four engineers we rotate being the directly responsible individual for handling any alerts of service performance generated through PagerDuty. The week you’re on call, you use the PagerDuty app to alert of any realtime DSI-Urgent alerts, and the PagerDuty slack integration to send other messages to the DSI-Notify slack channel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rKJsYOD0AcKp_KmEI9GThQ.png" /><figcaption>PagerDuty slackbot — friend to all.</figcaption></figure><p>Towards the end of the year we realized that while we had done well to add new PagerDuty alerts to support SLAs for our services, we weren’t as disciplined with triaging and fixing the underlying long-term issues. In order to step back from treating the symptoms and work towards overall service quality, we decided to add an “on-call summary &amp; handoff” digest to our weekly team meetings. This helped facilitate discussion about why we were being paged, iterate on a service to improve reliability, and change alert structure to reduce false alarms.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mGJIqvbFsvd8K7Ibmr1t7A.png" /><figcaption>Trialing a formalized handoff document, with categories of alerts to facilitate trend analysis and some comments on specific alerts for specific details.</figcaption></figure><p>This worked well as a pilot — we were able to better surface common issues, more easily identify patterns, and decouple the on-call cadence of addressing real-time issues with the need for making longer term substantive changes.</p><p>It was not, however, our long-term vision to add manual work to the on-call rotation. PagerDuty offers a few views into analytics, but we wanted finer grain control of our data and to be able to join it to other analytics data sources. We set out to use the <a href="https://v2.developer.pagerduty.com/page/api-reference">PagerDuty V2 API</a> to pull the data that would help inform patterns of incidents and supplant the manual tracking of our handoff document.</p><p>We use <a href="https://airflow.apache.org/">Apache Airflow</a> for a wide-range of recurring data ETL, and wrote a one-off Airflow DAG that downloads a history of the data from PagerDuty, writes it into S3, and then copies from S3 to Redshift. You can see a sanitized version of the python script in <a href="https://gist.github.com/AustinBGibbons/d6eb6fa1f2278d744070546f5f362ed5">this gist</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dRk39JgL1HRkdhabCQRBrw.png" /><figcaption>Airflow DAG for ingesting PagerDuty data</figcaption></figure><p>We focused on two tables: incidents and log_entries, which gives us a history of which team got notified about what alert on what date. PagerDuty offers a one month history of incidents through the API. Because a user can modify an incident well after the original date of the alert, we re-harvest the entire 30-day incident history and update any modified data. By contrast, log_entries is an append-only log, so we more simply take new data at a regular cadence and copy it into redshift.</p><p>Finally, with the data in Redshift we can create an automated version of our handoff document! Here’s an example of how we query the final schema to pull insights out of the incident and notification history:</p><pre>-- categorize alerts, and count by category + date<br>-- takes a rolling five day history<br>select<br>  date_trunc(&#39;day&#39;, (created_at)::timestamp)::date<br>  , case<br>    when incidents.summary like &#39;%airflow heartbeat%&#39;<br>      then &#39;Airflow Heartbeat&#39;<br>   when incidents.summary like &#39;%mysql_dump%&#39; <br>      then &#39;MysqlDump&#39;<br>    when incidents.summary like &#39;%TaskInstance: dump_%&#39; <br>      then &#39;MongoDump&#39;<br>    when incidents.summary like &#39;%ES%not green%&#39; <br>      then &#39;ES not green&#39;<br>    when incidents.summary like &#39;%queue is building%&#39; <br>      then &#39;Logstash Queue&#39;<br>    when incidents.summary like &#39;%kinesis%behind%&#39; <br>      then &#39;Kinesis Behind&#39;<br>    when incidents.summary like &#39;%TaskInstance%&#39;<br>      then &#39;Airflow - Other&#39;    <br>    else<br>      -- remove numbers to more easily group alerts<br>      regexp_replace(incidents.summary, &#39;[0-9]&#39;, &#39;&#39;)<br>  end as summary<br>  , count(1)<br>from<br>  pagerduty.incidents<br>where<br>  abs(datediff(&#39;day&#39;, getdate(), created_at)) &lt; 5<br>  and (escalation_policy_summary=&#39;DW-DSI&#39;)<br>group by<br>  1<br>  , 2<br>order by<br>  3 desc</pre><p>Which leads us to the grand conclusion: a Periscope dashboard where we can look at the number of alerts, the breakdown by category and service, and track trends over time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*faGiqOWaztIFrQlBKMGQRQ.png" /><figcaption>The final dashboard in Periscope, summarizing alerts over the past 7 days.</figcaption></figure><p>Our handoff doc is a little cleaner now: rather than having to manually type out reports about individual alerts, we can summarize the week from our dashboard and jump straight to the collective and individual actions we want to take to build resilient and reliable data infrastructure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DkmviIUpUDWHF6X8M0WuLA.png" /><figcaption>New handoff document, which focuses on concrete Action Items</figcaption></figure><p>This new process has helped us prioritize &amp; fix noisy alerts and persistent incidents. As a bonus, it helps our users surface and escalate lingering issues that have been masked by the cadence on on-call alerts, focusing on other important issues:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/956/1*OJm9vXu0YkbE_tiEcShi2Q.png" /><figcaption>The good case: our internal users attacking the real issues.</figcaption></figure><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fdde502%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Fhackernoon%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href">https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d47dde9e780e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hackernoon/pagerduty-analytics-with-python-periscope-d47dde9e780e">PagerDuty Analytics with Python &amp; Periscope</a> was originally published in <a href="https://medium.com/hackernoon">HackerNoon.com</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Getting Ramped-Up on Airflow with MySQL → S3 → Redshift]]></title>
            <link>https://medium.com/hackernoon/getting-ramped-up-on-airflow-with-mysql-s3-redshift-defcc4522c8c?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/defcc4522c8c</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[airflow]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Mon, 30 Jul 2018 16:56:03 GMT</pubDate>
            <atom:updated>2019-05-22T06:58:20.604Z</atom:updated>
            <content:encoded><![CDATA[<p>I recently joined <a href="https://www.plaid.com">Plaid</a> as a data engineer and was getting ramped up on<a href="https://medium.com/airbnb-engineering/airflow-a-workflow-management-platform-46318b977fd8"> Airflow</a>, a workflow tool that we used to manage ETL pipelines internally. If you’re totally new to Airflow, imagine it as a souped-up crontab with a much better UI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zd-Z796er5lxaFW81EaWGA.png" /><figcaption>It’s fast! It’s flexible! It’s free! It’s Airflow!</figcaption></figure><p>Around the time that I was joining, Plaid was migrating onto<a href="https://www.periscopedata.com/"> Periscope Data</a> for visualizing SQL queries, and my immediate mission became to get more of the data people relied on for analytics insights into our nascent <a href="https://hackernoon.com/tagged/redshift">Redshift</a> cluster, the data warehouse we query from Periscope. My colleague had spent some time harvesting our MongoDB tables into Redshift by tailing the oplogs and doing some post-processing in Spark, as well as parsing through a bunch of log dumps in S3 to create meaningful analytics tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Dt4pTxhZXWN8CtwF" /><figcaption>Plaid ETL pipeline circa early 2018</figcaption></figure><h4>Motivation</h4><p>Kindly, my coworker left a more straightforward task to me to help me get ramped up with Airflow — moving data regularly from MySQL to Redshift. We had recently begun using Amazon Aurora instances on RDS, and needed to harvest the data from RDS and load it into Redshift to establish KPIs for these new datasets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0hMmiPfougfcSfmz" /></figure><p>Our goal was to timeframe this exercise to a week, so we set ourselves some constraints:</p><ol><li>We were going to use MySQL’s ability to select into s3, and Redshift’s UNLOAD command.</li><li>We would only perform complete table-copies once every day, for every table. Since our largest table took only a few hours to copy, we decided to accept pulling all of the data every night as both easier to implement and more resilient to changes or incongruities in the two data sources.</li><li>We would work on getting the most important data, and not worry about details like deeply nested json columns or binary image files stored in the database.</li></ol><h4>Designing a workflow</h4><p>We wanted to design a translation from MySQL to Redshift, and knew that there would have to be a translation of the schema. Fortunately, AWS provides a resource <a href="https://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-template-redshift.html">comparing MySQL and Redshift types.</a> We can pull the schema out of MySQL using a straightforward query:</p><pre><strong>select</strong> <br>  table_name, column_name, data_type, ordinal_position, character_maximum_length <br><strong>from</strong> <br>  information_schema.columns <br><strong>where</strong> <br>  table_schema = database();</pre><p>We handled a few types manually, for example instead of moving binary data over we would detect a binary type and instead return a boolean as for whether the column was null or non-null, to avoid having to copy a large amount of binary data over the wire that would be unusable for analytics. We wrapped the functionality into some python scripts that generates translation configurations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/830/0*Nx5B0xgn3StVNWDS" /><figcaption>Example MySQL configuration</figcaption></figure><p>We then created dag_mysql_processor.py to take in these database configurations and generate the associated dags. It iterates through each entry and generates the corresponding step using a series of SQL templates that we wrote using the<a href="https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html"> AWS Docs</a> as a guide. For example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ikFXRIHAMiyyWScJ" /></figure><p>The result is a batch of Airflow DAGs, one for each table in a MySQL Database. Currently we have each of these DAGs running once daily, which provides a good-enough latency for our current use-cases, by completely re-building the table once a day.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bslSQlbJWpEU6gLVQEcBhQ.png" /><figcaption>Our linear DAG pulling data from MySQL to S3 to Redshift</figcaption></figure><h3>Bumps in the Road</h3><p>The first bit of trouble came about from trying to do a hot-swap. We wanted to ensure that the table looked correct before replacing the old data, so we added a step to validate the row counts. In the first version of our DAG, we executed each statement as a separate airflow task, but tables could occasionally disappear. Combining the swap step into a single, transactional task prevents any table downtime for our style of full-table replacement.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NvjBzDLxrkNvLFWPAhj8OQ.png" /><figcaption>V1 of the project had a race condition in dropping and recreating the table.</figcaption></figure><p>We anticipated that full-copies would be troublesome, as some of our tables are many billions of rows and a global “success or fail” — a consequence of only doing full table copies — would be hard to recover from in real-time. We ran a few experiments on some of our largest tables to understand where errors may be occurring, and stl_load_errors quickly became our go-to debugging <a href="https://hackernoon.com/tagged/tool">tool</a>.</p><pre><strong>select</strong> * <strong>from</strong> stl_load_errors <strong>order by</strong> starttime <strong>desc</strong> <strong>limit</strong> 1;</pre><p>Some of these issues we’re in translating columns from MySQL types to Redshift types. We were able to resolve a good handful of these issues by adding some logic to account for specific types during the conversion and to account for patterns in our schema, which we were able to match based on column name and type.</p><pre>potential_binary_column = <br>  <strong>&#39;md5&#39; in </strong>column_name <strong>or &#39;uuid&#39; in </strong>column_name</pre><p>Another culprit of errors are newlines present in the data, which causes a line to get split into two (or more) rows which then fail to parse. The solution here was to use a consistent and purposeful delimiter on either side of the equation. From mysql:</p><pre><strong>select <br>  </strong>{{ info.columns }} <br><strong>from <br>  </strong>{{ info.table_name }}<br><strong>INTO </strong>OUTFILE s3 &#39;{{ info.s3_path }}&#39;<strong><br></strong>FIELDS TERMINATED <strong>BY &#39;,&#39; </strong>enclosed <strong>by &#39;&quot;&#39; </strong>escaped <strong>by &#39;\\&#39;<br></strong>LINES TERMINATED <strong>BY &#39;\n&#39;<br></strong>MANIFEST <strong>ON<br></strong>OVERWRITE <strong>ON<br></strong>;</pre><p>And pairing it with the Redshift query</p><pre><strong>TRUNCATE</strong> &quot;{{ info.schema_name }}&quot;.&quot;{{ info.table_name }}&quot;;<br><strong>COPY</strong> &quot;{{ info.schema_name }}&quot;.&quot;{{ info.table_name }}&quot; <strong>from </strong>&#39;{{ info.s3_path }}.manifest&#39;<strong><br></strong>iam_role &#39;{{ PLAID_REDSHIFT_ROLE }}&#39;<strong><br>DELIMITER</strong> <strong>&#39;,&#39;<br>TRUNCATECOLUMNS<br>EMPTYASNULL<br>ACCEPTINVCHARS<br>IGNOREBLANKLINES<br>NULL AS &#39;\\N&#39;<br>MANIFEST<br>REMOVEQUOTES<br>STATUPDATE ON<br>MAXERROR </strong>{{ info.max_error }}<strong><br>ESCAPE<br></strong>;</pre><h3>Dawn of a New Day — The Intern Arrives</h3><p>Instead of hard-coded schema files, we want to move the configuration files into a persistent storage layer which we planned on calling dbdb, or database-database — a moniker we are inheriting from members of our engineering team who worked on a similar system at Square.</p><p>We received the feedback to not call it dbdb around the same time that Michael Troute joined us for the Summer as a software engineering intern. He identified a need for Plaid to support ad-hoc uploading of CSVs to our Redshift cluster, and we agreed to combine CSV uploads, DBDB, and other planned ETL improvements under the broader umbrella of Data Warehouse Management.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8Iv1PLLUhgAXF07H" /></figure><p>This paid immediate dividends for our development team. When we first started working with Airflow there were a handful of DAGs governed by a pair of configuration files, but the addition of several MySQL databases and an increasing suite of use cases led to the team having to open several pull requests just for configuration changes. Storing the state in the data warehouse manager instead lets us more easily modify the system— automatically adding and removing tables and columns as they get added or dropped from the upstream database and adding custom functionality like setting Redshift sort and distribution keys, and deploying better methodology for our database ingestion.</p><p>Some of our tables are billions of rows, and many of these tables are either “append-only” or “sliding-window” updates. For append-only tables the proverbial “low-hanging-fruit” is to only query new rows from the database. For other tables, the rows that are updated are usually created within the past few days, so a mixture of full, partial, and incremental updates will make sense here.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fHWtykoaK8rucWuS" /><figcaption>Iterative loading, easily controlled maintaining state in the Data Warehouse Manager</figcaption></figure><h3>Alternative Solutions</h3><p>We took a foray into using existing solutions that would save us the engineering effort. Our favorite tool out of this investigation was<a href="https://www.matillion.com/"> Matillion</a>, and if you’re looking for a drag-n-drop style data pipeline tool, I would definitely check them out. They offer an Amazon Machine Image (AMI) that you can deploy inside of your AWS infrastructure and have great support for these kinds of problems. Ultimately, we identified that our use-case required too much external processing for Matillion to be the right solution for us.</p><p>If you’re looking for a cloud provider, we’ve had success using <a href="https://www.stitchdata.com/">Stitch Data</a> for data processing pipelines, <a href="https://aws.amazon.com/blogs/aws/fast-easy-free-sync-rds-to-redshift/">this post from Amazon</a> recommends additional vendors, and <a href="https://blog.panoply.io/how-to-move-your-mysql-to-amazon-redshift">Panoply</a> recommends a similar method using mysqldump.</p><h3>Conclusion</h3><p>Airflow has been a reliable tool for us and is an important part of our in-house ETL efforts. Plaid works with many different data sources, and for non-sensitive datasets + 3rd-party data<a href="https://www.stitchdata.com/"> Stitch</a> and<a href="https://segment.com/"> Segment</a> have been instrumental in building up data workflows. For our data under our on-premise security umbrella, Airflow has shown itself to be reliable, informative, and accessible to new members of the team getting ramped up on data ingestion at Plaid.</p><p>Plaid has a blossoming data ecosystem spanning many billions of rows. If you’re an airflow veteran or live and breathe ETL pipelines we’d love to chat! We’d especially love to open-source our efforts: if you like working on these kinds of problems or think this solution could help your own use case, we’d love to get in touch in the comments below.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fdde502%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Fhackernoon%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href">https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=defcc4522c8c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hackernoon/getting-ramped-up-on-airflow-with-mysql-s3-redshift-defcc4522c8c">Getting Ramped-Up on Airflow with MySQL → S3 → Redshift</a> was originally published in <a href="https://medium.com/hackernoon">HackerNoon.com</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A lesson my mother didn’t intend to teach]]></title>
            <link>https://medium.com/@AustinBG/a-lesson-my-mother-didnt-intend-to-teach-cf33df10fe36?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/cf33df10fe36</guid>
            <category><![CDATA[lgbtq]]></category>
            <category><![CDATA[identity]]></category>
            <category><![CDATA[christianity]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Thu, 05 Jul 2018 00:07:00 GMT</pubDate>
            <atom:updated>2018-07-05T16:33:59.909Z</atom:updated>
            <content:encoded><![CDATA[<p><em>This story is part of a collection of essays on civil rights.</em></p><p>My mother cared about her children more than anything in the world. She was sick for many years with cancer, but she never once complained or moved us to change our behavior because of it. I was not an easy son — I was struggling with my own insecurities and I would lash out in anger when she would push us. She was pushing me to be better, to have vision and maturity beyond my adolescence, but when my father would say that she was sick and ask for kindness, I didn’t understand. In my eyes she was always so strong.</p><p>I did understand that she was our champion. When an English teacher falsely accused my sister of plagiarism, she came down on the school like a fury. When I was lazy and procrastinated on a project — a situation I put her into more than once, I’m afraid to say — she jumped on me immediately and helped me drive it to completion. As children she wouldn’t let us have sleep overs, because she knew that is where many teenagers experimented with drugs and alcohol , and though we complained and were upset of being denied this part of childhood we understood that she was doing it because she cared about us and wanted to protect us.</p><p>As she loved us she so to cared for a great many people. She was thoroughly sincere and fiercely loyal. When she passed away, the funeral director estimated more than a thousand people attended the wake, with cars being turned away as we had overflown the parking lot. She was accepting and welcoming, and touched the lives of a great many people in our community.</p><p>I knew this about her, and knew that despite her traditional, conservative catholic values she did not engage in the homophobia or hatred that besotted a generation of the religious right. There is only one occasion that I remember being surprised by her actions, by seeing her put her religion before a modern, more progressive stance. Part of what makes it stand out is how trivial it was. We were active members of our church. I was an altar boy, went to Sunday school, and sung in the church choir — despite not being a particularly talented singer — because it’s what my mother wanted. In the eighth grade we went through a confirmation ceremony, one of the sacraments of the church that connects you to God and cements your relationship with the catholic faith. As part of the process you select a confirmation name from one of the saints, read their stories and adopt them as your guide. That’s how I became Austin Bernard Christopher Gibbons, and adopted St. Christopher to watch over me on my travels, just as he had watched over Jesus when carrying him across a torrential stream.</p><p>Many of my friends chose a name because it was funny to do so: Matthew T. chose St. Blaise because it sounded like blaze, and he was one of my friends who was amenable to the “drugs and alcohol” part of sleepovers that my mother was so worried about. I had not set out to become Austin Bernard Christopher Gibbons. I originally selected Joan of Arc, the <em>Maid of Orléans</em>, because I had been playing Age of Empires II and they feature an entire campaign around her story. Age of Empires got me to read more history than any of the classes I had ever taken, and the story of the Hundred Years’ War and St. Joan’s role in the liberation of Orleans was exactly what I wanted in a hero — young, prophesied, victorious, and playable in video game format. I chose St. Joan of Arc as my patron saint for confirmation, and was prepared to become Austin Bernard Joan Gibbons, fully aware that this was a female name but unabashed because Joan of Arc was a badass, sneaking across the French countryside in men’s clothing to save her people. Also, I could tell that this whole confirmation thing just didn’t matter that much.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*HdDiuFi86eekt87b.jpeg" /><figcaption>St. Joan of Arc, adorned in men’s armor</figcaption></figure><p>I nevertheless remember being a little nervous telling my mother. I was a difficult child at times, and I usually knew when I was being difficult. I wanted to exert my own identity and wanted to take stances and establish myself. I think I picked this battle precisely <em>because</em> I thought it was unimportant, that it was something I could win and use to confide to myself my own uncertainties around gender and sexuality. But, I lost. For the first and only time that I remember from my childhood, my mother took a hardline stance with her faith and established that boys-should-be-boys. It wasn’t a grand battle. I didn’t cite visions of the Archangel Michael or wield my banner at <em>les Tourelles</em>. The entire exchange probably lasted three minutes. I stated my intentions and she balked. I tried to stick to my guns but was rebuked and deflected to St. Christopher. I was embarrassed, I came around, and that was the end of it.</p><p>There are a great many moments from my childhood where I can look back and see that I was wrong. There are only a few moments that I look back and still believe that she was in the wrong. My mother did so much to shape who I am, to instill beliefs and independence into my character, and I see myself becoming more of who she wanted me to be every year. But I am so too shaped by these few moments where I still disagree and fight her. In rejecting St. Joan as my patron saint, she was wrong. It’s almost too easy to say that now. Living in San Francisco during the 2015 supreme court case that decided for same-sex marriage was a joyous occasion, but not a surprising one. Gay Rights were already seen as the de facto standard across the city, and we were just waiting for the rest of the country to catchup. Now in 2018 the energy being poured into transgender equality carries the same spirit — advocates are fighting against the outdated, the entrenched, the backwards folk who don’t understand a progressive agenda; those who have failed to learn the lessons are stuck in their old ways, but the advocates will triumph in the end.</p><p>While I don’t agree with the dissidents of the transgender movement, I have empathy for their resistance. They see headlines like <em>Why the Trans Bathroom Debate is Utterly Ridiculous</em> and balk, especially if they’ve conceded that loving couples can do what they will in their own homes and their own communities They paint a picture of transgenderism entering their lives and draw the contrast. They are unwilling to confront the unfamiliar and find excuses to reject change from their faith, their community, or just from “the way things are”. Sometimes I wonder where my mother would be on this issue. Would she find sympathy for people struggling with their identity, or would she harbor that “God doesn’t make mistakes”? Shamefully, I’m sometimes relieved that I don’t have the answer to that question. But in that shame I probe inwards and understand I am not just afraid of being embarrassed by the dated sentiments of an aging generation, I’m afraid that many of my own values and even my self-identity are rooted in attitudes formed around the institutionalization of right and wrong, where stubbornness forbids an open heart and open conversation.</p><p>Joan of Arc met her demise amidst fear and anger; she was convicted of cross-dressing by her enemies mock her and embarrass her people. My own inquiry towards the other gender was met not with hatred or disgust but indifference, a casual adherence to the times. I was seen not as a visionary heroine that had to be burned at the stake, but a bratty son who was not taking the Sacrament of Confirmation seriously enough. My mother wanted me to feel the same pride and respect she had for her community, but I am left instead asking if I am pushing an agenda of obliviousness against those facing their own questions of identity — will I play the role my mother played, of rebuking someone’s self-expression out of indifference?</p><p>My fiancée is ancestrally Taiwanese, and my entire family comes from Ireland. Suffice it to say our 23andMe results were not particularly insightful. Our children will be faced with questions of identity that were never presented to their parents, even simple things like a survey that has a single choice for ethnicity will ask them to define themselves in a way that will require us to have a conversation with them about who they are, where they come from, and let them know that the world won’t always be receptive to biracial people. I’m not afraid of that question. Being <em>hapa</em> in the Bay Area won’t put them into particularly unique circumstance, and their immediate community likely will never challenge or pressure them in a way that other parts of the world might. I’m not afraid of what I will have to explain to them, but I am afraid that some day they will have to explain something to me. There are presently fringe issues already generating commentary like Kat Callahan’s <a href="https://groupthink.kinja.com/is-trans-ethnic-really-a-thing-492878854">perfect post</a> analyzing trans-ethnic conversations. I’ll echo the conclusion here:</p><blockquote>When they try to place themselves into new boxes; they’re ultimately missing the point. They have freedom to adopt these labels <em>because</em> of all of the work previously done by feminist and queer activists to break down the constructed concept of “heterocisnormativity.” What they don’t seem to realise is that ultimately, it is the destruction of the previous labels which was the whole purpose.</blockquote><p>My aspirations for my own children, should they want to take a new direction with their own ethnic identity, are to be open, honest, and receptive, to share what I know of the history of identity in America and be sympathetic of youthful viewpoints. However, and triumphantly, the hard work and conversations around demographic construction already have many inroads, and modern day trailblazers are laying a foundation for both discourse and introspection. I want to instill in my children the same determination and independence that my Mother gave me, and I am left hopeful that the conversations I have with my children will be to teach them a new lesson of self-expression and respect for identity.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cf33df10fe36" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Learning to Recruit as a Software Engineer]]></title>
            <link>https://medium.com/hackernoon/learning-to-recruit-as-a-software-engineer-25a36df527d1?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/25a36df527d1</guid>
            <category><![CDATA[recruiting]]></category>
            <category><![CDATA[hiring]]></category>
            <category><![CDATA[interviewing]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[interview-tips]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Wed, 19 Jul 2017 20:52:17 GMT</pubDate>
            <atom:updated>2019-05-22T06:58:10.166Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*achu0c2gLSXlm_TaqxzbfA.png" /><figcaption>Snowy and Tin-Tin on the hunt for talented Software Engineers</figcaption></figure><p>When working at small startups in the bay area you will undoubtedly find yourself wearing <em>many-hats. </em>I myself have served stints as Editor-in-Chief of a technical blog, was a member of both Office Beautification and Holiday Party Planning Committees, and led a series of Lunch-and-Learns on the importance of data collection. The hardest - by far - of these various caps, casquettes, and bonnets, was recruiting.</p><p>During my first full-time employment at <a href="https://quantifind.com">Quantifind</a> I was involved in an interviewer role for both software <a href="https://hackernoon.com/tagged/engineering">engineering</a> and machine-<a href="https://hackernoon.com/tagged/learning">learning</a> / data-science roles. During the past two years at <a href="https://www.periscopedata.com">Periscope Data</a> , I expanded my recruiting experience from beyond just software engineering interviews to covering the entire recruiting gamut. At various points I was a recruiting coordinator, candidate sourcer, hiring manager, and brand ambassador. I helped design interview pipelines, re-imagined the on-site experience, and worked with third-party tools and partners to expand our recruiting capabilities.</p><p>I am spewing this list of experiences not in the hopes of astonishing you with my recruiting repertoire - let me be the first to tell you that I am far from finished learning - but in the hope that I can offer some advice to anyone in the Same position as I was first tasked with helping to grow to twenty-something person startup.</p><h3>Recruiting Review Checklist</h3><p>Okay, so you just volunteered to <a href="http://42hire.com">help with recruiting</a>. Take one long deep breath as you turn your otherwise two-meetings-a-week calendar into this monstrosity of half-hour time slots:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E5XymTcSlpqd6O3aruBUXg.png" /></figure><p>Before you can each achieve this back-to-back goodness, you need to build a steady stream of candidates. Let&#39;s talk about how to get that done.</p><h3>Vocabulary</h3><p>Before you begin your recruiting adventure, you should learn the lingo. When we first started looking to bring recruiting in-house, we thought we could just hire “a recruiter” and they would take ownership over everything. This turned out to be about half-true. In actuality recruiting is broken down into several roles and responsibilities, and it is important to understand who plays what part:</p><ul><li>Crafting a <strong>Job Description </strong>is where this all starts. Before you can hire someone, you need to establish the role and requirements for candidacy. Doing so will help candidates match their experiences to your position and facilitate in-house communication about candidates and constructing interviews.</li><li><strong>Sourcing</strong> is the process of finding candidates to fill your recruiting pipeline. The term sourcing may imply either direct outreach or the broader realm of both cold outreach, referrals, managing your online presence, and participating in real-life events like meetups and conferences. Ultimately, anything that fits into the domain of introducing your company to qualified candidates fits here.</li><li><strong>Coordinating</strong> &amp; <strong>Scheduling </strong>are two sides of the same calendar management coin. Coordinating is the realm of working with a candidate to find a date and time that works well for them, arranging travel and hotel booking or reimbursement, and working with the scheduler (who may be the same person) to ensure the people they need to meet are available when the candidate is on-site. Scheduling is the process of booking time on the interview team’s calendars and making sure that the chosen interviewers make sense in the context of the overall interview, which is part of our next topic:</li><li><strong>Pipeline Design </strong>is the act of guiding candidates from initial interest right up to where they get an offer. Interviews take many forms: culture-fit phone calls &amp; coffee chats, technical phone screens, take home problems, previous work samples, or anything else that will help you focus in on the candidates ability to do the job. When you are designing a pipeline you need to understand not just how it will allow the right candidates to succeed in your interviews, but also the reflection of your company’s culture that is imparted on the candidates.</li><li><strong>Closing</strong> is an art-form all of it’s own and the area of recruiting that separates the good from the great. After you’ve made an offer to a candidate, how do you convince them to accept the offer and join your company? Closing a candidate should be in the forefront of the recruiting teams mind from the very first conversation.</li><li><strong>Candidate Experience</strong> gets it’s own slot away from the rest of pipeline design, in part because it is usually governed by other factors that may be outside of the pipeline designers control. Would you readily believe that a candidate is more likely to be engaged with your company if they speak to energetic, enthusiastic, and happy interviewers? For me personally, it’s the largest deciding factor once I have already committed to interviewing with a company.</li></ul><p>The candidate experience is the careful study of every interaction your candidate has with both team members during interviews and scheduling, but also within the environment in which they are interviewing. Is your office bright and energetic? Is it located in an accessible, happening part of town, or would it make sense to arrange car service to and from your headquarters? We were fortunate that we had the opportunity to design and decorate our relatively-empty office around both existing employees working environment and the candidate’s on-site experience. Starting with a fixed budget, we decided to focus on the entry way to the company with not just the working environment in mind, but also (very much so) hoping to impart a proverbial wow-factor when introducing the space to our candidates, instead of just being desks in a warehouse.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WLwRGSTFqdL-m2coo8vmtw.jpeg" /><figcaption>Our previous warehouse office space in the Dogpatch, shortly after moving in. Photo courtesy Chris Meier.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cO-vu24ZNb-6GIr_F7weRw.jpeg" /><figcaption>Panorama from our original lounge, featuring our indoor patio and painted logo in the background.</figcaption></figure><h3>Matching Vocabulary to People</h3><p>Squaring in our mind all the steps in recruiting, we can see each of these domains comes with an associated role. The <em>Recruiting Sourcer</em> sources. The <em>Recruiting Coordinator</em> coordinates and schedules. Easy enough. The important individual to mark when hiring a role is the <strong><em>Hiring Manager</em></strong>. This is the individual ultimately responsible for approving the hiring of a candidate, and they are working with the coordinators, schedulers, sourcers, head of HR, and office experience team to get that done.</p><p>I would strongly counsel the hiring manager, or their chosen proxy, be highly involved in the entire experience: designing the pipeline, deciding who is involved in what parts of the interview, ensuring the interviewers know what their interview covers: what criteria they should (and should-not) be evaluating within candidates and most important <em>why</em> they are evaluating the candidate along their rubric. A mistake I saw more than once was the hiring manager not being sufficiently involved in the process, electing to defer too much of the responsibility to the other roles. This led to numerous incidents:</p><ul><li>A candidate not being greeted properly when they entered the office space, causing them to start the day confused on whom to approach.</li><li>Interview questions being considered not right for the role ex post facto.</li><li>Interviewers asking what question they should be asking minutes before their interview.</li><li>Most crucially of all, failing to close more than one candidate because of an imperfect on-site / closing experience.</li></ul><p>My primary recommendation to anyone recruiting for a role at a small organization would be for a single individual to collectively review the entire process to ensure it all worked well from the candidate’s perspective.</p><h3>Pipeline Design</h3><p>During my tenure we iterated through several different approaches to our recruiting pipeline. Let me first recap my own experience in being recruited before highlighting some of the changes with which we experimented.</p><p>Periscope generally prefers “moving quickly”, and this definitely extends to recruiting. All told, it was 16 days from first contact to accepting an offer — we can trace the original engineering recruiting pipeline from our email exchange:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*njoz86gehcrEj0dTviz5lg.png" /></figure><ol><li>I reached out to Periscope on a Tuesday.</li><li>32 minutes later the CTO responded and we setup a chat for that Friday.</li><li>Thursday evening a reminder email, clearly from a human, not a robotic system.</li><li>30 minute Google Hangout meet-and-greet. Mostly culture fit, with a small piece of technical discussion. Next steps are outlined as being a code-screen.</li><li>We schedule a code screen over Hangouts for the following Tuesday via email.</li><li>Tuesday afternoon we do a coding exercise, next steps will be an on-site interview.</li><li>We setup an onsite that evening for next Monday.</li><li>Monday arrives! It’s a small team, so the interview involves meeting all of the technical team members. We work through a coding assignment to implement an isolated feature that’s part of their product over the course of four one-hour interviews. Half-way through we have a lunch break with the entire team.</li><li>After the interview is over, the CEO walks me out of the office and extends an offer. I state I am excited about the company and would like to follow-up offline, so afterwards they email me a digital copy.</li><li>From there, we have a few emails/phone-calls back and forth before I eventually accept!</li></ol><p>This was the format that they had been using for a quite awhile on either side of my interview with Periscope. Note that this interview format targets software engineering individual contributors, and it does not dive into other topics like infrastructure &amp; architecture design, front-end/design experience or a background in engineering leadership. There were informal conversations around these kinds of topics with candidates in these directions, but largely to pitch the candidate how they could fit within the team rather than qualify the candidate as a potential engineer. Let’s explore a little further in depth the different modifications to the pipeline that we explored.</p><h4>Phone Screens, Challenge Problems, and Coffee Chats</h4><p>The top of the funnel is where we were able to be the most flexible. You can experiment with different formats — namely the titular phone screen, challenge problem, and/or coffee chat — as well as pursuing candidates that showcased different types of strengths: be it a better culture fit, deeper experience in a particular engineering domain, or working with candidates from many different backgrounds.</p><p>Much of the inbound interest we received was from candidates that we would not reach out to given their resume. At the time that I was involved in hiring, we generally did not consider any new college or bootcamp graduates, in addition to other reasons for passing on a candidate. Recruiting displays a similar property as the <a href="https://en.wikipedia.org/wiki/Friendship_paradox">Friendship Paradox</a>, engineers are more likely to reach out if they are someone who struggles to attain employment. This is not to say they are not good engineers! Some people struggle with canonical interview formats, lack experience but have promise, or just haven’t been in the right situation to showcase their skills. We initially were responding to less qualified candidates with a message along the lines of “We’d love to chat in the future, but there isn’t a good role right now.” This was, after all, the truth! But as an experiment to increase recall we issued a programming challenge problem that asked users to implement code to move a robot around a grid and collect items. While it <em>did</em> demonstrate sufficient coding abilities to warrant further conversations with candidates for whom we would have otherwise passed, it ultimately did not lead to an individual hire. The data volume was too low to draw a meaningful conclusion but given the time that was invested on both the candidates end for programming the assignment and our end for digesting the assignment, it is not something I would do again. There are some more exciting directions in this idea around 3rd-party tools that I would be more keen to try if I was going to pursue this idea for a second time, we’ll touch upon this idea again when talking about sourcing candidates.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*Cq7YDzW4uUS1skT3T8NQAA.png" /><figcaption>Laszlo Podor Photography/Moment/Getty Images</figcaption></figure><p>Coffee chats, or more accurately in my case chai tea latte chats, were an almost successful way to increase outbound success in the funnel. I met up with several candidates from my coffee chat request email copy, and without offering too much evidence, I anecdotally felt I had a better success rate at the top of the funnel by offering to meet in person. This format ultimately lead to only a single offer who unfortunately didn’t close, so I can’t quite call it a success. I ultimately stopped making this offer to meet for coffee because it was fairly taxing on both time and, quite frankly, my chai tea latte budget.</p><h4>Culture-fit Interviews &amp; The Sell</h4><p>Your first conversation is a de facto cultural interview. Unfortunately, and for whatever reasons, some candidates enter a conversation with such a poor attitude that no amount of professional ability will make you want to work with them and the process stops right then and there. On the flip side there are some candidates that will click so well that you start praying they make it through your technical interviews. For the vast majority of interviews, this interview is you selling your culture to them, not the other way around. Once it’s evident that someone is in the realm of “friendly human being”, this conversation is no longer a qualifying interview, but an opportunity to convince the candidate that your startup is truly the one which they’ve been seeking.</p><p>My meet-and-greet phone call flow was</p><ol><li>Hellos &amp; Introductions</li><li>Product demo. This worked well because it was short, the entire thing would only take 2–3 minutes. It was, naturally, a shallow tour of the product, but it allowed me to showcase the product’s out-of-the-box wow-factor of creating charts from sql queries as fast as I could type.</li><li>Follow-up. A lot of people were fine taking my “Look! Charts!” demo at face value, but many others, especially people who had worked on similar problems, had immediate follow-up questions on what-all had just happened. This could be a single question or dove-tail into a 15 minute conversation, all with the goal of convincing the candidate we were a serious tech shop building cool technologies.</li><li>Tech-stack rundown. Ultimately whether we had a follow up product/tech conversation or not, I would do a run-down of the programming languages and technologies we use as a segue to inquire about what kind of role for which they were looking. Especially considering we were not interviewing many recent graduates, people had opinions and interests about the kinds of technologies and environments they wanted as their day-to-day working experience.</li><li>When chatting with several professional recruiters about closing candidates, they consistently highlighted that most, perhaps even all, of candidates had already decided whether they would take the job or not before ever coming onsite. Once the conversation had shifted to what the candidate was looking for in potential employers, we would chat about the other criteria that was important to them. My favorite approach was along the lines of “If we thought we were a good mutual fit for each other, why would you not join the company?” This question allowed for a very open and honest conversation, with responses across the board of “you know it’s honestly just kind of far from where I live in Redwood City” to “truthfully I’d really like to work with a functional language like Clojure”. This helps assuage fears (we’re right next to the caltrain, and have lyft credit!) or be honest that our ruby-on-rails + golang tech stack might not be the right fit.</li><li>Eventually I also started asking for target salary figures. This was another hedge on selling the candidate, as our monetary compensation (at the time at least) was fixed to software engineering levels (SWE-1, SWE-2, etc.) which was determined by interview performance outside of the context of compensation. In the many hundreds of interviews I gave, I only ever had one candidate who quoted a number so high that it made sense to stop the interview process, as nearly everyone gave a range that covered our target compensation.</li></ol><p>At the end of the interview, if there were no show-stopping red-flags, we would setup a second interview for the next business day. Other interviewers included a short technical conversation as part of this conversation, between steps 4 and 5, as an effort to increase the accuracy of this step. I initially had this in my interview as well, but at the time I was optimizing for recall and rarely disqualifying a candidate from that conversation so I ultimately dropped it to leave a more positive first impression on the candidate.</p><h4>Technical Problem Design</h4><p>We had been using the same phone screen question since before my own interview. Rather than disclosing their actual problem here, I’ll prompt one with similar properties:</p><p>A coworker provides you with a CSV of t-shirt orders, with dates, sizes, colors, brand, and so forth. Can you</p><ol><li>Print which colors are the most popular on weekends?</li><li>Write a method that takes in a t-shirt color and returns the distribution of sizes a user should order.</li><li>A user provides how many t-shirts of what color and size they sold. What two colors should they add to their repertoire to increase their sales the most?</li></ol><p>The idea of this questions is to start slow and build more complex solutions as you go, a pattern I have observed in many structured coding exercises. The first goal is to get some code running printing a summary statistic, then to increase the complexity of that statistic, and finally a more involved computation with a natural solution left open-ended enough to provide an opportunity for algorithmic discussion.</p><p>The problem hopefully allows the candidate to showcase a familiarity with coding that would highlight their ability to program in a professional environment: data organization, appropriate refactoring, clean &amp; well-decomposed code, and so forth. We were generally happy with our precision using this problem, we had good intuition for how well someone would do on-site from their performance on this assessment. Our recall was too costly to measure, as we could not invite candidates that we would have otherwise passed on to interview onsite for a number of obvious reasons. That being said, we frequently found “borderline” candidates were ultimately people that did not pass our onsite interview, since the two technical challenges were fairly similar.</p><p>Even as I wrote that paragraph I can sense the immediate feedback that this is an ineffectual interview style and that the four hour take home problem is the one true interview format, or what have you. I agree that this style is imperfect, but I still felt it was effective. After sitting in a half dozen or so of these interviews, clear patterns of behavior crop up and it feels possible to draw a positive correlation with on-site interview performance. Ultimately we had to do the best we could with the time constraints we had, and this format is what we stuck with.</p><p>After a handful of interviews, an interviewer is generally primed to know the outcome of the interview no more than half-way through the interview. For candidates that did well, we would schedule an onsite interview while still on the hangout. This level of comfort allowed me to pursue some flexibility in the question, for example if it was generally going well I could cut out the actual algorithmic coding in favor of an algorithmic conversation. To borrow words from another interview pipeline designer: “it is more about probing for signal than qualifying the actual solution to the problem”.</p><h4>On-site Experience</h4><p>The technical content of our onsite interview was similar in both solution and spirit to the previous interview, but put into the context of building an actual working feature for the product. We were explicitly evaluating the candidate in six categories: coding speed, code quality, algorithmic understanding, communication abilities, enthusiasm &amp; excitement, as well as “shipitude and initiative”, internal lingo embodying whether candidates were focused on getting working code written (shipitude) and/or investing in the design and architecture of the code (initiative) especially in the context of off-hand comments that people frequently made along the lines of “I would probably refactor this into a method”. Wherein the other categories a higher score was strictly better, shipitude and initiative embody competing (though not opposing) dimensions on the engineering spectrum. As such, they were most useful as conversations about why we thought a candidate was awesome rather than requiring a specific score.</p><p>Having done other types (i.e., whiteboard style) of interviews in the past, I am a strong proponent of our hands-on-keyboard, real-product-problem approach. It made it much easier to get a sense of what it would be like to work with a candidate and was less of a “probing for signal” and evoked more of a real analysis of the work product: if someone opened a pull-request with this change, how much feedback would be needed to get it production ready? When offering this feedback, do they take it well and make appropriate changes? Do they recognize patterns in feedback and adjust course appropriately? These were among the less explicit, internalized grading we were doing as interviewers.</p><p>A special note I want to make is that I casually observed the same conclusion that was the result of a <a href="https://mobile.nytimes.com/2013/06/20/business/in-head-hunting-big-data-may-not-be-such-a-big-deal.html">famous Google study</a>: almost no one is good at predicting job performance from interview scores.</p><blockquote>Years ago, we did a study to determine whether anyone at <a href="http://topics.nytimes.com/top/news/business/companies/google_inc/index.html?inline=nyt-org">Google</a> is particularly good at hiring. We looked at tens of thousands of interviews, and everyone who had done the interviews and what they scored the candidate, and how that person ultimately performed in their job. We found zero relationship. It’s a complete random mess, except for one guy who was highly predictive because he only interviewed people for a very specialized area, where he happened to be the world’s leading expert.</blockquote><p>Working with the engineers that we did hire, my unscientific conclusion is there was no noteworthy correlation between interview performance and on-the-job performance. In part, this is because I broadly loved working with my former teammates, but even considering my own dearth of data points I readily believe Google’s conclusion.</p><h4>The Close</h4><p>I only had the opportunity to extend an offer to one person with whom I was involved in hiring, and fortunately for me they were already quite excited to join the company thanks to it being an in-house referral. That being said, I saw several different strategies:</p><ul><li>Making an offer (handing over a contract) immediately after a successful interview.</li><li>Explicitly informing candidates that we would be making an offer, but would follow up with details later.</li><li>Stating that we were excited about working together and would discuss details “when it was the right time for them”.</li><li>A closing coffee chat without such an explicit or implicit “yes”, and following up with an offer later.</li></ul><p>These were different attempts at trading off expediency and removing pressure from candidates, not even to mention factors like salary, equity, benefits, title, and many other levers that can be pulled on an individual basis to make a successful hire.</p><p>Several (but not all) of the positions we were hiring for had open head-count; if there was a candidate that we were excited about, we were able to move forward without considering what other offers had been made or who else was in the funnel. This helped facilitate experimenting with closing technique, since it more clearly makes the goal to close the candidate as fast as possible. Many of the steps that were taken were to reduce time-to-offer, the goal here was to enable candidates to move at their own pace and make the best decision for them outside of the time pressures of “exploding offers”. While this idea was laid out with the best of intentions, it highlighted why time-limited offers exist. I was left with a feeling that people tend to get more excited about the company whom with they recently interviewed than one they visited days or weeks ago. Ultimately I did not see solid enough results to recommend doing anything outside of the norm: making an offline offer very shortly after an interview with a “good-until” date.</p><h4>The Accept!</h4><p>The last component is also the most exciting. After making an offer the conversation turns back to selling your company to the candidate, highlighting all the stated pros and diminishing the identified personal tradeoffs outlined in the first interview. When a candidate does (excitingly!) accept an offer you want to return the same excitement and enthusiasm that they have for you back to them. We tried a few variants here:</p><ul><li>Having the entire team email short congratulatory messages. At different points we experimented doing this both before a candidate accepted an offer and afterwards. I myself set some emails that were long-form prose laying out my excitement at having met them and some short messages with complementary dancing gifs.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*xTvaX7JWn9MsMRj5JueEFQ.gif" /><figcaption>The first gif I sent to a candidate. Needless to say, they accepted.</figcaption></figure><ul><li>At some point, our team became large enough that a candidate did not necessarily meet everyone during their interview process. At different points we had the whole team sending messages, just the interview team, and sometimes a singular select individual.</li></ul><p>I can’t really say one worked better than the other, and I suspect the right choice will be evident by the existing culture of your team. Additionally, the hiring manager would keep in contact with the new-hire, providing status updates, check-ins, and introductions to team members involved in on-boarding.</p><h3>Filling the Pipeline</h3><p>Okay, we’ve thought a lot about what the experience should be but we may have jumped the gun — we need candidates to interview! Let’s switch gears and talk about introducing our company to job seekers. How we will find them (or have them find us), what kind of information we are showcasing, and how we can keep track of it all.</p><h4>Online Presence</h4><p>What do you do when you are first interested in learning more about a company. Do you perhaps … google them? Congrats, you are the same as everybody else, and your candidates will be doing the same thing about your company. We should make sure the information out there reflects the exciting, passionate, and fun group of people that are working at your startup!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*fHH7LeaEfeSJklcltRvXRg.png" /><figcaption>I estimate 100% of candidates will visit your glassdoor page.</figcaption></figure><ul><li>Have you claimed your Glassdoor account, uploaded your logo, and politely inquired into whether your co-workers have left any positive reviews about their working and/or interviewing experience?</li><li>LinkedIn: you’re company page, with logo, backdrop photo, about us, open positions, connections, and more. Equally importantly, you need to make sure your fellow engineers have exciting personal profiles that just so happen to mention how great it is working at your company. If I had to venture a guess, I would say you were recruited for recruiting by virtue of being the bubbliest, most charismatic, or perhaps just the loudest amongst your peers. When scouting your company, potential candidates will almost definitely scan through the people who work there on LinkedIn, and imagine if they are people beside whom they would want to work. It is imperative that their profiles are selling your company, highlighting why your culture is amazing, how explosively your product is exploding, and why your customers love you. Like any other form of marketing, you have to speak to your audience and make them understand how your offering (employment) not only solves their problem (unemployment) but provides the greatest value: cultural energy, enthusiastic co-workers, and highlighting your best perks are all great ideas. One thing that I really want to emphasize is encouraging people to draft their own copy with minimal editing. Not only will it provide better variety to your reader, but it will help candidates understand if this is indeed the right fit for them. Nothing speaks more loudly than the truth, after all.</li><li>Now that you’re an expert in setting up a company profile page and gently persuading your co-workers to update their own presence, rinse and repeat that idea across all of the classic professional social media sites. Twitter, AngelList, Crunchbase, and Facebook are all worth visiting. When you are uploading pictures and drafting content for these pages, avoid duplicating photos and instead distribute different content and copy on different websites to impart more of an “exciting, vibrant, dynamic” company vibe, and not “someone uploaded the same stock photo everywhere” vibe. Depending on where you are in your company life-cycle, this can be challenging. If you don’t already have an employee with a DSLR camera and a score of well-documented company events, where are these photos coming from? Some recruiting platforms, like <a href="https://www.themuse.com/">The Muse</a> will bring a photographer or videographer on site and help build out your visual portfolio. Otherwise a quality modern smart-phone with purposeful lighting can do the trick.</li><li>Different candidates will find the content you post on different sites, but they will all have one thing in common: your website! If you’re looking to grow a team to the point where you are reading this blog, you probably have already taken steps to experiment with A/B testing on your home page and are comfortable with the idea of selling from your landing page. You may not have thought about expanding these ideas in the recruiting direction. Is there a call to action from your <em>about</em> and <em>job</em> pages? Does the content on your blog speak to candidates, or is it exclusively product announcements? I am not necessarily recommending you spend your design team’s valuable time on multiple about pages and running true A/B tests, but mindfulness of your audience is just as important in recruiting as it is in sales and marketing.</li></ul><p>Drafting a <strong>job description</strong> deserves more than it’s own bullet point or paragraph, it’s easily a blog post in its own right. However, the abridged version of our experience followed these steps:</p><ol><li>Read through other existing job descriptions to build a flavor and feel for common styles and language.</li><li>Draft a post describing (1) what the candidate would do in their first day, week, month, and year (2) what a candidate’s work history might look like (3) notes about the existing team and company, and (4) recent projects we had worked on as a team. Later we would add in an additional section describing the interview process itself, highlighting our hands-on-keyboard approach.</li><li>Run the job description through text analysis tools like <a href="http://gender-decoder.katmatfield.com/">http://gender-decoder.katmatfield.com/</a> and <a href="https://textio.com/">https://textio.com/</a> that help highlight biases that exist in word choice.</li><li>Share with other members of the recruiting team for gentle editing.</li><li>Post to the world!</li></ol><h3>Tools of the Trade</h3><p>After you’ve reviewed your brand’s online presence through a recruiting lens, you need to set up your machinery and mechanisms for driving your recruiting engine. Starting out you might do something simple, like keep it all in a spreadsheet, but you will quickly find that operating at scales of sending 10+ emails a day or working on recruiting with co-workers begets necessity for an Applicant Tracking System, or ATS. There’s a <a href="http://www.capterra.com/applicant-tracking-software/">huge list</a> of options out there and today we’ll talk about the two I’ve actually used: Lever and Greenhouse.</p><h4>Applicant Tracking System (ATS)</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Syu-FgaleXzLH4V3HBWilg.png" /></figure><p>The co-founder and CEO of Lever has a background in design, and it shows. Lever is a beautiful product. It’s grey-scale interface is a little much at first, but I quickly came to appreciate not only it’s sleek &amp; flat design, but the simplicity and usability of it’s interface. What I loved Lever for was its pipeline analytics overviews.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/610/1*kYCn0a-vQMr_F6XJdj0RRg.png" /><figcaption><a href="http://www.forentrepreneurs.com/recruiting/">http://www.forentrepreneurs.com/recruiting/</a></figcaption></figure><p>The structure has a very natural navigation to it that wasn’t present in some of the other ATSs that we evaluated. It was easy to step into specific sections of the pipeline and look into individual candidates movement through our recruiting pipeline. We were working at candidate volumes in the hundreds, so it was feasible to look through every single candidate to understand what was going on in the overall system, and Lever did a great job of facilitating this type of exploration.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*JIoeN60OmoBULlKQg_ZaMw.png" /></figure><p>When I speak to a recruiter at a San Francisco startup and ask them about their ATS, I expect them to say Greenhouse. While Lever and other competitors like Jobvite and Workable all have sizable fan-bases in the bay area, Greenhouse is frequently the name that comes up first. It’s a fully-developed product with extensive permission-ing and candidate management capabilities. I unfortunately first became involved with Greenhouse when they were moving from monthly to annual pricing and had to forgo using them for awhile. Ultimately we adopted Greenhouse when recruiting went from “many-hat” hiring managers to an actual HR team. If you’re a well-funded startup and looking for an ATS for the for-seeable future, you aren’t making a mistake with Greenhouse, but it will work best with someone whose job responsibilities include managing the tool. Most of my exposure was in filling out greenhouse report cards and perusing the candidate list to track our pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OaH3mdhkTWfO_wJtJL07JA.jpeg" /><figcaption>The official Greenhouse candidate scorecard stock photo</figcaption></figure><p>Greenhouse was the first ATS we used to fill out scorecards, our previous model being a shared excel document where we would copy paste a template. For this purpose, Greenhouse shines. It allows for optional and mandatory fields, structured note taking, and only shows other interviewers responses when you’ve filled out your own. The oddity here is the requirement for the five-field scale. We had happened to use a four point scale prior to switching to Greenhouse, so we had to entertain a slight adjustment phase before settling into the five-scale scheme.</p><p>Working with Greenhouse analytics was generally approachable, but I did come across a few quirks. It wasn’t always straightforward to pull the exact numbers I wanted out of the candidate pipeline, and I occasionally had to manually click through each of the categories — for example, each of the seven different reasons we had internally for not proceeding with a candidate after an offer was made — to understand what was happening in our recruiting funnel. While it does a good job of exposing the data from an active pipeline viewpoint, I often felt I wanted to drill-down into slices of data that weren’t readily accessible, and would do some pen-and-paper tracking of candidates to ensure I had the entire picture.</p><h4>Content Resource Management (CRM)</h4><p>In addition to an ATS, you will want something to help you manage your email. If you have been involved in sales or marketing roles, I won’t be preaching anything new — the same tools for managing your sales pipeline make sense in recruiting. Content Resource Management tools, or CRMs, are used across business organization to organize information and your email is no different. Email clients like Gmail have a lot of organizational capabilities built-in, including automatic tagging, filtering, and nested folders, but you can take your email to the next level with an email tracking tool like <a href="http://www.yesware.com/">Yesware</a> or one focused around pipelines like <a href="https://www.streak.com/">Streak</a>, in addition to dozens of other tools as covered by this <a href="https://resources.workable.com/tutorial/best-email-tools">great blog post</a> from Workday.</p><p>The tools I used the most often were Yesware and <a href="https://hunter.io/">Hunter</a>, which helps you find someone’s email address and offers a convenient chrome plugin to help guess addresses when your are perusing LinkedIn. Yesware and Hunter are one of many choices in the crowded email discovery and management spaces — if you have sales and marketing teams you should check if you already have licenses for other tools like Hubspot, Radius, and of course, LinkedIn.</p><h3>Sourcing Candidates</h3><p>You’ve done the legwork and you’re armed for battle. At this point you might even be thinking that recruiting is pretty fun! Now it’s time to embrace the most challenging aspect of our task: sourcing candidates. There are going to be many avenues you pursue, and no one of them will be the silver bullet. Recruiting is a numbers game on both sides of the equation: candidates should pursue many opportunities at once, and companies want to see a large volume of candidates to fit people who will best excel in their environments.</p><h4>Direct Outreach</h4><p>Welcome to trying to find your own candidates. This will involve a mixture of crawling LinkedIn, Github, StackOverflow, and AngelList. It’s possible (perhaps even likely) that I was just bad at this, but it was challenging doing direct reach “on the side”, while also working the rest of the recruiting pipeline and also occasionally doing my actual job of writing code. I won’t even try to recover my terrible conversion numbers, but getting a real workable pipeline doing your own sourcing requires a huge amount of time, and I now strongly believe it is not worth it. If you aren’t building multi-year relationships with a large number of individuals, have access to a database of candidate emails, and are only pitching a single role at a single company, then you are already a step behind professional recruiters and unlikely to find it’s worth the time and effort.</p><h4>Inbound Candidates</h4><p>That time we spent thinking about our online presence will hopefully help drive some inbound interest in your company. We can further boost inbound interest by advertising roles on traditional advertisement networks and there are important non-advertising channels as well, namely the Hacker News who’s hiring thread. <a href="https://news.ycombinator.com">Hacker News</a> is Y-Combinator’s news aggregator and is quite popular among the tech community, particularly so in SF. Every month on the first of the month they offer the opportunity for non-YC companies to post a job advertisement (which they are otherwise not permitted to do). The post regularly gets several hundred companies contributing a job description, and in my experiences this was responsible for a sizable portion of inbound interest. Most importantly, several of the hires that we made were Hacker News inbound candidates, myself included, and it is my immediate recommendation to both candidates and companies when they are asking about the software engineering job market. By posting to this thread in the proper format you should automatically be added to websites that aggregate the postings from those threads, such as <a href="https://whoishiring.io/">https://whoishiring.io/</a> and this <a href="http://gaganpreet.github.io/hn-hiring-mapped/src/web/">hiring map</a> from Gaganpreet.</p><p>Hacker News is certainly not the only strategy; we also ran a few Facebook ads targeting software engineers in addition to running ads for the product itself that frequently targeted the same audience. While I don’t know for sure if any of the software engineers we hired came through a Facebook ad, I do know that a data analyst we hired found us through a product advertisement. Other sources contributed to inbound interest — our company’s blog is technically focused and included, at various points, a “come work with us” call to action. Whereas direct outreach was hugely time-intensive, these efforts were not particularly costly in time, effort, or financially, so even without a massive effort to track the success, it felt appropriate to pursue.</p><h4>External Recruiters</h4><p>There’s something of a stigma around full-time, third-party recruiters. Often software engineers I speak to bemoan the emails and phone calls that they continually receive. Nevertheless, recruiters provide a valuable service and many of them do so with gumption. They are certainly worth considering, but working with external recruiters is expensive, taking 10–20+% contingency fee on the first year’s salary of a hire they introduce to you. A poor recruiter relationship can also be time-intensive, as it can be challenging to align your expectations around candidates: in addition to managing the relationship with the candidate, you have to iterate with the consultant and explain your hiring decisions. Ultimately, your incentives are not perfectly aligned. It can feel like they are working towards optimizing recall while you’re trying to calibrate the optimal recruiting precision where you can have a manageable workload. Especially when considering the relatively low initial time investment I would recommend giving it a try if you are prepared to pay the contingency fee.</p><h4>In-house Referrals</h4><p>I led a short campaign to get a paid referral program started at Periscope and as part of that effort polled my friends working in the Bay Area for the size of their in-house engineering referral bonus. Most companies that I spoke to had one, with payouts ranging from $1,000 on the low end to $20,000 on the high end. We ended up on $5,000 as it was both the median and mode from my short survey and we opened it up as a $5,000 bonus for most roles across the organization.</p><p>I’ve seen a few different strategies employed to drive referrals. The most common was to sit down with the individual leading sourcing and walk through LinkedIn friends lists to build a spreadsheet of people to reach out to, whether the referrer should be named when reaching out, as well as whether it is a professional, personal, distant, or LinkedIn-only acquaintanceship. If you are doing this with more than a small handful of accounts, it is worth exploring a tool like <a href="https://teamable.com/">https://teamable.com/</a> that can help organize and search your collective networks.</p><p>Other approaches I have heard used, like referral pizza parties or cash bonuses just for submitting resumes, also seem like viable strategies. When we later adopted Greenhouse as our ATS it provided its own workflow for referrals, the ease of which I felt helped encourage submitting referrals.</p><h4>Focused Hiring Platforms</h4><p>When I started writing this novel it was an exercise in brain-dumping content and I did so with the purest of intentions. Between then and now I have joined the remote interviewers program at Triplebyte, my favorite of the platforms I will talk about below. So please take this salt grain as you read my opinions of hiring platforms, though I do hope you will believe I am sincere.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ek6xKiD-vplmW00T_GA5IQ.png" /><figcaption>digest with a grain of salt, courtesy pxleyes.com</figcaption></figure><p>This is the place where I am the most excited. It is also the place that is experiencing the most rapid growth. Between when I first started searching for recruiting platforms a year and so ago and the publication of this post, I have discovered at least twice as many such products. It is possible that all of my recruiting google searches have allowed for better Facebook ad targeting, but I do believe that many of these companies are less than a year old.</p><p>In the preliminary phone call, the one that was mostly about selling the candidate, I would sometimes ask what other companies with which they were interviewing, and people were generally forthcoming. Especially when I was excited about a candidate, and doubly-especially when I was excited about a candidate but didn’t think our company was the right fit for them, I wished that I could simply <em>go</em> to the other companies and recommend the candidate. Well it turns out I was far from the only person with this idea, and there are a plethora of companies entering the domain of providing technical qualification quizzes and interviews as part of their sourcing of candidates.</p><p>At its heart it is a simple idea — look upwards in the pipeline, merging the technical qualification piece into the third-party sourcer. Champions of these platforms paint a beautiful picture of how “hiring is broken” and they are “re-inventing the technical interview”, but at a macro level they are still recruiters introducing candidates to companies. The major innovation is the technical pre-qualification that, for example, facilitates Triplebyte customers to bring candidates directly on-site, greatly shortening the internal pipeline. There are several other advantages that these platforms offer on to both the interviewees and the hiring companies. Having been involved in so many engineering interviews, they are able to greatly surpass my coarse intuition-driven lessons-learned, and instead model how a candidate will do in onsite interviews from the earlier stages, with enough data to draw real, meaningful conclusions. The evidence shows: more than 50% of the introductions they make (i.e., onsite interviews) result in an offer-made, which is well above the numbers I experienced myself and have gleaned from chatting with other hiring managers.</p><p>There are other platforms that are more like traditional job boards, but targeted at software engineers. Hired is my favorite of these examples, though I will concede it is a little gimmicky. The shtick is they remove a good portion of unconscious bias in resume processing, as they only initially display a candidate’s initials — no name, no bio, no photo. Unconscious (and conscious) bias is not a topic we will fit into this post, but I recognize that it is a challenge for anyone involved in hiring and appreciate organizations that are helping to reduce the impact of bias from their recruiting pipelines. The other angle Hired plays is that companies communicate target salary numbers immediately upon reaching out to a candidate — though I do not think they are not obligated to abide by that number if they do make an offer. The result is the creation of a sort of silent, blind auction with even greater nuances than a standard auction and the accompanying game theory to think about, but in our experience it wasn’t a huge deal and probably helped make downstream compensation conversations a little easier.</p><p>I have only just brushed past the tip of the recruiting platform iceberg. Without doing them full justice, here is a list of recruiting companies worth investigating if you are looking to accelerate hiring:</p><ul><li><a href="https://triplebyte.com/">Triplebyte</a> handles both sourcing and technical evaluation of candidates, moving them straight to the on-site portion of your recruiting pipeline. Other companies are also involved in the technical pre-screening space, like <a href="https://www.yodas.com/">Yodas</a>, <a href="https://codefights.com/talent">CodeFights</a>, and <a href="https://www.hackerrank.com/">HackerRank</a>.</li><li><a href="https://hired.com/">Hired</a>, <a href="https://www.vettery.com/">Vettery</a>, and <a href="https://woo.io/">Woo</a> are software-engineering focused hiring platform that emphasizes blind resume screening, a modern company-seeking-candidate reverse job board.</li><li><a href="https://www.themuse.com/">The Muse</a> adopted the AirBnB model of bringing a photographer on-site to shoot your space and build a beautiful profile. They work closely with your hiring team to set up photos, videos, and written copy selling your company. They then showcase your space through their site and will mention you in their recruiting articles, with titles like <a href="https://www.themuse.com/advice/20-amazing-tech-companies-to-work-for?ref=whos-hiring-0">20 Amazing Tech Companies to Work For</a>, that they work into their wide array of articles targeting job seekers.</li><li><a href="https://www.pramp.com/">Pramp</a> offer mock interviews to candidates, free of charge, which they do by having each person play both interviewer and interviewee, alternating roles. <a href="https://www.zenterview.com/">Zenterview</a> is an interesting entry in the mock interview space as it seems to have a “pay what you want” model.</li></ul><p>There seems to be a new entry into a different part of the recruiting space every month. In our list here, we have not even touched more traditional job boards - <a href="https://42hire.com/tagged/angellist">AngelList</a>, ZipRecruiter, Glassdoor, <a href="https://42hire.com/tagged/linkedin">LinkedIn</a>, Indeed, and many others all help software engineers find jobs. The space is huge, and growing.</p><h3>Go Forth, and Recruit</h3><p>If you&#39;ve stuck with me this far into the blog post, then I hope you feel equipped to turn on your recruiting engine and ramp up hiring. What has your experience been in recruiting been? Drop a note if you see something missing from the software engineer recruiting landscape. We talked about pipelines and sourcing today - we could dive even further into building the job description, managing the pipeline as a hiring manager and even sourcing candidates and further exploring even wider territory - getting co-workers involved in recruiting, building a Consistent pipeline instead of need-based hiring, diversity in hiring, and much more.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/520/1*h89Ki0tmxQj4WfJg4kNmQw.png" /></figure><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fdde502%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Fhackernoon%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href">https://medium.com/media/3c851dac986ab6dbb2d1aaa91205a8eb/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=25a36df527d1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hackernoon/learning-to-recruit-as-a-software-engineer-25a36df527d1">Learning to Recruit as a Software Engineer</a> was originally published in <a href="https://medium.com/hackernoon">HackerNoon.com</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[You think timezones are bad now? Just you wait.]]></title>
            <link>https://medium.com/future-travel/you-think-timezones-are-bad-now-just-wait-91e181a80eb4?source=rss-5358551e83a3------2</link>
            <guid isPermaLink="false">https://medium.com/p/91e181a80eb4</guid>
            <category><![CDATA[time-travel]]></category>
            <category><![CDATA[science-fiction]]></category>
            <category><![CDATA[physics]]></category>
            <dc:creator><![CDATA[Austin Gibbons]]></dc:creator>
            <pubDate>Thu, 01 Oct 2015 07:06:28 GMT</pubDate>
            <atom:updated>2018-06-18T20:12:23.901Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/825/1*0ayupxp7o863w1tNnxLoPA.jpeg" /></figure><p>What do sales folk, web programmers, and MMORPG clan leaders have in common? We all have to work with with people who are only online when we are asleep. My sincerest appreciation to my dear friends in London, Israel, and Singapore for your everlasting patience and our 12 hour ping-pong emails.</p><p>Of course, this does not hold a candle to the issues that some brave programmers have had to handle. I love <a href="https://www.youtube.com/watch?v=-5wpm-gesOY">this video from Computerphile</a> describing the insanity of timezones if you have yet to have had the pleasure of running into one of the boundless pitfalls, or here’s a fun example that I recently encountered while researching the <a href="https://en.wikipedia.org/wiki/America/Sao_Paulo">Sao Paulo timezone</a></p><blockquote>Between 1963–10–23 and 1963–12–09 in Brazil only Minas Gerais, Espirito Santo, Rio de Janeiro and Sao Paulo had summer time, but on request a split from America/Sao_Paulo was rejected with the reasoning that since 1970 the clocks were the same in the whole region</blockquote><p>What a dilemma. What time was it in fall of 1963 in Santa Catarina one second after 1963–10–22 11:59:59? Because of regional drought, they did not observe summer time that year, so for the people actually experiencing that time it was 1963–10–23 12:00:00, but because it happened before 1970, the start of Universal Coordinated Time, it does not differ from the standard Brasília Summer Timezone (BRST), where the time was 1963–10–23 01:00:00. What are you going to build into your Historic-Brazilian-Time-Calculator? Quoth Computerphile:</p><blockquote>What you do is you put away your code, you look at the first people … and you thank them very much for making it open source … and you never ever look at it again, for that way lies madness.</blockquote><p>If you are an engineer with these problems checkout <a href="http://momentjs.com/">moment</a>, it has been enormously helpful at Periscope and I recommend it whole heartedly.</p><p>But hold your horses! This is not another enumeration of the dangers of timezones: I drew you in with the U.S.S. Enterprise picture, remember? This is a whole new problem that the future coders of the world will have to handle: <strong>relativity</strong>. Yup, space ‘n stuff. You think you have trouble converting from PDT to BRST today, let me pose to you this question:</p><p><strong>You’re having a friendly chat with Captain James T. Kirk, and he signs off with “Great, let’s rendezvous Friday at oh-seven hundred hours” and drops the sub-space communication link. It’s currently 7:00 am on Tuesday. How many hours until your meeting?</strong></p><p>You have 72 hours until you check-in with Captain Kirk, right? Well there’s a problem — The U.S.S. Enterprise is traveling at 30,000 km/s, roughly one tenth the speed of light and you just so happen to be at rest in the universal reference frame. When three days has elapsed for Kirk, you’ll have experienced <a href="http://www.wolframalpha.com/input/?i=time+dilation+calculator&amp;a=FSelect_**TimeDilationRelativistic-.dflt-&amp;a=*FS-_**TimeDilationRelativistic.t-.*TimeDilationRelativistic.to-.*TimeDilationRelativistic.v--&amp;f3=4320+minutes&amp;f=TimeDilationRelativistic.to_4320+minutes&amp;f4=30000+km%2Fs&amp;f=TimeDilationRelativistic.v%5Cu005f30000+km%2Fs">an additional 22 minutes</a>; your meeting starts at 7:22!</p><p>In the future, if there are spaceships traveling at <a href="http://blogs.scientificamerican.com/life-unbounded/the-fastest-spacecraft-ever/">speeds attainable today</a> of 30 km/s — a mere one hundredth of a percent of the speed of light — the spaceship would observe around 1.5 less seconds every ten years. Added to the list of timezone rules based on location, year, season, <a href="http://972mag.com/the-worlds-only-ethnic-time-zone/81006/">religion</a>, and the <a href="https://en.wikipedia.org/wiki/Leap_second">dynamic speed of the earth</a> will come other problems from humans moving at relativistic speeds or being near a gravitational well. Timezones will have to evolve to become dynamic with respect to each other, it will not be simple enough to use static offsets that change at most once every few months — they will be continually become further out of sync while under the effects of time dilation, and yet even this will not be deterministic as the ship accelerates and decelerates on its voyage across the galaxy.</p><p>Fortunately <a href="http://www.astronomy.ohio-state.edu/~pogge/Ast162/Unit5/gps.html">Satellites in use for GPS</a> are actually dealing with this problem today; to an extent someone is already foraging this territory. There may come a day, however, when you ask someone what timezone they observe and receive the answer ERT: Enterprise Relative Time</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=91e181a80eb4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/future-travel/you-think-timezones-are-bad-now-just-wait-91e181a80eb4">You think timezones are bad now? Just you wait.</a> was originally published in <a href="https://medium.com/future-travel">Future Travel</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>