Now Reading
Information Modeling: The Unsung Hero of Information Engineering: Structure Sample, Instruments and the Future (Half 3)

Information Modeling: The Unsung Hero of Information Engineering: Structure Sample, Instruments and the Future (Half 3)

2023-06-01 09:24:37

Welcome to the third and last installment of our collection “Information Modeling: The Unsung Hero of Information Engineering.” Should you’ve journeyed with us from Part 1, the place we dove into the significance and historical past of information modeling, or joined us in Part 2 to discover varied approaches and strategies, I’m delighted you’ve caught round.

On this third half, we’ll delve into information structure patterns and their affect on information modeling. We’ll discover normal and specialised patterns, debating the deserves of assorted approaches like batch vs. streaming and lakehouse vs. warehouse, and the function of a semantic layer in advanced information structure.

We’ll additionally survey the panorama of information modeling instruments, evaluating business and open-source choices, and ponder the potential of AI in information modeling. To wrap up, we’ll introduce information modeling frameworks like ADAPT™ and BEAM, designed to information efficient information mannequin creation. Please be part of me as we take this thrilling journey towards understanding information structure higher.

Information Structure Sample with Information Modeling

Bringing collectively the most effective of the person approaches and strategies and understanding the frequent issues, we should all the time preserve the larger information structure image in thoughts. Typically you need a information vault modeling to your first layer when your supply system is consistently altering, otherwise you want a dimensional mannequin in your final layer to construct information apps on prime of it or permit self-serve analytics. However how are you doing this?

For these, we have to have a look at information structure per se. On this chapter, I’ll listing among the most typical architectural patterns I’ve seen, however with out going into all particulars of every.

Normal Function Information Structure Sample (Medallion, Core, and many others.)

Let’s begin with the one which suited me finest, the one you could have seen in a single type or one other. After I began at a consulting agency known as Trivadis, and right here it was known as “Foundational Structure of Information Warehouse”. We adopted finest practices similar to `staging>cleaning>core>mart>BI`.

Basic Information Structure of a Information Warehouse | Picture from Data Warehouse Blueprints, September 2016

We’d design these layers from the top-down strategy mentioned above and resolve the info modeling approach for every layer relying on necessities.

Let’s have an in depth have a look at every layer, as these are elementary for each information structure undertaking, and they’re going to assist us perceive why we’d wish to mannequin completely different layers otherwise. The next layers or areas belong to a whole Information Warehouse (DWH) structure however might be carried out into any information lake or analytics product you employ.

Staging Space

Information from varied supply programs is first loaded into the Staging Space.

  • On this first space, the info is saved as it’s delivered; due to this fact, the stage tables’ construction corresponds to the interface to the supply system.
  • No relationships exist between the person tables.
  • Every desk accommodates the info from the ultimate supply, which shall be deleted earlier than the following supply.
  • For instance: In a grocery retailer, the Staging Space corresponds to the loading dock the place suppliers (supply programs) ship their items (information). Solely the newest deliveries are saved there earlier than being transferred to the following space.

Cleaning Space

It should be cleaned earlier than the delivered information is loaded into the Core. Most of those cleansing steps are carried out within the Cleaning Space.

  • Defective information should be filtered, corrected, or complemented with singleton (default) values.
  • Information from completely different supply programs should be remodeled and built-in right into a unified type.
  • This layer additionally accommodates solely the info from the ultimate supply.
  • For instance: In a grocery retailer, the Cleaning Space might be in comparison with the realm the place the products are commissioned on the market. The products are unpacked, greens and salad are washed, the meat is portioned, presumably mixed with a number of merchandise, and all the things is labeled with value tags. The standard management of the delivered items additionally belongs on this space.


The information from the completely different supply programs are introduced collectively in a central space, the Core, via the Staging and Cleaning Space and saved there for prolonged intervals, usually a number of years.

  • A major job of the Core is to combine the info from completely different sources and retailer it in a thematically structured method somewhat than separated by origin.
  • Typically, thematic sub-areas within the Core are known as “Topic Areas.”
  • The information is saved within the Core in order that historic information might be decided at any later time limit.
  • The Core ought to be the one information supply for the Information Marts.
  • Direct entry to the Core by customers ought to be averted as a lot as doable.

