Summary
How can transformations, data quality and dependencies be organized from staging to the dataset?
This Data Framework on Oracle Autonomous Data Warehouse draws on DAMA data management practices. Its Data Staging Area (DSA) classifies information as STG for transactional data, STC for catalog tables and STH for historical data.
Query Mapping produces reusable intermediate tables. The design includes indexes, parameters in tables and business rule checks before each mapping, with email error notifications. Metadata documents attributes and supports versioning and change management; preserving history through partitions is also proposed.
The output dataset comes from the final mapping and its Query Dataset. QuickDBD is presented to support dataset modeling. Each workflow is encapsulated in a stored procedure with one or more mappings and generates one dataset. The monitor records checks, mappings, datasets, start and end, retaining the last N executions. Dependencies allow other workflows to be triggered as a controlled execution network.
Full video
The proposal organizes a Data Staging Area between sources and datasets. The STG, STC and STH categories distinguish transactional data, catalog tables and historical information within the framework. Query Mapping produces reusable intermediate tables, with indexes, parameters in tables and business rule checks before execution. Errors can be reported by email.
Mapping and dataset metadata documents attributes, versions and changes. The design proposes preserving history through partitions, taking output from the final mapping and its Query Dataset, and reusing that dataset as input to other processes. QuickDBD appears as a modeling tool associated with dataset design.
Each workflow is encapsulated in a stored procedure and combines one or more mappings to generate a dataset. The framework monitor records checks, mappings, datasets and workflow start and end, keeping the last N executions. A workflow can trigger others through dependencies to form a controlled execution network.