Building the Foundation: A Governed Manufacturing Analytics Layer with Snowflake

Part 1 of 2 — From raw data to a governed semantic layer, Cortex Analyst tools, and a Streamlit KPI dashboard built in three prompts.

A proof-of-concept architecture and engineering walkthrough

Manufacturing analytics · Governed data · Semantic layer · Cortex Analyst · Streamlit

Introduction

Most manufacturing analytics stacks are good at the same thing: they can tell you what happened. A KPI dashboard will show production output, downtime minutes, OQA compliance, and loss events for a machine, a shift, or a date range. What a dashboard cannot do on its own is help an engineer figure out why a number moved, or where to look next. That second step — investigation — has historically stayed a manual, human-driven process: open a filter, eyeball a chart, guess at a loss code, pivot a table, repeat.

This article walks through a proof-of-concept (POC) built on Snowflake that extends a manufacturing KPI dashboard into an agentic investigation layer. The POC keeps the dashboard, but adds a governed semantic layer, three Cortex Analyst tools, a Cortex Search glossary, and a Cortex Agent that orchestrates them behind a conversational Streamlit interface. The goal was not to replace the analyst — it was to compress the first few steps of an investigation into a single natural-language question, while keeping every number the agent returns traceable to a governed metric definition. This first part covers the foundation that makes all of that possible; Part 2 builds the agent on top of it.

This is a natural continuation of an earlier piece on using Snowflake CoCo to build an end-to-end analytics application. That project focused on how an AI coding agent accelerates the build of a semantic layer and a Streamlit app. This article picks up where that one left off: once the application exists, how do you turn it from a dashboard someone reads into a copilot someone can question? A note on what is real and what is modeled: the dashboard build comes from the actual conversation, while the schema and semantic view snippets are representative, and all data is synthetic.

The Manufacturing Use Case

The POC models a manufacturing analytics environment with governed data across three domains:

• Production — output, attainment, yield, scrap, by machine, shift, and part.
• Downtime — total downtime, downtime by machine/shift/operator, loss codes, loss reasons, downtime trends, and loss-booking compliance.
• Quality (OQA) — required inspections, completed inspections, missing inspections, and compliance by machine and date.

A fourth capability, Cortex Search over a loss code glossary, sits alongside the three analytical domains. It is deliberately not a metrics source — it exists only to ground an INVESTIGATE NEXT recommendation with a plain-language procedure or definition once the numeric investigation is already underway.

The Data Layer

We use an isolated database with a conventional RAW → GOVERNED separation. RAW holds the synthetic manufacturing tables as generated; GOVERNED holds the same data after cleaning, type standardization, and the access controls that would apply to any analytical layer built on top of it. The tables cover production records, downtime/loss events, OQA inspection records, and reference dimensions for machines, operators, shifts, and loss codes.

-- Representative schema separation used in the POC

CREATE DATABASE IF NOT EXISTS MFG_INTELLIGENCE;

CREATE SCHEMA IF NOT EXISTS MFG_INTELLIGENCE.RAW;

CREATE SCHEMA IF NOT EXISTS MFG_INTELLIGENCE.GOVERNED;

CREATE SCHEMA IF NOT EXISTS MFG_INTELLIGENCE.SEMANTIC;

CREATE TABLE IF NOT EXISTS MFG_INTELLIGENCE.GOVERNED.DOWNTIME_EVENTS (
    MACHINE_NAME      STRING,
    SHIFT_NAME        STRING,
    OPERATOR_NAME     STRING,
    OPERATOR_CODE     STRING,
    FROM_TIME         TIME,
    TO_TIME           TIME,
    DURATION_MINUTES  NUMBER,
    LOSS_CODE         STRING,
    LOSS_REASON       STRING,
    LOSS_STATUS       STRING,
    PRODUCTION_DATE   DATE
);

A representative table definition reflecting the shape of the data used by the Downtime Analyst — field names follow the POC’s Streamlit output shown later in this article.

This layer is intentionally unglamorous. It exists so that every analytical tool built on top of it starts from the same cleaned, conformed source, instead of each tool reaching back into raw extracts with its own assumptions.

From Three Prompts to a Working Dashboard

Before any of the semantic layer or agent work described in this article began, the underlying KPI dashboard itself was built conversationally, directly inside Snowflake, without hand-written code. The whole exchange took three prompts and roughly ten minutes, and it is worth walking through because it sets the tone for everything that follows: the same conversational pattern that built the dashboard is what later powers the investigation copilot.

Figure 1. From a discovery question to a deployed Streamlit dashboard in three prompts.

The first prompt was not a build request at all — it was a discovery question: “What databases do I have access to?” The agent listed four databases (PRODUCTION_DB, SNOWFLAKE, SNOWFLAKE_SAMPLE_DATA, and a personal database), then, once the build request came in, ran fourteen exploratory queries against PRODUCTION_DB.MFG_DATA on its own initiative before writing a single line of dashboard code. That step matters: the agent was scoping the actual table shapes — BOOKING_REPORT, MACHINE_MASTER, LOSS_IDLE_TIME — rather than guessing at column names.