Information Marts

Within the Information Marts, subsets of the info from the Core are saved in a type appropriate for person queries.

  • Every Information Mart ought to solely comprise the info related to every utility or a novel view of the info. This implies a number of Information Marts are sometimes outlined for various person teams and BI purposes.
  • This reduces the complexity of the queries, rising the acceptance of the DWH system amongst customers.
  • For instance, The Information Marts are the grocery retailer’s market stalls or gross sales factors. Every market stand provides a particular choice of items, similar to greens, meat, or cheese. The products are offered in order that they’re accepted, i.e., bought, by the respective buyer group.

???? And as a Basis, we’ve Metadata: Several types of metadata are wanted for the graceful operation of the Information Warehouse. Enterprise metadata accommodates enterprise descriptions of all attributes, drill paths, and aggregation guidelines for the front-end purposes and code designations. Technical metadata describes, for instance, information constructions, mapping guidelines, and parameters for ETL management. Operational metadata accommodates all log tables, error messages, logging of ETL processes, and way more. The metadata types the infrastructure of a DWH system and is described as “information about information”.

Not each structure is similar

Just some information warehouses or information engineering initiatives have exactly this construction. Some areas are mixed, such because the Staging and Cleaning areas, or otherwise named. The Core is typically known as the “Integration Layer” or “(Core) Information Warehouse.”

Nonetheless, the general system should be divided into completely different areas to decouple the opposite duties, similar to information cleansing, integration, historization, and person queries. On this method, the complexity of the transformation steps between the person layers might be decreased.

Why use it in the present day?

Isn’t it wonderful that one thing from 2016 remains to be so present? That’s why information modeling is entering into vogue once more, because it has by no means been completely outdated.

Databricks renamed these layers with bronze, silver, and gold to grasp it could be just a little higher and known as it Medallion Architecture, but it surely’s one thing each BI engineer works with day-after-day. In essence, it’s the identical idea.

Or if we have a look at the Data Engineering Lifecycle launched by the Fundamentals of Data Engineering, we see an analogous image however on a fair greater stage. You would apply the completely different layers to the Storage layer within the picture beneath.

The information engineering lifecycle, by Fundamentals of Data Engineering

Specialised Information Structure Patterns

On this chapter, we have a look at patterns which may not be thought of in formal information modeling, however every of its choices will extremely affect the info modeling half. Moreover the overall information structure, I name these specialised information structure patterns as these are higher-level information structure choices.

Batch vs. Streaming

An apparent determination you have to take early on is should you want real-time information for important information utility or batch with close to real-time micro batching each minute, the hour is sufficient.

Nonetheless, to today, steaming is primarily optionally available. Suppose you inform the enterprise staff you possibly can obtain close to real-time with hourly batching; they are going to be glad. The most recent up-to-date information will little affect your information analytics. Most circumstances are taking a look at historic information anyway.

However, some business-critical information options want real-time. Nonetheless, remember that the effort and challenges shall be way more important as you possibly can solely partially get better if a stream fails. However the good concept is to arrange your information pipeline as even based mostly on the streaming strategy, and due to this fact can go decrease and decrease with the latency of your batch to attain close to real-time.

Information Lake/Lakehouse vs. Information Warehouse Sample

As Srinivasan Venkatadri says accurately: “Information modeling, normally, also needs to discuss information (open codecs), Lakehouses, and strategies to transform or extract structural information from semi or unstructured would assist.” That is exactly the place information lake/lakehouse or information warehouse patterns apply. With these, you have to take into consideration these questions.

With the lengthy struggle between ELT with Information Lakes and conventional ETL with Information Warehouses, the completely different architectures enormously affect the info modeling structure.

I’ll solely go into some element right here, as I have written extensively about these subjects. However the determination to go together with an information lake or lakehouse will doubtless affect the info modeling to a extra open supply technique and instruments. Additionally, as you’re dumping information into your lake, you have to work with giant distributed information, the place Table Format makes a number of sense, to get database-like options on prime of information.

