A schema is a logically organized set of related database tables. In a data warehouse, schemas group information so analysts can find, join, query, and reuse data consistently. section 1.2.2 section 11.3.1
Analytics schemas shape raw sources into information aligned with organizational questions. They may combine, aggregate, or denormalize tables, reducing repeated work and making dashboards and routine analysis faster. section 11.4 section 11.3.1
Effective schema design commonly involves:
- Providing clean, consistently formatted foreign keys such as dates, user IDs, or location keys. section 11.3.1
- Prejoining frequently used datasets so analysts perform fewer joins. section 11.3.1
- Detecting malformed dates, incorrect data types, missing records, and other source-quality problems. section 11.3.1
- Aggregating relevant measures by useful business dimensions, such as weekly temperature and precipitation. section 11.3.1
Weather analytics schema
A weather workflow can retain historical forecasts, mark the most recent forecast, standardize date fields as timestamps, add a day field for joins, and expose weekly aggregates beside business metrics. section 11.3.1
Figure 11.18 Creating tables in analytics schemas involves synthesizing and aggregating multiple sources into one place to reduce time, effort, and inaccuracies when using data.
Use the figure to see how multiple sources are synthesized and aggregated into analytics tables that reduce effort and inaccuracies. section 11.4
