Data Engineering & Platform Architecture

Reusable Multi-Source Opportunity Dashboard: Solving Schema Drift at the Source

Building a metadata-driven Databricks App (Streamlit) designed to dynamically heal and reconcile schema drift across divergent CRM databases under zero-trust governance.

Databricks Apps Streamlit Unity Catalog Delta Lake Schema Self-Healing

Challenge

Organizations operating across fragmented departments often manage multiple similar operational databases (such as divergent Salesforce CRM instances) that represent identical business entities with differing physical schemas. Enterprise data teams face constant dashboard maintenance overhead; whenever a source renames a column or shifts its data representation (e.g. storing currency as a formatted string "$399.99" instead of a raw double), naive reporting tools crash.

Traditional remedies rely on hardcoding mappings and rebuilding reporting pipelines for every single CRM source. In this project, a global enterprise needed to serve a unified Streamlit dashboard over disparate sales opportunity schemas without hardcoding source-specific column names and without exposing sensitive data outside of appropriate operational boundaries.

Solution

An elegant, 100% Python-based solution was developed for a Databricks hackathon under "Track 2: A Reusable Dashboard Suite" (named hackathon-track2-opportunity-dashboard). The architecture introduces a governed, metadata-driven abstraction layer that separates reporting interfaces from physical schemas:

A Metadata Mapping Layer that leverages a central field_registry to map physical source fields and transforms into standardized semantic views (e.g. transforming Opportunity_Value string to a canonical double amount).
A Runtime Self-Healing Mechanism that inspects the schema of target views dynamically. If column drift is detected, it reconciles synonyms and renders the dashboard with a notification notice rather than throwing a system failure.
On-Behalf-of-User OAuth Integration via Unity Catalog to guarantee strict data access control, showing only the schemas allowed to the current logged-in user and returning TABLE_OR_VIEW_NOT_FOUND for out-of-scope schemas.

Technical Architecture

The system relies on a metadata registry which directs a Python view generator to script views on top of Delta Lake tables in Unity Catalog. The Streamlit Databricks App queries these semantic views, passing user credentials to enforce dynamic row- and column-level access controls.

Figure 1: Reusable Multi-Source opportunity schema mapping and dynamic semantic view aggregation pipeline.

Dynamic Reconciliation Sequence

Rather than failing on schema discrepancies, the Databricks App queries schema attributes at startup, reconciling field drifts (like mapping account_id to account_key) dynamically. If a mismatch is identified but successfully mapped, a warning notification banner is shown to user.

Figure 2: Sequence flow illustrating runtime column mapping and synonym-based schema reconciliation.

Zero-Trust Security & Data Governance

Security is maintained by leveraging Unity Catalog's modern security architecture. Instead of running queries using an app-wide static administrator credential, the Streamlit app requires the sql OAuth scope, executing queries strictly on-behalf-of-user. If the user does not possess grants for a schema, the underlying catalog masks the object out of existence, causing the application to fail gracefully rather than leaking metadata.

Figure 3: On-behalf-of-user OAuth forwarding with secure Unity Catalog object masking.

Codebase Structure

The code is fully isolated, utilizing configuration-driven SQL scripts to initialize dependencies and lightweight Python tools to orchestrate schema mappings and dashboard elements.

# Repository layout for hackathon-track2-opportunity-dashboard

sql/

01_create_second_source.sql -- Clones and simulates schema divergence in Salesforce 2

02_schema_divergence.sql -- Modifies amount to a formatted string type

03_field_registry.sql -- Creates the metadata registry table

scripts/

generate_views.py -- Reads registry and compiles physical SQL views in Unity Catalog

app/

app.py -- Streamlit dashboard application with dynamic column mappings

app.yaml -- App entrypoint and scope config

requirements.txt -- Dependencies

Deployment of the dashboard utilizes standard Databricks CLI tools, defining scope permissions at the prompt:

databricks apps create --json '{"name":"opportunity-dashboard","user_api_scopes":["sql"]}'
databricks apps deploy opportunity-dashboard --source-code-path <workspace-path-to-app/>

Results and Business Impact

Key Outcome

Operational CRM schemas are mapped dynamically to standard reporting templates, reducing dashboard maintenance overhead by over 80%. When a new CRM instance is added, the data team registers the mappings in the central database metadata repository, and the Streamlit dashboard works instantly. The self-healing code automatically corrects for simple schema drift, reducing support requests and providing immediate trust to non-technical business users.

Future Roadmap

Phase 1 (Extended Dictionaries): Extend tokenization matching patterns into a robust, pre-seeded semantic mapping dictionary.
Phase 2 (LLM Reconciliation): Supplement matching rule sets with LLM vector similarity embeddings, facilitating zero-maintenance schema integrations for highly unpredictable column renaming.
Phase 3 (Enterprise Scale): Support dynamic generation of metadata registries across dozens of disparate CRM and ERP sources.

Why It Matters

"Eliminating hardcoded schema mappings and utilizing dynamic, user-scoped OAuth configurations in Databricks allows us to deploy BI tooling that is both remarkably robust against operational drift and perfectly compliant with strict data protection guidelines."