Semantic Layer (In-memory vs. Persistence or Semantic vs. Transformation Layer)

The information lake determination can be extremely influential with the brand new Rise of the Semantic Layer, the place you run a logical layer on prime of your information within the case of an information lake and the potential of an information warehouse on prime of your information marts.

That is an attention-grabbing one, as we talked in part I and part I concerning the Conceptual, Logical, and Physical Data Models the place a Semantic Layer may change the logical mannequin. The semantic layer shops the queries in declarative YAMLs, and information not endured in bodily storage is executed at run time or when the info is fetched. Integrating the semantic layer into advanced information structure and general information modeling is sensible. It will probably serve a variety of information customers with completely different necessities, similar to direct SQL, API (REST), and even GraphQL, with the nice advantage of writing enterprise logic solely as soon as. Right here once more, as enterprise logic is the most important treasure of information modeling, we’ve many new choices to mannequin our information with semantic layers.

The counter structure sample is to go together with a transformation layer that sometimes might be achieved with dbt or a Data Orchestrator, the place you persist every step into bodily storage. Primarily to realize sooner velocity querying these information units and reusing the info units with different marts or information apps. The transformation layer and its transformational modeling decide between table formats vs. in-memory formats, query push-downs, and data virtualization.

???? Materialized Views: A change layer the place you persist information is similar to what materialized views did earlier than the modern era of data engineering.

Fashionable/Open Information Stack Sample

Subsequent up is Modern Data Stack structure, or Open Data Stack. This sample is principally for sheer choice we’ve these days to select from the open information stack. It’s its structure to decide on the precise instrument finest suited to the necessities at hand within the firm.

It issues should you selected an open supply instrument, v0.5, which may get out of order in a few years or should you selected the v0.1 that made the crackdown the road. However these are difficult bets, and solely skilled information architects and other people working within the area could make choices based mostly on instinct for the precise method a undertaking is progressing.

It’s also to say no to yesterday’s new shiny instrument, resist the urge, and wait till the product is extra mature. However on the identical time to take dangers and guess on a instrument that’s open to success and embraces open-source philosophy as a substitute of constructing a worse copy in-house. Every enterprise firm both makes use of a closed-source resolution to deal with the data engineering lifecycle or makes its stack or newer choice, betting on a developed framework within the open.

Crucial vs. Declarative Sample

The declarative strategy is manifesting itself increasingly. Began throughout the fronted revolution the place react was declaring parts, Kubernetes did it for DevOps, Dagster revolutionized orchestration with Software-Defined Assets or us at Airbyte, the place we created a Low-Code Connector developer kit to create information integrations in minutes by filling out a YAML file.

It is just doable due to drastically decreased complexity and the necessity to write many boilerplates. The declarative method describes what and the how is taken care of within the framework.

I’ve way more to say, however you possibly can learn extra on our information glossary on declarative and the way it pertains to Functional Data Engineering and what the alternative, imperative sample is.

Notebooks vs. Information Pipelines Sample

A sample straight associated to orchestration is the notebooks versus information pipeline sample, the place you possibly can write and run a pipeline solely a notebook or a mixture of mature, unit-tested information pipelines with a secure orchestrator with all bells and whistles included.

Centralized and Decentralized Sample

Suppose you’re within the area for some time. What number of cycles have you ever been via from doing all the things server aspect, switching all the things to server rendering, and forth and again with the battle of shopper vs. server, microservices vs. monoliths, and recently, central cloud information warehouses vs. a yet-to-show decentralized Data Mesh. And plenty of extra forth and again will observe.

No matter you select, begin with simplicity. You may all the time add complexity later when the product and resolution mature.

All the things else

There are such a lot of extra patterns I’ll write about however at a while later. For now, I depart you with pointers above, and I’ll come again to it someday later.

Information Modeling Instruments

Well-liked information modeling instruments embrace Sqldbm, DBDiagrams, Enterprise Architect, and SAP PowerDesigner. These instruments are broadly used within the trade and provide highly effective options similar to information modeling, profiling, and visualization.

