Install
Transformation & Modeling terms
dbt, SQL, semantic layers and the modeling of data for analysis.
Common table expression A named, temporary result set defined at the start of a SQL query and referenced later in that query. Data mart A smaller, focused subset of a data warehouse built around the needs of one business function or team. Data modeling The practice of organizing data into entities, relationships and schemas that make it accurate and efficient to query. Data vault A modeling methodology that splits data into hubs, links, and satellites to make a warehouse auditable and adaptable to change. dbt (data build tool) An open-source tool from dbt Labs that lets analytics teams write data transformations as version-controlled, tested SQL. Denormalization Deliberately introducing redundancy into a data model to reduce joins and speed up read queries. Dimension table A table of descriptive attributes, such as customer or product details, that a fact table references for context. ELT (extract, load, transform) A data integration pattern that loads raw data first and transforms it afterward using the warehouse's own compute. ETL (extract, transform, load) A data integration pattern that transforms data before loading it, unlike ELT which transforms after loading. Fact table The table in a dimensional model that stores measurable, numeric events, each row linked to the dimensions describing it. Incremental model A transformation that updates only new or changed rows on each run instead of rebuilding the entire table. Materialized view A query result stored physically and refreshed on a schedule or trigger, rather than recomputed on every read. Normalization Structuring a database so each fact is stored in exactly one place, reducing redundancy and inconsistency. One big table A denormalized modeling approach that joins facts and dimensions into a single wide table instead of many. Reverse ETL The practice of syncing modeled data from a warehouse back out into operational tools like a CRM or ad platform. Schema evolution The ability of a table to accommodate structural changes, like an added or renamed column, without breaking data. Schema-on-read Storing data in its raw form and applying structure only at query time, instead of when it is first loaded. Semantic layer A layer that defines business metrics and dimensions once, centrally, so every tool and report uses the same numbers. Slowly changing dimension The techniques for recording how a dimension table's attributes change over time without breaking historical reporting. Snowflake schema A dimensional model where dimension tables are normalized into multiple related tables instead of one flat table. SQL (structured query language) The standard declarative language for defining, querying, and manipulating data in a relational database. Star schema A dimensional modeling pattern with a central fact table connected to descriptive dimension tables, shaped like a star. Surrogate key An artificial, meaningless identifier generated by the warehouse to uniquely identify a dimension or fact row. Window function A SQL calculation performed across a set of related rows without collapsing them into a single output row.