SQL queries can extract data for technical or business users, or manage existing data. Documenting these queries helps clarify their impact.
Each query type has its own place in DataGalaxy's meta-model, and a few choices to make about how the queries are organized.
How to model selection queries
Selection queries extract information and make it available to technical or business users.
What is a selection query?
A selection query separates the data source from the query result made available to users.
How to represent a selection query in DataGalaxy
Represent the data source and the query result as separate objects in DataGalaxy.
Link the data set to the database with the uses relation at the data-set level. This provides precise lineage.
What does a selection-query example look like?
The example extracts data from a CRM database and makes it available in a data set named Query: customer analysis, linked to the database with the uses relation.
What to decide before you model a selection query
- Link granularity: decide whether to create the link at the table and data-set level, as in the example, or at a finer level. Base this choice on your use cases and your automation and maintenance capabilities.
- Query identification: distinguish functional and technical names, and consider a dedicated attribute so filtered views can highlight the queries.
- Organization: classify queries under one or more umbrella applications when the Usages module's tree would otherwise become overloaded.
How to model management queries
Insert, update, and delete queries manage data rather than publish it.
What is a management query?
A management query represents a data-management operation such as insertion, update, or deletion.
How to represent a management query in DataGalaxy
Represent this type of query with a Processing module object because it describes a technical transformation rather than published data.
What does a management-query example look like?
The example deletes data from the kb_demo_customer table: the processing KB demo Delete inactive customers has this table as both input and output. The extraction query shown in green (the data set from the previous example) serves a functional purpose, which distinguishes it from the management query.
What to decide before you model a management query
Balance the required information granularity with the maintenance capabilities available for the use case.