Integrating Foundation Models into the Modern Data Stack: Challenges and Solutions

numbers_station · Tech · 2023 · source Agentic: no · promotional bias: low · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Foundation-model automation for data engineering workflows

Numbers Station wanted to bring foundation models into the modern data stack to reduce manual data engineering work across SQL generation, data cleaning, and record linkage. The hard production issues were scale, prompt brittleness, deterministic-output expectations, and enterprise domain knowledge that generic pretrained models do not know.

Constraints:

• business users wait on data engineering teams for ad hoc SQL queries
• data cleaning rules take a long time to derive and break on edge cases
• record linkage often lacks common identifiers across systems such as Salesforce and HubSpot
• foundation models are expensive and slow on millions or billions of rows
• modern data stack users expect deterministic outputs
• prompt formatting and demonstration choices can change predictions
• enterprise query generation depends on organizational definitions such as active-customer logic
• domain knowledge may live in documents, logs, metadata, semantic layers, or search indexes

Architecture

Numbers Station frames data tasks as generation problems handled by foundation models with prompts and in-context examples. SQL generation translates natural-language business questions into queries; data cleaning applies example-driven transformations; and data linkage compares records across systems in natural language. For scale, the talk recommends composing rules with foundation models, using the model only for last-mile complex cases, and distilling prototypes into smaller task models. For brittleness, it proposes multiple prompts with aggregated predictions, prompt decomposition, and careful demonstration sampling. For missing domain knowledge, it proposes continual pretraining on organizational artifacts and inference-time retrieval through knowledge graphs, semantic layers, or internal-document search.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  Modern_data_stack_sources["Modern data stack sources"] --> Data_warehouse["Data warehouse"]
  Data_warehouse --> dbt_transformation_layer["dbt transformation layer"]
  dbt_transformation_layer --> Business_intelligence_layer["Business intelligence layer"]
  Business_user["Business user"] --> SQL_generation_prompts["SQL generation prompts"]
  SQL_generation_prompts --> Foundation_model["Foundation model"]
  Data_warehouse --> Data_cleaning_prompts["Data cleaning prompts"]
  Data_cleaning_prompts --> Foundation_model
  Modern_data_stack_sources --> Data_linkage_prompts["Data linkage prompts"]
  Data_linkage_prompts --> Foundation_model
  Foundation_model --> Rule_composition_layer["Rule composition layer"]
  Foundation_model --> Model_distillation_pipeline["Model distillation pipeline"]
  Foundation_model --> Multi_prompt_aggregation["Multi-prompt aggregation"]
  Domain_knowledge_layer["Domain knowledge layer"] --> SQL_generation_prompts
  Domain_knowledge_layer --> Continual_pretraining_corpus["Continual pretraining corpus"]
  Continual_pretraining_corpus --> Foundation_model
  Domain_knowledge_layer --> External_memory["External memory"]
  External_memory --> Foundation_model
  Business_intelligence_layer --> Business_user

Components

Data flow

Data originates in systems such as Salesforce or HubSpot, is loaded into warehouses such as Snowflake, transformed with tools like dbt, and visualized in Tableau or Power BI. Foundation-model applications sit across this stack: business questions become SQL prompts, cleaning examples become transformation prompts, and record pairs become linkage prompts. Simple cases can be handled by rules, while complex last-mile cases call the foundation model. Training-time domain adaptation uses organizational documents, logs, and metadata for continual pretraining, while inference-time augmentation retrieves domain knowledge from knowledge graphs, semantic layers, or search indexes.

Why this architecture

The architecture composes foundation models with rules, distillation, prompt aggregation, and domain-knowledge retrieval because the data-stack setting is both high-scale and high-precision. Foundation models are flexible enough to prototype SQL, cleaning, and linkage tasks from prompts, but applying them naively to whole databases is too slow and expensive, prompts are brittle, and generic pretraining misses enterprise definitions. The result is a hybrid system: rules handle easy cases, models handle ambiguous last-mile cases, distilled models scale successful prototypes, and domain knowledge is injected at training or inference time.

Models used: foundation models · autoregressive language models · smaller distilled models · continually pretrained open-source models

Scale

• The talk was a 10-minute lightning talk.
  “we're gonna be going 10 minute talks”transcript, stated
• Rules may handle roughly 80% of a linkage or cleaning problem, leaving complex last-mile cases for foundation models.
  “if there's some very basic rule that can solve 80 of the problem we should use it”transcript, stated
• Naively applying a foundation model to an entire database may mean millions or billions of rows.
  “an entire database that has millions or billions of rows this is just impossible and it's going to be extremely expensive extremely slow”transcript, stated
• Business-user SQL requests can require multiple iterations and take a long time in the traditional workflow.
  “these ad hoc requests usually require multiple iterations and can take a long time to fulfill”transcript, stated

Alternatives considered (roads not taken)

Manual SQL requests through data engineering teams

Rejected because: The traditional workflow requires back-and-forth iterations and can take a long time, while foundation models can generate SQL from natural-language requests for simpler cases.

Handcrafted SQL rules for all cleaning and linkage cases

Rejected because: Rules are slow to develop and brittle on edge cases, so Numbers Station composes rules with foundation models for the last mile.

Call a large foundation model on every row