The second prompt was the build request itself: “Create a nice Streamlit application to showcase the important KPIs along with the necessary filters so the user can make use of it.” From that single sentence, the agent designed a filter panel (machine, date range, shift), six KPI cards (total records, total loss duration, active machines, active operators, distinct loss codes, average daily loss), four charts (loss by machine, loss by loss code, daily loss trend, loss by shift), and two supporting tables (top operators by loss time, detailed loss records) — all scoped to the tables it had already inspected.

The third phase was deployment, and it is the part most worth noting for an engineering audience: the agent’s first approach — writing the Streamlit file into a stage through a SQL COPY INTO statement — did not match how Snowflake expects a Streamlit app’s files to be structured. Rather than surfacing that as an error for a human to debug, the agent recognized the mismatch, tried an intermediate approach (ALTER STREAMLIT to update the main file directly), and when that did not fit either, corrected course a second time: it created a proper stage, staged the file correctly, and issued a direct CREATE STREAMLIT statement against it.

CREATE OR REPLACE STREAMLIT PRODUCTION_DB.MFG_DATA.PRODUCTION_KPI_DASHBOARD
  ROOT_LOCATION = '@PRODUCTION_DB.MFG_DATA.APP_STAGE'
  MAIN_FILE = 'streamlit_app.py'
  QUERY_WAREHOUSE = ANALYTICS_WH
  TITLE = 'Production KPI Dashboard'
  COMMENT = 'Production KPI dashboard with loss tracking, machine
             performance, and operator analysis';

The statement that actually created the working app, taken directly from the build conversation — the exact object the rest of this article’s dashboard screens are modeled on.

Designing the Semantic Layer

The semantic layer is where Cortex Analyst semantic views define what a metric means: what “total downtime” aggregates, what grain “by shift” operates at, and which dimensions are valid to filter or group by. This is the layer that keeps the agent from generating ad hoc SQL against raw tables on every question. Instead, the agent calls a semantic view through Cortex Analyst, and the view’s own definition of the metric is what comes back — not a definition re-derived by the LLM in the moment.

# Representative semantic view structure (Cortex Analyst)

semantic_view: downtime_analyst
  tables:
    - name: downtime_events
      base_table: MFG_INTELLIGENCE.GOVERNED.DOWNTIME_EVENTS

  dimensions:
    - machine_name
    - shift_name
    - operator_name
    - loss_code
    - loss_reason
    - production_date

  facts:
    - name: total_downtime_minutes
      expr: SUM(duration_minutes)
      description: "Total booked downtime in minutes"

A representative semantic view definition. The description field is what the agent uses to decide whether this fact answers the current question.

This is also the layer where the article’s central claim is enforced structurally, not just in prompt language: because the agent can only retrieve facts that a semantic view exposes, there is no path for it to compute a metric the semantic layer has not already defined.

Building the Three Analytical Capabilities

Production Analyst

Handles production output, production trends, attainment, yield, scrap, and breakdowns by machine, shift, and part. In the investigation workflow this tool is called secondarily — after a downtime question has been scoped — to check whether a production-side change (a part changeover, a new job) coincides with the observed downtime pattern.

Downtime Analyst

Handles total downtime, downtime by machine/shift/operator, loss codes, loss reasons, downtime trends, and loss-booking compliance. This is the primary tool for Part 2’s demo scenario, and the one most directly reflected in the Streamlit screens shown later: reported loss events, downtime by operator, and downtime by shift all come from this capability.

Quality Analyst

Handles OQA inspection compliance — required inspections, completed inspections, missing inspections, and compliance by machine and date. It is not part of the primary demo scenario in Part 2 but follows the identical governed-tool pattern as the other two.

All three tools share one property: they are read-only. None of them can modify the underlying governed data, which keeps the copilot’s blast radius limited to analysis regardless of what a user asks.

Conclusion: The Foundation Is Ready

At this point, the foundation is in place. Governed data sits in a clean RAW → GOVERNED structure, a semantic layer defines what every metric means exactly once, and three read-only analytical capabilities — Production, Downtime, and Quality — answer questions against that single source of truth. On top of it sits a Streamlit dashboard that took three prompts to build and still does what dashboards do best: show the current state of the shop floor. But a dashboard stops at “what happened.” The moment an engineer asks why downtime on a machine climbed, the investigation falls back to filters, charts, and guesswork. In Part 2, Beyond the Dashboard, we put an agent on top of this foundation — a Cortex Agent that orchestrates these governed tools, grounds its answers with Cortex Search, and turns a single natural-language question into an evidence-first starting point for investigation.