Four projects we’re building on the modern data stack

There’s a version of the modern data stack story that is common. Lakehouse architecture, semantic layers, self-service analytics, democratized data. It’s a good story. Mostly told by vendors and consultants selling stuff.

Here’s a different version: what you get when you’re a data engineering lead inside a state government agency, building things for field representatives who need answers in the field, analysts who don’t write SQL, and compliance teams who need to share data without sharing the wrong things.

Donald Rumsfeld said something that stuck with me years ago: you fight with the army you have, not the one you want. It was interpreted as an insult to staff but, to me, it meant that you don’t plan on theory. You plan based on your reality.

I recently got approval to build four projects inside Snowflake using Streamlit and Cortex. Here’s what they are, why they exist, and what I expect to learn from them.

Platform decisions

Everything we’re building runs inside Snowflake using two capabilities that didn’t exist in their current form two years ago: Streamlit in Snowflake and Snowflake Cortex.

Streamlit in Snowflake means deploying interactive web applications directly inside our Snowflake account. No external servers, no separate deployment pipeline, no firewall exceptions. The app runs in the browser, connects to Snowflake data through Snowpark, and respects our existing role-based access controls natively. In a state government environment where every external endpoint is a conversation and every new vendor relationship is a procurement process, that matters.

Cortex is the AI layer. Large language models (Claude, Llama, Mistral, and others) deployed within Snowflake’s infrastructure perimeter. When I call an LLM from inside Snowflake, the prompt and the response never leave Snowflake’s compute environment. No data goes to Anthropic. No data goes to Meta. Same perimeter as any other Snowflake query, governed by the same RBAC roles, same data residency guarantees.

That last point is not a minor detail. It’s what made enterprise approval possible. The upside I used in every submission (no data leaves the Snowflake perimeter, no external API calls to third-party LLM providers, no model training on state data) sounds great, but security means something to me. Same data residency as any other query.

Project One: BookSmart

The most operationally grounded of the four. We sell scratch tickets through approximately 11,000 retailers across Ohio. This is not a trade secret; it’s public info. Every ticket book has a lifecycle: shipped, issued, activated, sold. Again, generally known info. Field representatives and regional sales managers need to know when a retailer has books that were shipped or issued but never activated.

Currently that visibility comes from a manual report run out of Snowflake. Someone runs a query, exports a file, distributes it. BookSmart is a Streamlit dashboard that field reps and managers will access directly, filter by region, district, and chain, toggle to a zero-activation view that surfaces retailers with idle inventory, and download on demand.

The engineering story behind BookSmart is about the pipeline, not the dashboard. The source table, which I won‘t mention because vendor table structures are proprietary, is a status-event table: one row per status change per book, not a snapshot. Building the dashboard on that directly would be slow and fragile. The real work is an aggregation view that collapses event history into current state per retailer per game, joined to retailer and game metadata. For someone with even minor SQL experience, it’s a light lift. The dashboard will query that view exclusively.

The pipeline runs in streaming mode through StreamSets, landing in S3, loading into Snowflake via Snowpipe. The failure recovery design is a STREAM_AUDIT table that tracks the last confirmed successful offset for every run. When a run starts, it compares the current source high-water mark against the last confirmed offset. A gap means a prior run failed; replay from the last known good position before processing new changes.

I already know the critical part of this project won’t be the dashboard. It’s the aggregation layer and stream logic underneath it. I’d like to get stream mode working on this at the ELT layer.

Project Two: Lobo

The one that gets the most attention when I describe this portfolio to other data leaders (and it also gets me jonesing to work). A conversational analytics chatbot built on Snowflake Cortex Analyst, deployed as a Streamlit app, piloting on jackpot data.

The explanation is simple: a business user types a plain-English question and gets an accurate, immediate answer without writing SQL or opening a report. Cortex Analyst converts the natural language question to SQL using a semantic model, executes it against our Snowflake tables, returns the result.

The semantic model is everything. It’s a YAML file that describes your data domain to the LLM: table names, column names and descriptions, metric definitions, dimension labels. The better the YAML, the better the answers. The design principle I’m building around: never let the LLM infer what a metric means. Every metric that matters to the business needs to be explicitly defined in the YAML as a named metric with an explicit formula. If it’s not there, the LLM will guess. Sometimes it guesses right. You cannot build a production system on sometimes.

Lobo will have a feedback loop from day one. Every interaction, question asked, SQL generated, answer returned, user rating, writes to a LOBO_FEEDBACK_LOG table. Thumbs up, thumbs down, no rating. That table is the primary signal for semantic model tuning. Review the low-rated rows, find the patterns, update the YAML, re-run the test suite, commit a new version.

Version control for the semantic model YAML is not optional; rather, it‘s a governed artifact. The YAML lives in Git and gets uploaded to a Snowflake internal stage that Cortex Analyst reads at query time.

The alpha is narrow by our own design: two basic questions, one table, one domain. Prove correctness in a constrained scope before expanding. The beta path adds a second domain and a routing layer: Cortex Analyst for structured questions against well-defined schemas, Cortex Search for exploratory questions against text-heavy data. Phase 2 adds AI_COMPLETE generating a narrative explanation on top of the Analyst SQL result.

