<?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 James Isbell on Medium]]></title>
        <description><![CDATA[Stories by James Isbell on Medium]]></description>
        <link>https://medium.com/@bigdatajames?source=rss-c42688422712------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*banmb-OYLlccUP1OwVMlAg.jpeg</url>
            <title>Stories by James Isbell on Medium</title>
            <link>https://medium.com/@bigdatajames?source=rss-c42688422712------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 23:31:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bigdatajames/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[Implementing True Data-Layer Governance on Snowflake]]></title>
            <link>https://medium.com/snowflake/implementing-true-data-layer-governance-on-snowflake-3473b84d3f48?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/3473b84d3f48</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[streamlit]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[snowflake-rbac]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Fri, 04 Sep 2026 19:01:01 GMT</pubDate>
            <atom:updated>2026-09-04T19:01:01.181Z</atom:updated>
            <content:encoded><![CDATA[<h3>Implement True Data-Layer Governance on Snowflake</h3><h4>Using inherited caller grants to unlock data layer governance in Streamlit and React</h4><p>Every application that queries Snowflake through a service account starts the same conversation: who can see what, do we need a new role, how do we filter the data for this user. The answer compounds over time (new roles, new grants, filtering logic rebuilt in code) until the app layer is running a second permissions model in parallel with the one you already maintain at the data layer. Inherited caller grants collapse your governance back to the data layer: the user’s existing Snowflake permissions govern what they can see, in every surface, automatically.</p><p>This post walks through setting up an account-wide caller role and implementing <a href="https://docs.snowflake.com/en/developer-guide/restricted-callers-rights">caller’s rights</a> in both Streamlit and React/Next.js apps on SPCS. Within Snowflake, our data teams have adopted this model, allowing us to:</p><ul><li>Define governance exactly once</li><li>Apply that governance model to every customer facing surface</li><li>Avoid additional role creation and grants</li></ul><h3>Owner’s Rights vs. Caller’s Rights</h3><p>Snowflake stored procedures and app runtimes default to owner’s rights: queries run as the app owner role, regardless of who is actually using the app. This is the same model used by traditional BI tools connecting through a shared service account. That model is useful for dashboards that should show the same data to everyone but a liability when you need per-user row filtering or column masking.</p><p>Caller’s rights flips this: queries run with the privileges of the user who invoked the app. Row access policies see the actual user. Masking policies apply. Object grants are checked against the user’s role hierarchy. The app itself doesn’t need to implement any of this — it falls out for free from the data layer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Er_LADCBV6xdnRJxRMQ7Cw.jpeg" /><figcaption>Image generated by Gemini</figcaption></figure><h3>What Are Inherited Caller Grants?</h3><p>A caller grant is not a data permission. Granting CALLER SELECT on a table doesn’t give a role the ability to read that table: the calling user still needs SELECTon that table themselves. The caller grant authorizes the app to use the caller’s existing SELECT privilege when it runs. Without it, that privilege is walled off even in caller’s rights mode.</p><p>A regular <a href="https://docs.snowflake.com/en/sql-reference/sql/grant-caller">GRANT CALLER</a> scopes a caller-rights permission to a single, named object:</p><pre>GRANT CALLER SELECT ON TABLE hr.compensation.salary_bands TO ROLE my_app_role;</pre><p><a href="https://docs.snowflake.com/en/user-guide/inherited-grants-using">Inherited grants</a> (via GRANT INHERITED CALLER) elevates this to all objects of a given type across a schema, database, or even account. Critically, it applies to future objects, removing the need to execute explicit FUTURE GRANTS.</p><pre>GRANT INHERITED CALLER SELECT ON ALL TABLES IN ACCOUNT TO ROLE snowflake_caller_rl;</pre><p>This means you create the caller role once and it remains valid as new schemas, tables, and services are provisioned. No re-granting for each new object.</p><h3>Account Setup</h3><p>One note before running this: inherited grants are <strong>currently in public preview</strong>. You’ll need to opt in at the account level first — see the <a href="https://docs.snowflake.com/en/user-guide/inherited-grants-intro">documentation</a> for the opt-in step.</p><p>At Snowflake, we have created a single caller role and loaded it with inherited select and usage grants covering every object in our account. Note that this covers read only operations for our apps — write operations can be handled separately, either through this same caller framework, or through owner’s rights, depending on the use case. Example syntax is below:</p><pre>-- Create the caller role<br>CREATE ROLE IF NOT EXISTS snowflake_caller_rl;<br><br>-- Inherited caller grants for all object types in the account<br>GRANT INHERITED CALLER USAGE ON ALL AGENTS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL MCP SERVERS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL CORTEX SEARCH SERVICES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL DATABASES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL DYNAMIC TABLES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL EVENT TABLES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL EXTERNAL TABLES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL FILE FORMATS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL FUNCTIONS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL MATERIALIZED VIEWS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL PROCEDURES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER USAGE ON ALL SCHEMAS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL SEMANTIC VIEWS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER READ ON ALL STAGES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL TABLES IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br>GRANT INHERITED CALLER SELECT ON ALL VIEWS IN ACCOUNT<br>  TO ROLE snowflake_caller_rl;<br><br>-- Make it universally available<br>GRANT ROLE snowflake_caller_rl TO ROLE PUBLIC;</pre><p>Granting to PUBLIC means every user and role in the account automatically has this role. Because it contains only caller grants, not direct object privileges, this does not expose any data. A user can only reach objects they already have permission to access through their own role hierarchy (including via <a href="https://docs.snowflake.com/en/sql-reference/sql/show-caller-grants">SHOW CALLER GRANTS</a>).</p><h3>When to Stick with Owner’s Rights</h3><p>Caller’s rights isn’t always the right choice. Owner’s rights are appropriate when:</p><ul><li>The app is a public dashboard where all users should see the same aggregated data</li><li>You want uniform access for all users and you don’t need per-user filtering</li><li>You’re building a service-account-style integration where the app acts as a principal in its own right</li></ul><p>But, for anything where per-user visibility, masking, or audit trails matter, caller’s rights is the right default.</p><h3>Caller’s Rights on Streamlit (SPCS)</h3><p>For developers coming from Looker or Tableau, think of this caller’s rights implementation as a user-delegated connection: each query executes with the individual user’s identity, not a shared service account.</p><p>Once the caller role exists, Streamlit developers can build apps that completely decouple the app owner’s role from the data it queries. If any role in the account has SELECT on a table with row access or masking policies, the app will respect those policies for each individual user, without a single line of filtering code.</p><p>The connection setup requires two things: using the caller’s rights connection rather than the default connection, and using session-scoped caching to prevent data from one user’s session leaking into another’s.</p><pre>import streamlit as st<br>import pandas as pd<br><br>DATA_CACHE_TTL = 300<br><br># Module-level — the caller token is valid for only 2 minutes from session start.<br># Do not create this connection conditionally or on a non-landing page.<br>conn = st.connection(&quot;snowflake-callers-rights&quot;)<br><br># Use session scoped cache to prevent leakage across user sessions<br>@st.cache_data(ttl=DATA_CACHE_TTL, scope=&quot;session&quot;)<br>def run_query(_conn, sql: str) -&gt; pd.DataFrame:<br>    df = _conn.query(sql)<br>    df.columns = df.columns.str.lower()<br>    return df</pre><p>In local development snowflake-callers-rights will throw an error outside of a container runtime. For local testing, swap to st.connection(“snowflake”) (owner’s rights) and point that connection at your account in connections.toml.</p><p>This implementation comes with two important caveats:</p><ul><li><strong>SPCS only:</strong> Caller’s rights connections work only for SPCS-powered Streamlit apps. Legacy warehouse-compute apps do not support this pattern.</li><li><strong>Version requirement</strong>: <a href="https://docs.snowflake.com/en/developer-guide/streamlit/features/restricted-callers-rights">Restricted caller’s rights</a> require Streamlit v1.53.1 or later. Error messages in older versions are opaque: if caller grants aren’t behaving as expected, version is the first thing to check.</li></ul><h3>Caller’s Rights on Snowflake App Runtime</h3><p>React / Next.js apps on the <a href="https://docs.snowflake.com/en/developer-guide/snowflake-app-runtime/about-snowflake-app-runtime">Snowflake App Runtime</a> (in preview) follow the same principle, but you assemble the token combination yourself.</p><p>SPCS provides two tokens: a service token at /snowflake/session/token (identifying the app itself), and a per-request user token via the sf-context-current-user-token header (identifying the caller). Snowflake accepts the two concatenated as a single OAuth credential. You can use following code block example in lib/snowflake.ts:</p><pre>// lib/snowflake.ts<br>import fs from &quot;fs&quot;;<br>import { headers } from &quot;next/headers&quot;;<br>import Snowflake from &quot;snowflake-sdk&quot;;<br><br>const _pool = new Map&lt;string, Snowflake.Connection&gt;();<br><br>function getServiceToken(): string {<br>  return fs.readFileSync(&quot;/snowflake/session/token&quot;, &quot;utf8&quot;).trim();<br>}<br><br>async function getConnection(serviceToken: string, callerToken: string): Promise&lt;Snowflake.Connection&gt; {<br>  if (!_pool.has(callerToken)) {<br>    const conn = Snowflake.createConnection({<br>      host: process.env.SNOWFLAKE_HOST,<br>      account: process.env.SNOWFLAKE_ACCOUNT!,<br>      authenticator: &quot;OAUTH&quot;,<br>      // Combined token: service identity proves which app is calling;<br>      // caller identity proves who the signed-in user is.<br>      // Snowflake runs the query as the intersection of what each allows.<br>      token: `${serviceToken}.${callerToken}`,<br>    });<br>    await new Promise&lt;void&gt;((resolve, reject) =&gt;<br>      conn.connect((err) =&gt; (err ? reject(err) : resolve()))<br>    );<br>    _pool.set(callerToken, conn);<br>  }<br>  return _pool.get(callerToken)!;<br>}<br><br>export async function query&lt;T = Record&lt;string, unknown&gt;&gt;(sql: string, binds?: unknown[]): Promise&lt;T[]&gt; {<br>  const serviceToken = getServiceToken();<br>  const callerToken = (await headers()).get(&quot;sf-context-current-user-token&quot;);<br>  if (!callerToken) throw new Error(&quot;No sf-context-current-user-token header — is the app running in SPCS with caller&#39;s rights?&quot;);<br>  const conn = await getConnection(serviceToken, callerToken);<br><br>  return new Promise((resolve, reject) =&gt;<br>    conn.execute({<br>      sqlText: sql,<br>      binds,<br>      complete: (err, _stmt, rows) =&gt; (err ? reject(err) : resolve((rows ?? []) as T[])),<br>    })<br>  );<br>}</pre><p>And then downstream in API routes:</p><pre>// app/api/compensation/route.ts<br>import { query } from &quot;@/lib/snowflake&quot;;<br><br>// Caller&#39;s rights responses are per-user — never cache or prerender.<br>export const dynamic = &quot;force-dynamic&quot;;<br><br>export async function GET() {<br>  const rows = await query(<br>    `SELECT employee_id, band, salary FROM hr.compensation.salary_bands WHERE snowhouse_username = CURRENT_USER()`<br>  );<br>  return Response.json(rows);<br>}</pre><p>For raw SPCS services (not App Runtime), add executeAsCaller: true under capabilities.securityContext in your service spec. Without it, Snowflake won’t inject the user token header.</p><p><strong>Note on caching</strong>: if you cache query results, scope the cache key to include the caller token. Caching without a per-user scope will serve one user’s filtered data to another: the same risk scope=”session” guards against in Streamlit.</p><h3>Toward a Single Data Governance Layer</h3><p>At query time, Snowflake applies the calling user’s role, not the service account’s. Row access policies filter to what that user can see. Column masking applies. Object grants are checked against their full role hierarchy. If the user can’t see something in a worksheet, they can’t see it in your app.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3473b84d3f48" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/implementing-true-data-layer-governance-on-snowflake-3473b84d3f48">Implementing True Data-Layer Governance on Snowflake</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AI Ready RBAC]]></title>
            <link>https://medium.com/snowflake/ai-ready-rbac-57219d444aeb?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/57219d444aeb</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[snowflake-rbac]]></category>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[agentic-ai]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Tue, 21 Jul 2026 21:33:57 GMT</pubDate>
            <atom:updated>2026-07-21T21:33:57.887Z</atom:updated>
            <content:encoded><![CDATA[<h4>Data governance best practices for the agentic analytics era</h4><p>Most data teams have spent years working around their RBAC rather than through it. Access was controlled at the BI tool, filtered in the dashboard, hardcoded in the semantic layer. It mostly worked. Agentic analytics makes that debt visible and urgent.</p><p>Getting RBAC right is not just a governance checkbox. It is the prerequisite for confidently deploying AI agents that serve every user the right data, automatically, without per-user prompt engineering, without hardcoded filters, and without the risk of leaking data across organizational boundaries.</p><p>This post will walk through Snowflake’s own internal RBAC principles and implementation patterns, which have helped us securely scale our GTM assistant and other agents to thousands of internal users.</p><h3>Why agentic analytics is forcing this conversation</h3><p>For most of the history of enterprise data teams, access control was a BI tool problem. You built a dashboard, figured out who should see it, and configured row-level security in Tableau or Looker or Power BI. It mostly worked because the dashboard was the access surface. Control the dashboard, control the data.</p><p>The side effects accumulated quietly. Every tool had its own access model, its own syntax, and its own list of who was allowed to see what. The access policy wasn’t in one place; it was scattered across every surface that happened to touch the data, maintained by whoever built that surface, and impossible to audit in aggregate. Data teams weren’t building one access model; they were building dozens of them, slightly differently each time.</p><p>Agentic analytics changes the calculus entirely. When a user can ask a natural language question and get back a direct query against your warehouse, there is no dashboard in the middle to catch what shouldn’t be seen. Critically, many of those users will never have had access to Snowflake or your data platform directly, and now they’re being handed a natural language interface that queries the warehouse on their behalf. An AI agent doesn’t pass through your BI tool’s row-level security. It doesn’t know that someone configured a filter on a workbook two years ago. It queries the data directly, and whatever the database returns is what the user sees.</p><p>This brings us to the biggest boogeyman of the AI era: agents will take all available paths to satisfy user requests.</p><p>In the old world, many companies benefited from security through obscurity — users simply couldn’t find the sensitive data they may have had access to. In the new world, agents will relentlessly pursue every possible path to find data, probing your databases, schemas, tables, and columns until they find what they are looking for. This boogeyman isn’t a myth; it’s a feature.</p><p>This means the only access model that matters is the one at the database layer. Row access and masking policies attached directly to tables in Snowflake are enforced regardless of how a query arrives, whether it comes from a BI tool, a Cortex agent, a Jupyter notebook, or a raw SQL client. There is no way to route around them. A business user asking a question through a chat interface gets exactly the same row-level enforcement as a data engineer running a query directly. The access definition lives in one place, tested in one place, and reviewed in one place like any other piece of code.</p><p>The shift to agentic workflows didn’t create this problem; it made ignoring it impossible.</p><h3>Core principles</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uZFF0EkHpHZzLdf6lKFeaQ.png" /><figcaption>Layered RBAC architecture; generated by Gemini</figcaption></figure><p>Before we embark on any architectural pattern, it’s useful to align on guiding principles. As we build out our certified data layer at Snowflake, we’ve aligned on four sets of principles that have helped us scale our internal access policies.</p><ol><li>Broad access, fine-grained filtering</li><li>Source-system authority</li><li>Explicit override roles for broad access</li><li>Bring engineering principles to governance</li></ol><p>Together, these have eliminated most of the access request queue, removed the data team from access decisions they shouldn’t be making, and given us a governance model that holds up under agentic query patterns. Here’s how we implement each one.</p><h3>1. Broad Access, Fine-Grained Filtering</h3><p>Grant all human users schema-level RO access via a single role grant. Row Access Policies and Masking Policies do the heavy lifting of restricting data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*whvRCKheMTIqMbrkNN6A9w.png" /></figure><p>These layers are additive protections. A user must pass ALL three: (1) have the RO role granted, (2) pass the RAP filter on each row, (3) pass the masking check on each column. This means granting broad schema access is safe and policies enforce the real security boundary.</p><p><strong>Schema read-only role creation and grants</strong></p><pre>-- For every new schema in your user facing database, create a read only role <br>-- with current and future grants that can later be granted to a broad set of end users<br><br>-- Create + wire up<br>CREATE ROLE IF NOT EXISTS &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE ON DATABASE &lt;DB&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE ON SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br><br>-- Existing objects<br>GRANT SELECT  ON ALL TABLES                 IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT SELECT  ON ALL VIEWS                  IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT SELECT  ON ALL SEMANTIC VIEWS         IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE   ON ALL FUNCTIONS              IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE   ON ALL PROCEDURES             IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE   ON ALL CORTEX SEARCH SERVICES IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>GRANT USAGE   ON ALL AGENTS                 IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br><br>-- Future objects (same set with FUTURE)<br>GRANT SELECT  ON FUTURE TABLES                 IN SCHEMA &lt;DB&gt;.&lt;SCHEMA&gt; TO ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl;<br>-- ... (same pattern for views, semantic views, functions, procedures, cortex search, agents)<br><br>-- Wire into role hierarchy, e.g., vend to dev roles<br>GRANT ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl TO ROLE &lt;DB&gt;_dev_modeling_rl;<br><br>-- Once row and masking policies are in place, this single grant gives<br>-- every user access to the schema. The policies enforce the real boundary —<br>-- broad schema access is safe because RAPs and masking handle row and column filtering.<br>GRANT ROLE &lt;DB&gt;_&lt;SCHEMA&gt;_ro_rl TO ROLE &lt;CUSTOMER_FACING_ROLE&gt;; -- e.g., PUBLIC</pre><p>We have further optimized this workflow by creating a task that automates role creation and grants for every new schema that lands in our customer facing database.</p><h3>2. Source-System Authority</h3><p>Access rules are often already determined in source systems. Salesforce administrators make decisions about who has access to specific objects and rows of data, as do administrators of many third-party tools. Instead of maintaining a parallel access model inside Snowflake, we derive our RAP mapping tables directly from these source systems. When a rep’s territory changes in Salesforce, the next pipeline run updates their access automatically. When an employee leaves and is deprovisioned in Workday, their access is gone on the same schedule. The data team is not in the loop for either event, and that’s the goal.</p><p>Below is an example of how we build the core inputs for a row access policy leveraging workday hierarchies. This example can extend to other third-party tools (or against your own internal system), including Salesforce profiles and hierarchies, more granular Workday permission sets, and many more.</p><pre>-- Pre-compute the manager → reports access map once as a table.<br>-- Recursive CTE walks the full Workday org tree; output is flat rows:<br>--   (snowflake_username, viewable_employee_id)<br>-- Covers self-access and all direct/indirect reports.<br>create or replace table employee_access_mapping as<br><br>with recursive employee as (<br>    select<br>        workday_employee_id::int                                                        as workday_employee_id<br>        -- Null out self-referential manager links (top of tree) to avoid infinite recursion<br>        , iff(workday_employee_id = manager_workday_id, null, manager_workday_id::int) as manager_workday_id<br>        , is_active<br>    from workday_employees<br>    qualify max(snapshot_date) over (partition by workday_employee_id) = snapshot_date<br>)<br><br>, active_employee as (<br>    select * from employee where is_active = true<br>)<br><br>, hierarchy as (<br>    -- Base: each employee → immediate manager<br>    select<br>        e.workday_employee_id<br>        , m.workday_employee_id   as manager_workday_id<br>        , m.manager_workday_id    as next_manager_workday_id<br>        , 1                       as level<br>    from active_employee as e<br>    left join active_employee as m on e.manager_workday_id = m.workday_employee_id<br><br>    union all<br><br>    -- Recurse: climb one level higher each iteration<br>    select h.workday_employee_id, m.workday_employee_id, m.manager_workday_id, h.level + 1<br>    from hierarchy as h<br>    inner join active_employee as m on h.next_manager_workday_id = m.workday_employee_id<br>)<br><br>select<br>    u.snowflake_username<br>    , h.manager_workday_id   as viewer_employee_id<br>    , h.workday_employee_id  as viewable_employee_id<br>from hierarchy as h<br>inner join snowflake_user_to_employee_mapping as u on h.manager_workday_id = u.workday_employee_id<br>where h.manager_workday_id is not null<br><br>union<br><br>-- Self-access: every employee can see their own record<br>select u.snowflake_username, e.workday_employee_id, e.workday_employee_id<br>from active_employee as e<br>inner join snowflake_user_to_employee_mapping as u on e.workday_employee_id = u.workday_employee_id;<br><br><br>-- ============================================================<br>-- STEP 2: Memoizable function wrapping the access map lookup<br>--<br>-- A row access policy runs per row. If we queried<br>-- employee_access_mapping directly inside the policy, we&#39;d do<br>-- a full table lookup for every row being protected —<br>-- potentially millions of times per query.<br>--<br>-- A memoizable function solves this: Snowflake evaluates it<br>-- once per query, caches the result, and reuses the cache for<br>-- every subsequent row. The policy then does an O(1) array<br>-- membership check instead of a table scan per row.<br>--<br>-- Important: we accept `username` as an argument rather than<br>-- calling current_user() inside this function. This is intentional.<br>-- current_user() inside a memoizable function resolves to the real<br>-- session user, not the test user injected via POLICY_CONTEXT during<br>-- testing. Accepting it as an argument means the RAP body controls<br>-- whose entitlements are loaded — and POLICY_CONTEXT can override it.<br>-- ============================================================<br>create or replace function fn_viewable_employee_ids(username varchar)<br>    returns array<br>    memoizable<br>as $$<br>    -- Returns every workday_employee_id this user is allowed to see<br>    -- as an array — built once per query, reused for all row checks<br>    select array_agg(viewable_employee_id::varchar)<br>    from employee_access_mapping<br>    where snowflake_username = username<br>$$;</pre><h3>3. Explicit Override Roles for Broad Access</h3><p>When a persona legitimately needs ALL data in a domain — FP&amp;A needs all revenue, HRBPs need all compensation — this is provisioned through an explicit bypass role that the policy incorporates. The key word is explicit: bypass roles are named, documented, auditable, and owned by a specific approver. Membership is reviewed regularly.</p><p>This is the part of RBAC design that doesn’t automate away. The technical implementation is a few lines of SQL. The hard work is the human conversation: who owns each bypass group, what the approval process looks like, and who reviews membership when someone changes roles. That conversation needs to happen with the business stakeholders who own each sensitive domain, not just the data team.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/671/1*ROU7bAZ8mIUw8QObHBU5ww.png" /></figure><pre>-- STEP 3: Row Access Policy<br>--<br>-- Attached to any table that has a workday_employee_id column.<br>-- Snowflake evaluates this for every row a query touches.<br>-- Return true → show the row. Return false → hide it silently.<br>--<br>-- Tier 1: Platform and HR tooling bypass<br>--         Roles used by data engineers, BI tooling, and HR systems<br>--         skip filtering entirely. sys_context IS_ROLE_ACTIVATED checks<br>--         whether a role is anywhere in the active role hierarchy<br>--         (primary + secondary + their inherited roles) — not just<br>--         the top-level role — so this works regardless of how the<br>--         user connected. It is also session-constant: evaluated once<br>--         per query, not once per row.<br>--<br>-- Tier 2: Self + reports<br>--         Everyone else can only see their own record and the records<br>--         of employees who report up to them in Workday.<br>--         Array is built once (memoized), O(1) check per row.<br>--<br>-- Default: deny all.<br>-- ============================================================<br>create or replace row access policy employee_self_and_reports_access<br>    as (workday_employee_id int)<br>    returns boolean -&gt;<br>    case<br><br>        -- Tier 1: Broad internal role bypass.<br>        -- Data engineering, modeling, and HR roles — anyone who needs<br>        -- to work across the full employee dataset without being filtered.<br>        when sys_context(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;ACCOUNTADMIN&#39;)::boolean<br>          or sys_context(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;SECURITYADMIN&#39;)::boolean<br>          or sys_context(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;ANALYTICS_MODELING_RL&#39;)::boolean<br>          or sys_context(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;HR_RL&#39;)::boolean<br>        then true<br><br>        -- Tier 2: Self + reports access (memoized).<br>        -- fn_viewable_employee_ids() builds the full list of employee IDs<br>        -- this user can see — once per query, not per row.<br>        -- The cast chain (int → varchar → variant) is required because<br>        -- array_contains() expects a variant on the left and the array<br>        -- was built with varchar values.<br>        -- current_user() is passed from here (the RAP body), not from<br>        -- inside the function, so EXECUTE USING POLICY_CONTEXT correctly<br>        -- overrides it during testing.<br>        when array_contains(<br>            workday_employee_id::varchar::variant,<br>            fn_viewable_employee_ids(current_user())<br>        )<br>        then true<br><br>        -- Default: deny.<br>        -- Deny-by-default means a misconfigured entitlement produces<br>        -- no access rather than accidental over-exposure.<br>        else false<br><br>    end;</pre><h3>4. Bring Engineering Principles to Governance</h3><p>Row access and masking policies have historically been too hard to test. Our team has been able to accelerate our development and rollout of these governance features by building them into our standard analytics development workflows — namely using the dbt framework. With a few custom macros, you can build development, staging, and CI versions of row access and masking policies as you would any other object.</p><p>Below is a sample of a policy model materialization macro that can work wherever you use dbt, i.e., in dbt core, dbt projects on Snowflake, and dbt platform:</p><pre>{% materialization row_access_policy, default %}<br><br>  {%- set identifier = model[&#39;name&#39;] -%}<br><br>  {%- set target_relation = api.Relation.create(<br>      database=database,<br>      schema=schema,<br>      identifier=identifier,<br>      type=&#39;view&#39;) -%}<br><br>  {# Check if the policy already exists in this schema #}<br>  {%- set policy_exists_query -%}<br>    SHOW ROW ACCESS POLICIES LIKE &#39;{{ identifier }}&#39; IN SCHEMA {{ database }}.{{ schema }}<br>  {%- endset -%}<br><br>  {%- set policy_check = run_query(policy_exists_query) -%}<br>  {%- set policy_exists = (policy_check | length) &gt; 0 if policy_check else false -%}<br><br>  {{ run_hooks(pre_hooks, inside_transaction=False) }}<br>  {{ run_hooks(pre_hooks, inside_transaction=True) }}<br><br>  {% call statement(&#39;main&#39;) -%}<br>    {% if policy_exists %}<br>      {# Extract everything after the -&gt; arrow as the policy body #}<br>      {%- set body_start = sql.find(&#39;-&gt;&#39;) -%}<br>      {%- if body_start != -1 -%}<br>        {%- set policy_body = sql[body_start + 2:] | trim -%}<br>        ALTER ROW ACCESS POLICY {{ target_relation }} SET BODY -&gt; {{ policy_body }}<br>      {%- else -%}<br>        CREATE OR REPLACE ROW ACCESS POLICY {{ target_relation }} {{ sql }}<br>      {%- endif -%}<br>    {% else %}<br>      CREATE OR REPLACE ROW ACCESS POLICY {{ target_relation }} {{ sql }}<br>    {% endif %}<br>  {%- endcall %}<br><br>  {{ run_hooks(post_hooks, inside_transaction=True) }}<br>  {{ adapter.commit() }}<br>  {{ run_hooks(post_hooks, inside_transaction=False) }}<br><br>  {{ return({&#39;relations&#39;: [target_relation]}) }}<br><br>{% endmaterialization %}</pre><p>The result is a policy model that looks exactly like any other dbt model file and sits alongside data models — versioned, reviewed, and deployed on the same basis.</p><pre>-- models/policies/rap_employee_self_and_reports.sql<br>{{ config(materialized=&#39;row_access_policy&#39;, schema=&#39;policies&#39;) }}<br><br>as (workday_employee_id int) returns boolean -&gt;<br>  case<br>    when sys_context(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, &#39;HR_RL&#39;)::boolean<br>    then true<br><br>    when array_contains(<br>      workday_employee_id::varchar::variant,<br>      -- Function created through dbt custom materialization<br>      -- and accessible via dbt ref<br>      {{ ref(&#39;fn_viewable_employee_ids&#39;) }}(current_user())<br>    )<br>    then true<br><br>    else false<br>  end</pre><p>In our RAP implementation, we intentionally use SYS_CONTEXT(&#39;SNOWFLAKE$CURRENT&#39;, &#39;IS_ROLE_ACTIVATED&#39;, ...) for bypass checks rather than IS_ROLE_IN_SESSION. The difference matters for testing: IS_ROLE_IN_SESSION is always evaluated against the caller&#39;s real session: it cannot be spoofed. SYS_CONTEXT reads from the SNOWFLAKE$CURRENT context namespace, which EXECUTE USING POLICY_CONTEXTcan override. This means any policy can be fully validated in dev by injecting a role into the policy context, without granting that role to the testing user or switching sessions.</p><pre>-- Test: regular employee sees only their own record (and reports, if any)<br>EXECUTE USING POLICY_CONTEXT(<br>    CURRENT_USER                     =&gt; &#39;JSMITH&#39;,<br>    SNOWFLAKE$CURRENT_ACTIVATED_ROLES =&gt; (&#39;BASIC_RL&#39;)<br>) AS<br>SELECT COUNT(*) FROM employee_data;<br>-- Expected: small number (self + reports only)<br><br><br>-- Test: HR modeling role bypasses filtering entirely<br>EXECUTE USING POLICY_CONTEXT(<br>    CURRENT_USER                     =&gt; &#39;JSMITH&#39;,<br>    SNOWFLAKE$CURRENT_ACTIVATED_ROLES =&gt; (&#39;ANALYTICS_RL&#39;)<br>) AS<br>SELECT COUNT(*) FROM employee_data;<br>-- Expected: total row count<br><br><br>-- Test: unknown user with no entitlements sees nothing<br>EXECUTE USING POLICY_CONTEXT(<br>    CURRENT_USER                     =&gt; &#39;NONEXISTENT_USER&#39;,<br>    SNOWFLAKE$CURRENT_ACTIVATED_ROLES =&gt; (&#39;BASIC_RL&#39;)<br>) AS<br>SELECT COUNT(*) FROM employee_data;<br>-- Expected: 0</pre><p>These four principles compound. Broad schema access eliminates the ticket queue. Source-system authority keeps the mapping tables accurate without manual maintenance. Explicit bypass roles make the exceptions governable. Engineering practices — dbt materializations, version control, EXECUTE USING POLICY_CONTEXT testing — make the whole system maintainable at scale.</p><p>The teams that have built this foundation are the ones who can say yes when someone asks whether an AI agent can safely query sensitive data. The answer isn’t in the agent’s system prompt, it’s in the database.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=57219d444aeb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/snowflake/ai-ready-rbac-57219d444aeb">AI Ready RBAC</a> was originally published in <a href="https://medium.com/snowflake">Snowflake Builders Blog: Data Engineers, App Developers, AI, &amp; Data Science</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[IRL: Building business context with an automated corporate change log]]></title>
            <link>https://medium.com/@bigdatajames/irl-building-business-context-with-an-automated-corporate-change-log-e784886e277c?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/e784886e277c</guid>
            <category><![CDATA[impact-analyses]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[python-programming]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Tue, 21 Jan 2025 01:57:38 GMT</pubDate>
            <atom:updated>2025-01-21T01:57:38.924Z</atom:updated>
            <content:encoded><![CDATA[<p>The current analytics stack does a better job than ever before at explaining <em>what</em> is happening within a business. Explaining <em>why</em> has always been much harder and is still largely unsolved. The context behind business changes remains unstructured, siloed, and inaccessible to the majority of stakeholders within businesses. We do a better job than ever at measuring and managing key performance indicators (KPIs) — but we still don’t have a meaningful way to put those KPIs in the context of product launches, incidents, organizational changes, or industry events. The truth is that business intelligence has never actually been very intelligent.</p><p>With the advent of LLMs and the prevalence of cross-tool integrations (e.g., incidents, support tickets, deal workflows), we have an opportunity to make that business context structured, distributed, and accessible to every user in an organization.</p><h3>Milemarker</h3><p>After years of searching Slack and email for context and annotating charts in business review decks with those insights — I had had enough. With LLMs, I thought, I might be able to collect and curate that context automatically at scale.</p><p>The first major insight to unlock this work is that most corporate context is collected directly in Slack. Most companies go to great lengths to set up Slack integrations with Salesforce, Hubspot, Zendesk, Github, etc. You want to know about a new business opportunity or deal? It’s in Slack. You want to know about a change to our docs site? It’s in Slack. And thus, Slack emerged as the most logical entry point for our context collecting application. Luckily LLMs don’t just help us write dad jokes, they make coding more accessible to people like me — your everyday data practitioner. With irrational exuberance and some help from the robots, I got to building <strong>Milemarker</strong>, an internal slack application that would automatically capture business context and create an internal corporate change log.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FEvwfBNjdOmVvaj7OTMH6Q.png" /><figcaption>Demonstrative corporate events and metric annotations; image by the author</figcaption></figure><h3>Architecture</h3><p>Milemarker is a internal Slack application that parses message and thread data to create a structured and automated corporate changelog. Within this change log, is all the context of corporate history, that can then be used for myriad downstream analytics use cases (e.g., impact analyses).</p><p>We leverage the Slack API to pull relevant message and thread data (from specific channel subscriptions or mentions) and then create a message and thread database in Snowflake (via the Snowflake API). Using that data, we then use Snowflake’s own hosted LLMs (Cortex) to summarize and categorize the event to be stored in its own database. To make it easier on our users, we are experimenting with a Streamlit powered visualization layer for this event data in a Snowflake Native Application. Here we can demoratize our full corporate history — going forward I hope to overlay those events onto relevant metrics, to provide in-place context into business changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*uqj6GmwWQu0FBdfr" /><figcaption>Milemarker Architecture; Image by the author</figcaption></figure><p>The resulting application is a simple application that creates an incredibly rich data set. With 500+ custom corporate events (and growing), this dataset lets PMs understand shipping velocity and corresponding marketing launches; it lets the support team understand incidents and outages across the company, and it gives analysts the power to put business metrics in context.</p><h3>Internal Slack Application</h3><p>Our work starts first and foremost with the Slack bot that we installed on our workspace. We used a Flask Python application given the lightweight and simple yet extensible framework and deployed it using Fly.io (which we already used for a number of other enterprise use cases, simplifying deployment vs. some other alternatives).</p><p>On the Slack end, we configured the event subscription, interactivity, and shortcut endpoints in the manifest and aligned to the urls set up in our flask application e.g.,:</p><pre>@flask_app.route(&quot;/slack/events&quot;, methods=[&quot;POST&quot;])<br># picks up post requests to sent from Slack to the<br># application URL, e.g., https://milemarker_app/slack/events<br>def slack_events():</pre><pre>@flask_app.route(&#39;/slack/slash-command&#39;, methods=[&#39;POST&#39;])<br># picks up post requests to sent from Slack to the<br># application URL, e.g., https://milemarker_app/slack/slash-command<br>def handle_slash_command():</pre><p>Though we provided the bot with minimal permissions, one concern was the possibility the bot would get added to DMs or Private Channels and start scraping private or extra sensitive information.</p><p>To combat this, we added a specific listener to ensure that the bot would auto-leave DMs and Private Channels, only remaining in company-wide open channels.</p><pre>elif event_type == &#39;member_joined_channel&#39;:<br>    event_id = json_data[&quot;event_id&quot;]<br>    client = WebClient(token=os.environ[&quot;SLACK_BOT_TOKEN&quot;])<br>    channel_id = json_data[&#39;event&#39;][&#39;channel&#39;]<br>    try:<br>        # Call conversations.info method to check for private channels<br>        response = client.conversations_info(channel=channel_id)<br>        if response[&#39;ok&#39;]:<br>            channel_info = response[&#39;channel&#39;]<br>            is_channel = channel_info.get(&#39;is_channel&#39;, False)<br>            is_private = channel_info.get(&#39;is_private&#39;, False)<br>        else:<br>            return &quot;Error retrieving channel information.&quot;<br>    except SlackApiError as e:<br>        return f&quot;Slack API Error: {str(e)}&quot;<br>    # Leave the channel<br>    if is_channel is False or is_private is True:<br>        try:<br>            post_response = client.chat_postMessage(<br>                channel=channel_id,<br>                text=&quot;Homey don&#39;t play that. Leaving private channel.&quot;<br>            )<br>            print(&quot;Message posted: &quot;, post_response)<br>        except SlackApiError as e:<br>            return jsonify({&quot;error&quot;: f&quot;Failed to post message: {str(e)}&quot;}), 500<br>        try:<br>            response = client.conversations_leave(channel=channel_id)<br>            return jsonify({&quot;status&quot;: &quot;bot has left the private channel&quot;}), 200<br>        except SlackApiError as e:<br>            return jsonify({&quot;error&quot;: str(e)}), 500</pre><h3>Message and Thread Data Subscription</h3><p>We made the design decision to add the bot to specific high-importance channels that capture the majority of our company specific discourse (e.g., <strong>#general</strong>, <strong>#announcements</strong>, <strong>#incidents</strong>, etc.) — most of our external tools already integrate with these channels to provide a comprehensive view of high level business context.</p><p>Once added, we could use the <a href="https://api.slack.com/apis/events-api">Slack Events API</a> to subscribe to and handle incoming messages. Once here, we parsed the data in the message focusing on the:</p><ol><li>Permalink to the slack message (which is based on the channel and timestamp of the message)</li><li>Message type (new vs. thread response)</li><li>Message text</li><li>Slack channel ID</li><li>Slack User ID</li><li>Relevant timestamps</li></ol><p>We then send all of that data to our Snowflake thread and messages database.</p><pre>elif &#39;event&#39; in json_data and event_type == &#39;message&#39;:<br>    event_id = json_data[&#39;event_id&#39;]<br>    if is_event_id_handled(event_id):<br>        print(&quot;event id already handled&quot;)<br>    else:<br>        save_handled_event_id(event_id)<br>        message_ts = json_data[&#39;event&#39;][&#39;ts&#39;]<br>        date_selection = datetime.fromtimestamp(float(message_ts)).strftime(&#39;%Y-%m-%d&#39;)<br>        # Parse whether the message is a new message or a response to a thread<br>        if &#39;thread_ts&#39; in json_data[&#39;event&#39;]:<br>            thread_ts = json_data[&#39;event&#39;][&#39;thread_ts&#39;]<br>            message_type = &#39;Thread Response&#39;<br>        else:<br>            thread_ts = message_ts<br>            message_type = &#39;New Message&#39;<br>        message_text = json_data[&#39;event&#39;][&#39;text&#39;]<br>        permalink = fetch_message_permalink(channel_id, message_ts, slack_bot_token)<br>        snowflake_insert_data = (message_type, permalink, thread_ts, message_ts, user_id, channel_id, message_text)<br>        snowflake_payload = &#39;(message_type, permalink, thread_ts, message_ts, user_id, channel_id, message_text)&#39;<br>        snowflake_values = &#39;(%s, %s, %s, %s, %s, %s, %s)&#39;<br>        # Messages that are automatically scraped on a go forward basis<br>        snowflake_schema = &#39;THREAD_DATA&#39;<br>        snowflake_table = &#39;SLACK_EVENT_SUBSCRIPTION_THREADS&#39;<br>        export_to_snowflake(snowflake_insert_data, snowflake_schema, snowflake_table, snowflake_payload,<br>                            snowflake_values, slack_team_id)<br>        print(&#39;Record exported to Snowflake&#39;)</pre><h3>Message &amp; Thread Database</h3><p>Within Snowflake, we created a separate database to house these slack threads. This allowed us to cordon off access for our bot following principles of least privilege. Within our thread_data schema we created four tables to receive incoming data from the our slack bot:</p><ul><li>slack_event_subscription_threads: receives data described above via the Slack Events API (i.e., event api based subscriptions to channels)</li><li>slack_interaction_threads: receives data from the <a href="https://api.slack.com/interactivity/handling">interactions</a> with the bot such as slack shortcuts and mentions (not covered in this doc)</li><li>slack_event_subscription_backfill_threads: receives data from manual backfills of channels (so we could build a deeper history, not just on a go forward basis) triggered by a custom configured slash command (not covered in this doc)</li><li>slack_mention_threads: receives data specifically from bot mentions (not covered in this doc)</li></ul><p>Each of these have the same basic schema, which facilitates aggregation and can be used for larger backfills.</p><h3>Thread Processing &amp; Categorization</h3><p>The next and most important step is extracting all the richness from that thread data and using LLMs, categorizing messages into contextual events. Given the sensitivity of the data we are processing, pushing the data to an external an external AI vendor (e.g., OpenAI) would introduce additional unneeded risk. But Snowflake’s available AI models offered a legal and security friendly entry point for this type of categorization.</p><p>Each time a new message is collected through the Slack API — we send that data to the Snowflake Cortex for categorization and summarization. After experimenting with their <a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/llm-functions">various offerings</a> (e.g., mistral-7b, snowflake-artic, and llama3), and dealing with multiple false positives, we landed on using Snowflake’s classify_text and summarize functions. While there is further opportunity to <a href="https://docs.snowflake.com/en/sql-reference/functions/classify_text-snowflake-cortex#using-optional-arguments">tune these models</a>, I’ve taken them out of the box with great success.</p><pre>def run_snowflake_cortex_model(message_array):<br>    import snowflake.connector<br><br>    # Remove quotations from message for SQL handling<br>    message_array_escaped = message_array.replace(&quot;&#39;&quot;, &quot;&#39;&#39;&quot;)<br><br>    # Create Connection<br>    try:<br>        conn = snowflake.connector.connect(<br>            # connection credentials and details to be added here<br>        )<br>        cur = conn.cursor()<br><br>        try:<br>            select_query = f&quot;&quot;&quot;<br>            select<br>                &#39;{message_array_escaped}&#39; AS message_array,<br>                -- Use Classify Text to bucket values<br>                snowflake.cortex.classify_text(message_array, <br>                    [<br>                      &#39;Operating Expenses&#39;,<br>                      &#39;Customer Activity&#39;,<br>                      &#39;Engineering Support &amp; Incidents&#39;,<br>                      &#39;World&#39;,<br>                      &#39;Finance &amp; Accounting&#39;,<br>                      &#39;Go-to-Market Activity&#39;,<br>                      &#39;Product Launch or Announcement&#39;,<br>                      &#39;Public Relations &amp; Marketing&#39;,<br>                      &#39;Team &amp; Internal&#39;,<br>                      &#39;Cost of Goods Sold&#39;,<br>                      &#39;Other or Unknown&#39;<br>                    ]<br>                  ) as classify_categorization,<br>                -- Extract just the label<br>                replace(classify_categorization:label, &#39;&quot;&#39;, &#39;&#39;) AS ml_categorization,<br>                -- Use Summarize to provide an ML summary of the event<br>                snowflake.cortex.summarize(message_array)<br>                    AS ml_summary<br>            &quot;&quot;&quot;<br>            cur.execute(select_query)<br>            row = cur.fetchone()<br>        except snowflake.connector.Error as e:<br>            return {&#39;error&#39;: f&quot;Snowflake query execution error: {str(e)}&quot;, &#39;success&#39;: False}<br>        finally:<br>            cur.close()<br>            conn.close()<br>        if row:<br>            # Label<br>            ml_summary = row[3]<br>            # Event Type<br>            ml_categorization = row[2]<br>            return ml_summary, ml_categorization<br>        else:<br>            print(&quot;No results found&quot;)<br>    except snowflake.connector.Error as e:<br>        return {&#39;error&#39;: f&quot;Snowflake query execution error: {str(e)}&quot;, &#39;success&#39;: False}</pre><p>That can take a message such as this one from our DevRel lead…</p><blockquote>Wow happy new years! (Or is it new year? idk. Someone should figure that out.) Over in DevEx/Marketing, we’ve celebrated the new year by <strong>moving </strong><a href="http://docs.mux.com/"><strong>docs.mux.com</strong></a><strong> to </strong><a href="http://mux.com/docs"><strong>mux.com/docs</strong></a><strong>. </strong>For customers this is a small change. Since the two sites are now part of the same app, routing should be slightly faster. For us, though, we get to share styles and components! Which means less redundant code to load and less redundant code to maintain. Great for doing a lot with a little team.</blockquote><p>…and categorize it as <strong>Public Relations &amp; Marketing</strong> with the following summary:</p><blockquote>The new year was celebrated in DevEx/Marketing by moving docs.mux.com to mux.com/docs. This allows for sharing of styles and components, reducing redundant code. Old links still work with redirects in place.</blockquote><p>Along with the original message permalink and event date from the message, this summarization starts to build out or event database.</p><p>Of course it’s garbage-in and garbage-out — my favorite events to-date are in <strong>Team &amp; Internal </strong>and are lunch related:</p><blockquote>`[“Lunch train leaving in ~5 mins!”]</blockquote><p>and</p><blockquote>Multiple extra lunches, including a lamb shawarma from La Med with Becca’s name, are available for those in town. One person has already claimed a meal, and another person expressed gratitude for a meal from Becca or Todd.</blockquote><p>Garbage or not, the results of these models are sent back to Snowflake to construct our event database.</p><h3>Manual User Input with Slack Modals</h3><p>In addition to automated event collection — we needed an ability to manually add events to our events database to capture additional business context that’s not discussed directly in Slack. Slack modals offer a clean and customizable in-app experience, and can quickly be constructed using <a href="https://app.slack.com/block-kit-builder/">Slack’s Block Kit Builder</a>.</p><p>The result is a simple interface that is trigged on shortcut commands and app mentions. On modal submission, data is similarly sent to our events database in Snowflake.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/516/0*I-b2ELDCSaOoPShC" /><figcaption>Milemarker Slack Modal (image by the author)</figcaption></figure><h3>Event Database</h3><p>Similar to our thread data in Snowflake, the event_data schema in Snowflake contains two tables focused on the input type (but with the same schema otherwise).</p><ul><li>slack_llm_event_submissions</li><li>slack_manual_event_submissions</li></ul><p>These are unioned in a downstream view: slack_event_submissions that is referenced further downstream in our visualization layer.</p><h3>Visualization Layer</h3><p>What good is all this data if there’s not a good way for users to interact with it (and importantly update, add to it, or delete it)?</p><p>With Streamlit and Vega-Lite support, Snowflake Native Applications provide an easy entry point to build interactive visualizations, which support manual input.</p><p>First we load data from our Slack Events aggregated view, with some formatting / handling of null values.</p><pre>def load_data():<br>    try:<br>        snowpark_df = session.sql(f&quot;SELECT * FROM {slack_events_table}&quot;)<br>        df = snowpark_df.to_pandas()<br><br>        df[&#39;DATE_SELECTION&#39;] = pd.to_datetime(df[&#39;DATE_SELECTION&#39;], errors=&#39;coerce&#39;)<br>        # Coerce values to be &quot;None&quot; to match st.data_editor format<br>        df[&#39;USER_ID&#39;] = df[&#39;USER_ID&#39;].apply(lambda x: &quot;None&quot; if pd.isnull(x) else x)<br>        df[&#39;METRIC_SELECTION&#39;] = df[&#39;METRIC_SELECTION&#39;].apply(lambda x: &quot;None&quot; if pd.isnull(x) else x)<br>        df[&#39;USERNAME&#39;] = df[&#39;USERNAME&#39;].apply(lambda x: &quot;None&quot; if pd.isnull(x) else x)<br>        df[&#39;NAME&#39;] = df[&#39;NAME&#39;].apply(lambda x: &quot;None&quot; if pd.isnull(x) else x)<br>        if df.empty:<br>            df = pd.DataFrame(columns=[&#39;EVENT_ID&#39;, &#39;SOURCE&#39;, &#39;PERMALINK&#39;, &#39;USER_ID&#39;, &#39;USERNAME&#39;, &#39;NAME&#39;,<br>                                       &#39;DATE_SELECTION&#39;, &#39;FREE_TEXT_SELECTION&#39;, &#39;DOMAIN_SELECTION&#39;, &#39;METRIC_SELECTION&#39;])<br>        df_sorted = df.sort_values(by=&#39;DATE_SELECTION&#39;, ascending=False)<br>        return df_sorted</pre><p>Next, we leverage Streamlit to create a editable dataframe with custom drop downs for event types, sources, and affected metrics (which can then be used later in metric annotations).</p><pre>with st.spinner(&quot;Loading data. Please wait...&quot;):<br>    # Create a visualization with the slack events combined view<br>    with st.container():<br>        edited_events_session_df = st.data_editor(<br>            st.session_state.edited_events_df,<br>            height=500,<br>            column_order=(<br>                &quot;DATE_SELECTION&quot;,<br>                &quot;DOMAIN_SELECTION&quot;,<br>                &quot;FREE_TEXT_SELECTION&quot;,<br>                &quot;SOURCE&quot;,<br>                &quot;PERMALINK&quot;,<br>                &quot;METRIC_SELECTION&quot;<br>            ),<br>            column_config={<br>                &quot;PERMALINK&quot;: st.column_config.LinkColumn(<br>                    &quot;Slack Link&quot;,<br>                    disabled=True,<br>                    width=&quot;small&quot;<br>                    # Not supported in v1.26<br>                    # display_text=&quot;Slack Link&quot;<br>                ),<br>                &quot;DATE_SELECTION&quot;: st.column_config.DateColumn(<br>                    &quot;Event Date&quot;,<br>                    format=&quot;MMM-DD-YYYY&quot;<br>                ),<br>                &quot;SOURCE&quot;: st.column_config.SelectboxColumn(<br>                    &quot;Event Source&quot;,<br>                    options=[&#39;Direct Entry&#39;, &#39;Slack Thread Submission&#39;, &#39;Web Scrape&#39;, &#39;Auto Event Submission&#39;,<br>                             &#39;User Edited&#39;]<br>                ),<br>                &quot;FREE_TEXT_SELECTION&quot;: st.column_config.Column(<br>                    &quot;Event Summary&quot;,<br>                    width=&quot;large&quot;<br>                ),<br>                &quot;DOMAIN_SELECTION&quot;: st.column_config.SelectboxColumn(<br>                    &quot;Event Type&quot;,<br>                    options=domain_list<br>                ),<br>                &quot;METRIC_SELECTION&quot;: st.column_config.SelectboxColumn(<br>                    &quot;Affected Metric&quot;,<br>                    options=metric_names_df<br>                ),<br>                &quot;USER_ID&quot;: st.column_config.Column(<br>                    label=&quot;User ID&quot;,<br>                ),<br>            },<br>            use_container_width=True,<br>            hide_index=True<br>        )</pre><p>We can then parse row updates (between our session_state dataframe and the edited one) and create a save button for users to edit summaries or recategorize the event altogether.</p><pre>row_updates = edited_events_session_df.loc[(st.session_state.edited_events_df != edited_events_session_df).any(axis=1)]<br><br>st.write(&quot;Rows that will be updated&quot;)<br>st.dataframe(row_updates)<br><br># Save changes button<br>if st.button(&quot;Save Changes&quot;, type=&quot;primary&quot;):<br>    with st.spinner(&quot;Saving changes. Please wait...&quot;):<br>        try:<br>            # Update the rows from the dataframe<br>            for index, row in row_updates.iterrows():<br>                if row[&#39;TABLE_SOURCE&#39;] == &#39;SLACK_MANUAL_EVENT_SUBMISSIONS&#39;:<br>                    update_table = slack_manual_events_table<br>                elif row[&#39;TABLE_SOURCE&#39;] == &#39;SLACK_LLM_EVENT_SUBMISSIONS&#39;:<br>                    update_table = slack_llm_events_table<br>                elif row[&#39;TABLE_SOURCE&#39;] == &#39;WEB_LLM_EVENT_SUBMISSIONS&#39;:<br>                    update_table = web_llm_events_table<br>                else:<br>                    update_table = None<br>                # Format dates back to upload<br>                if row[&#39;DATE_SELECTION&#39;] is None:<br>                    date_selection = None<br>                elif isinstance(row[&#39;DATE_SELECTION&#39;], pd.Timestamp):<br>                    date_selection = pd.to_datetime(row[&#39;DATE_SELECTION&#39;]).strftime(&#39;%Y-%m-%d&#39;)<br>                elif isinstance(row[&#39;DATE_SELECTION&#39;], str):<br>                    date_selection = pd.to_datetime(row[&#39;DATE_SELECTION&#39;]).strftime(&#39;%Y-%m-%d&#39;)<br>                else:<br>                    date_selection = pd.to_datetime(row[&#39;DATE_SELECTION&#39;]).strftime(&#39;%Y-%m-%d&#39;)<br>                event_id = row[&#39;EVENT_ID&#39;]<br>                source = row[&#39;SOURCE&#39;]<br>                permalink = row[&#39;PERMALINK&#39;]<br>                free_text_selection = row[&#39;FREE_TEXT_SELECTION&#39;]<br>                free_text_selection_escaped = free_text_selection.replace(&quot;&#39;&quot;, &quot;&#39;&#39;&quot;)<br>                domain_selection = row[&#39;DOMAIN_SELECTION&#39;]<br>                metric_selection = row[&#39;METRIC_SELECTION&#39;]<br>                query = f&quot;&quot;&quot;<br>                    UPDATE {update_table}<br>                    SET<br>                        SOURCE = &#39;{row[&#39;SOURCE&#39;]}&#39;,<br>                        PERMALINK = &#39;{row[&#39;PERMALINK&#39;]}&#39;,<br>                        DATE_SELECTION = &#39;{date_selection}&#39;,<br>                        FREE_TEXT_SELECTION = &#39;{free_text_selection_escaped}&#39;,<br>                        DOMAIN_SELECTION = &#39;{domain_selection}&#39;,<br>                        METRIC_SELECTION = &#39;{row[&#39;METRIC_SELECTION&#39;]}&#39;<br>                    WHERE<br>                        EVENT_ID = &#39;{row[&#39;EVENT_ID&#39;]}&#39;<br>                &quot;&quot;&quot;<br>                session.sql(query).collect()<br>            st.success(&quot;Changes saved successfully!&quot;)<br>        except Exception as e:<br>            st.error(f&quot;Error saving changes: {e}&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wYC5wnGiw16Z40m6ePx7sg.png" /><figcaption>Row update mechanism; Streamlit</figcaption></figure><p>While we’re still working on a graphical visualization layer (using Vega-lite) — an early prototype gives the idea of where we want to go with this work, including custom hover and tooltip behavior.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xYFUWU8teugyKb7-JNEavg.png" /><figcaption>Lunch train is leaving!</figcaption></figure><p>In the future I’m excited to integrate metrics into this layer, ultimately displaying events as annotations on top of the metrics that they impact. That will help explain the <strong>why</strong> behind a metric’s movement, and not just the <strong>what</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e784886e277c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A day with the Looker to Google Sheets data connector]]></title>
            <link>https://medium.com/@bigdatajames/a-day-with-the-looker-to-google-sheets-data-connector-fc0ef18255d8?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/fc0ef18255d8</guid>
            <category><![CDATA[looker]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[google-sheets]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Mon, 06 Feb 2023 15:06:21 GMT</pubDate>
            <atom:updated>2023-02-06T15:06:21.278Z</atom:updated>
            <content:encoded><![CDATA[<h4>Your models and slides deserve the semantic layer</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DeCBioRV1b343Mogxi8Vzg.png" /><figcaption>Man catching big data via DALL-E</figcaption></figure><p>It has been almost exactly three years since Google closed on its <a href="https://techcrunch.com/2020/02/13/google-closes-2-6b-looker-acquisition/?guccounter=1&amp;guce_referrer=aHR0cHM6Ly93d3cuZ29vZ2xlLmNvbS8&amp;guce_referrer_sig=AQAAAGgc8nfaWAD4lGHfigzt9VqsBk7ytvhESC7IK5_Tq0hIE7od96vD4gjBUtA0mJ4LWnl43HflG8uDTlPZjeQ6SdUbhfZ7QUJr1JcHQLne8jL-rLlSfdCbWCHx0M1Za7s0px166sB04LVMYKdMiTUSRbgtR5tHk0mAN0kj5GWXt0U4">acquisition</a> of Looker. In that time we’ve gotten a <a href="https://cloud.google.com/blog/products/data-analytics/looker-product-2021">extensions, APIs, and the Looker Marketplace</a>; we’ve gotten the <a href="https://cloud.google.com/blog/products/data-analytics/looker-and-data-studio-integrate-for-best-of-both-worlds">Looker Studio integration</a>; and we’ve gotten a <a href="https://community.looker.com/news-announcements-1007/the-purple-is-gone-18512">lot less purple</a>.</p><p>In those three years though, I’ve been eagerly awaiting the most obvious power-feature that could bring the semantic layer to the masses: the Looker -&gt; Google Sheets data connector.</p><h3>The semantic layer meets you where you work</h3><p>Last week while trying to update some embed features in our Looker account, I saw a curious toggle in the Looker BI Connectors menu: Google Sheets???</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/1*E2HBZAAnc8_EuVZINlXZkg.png" /></figure><p>I was surprised to see the new feature (absent a meaningful marketing push from Google), but was exicted to integrate Looker’s power into the world’s leading data tool — Google Sheets.</p><p>After <a href="https://cloud.google.com/looker/docs/connected-sheets#enabling_connected_sheets">enabling and then following up on the Sheets side</a>, our Google Workplace account was whitelisted for the feature this morning (note: the sheets connector is in <a href="https://workspaceupdates.googleblog.com/2023/02/connected-sheets-looker.html">GA as of February 1st</a> being rolled out to all domains over the following few weeks.)</p><p>And with a few clicks and keystrokes I was in.</p><h3>Models &amp; slides</h3><p>As a data practitioner you might be asking yourself: “why does this matter for me? I’ve got a great LookML model and my users engage with data on my terms”.</p><blockquote>I’ve got two words for you: <strong>models</strong> and <strong>slides</strong></blockquote><h4><strong>Models deserve the semantic layer</strong></h4><p>Ask yourself — where is your financial plan? Where are your CAC and LTV models? As I’ve <a href="https://medium.com/@bigdatajames/full-catastrophe-analytics-406a2d8414d3">alluded to in the past</a>, BizOps and Finance teams build models in spreadsheets and they can’t stop won’t stop. The best models integrate data directly from the data warehouse as the foundations of these models and its your role to help their owners maintain the integrity of their work.</p><p>To date, that could be achieved with different degrees of cost and automation depending on your respective stack, e.g,:</p><ul><li>BigQuery users could connect to their warehouse directly and load tables or execute saved queries</li><li>You could pay a company like <a href="https://seekwell.io/">Seekwell</a> to help connect Snowflake -&gt; Sheets and execute saved or adhoc queries</li><li>You could build on AppScript to connect your sheet via e.g. the Snowflake or Looker API to once again execute saved / adhoc queries</li><li>etc.</li></ul><p>These options share the same drawback: they don’t leverage the semantic layer, exposing them to version control issues and definitional drift in underlying metrics / tables.</p><h4>Scalable and accurate slides</h4><p>At some point, you have likely copied data from our business intelligence tool or SQL IDE into a spreadsheet to create charts for some review. Don’t be ashamed. We’ve all done it.</p><p>At Mux, our highly clever analytics engineering team helped us connect Google Sheets (via App Script) to the Looker API to execute queries in a sheet and automatically update data for various models (per above) as well as weekly and monthly business metrics reviews.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q23sDtuNASKcGFhPXZU3DQ.png" /><figcaption>Current Apps Script -&gt; Looker API example hacky solution</figcaption></figure><p>That workflow has helped us avoid copying and pasting charts from Looker into Google Slides (critical for large repetitive customer facing workloads that leverage the DWH to build reports). But as you’ll notice from the screenshot above — it entails a series of manually curated SQL scripts pasted into a spreadsheet — the perfect seedbed for version control issues and metric definition drift.</p><h3>First Impressions of the Looker -&gt; Sheets connector</h3><p>Twelve hours in, my experience accessing and working with Looker data in Sheets has been a very smooth experience. The workflow follows the following general steps, which we’ll walk through in more detail below:</p><ul><li>Load</li><li>Pivot</li><li>Build</li><li>Inject</li><li>Schedule</li></ul><h4>Load</h4><p>Once connected, you’ll be prompted to select an explore. Doing so will loading the full Explore in a preview page, highlighting all available dimensions and measures (inclusive of data type and descriptions).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/795/1*h86Z5UlZWq-WjcE45e8S5w.png" /><figcaption>Demo Explore Preview (Invoice Details)</figcaption></figure><h4><strong>Pivot</strong></h4><p>To use the data, you next create a pivot table from the explore. In this view, you will find the familiar Row, Column, Values, Filters structure of a normal pivot table juxtaposed next to all dimensions and measures from your selected explore (searchable and organized by Looker View Labels).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0tuvnUqVtGJ3jjcSxjQarg.png" /><figcaption>Demo Looker based Pivot Table (Invoice Details)</figcaption></figure><h4><strong>Build</strong></h4><p>After selecting your desired Rows, Columns, Values, and Filters, hitting ‘Apply’ will send your query to Looker and then to your underlying DWH. Behind the scenes your query engine is leveraging your LookML semantic layer to fire off more complicated queries (including joins across tables):</p><pre>-- From Snowflake Query activity <br>SELECT<br>    (<br>        TO_CHAR(<br>            DATE_TRUNC(&#39;month&#39;, dd_organizations.&quot;BILLING_START&quot;),<br>            &#39;YYYY-MM&#39;<br>        )<br>    ) AS &quot;dd_organizations.billing_start_month&quot;,<br>    MAX(1) AS dynamic_field_e487288f22c74cefa0907b4ecc42db3e,<br>    MIN((fct_invoice_metrics.&quot;INVOICE_ID&quot;)) AS dynamic_field_636c985c00134dd88c9301b6f58ed10a<br>FROM<br>    &quot;CORE&quot;.&quot;FCT_INVOICE_METRICS&quot; AS fct_invoice_metrics<br>    LEFT JOIN &quot;ANALYTICS&quot;.&quot;DD_ORGANIZATIONS&quot; AS dd_organizations ON (dd_organizations.&quot;ORGANIZATION_ID&quot;) = (fct_invoice_metrics.&quot;ORGANIZATION_ID&quot;)<br>GROUP BY<br>    (<br>        DATE_TRUNC(&#39;month&#39;, dd_organizations.&quot;BILLING_START&quot;)<br>    )<br>ORDER BY<br>    1 DESC FETCH NEXT 30001 ROWS ONLY<br>    -- Looker Query Context &#39;{&quot;user_id&quot;:2,&quot;history_slug&quot;:&quot;0495c7913712f017580acac8b716894d&quot;,&quot;instance_slug&quot;:&quot;c800b442b2d45569b433208ceb58a9ad&quot;}&#39;</pre><h4><strong>Inject</strong></h4><p>Value is finally created (above and beyond using Looker by itself) when this data is injected into models and slides, automating reporting workflows and improve downstream data integrity.</p><h4><strong>Schedule</strong></h4><p>The Looker -&gt; Sheets data connector comes with an easy to set-up scheduler that will allow users to automatically update data on a regular cadence (e.g,. every hour, every day, every week, etc.). This can be further leveraged to make sure downstream data artifacts are always fresh.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zVF7TKnUqfPzlVoDCoCAzQ.png" /><figcaption>Schedule Refresh Window</figcaption></figure><h3>What’s next</h3><p>With the Looker -&gt; Sheets connector in the wild, we will take advantage in the coming weeks by migrating data sources for relevant financial models and recurring reports. Beyond replacing existing data sources with sturdier ones, this feature will allow us to create value and not just avoid costs (e.g., a deck-generator for our Customer Success team to aid in customer business reviews)</p><p>Most importantly, I’m excited to get my BizOps and Finance team more engage with our data ecosystem, and I think the familiar old pivot table might just be the best front door.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fc0ef18255d8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A ship without a rudder: 5 Operating Principles for your Analytics team]]></title>
            <link>https://medium.com/@bigdatajames/a-ship-without-a-rudder-5-operating-principles-for-your-analytics-team-2f7566333600?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f7566333600</guid>
            <category><![CDATA[leadership]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[business-operations]]></category>
            <category><![CDATA[operations]]></category>
            <category><![CDATA[team]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Mon, 09 Jan 2023 17:05:15 GMT</pubDate>
            <atom:updated>2023-01-09T17:05:15.159Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0lgX8QP6HIWlCw3GaXaQ6Q.png" /><figcaption>Hand Holding Compass by the author via DALL-E 2</figcaption></figure><blockquote>The principles you live by create the world you live in.</blockquote><blockquote>— Tony Robbins</blockquote><p>I recently had the privilege of taking over Mux’s BizOps team, combining it under one roof with our Analytics and Data Science functions. The change helped accelerate both teams, allowing us to learn from one another’s super powers while staying completely coordinated. And my dirty secret is that I’ve always been a BizOps person doing my best Data Science impression.</p><p>The change was also an opportunity to reset our team mission, roles and responsibilities, and <strong>operating principles</strong>. These operating principles have been valuable to me and our team but even more valuable as a device for communicating and setting expectations with stakeholder teams. They might be helpful for you too, dear reader, as you think about how to run your respective team or organization.</p><h3>The life of a project</h3><p>To orient ourselves, we first considered the life of a BizOps Analytics project, which generally follows 5 steps:</p><ol><li><strong>Ideation</strong>, i.e,. figuring out the universe of potential projects</li><li><strong>Prioritization, </strong>i.e,. figuring out what to work on within that universe</li><li><strong>Execution</strong>, i.e,. how to go about working on that project</li><li><strong>Synthesis, </strong>i.e., sharing the results of the work (and communication writ large)</li><li><strong>Graduation, </strong>i.e., what happens after the project is “complete”</li></ol><p>Our team agreed upon an operating principle for each step along this path and each principle has a set of implications for how we work as a team and with stakeholders.</p><h3>Ideation</h3><p>Tolstoy wrote, “<strong>every unhappy family is unhappy in its own way.” </strong>Extending to BizOps &amp; Analytics teams, I’d challenge and say that every unhappy analytics team is in the same failure state as task-takers. They’re asked: “What was the revenue is Tuscaloosa last month?” or “How many cumquats did we ship to Chicago yesterday?” And the analysts, well, they hate it. They’re engaging at the analysis level as opposed to the problem level, robbing the analyst of creative problem solving, context, and agency more generally. This model also leads to worse outcomes for the organization because, just maybe, Tuscaloosa doesn’t matter at all. That leads me to our first operating principle:</p><blockquote>1) We engage best at the <strong><em>problem level</em></strong> not at the analysis level</blockquote><p>Getting to the success state — the analysis level — usually starts with the question-behind-the-question, e.g., “Why do you want to know what revenue was in Tuscaloosca last month?” That meta-question might lead to an entirely different analysis e.g., “I’m actually curious about the revenue distribution across major southern cities so I can staff our sales team accordingly”.</p><p>The best version of problem-level-engagement requires BizOps and Analytics to deeply partner with stakeholders: doing our best means understand and internalize their hopes, fears, and dreams.</p><h3>Prioritization</h3><p>How big is your data, analytics, and bizops team? It probably doesn’t feel big enough for how much you’re asked to do. As strategic levers within our organizations our time is scarce yet incredibly valuable, which makes picking the right projects one of the most important things we can do. We’ve injected this ethos directly into our operating principles to level set expectations internally within our team and with our stakeholders:</p><blockquote>2) We focus on the <strong><em>most impactful work</em></strong> for our company</blockquote><p>This is not always easy to do — yet it’s one of the most critical things you can do for your company and for your career. The implications are two-fold:</p><ol><li>For your stakeholders: We may be <em>working on other important projects and have to say no (or not yet)</em></li><li>For your team: We reserve the right to decline project work but it’s critical that we are <em>transparent about our priorities and bandwidth</em></li></ol><p>Lastly — I try to ask myself at least once a day “why I am working on this particular project?” Too often, it’s because that project is easy, or it’s interesting, or it’s known. I’m working on it because it’s <strong><em>comfortable</em></strong>. But comfortable doesn’t pay the bills, <strong><em>important</em></strong> does.</p><h3>Execution</h3><p>Work in our field teeters across a triangle of execution vectors. On the path to a deliverable we might spend weeks designing and building a data model at <strong>scale</strong>; we might pursue pure intellectual and analytical <strong>rigor</strong> with the perfect experiment plan; and sometimes we might favor <strong>speed</strong> and run arithmetic in a spreadsheet. These are all acceptable paths in and of themselves, but only when they’re applied to the right problem. Keeping this balancing-act in mind, our team our team follows this third principle as we execute projects:</p><blockquote>3) We balance <strong><em>scale</em></strong>, <strong><em>rigor</em></strong>, and <strong><em>speed</em></strong> as we approach each problem</blockquote><p>At its heart, knowing which execution vector to pursue requires us to understand the cost of being wrong. Hacky data infrastructure work can cascade to hundreds of users through business intelligence or it might create technical debt that is hard to dig out of. A badly planned or executed experiment may lead us to make the wrong product decisions. Meanwhile, a (heavily qualified) 80% accurate answer for a marketing leader about which campaigns seem more effective may be enough to make a quick decision.</p><p>Our team strives to <strong>solve data and BI problems once at scale. </strong>We recognized that strategic analytics require deep <strong>analytical rigor</strong><em>.</em> And we appreciate that some time-sensitive projects may only <strong>need back-of-the-envelope math</strong>.</p><h3>Synthesis</h3><p>I first stumbled upon the words ‘computational kindness’ reading <a href="https://us.macmillan.com/books/9781250118363/algorithmstoliveby#:~:text=%E2%80%9CCompelling%20and%20entertaining%2C%20Algorithms%20to,science%20and%20the%20human%20mind.">Algorithms to Live By</a> in 2016. The concept immediately burrowed its way in my brain and established itself as a North Star for how I synthesize findings or build business intelligence products:</p><blockquote>4) We practice <strong><em>computational kindness </em></strong>when we synthesize and communicate our findings</blockquote><p>Practically for our team, <strong>computational kindness</strong> means always investing in the last mile of our work<em>. </em>It means doing our best to tell stories in an articulate and deliberate manner regardless of medium (slack, email, slides, Hex, etc.). I tend to think of our strategy and analytics work as a pizza or as a joke; <strong>if you don’t nail the delivery, it’s all for naught.</strong></p><h3>Graduation</h3><p>You built the perfect analysis or process and nailed your presentation. Nice work, you’re done, right? Not quite. The last step of the project is the follow through, including:</p><ol><li>Documenting the results of the analysis in widely accessible library</li><li>Implementing recommendations</li><li>Monitoring and evaluating results of the implementation</li></ol><p>As we think about the lifecycle of our responsibilities we have included this last operating principle to take us to completion:</p><blockquote>5) We <strong><em>operationalize analytics and</em></strong><em> </em><strong><em>processes</em></strong> to drive efficiency and accountability</blockquote><p>Often this means handoff to product or operations teams to implement recommendations at scale. In those cases, our team is expected to measure and manage results of that work post-launch. In our modern data stack, that often means that an analysis can graduate to a dbt model / dashboard, plugging in real-life values and making sure that we keep moving forward to the next project!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f7566333600" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Keep your friends close, keep your entities closer]]></title>
            <link>https://medium.com/data-science/keep-your-friends-close-keep-your-entities-closer-ae216b02da1e?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/ae216b02da1e</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Wed, 05 Oct 2022 21:39:59 GMT</pubDate>
            <atom:updated>2022-10-07T20:34:35.927Z</atom:updated>
            <content:encoded><![CDATA[<h3>Keep Your Friends Close, Keep Your Entities Closer</h3><h4>Entity relationship diagrams: not cool, not modern, still super valuable</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zuTtu0F-lDbRUVjqkCojkg.png" /><figcaption>Apples, Oranges, and Bananas (Generated by DALL-E)</figcaption></figure><p>In its purest form the data warehouse is a mirror. Done right, that mirror reflects the real-world entities and events from your business or domain and articulates them in a smattering of tables, metrics, and dimensions. Done really right — your internal stakeholders and other analysts can navigate that ecosystem without fear of nuance or error — getting there starts with the lowly <strong>entity relationship diagram</strong> (ERD).</p><h3>Consider the Jaffle Shop</h3><p>dbt’s <a href="https://github.com/dbt-labs/jaffle_shop/tree/main/models">Jaffle Shop</a> demo project is a fictional ecommerce business that has two core entities: customersand orders. Every customer is a unique individual (a human) and every order reflects a transaction made by one of those customers. As such we’d say that Customers:Orders are One:Many and every order inherits the attributes of those upstream customers making the transaction. In that way, we can easily answer questions such as “What dollar value of transactions are from customers with the last name Smith?”</p><p>The ERD would look something like below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*oUHGKgt65oMEhz8YIc3xmA.png" /><figcaption>Demonstrative ERD for dbt’s Jaffle Shop; Image by the Author</figcaption></figure><p>This is an intentionally reductive model that’s useful for helping get up and running with dbt — it’s so simple that it wouldn’t really merit making an ERD. Real world examples, however, engender exponentially higher complexity and are where ERDs begin to earn their keep.</p><h3>Democratizing information and reducing cognitive overhead</h3><p>During my years working in data at YouTube, one of my colleagues often remarked on the <strong>cognitive overhead </strong>that was required to use our internal data warehouse correctly. That cognitive overhead — how many logical connections or jumps your brain has to make in order to understand or contextualize the thing you’re looking at — was driven by the complexity of the business, entities, and events it was attempting to mirror.</p><p>In the course of their work, analysts needed to navigate across entities such as channels, content owners, videos, digital assets, copyright claims, partners, viewers, users, etc… Seemingly simple questions such as “How much watch time did we have in Germany last month?” could be inconspicuous landmines if not attached to the right entity (e.g., do we care about the viewer’s country? the channel’s declared country? the content owner’s country?). Many-to-many relationships between these entities and non-summable metrics made analysis even trickier.</p><p>There were a handful of analysts that knew how all those pieces worked together and could ask the right questions (in the form of SQL queries) — others queried at their own peril.</p><p>In retrospect we would have greatly benefited from a well-articulated semantic layer to help our analysts navigate this ecosystem without the fear of non-sense. But we were missing an even simpler solution: a well-articulated <strong>entity relationship diagram</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7b4akC9m0SbRcRpA6q3Z4Q.png" /><figcaption>Puzzle Pieces (DALL-E)</figcaption></figure><p>Even if you have a semantic layer at your disposal, you need to figure out how to put the pieces together — that’s why this lo-tech, notably non-modern approach to data modeling is still so critical today. It serves as a <strong>forcing function for the creator</strong>, requiring that they think critically about the real-world events and entities they are trying to model (and how they relate to one another). <strong>For the</strong> <strong>data consumer, the ERD is a powerful enabler, immediately democratizing knowledge</strong> that is otherwise walled-off in many mature organizations.</p><h3>An ERD for your troubles</h3><p>When I first joined Mux in 2021, we had the benefit of building our data warehouse from scratch. This meant that we needed to figure out what core entities and events needed to be represented in that data warehouse. I spent hours with the raw data in our Data Lake and in our public <a href="https://docs.mux.com/api-reference/video">API reference</a> and docs just trying to wrap my head around this ecosystem — and those hours have been some of the most high ROI of my tenure here. The result of that time is the crude document below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7TklIlax_xg-fOuRV9UTLg.png" /><figcaption>Mux Core Product Entities (demonstrative), Image by the Author</figcaption></figure><p>This demonstrative view captures less-than-half of the complexity of our ecosystem (i.e., it does not account for newer products, invoices, viewer sessions, GTM systems, etc.) —still putting this all down on paper helped us quickly understand what our end-state data warehouse would look like in order to reflect reality, and how to connect the dots in our semantic layer.</p><p>ERD in hand, we started building our dbt models and LookML and have arrived at a stable set of tables that reflect our core entities and events, while we continue to build out newer frontiers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6QXvi0zlmaHtALG29dBf9w.png" /><figcaption>Mux DWH Organization around core entities, Image by the Author</figcaption></figure><h3>Closing thoughts</h3><p>In summary, if your data team doesn’t have one already, invest the time to make a thorough entity-relationship-diagram (ERD). While it may feel less-productive than writing SQL or dash-boarding, the time spent here will be some of your most valuable.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ae216b02da1e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/keep-your-friends-close-keep-your-entities-closer-ae216b02da1e">Keep your friends close, keep your entities closer</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Making the case for Analytics Product Managers]]></title>
            <link>https://medium.com/@bigdatajames/making-the-case-for-analytics-product-managers-15ca72fba79d?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/15ca72fba79d</guid>
            <category><![CDATA[product-management]]></category>
            <category><![CDATA[analytics-platforms]]></category>
            <category><![CDATA[data-product-manager]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Wed, 21 Sep 2022 18:09:08 GMT</pubDate>
            <atom:updated>2022-09-21T18:09:08.149Z</atom:updated>
            <content:encoded><![CDATA[<h4>The more we treat Analytics as a Product, the more we need Analytics Product Managers.</h4><blockquote>We sit around and bitch about how TV has ruined the audience for reading — when really all it’s done is given us the really precious gift of making our job harder.</blockquote><blockquote>— David Foster Wallace from Although of Course You End Up Becoming Yourself, by David Lipsky</blockquote><h3>The missing link</h3><p>Over the last decade, analytics teams are starting to look a lot more like software development teams. Companies of every sort are hiring data and analytics engineers, recognizing that analytics functions would benefit from engineering-like discipline and principles (e.g., Don’t Repeat Yourself). Our core modern data stack is built on the back of that ethos: tools such as dbt, LookML, Metaplane, and Hex lean into this core idea that there’s a better way to build and share analytics ecosystems than the ‘old ways’. We’re increasingly treating <a href="https://locallyoptimistic.com/post/run-your-data-team-like-a-product-team/">data as a product</a> but we’re still largely missing the eponymous role that makes all of this work: <strong>the analytics product manager</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*DOx8OdkatSzL4_Z5lTPxtQ.png" /><figcaption>Image by the Author</figcaption></figure><h3>That’s not my job</h3><p>As of writing, a cursory LinkedIn Job Search for ‘analytics engineer’ yielded 900 results; ‘data engineer’ a staggering 20K roles. Somewhat tellingly, there’s not a meaningful way of searching for Analytics PM roles without picking up every PM role on the planet. Suffice it to say they barely exist in the wild. But the need for this expertise and role has never been more profound. Just as traditional PMs help support engineering teams with strategy, prioritization, communication, marketing, legal requirements, market research, and customer interviews, I would argue that all internal analytics teams would benefit from an Analytics Product Manager helping them tackle these questions.</p><p>Just stop to think of all the decisions that need to be made when you set up your analytics practice:</p><ul><li><strong>Market research / Development: </strong>Which storage/compute platform do we use? What business intelligence tool should sit on top that? How do we expose data to end-users more generally? Do we leverage a semantic layer or data dictionary?</li><li><strong>Legal / Compliance: </strong>Who should be provisioned to use those tools? Is it the whole company? By function? Case-by-case?</li><li><strong>Design / UX: </strong>What data model will we follow? (Kimball, SCD2, etc.); do we follow any table naming conventions? Field naming conventions?</li><li><strong>KPIs: </strong>What’s the data warehouse’s SLO or SLA? Do we run batch or need to address real-time use cases? How do we measure the success of our own team? What KPIs do we track?</li><li><strong>Prioritization: </strong>How do we handle inbound requests for data integrations? How do we handle inbound requests for analytics (both from an intake process as well as prioritization process)?</li><li><strong>Marketing: </strong>How do we evangelize new datasets, reports, and analyses within our organization?</li><li>and so on…</li></ul><p>In best case scenarios — enterprising analysts, data scientists, or analytics engineers have leaned-in and tried to push this analytics agenda in their respective organizations. In most cases, however, those questions are addressed via inertia, by consensus, or just not at all.</p><h3>Liked but not loved</h3><p>In my experience at Google, ubiquitous access to and ease of use of our internal analytics platform, #plx, was a double edged sword. Everybody could create a dashboard, and many people did. Very few, however, thought about how that dashboard should solve a business need or question. Fewer yet made themselves available when questions arose around data provenance, meaning, or accuracy. My old colleague Joel Truher (then Director of Engineering for the YouTube Data ecosystem) once referred to the YouTube Data Warehouse as “liked, but not loved”. It was nobody’s baby, and not really anybody’s job to set its direction or rules of engagement for internal data consumers.</p><p>Out in the real world, we’ve never been able to do so much with so little and in some ways that has just made our job harder. dbt lets analysts add data to their warehouses on a whim, Looker and Tableau make dashboard creation a breeze, and low-code tools such as <a href="http://hex.tech/">Hex</a> and <a href="http://superblocks.com/">Superblocks</a> are helping with creation and proliferation of data applications. When the <strong><em>WHAT</em></strong> is easy, we have to pay more attention to the <strong><em>HOW</em></strong> and the <strong><em>WHY</em></strong> of our work, or we end up with our own liked but unloved ecosystems, aimless and rife with entropy.</p><h3>Everybody, Somebody, Anybody, and Nobody</h3><p>One reality of our current situation is that not every analyst is equipped to tackle these problems, nor do they want to. As my friends at <a href="http://hirupert.com/">Rupert</a> have been <a href="https://open.spotify.com/show/6Gu4Ukrfpr8OgAUKDVNrjo">discussing</a>, not every analyst is good at building dashboards from a UX and Design perspective; not every analyst is thoughtful about their own KPIs (e.g., what is the activation moment for an internal data customer?). Similarly, not every analytics engineer wants to think about tool integrations or permissions requirements. And that’s okay! Doing these things well — the product management of data — requires a different way of thinking, skill-set, and interest-set than the typical analyst or analytics engineer might bring to bear. Taking it a step further, it’s unfair to expect them to do it all.</p><p>I’ve always been of the opinion that the best way to ensure that a set of problems get solved is to make it somebody’s job to solve them. In this case, that job is the <strong>analytics product manager</strong>.</p><h3>Where do we go from here?</h3><p>The team at Rupert, among others, are building tooling to help with some parts of this puzzle (focused on the BI / distribution layer) — <strong>effectively product-ifying the product-ification of data</strong>. In the end, perhaps all of these data-related-decisions will be made for us in a shiny new layer of the modern data stack. Until then, we need to bring specialized thought leadership and dedicated ownership to solve these problems.</p><p>As we adjust to this new reality, companies will increasingly hire dedicated PMs to manage their internal analytics systems and platforms. At first it will be the hand-raising analyst that steps into a different kind of function within the same company. Over time, companies will start to target and hire for this role (which I expect will be as sought after as other tenured PMs in the space). And eventually, every data team worth their salt will have one-or-more Analytics PMs to help guide them along their journey.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=15ca72fba79d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building Analytics @Mux]]></title>
            <link>https://medium.com/@bigdatajames/building-analytics-mux-3c4f1bdb2e45?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/3c4f1bdb2e45</guid>
            <category><![CDATA[analytics-platforms]]></category>
            <category><![CDATA[analytics-hiring]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[modern-data-stack]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Mon, 29 Aug 2022 16:03:30 GMT</pubDate>
            <atom:updated>2022-08-29T16:03:30.039Z</atom:updated>
            <content:encoded><![CDATA[<p><em>This story was originally </em><a href="https://www.mux.com/blog/building-analytics-at-mux"><em>published on the Mux Blog</em></a></p><p>We’re building more than just <a href="https://mux.com/blog">better video</a> over here at Mux. We’re also building the teams, systems, and culture that help power online video for developers everywhere. Over the last year, we’ve invested heavily in our analytics function at Mux, and we are excited to share a bit about that journey and what we’ve learned along the way.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Ftenor.com%2Fembed%2F11386682&amp;display_name=Tenor&amp;url=https%3A%2F%2Ftenor.com%2Fview%2Fdata-take-that-for-data-nba-gif-11386682&amp;image=https%3A%2F%2Fc.tenor.com%2F1jRh-_N1-JAAAAAd%2Fdata-take-that-for-data.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=tenor" width="600" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/795fac5ffbacc9177007bc1dfb45a134/href">https://medium.com/media/795fac5ffbacc9177007bc1dfb45a134/href</a></iframe><h3>Building an analytics team</h3><p>Before I joined Mux, the company was focused on product-led growth with a fairly slim footprint and team. Analytics was incredibly scrappy, with a sturdy foundation of data and a few folks who knew how to work with the available systems to get the data they needed for basic insights and reporting. That scrappy team included a data engineer, an analytics engineer, and a data architect. While there is a “walked into a bar” joke to clearly be made here, this was the right group to get things started for us at Mux. Our architect is largely responsible for data modeling and internal systems integrations with our data lake, our data engineer is responsible for all 3rd-party systems integrations with our data lake, and our analytics engineer is responsible for the data warehouse operations more generally (including getting dbt up and running).</p><p>As we neared 100 employees and <a href="https://mux.com/blog/online-video-is-changing-and-mux-is-growing/">another fundraising round</a>, it was time to build out an analytics function that could facilitate decision making and optimization for the next stage of growth — which is when I came in. My job was to build a team of people who would care about all that data and know how to put it to use. It wasn’t always easy, but we got there. Below, I’ll walk through 3 steps that helped us hire an awesome team of business analysts and data scientists.</p><h3><a href="https://www.mux.com/blog/building-analytics-at-mux#specialize-and-trade">Specialize and trade</a></h3><p>Which roles you hire for first should depend a lot on what you’ve already got on the table and what you’re missing. Considering the fungibility of your team’s tools and skill sets will ultimately help you answer an important question: Could a generalist do what we need?</p><p>There are 2 general analytical ecosystems that every company needs to be aware of: marketing analytics and core product/onboarding analytics. While these domains are obviously learnable, they’re rife with their own increasingly specialized toolkits, jargon, and challenges. The right team needs experimentation chops and hard skills built through on-the-ground experience. For Mux, the right team at this stage had to be built with specialized expertise.</p><p>I had no such expertise. When our leadership asked for a cost-of-acquisition analysis on different ad campaigns with a last-touch attribution model, I was terrified — but the request helped me prioritize the first roles to hire on my team:</p><ul><li>Marketing analytics manager</li><li>Product analytics manager</li><li>Product analytics specialist</li></ul><p>Now we just had to find those folks!</p><h3><a href="https://www.mux.com/blog/building-analytics-at-mux#hiring-is-hard-get-help">Hiring is hard; get help</a></h3><p>Let me first say: what a good time to be a talented analyst on the job hunt. You know SQL? Can string together 3 sentences in a row? Heard of a T-test? When can you start? The corollary to that is: woof, what a tough time to be an analytics leader or hiring manager at a growth-stage startup.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/588/0*kMg2vfK7P9eSgxfw" /></figure><p>There were weeks upon weeks of pounding LinkedIn. I had some great conversations with candidates, but more often than not, I heard crickets on the other side of my light-digital-employment-stalking. And then — eureka — it happened: we hired a dedicated business recruiter who was immediately tasked with helping her helpless colleague (ahem, me) build the team.</p><p>This recruiting partner identified candidates through sophisticated LinkedIn searches, set up email-based candidate campaigns, tested language in those campaigns, screened candidates, and held all the follow-up conversations. Within a month, we had a humming pipeline and were starting to push offers out to multiple analysts. Without that support, I’m sure I would still be silently screaming into the LinkedIn void.</p><h3><a href="https://www.mux.com/blog/building-analytics-at-mux#closing-the-deal">Closing the deal</a></h3><p>With offers out, we still had to convince these folks to join the team. Given the hot market, we were prepared for candidates to press hard or take competing offers. While there are myriad factors a candidate might consider in their decision, we took 3 steps that I think helped our candidates say yes:</p><ul><li><strong>Be authentic:</strong> The key to convincing a candidate to join the team is to understand their hopes, dreams, motivators, and demotivators. Starting from that position, it’s easier for the candidate to understand how the role and opportunity can fit those goals; if there’s not a match, then it’s best for both sides to shut the door rather than go through it.</li><li><strong>Be concrete: </strong>We shared clear and specific projects that the analysts would have to tackle after they ramped up, including the core problem statements that we wanted to solve (e.g., setting up a lead scoring framework and model and implementing it with go-to-market teams, customer-level profitability modeling, and cost-of-acquisition and lifetime-value modeling).</li><li><strong>Be transparent: </strong>We tried to be explicit with each candidate about what the day-to-day work would actually entail. This meant highlighting that despite having an analytics dev team, they would have to do a significant amount of up-front work on foundational data and reporting until we could transition to more “pure” strategic analytics in the coming months. (Note: I still can’t stress enough how important it is to have a dedicated analytics engineer so analysts can focus on their core competencies!)</li></ul><h3>Building an analytics stack</h3><p>In parallel with building out the business analytics team, our first task was to set up analytics infrastructure that could scale with the business and its ever-evolving needs. While we already had a robust Airflow infrastructure to load internal product data into a database of our choice (and had previously been using Metabase on Amazon RDS), we had a lot of optionality in building out the rest of the ecosystem.</p><p>In retrospect, I’m lucky that the team took a chance on a BigTech alum like myself; after nearly 8 years working in the analytics space at Google, I knew very little about the increasingly specialized and diverse set of players who contribute to the modern analytics stack for the rest of the world. Fortunately, we had a lot going for us:</p><ul><li>The fragmented constellation of providers has an increasingly clear set of winners (e.g., dbt) that serve as the nucleus of a mature stack.</li><li>Beyond that nucleus, there is a whole opt-in solar system of purpose-built tools that offer plug-and-play functionality with the core providers.</li><li>The cross-tool/service set of integrations (e.g., one-click Salesforce to Snowflake integrations) made our big-data dreams attainable even with a small team.</li><li>The broader analytics community is incredibly welcoming and full of key opinion formers who live and breathe this world, actively sharing opinions on Medium, over Slack, at industry conferences, and beyond.</li></ul><h3><a href="https://www.mux.com/blog/building-analytics-at-mux#the-modern-data-stack-mux">The modern data stack @Mux</a></h3><p>And so, we got to work. We spent time with software providers and other coaches, learned about the ecosystem, evaluated providers relative to our needs (and budget!), and started quickly implementing and integrating services.</p><p>While we are continually evaluating other services (data ops, data catalogues, metrics layers, etc.), our modern stack is coming into focus with an ecosystem built around dbt, Snowflake, and Looker. This nucleus lets us manage our data model, permissions, and documentation and ultimately allows us to get data into end-users’ hands, with an emphasis on self-service exploration. Orbiting around the nucleus, we also have Fivetran and Hightouch, which help us seamlessly move data into and out of third-party systems.</p><p>Finally, we are very excited about our work with <a href="http://www.hirupert.com/">Rupert</a>. Rupert will help us get the most out of all this investment by making business intelligence, analytics, and data more approachable for our business stakeholders.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VbOOiqia7kSTC85V" /></figure><h3><a href="https://www.mux.com/blog/building-analytics-at-mux#it-s-a-big-world-out-there">It’s a big world out there</a></h3><p>Of course, there’s also a whole ecosystem of analytics-adjacent systems that plug into this world (e.g., data enrichment services, marketing and funnel analytics tracking tools, experimentation platforms, etc., etc., etc.) — and of course, there’s a lot more that goes into the success of a stack beyond signing a contract!</p><p>We could (and maybe we will?) share other sagas about our data model (e.g., implementing SCD2 tracking and a star schema around core entities), our development ethos between LookML &amp; dbt, our dashboarding principles (ink:information), and LookML best practices…but we’ll save those stories for another day.</p><h3>Building an analytics culture</h3><p>So we’ve got big data, cool tools, and fun dashboards. Whoopty doo. All that work is vanity if you don’t run the last mile: using all that data to actually make better decisions as an organization. Building a culture around data is the real job of an analytics leader, and that means getting decision makers to look at the dashboard you built for them; it means injecting data into operational workflows for go-to-market teams; and it means understanding what success and failure look like.</p><p>Like our work on the data warehouse and business intelligence layers, our efforts to build a data-driven culture will never be finished — but we have a few principles and projects we are tackling in 2022 that will go a long way to help us scale:</p><ul><li><strong>Make data a habit: </strong>We are standing up a recurring metrics review forum that dives deep into our leading indicators across the business. While this is critical in order for company leaders to have a pulse on the business, it also means that leaders from across the company engage with our analytics infrastructure on a recurring basis and start building that data muscle — an added bonus for our team.</li><li><strong>Inject data into processes and playbooks:</strong> Most importantly, our teams are adopting metrics-based OKRs that will serve as a forcing function to measure and manage our team-level priorities and goals; we’re also building data-driven playbooks for go-to-market teams that live in their tools (e.g., Salesforce). Everyone becomes an analyst without even realizing it!</li><li><strong>Push, don’t pull:</strong> We’re not waiting for anybody to ask us for data; instead, we’re using Rupert and Looker to make source-of-truth business intelligence assets easily searchable and proactively delivering relevant information to interested parties where they work, communicate, and collaborate (e.g., sales portfolios sent every Monday morning to account executives and sales development reps, top-line metrics pushed to leaders on a weekly basis, configuring rules based alerts).</li><li><strong>(Over)communicate, (over)train, (over)communicate (again):</strong> To bring internal users into our world, we lead regular training sessions on our Looker instance, hold office hours weekly, and send weekly Snowflake and Looker feature updates to our users on Slack and by email. We also review our quarterly roadmap (for data infrastructure and analytics) with the leadership team on a quarterly basis to align priorities (which can then be communicated out more broadly).</li></ul><p>If we’re able to hit these points, I’m excited to see what our team and our company will look like in a year!</p><h3>Your thoughts?</h3><p>If you’re going through a similar journey and want to talk about the steps your organization is taking, find us on <a href="https://www.linkedin.com/in/jtisbell/">social</a>, <a href="https://locallyoptimistic.com/">Locally Optimistic</a>, or <a href="https://www.getdbt.com/community/join-the-community">dbt slack</a>. And while we’re not hiring for analytics roles right now, there are a ton of <a href="https://mux.com/jobs/">exciting open roles</a> across Mux. Join us!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3c4f1bdb2e45" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Full Catastrophe Analytics]]></title>
            <link>https://medium.com/@bigdatajames/full-catastrophe-analytics-406a2d8414d3?source=rss-c42688422712------2</link>
            <guid isPermaLink="false">https://medium.com/p/406a2d8414d3</guid>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[analytics-platforms]]></category>
            <dc:creator><![CDATA[James Isbell]]></dc:creator>
            <pubDate>Thu, 18 Aug 2022 23:10:45 GMT</pubDate>
            <atom:updated>2022-08-18T23:54:01.818Z</atom:updated>
            <content:encoded><![CDATA[<h4>It’s time to build a new-front door to the modern data stack</h4><p>In the 1964 film Zorba the Greek, the titular character drops a striking <a href="https://www.youtube.com/watch?v=x9Dy_5zMfEM">one liner</a> early on in the film: <em>“I am a man, so I’m married. Wife, children, house — everything. The full catastrophe.”</em></p><p>The notion of a Full Catastrophe — innate complexity and inherent disaster — is a trope that’s crept its way into multiple facets of pop culture. It’s made its way into <a href="https://www.youtube.com/watch?v=boKFHJlSDsY">songs</a>, <a href="https://www.amazon.com/Full-Catastrophe-Karen-Elizabeth-Lee/dp/1631520245">memoirs</a>, and <a href="https://www.amazon.com/Full-Catastrophe-Living-audiobook/dp/B00115MP3S/ref=sr_1_1?keywords=full+catastrophe+living&amp;qid=1660773221&amp;s=books&amp;sprefix=full+ca%2Cstripbooks%2C149&amp;sr=1-1">self-help books</a>. This note will take it in an entirely new direction: to the modern data stack.</p><p>Let’s just fill in the blanks: “I am an <strong>analyst</strong>, so I use the <strong>modern data stack</strong>. Snowflake, dbt, Hightouch, Fivetran, Looker, Tableau, Atlan, Metabase, Sigma, Hex, Pecan, Sisu, Metaplane — everything. The full catastrophe”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jwSRB0nYPNwHVoG6MalEdw.png" /></figure><p>It feels like we are reaching broad consensus as an industry around the back-end of the stack, wherein dbt, Fivetran, and Hightouch/Census enable broad functionality around your DWH of choice. The front-end is a different story. The hyper-specialization and proliferation of front-end analytics tools has led to an ecosystem teeming with entropy and cognitive overhead. That world can be difficult for us to navigate and harder yet for our customers and business partners. We have never been more productive with this toolkit but I can’t shake the feeling that if we’re not careful, any team’s implementation might turn into an utter catastrophe.</p><h3>One size fits does not fit all</h3><p>As Benn Stancil <a href="https://benn.substack.com/p/business-in-the-back-party-in-the-front">very thoughtfully articulated</a>, the proliferation of front-end data tools has arisen because analytics use cases and thinking can be vastly different from one team to another, and even from one person to another.</p><p>At Mux, our analytics team is increasingly working in <a href="http://hex.tech/">Hex</a> to create standalone/embedded analytical artifacts. We manage a large Looker environment (but not everybody at the company has a seat). And our engineers regularly ask me if they can have a drag-and-drop business intelligence layer that doesn’t have all of the complexity of LookML to set up. (Until this point I’ve generally said, “sorry, no” trying to minimize the catastrophe).</p><p>Other companies might integrate tools such as Sisu, Mode, or Tableau (on top of the rest of the stack). Ultimately, our data teams end up owning multiple business intelligence-like tools for different use cases. As putative data professionals, we can generally handle this complexity and know which tools to use for each use-case. Our users, though, don’t catch on quite as quickly (and they shouldn’t be expected to). Often they don’t know where to find what they need.</p><h3>If you can’t beat ’em join ‘em</h3><p>The role of the data warehouse and consequent business intelligence platform is to break down barriers between data domains, connect the dots with the right primary and foreign keys, and do cross-cutting analyses. With a well designed warehouse we can understand which customer types are creating Zendesk tickets; we can examine the impact of marketing spend on customer acquisition and revenue, blah blah blah.</p><p>All that said — every third party tool your organization uses likely has its own reporting layer. There are elements that can be integrated into your DWH with Fivetran or the likes (and combined with data from the rest of your ecosystem), but deep dive analysis that stays within that domain doesn’t need to be recreated in the DWH and your BI tool. Doing so would only amount to analytics vanity. You’re not going to beat Google Analytics at their own game; you’re not going to out-Heap Heap — just let them win.</p><h3>BizOps gonna spreadsheet</h3><p>Your team might also build the slickest dashboards you could possibly imagine —still I guarantee that there is a spreadsheet at your company called `<strong><em>July 2022 Operating Model</em></strong><em>`</em> and there’s definitely a `<strong><em>2H 2022 Plan</em></strong><em>` </em>in there somewhere too. As data professions, we are intrinsically allergic to this. But I’m here to tell you that this is OKAY. These are sources of truth in their own right, and their structure and content generally doesn’t lend to ingestion into a DWH. Our job is to help our customers get the right data, in the right place, at the right time. And sometimes a spreadsheet fits the bill. Just…no Excel please.</p><h3>Building a front door to the modern data stack</h3><p>Jon Kabat-Zin’s seminal work <a href="https://en.wikipedia.org/wiki/Full_Catastrophe_Living">Full Catastrophe Living</a> reframes the Full Catastrophe as something to celebrate and embrace: something to find peace with. I’d similarly suggest we find a way to navigate this increasingly complex ecosystem without driving ourselves or our stakeholders crazy.</p><p>So far we’ve established that:</p><ul><li>Business intelligence / analytics can live in multiple analytics-focused tools within the same company (e.g., Hex, Sisu, Looker, Mode)</li><li>Domain specific reporting in 3rd Party Tools will be better than you can ever recreate in your BI / Analytics layers</li><li>Spreadsheets are business intelligence too (but again, not excel).</li></ul><p>The more I accept this view of the world — the more important I think it is to build a front-door that can take you to any room in this multi-chambered palace.</p><p>That front door needs to know about all of the tools highlighted above and have context into sources of truth, data quality issues, field definitions, end-user profiles, and more. If a user searches for `<strong><em>2H 2022</em></strong> <strong><em>Plan</em></strong>` then send that sucker a spreadsheet! If they searched for `<strong><em>Web Traffic by Search Terms</em></strong>` — then send them to Google Analytics not to a half-baked Looker report.</p><p>What Salesforce did for the customer record — fueled by integrations with every notable GTM tool — there’s room for an analogue in the analytics ecosystem. The right tool will help unify the modern data stack and let us embrace our own little catastrophe. So, who’s gonna build it?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=406a2d8414d3" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>