Database Structure

In this chapter it is covered everything that is accesible for the customer in the subscriber database. We’ve organized it into layers that build on each other. At the bottom you have raw replicated data, and as you to the next layer the data becomes more business-friendly and ready for actual use. This works well because some users need detailed raw data while others just want pre-built reports.

Source Tables

These are copies of production tables that we’ve decided are worth replicating. We don’t copy everything - only tables that customers actually need for analysis. The selection process considers what’s business-critical, what customers ask for, and obviously what we’re allowed to share from a security standpoint. We maintain these as proper replicas, not just snapshots. All the foreign key relationships are preserved, so you can still do complex joins across multiple tables. The sync process is pretty smart - it only updates records that have actually changed, which keeps the overhead reasonable. We use the change tracking system to figure out what needs updating and then we update records at subscriber in desired frequency. Source Tables are the foundation for everything else in subscriber. If you need granular data for custom analysis or data science work, this is where you start. They give you direct access to the detailed records without having to go through any pre-built views that might not fit your use case.

Standard Views

This is where we try to make life easier for business users. Standard Views take the raw source data and package it into something that actually makes business sense. Instead of having to figure out complex joins and remember cryptic column names, users get views with descriptive names and pre-calculated fields. We built these based on the most common requests we get. Rather than having every customer write the same complex SQL to get basic metrics, we’ve done the work once and made it available to everyone. It’s faster for users and ensures consistency in whatever metrics matter. All views only reference Source Tables - never production directly.

Customer Specific Objects

Sometimes the standard stuff isn’t enough. Enterprise customers often have unique requirements - maybe they need to combine our data with their own, or they have industry-specific calculations that don’t make sense for everyone else. That’s what this layer handles. Each customer can have custom specialized views that do exactly what they need, or pre-computed tables for complex calculations that would be too slow to run on demand. We handle these through a formal process - customers submit requirements, we assess the impact and effort, and then we agree on timelines and maintenance terms.

Covered information

  • Object Definitions - Business descriptions so you understand what tables actually means and how it should be used.
  • Complete technical spcification for every table and view - dates, identity information, column names and their id, data types, nullability, collation, identity, computation or constraints.
  • Entity Relationship Diagrams - Show how tables connect to each other through foreign keys, they’re essential for understanding the database structure.
  • Data Lineage Diagrams - Trace data from source tables to standard views that customers use, they show not just where data comes from, but what transformations happen along the way.
  • Relationship tables - Visually understanable tables with all of the relations relevant for specific object with the possibility to move to another object and explore it in a more detail.
  • View Specifications - Full SQL code for all of the views, that is designed to be both executable and readable so you could study it to understand how the view works and what type of data it provides.