Open-source information modeling instruments similar to MySQL Workbench and OpenModelSphere are free and provide important options for creating information fashions. They’re useful for small initiatives and supply a chance for information engineers to be taught information modeling expertise.

Selecting the best information modeling instrument depends upon the group’s wants, funds, and undertaking measurement. Massive organizations might require costly enterprise-level instruments, whereas small companies might go for open-source instruments. Choosing a instrument that’s straightforward to make use of, has the wanted options, and is appropriate with the group’s database administration system is crucial.

Different instruments are, whose key options are Information Product Design, Information Modeling, Enterprise Glossary, Collaboration, Reusability, and Open API.

dbt might be seen as a change modeling instrument. Dagster can be utilized as a DAG modeling instrument. And so forth. However you too can use ExaliDraw for Markdown-based drawing or (a number of templates for AWS, Azure, and many others.) to attract architectures.

Should you battle to suppose in dbt tables and SQL is just not the SQL is just not the precise language. One drawback, SQL is a declarative language, which is a blessing and a curse. Particularly should you do recurring queries, SQL will get nasty spaghetti coded, which once more dbt helps with Jinja Templates, however because it’s not a language, with out a lot in-built assist. Reconfigured (not free) was constructed for individuals with out years of expertise, focusing closely on enterprise logic.

What about ChatGPT?

With all of the hype of generative AI, particularly ChatGPT, it asks if AI can mannequin our information.

If we recap the knowledge from this collection, most of it condenses into translating enterprise necessities into an information mannequin or semantics, additionally known as enterprise logic. As Chad Sanderson talked about in his post:

“The exhausting a part of information improvement is knowing how code interprets to the true world. Each enterprise has a novel method of storing information. One buyer ID may very well be saved in a MySQL DB. One other may very well be imported from Mixpanel as nested JSON, and a 3rd could be collected from a CDP. All three IDs and their properties are barely (or considerably) completely different and should be built-in”.

As Chad continues, and I strongly agree, he says, “As sensible as ChatGPT could be, it will must perceive the semantics of how these IDs coalesce into one thing significant to automate any step of modeling or ETL. The algorithm should perceive the true world, grok how the enterprise works, and dynamically tie the info mannequin to that understanding.”

This isn’t AI or ML anymore; that’s extra instinct, long-term expertise in working within the area, and experiencing among the challenges to get a sense for it.

See Also

If there’s a place the place you don’t want to make use of automation, then it will be in information modeling and the general information structure. Right here we’d like discussions and a deep understanding (area information) of the sphere and information modeling.

Alternatively, based mostly in your information mannequin, you should use AI to generate schemas or the bodily mannequin of a database. Brainstorm with ChatGPT based mostly in your created information mannequin if one thing is lacking or if it sees one thing that may be modified. Typically you get nice insights from it by offering the answer you got here up with.

Possibly most significantly, an information engineer will all the time be wanted to evaluate the end result, perceive and make sense of the info in entrance of him and perceive the general information circulation of the group. That may solely be delegated for some time.

Information Modeling Frameworks

Moreover instruments, there are additionally useful frameworks that aid you mannequin your information, asking the precise questions.


ADAPT says that greater than current information modeling strategies like ER and dimensional modeling is required for OLAP database design. That’s why ADAPT is a modeling approach designed particularly for OLAP databases. It addresses the distinctive wants of OLAP information modeling. The essential constructing blocks of ADAPT are cubes and dimensions, that are the core objects of the OLAP multidimensional information mannequin.

Though ADAPT was created for OLAP cubes within the previous days, most strategies and frameworks additionally apply to common information modeling these days. There are 9 ADAPT database objects, and their symbols illustrate how you can use logos with easy examples.

Legend of ADAPT Framework | Supply unknown

Why ADAPT over ER and dimensional modeling
ADAPT is taken into account superior to ER and dimensional modeling for a number of causes:

  1. Incorporation of Each Information and Course of: ADAPT incorporates each information and course of in its strategy, which is especially helpful for designing (OLAP) information marts.
  2. Logical Modeling: ADAPT emphasizes logical modeling. This prevents the designer from leaping to options earlier than totally understanding the issue.
  3. Enhanced Communication: ADAPT enhances communication amongst undertaking staff members, offering an on a regular basis foundation for dialogue. This improved communication results in higher-quality software program purposes and information fashions.
  4. Complete Illustration: ADAPT permits for the illustration of an (OLAP) utility in its entirety with out compromising the design as a result of limitations of a modeling approach designed for an additional function.

