Business Intelligence Network

Data profiling steps—an efficient process for data profiling
Ralph Kimball, a father of data warehouse architecture, suggests a four-step process for data profiling:
1. Use data profiling at project start to discover if data is suitable for analysis—and make a “go / no go” decision on the project.
2. Identify and correct data quality issues in source data, even before starting to move it into target database.
3. Identify data quality issues that can be corrected by Extract-Transform-Load (ETL), while data is moved from source to target. Data profiling can uncover if additional manual processing is needed.
4. Identify unanticipated business rules, hierarchical structures and foreign key / private key relationships, use them to fine-tune the ETL process.

Data warehouse: a foundation for business intelligence

A data warehouse is a repository that stores current and historical data from disparate sources. It’s a key component of a data analytics architecture that creates an environment for decision support, analytics, business intelligence, and data mining.
A data warehouse holds data from multiple sources, including internal databases and SaaS platforms. After the data has been loaded, it can be cleansed, transformed, catalogued, and checked for quality before it’s used for analytics dashboards, reporting, machine learning, or anything else.
Historically, businesses used ETL tools to pipe data into expensive on-premises data warehouse systems. Due to the limited capacity of these expensive systems, business users needed to perform as much prep work as possible before loading data into the system. Today, however, cloud-based data warehouses — including Amazon Redshift, Microsoft Azure SQL Data Warehouse, Google BigQuery, and Snowflake — offer flexible infrastructure whose processing and storage capacity can quickly scale based on an organization’s data needs. More and more organizations are opting to skip preload transformations in favor of running transformations at query time — a process referred to as ELT. This lets business users transform raw data within a data warehouse at any time for any particular use case.
Data warehouses vs. data lakes vs. data marts
Although a data warehouse is an effective and useful way to store data for business analytics, it’s best suited for structured data defined by a schema.
By contrast, a data lake can hold both structured and unstructured data, so in addition to sources defined by schemas, it can hold raw data such as log files, internet clickstream records, images, or social media posts.
A data mart is similar to a data warehouse, but holds data for one specific department or line of business, such as sales or finance. A data warehouse can feed data to a data mart, or a data mart can feed a data warehouse.
Data warehouses, data lakes, and data marts perform different duties. Businesses may use all three for different purposes.

The Purposes Metadata Serves
Metadata serves a variety of purposes, with resource discovery one of the most common. Here, it can be compared to effective cataloging, which includes identifying resources, defining them by criteria, bringing similar resources together and distinguishing among those that are dissimilar.

It also is an effective means of organizing electronic resources, which is an important use given the growth in Web-based resources. Typically, links to resources have been organized as lists and built as static webpages, with the names and resources hardcoded in HTML. A more efficient practice, however, is to use metadata to build these pages. For Web purposes, the information can be extracted and reformatted through use of software tools.

Another use of metadata is as a means of facilitating interoperability and integrating resources. Using metadata to describe resources enables its understanding by humans as well as machines. This permits the most effective levels of interoperability, or how data is exchanged among many systems with disparate operating platforms, data structures and interfaces. In turn, it facilitates resource searches across the network.

Metadata also facilitates digital identification via standard numbers that uniquely identify the resource the metadata defines. Along these lines, another practice is to combine metadata so that it acts as a set of identifying data that differentiate objects or resources, supporting validation needs.

Finally, metadata is an important way to protect resources and their future accessibility. It’s a critical concern given the fragility of digital information and its susceptibility to corruption or alteration. For archiving and preservation purposes, it takes metadata elements that track the object’s lineage, and describe its physical characteristics and behavior so it can be replicated on technologies in the future.