Advanced Modeling
Advanced Modeling describes the practice of building powerful, role-specific analytical models by combining filtered data sources under Custom Models. The architecture is built around two distinct filter layers that serve fundamentally different purposes — understanding both is the key to modeling efficiently at scale.
The Two-Layer Architecture
Lakehousecat separates data loading from data visibility through two independent filter layers:
Layer 1 — Data Source Filters: controlling what gets loaded
When you configure a Data Source, you are deciding which data is worth loading into the system at all. Not all data in a source system is analysis-relevant — loading everything would increase ingestion time, storage consumption, and reduce the accuracy of AI-generated queries.
Data is physically copied from the source system into ClickHouse — either as a Full Load (complete reload) or as an Incremental Load (only new or changed records). This happens through Jobs in the Operations section. The Data Source filter determines exactly what is included in that copy.
Once configured, the same filtered Data Source can be shared across multiple Custom Models. The data is loaded once — all models that reference it work from the same ClickHouse dataset.
Layer 2 — Custom Model Filters: creating logical perspectives
After data is loaded, Custom Model filters create logical perspectives on top of the already-loaded data. These filters are implemented as ClickHouse VIEWs — they contain no data themselves, only a definition of which slice of the underlying dataset is visible.
This is the key efficiency advantage: you load data once at the Data Source level, and then create as many Custom Model perspectives as needed at zero additional storage or ingestion cost. A Sales model, a Marketing model, and an Executive model can all share the same loaded data — each seeing only their relevant slice.
The Modeling Workflow
Step 1: Design and filter Data Sources
Before creating any Custom Model, prepare the data sources that will feed it. For each source system:
- Navigate to Workspace → Data Sources.
- Click the pencil icon on the data source.
- Go to the Filters tab.
Note: The Filters tab shows the live schema from the connected source. Ensure the connection is valid before configuring filters.
Schema Filter
Controls which database schemas are included. Use Include or Exclude — not both simultaneously.
| Option | Behavior |
|---|---|
| Include Schemas | Only the selected schemas are passed to the semantic layer. All others are ignored. |
| Exclude Schemas | All schemas are included except the selected ones. |
Include and Exclude are mutually exclusive. Selecting any item in Include clears the Exclude list, and vice versa.
Recommendation: Use Include to explicitly whitelist only the schemas relevant to the use case. This avoids loading system schemas or unrelated data.
Table Selection
Controls which tables within the filtered schemas are included. Supports wildcard patterns using *.
| Option | Behavior |
|---|---|
| Include Tables | Only the listed tables are included. Supports schema.table_name qualified names. |
| Exclude Tables | All tables are included except the listed ones. |
Wildcard example: sales.* includes all tables in the sales schema. *.tmp_* excludes all tables starting with tmp_ in any schema.
Column Selections
Per-table column filtering. For each entry, select a table and choose which columns to include or exclude. Only the specified columns are visible to the semantic layer and AI queries.
Column selections are additive — configure them for multiple tables within the same data source independently.
Column Row-Level Filters
Row restrictions applied at the semantic layer, equivalent to a WHERE clause on the loaded data.
| Part | Description |
|---|---|
| Table | The table to filter |
| Column | The column to filter on |
| Operator | EQUALS, NOT_EQUALS, IN, CONTAINS, etc. |
| Value | The filter value. For IN, provide a comma-separated list. |
Use cases:
- Multi-tenant isolation:
tenant_id EQUALS current_tenant - Exclude test data:
environment NOT_EQUALS test - Restrict to active records:
status EQUALS active
After configuring all filters, click Save. The filters take effect on the next data load.
Step 2: Add a Timeline Data Source
For any model involving time-based analysis, create a Timeline data source. The Timeline provides the time dimension axis — enabling the AI to correctly interpret queries like "last quarter", "year over year", or "last 30 days".
Timeline setup:
- Set Start Date and End Date to span the full range of historical and future data
- Choose a Granularity matching your lowest reporting level (typically
day) - Configure Fiscal Year settings if your organization uses non-standard fiscal periods
One Timeline per Custom Model is sufficient, regardless of how many transactional data sources are linked.
For detailed configuration, see Data Sources — Timeline.
Step 3: Create the Custom Model
- Navigate to Workspace → Models → Custom Models.
- Click the + button.
- Enter a name and optional description.
- Click Create to open the Custom Model editor.
Select a Provider Model
In the Provider Model dropdown, select the AI provider configuration this model should use. The Provider Model determines which LLM processes all queries made through this Custom Model — including natural language queries and semantic extraction.
Provider Models are configured and managed by Administrators. Each request to the provider generates API costs.
Link Data Sources
In the Datasources tab, add each Data Source this model should have access to. Include the Timeline data source here as well.
The Custom Model now has access to all linked data sources and their filtered schemas. This combined view forms the basis of the semantic layer.
Step 4: Apply Custom Model Filters — Define Perspectives
With data sources linked, you can apply a second filter layer directly on the Custom Model. These filters restrict what each model exposes from the already-loaded data — without triggering any reload.
- In the Custom Model editor, go to the Filters tab.
- Select the data source you want to configure a perspective for from the dropdown. Data sources with existing filters are marked with ●.
- Configure the filters: the controls are identical to Data Source filters (schema, table, column, row-level).
- Click Save.
The critical difference from Data Source filters:
| Data Source Filter | Custom Model Filter | |
|---|---|---|
| Purpose | Controls what data is loaded into ClickHouse | Controls what the model exposes from already-loaded data |
| Effect | Physical data copy — affects storage and ingestion | Logical ClickHouse VIEW — no storage cost, no reload |
| Shared across | All models that use this data source | This model only |
| When to use | Remove irrelevant data from the system entirely | Create different perspectives on the same loaded data |
If multiple Custom Models need different perspectives of the same source data, load the data once with a broad Data Source filter, then use Custom Model filters to scope each model's view. This is more efficient than creating separate Data Sources with overlapping data.
Step 5: Trigger Semantic Extraction
After linking data sources, build the semantic layer:
- In the Data Sources editor, open each linked data source.
- Go to the Operations tab.
- Click Run Semantic Extraction.
Repeat for all data sources linked to the model. The semantic extraction reads the filtered schema and generates the metadata the AI uses for query generation.
Re-run semantic extraction after any Data Source filter change. Custom Model filter changes do not require re-extraction.
Step 6: Enrich the Semantic Layer with Descriptions
Semantic extraction generates an initial layer automatically. Enriching it with precise descriptions significantly improves AI query accuracy.
In the Data Sources editor, add descriptions at each level:
| Level | What to add |
|---|---|
| Datasource | High-level description of the data source, its purpose, and business context |
| Table | What the table represents, its purpose, and key business rules |
| Column | Column meaning, units, valid value ranges, and business interpretation |
| Relations | Cross-table and cross-datasource join hints (e.g., "orders.customer_id links to customers.id") |
Example — Column description:
order_amount: Total order value in EUR, excluding VAT. Positive values are orders; negative values are refunds.
Example — Relation hint:
sales.orders.customer_id → customers.customers.id
Click the microphone icon next to any description field to dictate descriptions by voice. Use the magic icon (✨) that appears after transcription to automatically clean up and optimize the phrasing.
Step 7: Share the Custom Model
Users do not interact with Data Sources directly — they interact with Custom Models. As long as a Custom Model is not shared, it is completely invisible to all users except its creator. This is intentional: objects are private by default, and access is granted deliberately.
A Custom Model is not visible to users until it is explicitly shared.
- In the Custom Models list, click More (⋯) on the model.
- Select Share.
- Add the relevant groups or users and set the permission level.
- Confirm.
| Permission | What the recipient can do |
|---|---|
| Read | Use the model in sessions |
| Write | Edit the model configuration |
| Owner | Full control, including re-sharing and deletion |
Grant Read to end users. Grant Write to Builders who collaborate on model configuration.
Sharing a Custom Model is a data access decision, not just an organizational one. Anyone who receives access to a Custom Model can query all data the model exposes — including every table, column, and row that passes through the configured filters.
Before sharing, verify:
- Which data sources are linked and what data they contain
- Whether sensitive data (HR, payroll, personal data, financial records) is within the model's scope
- Whether all intended recipients are authorized to access that data under your organization's data governance policies
If the model's data scope is not appropriate for the recipient group, apply or tighten the Custom Model filters before sharing. See Sharing — Data Access Implications for a full discussion.
Step 8: Lock Stable Models
Once a model and its data sources are working correctly in production, lock them to prevent accidental changes.
- In the Data Source or Custom Model editor, toggle Locked.
Locked objects remain active and queryable but cannot be modified without explicitly unlocking them first.
Role-Based Data Perspectives
The two-layer architecture makes it possible to give different teams different views of the same underlying data — without duplicating the source system, reloading data, or writing access-control logic in SQL.
The Pattern
Why this is efficient
All four Custom Models share one Data Source. The data is loaded into ClickHouse once. Each Custom Model adds a logical ClickHouse VIEW that filters the already-loaded data to its relevant scope. No additional ingestion is required when adding a new perspective — only a new Custom Model with its filter configuration.
This is fundamentally different from creating separate Data Sources for each team: separate Data Sources would load the same underlying data multiple times, multiplying storage and ingestion cost.
When to create separate Data Sources instead
Create separate Data Sources (rather than one shared source with CM filters) when:
- Different teams need data from genuinely different schemas or databases (e.g., Sales from PostgreSQL, Inventory from MSSQL)
- The filter boundaries are so different that a shared source would be cumbersome to maintain
- Row-level access control is so strict that a shared load is a security risk
How to build role-based perspectives
Step 1 — Create a broadly filtered Data Source
Connect the source database and apply filters that include all schemas and tables relevant across all planned perspectives. Exclude only system schemas, staging tables, and columns that should never appear anywhere (e.g., raw PII that is not needed by any team).
Step 2 — Run Semantic Extraction
Trigger semantic extraction on the Data Source. The AI builds the semantic layer from the filtered schema.
Step 3 — Create one Custom Model per perspective
| Custom Model | Provider Model | Linked DS | CM Filters | Target Group |
|---|---|---|---|---|
| Sales Assistant | anthropic-sonnet | company-erp | schema=sales, region=EMEA | Sales group |
| Marketing Assistant | anthropic-sonnet | company-erp | schema=marketing | Marketing group |
| Warehouse Assistant | anthropic-sonnet | company-erp | schema=warehouse, location=DE | Logistics group |
| Executive Overview | anthropic-sonnet | company-erp | schemas=sales+marketing | Management group |
Step 4 — Share each Custom Model with the appropriate group
Use the Share dialog on each Custom Model to assign it to the relevant groups. Users in the Sales group see only the Sales Assistant; users in Logistics see only the Warehouse Assistant.
The result: each team has a purpose-built AI assistant with a curated, accurate view of their domain — backed by a single, shared data load.
Storage Trade-off: ClickHouse Dataset Replication
Every Data Source — including each filtered view of the same source database — is loaded and stored as a separate dataset in ClickHouse during ingestion. Custom Model filters do not create additional data storage — they create logical views on existing ClickHouse data.
If you create five separate Data Sources from one database, the ingested data for those five scopes is stored five times. If you create five Custom Models with filters on one shared Data Source, the data is stored once.
Guidance:
- Use a single, broadly filtered Data Source and differentiate perspectives at the Custom Model filter level where possible
- Create separate Data Sources only when data genuinely comes from different connections or requires independent ingestion schedules
- Define perspectives at the team or use-case level, not at the individual user level
- Use row-level filters to narrow each perspective rather than creating separate data sources
- Monitor ClickHouse storage in the Monitoring section as the number of Data Sources grows
Multi-Source Modeling Example
Scenario: Build a "Sales 360" model combining data from four separate source systems.
| Data Source | Type | Data Source Filters |
|---|---|---|
sales-db | PostgreSQL | Include schema: sales — Include tables: orders, order_items, invoices |
crm-db | MySQL | Include schema: crm — Include tables: customers, contacts, companies |
inventory-db | MSSQL | Include schema: dbo — Exclude tables: tmp_*, staging_* |
time-dimension | Timeline | 2018–2030, daily granularity, fiscal year starting April |
All four data sources are linked to one Custom Model: Sales 360 Assistant. No Custom Model filters are applied here — this model has access to the full filtered scope of all linked sources.
For a regional variant, a second Custom Model Sales 360 EMEA links the same four data sources and adds a CM row-level filter on sales-db: region EQUALS EMEA. No additional data loading required.
After semantic extraction and description enrichment, the AI can answer:
- "What is the revenue per customer segment for Q1 of the current fiscal year?"
- "Which products had the highest inventory turnover last month?"
- "Show me the top 10 customers by invoice value year to date."
Best Practices
- Filter narrowly at the Data Source level: Only load data that is analysis-relevant. Removing irrelevant schemas and tables improves ingestion speed, reduces ClickHouse storage, and increases AI query accuracy.
- Use Custom Model filters for perspectives, not for data reduction: If two teams need different views of the same data, use CM filters. Only create a separate Data Source if the data itself needs to be loaded differently.
- Load once, view many times: A single Data Source can serve many Custom Models. This is more efficient than separate Data Sources with overlapping data.
- One Timeline per model: A single well-configured Timeline is sufficient for most analytical models.
- Describe relationships explicitly: The AI cannot infer foreign key relationships from schema alone. Always describe cross-table and cross-source joins in table or column descriptions.
- Re-run extraction after Data Source filter changes: Any DS filter change requires a new semantic extraction. CM filter changes do not.
- Share after validation: Test the Custom Model in a session before sharing it with user groups.
- Lock stable models: Once a model is working correctly in production, lock it to prevent accidental changes.
- Define perspectives at team level: Create one model per team or use case, not per individual user.
Answer Quality: What Determines It
The quality of answers in Lakehousecat sessions is determined by two independent factors. Understanding both helps you set expectations and take corrective action when answers fall short.
1. Data Quality
Lakehousecat is not an ETL tool and does not clean or transform data. It works with the data as it exists in your source systems after it has been loaded into ClickHouse. The quality of your source data directly determines the quality of the answers.
Factors that reduce answer quality:
- Inconsistent or contradictory data (e.g., duplicate records, conflicting values across tables)
- Missing values in key columns the AI relies on for aggregation or filtering
- Poorly named columns with no descriptions in the semantic layer
- Loading more data than necessary — irrelevant tables and columns increase the chance of the AI making incorrect joins or selections
Recommendation: Load only the data that is analytically relevant. Ideally, connect Lakehousecat to a clean analytical layer — a data warehouse or mart — rather than directly to raw transactional databases. The closer your source data is to an analytics-ready state, the better the answers will be.
2. Model Quality
The AI provider model you select for a Custom Model directly affects answer quality. More capable models (typically the latest, higher-tier offerings from each provider) produce more accurate SQL, better handle ambiguous questions, and are less likely to hallucinate column names or relationships.
Trade-off: More capable models are also more expensive per API call. Every session message, semantic extraction, and model training operation consumes API credits from your provider account.
Important: All LLM responses are probabilistic, not deterministic. Even highly capable models can produce factually incorrect answers when the question is ambiguous, the semantic layer is incomplete, or the underlying data has quality issues. Critical analytical results should always be validated against the source data before being used for decisions.