In abstract, ADAPT is a says to be a extra versatile, complete, and communication-enhancing modeling approach for OLAP databases in comparison with ER and dimensional modeling.

BEAM for Agile Information Warehousing

BEAM, or Enterprise Occasion Evaluation & Modeling, is a technique for agile requirement gathering designed explicitly for Information Warehouses, created by Lawrence Corr and Jim Stagnitto within the Agile Data Warehouse Design e-book. BEAM facilities requirement evaluation round enterprise processes as a substitute of solely specializing in studies.

It makes use of an inclusive, collaborative modeling notation to doc enterprise occasions and dimensions in a tabular format. This format is simply understood by enterprise stakeholders and simply carried out by builders. The concept is to facilitate interplay amongst staff members, enabling them to suppose dimensionally from the get-go and foster a way of possession amongst enterprise stakeholders.

The rules of BEAM embrace:

  • Modelstorming: Enterprise intelligence is pushed by what customers ask about their enterprise. The technical setting is secondary. Storyboarding the info warehouse to find and plan iterative improvement
  • Asking tales with 7W: Telling dimensional information tales utilizing the 7Ws (who, what, when, the place, what number of, why, and the way—mannequin by instance, not abstraction; utilizing information story themes.
  • Visible modeling: Sketching timelines, charts, and grids to mannequin advanced course of measurement – merely
  • Enterprise Pushed: Effectively-documented information warehouses that take years to deploy will all the time have to be up to date. Enterprise customers will look elsewhere. Often with enterprise items: “I would like it now, or I’d somewhat persist with Excel resolution ….”
  • Buyer Collaboration: Finish customers’ enterprise information is your best useful resource.
  • Responding to Change: Selling change via the above actions, resulting in weekly supply cycles.
  • Agile design documentation: Enhancing star schemas with BEAM dimensional shorthand notation

Lawrence Corr emphasizes the significance of asking the precise questions or “information tales.” For example, a buyer’s product buy may set off questions concerning the order date, buy and supply areas, the amount purchased, buy cause, and shopping for channel. A complete image of the enterprise course of is shaped by fastidiously addressing these questions and offering the idea for technical specs.

Widespread Information Mannequin

There are examples of normal information fashions, so you do not want to start out from scratch. The idea behind these approaches is to rework information contained inside these databases into an ordinary format (information mannequin) and a standard illustration (terminologies, vocabularies, coding schemes), then carry out systematic analyses utilizing a library.

For instance, each mannequin wants dimensions similar to buyer, area, and many others. Some references I discovered you see beneath:

Making use of Information Modeling / Greatest Practices?

On the finish of all we discovered, how do you apply information modeling in follow? This collection gave you a very good introduction and a few tips that could search for once you begin with information modeling.

Under I discovered the Best Practices for Data Modeling, which guides you thru among the important steps from one, designing the info mannequin to eleven, verifying and testing the applying of your information analytics:

  1. Design the info mannequin for visualization
  2. Acknowledge the calls for of the enterprise and intention for related outcomes
  3. Set up a single supply of fact
  4. Begin with easy information modeling and increase later
  5. Double-check every step of your information modeling
  6. Arrange enterprise queries in accordance with dimensions, information, filters, and order
  7. Carry out computations beforehand to stop disputes with finish prospects
  8. Seek for a relationship somewhat than only a correlation
  9. Utilizing modern instruments and strategies
  10. Enhanced information modeling for improved enterprise outcomes
  11. Confirm and check the applying of your information analytics

One other wonderful finest follow for dbt might be present in How to Write a High-Quality Data Model From Start to Finish Using dbt by Madison or making use of dimension mannequin with Kimball and dbt in Building a Kimball dimensional model with dbt (GitHub) by Jonathan.

The Way forward for Information Modeling

As I’ve delved into the intricacies of information modeling within the earlier elements of this collection, it’s clear that we’re witnessing a revolution in the way in which we understand, handle, and work together with information. The digital age is characterised by info overload, and information modeling offers the framework to harness this information and remodel it into invaluable insights.

Reflecting on the way forward for information modeling, I can’t assist however really feel a way of optimism combined with anticipation. It’s thrilling to ascertain a world the place data-driven choices are the norm somewhat than the exception, and I genuinely consider we’re heading in the right direction.

Rising applied sciences like AI and machine studying promise to streamline additional and automate the method of information modeling. There’s potential for AI to tackle a extra lively function in information modeling, translating advanced enterprise logic into coherent information constructions.

This imaginative and prescient, nevertheless, doesn’t imply we will grow to be complacent. It’s extra essential than ever for information professionals to remain on prime of evolving trade developments and strategies. After which, there’s the matter of the assorted information modeling instruments obtainable. The longer term will doubtless increase the variety of open-source and proprietary choices. However on the finish of the day, choosing the precise instrument will all the time come all the way down to your particular necessities, constraints, and the character of your information.

We additionally should bear in mind the significance of information structure patterns. As information grows in quantity and complexity, discovering probably the most appropriate structure turns into more and more important. The selection between batch vs. streaming or information lake vs. information warehouse may considerably affect your information modeling efforts. So choices round implementing a semantic layer, choosing a contemporary/open information stack, or navigating between centralized and decentralized patterns.

As I wrap up this collection of information modeling, I encourage you all to continue learning, experimenting, and pushing the boundaries of what’s doable along with your information. Keep in mind, the essence of information modeling is simplicity, regardless of how advanced the underlying information could be. The way forward for information modeling is fixed change. However one is inevitable; will probably be important for each firm. I’m excited to see how the sphere evolves and the way we, as information practitioners, proceed to drive this evolution :).