Once we move to beta, we switch from project planning structure to software release structure.

My expectation going in is that conversational analytics is a user experience problem as much as a technical one. The hard part won’t be the technical layer. It’ll be managing user expectations, handling the questions the system can’t answer, and building the feedback loop that makes it improve over time.

Project Three: Redistricting Magic

Ohio Lottery districts are geographic collections of ZIP codes and ZIP+4 groups, each containing a set of retailers. Districts sit inside regions. Regions are fixed. Districts move. Most agencies in my world operate similarly.

As the retailer base changes, districts can become imbalanced. One district carries 150 retailers; another carries 70. Travel burden is uneven. The people responsible for drawing district boundaries currently do this manually, with spreadsheets, with no tooling to model the trade-offs. This is common over time because economic booms aren’t evenly distributed. Fifteen years ago, it was fracking downstate; now it’s data centers mid-state. If there’s any justice in the world, Northeast Ohio is getting its economic boom next.

Redistricting Magic is a Streamlit optimization app that simplifies the process by automating the bejeezus out of it. The user selects a region, picks an optimization mode — balance by retailer count, minimize travel distance, or a blend of both — and the app runs a constraint-based optimizer over the ZIP-to-district assignments within that region. The result is a proposed redistricting: a table of ZIP and ZIP+4 reassignments that reduces the imbalance the user is optimizing for.

The optimizer will run in Snowpark Python using scipy or PuLP as the solver. At least, that‘s my hope… there are some Python quirks in Snowpark. The hard constraint is inviolable: no retailer, ZIP, or ZIP+4 may cross a region boundary. Districts can be redrawn within a region. Regions never change. Every optimizer output gets checked against this constraint before it’s shown to the user.

The blend mode is a weighted objective function: α × retailer_count_variance + (1 − α) × avg_travel_distance. The user sets α with a slider. Zero is pure travel distance optimization. One is pure count balance. The UI will show current versus proposed retailer count and average travel distance per district, plus a map visualization of current versus proposed boundaries side by side.

There’s also an AI_COMPLETE panel that will generate a plain-English explanation of the proposed changes: which districts gain retailers, which lose, what the travel distance impact looks like, what trade-offs the proposal makes. Two or three sentences from a structured prompt with the optimizer output injected as context. Small feature, but it should cut the cognitive load of interpreting a table of numbers.

Proposals export as CSV or XLSX and feed a separate approval workflow. The app produces proposals only; nothing writes back to production district assignments.

The prerequisite I’m focused on first is locating and loading the district-to-ZIP mapping. Everything else waits on that. Never start the optimizer before the mapping exists.

Project Four: Synthetic Data Generator

The most novel of the four, and the one I think has the highest long-term value. Yet it’s also the one that stirs zero excitement in me because it’s all utility and no exploration.

We need to share data with other state agencies and external vendors for integration testing, reporting, and analysis. Sharing real production data creates privacy risk and triggers extensive approval processes. The question is whether we can share data with the same statistical properties as the real data, same distributions, same column relationships, same format, without any real records in it.

This is what the Synthetic Data Generator is designed to do. The user selects a source table from an authorized registry, the app profiles it statistically (distribution per column, null rates, correlations), then generates a synthetic dataset that samples from those distributions rather than from the real data.

The key element is fidelity. My personal research shows simple synthetic data tools generate random values within a range. This produces data that’s technically valid but analytically useless; the distributions don’t match reality, the correlations are gone, and the generated data doesn’t behave like production data when you run analysis on it. The approach here is different: sample from the actual observed distribution. For numeric columns, use the real histogram. For categoricals, use the real frequency table. For dates, preserve the real seasonal patterns. The synthetic data should be statistically (mostly) indistinguishable from real data for most analytical purposes.

PII masking is clear and auditable. The user tags each PII column before generation and selects a masking strategy per type: name substitution from a synthetic corpus, email format-preserving scrambling, phone and SSN format-preserving random replacement, address generation via AI_COMPLETE, date-of-birth age-preserving shift. Every export writes an audit log row per masked column: column name, PII type, masking strategy, timestamp, exporting user. The log becomes the compliance artifact.

The workflow is four stages: Profile, Configure, Preview, Export. Profile runs the statistical analysis and displays column-level distribution summaries. Configure is PII tagging and masking strategy selection. Preview generates 100 rows to inspect before committing to the full export. Export generates the full row count, applies masking, writes to an internal stage, delivers a download button.

AI_COMPLETE plays a specific and limited role: generating plausible free-text values for address and name fields where format-preserving random replacement produces implausible strings. LLM calls are batched; output gets reviewed in the preview stage before export. For everything else, numeric, categorical, date, structured ID fields, statistical sampling handles generation without any LLM involvement.

The non performative governance and security matters as much as the technical design here.

What These Four Projects Add Up To

This should add up to a productive summer, to say the least.