Rejected because: For whole-database prediction over millions or billions of rows, this is too expensive and slow compared with rules or distilled models.

Rely on a single prompt format

Rejected because: Formatting and demonstration choice can change predictions, so the talk recommends multi-prompt aggregation, prompt decomposition, and careful demonstration sampling.

Use the public pretrained model without enterprise domain context

Rejected because: Enterprise data tasks often require organizational definitions and metadata, so the talk proposes continual pretraining and inference-time external memory.

Patterns

prompt-chaining (prompting)

The talk recommends decomposing prompts with chains to reduce prompt brittleness in deterministic data applications.

model-distillation (modeling)

Numbers Station uses the large foundation model for prototyping and then teaches a smaller model to perform scalable data tasks.

cost-aware-routing (operations)

The system routes easy records to rules and reserves foundation-model calls for complex last-mile examples where model reasoning is needed.

rag-retrieval (retrieval)

For domain knowledge gaps, the talk proposes adding inference-time external memory from a knowledge graph, semantic layer, or search index.

fine-tuning-adaptation (modeling)

For training-time domain knowledge, Numbers Station proposes continually pretraining open-source models on organizational artifacts.

human-in-the-loop-gate (workflow)

The talk distinguishes SQL copilot use cases where a human remains in the loop and latency matters more than bulk throughput.

Tradeoffs

Compose rules with the foundation model.

Chose rules for simple cases and foundation models for complex last-mile examples over foundation-model calls for every record.

Rationale: This keeps cost and latency practical while preserving model flexibility for cases where rules are brittle.

Use model distillation for scalable bulk tasks.

Chose large model for prototyping and smaller distilled model for task execution over running the large foundation model directly across huge databases.

Rationale: The large model is useful for prototyping and teaching, but direct row-level use is too slow and expensive at database scale.

Use external memory at inference time for enterprise knowledge.

Chose knowledge graph, semantic layer, or search index augmentation over expecting the public pretrained model to know organization-specific definitions.

Rationale: Domain knowledge changes by organization and often lives in internal artifacts, so retrieval-time augmentation can provide context without retraining for every query.

Continually pretrain open-source models for domain knowledge.

Chose training-time adaptation on documents, logs, and metadata over only adding context at inference time.

Rationale: Some enterprise knowledge can be embedded into model weights by leveraging untapped organizational data, while inference-time retrieval remains another option.

Aggregate multiple prompts for brittle tasks.

Chose apply multiple prompts and aggregate predictions over trusting a single prompt/demonstration set.

Rationale: Multiple prompts reduce sensitivity to prompt formatting and example choice in workflows where users expect deterministic behavior.

Gotchas (what bit them)

Foundation models worked well for simple SQL but lacked enterprise domain knowledge for complex queries.

Root cause: Generic pretrained models do not know organization-specific meanings such as which date column or active-customer definition to use.

Resolution: Numbers Station proposes injecting domain knowledge through continual pretraining or inference-time external memory.

Rule-based data cleaning is slow to build and breaks on unseen cases.

Root cause: Manual SQL rule development has to anticipate edge cases across messy enterprise data.

Resolution: Use in-context examples so a foundation model derives patterns automatically, and compose rules with the model for the hardest cases.

Calling a large model across an entire database is not viable.

Root cause: Foundation models are large, slow, and expensive, especially for millions or billions of row-level predictions.

Resolution: Distill successful prototypes into smaller models and use rules where rules solve simple cases.

Prompt brittleness is especially problematic in data workflows.

Root cause: Small changes in prompt formatting or example selection can change predictions, while data-stack users expect deterministic outputs.

Resolution: Apply multiple prompts and aggregate predictions, decompose prompts into chains, and sample demonstrations carefully.

Data linkage lacks common join keys.

Root cause: Systems such as Salesforce and HubSpot may represent the same customer without a shared identifier.

Resolution: Feed both records to a foundation model and ask in natural language whether they refer to the same entity, using rules for simple cases.

Evaluation

The talk reports qualitative research and experiment findings rather than a production KPI dashboard. Evaluation signals include observed behavior on SQL generation, data cleaning, data linkage, prompt-format perturbations, manual versus random demonstrations, and comparison of large models to distilled smaller models. It mentions performance gaps and improvements from multi-prompt aggregation but does not disclose numeric scores.

Metrics: prompt-format sensitivity · manual versus random demonstration performance gap · prediction aggregation improvement · scale feasibility on millions or billions of rows · latency for human-in-the-loop SQL copilot use · large-versus-distilled model gap

Outcomes

• Numbers Station applied foundation models to SQL generation, data cleaning, and data linkage workflows in the modern data stack.
• The talk identifies a practical architecture for mixing rules, large foundation models, distillation, prompt aggregation, and domain-knowledge augmentation.
• The approach can reduce data-engineering back-and-forth for simple natural-language SQL generation.
• The architecture preserves rule-based paths for simple high-scale tasks and reserves foundation-model calls for more complex cases.
• Domain knowledge can be supplied either through continual pretraining or inference-time external memory.

The talk is a 10-minute lightning presentation and does not disclose deployment volume, customer count, latency numbers, or production KPI deltas. Its concrete scale claims are qualitative except for rule coverage around 80% of some problems and the infeasibility of direct model calls across millions or billions of rows.