Studying extra about Information Modeling

Under are some sources and useful feedback I gathered from you all; thanks for the precious suggestions all through writing this text.


  • MongoDB Programs and Trainings, providing complete guides to understanding and mastering MongoDB. Link
  • Ebook: “Agile Information Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema” by Lawrence Corr. It is a foundational textual content for understanding dimensional modeling in an agile context. Link
  • Ebook: “Information Warehouse Blueprints: Enterprise Intelligence in der Praxis” by Dani Schnider, Claus Jordan, Peter Welker, and Joachim Wehner. This is a wonderful useful resource for German audio system. Link
  • Ebook: “Data and Reality” – a timeless information to information modeling, really helpful by Jenna Jordan.
  • Video: “Information Modeling within the Fashionable Information Stack” – a invaluable useful resource for understanding the present state of information modeling. Link
  • Article: “Introducing Entity-Centric Information Modeling for Analytics” on Preset – a very good learn for understanding an entity-centric strategy to information modeling. Link
  • Web site: by Shane Gibson – a useful resource for lowering the complexity of managing information for Leaders, Analysts, and Consultants. Link
  • Podcasts: “Shane Gibson – Making Information Modeling Accessible – The Joe Reis Present” on Spotify. Link

Useful feedback

  • Use well-defined ontologies that describe what you are promoting and relationships between parts utilizing frequent trade ideas, as urged by Rayner Däppen. Link to comment
  • Preserve your information fashions up to date, aligned, documented, validated, and verified with the enterprise. This may make sure the fashions precisely replicate the present state of the corporate.
  • Take into account the place to construct the semantic/metrics layer to permit for quick, interactive analytics/dashboards and how you can make it obtainable to a number of instruments to keep away from varied definitions. Link to comment
  • From “Data Modeling in the Modern Data Stack” – computation is now the costly a part of information modeling, not storage. A hybrid strategy is usually utilized in trendy information stacks to stability complexity, computational price, information redundancy, and flexibility.

Source Link

What's Your Reaction?
In Love
Not Sure
View Comments (0)

Leave a Reply

Your email address will not be published.

2022 Blinking Robots.
WordPress by Doejo

Scroll To Top