Types of data modeling
Dimensional, wide table and multi-source consolidation. Three ways of organize the Gold layer, each with a concrete example and a case in point that shines.
- Know the three patterns with a practical example of each
- Choose the right pattern by use case
- Avoid over-engineering when modeling
Modeling is deciding the shape of your layer Gold. There is no format Universal "right": there is what serves your consumption. The three patterns below cover almost everything you will need. Start with diagram, then see the concrete example of each one in the tabs.
The three patterns, with example
Separate facts (the events, with the metrics) of the dimensions (the entities that describe the event). The fact stores keys and numbers, the dimensions store the attributes. You add it at the time of analysis.
fact_sales (one line per sale):
| data_id | product_id | client_id | value | qty |
|---|---|---|---|---|
| 20260308 | P-42 | C-5521 | 1234,50 | 3 |
dim_produto and dim_cliente (attributes, without repeating each sale):
| product_id | name | category |
|---|---|---|
| P-42 | Runner sneakers | Shoes |
Good when: Heavy BI, lots of aggregated metrics across multiple dimensions, and you want to save storage by not repeating attributes. The cost is needing joins every time.
A single large table, with everything already together, one row per event in the grain you consume. No joins when using.
sales_wide (one line per sale, with the attributes inside):
| date | product | category | customer | city | value | qty |
|---|---|---|---|---|---|---|
| 08/03/2026 | Runner sneakers | Shoes | Silva Store | Recife | 1234,50 | 3 |
Good when: ad-hoc exploration, simple consumption, and mainly consumption by AI via MCP. An agent understands a large table much better than having to figure it out and perform joins. At Nekt, AI-focused Gold tends to be wide.
Gasket multiple sources in a single table, standardizing the fields by a common key. Agency classic: unite media investment from different platforms.
Raw sources (each speaks a language):
| source | spending field | result field |
|---|---|---|
| Meta Ads | spend | leads |
| Google Ads | cost | conversions |
consolidated_media (one line per customer, channel and day, standardized fields):
| date | customer | channel | investment | leads |
|---|---|---|---|---|
| 08/03/2026 | Silva Store | Goal | 320,00 | 18 |
| 08/03/2026 | Silva Store | 410,00 | 22 |
Good when: you need to compare or add data that come from different systems. The job is to find the key common and standardize names and units.
Comparing the three
| Standard | Format | Best for | Ideal consumption | Complexity |
|---|---|---|---|---|
| Dimensional | Fact + dimensions | BI with many metrics | Dashboards, BI tools | High (joins) |
| Wide table | A wide table | Exploration and AI | MCP, exploration, Data API | Low |
| Consolidation | Unified multi-source | Bringing different systems together | Cross-source reporting | Average (standardize) |
Star dimensional model is beautiful in diagram and expensive in practice. If your consumption is exploration and AI, a wide table solves it with less joins, less maintenance and better results in MCP. Just ride a star when the storage volume or BI really requires it.
Once you choose the pattern, you don't need to write the transformation whole in hand. Nekt's MCP creates the query or notebook from natural language: ask "set up a wide sales table per order, with product, customer and channel" and it generates the code for you to review and adjust. Structuring the model is much faster.
Decide now
An agency started out wanting to set up a complete star scheme for media. In practice, consumption was an agent answering questions and a simple dashboard. They switched to a consolidated wide table (a line per customer, channel and day). Less maintenance, and the agent He started getting more correct because he no longer had to guess joins between facts and dimensions.
Reference materials
To delve deeper into each pattern and see how to apply it to your workspace:
↗ Guide: Catalog organization ↗ Dig deeper into the docs: transformations at Nekt