Data and Business Intelligence Glossary Terms

A test that compares two different versions of a product or interface (typically the existing one and a new variant) by presenting different options at random to different people and gathering data on their behavior.

A program’s API, or application programming interface, is its defined set of endpoints that other programs can interact with — the contract that says what you can ask for, how to ask, and what you’ll get back. The point of an API is that you don’t need to know how the other program works inside. You only need to know the contract. The term is broad. A software library’s function signatures are an API. So is the set of URLs a web service exposes. In analytics work, “API” almost always means the second kind: a web API you call over HTTP. How a web API call works You send an HTTP request to a URL and get a structured response back. The pieces: A method — GET to read, POST to create, PATCH or PUT to update, DELETE to remove. A path — the resource you want, like /v1/charges. Parameters — filters, date ranges, page size, usually in the query string. Headers — including authentication, typically an API key or a bearer token such as a JWT. A status code in the response — 200 for success, 401 for bad credentials, 429 when you’ve hit a rate limit. Most modern web APIs follow REST conventions: resources get their own URLs, HTTP methods carry the meaning of the operation, and requests don’t depend on server-side session state. The response body is nearly always JSON: { "object": "charge", "id": "ch_3Nx8", "amount": 4900, "currency": "usd", "created": 1714500000, "paid": true } You’ll also run into GraphQL, where you send one query describing exactly the fields you want, and older SOAP or XML-RPC APIs in enterprise systems. APIs and analytics Most of the tools your company runs on — payments, CRM, support, version control, ad platforms — hold their data behind an API rather than a database you can query. Getting that data into a place you can analyze means calling the API on a schedule, handling pagination and rate limits, and loading the results into a data warehouse. That’s the extract step of an ETL pipeline, and it’s how most of our integration guides get data flowing. A few practical things to watch for: APIs paginate, so a “list” endpoint rarely returns everything at once; they rate-limit, so pipelines need backoff; and they version, so an endpoint that works today can be deprecated later. Timestamps and IDs also need careful handling — see data types for why casting matters once the data lands. Metabase has its own API too, which you can use to automate administration or embed analytics in another application.

ARR (annual recurring revenue) is the annualized value of the recurring subscriptions you have active right now. In practice it’s almost always MRR × 12: normalize every subscription to a monthly amount, add them up, multiply. It answers “if nothing changed for the next twelve months, how much recurring revenue would we collect?” That framing matters — ARR is a run rate, a snapshot of the present projected forward. It is not a forecast, and it is not a number you earned. ARR vs. MRR They are the same quantity at different scales, so the only real question is which one you report. Monthly-billed and usage-heavy businesses tend to live in MRR because month-to-month movement is the signal. Annual-contract businesses tend to report ARR because it matches how deals are actually sold. Pick one as the reported metric and derive the other, rather than maintaining two calculations that will eventually disagree. ARR vs. GAAP revenue GAAP revenue is recognized as you deliver the service. If a customer signs a $120,000 annual contract on December 1, that contract adds $120,000 to ARR on day one, but only $10,000 to December’s recognized revenue. ARR is therefore always ahead of recognized revenue in a growing company, and the gap widens the faster you grow. Finance closes the books on recognized revenue; the ARR number belongs on the growth dashboard, not in the financial statements. ARR vs. bookings Bookings are the total contract value signed in a period, including one-time fees, professional services, and multi-year commitments. ARR excludes all of that. A three-year $300,000 contract is $300,000 of bookings and $100,000 of ARR. Mixing them up is the single most common way ARR gets inflated. What to exclude Keep out anything that isn’t contractually recurring: setup fees, one-off services, hardware, and usage spikes that aren’t committed. Decide explicitly how to treat trials, customers in a failed-payment state, and discounts — the honest choice is to count the discounted amount you actually bill. Once you have ARR, the interesting work is decomposing its movement into new, expansion, contraction, and churn, and dividing it by customers to get ARPU. Start with the MRR metric guide for the normalization SQL, and revenue analytics for the wider picture.

An aggregation is the act of summarizing data with a mathematical function, such as averaging the values in a column or counting the number of rows in a table. The resulting number is often called a metric, which is distinct from metrics in Metabase. Individual values in a field may not hold much meaning on their own, but when we combine these values in some specific way, we can paint a more comprehensive picture of our data. Aggregation is the process of collapsing those values into a single result, and is usually performed in conjunction with grouping — that is, combining multiple rows based on a certain value, like grouping by a dimension (e.g., a product category or country). Aggregations can be calculated on the fly, but you may also want to create summary tables with your results and save the results of those aggregate functions for future use. Summary tables can be especially useful when working with large datasets; since summary tables are precomputed, queries that rely on them can run much faster. Common aggregate functions in SQL Different databases have different sets of functions, but here are some of the most common aggregate functions that you’ll encounter: COUNT() - Counts the number of rows in a table. AVG() – Computes the average of values in a field. MIN() – Identifies the minimum value in a field. MAX() – Identifies the maximum value in a field. SUM() – Returns the sum of values in a field. STDEV() – Calculates the standard deviation of values in a field. Example aggregation Using Metabase’s Sample Database, let’s say we wanted to know the average price of our products, grouped by product category. In this case, we’ll use the Products table. Our SQL query would look like this: SELECT category, avg(price) FROM products GROUP BY category Just like we wanted, we’ve calculated the average of values in the Price column of our Products table, and grouped those averages according to their value in the Category field. If we wanted to execute that same aggregation in Metabase’s query builder, we’d Summarize by Average of Price, and then group by Category, like in the image below: Fig. 1. Performing an aggregation in the query builder: average price of products grouped by product category.

You can set up a question to send you an alert via email or Slack if a question returns data, or if the data crosses a goal line that you define. Read the docs on how to set up alerts.

An analytical database is a database optimized for a few large analysis operations rather than many small transactions. OLAP usually relies on analytical databases.

The database in which Metabase stores operation data about questions, dashboards, and other Metabase information.

An area chart is a type of line chart where the space between plotted values and the x-axis is filled in with a solid color. You can think of an area chart as a cross between a standard line chart and a bar chart. In an area chart, you still have a line that connects data points (usually plotting change over time), but by filling in the area between that line and the x-axis, you’re highlighting both the data’s relationship to zero and the relationship of any subcategories to a total. When to use an area chart Area charts are a great choice when: You’re plotting multiple subsets of data that, together, make up some total value. You need to represent volume and changes in volume over time. Your data contains too many x-axis data points for a stacked bar chart. Consider a different visualization, like a bar or line chart, if: You’re only measuring a single series over time. Your data is highly volatile, subject to frequent and/or significant swings. You’re measuring a large number of subcategories within one chart. Example area chart Similar to a stacked bar chart, stacking data on an area chart works best if you have a small number of categories to track — too many and your area chart will start to get chaotic or unreadable. Figure 1 shows Orders broken by out by Product → Category, visualized as a stacked area chart. Using this visualization, we can understand both the rise and fall of individual product categories and how orders have performed as a whole: Fig. 1. An example area chart.

An attribute is a property that describes or identifies some entity. People in the data world use “attribute” in a few different contexts, so we’ll do our best to disambiguate here. Basically, an attribute is a characteristic of something. That something might be a table, but attribute could also refer to a characteristic of a specific record, like user attributes in Metabase. Attributes in relational databases In relational databases, people often use attribute synonymously with column or field, like how a product’s Category is an attribute of (or describes) that product. This usage of attribute comes up a lot within the context of data modeling and when designing entity relationship diagrams. Example attribute Here’s a look at the People table in Metabase’s Sample Database, which includes fields like ID, Name, Address, City, State, and so on: Fig. 1. A look at the People table. Each of these fields is an attribute — the values in those fields describe something about the record they’re associated with, in this case a “person” in the People table. User attributes in Metabase Sync user attributes is only available on Pro and Enterprise plans (both self-hosted and on Metabase Cloud). Attribute can also refer to a distinct variable value that gets associated with a certain user, like a User_ID. That structure is known as a key-value pair, sometimes referred to as an attribute-value pair. In Metabase, some plans allow you to set user attributes yourself (or pass them to Metabase via SSO). You can use these user attributes to set up custom destinations on a dashboard, for example by using a user ID to parameterize a URL when that user clicks on a chart. User attributes are also an important part of data sandboxes, which give you granular control over the data that people using your Metabase instance can access. Since data sandboxes are associated with individual users, setting distinct user attributes lets Metabase know exactly how to filter a table depending on who views it.

Usage analytics is only available on Pro and Enterprise plans (both self-hosted and on Metabase Cloud). Usage analytics, formerly known as “audit log”, contains records of activity and performance in a Metabase, available to admins in some Metabase plans. Audit logs give admins an overview of how people are interacting with their Metabase — which questions are most popular, who’s looking at which dashboards, what alerts people have set up, and others.

A BI tool is an app designed for people to look at data without relying on code. These apps allow people to visualize and share data as tables, charts, and dashboards. BI tools plug into existing data sources at your organization, such as your data warehouse, CRM, or event analytics service. Common BI tools Spreadsheet applications A classic example of a BI tool is a spreadsheet app like Microsoft Excel or Google Sheets, where you can visualize data in tables, pivot tables, or charts, and share the results as individual files (or links to those files in the cloud). BI platforms A BI tool is often thought of as an app that is only used to visualize data and make reports. A BI -platform- like Metabase is type of BI tool that can handle additional tasks adjacent to reporting, such as data modeling, data cataloging, version control, and permissions management. How do BI tools fit into a data stack? Fig. 1. BI tools fit under the analytics component of a modern data stack. BI tools are one of many analytics tools that can be set up at the user-facing end of a data stack. BI platforms in particular can handle some of the same tasks as other parts of your stack. Here’s how you can expect the pieces to interact: BI tools vs. databases BI tools aren’t data sources — they don’t replace production databases or data warehouses for storing data owned by your organization. BI tools pull information from databases by running queries and displaying the results. BI tools vs. ETLs BI tools don’t replace ETLs (or ELTs) for ingesting or transforming large amounts of data on a schedule. However, like ETLs, some BI platforms can handle data modeling and data stitching (joining data from different databases) by running queries on the fly. BI tools vs. event analytics services Event or web analytics services like Google Analytics, Segment, or Amplitude collect usage data from your product. Although these services come with their own interface to visualize and share that data, they aren’t considered BI tools. You can think of them as mini data stacks that can be used standalone. Event analytics services can be integrated into your core data stack by combining them with a central BI tool. You can download event data from the service and move it into the data warehouse connected to your BI tool, or if supported, wire up the service to connect directly to your BI tool. BI tools vs. open source coding tools Open source tools like Jupyter Notebook and RShiny use programming languages like Python and R to work with data. They can be used to build reports and dashboards for analytics, but they aren’t considered BI tools because they rely on code rather than a visual interface.

A bar chart is data visualization that uses rectangles (or bars) along axes that are proportional in size to the values being measured. These bars are scaled to the values associated with discrete, categorical data. Bar charts are useful when you want to compare fixed values across categories. One axis will contain the categories being measured, while the other will show their corresponding numerical values. Fig. 1. A bar chart showing the average rating of products by category. Figure 1 shows a standard column chart where the bars are displayed vertically, but you can also visualize your data with horizontal bars, known as a row chart. Figure 2 displays that same data — average product rating by category — in a row chart. Fig. 2. The same data visualized with horizontal bars, called a row chart. You aren’t limited to a single dimension with bar charts; they can be great for multi-series charting. For example, maybe you want to compare how orders performed by quarter based on user source. In this scenario, you could use a standard multi-series bar chart or a stacked bar chart, where user sources are stacked proportional to their values within a single bar, each bar displaying data from one quarter. And if you’d rather see those as relative percentages of within their stacks, consider a 100% stacked bar chart. Bar charts are widely adopted and easy to interpret, making them a good option for quick-reference comparisons.

A bin is a single range of continuous values used to group values in a chart. Binning data helps simplify data visualizations, so people can get a sense of their data’s distribution and easily spot outliers. You most often see bins used with histograms, but they aren’t exclusive to histograms and can be useful with other visualizations like line charts or pie charts. If a measure in your dataset contains a lot of unique values, plotting each individual data point on a chart can look cluttered and may not be the best representation of your data. When you bin that data, those values get grouped into equal-sized intervals (like 1–10, 11–20, 21–30, and so on), and your resulting chart will show a count of values within each bin. Data binning example Figure 1 shows the price of products in Metabase’s Sample Database, displayed as a histogram. Fig. 1. The prices of products in our Sample Database, shown as a histogram. Metabase automatically generates bins based on how the data is distributed. The bins here are the price ranges; we can see that we have more products in the $37.50–50.00 price range than any other. Metabase auto-binned these values, but we can also select the number of bins we wanted (either 10, 50, or 100) to tweak this chart further. If your bin size is too small, you’ll have too many and likely end up with a visualization that’s hard to interpret. However, too few bins will give you an incomplete or overly-compressed picture of your data’s distribution, so play around and figure out what works best for your data.

A breakout is the grouping of aggregated results by one or more dimensions, like the count of users grouped (or broken out) by country. The column you choose to group by is sometimes known as a breakout column. In native SQL queries, the GROUP BY statement serves a similar function, separating and organizing results based on whatever dimension(s) you’ve specified. Example breakout in Metabase Figure 1 shows a metric (sum of order subtotal), broken out by two dimensions: 1) the month an order was placed and 2) the product category. Here we’ve visualized this breakout as a stacked bar chart. Fig. 1. A stacked bar chart with a metric (sum of order subtotals) broken out by two dimensions: month and product category. You can also break out your data when drilling through on a chart in Metabase. Clicking on a chart like this brings up the drill-through menu, where you can further break out your data. In figure 2, we’ve used the action menu to break out a subset of our data by the Vendor of the product ordered. Fig. 2. Using the action menu to break out our data.

A bubble chart is a way to visualize the correlation between three different variables. It’s basically a scatterplot where each plot has put on some weight. You can also use color to display an additional dimension. If the set of values used to determine the bubble size includes zero or negative values, bubble charts are probably not a good choice. There are ways around it (like using absolute values), but generally it’s hard for people to make sense of bubbles with negative size, for example. Bubble chart example Fig. 1. A bubble chart. In figure 1, the bubble chart shows the correlation between the product’s average rating (the x-axis) and the product’s price (the y-axis). Additionally the size of each point (that is, each bubble) corresponds to the quantity of the product ordered; the higher the quantity, the larger the bubble. We might expect to see some correlation to between average rating, price, and quantity ordered, but the Sample Database included with Metabase is a random dataset, so there isn’t any real pattern here. In figure 2, we’ve further broken out the plotted bubbles by category, with the color of each bubble corresponding to product categories: Gizmos, Gadgets, Doohickeys, and Widgets. Fig. 2. A bubble chart further broken out by product category.

CAC (customer acquisition cost) is what you spend to win one new customer: acquisition cost for a period divided by the new customers that period produced. It’s the denominator of nearly every growth-efficiency argument, and it’s meaningless until you state which costs and which customers went into it. Blended vs. paid CAC Blended CAC divides all sales and marketing cost — ad spend, salaries, tooling, content, events — by all new customers, including the ones who found you organically. It’s the honest company-level number, the one that tells you whether growth is affordable overall. Paid CAC divides paid media spend by the customers attributable to paid channels only. It’s the number you use to decide whether to spend more on a specific channel. Blended CAC is always lower than paid CAC when organic works, and the gap between them is a rough measure of how much your brand and content are doing for free. Problems start when the two get quoted interchangeably: a team reports flattering blended CAC to the board while making budget decisions on channels that, measured properly, don’t pay back. Label every CAC number with its scope. CAC payback CAC payback is how many months of gross profit from a new customer it takes to recover their acquisition cost — CAC divided by monthly gross-margin-adjusted revenue per customer. It’s usually more actionable than CAC alone because it’s a cash question: how long until this customer stops being a hole in the bank account? Twelve months or less is comfortable for most subscription businesses; long payback works only if you can fund it. LTV:CAC The other standard framing compares LTV to CAC. The rule of thumb is a ratio of 3:1 or better, but treat it gently. LTV depends on an assumed lifetime derived from churn, so a small change in the churn assumption swings the ratio dramatically, and an unusually high ratio often means you’re underinvesting in acquisition rather than winning. Use gross profit, not revenue, in LTV or the ratio is fiction. Common traps Use a consistent attribution window, count fully-loaded costs including salaries, and align the period so that spend and the customers it produced belong to the same window — spend in March rarely converts in March. Break CAC down by segment too: a single company-wide figure hides the fact that enterprise and self-serve customers cost wildly different amounts to win. For the SQL, the source tables, and channel-level breakdowns, see the CAC metric guide; for the top of the same funnel, cost per lead.

A common table expression, or CTE, is a named result set in a SQL query, equivalent to a subquery. CTEs are evaluated each time the main query is run.

A card is a component of a dashboard that displays data or text. Metabase dashboards are made up of cards, with each card displaying some data (visualized as a table, chart, map, or number) or text (like headings, descriptive information, or relevant links). Cards and questions Cards on a dashboard are more than just mini versions of the questions you’ve asked. If you’re adding saved questions to a dashboard and calling it a day, you may be missing out on a lot that cards can do. You can combine multiple saved questions in a single card as long as they share a dimension. Your cards don’t have to contain different questions either. It may be useful to put include same card on a dashboard multiple times, like if you want to visualize one question as both a line chart and a bar chart. Editing cards on a dashboard When editing a dashboard, you can: Arrange and resize cards on the dashboard’s grid. Change a card’s visualization options without affecting the underlying question. Connect cards to dashboard filters to filter the question’s results. Set a card’s click behavior to change what happens when someone clicks on a card. And while it’s great for those cards on your dashboards to look slick and visually appealing, it’s more important that you’re conveying the information that people need to see, without too much added fluff. Example of cards on a dashboard The dashboard in figure 1 includes text cards that act as headings and descriptions, as well as cards with numbers, trends, a line chart, and a region map: Fig. 1. Cards with questions and text on a dashboard. Cards and the Metabase API In the Metabase API, the api/card route refers to questions, rather than cards on a dashboard. You can still use the API to edit and get information about the cards on your dashboards (like with api/dashboard), but keep that distinction in mind, and check out the API documentation for a full list of routes and endpoints.

Change data capture (CDC) is a technique for detecting row-level changes in a source database — inserts, updates, and deletes — and streaming just those changes to another system, usually a data warehouse. Instead of asking “what does this table look like right now?”, CDC asks “what happened to this table since the last time I looked?” Log-based CDC Every serious transactional database already writes a durable record of every change it makes, because it needs one to recover from a crash and to feed replicas: Postgres has the write-ahead log (WAL), MySQL has the binlog, SQL Server has its transaction log. Log-based CDC reads that log directly, decoding it into a stream of change events. This is the good version. It catches every change including deletes, it captures intermediate states rather than just the final value, it doesn’t require touching the table schema, and it puts almost no query load on the source database — the reader looks like just another replica. The costs are operational: you need replication-level access, you need to keep the log from being recycled before your reader consumes it, and schema changes upstream have to be handled deliberately. Query-based CDC The simpler approach is to poll. You add an updated_at column, index it, and repeatedly ask the database for anything newer than your last high-water mark: SELECT * FROM orders WHERE updated_at > '2024-05-01 03:00:00' ORDER BY updated_at; Query-based CDC works on any database and takes an afternoon to set up. Its weaknesses are real, though. Hard deletes are invisible — the row is simply gone, and no query can tell you it left. Any code path that updates a row without touching updated_at silently drops that change. And rows that change several times between polls only ever show you the last state. Why it beats full-table snapshots The naive alternative is to re-copy the whole table on every run. That’s fine for a 50,000-row reference table and untenable for a 500-million-row events table: the snapshot is slow, expensive, hammers the source database, and forces you to run it rarely — which is exactly why “the dashboard is a day behind” is such a familiar complaint. CDC decouples freshness from table size. The work per run is proportional to the number of changes, not the number of rows, so you can run it every few minutes on a table that would take hours to snapshot. Most managed pipeline tools do a one-time historical snapshot to seed the destination and then switch to CDC for everything after, which is a sensible default for any table large enough that you’d notice a full copy.

Churn is the loss of customers, subscriptions, or recurring revenue over a period of time. It’s the mirror image of retention: if 100 customers start the month and 5 cancel, churn is 5% and retention is 95%. The word “churn” on its own is ambiguous. Before you compare your number to anyone else’s — or to last quarter’s — you need to say which churn you mean. Customer churn vs. logo churn vs. revenue churn Customer churn counts people or accounts that stopped paying, divided by the number who could have. A team with 100 customers that loses 5 has 5% customer churn regardless of what those customers paid. Logo churn is the same idea applied to accounts in a B2B context — one “logo” is one company, no matter how many seats or contracts it holds. It’s customer churn under a different name. Revenue churn counts money instead of accounts. Losing five $50/month customers and losing one $250/month customer are identical on a revenue basis and very different on a customer basis. Which one is right depends on the decision. Customer churn tells you about product fit and onboarding; revenue churn tells you what happened to the business. Small-business-heavy companies usually see customer churn far above revenue churn, because the accounts that leave are the cheap ones. If the two diverge in the other direction, you’re losing your biggest accounts, which is a much louder alarm. Gross vs. net Gross churn only counts what you lost. Net churn subtracts expansion — upgrades, seat growth, usage overage — from the losses in the same period. Net churn can be negative, meaning the existing base grew even though some customers left. The retention-side versions of these are gross revenue retention and net revenue retention, and most companies report both: GRR shows how leaky the bucket is, NRR shows whether the bucket fills itself. Getting the number out of your data Churn is deceptively hard to compute because the denominator, the period, and the definition of “canceled” all have to be pinned down first — a downgrade isn’t a cancellation, a failed payment isn’t always a churn, and annual contracts only get the chance to churn once a year. Break it down by segment (plan, size, acquisition channel, signup month) rather than watching a single company-wide average, since aggregate churn hides the cohorts that are actually failing. For the calculation itself — SQL patterns, the data you need, and the pitfalls in each one — see the churn rate metric guide.

Cohort analysis groups records by a shared starting event and then follows each group separately over time. A cohort is that group: everyone who signed up in March 2026, everyone who made a first purchase during a promotion, everyone hired in Q1. The defining feature is that membership is fixed at the start and never changes — a March cohort is always the same people, however many of them are still around. That makes a cohort different from a segment, which is a slice defined by an attribute (enterprise plan, EU region) and whose membership can change as records change. You often combine the two: retention curves for the March cohort, split by plan. Why cohorting beats an aggregate average A single blended number mixes customers who have been with you for three years with customers who signed up yesterday, so it moves for two reasons at once: the underlying behavior changed, or the mix changed. Fast growth is the worst offender. If you double your new-signup volume, a company-wide retention rate will drop even if nothing about the product got worse, simply because young accounts are a larger share of the base. It also works in reverse — slowing acquisition makes retention look like it improved. Cohorting removes the mix effect. Each cohort is measured against its own starting size at its own age, so month 3 for the January cohort is comparable to month 3 for the June cohort. That’s what lets you answer the question you actually care about: is the product getting better for new customers over time? Reading the triangle The standard display is a triangle (or heatmap): one row per cohort, one column per period since the cohort started, each cell showing the share still active. It’s triangular because recent cohorts haven’t lived long enough to fill the right-hand columns. Read it two ways: Across a row — the retention curve for one cohort. Steep-then-flat means you have a durable core and an onboarding problem. A steady slide toward zero means no core at all. Down a column — the same age across cohorts. If month-3 retention climbs as you move down, newer cohorts are doing better, and whatever you changed is working. Watch cohort size too. A tiny cohort’s percentages are noisy, and one big customer leaving can look like a collapse. To build the triangle from your own data, start with the cohort retention dashboard.

In Metabase, a collection is a set of items — questions, models, dashboards, and subcollections — that are stored together for some organizational purpose. You can think of collections like folders within a file system. The root collection in Metabase is called Our Analytics, and it holds every other collection that you and others at your organization create. You may keep a collection titled “Operations” that holds all of the questions, dashboards, and models that your organization’s ops team uses, so people in that department know where to find the items they need to do their jobs. And if there are specific items within a collection that your team uses most frequently, you can pin those to the top of the collection page for easy reference. Pinned questions in a collection will also render a preview of their visualization. Fig. 1. A look at a collection in Metabase. Administrators can set collection permissions to determine which collections (and by extension, their contents) are viewable by which groups in Metabase. In addition to the top-level Our Analytics collection and whatever collections your organization creates, each Metabase user has their own Personal collection. These are semi-private, in that admin users can view the contents of everyone’s personal collections. Personal collections are a good place to store questions and dashboards that you haven’t quite perfected yet, and you can move them to a public collection whenever you’re ready.

A column is a list of values, usually belonging to a particular field, displayed vertically in a table. In a relational database table, values within a column each correspond to a different record. Values in a column share a data type. That is, if a column’s data type is Integer, that means every value within that column must be an integer. There may be other constraints too, related to formatting, character length, or whether or not that value is mandatory. Example column Here’s an image of the Orders table in Metabase’s Sample Database, with the Created At column highlighted. The Created At column’s data type is DateTime, and the values in this column each correspond to the timestamp of a single order. Fig. 1. A look at the Orders table, with the Created At column highlighted. Columns vs fields While columns and fields aren’t technically the same thing, it’s usually okay to use these terms interchangeably. See Columns vs. Fields. However, keep in mind that columns don’t always directly correspond to fields in a database. For example, you may want to create a custom column in Metabase that contains calculated values, like one that displays the percentage discount for each order. You’d create this custom column by telling Metabase to calculate the Discount divided by the Subtotal and display the resulting value in a new column. Columnar storage While many traditional relational databases store data as rows and are typically best suited for holding transactional data, some databases (like data warehouses optimized for analytics) utilize columnar storage. Columnar databases (also known as column-oriented databases) physically store a column’s values together, rather than indexing based on entire rows. This can drastically speed up analytical queries and aggregate functions, since those queries will be able to retrieve similar data from the same location on a disk, rather than executing large reads across the database to pull a column’s values from individual records.

Columnar storage is a way of laying data out on disk column by column instead of row by row. It’s the single biggest reason an analytical database can scan a billion-row table in seconds while your application database would grind. Row storage vs. column storage Take a table of orders with 40 columns. A row-oriented database — Postgres, MySQL, most transactional databases — stores each row’s 40 values together in a page: [id:1, customer:88, product:"Widget", amount:19.99, ...40 fields] [id:2, customer:12, product:"Gizmo", amount:44.50, ...40 fields] That’s ideal for OLTP work: “fetch order 4471” or “insert this order” touches one contiguous chunk of disk. A column-oriented database stores each column’s values together instead: id: [1, 2, 3, 4, ...] customer: [88, 12, 5, 91, ...] amount: [19.99, 44.50, 7.25, 130.00, ...] Fetching one whole order now means reading from 40 separate places — which is why you don’t run an application on a columnar database. Why analytical queries get faster Analytical queries are the mirror image of transactional ones: they touch very few columns across very many rows. SELECT date_trunc('month', created_at), SUM(amount) FROM orders GROUP BY 1 needs two columns out of 40. A row store has to read every row in full to get at those two values, so it moves roughly twenty times more data off disk than the query actually uses. A column store reads only the two column files. On a wide table that alone is often an order-of-magnitude difference in I/O. Columnar layouts also enable two further tricks. Vectorized execution processes a block of values from one column in a tight loop, which is far friendlier to modern CPUs than hopping between fields of a row. And because most engines keep per-block statistics like min and max, a filter such as WHERE created_at >= '2024-01-01' lets them skip entire blocks without decompressing them. Compression Values in a single column are all the same data type and usually similar to each other, which compresses far better than the mixed bag you get in a row. A country column with 200 distinct values across 500 million rows collapses under dictionary encoding; a sorted created_at column collapses under delta encoding; a mostly-null column collapses to almost nothing under run-length encoding. Ten-to-one compression ratios are ordinary. That means less storage cost, and — because the bottleneck for a big scan is usually disk and network rather than CPU — less data to move, which makes queries faster again. This is what people mean when they say a data warehouse is “optimized for analytics.” Snowflake, BigQuery, Redshift, ClickHouse, and DuckDB are all columnar, as are the file formats like Parquet that back most data lakes.

A combo chart is a chart that combines bars and lines.

Compaction is a maintenance process that takes data stored in scattered pieces and rewrites it into fewer, larger, contiguous units. Nothing about the data changes — the same rows go in and come out — but afterwards a query has far less work to do to find them. Why data ends up scattered Storage systems append. A streaming pipeline writing every minute produces 1,440 files a day. An update to a row in a log-structured store writes a new version rather than editing the old one in place. A table that’s been written to incrementally for a year is a pile of small fragments, each with its own header, metadata, and minimum read cost. That fragmentation is what hurts. Reading a thousand 1 MB files is dramatically slower than reading one 1 GB file, because you pay per-file overhead a thousand times over — and on object storage, a thousand separate network round trips. Compaction merges them, and while it’s at it drops rows that have been superseded or deleted. Where you’ll meet it Data lakehouses. Table formats like Iceberg and Delta Lake accumulate small files and delete markers with every write. Compaction rewrites them into right-sized files and prunes obsolete snapshots. This is the version most analytics teams end up owning, because it’s usually scheduled rather than automatic. Log-structured databases. Cassandra, RocksDB, HBase and friends merge their on-disk segments continuously; it’s a core part of how they work rather than an optional chore. Relational databases. Postgres VACUUM FULL and its equivalents reclaim space left behind by updates and deletes, which is the same idea under a different name. Time-series and observability stores. Metrics arrive in tiny increments and get compacted into larger blocks as they age, often alongside downsampling. What it costs and what it buys Compaction is not free. It reads and rewrites data, so it consumes CPU, I/O, and — on cloud object storage — request and egress charges. Run it too often and you spend more on maintenance than you save on queries. Run it too rarely and query times creep up while storage bills inflate with obsolete versions nobody reads. The signals that it’s overdue are recognizable: dashboards that got gradually slower without the data getting meaningfully bigger, query planners reporting enormous file counts for modest row counts, and storage growing faster than the volume of new records would explain. Most teams settle on a scheduled job during a quiet window, tuned so that recently written partitions get compacted soon after they stop receiving writes, and older partitions are left alone. If you’re tracking the effect, watch query runtimes and storage size together — compaction should push both down at once.

Concat (or concatenate) is a function that combines multiple strings into a single string. The word comes from the Latin root con- for “together”, and caten- for “chain”. For example, you might want to concatenate a city (Vienna) with a country (Austria) to get a single location string like “Vienna, Austria”. The Metabase concat expression is translated into a SQL CONCAT function when running against your database, like this: SELECT CONCAT(City, ", ", Country) AS "Location" In Metabase and other BI tools, you can assume that concat only works with the text data type (often called a string). If you want to use concat with other data types, like numbers or dates, you’ll have to convert them to text first. If you prefer to write SQL, note that the functions JOIN, UNION, and MERGE might sound like they work similarly to CONCAT, but they are used to combine rows and columns from different tables, rather than text strings from different columns.

Cross-filtering is clicking on a chart or table in a dashboard to filter everything else in the dashboard.

It’s common to track values that add up over time, like sales, miles traveled, etc. In contrast to a regular line chart, a cumulative value chart shows the total for each point in time, such as the day during the quarter. When more is added, that line will be steeper, when little (or nothing) is added, it will be closer to horizontal. Cumulative value charts also lend themselves to period-over-period comparison (like the same quarter year-over-year), as well as showing a goal that the data line should cross.

Sometimes called a “calculated” column. You can use a custom expression to add columns to the results of your query. For example, adding a column that calculates the difference between the total and subtotal.

A custom destination is a setting on a dashboard card that determines what happens when people click on the card. You can set up a chart to send people to another question, dashboard, or external URL. You can also use values from the chart to parameterize the URL, for instance, by inserting an ID into the URL string.

A custom expression in Metabase is a formula that uses functions and mathematical operators to allow for more complicated questions, similar to a formula in spreadsheet software. You can include custom expressions in questions asked via Metabase’s query builder. Custom expressions give those GUI questions more power and flexibility, without requiring you to write a full SQL query. You can write custom expressions to create custom columns, or to create more advanced filters and aggregations. Example custom expression in Metabase The example below shows the creation of a custom column that categorizes Orders according to their Subtotal using the case function. Fig. 1. Writing a custom expression in the notebook editor. Figure 2 shows the resulting table, with our new custom column on the right: Fig. 2. The resulting Order size column created using a custom expression.

A directed acyclic graph (DAG) is a graph in which it’s impossible to get back to any starting node by following the links between nodes. Break the name apart and it explains itself: Graph: a set of nodes connected by edges. Directed: each edge points one way. A → B does not imply B → A. Acyclic: no cycles. Follow the arrows as far as you like and you’ll never return to where you started. That last property is the useful one. It guarantees you can lay the nodes out in an order where every node comes after everything it depends on — a topological sort — which is exactly what you need to run a sequence of dependent steps. DAGs in data pipelines Almost every data pipeline is a DAG, whether or not anyone draws it. Each node is a task — extract from an API, load into staging, transform into a fact table, refresh a rollup — and each edge means “this must finish before that starts.” A typical shape: extract_stripe ─┐ ├─► load_raw ─► clean_customers ─┐ extract_crm ────┘ ├─► customer_revenue ─► refresh_dashboard load_raw ─► clean_orders ────┘ Modeling it as a DAG buys you three things: Parallelism. Two nodes with no path between them can run at the same time. The orchestrator works this out from the graph rather than you scheduling by hand. Correct retries. If clean_orders fails, the scheduler knows customer_revenue and everything downstream must not run, while unrelated branches can proceed. Impact analysis. Walk the edges forward from a broken source to see every table and report affected; walk backwards from a wrong number to find where it came from. That backward walk is data lineage. Orchestration tools such as Airflow, Dagster, and Prefect ask you to define pipelines as DAGs directly — in Airflow the object you write is literally called a DAG. Transformation tools like dbt infer one from the references between models. Why the acyclic part is non-negotiable A cycle in a pipeline is a deadlock: A waits for B, B waits for A, nothing runs. Most orchestrators reject cycles at parse time rather than at run time, which is why “circular dependency” is a common error message when a new model accidentally references something downstream of itself. Where else DAGs show up The pattern is everywhere once you notice it. Git commit history is a DAG. Build systems and package managers resolve dependencies with one. A spreadsheet’s formula dependencies form a DAG, and the circular-reference warning you get in Excel is the acyclic rule being enforced. Connecting the output to Metabase The point of the pipeline is what sits at the end of it: tables in your data warehouse that people can actually query. If you’re assembling one, building a data pipeline walks through the ETL options for getting source systems into a warehouse that Metabase can connect to.

A dashboard is a data visualization tool that holds important charts and text, collected and arranged on a single screen. Dashboards provide a high-level, centralized look at KPIs and other business metrics, and can cover everything from overall business health to the success of a specific project or campaign. The term comes from the automotive dashboard, which — like its business intelligence counterpart — provides status updates and warnings about important functions (just for things like low brake fluid instead of how your recent marketing campaign performed). Dashboard vs. report Dashboards aren’t exactly the same as reports, though you’ll sometimes hear people refer to dashboards as reports. The difference is that dashboards tend to be easier to read and understand at a glance, while traditional reports provide a more detailed look at a subject. Unlike traditional reports, dashboards are viewable on a single screen and often incorporate some interactive elements. You’re probably not going to print out a dashboard to read, which would make a lot more sense for a traditional report that draws on static, historical data. However, just like with traditional reports, you can send out updated dashboards according to a set schedule, like with dashboard subscriptions in Metabase. Dashboards in Metabase In Metabase, dashboards are made up of cards that contain either questions or text. You have a lot of options when creating and editing dashboards in Metabase, like: Arranging and resizing cards to fit your desired dashboard design. Making your dashboards interactive by setting custom click behavior and linking one dashboard to another. Adding filter widgets and wiring them up to specific fields on individual cards. Format text with Markdown to annotate your dashboard with text or GIFs. Sharing your dashboard with a link or by embedding it in your website or app. Example dashboard Figure 1 shows an example of a dashboard in Metabase with three question cards and three filter widgets. If someone inputs a customer ID, customer name, or date into one of the filter widgets, the charts will adjust accordingly to fit reflect that added filter. Fig. 1. A dashboard with three question cards, and three filter widgets.

Dashboard subscriptions allow you to send the results of all the questions on a dashboard via email or Slack on a schedule.

A data dictionary is a document that describes the tables, fields, and other elements in a database and explains their meaning and origin. Data dictionaries are repositories for a database’s metadata, storing the administrative information that people need to understand and make use of that data. Think of them like a typical dictionary, but instead of every word in a language, data dictionaries contains definitions and about the objects that make up your database. An up-to-date and comprehensive data dictionary helps make sure that everyone stays on the same page about what certain fields or tables mean in practice. Data dictionaries can also help make sure that different departments are all using those terms consistently. Data dictionaries are usually a separate file or set of files stored alongside the database they describe. While some aspects of your database’s data dictionary may be accessible to all database users (like important descriptions that everyone needs to know), other parts may only be viewable by database administrators (like technical details about the physical implementation of your database). Data dictionary in Metabase In Metabase, the data reference section acts as a data dictionary. What goes in a data dictionary? Data dictionaries collect and store metadata associated with a database, usually information like: Table and field descriptions Data types Integrity constraints Naming conventions Locations of files While the exact formatting of your data dictionary will depend on your organization and the complexity of your dataset, it’s common for data dictionaries to be formatted as a table or series of tables, with fields for metadata like field name, description, data type, character length, and whether null values are permitted. You can make a data catalog with a simple spreadsheet, within your relational database software, or even as a text document. Data dictionary vs. schema vs. data catalog There’s some overlap here with a database’s schema, but generally speaking a schema defines the structure of the database and how tables and their fields fit together, while a data dictionary provides contextual information about that data. Maybe you’ve heard about data catalogs too, another similar concept. Some organizations utilize data catalogs to better facilitate discovery and analysis of their data; they’re like data dictionaries with some added features and functionality, taking things a step further than the traditional document-based data dictionary.

A data lake is a place to store both structured and unstructured information, typically as files or blobs. You can think of a data lake as a dumping ground for all of your data, regardless of structure, format, or intended use. The idea of a “lake” is largely marketing jargon, but the aquatic comparison comes from the idea that information in a data lake flows in a more “natural” state than that of the more rigid and hierarchical data warehouse. And because they can hold raw data that doesn’t need to adhere to a specific schema, data lakes tend to be cost-effective when scaling to store substantial amounts of information (into the petabytes). Since there’s no need to define a schema at the start, data lakes can be straightforward to set up; you can load data in for a specific use or just to keep it on hand for the future, even if you aren’t yet sure what kinds of queries you’ll need to run on it. However, once you do get things set up, configuring the tools you’ll need to actually make your data lake useful can get complex and expensive — typically requiring the expertise of data engineers. Those engineers will set up ETLs as needed, or even train machine learning models on parts of your data lake. Data lakes rely on a schema-on-read system, meaning data only gets verified against a schema once it’s pulled from that data lake for querying, rather than when it’s first written. This does mean, however, that pulling from and making use of a data lake takes more work. And just because a data lake allows for greater flexibility doesn’t mean you should thrown all data governance out the window; the information that goes into your lake should still be of good quality, cleaned and annotated so that your ETLs or query engine (and by extension, the people who need the data) can make good use of it. When to use a data lake If you need to analyze huge volumes of semi-structured and/or unstructured information (like if you’re an IoT company) then a data lake may be a good fit. Since there’s no need to enforce an overarching schema when data is written, data lakes can also be an effective solution if you’re dealing with many different types of data sources at once — like streaming data, structured application databases, data from IoT devices, social media, or web traffic. Ultimately, organizations with complex data needs may not rely exclusively on a data lake or a data warehouse (or even a data lakehouse), and instead construct data architectures that can incorporate both, taking into account the organization’s overall strategy, the needs of the people who’ll use it, and the types of queries those people will need to execute. Setting up a data lake Let’s say you want to set up a data lake. In broad strokes, the process will look something like this: Choose a cloud storage provider. There are data lake services out there that can help you set up the various layers and tools you’ll need, but at its core your “lake” is your storage layer — wherever you’re keeping that structured and unstructured data together (like in AWS S3 or Microsoft Azure). Identify your data sources. These may be structured (like an application database), semi-structured (like XML or JSON files), or unstructured (like social media posts, images, or text documents). Clean up and ingest data from those sources. At this stage, you’ll annotate those data sources (especially the semi-structured and unstructured ones), adding metadata and tagging and classifying them based on the types of questions you’re likely to ask of that data. Once that data has been cleaned up, those annotated copies get loaded into your data lake, probably in a columnar format like parquet that’s better for analytical queries. Create ETLs as needed and query your data lake. Because of their mix of formats and often-unstructured nature, engineers and data scientists are usually the ones directly accessing a data lake. People like your data analysts will query the data lake through the use of query engines like Presto or SparkSQL, which run ETLs over the data lake, structuring it on a regular schedule so the data can be queried via SQL. Those queries get executed on the cleaned-up, annotated, columnar copies of your data, rather than on the raw data sources themselves (both the raw data and the cleaned-up date are stored in your data lake).

A data lakehouse is a storage architecture that puts warehouse-style tables on top of the cheap object storage used by a data lake. You still keep your files in something like S3 or Google Cloud Storage, but a table format layer sits over those files and gives you the things you’d normally only get from a data warehouse: a defined schema, transactions, and the ability to update or delete individual rows. Why the category exists Before lakehouses, most organizations ran both. Raw and semi-structured data landed in a lake, then ETL jobs copied a cleaned subset into a warehouse for analysts to query. That works, but you end up paying for two storage systems, maintaining the pipelines between them, and answering “why does the warehouse number differ from the lake number?” every quarter. The lakehouse collapses that. Instead of moving data into a second system to make it queryable, you leave it where it landed and describe it well enough that a SQL engine can treat it like a table. Table formats and ACID on object storage The piece that makes this work is the open table format — Apache Iceberg, Delta Lake, or Apache Hudi. Object storage on its own has no notion of a row, a transaction, or a schema change; it just has files. A table format adds a metadata layer that tracks which files belong to which version of a table, so you get: ACID transactions. A writer commits a new set of files atomically, so readers never see a half-finished write. Time travel. Old snapshots are retained, so you can query a table as of last Tuesday — which is how you reproduce a report or diagnose a bad pipeline run. Schema evolution. You can add, rename, or retype a column without rewriting every file. Row-level updates and deletes. Useful for corrections and for honoring deletion requests, which plain Parquet files make painful. Because a lakehouse writes many small files as data streams in, compaction becomes a routine maintenance job rather than an optimization you get to ignore. When a lakehouse makes sense A lakehouse is a good fit if you already have a large lake and want analysts to query it without maintaining a separate warehouse copy, or if you have a mix of structured tables and semi-structured data that you want governed the same way. It’s less compelling if your data is modest in volume and already tidy — a managed warehouse will be simpler to run and usually faster out of the box. Plenty of organizations still land somewhere in the middle, keeping a lakehouse for large or raw datasets and a warehouse for the curated models that leadership reports run on. Either way, what your BI tool connects to is a query engine sitting over those tables, so from an analyst’s point of view a well-run lakehouse looks like any other analytical database.

A data mart is a subset of a data warehouse curated for one team or subject area, along with the tables, models, and dashboards that team actually uses. Marketing gets its campaigns, sessions, and attribution tables; finance gets its invoices, payments, and revenue recognition tables. Same warehouse underneath, different vertical slice on top. Data marts aren’t something you buy. They’re something your organization defines: a scope, a set of transformations that produce the tables in that scope, and the “human interface” — the BI tool collection of dashboards and saved questions — that the team works in. Data mart vs. data warehouse vs. data lake The three terms sit at different levels of structure and scope: A data lake holds raw data of any shape, structured or not, with no schema enforced when it’s written. Broad scope, minimal structure. A data warehouse holds structured, cleaned data from many sources, modeled for analysis. Broad scope, high structure. A data mart holds a curated slice of that structured data for one audience. Narrow scope, high structure, and usually more aggregation. The distinctions are fuzzier in practice than the diagrams suggest — plenty of organizations run all three, and a “mart” is frequently just a schema in the same warehouse rather than a separate system. Why build one Findability. A warehouse with 800 tables is not a place a marketer wants to go looking. Twenty well-named tables scoped to their work is. Shared definitions. When one team owns a slice, they can settle on what “qualified lead” or “recognized revenue” means for their reporting, ideally through a semantic layer rather than a folder of near-identical SQL. Performance and cost. Mart tables are usually pre-joined and pre-aggregated, often in a star schema, so common questions scan far less data than they would against raw fact tables. Access control. A narrower slice is easier to grant access to. HR can have the people tables without also having the payments tables. The tradeoff Every mart is another set of pipeline steps to maintain, and marts drift. If the finance mart and the sales mart each build their own version of “customer” from the raw tables, you’ve reproduced the silo problem one level up. The usual guard is to build marts on top of shared, conformed core tables rather than straight off raw sources — so the marts differ in scope and aggregation, not in what the underlying entities mean.

The term data model is used to describe any pattern that organizes and labels information. People will use “data model” as a generic way to refer to concepts like schemas, derived tables (views), or ERDs. A good data model helps people find things faster. For example, a mall directory is a data model that organizes information about the stores in the mall. It groups and labels the stores by category or location, and explains how the stores are related to one another by displaying them on a map. This model makes it easier for people to find out where to go, compared to wandering through the mall on their own, or reading through a random list of store names. Data modelling example To make decisions during data modelling, it’s best to start by figuring out what people want to look for, and why. Let’s say we want to create a data model for storing information about movies, to help people look for new things to watch. You can think of this data model as a template that can be filled out for any movie. The template should do two things: Represent the parts of a movie that are useful for finding a specific one. For example, people might search for a movie they want to watch by title, director, genre, or actor. Describe the relationships between the parts, so that it is easy to look up one group of information based on another. For example, the template should make sure that any movie title is associated with at least one director. The simplest type of data model groups related parts together into one template, and includes some information about how to fill it out. For example, the template below can be used as a data model for any movie. Movies Title: Any text (mandatory). Director: List of names (mandatory). Genre: Any text (optional). Cast: List of names (optional). The model can be expanded by adding more parts related to movies such as Release year or Run time. We can also expand on existing parts if they are useful for looking things up. For example, people might want to search for a movie by specific information about the actors in them, such as any acting awards they’ve won. Since Cast only keeps track of actor names, we can split out award information into a new data model. Acting Awards Award: Name of acting award (mandatory). Award year: Year (mandatory). Actor: First name and last name (optional). Since actor names appear in both models (either under Cast or under Actor), there is a relationship to connect the Movies model and the Acting Awards model. When both templates are filled out with real movie and awards information, people will be able to look up a movie by a particular award. The written templates above are a basic way to think about breaking down information for data models, but there are many best practices that you can follow depending on the use case. You can find examples of common data model formats in the next section. Common data models Schemas Schemas are a conceptual data model. They are used by people who work with databases. Information is represented by named columns and data types. Relationships are described by structures such as tables or JSON objects. ERDs ERDs are a visual data model. ERDs are used by people who need to talk about information management and architecture. Information is represented by different shapes, such as rectangles or diamonds. Relationships are described by different lines, such as arrows or dashed lines. Metabase models A Metabase model is a data model that you can create and save from a question or SQL query. Information is represented by named columns and any associated metadata. Relationships are described by the logic used in the question or SQL query. How people actually use the term data model You might find that different teams use the term “data model” informally to mean different things: People who write SQL may use it to refer to derived tables or views. Programmers may use it to refer to a schema or ERD. The Data Model in Metabase If you’re a Metabase admin, you’ll have access to the Data Model page in Metabase. The changes you make here will affect the way that data appears across all of Metabase. What’s the difference between the Data Model page and a Metabase model? The Data Model sits on top of the raw data warehouse tables connected to Metabase. It is a layer of modelling you can use to clean up the tables that your organization can see. You can think of it as a way to “translate” information between the data world and the business world by assigning human-readable names and saving common definitions of segments or metrics. Metabase models sit on top of the Data Model. They can be created by anyone with permissions to use the underlying database tables.

A data pipeline is the set of automated steps that move data from where it’s produced to where it’s analyzed, transforming it along the way. If ETL names one common shape of that work (extract, transform, load), “pipeline” is the umbrella term for the whole plumbing: the connectors that pull from your APIs and application databases, the transformations that clean and reshape the results, the destination data warehouse or data lake, and the scheduler that keeps it all running. Batch vs. streaming Most pipelines are batch pipelines: they run on a schedule (every hour, every night) and process everything that changed since the last run. Batch is cheaper, easier to reason about, and easier to re-run when something breaks. The tradeoff is latency — a dashboard built on a nightly batch shows you yesterday. Streaming pipelines process events continuously as they arrive, usually through a message log like Kafka or Kinesis. You get near-real-time freshness, which matters for things like fraud checks or live operational monitoring, but you pay for it in infrastructure and in the harder engineering problems that come with unbounded data: late-arriving events, out-of-order records, and windowing. Plenty of teams run both. A common middle ground is micro-batching — running a batch pipeline every few minutes — which gets you most of the freshness of streaming without rebuilding your stack around it. Change data capture sits in the same middle ground, streaming row-level changes out of a source database instead of re-reading whole tables. Orchestration Once you have more than a handful of steps, you need something to decide what runs when and what depends on what. That’s orchestration, and it’s usually modeled as a DAG — a directed acyclic graph where each node is a task and each edge is a dependency. Tools like Airflow, Dagster, and Prefect exist to run those graphs, retry failed tasks, and tell you when a step is late. Idempotency and backfills Pipelines fail. Networks time out, APIs rate-limit you, a schema changes upstream without warning. The property that makes failure survivable is idempotency: running the same step twice produces the same result as running it once. In practice that usually means writing with a merge or upsert keyed on a primary key, or replacing a whole date partition rather than appending to it. Idempotency is also what makes backfills possible. When you add a column, fix a bug in a transformation, or connect a source that has two years of history, you re-run the pipeline over past date ranges to rebuild the affected data. If your steps aren’t idempotent, a backfill produces duplicates instead of corrections — which is why the discipline pays for itself the first time something breaks.

Metabase’s data reference section, found at /reference/databases or by clicking on the book icon throughout Metabase, lets you add and look up metadata about your databases and their tables and fields.

A data sandbox is a set of boundaries that define access to a table down to its columns and rows. You can coordinate data sandboxes with your SSO setup.

A data type is a field’s classification as implemented directly by a database. Data types tell the database what kind of values to expect in a column — integers, strings, timestamps — and it enforces that rule on every row written. Types are declared when you define a table, as part of its schema: CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, subtotal NUMERIC(10, 2) NOT NULL, status VARCHAR(20), is_gift BOOLEAN DEFAULT false, created_at TIMESTAMPTZ NOT NULL ); Common SQL data types Exact names vary between database engines, but the families are the same everywhere: Integers — SMALLINT, INTEGER, BIGINT. Whole numbers, used for counts and IDs. Exact decimals — NUMERIC or DECIMAL, with a declared precision and scale. Use these for money. Floating point — REAL, DOUBLE PRECISION. Fast and approximate; 0.1 + 0.2 won’t be exactly 0.3, which is why they’re a bad choice for currency. Text — CHAR (fixed length), VARCHAR (bounded), TEXT (unbounded). Boolean — true / false, and in SQL also NULL. Dates and times — DATE, TIME, TIMESTAMP, and TIMESTAMPTZ, which carries a time zone. Store timestamps in UTC and convert at query time. Semi-structured — JSON or JSONB for nested payloads, plus arrays in engines that support them. See JSON for the trade-offs. Identifiers and other specials — UUID, INET, geometry types, and enums, depending on the engine. Why type matters for analytics Type decides what you’re allowed to do with a value: Aggregation. You can SUM a number. You can’t SUM text, so a revenue column stored as VARCHAR blocks the exact question you bought a warehouse to answer. Sorting. Numbers stored as text sort alphabetically, which puts 100 before 2. Time grouping. A date stored as a string can’t be truncated to a month or filtered to “last 30 days” without casting it first. Joins. A foreign key whose type doesn’t match the primary key it points at can fail to match or quietly kill performance. Precision. Floating-point money drifts by fractions of a cent, and those fractions turn into reconciliation work later. Data usually arrives untyped. Spreadsheet exports and API responses hand you strings for everything, so casting to the right type belongs in the transform step of your ETL pipeline, not in every downstream query. Data types vs. field types A data type is what the database enforces. A field type is a layer of metadata on top of it describing what a column means — that a VARCHAR holds an email address, or that a NUMERIC is currency. Two columns can share a data type and behave very differently in a report because their field types differ.

A data warehouse is a database used for analytics. This could be a standard relational database, or one that’s specifically designed for analysis, relying on columnar storage. The analysis part happens in whatever BI tool your organization uses, but that BI tool has to pull its data from somewhere — that “somewhere” is your data warehouse. Data warehouses typically store information from a number of sources (like the different operational databases that your organization uses), and those raw data sources make their way into your data warehouse via ETLs in a data pipelines, usually constructed by a data engineer. Unlike a data lake, the information you store in a data warehouse is structured, so it’s ready for analysis when you query it with your BI tool. Whether that structuring (or transformation) process happens inside (ELT) or outside (ETL) the data warehouse depends on how your pipelines are configured and what transformation tools you’re using.

A dimension is a descriptive, qualitative attribute of your data, like a name, category, or URL field. Often you will filter or group a measure measure or metric by a dimension, like grouping revenue (the metric) by state (the dimension). Unlike measures, dimensions can’t be calculated or computed; they’re discrete and categorical. That is, you can’t determine the average name or standard deviation of a field containing user birthdays. Example dimension A product’s category is a dimension of that product, and the average price of that product (the measure) could be grouped or filtered by that dimension.

To explore a pre-selected subset of data, e.g., by clicking on a bar in a histogram to see the raw values for that bar.

An ERD, or entity relationship diagram, is a graphical representation of how tables in a database connect to each other. ERDs show a database’s structure (or schema) at a high-level. ERDs are a useful tool when designing a new data model or identifying issues within an existing schema. Entity relationship diagrams are basically just boxes (your entities, or tables) connected with lines (the relationships between them). Your database software may have some built-in functionality to create ERDs, but you can also use whatever design software you like best or go the analog route and draw out your ERD on a piece of paper. The how is less important; what really matters is making sure that your diagram is accurate and logical, so you can design the most effective database for your specific use case. Example ERD Here’s an example of an ERD showing Metabase’s Sample Database: Fig. 1. A simple ERD of Metabase's Sample Database. The four tables, Orders, Products, People, and Reviews, are our entities, and the connecting lines show the three one-to-many relationships between them. ERD design and notation When sketching out an entity relationship diagram, each box should contain information like that table’s name, field, and key information (primary and foreign keys). You’ll notice in the example above that each table’s key information is indicated with (PK) and (FK) next to field names. The type of line between each entity illustrates the kind of relationship each table has to another. Different organizations and industries use different conventions for ERD notation, but one of the most common is crow’s foot notation, named because the three-pronged symbol (the one that gets used for “to many”) looks a bit like the foot of a bird. Figure 2 shows the common symbols used in crow’s foot notation and their corresponding relationship types: Fig. 2. The lines used in crow's foot notation to represent different types of table relationships.

Extract, transform, load: an ETL is a common operation in data processing systems that reads data from sources, massages it, and stores it in another system. For example, an ETL job might read web server logs and a customer database to fill up a table of user sessions. Almost every analytics setup runs on one. Your data starts scattered across a production database, a payments processor, a CRM, and a support tool; a pipeline is what brings it together somewhere you can query it. The three steps Extract. Pull raw data from each source: a database read, a nightly CSV drop, or — most often now — a paginated call to a vendor’s API returning JSON. Transform. Clean and reshape it. Cast data types, standardize currencies and timestamps, deduplicate, rename columns to something consistent, and join related records into the shape your analysts expect. Load. Write the result into the destination, usually a data warehouse or data lake. Pipelines are typically modeled as a DAG, where each step declares what it depends on, so the orchestrator knows what can run in parallel and what has to wait. ETL vs. ELT The classic order is extract, transform, load — transform the data on the way in, so only clean, modeled tables land in the warehouse. That made sense when warehouse storage and compute were expensive: you didn’t want to pay to store raw data you’d never query. Cloud warehouses inverted the economics, and ELT became the default. You extract and load raw data first, then transform it in the warehouse itself with SQL: CREATE TABLE analytics.user_sessions AS SELECT user_id, min(event_time) AS session_start, count(*) AS event_count FROM raw.web_events GROUP BY user_id, session_id The trade-offs: ETL keeps the warehouse tidy and can strip sensitive fields before they’re ever stored — useful when compliance rules limit what you may retain. ELT keeps the raw data around, so when a transform turns out to be wrong you can rebuild from source instead of re-extracting everything. It also lets analysts, not just data engineers, own the transform layer. Most teams end up doing both: light cleanup in flight, heavy modeling in the warehouse. Reverse ETL A reverse ETL runs the pipeline backwards, pushing modeled data from the warehouse back into operational tools — a health score into your CRM, a churn risk flag into support software — so the people doing the work see it where they already are. Getting started If you’re standing up your first pipeline, our guide to building a data pipeline walks through the moving parts, and the individual integration guides cover how to pull data out of specific tools.

Embedded analytics refers to using third-party software in your application to allow your users to query their data. Embedded analytics can refer to the simple insertion of static charts in your app (usually via an iframe), but embedded analytics usually refers to a more interactive experience where people can view and create charts, tables, and dashboards, and in general, explore their data on their own. Embedded analytics is more than simply using a charting library in your app; the analytics software should be able to work with SSO, manage groups and permissions, and even data modeling, all of which make it easier to incorporate analytics into your product. Software that provides embedded analytics usually allows you to white-label the charts and dashboards so that they look like they belong in your app. Organizations typically embed analytics with a third-party tool (like Metabase), rather than build the analytics themselves, because 1) it’s often cheaper (in both the short and long term), and 2) you provide a better user experience than you would were you to build your own solution. Analytics gets very complicated very quickly, and most people would rather devote resources to their core business than try to roll their own analytics in house. Embedded analytics example Check out a demo of three different sites, each with embedded analytics that uses Metabase under the hood. (You can toggle between sites on the nav in the upper right.) metaba.se/sdk-demo.

Embedding is the process of placing some functionality of one application inside another. In analytics, this usually means integrating data visualizations into a parent application, allowing people to view charts within the context of their application. Embedding can save time and resources for the parent app too, allowing teams to draw on existing analytics tools rather than building everything from scratch themselves. While not the only way to embed something, embedding in Metabase involves using an iframe (an inline frame) to place a question, dashboard, or (in some plans) the full Metabase app within another application. Embedding Metabase charts and dashboards Embedding is more than just placing a static image of a chart into your site or app. Instead, that iframe creates a nested browser within your main browser or app that points to its own, separate URL. This way, the embedded Metabase chart or dashboard stays up to date. When you view an embedded chart, you’re still seeing the Metabase chart itself, just nested in the parent application. Depending on security configurations, your individual embedded charts and dashboards are either public or secure embeds. You can also configure or lock parameters to affect what people see on those charts, like in figure 1: Fig. 1. Making parameters editable and enabling dark mode before publishing a dashboard for embedding. Embedding the full Metabase application Interactive embedding is only available on Pro and Enterprise plans (both self-hosted and on Metabase Cloud). With some plans, you’re able to embed the full Metabase experience within your application. Interactive embedding is particularly useful for multi-tenant analytics, like providing your customers specific reports that they can view and interact with all while remaining in your app.

An entity key is the value that uniquely identifies a row in a table. In Metabase specifically, “Entity Key” is the name of the field type you assign to that column, so that Metabase knows the column identifies a record rather than describing one. Entity key vs. primary key The two terms point at the same column most of the time, but they live in different layers: A primary key is a database constraint. The database enforces it — writes that would create a duplicate or a null are rejected. An entity key is a metadata label in Metabase’s semantic layer. Nothing is enforced; the label tells Metabase how to treat the column. That distinction matters most with analytical databases and warehouse tables, which frequently don’t declare primary key constraints at all. There’s no PRIMARY KEY for Metabase to read, but the table still has a column that identifies each record — so you mark it as the Entity Key yourself. What the label changes Tagging a column as the Entity Key affects how Metabase behaves around that field: Metabase can show the detail view for a single record when you drill through on a value. Columns tagged Foreign Key elsewhere can be connected to it, which is what makes joins suggest themselves in the query builder and makes related-record links work. Metabase treats the column as an identifier rather than a number to be summed or averaged — you rarely want the average of an ID. You’ll also see a companion field type, Entity Name: the human-readable label for the same record, like customers.name next to customers.id. Entity Key is what the machine matches on; Entity Name is what people read. Setting it Metabase picks up entity keys automatically during sync wherever a database declares a primary key. Where it doesn’t, an admin sets the field type on the Data Model page in the Admin panel, and the change applies everywhere in Metabase. You can also set field types on the columns of a model, which is useful when the model’s output has a natural identifier that no underlying table declares. Picking the right column The column you tag should be genuinely unique at the grain of the table. A common mistake is tagging a column that looks like an ID but repeats — order_items.order_id, say, which appears once per line item and identifies the order, not the row. Tagging that as the Entity Key means drill-through lands on the wrong record. If nothing in the table is unique on its own, the table’s real identifier is a combination of columns, and the honest fix is upstream: give the table a surrogate key in your ETL layer before it reaches Metabase.

A field is an attribute of a record in a database table. You can think of a field like the “heading” of a column, or a label on a container of values. Fields in Metabase also contain metadata about the values the field contains (see Field properties). Example field Subtotal is a field in the Orders table in the Sample Database included with Metabase. Fig. 1. Fields in the Orders table of the Sample Database included with Metabase. The fields in a table define the values each record can have in the table. Field properties In Metabase, fields have four properties: Field name Field description Data type (like Integer or Text) Field type (like Income or Foreign key) In figure 1, the Subtotal field has a field type of Income and a data type of Float, as well as a description: “The raw, pre-tax cost of the order. Note that this might be different in the future from the product price due to promotions, credits, etc.” Columns vs fields People usually use the terms “column” and “field” interchangeably. They’re technically not the same thing, but it usually doesn’t matter. See Columns vs. Fields.

A field filter is a special variable that you can use in SQL queries that lets you create a “smart” filter widgets. You can connect your SQL-built charts to dashboard filters. If you write queries in SQL, you should definitely learn how these work, as you can do things like create pre-populated dropdowns in your filter widgets.

A field formula is an equation that powers a custom expression, like the formula you’d enter in the cell of a spreadsheet. For example, = [Subtotal] - [Discount].

A field type, also known as a semantic type, is the meaning of a field (or column) in a table. The field type determines how Metabase displays the field. For example, a column’s data type might be “text,” but its field type could be “URL”. Metabase would then display the column values as links.” When you create or edit a model, you can select the field type for a column. Admins can change field types globally on the Data Model page in the Admin Panel.

A filter is a predicate expression that limits the results of a query based on some stated criteria. For example, you may want to limit the records in your Orders table so that you only see orders where the value of the Total field is over 100. We can use a predicate expression, Total > 100, to filter the orders. For each record, the query evaluates whether that expression resolves true or false, and narrows the results accordingly. So in this case if the record has a total greater the 100, that record is included in the results. In SQL, queries are filtered using the WHERE clause, like WHERE Total > 100. You can also filter aggregations in SQL using the HAVING clause, like HAVING AVG(rating) > 3.5. Filters in Metabase Filter the results of your questions. Add filters to your dashboards. Set up cross-filtering so dashboard filters update when someone clicks on a card. Link filters on a dashboard to limit results based on the value of another filter. Configure smart field filters in native SQL queries that know which filtering options to present based on field type and column data. Create filter widgets that act as a search function on a dashboard, like for a lookup tool. Example filter in Metabase Figure 1 shows the Products table in Metabase’s Sample Database, with a filter added that narrows the results to only include products where the Title field contains the word “Hat”: Fig. 1. A question in Metabase with one filter added.

A filter widget is a UI element on a dashboard or SQL query where you can input a value or select one from a menu to filter results, like selecting CA to limit results to the state of California.

A foreign key is a column in one table that holds the primary key of a row in another table. It’s the wire that connects two tables: orders.customer_id holds a value that exists over in customers.id, so every order knows which customer it belongs to. A concrete example CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT REFERENCES customers (id), total NUMERIC ); The REFERENCES clause is what makes customer_id a real foreign key rather than an integer column with a suggestive name. Once it’s declared, the database enforces referential integrity: you can’t insert an order pointing at customer 9999 if no such customer exists, and depending on how the constraint is configured, you can’t delete a customer who still has orders — or the delete cascades to those orders. Cardinality: the one-to-many pattern Foreign keys are how you express relationships in a schema: One-to-many: one customer has many orders. The foreign key lives on the “many” side — orders carries customer_id, not the other way round. Many-to-many: students enroll in many courses, and courses hold many students. This needs a third junction table carrying two foreign keys, one to each side. One-to-one: less common, usually used to split rarely-read or sensitive columns into a separate table. On an entity relationship diagram, foreign keys are the lines between boxes, and the notation at the ends of each line tells you which cardinality applies. Foreign keys and joins A foreign key is the natural join condition between two tables. The relationship is already declared, so the query practically writes itself: SELECT customers.name, SUM(orders.total) AS lifetime_value FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name Note that a foreign key column is not unique — that’s the whole point. Many orders can carry the same customer_id, which is why joining from the customer side to the order side multiplies rows, and why you usually follow the join with an aggregation. Missing constraints in the warehouse Foreign key constraints are standard in transactional databases, where they protect application data. They’re often absent in analytical databases, where data arrives through pipelines and constraint checking would only slow loads down. The relationship still exists conceptually — the column is a foreign key in every sense that matters to an analyst — it just isn’t enforced, so nothing stops an orphaned customer_id from showing up in your reports. Foreign keys in Metabase Metabase reads declared foreign keys when it syncs a database and tags those columns with the Foreign Key field type, which is what lets Metabase link related tables and offer drill-through from a value to the record it points at. When a warehouse table doesn’t declare its constraints, an admin can assign the Foreign Key field type and its target column by hand on the Data Model page.

A full outer join is a join that keeps every row from both tables, matched or not. Rows that pair up appear once, combined. Rows with no counterpart still appear, with NULL filling the columns from the other side. Think of the four join types as answers to “which side gets to keep its unmatched rows?” Inner says neither, left and right say one, full outer says both. A worked example: reconciliation Full outer joins shine when you’re comparing two systems that are supposed to agree. Your payment processor exports a list of charges; your accounting ledger has its own list. You want to see everything, especially the rows only one system knows about: SELECT charges.id AS charge_id, ledger.id AS ledger_id, charges.amount, ledger.amount FROM charges FULL OUTER JOIN ledger ON charges.id = ledger.external_id Before: 500 charges from the processor, 498 ledger entries. Three ledger entries reference a charge ID that doesn’t exist. After: 503 rows. 497 of them have values on both sides. Three rows have a charge_id and a NULL ledger_id — payments that were never booked. Three rows have a ledger_id and a NULL charge_id — ledger entries pointing at nothing. Neither discrepancy would have been visible from a single left join, and neither would have existed at all in an inner join’s output. Add WHERE charges.id IS NULL OR ledger.id IS NULL and you have an exception report you can run on a schedule. Other good uses Aligning two time series that don’t cover the same periods — say, signups by week from one source and spend by week from another. A full outer join on the week keeps every week either source knows about, so no gap gets silently dropped. Schema migrations and ETL validation: comparing an old table against its rebuilt replacement, row by key, to catch anything gained or lost. Caveats The output has no single complete key column. charges.id is NULL for right-only rows and ledger.id is NULL for left-only rows, so anything you group by afterwards should use COALESCE(charges.id, ledger.external_id) rather than either column alone. Full outer joins are also the easiest way to accidentally produce an enormous result set, since nothing is filtered out — a many-to-many join condition multiplies both sides. Finally, not every database implements FULL OUTER JOIN. MySQL, for example, doesn’t; the usual workaround is a left join UNIONed with a right join. Full outer joins in Metabase Full outer join is one of the four join types available in the query builder’s join-type picker. Because engine support varies, the picker only offers the join types your data source actually supports.

A GUI (Graphical User Interface) question is a question built using Metabase’s graphical query builder (as opposed to a question created with the native query editor).

GeoJSON is an open standard format for encoding and exchanging geospatial data, built on JavaScript Object Notation (JSON). It is widely used in Geographic Information Systems (GIS) and web mapping due to its simplicity, human-readability, and compatibility with web technologies. GeoJSON represents geographic features such as points, lines, polygons, and their multi-part counterparts (e.g., MultiPolygon). Each feature consists of a geometry object (spatial data) and properties (non-spatial metadata), making it ideal for storing both location and descriptive information. GeoJSON uses the WGS 84 coordinate system, with coordinates expressed in decimal degrees.

A goal line is a constant line added as a marker to a chart showing how high (or low) values must be to be considered noteworthy.

A grid map is a map with values overlaid graphically in a regular grid, e.g., as squared 10km by 10km colored to show values.

A histogram is a chart that displays continuous data using vertical bars that each represent a discrete, equal-sized range. These ranges are called bins. While bar charts are used to visualize categorical information (like product categories), histograms display information along a spectrum, separated into binned ranges. If your data is numerical — say, price or quantity — consider opting for a histogram. Example histogram You could use a histogram to visualize a count of orders broken down by subtotal, like so: Fig. 1. A histogram showing the distribution of order subtotals. Histograms will only show a thin sliver of white space between each of the bins to indicate that the bars represent bins across a range of continuous values.

Horizontal scaling means adding more machines to a system to increase its capacity — three servers instead of one, then thirty. It’s usually called scaling out, in contrast to vertical scaling, which makes a single machine bigger. The appeal is that there’s no ceiling. A single server eventually runs out of CPU sockets and memory slots; a fleet doesn’t. Horizontal scaling also gives you redundancy for free: if one of thirty servers dies, you’ve lost 3% of your capacity rather than your entire service. What it requires Scaling out only works if the work can be split up, which puts real constraints on how the system is built: A load balancer in front, to distribute requests across the fleet. Without one, extra servers just sit idle. Stateless application servers. If a server keeps a user’s session in local memory, that user has to keep landing on the same machine. Push session state into a shared store and any server can handle any request. A shared data layer. Every instance has to see the same data, which is why the database is usually the last thing to scale out and the hardest. Coordination for scheduled work. Ten instances each running the same nightly job at midnight is ten times the work and, often, ten copies of the same email. Scaling out a database Application servers are the easy part. Databases resist horizontal scaling because they hold state, and the strategies for splitting that state each come with a cost: Read replicas spread read traffic across copies of the data. Straightforward, and the usual first move for analytics — but writes still all go to one primary. Sharding partitions rows across independent databases by some key, such as customer ID. Genuinely unlimited scale, at the price of cross-shard queries becoming hard and rebalancing becoming a project. Distributed query engines, the model most cloud data warehouses use, spread a single query’s work across many nodes. This is why an analytical warehouse can scan a billion rows in seconds where a single server would grind. The trade-offs Scaling out costs more in complexity than it saves in hardware, at least at first. You now have deployments to coordinate, logs spread across machines, and failure modes — partial outages, inconsistent versions mid-deploy, a replica lagging behind — that a single server simply doesn’t have. Capacity also comes in chunks: you add a whole instance, not the 15% more headroom you actually needed. The usual practical advice holds. Scale up until it gets expensive or you need redundancy, then scale out — and make sure you’re watching resource utilization closely enough to know which one you’re short of.

An inner join is a join that only keeps rows where the keys from the two input tables matched. It returns the intersection: rows that exist on both sides and nothing else. A worked example Say you have an orders table with four rows, and a products table. One of those orders references a product that was deleted from the catalog, so its product_id has no match: SELECT orders.id AS order_id, products.title, orders.total FROM orders INNER JOIN products ON orders.product_id = products.id Before: orders has 4 rows, one of which points at a missing product. After: the result has 3 rows. The orphaned order is gone — not blanked out, gone. There is no row for it in the output at all, and its total won’t appear in any sum you calculate downstream. That’s the defining behavior. In every other join type, unmatched rows survive with NULLs. In an inner join they’re dropped. An inner join is also a filter This is the part that trips people up. Because unmatched rows disappear, an inner join quietly filters your data, and it does so without appearing anywhere in your WHERE clause. If you join orders to customers and 2% of orders carry a stale customer_id, your revenue total silently drops by 2%. A quick sanity check: compare the row count before and after the join. If the count fell and you didn’t expect it to, you have referential integrity problems in the source data — and an inner join is hiding them rather than showing them. Switching to a left outer join and looking for NULLs on the right-hand side is how you find the culprits. Syntax notes INNER JOIN and plain JOIN are the same thing in SQL — the INNER keyword is optional, though writing it out makes your intent obvious to the next reader. Because the join is symmetric, A INNER JOIN B and B INNER JOIN A return the same rows (only the column order differs), which is not true of the outer joins. When to reach for it Use an inner join when a missing match means the row isn’t relevant to the question. Counting revenue per product category, measuring average order value for orders you can attribute, listing users who have an active subscription — in all of these, a row without a match is noise. Reach for an outer join instead the moment the absence of a match is itself interesting: customers who never ordered, products that never sold. Inner joins in Metabase If you write a native query with a bare JOIN, you get an inner join. The query builder defaults to a left outer join instead, so you pick the inner join explicitly from the join-type picker when you want it.

JSON (JavaScript Object Notation) is a way to represent data by combining basic values in arrays and key/value structures using the syntactic conventions of JavaScript. It’s plain text, it’s readable by humans, and despite the name it has nothing to do with JavaScript anymore — every language can parse it. JSON won because it’s simple. There are only six kinds of value: String — "hello", always in double quotes Number — 42 or 3.14, with no distinction between integers and floats Boolean — true or false Null — null Array — an ordered list, [1, 2, 3] Object — an unordered set of key/value pairs, {"a": 1} Objects and arrays can nest inside each other, which is how JSON represents anything complicated: { "id": 1042, "email": "ada@example.com", "active": true, "plan": { "name": "pro", "seats": 12 }, "tags": ["beta", "enterprise"], "cancelled_at": null } Note what’s missing. There are no dates — timestamps travel as strings, usually ISO 8601 like "2026-07-21T14:03:00Z". There are no comments. And numbers have no declared precision, which is why money is often sent as an integer count of cents rather than a decimal. JSON and APIs Nearly every web API you’ll pull data from returns JSON. Converting an in-memory object into a JSON string to send over the wire is called serialization; parsing it back on the other end is deserialization. JSON also shows up as a transport format for structured tokens: a JWT is essentially two small JSON objects, base64-encoded and signed. JSON in a database Most databases can store JSON in a column — PostgreSQL has json and jsonb, MySQL and SQL Server have JSON, BigQuery and Snowflake have their own semi-structured types. That’s convenient when API responses have fields you don’t want to model in advance. It’s also a trap if you leave everything in there. JSON columns are slower to filter and aggregate than typed columns, and a BI tool can’t offer good filters on a field it doesn’t know the data type of. The usual practice is to keep the raw JSON payload for safety, then flatten the fields you actually analyze into proper typed columns during the transform step of your ETL pipeline: SELECT payload ->> 'email' AS email, (payload -> 'plan' ->> 'seats')::int AS seats FROM raw.users Do that once, at load time, and every downstream question gets faster and easier to write.

A JSON Web Token (JWT, usually pronounced “jot”) is a standard for sharing signed authentication claims between web services. One service states some facts about a user — who they are, what they’re allowed to do, when the statement expires — signs them, and hands the result to another service, which can verify the signature without calling back. What’s inside a JWT A JWT is three base64url-encoded parts joined by dots: header.payload.signature. Header — a small JSON object naming the signing algorithm, e.g. {"alg": "HS256", "typ": "JWT"}. Payload — the claims, also JSON. Signature — the header and payload signed with a secret or private key. The payload holds claims, which are just key/value pairs. Some are standardized: { "sub": "user_1042", "iss": "https://auth.example.com", "aud": "analytics.example.com", "exp": 1753104000, "iat": 1753100400, "email": "ada@example.com", "groups": ["finance"] } sub is the subject (who the token is about), iss the issuer, aud the intended audience, iat when it was issued, and exp when it stops being valid. Everything else is custom. Signed, not secret The most common misconception: a JWT is signed, not encrypted. Anyone holding the token can base64-decode the payload and read every claim. The signature only proves the payload wasn’t altered and came from someone holding the key. So never put anything in a JWT you wouldn’t be comfortable showing the bearer, and always send tokens over HTTPS. Verification also has to be done properly. The receiving service must check the signature against the expected algorithm, reject tokens whose alg doesn’t match what it expects, and confirm exp, iss, and aud before trusting a single claim. Expiry and revocation Because verification is offline, a JWT stays valid until it expires — there’s no lookup that can cancel it mid-flight. That’s the trade-off for not needing a database round trip on every request. In practice teams keep the lifetime short (minutes to an hour) and issue a fresh token when needed, rather than trying to revoke tokens already in the wild. If you need instant revocation, a server-side session token is the better fit. Where you’ll run into JWTs JWTs are the workhorse of single sign-on: your identity provider authenticates the user, signs a token describing them, and the application trusts it. They’re also how signed embedding works — the parent application signs a token describing which content to show and which filters to apply, so the embedded view can’t be tampered with by whoever is looking at the page.

A join is the combination of results from two tables in a relational database. While the word “join” makes it sound like you’re merging the tables themselves, a join actually takes the rows from two (or more) different tables and returns a new set of rows that combines columns from those tables, using entity keys and foreign keys to determine which rows are related. Types of joins There are four types of SQL joins: Left outer join: select all records from Table A, along with records from Table B that meet the join condition, if any. Right outer join: select all records from Table B, along with records from Table A that meet the join condition, if any. Inner join: only select the records from Table A and B where the join condition is met. Full outer join: select all records from both tables, whether or not the join condition is met. Example joins in Metabase Metabase defaults to left outer joins for questions asked in the query builder, but inner joins are the default for native SQL queries (that is, if you just use JOIN in your query rather than specifying which type of join). Let’s say we wanted to return results from both the People and Orders tables in Metabase’s Sample Database, like a table of includes an order ID, the name of the person who placed that order, and their user ID. Query builder join Figure 1 shows what this join would look like in Metabase’s notebook editor. We’d also want to pick which columns are visible, so we aren’t shown every column from both tables. {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/join/join-notebook-editor.png” description=”Fig. 1. A join in the query builder.” %} Native SQL query join If we were to write this same query in SQL, it may look something like this: SELECT orders.id AS "Order ID", people.name AS "Name", people.id AS "User ID" FROM people JOIN orders ON people.id = orders.user_ID Here we’ve identified where the join happens (in this case, joining at People → ID and Orders → User_ID, an entity key and foreign key).

A KPI, or key performance indicator, is a metric that shows progress toward a personal or company goal — and that a team has explicitly agreed to be measured by. Monthly recurring revenue, support ticket resolution time, and deployment frequency are all KPIs for the teams that own them. KPI vs. metric Every KPI is a metric, but very few metrics are KPIs. A metric is any number you can compute from your data: page views, average order value, rows inserted per hour. It becomes a key performance indicator only when someone decides that moving it up or down is the point of their work this quarter. That distinction matters in practice. You can have hundreds of metrics defined in your data model and only four or five KPIs on the wall. Adding a metric costs you a definition; adding a KPI costs you attention, and attention is the scarce thing. What makes a metric “key” A metric earns KPI status when it clears a few bars: It maps to a goal. If nobody can say what number would count as success, it’s a diagnostic, not a KPI. Someone owns it. A KPI with no owner is a number that gets stared at, not moved. It’s actionable. The team can plausibly change it with the work they actually do. It’s defined once. “Active user” has to mean the same thing in every report, or the KPI turns into an argument about definitions. It moves on a useful cadence. A number that only changes annually can’t steer a weekly standup. Picking KPIs Start from the goal, not from the data you happen to have. Write down the outcome you want, then ask which single number would most credibly tell you whether you’re getting it. Pair that number with a counter-metric so nobody can win by cheating: cycle time alongside change failure rate, or new signups alongside churn rate. Different functions land on different KPIs. Finance and revenue teams watch MRR and retention; support watches CSAT and first response time; engineering watches cycle time and deployment frequency. Our metrics library has definitions, formulas, and common pitfalls for each of them. Tracking KPIs Put your KPIs where people already look. Define each one once against your data, break it out by the dimensions you actually act on (plan, region, team), and give it a home on a dashboard with the trend visible, not just the current value. A KPI without history is a number; a KPI with history is a signal. Resist the urge to add more. When a KPI list grows past a handful, teams stop reading it, and the numbers quietly go back to being metrics.

LDAP (Lightweight Directory Access Protocol) is a protocol for querying and updating directory services — the systems that hold an organization’s user accounts, groups, and org structure. Active Directory, OpenLDAP, and most corporate identity servers speak it. The directory itself is a hierarchical tree rather than a set of relational tables. Entries are identified by a distinguished name that reads from the specific to the general, like cn=jsmith,ou=Analytics,dc=example,dc=com, and each entry carries attributes: email, display name, department, group memberships. Authentication and authorization LDAP does two distinct jobs, and it’s worth keeping them separate in your head. Authentication happens through a bind operation: an application passes the user’s credentials to the directory, and the directory answers yes or no. The application never stores the password, which is the point. When someone is deactivated in the directory, they lose access everywhere at once rather than in whatever order IT gets around to it. Authorization comes from group membership. The same directory that says who a person is also says which groups they belong to, so applications can map an LDAP group onto their own permission model — everyone in ou=Finance lands in the finance group, gets the finance collections, and inherits whatever data access that implies. That group mapping is what makes LDAP worth wiring up for analytics specifically. Access to sensitive tables tracks the org chart automatically, and a transfer or a departure updates permissions without anyone editing a list by hand. LDAP, SSO, and SAML LDAP predates the browser-based SSO protocols and works differently. With LDAP the application collects the password and checks it against the directory. With SAML or OIDC, the application redirects to an identity provider, the user authenticates there, and the app receives a signed assertion — it never sees the password at all. In practice these coexist. Many organizations run LDAP as the underlying directory and a SAML identity provider in front of it for browser logins, with the directory still supplying group membership. If you’re choosing today for a new setup, SAML or OIDC is generally the better fit for anything web-facing; LDAP remains common where an established Active Directory is already the source of truth. Operational notes A few things that reliably cause trouble: Plain LDAP is unencrypted. Use LDAPS or StartTLS; otherwise credentials cross the network in the clear. The service account matters. Applications bind with a dedicated account to look users up. Give it read access to the subtree it needs and nothing more. Group sync direction. Decide whether the directory or the application owns group membership. Both owning it produces changes that quietly revert on the next sync. Watch the failed login rate. A spike usually means an expired service account or a misconfigured search base, not an attack — but it can be either, which is why the audit log is worth keeping.

A leading indicator is a metric that moves before the outcome it predicts. A lagging indicator moves after, confirming what already happened. Revenue is lagging. Qualified pipeline is leading. Both are useful, and they’re useful for different things: you steer by the leading one and you’re graded on the lagging one. The distinction is about timing and controllability, not importance. Lagging indicators are usually the numbers that matter most to the business — revenue, churn, retention, profit — but by the time they move, the decisions that caused them are months old. Leading indicators are noisier and further from money, but you can still do something about them. They come in pairs The useful unit isn’t a single leading indicator, it’s a pair: a lagging outcome you’re accountable for and one or two leading measures you believe drive it. Some examples across functions: Sales — closed revenue is lagging; pipeline coverage, meetings booked, and sales cycle length lead it. Coverage below target today is next quarter’s miss. Support — CSAT is lagging; first response time and ticket volume lead it. Response time slipping this week shows up in satisfaction scores next month. Software delivery — incidents and outages are lagging; lead time for changes, deployment frequency, and change failure rate lead them. Customer success — renewal is lagging; product usage, support sentiment, and a composite health score lead it, often by a full contract cycle. Choosing one that’s actually predictive A leading indicator is a hypothesis: this moves, therefore that will move. Test it. Pull both as a time series, shift the leading one forward by the lag you expect, and see whether the relationship holds in your data. Plenty of dashboards carry “leading” metrics that correlate with nothing. Two things to watch. First, leading indicators are easier to game precisely because they’re closer to the work — activity metrics like calls made or tickets touched go up on command without any outcome improving. Second, don’t drop the lagging metric once you have a leading one; the lagging number is how you find out your hypothesis stopped being true. A practical setup: a small set of leading indicators reviewed weekly by the team that can move them, and the lagging outcomes reviewed monthly by whoever owns the result.

A left outer join is a join that keeps every row from the first (left-hand) table, whether or not it matched anything in the second (right-hand) table. Where there was no match, the right-hand columns come back as NULL. A worked example You have 100 signed-up customers, 30 of whom have never placed an order. You want a list of every customer with their order count: SELECT customers.name, COUNT(orders.id) AS order_count FROM customers LEFT OUTER JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name Before: 100 customers, 30 with no matching order rows. After: 100 rows in the result. The 30 order-less customers appear with order_count = 0. Run the same query as an inner join and you’d get 70 rows — the customers you most want to know about would have vanished. One detail worth internalizing: COUNT(orders.id) returns 0 for those rows, but COUNT(*) returns 1, because the unmatched customer still occupies one output row. Count a column from the right-hand table, not the whole row. Finding the rows that didn’t match Because unmatched rows survive with NULLs, a left outer join is also how you find absences. Filter for a NULL on the right-hand side and you get exactly the rows with no counterpart: SELECT customers.name FROM customers LEFT OUTER JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL That pattern — sometimes called an anti-join — answers questions like “which accounts have no activity this quarter?” or “which products were never sold?” It’s the everyday tool behind churn and engagement analysis. Put the condition in the WHERE clause deliberately, though. Any other filter on a right-hand column (WHERE orders.total > 50) will discard the NULL rows and turn your left join back into an inner join by accident. If you want to restrict which rows are eligible to match, put that condition in the ON clause instead. Left vs. right A LEFT JOIN B and B RIGHT JOIN A return the same rows. Left joins are far more common simply because reading order matches evaluation order: you start with the table you care about and hang optional information off it. See right outer join for when the mirror image reads better. Left outer joins in Metabase The query builder uses a left outer join by default when you add a join step, which matches the usual intent: keep everything from the table you started with, and enrich it. You can switch the join type from the picker in the notebook editor. In a native query you have to spell it out, since a bare JOIN in SQL means an inner join.

A line chart is a type of visualization that connects discrete values connected by lines to show changes and trends. Line charts are useful for tracking quantitative data over some sequence, often (though not always) time, known as a time series. You’ll typically plot the measure or metric along the y-axis, with your sequence along the x-axis, like the count of orders (y-axis) over time (x-axis). Line charts, like bar charts, are a go-to chart for visualizing changes in data and for forecasting future data based on trends. Example line chart in Metabase Figure 1 shows a line chart that plots the number of reviews from the Sample Database’s Reviews table over time, broken out by month: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/line-chart/line-chart-example.png” description=”Fig. 1. A line chart showing the number of reviews over time.” %} This example also includes a trend line showing the overall direction of the points on the chart. You have a lot of options when it comes to customizing a line chart, whether you want to display your data as an area chart, adjust the scale of axes in your line chart, or even track multiple series within one graph.

The linear axis is an axis in a chart that spaces marks according to their values.

A linked filter is a filter widget that only displays a subset of values depending on the value of another filter. For example, a City filter can be linked to a Country filter so that the former only shows cities in the latter.

A load balancer is hardware or software that sits in front of a group of servers and spreads incoming traffic across them. Clients connect to one address; the load balancer decides which server behind it actually handles each request. It is the piece that makes horizontal scaling usable. Adding a second application server does nothing on its own — something has to send half the traffic there. That something is the load balancer. How it decides where to send traffic Common strategies, roughly in order of how often you’ll see them: Round robin. Each request goes to the next server in the list. Simple, and fine when requests are all about the same size. Least connections. Send the request to whichever server is currently handling the fewest. Better when request durations vary a lot — which is exactly the case for analytics, where one query returns in 20ms and the next runs for two minutes. Session affinity (“sticky sessions”). Keep a given user pinned to the same server for the length of their session. Needed when servers hold state locally, and a good sign you should move that state out. Weighted. Give bigger servers a larger share, useful during a rolling upgrade or when your fleet isn’t uniform. Health checks and failover The other half of a load balancer’s job is knowing which servers are actually working. It periodically pings a health endpoint on each one; if a server stops responding, it’s pulled out of rotation until it recovers. That’s what turns a fleet of individually unreliable machines into a service that stays up, and it’s why a rolling deploy doesn’t drop requests — instances are drained one at a time rather than all restarting at once. This is also where a lot of confusing outages come from. A health check that only verifies “the process is running” will happily keep sending traffic to a server whose database connection pool is exhausted. In an analytics stack You’ll run into load balancers in two places. In front of your BI application, when you run more than one instance of it for capacity or availability. Because analytics requests are long and uneven, least-connections beats round robin here, and timeouts need to be generous enough to survive a slow query — a 60-second idle timeout will cut off exactly the reports people complain about. And in front of the database, where a database proxy or a set of read replicas behind a single endpoint spreads read traffic while writes still go to the primary. Either way, the load balancer is one of the better places to collect metrics: it sees every request, so its logs give you traffic volume, latency percentiles, and error rates per backend without instrumenting anything.

A measure is a numerical attribute of your data, and can be broken out by one or more dimensions. These are the fields in your database containing numerical values that can be calculated or aggregated. Measures are quantitative; you can perform calculations on them like sums, averages, or standard deviations. Example measure If we have a table in our database that stores product information, the field containing each product’s price is a measure. Measures vs. metrics On its own, a measure won’t give us a very well-rounded view of our data — that’s where metrics come in. Metrics are a kind of measure; they’re the calculations that you perform on your raw data (your unaggregated measures). While a product’s price is a measure, the standard deviation of all our products’ prices is a metric. The KPIs that your organization tracks are metrics too, like each month’s average order totals.

Metadata is information that describes data to make it easier to find, manipulate, and make use of that data. Metadata examples Think about a file on your computer, like a digital image or text document. Among many other attributes, that file has a name, file type, extension, size, and timestamps noting when it was created, last opened, and last modified. This is all metadata — none of those attributes are really the file itself, but they do tell you important things about the file. Understanding and keeping track of this metadata tells both you and your computer how that file should be sorted and handled, like indicating to your computer what software to use when opening that file. Metadata exists beyond the analytics world, and is found pretty much everywhere. It’s important in a wide variety of industries, from photography to libraries to broadcast television, since any organization that handles or generates data needs to be able to find and organize it. Metadata is sometimes human-readable (like the title of a book or field names in a database), but can also to be machine-readable, like an XML or JSON file. Metadata in relational databases and data warehouses In a relational database, metadata includes all the information that make up that database’s schema, like the following: Table names Field names Entity keys Foreign keys Data types Views Integrity constraints However, there’s more to database metadata than just its schema. User information, business definitions, table and field descriptions, database size, and storage information are all important pieces of metadata too. Depending on how your database is configured, you may store some metadata within the database itself (like table and field names), or in a separate file or set of files that contain all of a database’s metadata. This is known as a data dictionary. In a data warehouse, metadata acts like an index or table of contents, defining all of the objects stored within that data warehouse, as well as information about the various ETL jobs that manipulate data so it can be useful for those who need it. Metadata about an ETL would likely include the name of the job, its purpose, when and how often it runs, which data the job uses, and where that data ends up. And if that job is properly annotated with plenty of useful metadata, it’s then easier for you or a coworker to understand what exactly the job does and why. Using metadata in Metabase Metadata plays a big part in Metabase! For example, designating a column’s field type (itself a form of metadata) gives Metabase an idea of what that field actually means, so Metabase can know how to format that field or what kind of visualization to show you. Models make use of metadata too. Annotating columns with a description when creating a model can go a long way in helping people better understand your data. Figure 1 shows how those descriptions show up when hovering over a column in that model: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/metadata/hover-description.png” description=”Fig. 1. Viewing the Products table’s metadata in the data reference section.” %} Finally, you can always view table metadata in the data reference section of Metabase’s data browser. Figure 2 shows the how that looks for the Sample Database’s Products table. As you can see, this view provides useful information like column names, descriptions, field types, and data types: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/metadata/metadata-data-reference.png” description=”Fig. 2. Viewing the Products table’s metadata in the data reference section.” %}

A metric is a calculation performed on a measure. Metrics are quantitative attributes of data, with some summarization applied. Metric vs. measure You’ll see the terms metric and measure used interchangeably, and they’re pretty similar concepts, both referring to some numerical value that’s part of (or drawn from) your data. However, there’s an important distinction: measures are raw, unaggregated data, while metrics are aggregated (or summarized) data. For example, while a field like Discount is a measure, the standard deviation of that Discount field would be a metric. Some people will also use “metric” to mean a computation of measures that’s specifically related to performance goals, like CRR (customer churn rate) or NRR (net revenue retention). By this definition, a metric is basically a KPI (key performance indicator), depending on whether or not someone has designated that metric as “key.” Example metric If we wanted to determine the average of order subtotals in Metabase’s Sample Database, we’d do so by summarizing, like in figure 1: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/metric/example-metric-summarization.png” description=”Fig. 1. Summarizing the Orders table by subtotal average, a metric.” %} In this case, Subtotal is a measure, but average subtotal is our metric. Metrics in Metabase In Metabase, a capital-M Metric is a saved, reusable definition of an aggregation — the official way your team calculates something like revenue, active accounts, or win rate. Metrics are saved into collections like questions and dashboards are, so anyone can create one and browse the ones that already exist, and admins can mark the vetted ones as official. A metric isn’t limited to a single table: you can build one on top of a table, a model, or another metric, and join in extra data sources when the calculation needs them. If there are certain aggregations that you and your team need to reference and use on a regular basis (like revenue), you may want to create a metric in Metabase so you can access it when asking questions, without rebuilding that aggregation yourself every time.

A model is a fundamental building block in Metabase that you can use as a starting point for new questions.

Multitenancy is a software architecture where a single instance or deployment of an application serves multiple, distinct customers — the tenants — and each tenant only ever sees its own data. The alternative, single-tenancy, gives every customer their own isolated deployment. Multitenancy is cheaper to run and much easier to upgrade: one deployment to patch, one set of servers to monitor, one schema migration instead of two hundred. The trade-off is that isolation stops being a property of the infrastructure and becomes something your application code has to enforce on every single query. How tenant data is separated There are three common patterns, in increasing order of isolation and operational cost: Shared tables with a tenant column. Every row carries a tenant_id (or account_id, or org_id), and every query filters on it. Cheapest to run, easiest to get wrong. Separate schemas per tenant. Same database, different namespaces. Reasonable middle ground until the tenant count gets into the thousands. Separate databases per tenant. Strongest isolation and simplest reasoning, but the most infrastructure to manage. With the shared-table approach, a forgotten WHERE tenant_id = ? in one query is a data leak, so the filter usually belongs somewhere structural — a view, a row-level security policy, or a query layer that appends it automatically — rather than being retyped by hand: SELECT order_id, total, created_at FROM orders WHERE tenant_id = :tenant_id AND created_at >= '2025-01-01'; Multitenancy in analytics The problem shows up again when you put analytics in front of those tenants. If you’re building embedded analytics for customers, you want to build a dashboard once and have it show each customer only their own rows — not maintain one copy per account. That’s what a data sandbox does: it defines boundaries on a table down to its rows and columns, so the same question returns different results depending on who’s looking at it. Sandboxes can be coordinated with your SSO setup, so the tenant identity your app already knows about drives what the analytics show. Pair that with white labeling and the embedded reports look like part of your product rather than a bolted-on tool. Things that bite people Internal reporting that forgets the filter. Your own team’s dashboards usually run without tenant scoping — fine, until one of them gets shared into a customer-facing context. Noisy neighbors. One tenant with 400 million rows can slow queries for everyone else on shared infrastructure. Aggregates across tenants. Useful for your business, dangerous to expose. Keep those questions in a collection that customers can’t reach.

A native query is a query written as text in the database’s query language (usually SQL).

The native query editor is the editor in Metabase used to write queries as text. It includes a sidebar with three tabs: data reference, SQL variables, and Snippets.

Data normalization is the process of structuring information within a relational database to reduce redundancy. Normalizing data ensures that the tables in a database function as efficiently as possible, eliminating ambiguity so that each table serve a singular purpose. When moving from a denormalized database to a normalized one, you’ll probably need to break out your existing tables to create additional, smaller tables. Those new tables will have a narrower focus, and will link to other tables through entity and foreign keys. With normalization comes the added benefit of reducing your overall database size and easing database maintenance, since you’re no longer storing the same information in several places. Example of data normalization The normalization process is carried out according to rules that build upon each other, known as normal forms. First normal form (1NF) states that fields should not store multiple values within a single cell, and that each field within a table should be unique. Here’s an example: Denormalized table Product_ID Product_name Product_color1 Product_color2 P001 Knit cardigan Pink Maroon P002 Bootcut jeans Navy   P003 Linen vest Camel Off-white P004 Running sneakers Orange   You’ll notice that we have two fields containing similar information, about product color. To bring this table in accordance with 1NF, we need to break this table out into two separate tables that can be joined together. Normalized product name table Product_ID Product_name P001 Knit blazer P002 Bootcut jeans P003 Linen vest P004 Running sneakers Normalized product color table Product_ID Product_color P001 Pink P001 Maroon P002 Navy P003 Camel P003 Off-white P004 Orange Check out our Learn article for examples of 2NF and 3NF. While normal forms beyond these three exist, their use is largely theoretical and the first three should be sufficient for most practical database needs.

A north star metric is the one number a company or team agrees to steer by. Its job is coordination: when a hundred people are making small decisions every week, a shared north star makes those decisions point the same direction. Classic examples are nights booked for a marketplace, messages sent for a communication tool, or weekly active teams for a collaboration product. What qualifies A good north star metric has four properties: It measures value delivered, not effort spent. “Songs listened to” is value. “Features shipped” is effort. It moves ahead of revenue. If it only goes up after money lands, it’s a scoreboard, not a steering wheel — see leading indicators. Teams can actually influence it. If no roadmap decision plausibly moves it this quarter, it won’t change behavior. It survives decomposition. You should be able to break it into inputs each team owns — new users × activation × frequency — so that the north star has a real relationship with the KPIs below it. Common failure modes Vanity metrics. Registered users, page views, and cumulative totals only go up. A number that cannot fall carries no information; if a bad quarter wouldn’t show up in it, it’s not a north star. Gameable metrics. Anything a team can move without helping a customer will eventually be moved that way — not usually through bad faith, but because incentives quietly reshape priorities. Sessions per user goes up when the product is confusing. Tickets closed goes up when agents close tickets early. Revenue as the north star. Revenue is the outcome you want, but it’s lagging, heavily influenced by pricing and sales mix, and gives a product team almost no signal about what to build next week. Too many north stars. If everything is a north star, nothing is. One metric or a small set? One headline metric works well when a company has a single dominant loop. Most larger companies need a small constellation instead — a north star plus three or four inputs, plus explicit guardrail metrics that must not degrade while you push the star up (churn, support load, latency, margin). The guardrails are what stop the north star from being optimized into a bad product. Whatever you pick, write down the exact definition, the segment it applies to, and who owns it. Undocumented definitions drift, and two teams quietly computing the same north star differently is worse than having none. Browse the metric guides for candidate definitions and the data each one needs.

The notebook editor is the GUI interface for asking questions in Metabase. See query builder for more info.

OLAP stands for online analytical processing: processing that involves a small number of large operations, such as creating quarterly reports. “How did revenue break down by plan and region over the last eight quarters?” is an OLAP question. Answering it means scanning a lot of rows, grouping them, and returning a handful of numbers. OLAP is the counterpart to OLTP, which handles a large number of small operations. The two workloads pull database design in opposite directions, which is why most companies eventually run both kinds of system. What OLAP workloads look like Analytical queries share a shape: They touch a lot of rows but return few, because they end in an aggregation like SUM, COUNT, or AVG. They read a few columns out of wide tables, rather than whole records. They’re mostly read-only. Data arrives in scheduled batches, not one transaction at a time. They’re unpredictable. Nobody knows in advance which grouping someone will want next. Here’s a typical one: SELECT date_trunc('quarter', created_at) AS quarter, plan, sum(subtotal) AS revenue FROM orders GROUP BY 1, 2 ORDER BY 1 That query reads two columns from potentially hundreds of millions of rows and returns a few dozen. On a database tuned for single-row lookups, it’s expensive; on one tuned for analytics, it’s routine. How OLAP systems are built for it Analytical databases — Snowflake, BigQuery, Redshift, ClickHouse, DuckDB — make different trade-offs than transactional ones: Columnar storage. Values from the same column live together, so a query that reads two of forty columns only pays for two. Compression. Columns of similar values compress well, which cuts how much data has to come off disk. Denormalized schemas. A star schema keeps a wide fact table beside smaller dimension tables, trading redundancy for fewer joins. Parallel scans. Work is spread across nodes, because scanning is the bottleneck. The older, narrower meaning of OLAP — precomputed multidimensional “cubes” that you slice and dice — still shows up in vendor documentation. Modern columnar warehouses mostly do the same job by querying raw tables fast enough that precomputation isn’t required. Getting OLAP data in the first place Your source data usually starts in transactional systems and SaaS tools. Moving it into an analytical store is what ETL and ELT pipelines are for. Once it’s there, a BI tool can sit on top so people can ask questions without writing SQL by hand.

OLTP stands for online transaction processing: processing that involves a large number of small operations, such as logging user activity on a website. Placing an order, updating a profile, adding an item to a cart — each one touches a few rows and has to finish in milliseconds, thousands of times a second. OLTP is the counterpart to OLAP, which handles a small number of large operations. Same data, opposite access patterns. What OLTP workloads look like Transactional queries share a shape: They read or write a handful of rows, usually looked up by primary key. They’re a mix of reads and writes, and the writes matter — correctness is not negotiable. They’re predictable. The application issues the same few dozen query patterns forever. They run constantly and must stay fast under concurrency. SELECT * FROM orders WHERE id = 48291; UPDATE orders SET status = 'shipped' WHERE id = 48291; The “T” is doing real work here. A transaction groups several statements so they either all succeed or all roll back — you never want an order marked paid without the payment row landing too. How OLTP systems are built for it Transactional databases like PostgreSQL, MySQL, and SQL Server optimize for this pattern: Row-oriented storage. An entire record sits together on disk, so fetching one row is one read. Indexes. B-tree indexes turn lookups into near-constant-time operations instead of table scans. Normalization. Data is split across many narrow tables so each fact is stored exactly once and updates stay cheap and consistent. Locking and isolation. Concurrent transactions are kept from stepping on each other. Why you shouldn’t run analytics on your OLTP database The application database behind your product is an OLTP system, and it’s tempting to query it directly. Two things go wrong. First, a single analytical query can scan enough rows to slow down the database your customers are actively using. Second, normalized schemas make analysis painful: a question about revenue by plan may need five joins before you can start. The usual fix is to copy data out on a schedule with an ETL or ELT pipeline into an analytical database, or at minimum to point reporting at a read replica so heavy queries can’t hurt production. From there you can join product data with the SaaS tools your team runs on and analyze all of it together.

An ordinal axis is an axis in a chart that displays values according to the discrete groups those values are in.

Our Analytics is the root collection in Metabase.

A parameter is a special type of variable that specifies an input to a query. Setting a parameter lets end users input a value (like in a dashboard or report) to change what data that query returns, typically filtering by a measure or dimension. The parameter passes that value through to the query being run, and the results of that query will depend on whatever value that person entered. Parameter vs. variable vs. argument You may see “parameter” used interchangeably with “variable” or “argument,” so it’s worth pointing out some distinctions here. A parameter is a type of variable; it’s just one where some specific input value gets passed along to the program or query being run. Not all variables are parameters, though — you may also have variables that are set within your program or query and can’t be modified by anyone on the other side. An argument refers to the value itself that gets passed along when your program or query runs. For example, if you set a parameter as {% raw %}{{productID}}{% endraw %}, and enter a value of 34, your argument is 34. A parameter defines that there will be an input value, but the input value itself is the argument. So yes, technically these terms all differ, but it’s okay to use them interchangeably, as long as you’re generally referring to a place or container to pass values into so you can filter results. Parameters in Metabase In Metabase, you can set a parameter using a filter widget or via a URL. Parameters come into play in Metabase in a few different ways: SQL templates By adding parameters to SQL queries in Metabase, you can create SQL templates that add filter widgets to those queries, allowing people to easily change that parameter’s value when they run that query. If we wanted to create a SQL template on a query that counted the number of customers in each state using the Sample Database’s People table, we’d use: SELECT count(*) FROM people WHERE state = {%raw%}{{State}}{%endraw%} By wrapping {%raw%}{{State}}{%endraw%} in double curly brackets, we’ve created a parameter that adds a filter widget to this question, letting people input the state they want without needing to change the text of the query itself, like in figure 1: {% include image_and_caption.html url=”/glossary/images/parameter/parameter-sql-template.png” description=”Fig. 1. Creating a SQL template that adds a filter widget to a query.” %} Dashboard filters Dashboard filters let you set parameters that get applied to a dashboard. For example, you can create a dashboard filter that lets people input a State value and link that that filter to the State column in the questions or cards on your dashboard. Then when people enter the value they want (like North Carolina, shown in figure 2), they’ll see those cards change accordingly. {% include image_and_caption.html url=”/glossary/images/parameter/parameter-dashboard-filter.png” description=”Fig. 2. A dashboard with a filter applied on the State column.” %} When you enter a value into a dashboard filter, you’ll notice that the URL changes to include that value. Custom destinations You can also insert parameters into a URL to dictate what happens when people click on a chart in a dashboard. For example, you can set a custom destination by using values from the card’s results to construct a URL that directs people to another dashboard or external site with that ID as part of the URL. Maybe you have a dashboard containing questions that track how different products in your inventory have sold, and a dashboard filter that lets people input the product they want to check in on. You could take things a step further by passing that product’s ID onto a custom destination, parameterizing a URL with that ID value and sending people to that product’s page on your website. When you visit that site, its URL may look something like this: https://www.your-website.com/products/id?productID=34 In this case, that productID=34 in the URL is your parameter. Embedding When embedding Metabase questions and dashboards in your app, you can set parameters to customize what different users see when viewing those embeds.

A personal collection is Your semi-private collection in Metabase, viewable only by you and Admins. Your personal collection is a great place to draft questions and dashboards.

A pin map displays a single discrete marker for each point of interest.

A pivot table is a data visualization tool that summarizes rows and columns of a table and lets you rotate (“pivot”) the columns to view those summaries in different ways. The summary rows are usually subtotals or grand totals, though they can also be other metrics like averages. This ability to rotate columns by 90 degrees, so that the values in that column become the columns themselves for the pivoted tables, can be really helpful when trying to analyze data across multiple dimensions, like time, location, and category. Example pivot table If we want to see how orders perform over the days of the week, broken out by different product categories, a pivot table is a great choice, as it’ll give us an easy-to-digest glimpse at a lot of numerical data. That pivot table may look something like this: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/pivot-table/pivot-table.png” description=”Fig. 2. A pivot table containing information about how four product categories performed on different days of the week.” %} It’s nice to have those summary rows that include totals for each day of the week, but it’s still not easy to compare category sales on different days. However, if we pivot the Created At column so that its values become the column headings, our result looks like this: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/pivot-table/pivot-table-pivoted.png” description=”Fig. 2. Our same pivot table, with the Created At column pivoted, giving us a better look at all of the data in our table.” %} Now we can quickly compare orders across day and product category, while still seeing the totals for both, without having to scroll through a long list of rows.

In SQL, a predicate is a type of conditional expression that evaluates to either true or false, like quantity > 0. Including a predicate in your query narrows down your results by filtering out unwanted rows based on whether that expression returns true or false. Predicate expressions all contain some sort of comparison element, like =, >, or <. When evaluated, the resulting true and false values are known as boolean values, though not all databases support boolean values as a data type. Not all databases support the same list of predicates either, especially predicates beyond mathematical comparisons (like BETWEEN or ISNULL), so check out your database’s documentation to know for sure which predicates will work for your use case. Null values: not zero, just not there While predicates typically evaluate to one of two boolean values (like true or false), if the field being evaluated lacks a value entirely, it’s known as null. That doesn’t mean its value is zero, but that rather that is no value present in that field. If your predicate expression requires that quantity > 0, then a row without values will not return true or false, but rather will return null. Example predicate An example of a predicate is condition that follows WHERE in a simple SQL SELECT query, like so: SELECT * from orders WHERE subtotal > 35 In this case, our predicate expression is subtotal > 35. Each row in the Orders table has a value in the Subtotal field, and for each row, this predicate evaluates whether it’s true or false that the subtotal is greater than $35. From there, our query returns only those rows with a subtotal greater than $35. In Metabase’s query builder, you use predicates when filtering your data. You can also write your own predicates in the notebook editor using custom expressions. In the question below, we’re filtering the People table in the Sample Database to only show us records where the State field equals Montana, or state = MT: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/predicate/predicate-filter.png” description=”Fig. 1. A predicate expression (or filter) in Metabase’s query builder that will return only records where the State field equals Montana (MT).” %}

A primary key is a column (or a set of columns) whose value uniquely identifies every row in a database table. If you know a row’s primary key, you know exactly which row you’re talking about — no ambiguity, no duplicates. What the constraint actually enforces In a relational database, declaring a primary key is a promise the database itself enforces. Two rules come with it: Uniqueness: no two rows can share the same primary key value. Not null: the primary key column can never be empty. You declare it when you create the table: CREATE TABLE customers ( id INT PRIMARY KEY, email TEXT NOT NULL, signed_up_at TIMESTAMP ); Try to insert a second row with id = 42, and the database rejects the write. That’s the point: the constraint stops bad data from getting in, rather than leaving you to find duplicates months later in a report. Natural keys vs. surrogate keys A natural key is a column that’s already meaningful to the business and happens to be unique — an ISBN, a country code, a SKU. A surrogate key is a value invented purely to identify the row: an auto-incrementing integer, or a UUID. Surrogate keys are the common default, because natural keys have a habit of changing. Email addresses get updated, product codes get restructured after an acquisition, and a “unique” phone number turns out to be shared by two people at the same company. When a key changes, every table that references it has to change too. A meaningless integer never has that problem. Why analysts care Primary keys are what make joins trustworthy. A foreign key in one table points at the primary key of another, and that pairing is how orders knows which customer placed it. If the key you’re joining on isn’t actually unique, the join silently multiplies rows — one order matches three customer records, your revenue total triples, and nothing in the query errors out. Primary keys also make normalization possible. Splitting data into separate tables only works if each table has a stable identifier that other tables can reference. Primary keys in Metabase When Metabase syncs a database, it reads the declared primary key constraints and marks those columns with the Entity Key field type. That’s the tag that lets Metabase link related records together and offer drill-through into a single row’s detail view. If your warehouse tables don’t declare constraints — which is common in analytical databases — an admin can set the Entity Key field type by hand on the Data Model page.

A production database stores information that Metabase manages (as opposed to the application database that stores Metabase’s own management data).

A public embed is a question or dashboard that Metabase serves at its own public URL, which you can then drop into another web page in an iframe. Anyone who has the link can view it — there’s no login, no permission check, and no way to tell one viewer from another. That last point is the whole trade-off. A public embed is the simplest way to put a chart in front of people, and the only one that needs no authentication work on your side. It’s also the wrong choice for anything a stranger shouldn’t see, because “unlisted URL” is not access control. Treat the link as public the moment it exists. When a public embed is the right fit Public embedding suits data you’d be comfortable publishing anywhere: A status or uptime page showing service health to customers. Community or open-data dashboards — usage stats, roadmap progress, release cadence. A chart in a blog post, docs page, or conference talk. Internal wikis where the whole company can already see the underlying numbers. When to reach for something else If viewers should each see their own slice of the data — one customer’s orders, one team’s tickets — you want secure embedding instead, where a signed token carries the viewer’s identity and Metabase filters the results accordingly. If you only need to hide the Metabase chrome for internal users who already have accounts, interactive embedding keeps their permissions intact. The rule of thumb: public embeds answer “what should everyone see?” Every other embedding question is really a question about who is looking.

Now deprecated in favor of Dashboard Subscriptions, pulses were questions that ran according to a specific schedule and would be sent out via email or Slack.

In Metabase, the query builder is the graphical interface for asking questions. If you aren’t a SQL person or just prefer to analyze your data using buttons and dropdowns instead of code, the query builder’s got you covered. And if you aren’t exactly sure what you’re trying to figure out about that data, those buttons and dropdowns can give you some ideas, like listing options for the filters and groupings you can add to your starting table, model, or saved question. Asking questions with Metabase’s query builder There are a couple ways you can use the query builder to ask questions about your data: Start from the data browser. Add filters and summarizations using the sidebars to the right of your data visualization. Create your question from scratch using the query builder interface. The query builder offers more flexibility for constructing a question: in addition to the regular filtering and summarization options, you can use custom expressions to create more sophisticated filters and aggregations. You can also join tables, create custom columns, and preview your results at each step before visualizing the final product. These paths aren’t mutually exclusive — you can start in the data browser, visualize your data, use the sidebars to tweak your question, open the query builder to make additional changes, and so on. Example: using the query builder We’ll use the query builder to construct a question using Metabase’s Sample Database. Let’s say we want to know how our large orders (that is, orders with a Subtotal greater than $100) are broken out by Product → Category. Figure 1 shows how we’d construct this question in the query builder: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/query-builder/notebook-editor.png” description=”Fig. 1. Asking a question using the query builder.” %} Once we visualize our question, let’s add another filter so we’re only viewing full-price orders (orders where there was no discount applied). Figure 2 shows what our query builder looks like just before adding that second filter: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/query-builder/second-filter.png” description=”Fig. 2. Adding a second filter while visualizing our data in the query builder.” %}

In Metabase, a question is a query, its results, and its visualization. If you’re trying to figure something out about your data in Metabase, you’re probably either asking a question or viewing a question that someone else on your team created. In everyday usage, question is pretty much synonymous with query. What you can do with questions in Metabase You can ask questions in Metabase using the graphical query builder or the native query editor, and then do things like: Save your question to a collection so that you can come back to or build on it later. Add that question to relevant dashboards. Questions on a dashboard are known as cards. Set up email or Slack alerts on your question. Share the results of your question by sending links to people on your team — even to questions that you haven’t saved. Download the results of your question as CSV, XLSX, or JSON. Convert your saved question to a model. Example question Figure 1 shows a question based on Metabase’s Sample Database — the average rating of our company’s Products, broken out by Category. Here we’ve visualized this question as a bar chart: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/question/example-question.png” description=”Fig. 1. An example question with one summarization, visualized as a bar chart.” %} And figure 2 shows what this same question looks like as a table: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/question/example-question-table.png” description=”Fig. 2. The same question, visualized as a table.” %} Questions and the Metabase API In the Metabase API, you can edit and get information about questions in your Metabase instance using the api/card route.

A read replica is a copy of a database that receives a continuous stream of changes from the primary and answers read-only queries. Writes go to the primary; the primary ships its changes to the replica; the replica serves SELECTs. Most managed database services will create one for you with a checkbox. Replicas do two jobs. They spread read load across more machines — a form of horizontal scaling — and they give you a warm copy to promote if the primary fails. Why this matters for analytics The single most common reason to add a replica is analytics. A production database is tuned for OLTP: thousands of small, fast transactions that keep your app responsive. An analytical query is the opposite — it scans millions of rows, aggregates them, and doesn’t care much about latency. Run enough of those against the primary and checkout gets slow. Pointing your BI tool at a replica instead is one of the cheapest fixes available. The data is the same, the SQL is the same, but a badly written query with a missing filter now hurts a machine nobody’s shopping cart depends on. If your data volumes are modest and you don’t have a warehouse yet, a replica is often the right first step — and a lot less work than building an ETL pipeline. What a replica does not fix A replica is still a row-oriented transactional database with your application’s normalized schema. It doesn’t make analytical queries fast, it just makes them somebody else’s problem. When those queries start taking minutes, the answer isn’t another replica — it’s a columnar analytical database or data warehouse with a model built for the questions you’re actually asking. Replication lag Replication is asynchronous in most setups, so the replica is always slightly behind — usually milliseconds, occasionally much more when the primary is under heavy write load or the replica is busy serving expensive scans. This is fine for most reporting and confusing for some of it. Things to keep in mind: “Today’s” numbers may not be complete. A dashboard refreshed at 9:00 may miss writes from 8:59:58. Support workflows break in obvious ways. A rep who updates a record in the app and immediately checks a dashboard may not see the change. Lag itself is worth monitoring. If it climbs steadily, either the primary is writing faster than the replica can apply, or your analytics queries are starving it. Read-only means read-only You can’t write to a replica, which is a feature: it removes a whole class of accident. It also means anything your BI tool needs to persist — scratch schemas, materialized results, persisted models — has to live somewhere else, so check that before you flip the connection over.

A record is a group of related data with the same structure. Just like in a traditional spreadsheet, records in a relational database are stored as horizontal rows within a table, and contain values that correspond with that table’s fields, or columns. Records typically reference a single unit, whether that’s a customer, an order, a session, or some other object that your database captures. A record in a database is usually identified by its value in that table’s entity key field. Example record Let’s take a look at the Orders table in Metabase’s Sample Database (figure 1). {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/record/orders-table.png” description=”Fig. 1. The Orders table, where each horizontal row is one record, or group of related data.” %} We see the fields in this table (the columns), like ID, User ID, Product ID, Subtotal, and so on. Each record has values that correspond with those fields, and together, those related properties make up one record. For example, we can see that the record (or row) with the ID of 8 was an order with a subtotal of $68.23, a discount of $8.65, and was created on June 17, 2019. The record right below it, with an ID of 9, follows the same structure, even though its values differ. We can click on the ID field to get a better view of a record itself, like in figure 2: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/record/record-8.png” description=”Fig. 2. Viewing the individual record for the order with an ID of 8.” %}

A region map is a map that shows information for geographic regions (such as countries or states), e.g., by coloring each region to show a value.

A relational database is a collection of tabular data, or the application that manages the storage and retrieval of tabular data. Relational databases contain tables, made up of columns (also known as fields) and rows (also known as records). You’ll establish relationships between tables in a database by assigning a single field to two or more tables. For one of those tables, that field will be designated as an entity key, while for the other(s) it’ll be a foreign key. With these relationships in place, you can query data (probably using SQL) across tables without having to reorganize or duplicate that data. Introduced in the early 1970s, relational databases remain a (if not the) dominant model for structuring data today. While technically a relational database refers to your data itself and a relational database management system (RDBMS) refers to the software application you use to manage that data, in reality people use the terms interchangeably. The relational model is so prevalent that in many contexts, the word “database” itself implies a relational one, unless otherwise specified. Example relational database Metabase’s Sample Database (the one you see used in examples throughout our docs and tutorials) is an H2 relational database. Figure 1 shows a look at the four tables in the Sample Database: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/relational-database/tables-in-sample-db.png” description=”Fig. 1. Metabase’s Sample Database (a relational database) contains four tables: Products, Orders, People, and Reviews.” %}

Retention is the share of a starting group — customers, users, or recurring revenue — that you still have at the end of a period. It is the complement of churn: 92% retention and 8% churn are the same fact stated two ways. Teams tend to use retention when they want to talk about the base they kept and churn when they want to talk about the leak. Retention rate vs. retention curve A retention rate is one number for one period: of the accounts active on July 1, 90% were still active on July 31. It’s easy to put on a dashboard and easy to misread, because it collapses new and old customers into a single average. A retention curve tracks one starting group over many periods: of the users who signed up in January, what share were still active in month 1, month 2, month 3, and so on. Curves are far more informative because they show shape. A curve that drops steeply and then flattens means you have a real core of users and a bad onboarding experience. A curve that declines steadily toward zero means nobody sticks, and no amount of acquisition will fix it. You can only see that shape by grouping users into cohorts. Revenue retention On the revenue side the same distinction shows up as gross versus net. Gross revenue retention counts only what you kept from the starting base and is capped at 100%. Net revenue retention adds expansion from those same accounts and can exceed 100%, which means the existing base grows on its own. Reporting only NRR is a common way to hide a leaky product behind a few large upgrades, so most teams watch both. Choosing a definition of “retained” For subscriptions, “retained” is usually unambiguous: the subscription is still active and paying. For products without a contract, you have to pick an activity definition and stick to it — logged in, completed a core action, placed an order. Different definitions produce wildly different curves, so write yours down next to the metric rather than leaving it implicit in a query. To build the curves themselves, start with the cohort retention dashboard for the triangle view, or the product retention dashboard for activity-based retention.

Reverse ETL is the process of copying data out of your data warehouse and into the operational tools your teams work in every day — a CRM, a support desk, an email platform, an ad network. It’s the same plumbing as a regular data pipeline, pointed the other way. How it inverts ETL A normal ETL job extracts from operational systems, transforms the data, and loads it into the warehouse so people can analyze it. Reverse ETL extracts from the warehouse, maps the results onto the fields a SaaS tool expects, and loads them in over that tool’s API. The transformation step is different in kind. In an ETL you’re mostly normalizing messy source data into something queryable. In a reverse ETL you’re doing field mapping and identity resolution: matching a warehouse row to the right Salesforce account or Intercom contact, deciding which system wins when both have a value, and syncing only the records that actually changed so you don’t burn through API rate limits. The “warehouse as source of truth” argument The case for reverse ETL is that your warehouse is the only place where the whole picture exists. Product usage lives in your application database, payments live in your billing provider, tickets live in your support tool. Only after those sources are joined together can you compute something like a customer health score, a product-qualified-lead flag, or a churn risk tier. Historically, those computed values stayed trapped in the BI tool. An analyst would build a dashboard showing which accounts were at risk, and a customer success manager would read the dashboard, switch to the CRM, and act. Reverse ETL closes that loop by writing the value onto the account record itself, so it shows up where the work happens — in a view, a filter, an alert, or an automated workflow. The second half of the argument is about consistency. If every tool computes “active customer” from its own partial data, you get four different answers. If the definition lives once in the warehouse — ideally in a semantic layer — and gets synced outward, everyone is working from the same numbers. What to watch for Reverse ETL makes your warehouse a production dependency. A broken transformation no longer just produces a wrong chart; it can push bad values into the system your sales team is quoting from. It’s worth treating synced fields as read-only in the destination tool, keeping the sync scoped to a small set of clearly-owned fields, and monitoring sync failures the way you’d monitor any other pipeline.

A right outer join is a join that keeps every row from the second (right-hand) table, whether or not it matched anything in the first (left-hand) table. Unmatched left-hand columns come back as NULL. It is the exact mirror of a left outer join: A RIGHT JOIN B returns the same rows as B LEFT JOIN A. Which means the interesting question isn’t what it does, but when writing it this way is clearer than flipping the table order. A worked example You have a tickets table and an agents table, and you want a workload view across your whole support team — including the two agents who happened to close nothing last week. SELECT agents.name, COUNT(tickets.id) AS tickets_closed FROM tickets RIGHT OUTER JOIN agents ON tickets.assignee_id = agents.id WHERE tickets.closed_at >= '2026-07-14' OR tickets.id IS NULL GROUP BY agents.name Before: 12 agents, and a tickets table with rows for only 10 of them. After: 12 rows. The two idle agents show up with tickets_closed = 0 instead of dropping out of the report — which is the whole point, since a workload dashboard that hides the people doing nothing is worse than useless. When the right join actually reads better Most analysts rewrite right joins as left joins, and that’s usually good advice: SQL reads top to bottom, so starting from the table you want to preserve keeps the query’s intent visible in the first line. Right joins earn their keep in two situations: You’re extending an existing query. There’s already a FROM tickets JOIN ... chain three joins deep, and you need to bolt on a table whose rows must all survive. Appending RIGHT JOIN agents is a one-line change; restructuring the whole FROM clause to lead with agents is not. Generated or layered SQL. When a model, CTE, or tool builds the left-hand side for you, you don’t get to choose what comes first. Watch the filter trap The OR tickets.id IS NULL in the example above isn’t decoration. Any WHERE condition on a left-hand column silently discards the preserved rows, because NULL >= '2026-07-14' is not true. The cleaner fix is to move the date condition into the ON clause, where it restricts which rows are eligible to match without evicting the unmatched ones. This bites harder on right joins than left joins, purely because the preserved table is the one further from your eye. Right outer joins in Metabase The query builder offers right outer join as one of the four join types in the join-type picker, alongside left outer, inner, and full outer. Support varies by database — a few engines that Metabase connects to don’t implement every join type, and the picker will tell you when an option isn’t available for that data source.

A row is a single group of related data within a table. Relational databases contain tables with rows and columns (also known as records and fields, respectively). Columns are vertical, and hold a list of values all from the same field. Rows are your horizontal elements in a table. The values in a row belong to different fields, but all refer to a single unit (like a row that contains information about one customer or one order). Row vs. record vs. tuple Technically speaking, a row is the underlying logical grouping of related data in a table, while a record refers to that same grouping within the context of an application. When it comes to inserting, updating, or deleting in SQL, you’re dealing with rows, not records. This is a pretty subtle distinction though, and most people use these terms interchangeably. You’ll sometimes see tuple used as well, which is a mathematical term for the same concept. Example row in Metabase Figure 1 shows the Products table in Metabase’s Sample Database, with one row highlighted. This row contains information from several different fields, each value relating to one of the products in our inventory (in this case, the Enormous Aluminum Shirt). {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/row/example-row.png” description=”Fig. 1. A row in the Products table.” %}

A row chart is a variation on a bar chart, where data is visualized with horizontal bars rather than vertical.

Security Assertion Markup Language (SAML) is n XML-based standard for exchanging authentication and authorization data.

A stock keeping unit (SKU) is a scannable bar code used to uniquely identify some type of product.

Structured query language (known as SQL) is a standardized and widely-used language for accessing and manipulating data in a relational databases. Using SQL involves writing and executing structured commands, known as statements, that communicate to a database what information you need or what you want to change. SQL is a published ANSI and ISO standard, meaning there are established rules about what exactly the language includes and how it works. However, SQL-based database systems (like PostgreSQL, MySQL, SQL Server, etc.) each have slightly different functions and their own syntactical quirks — no major databases conform 100% to the official written standard. Using SQL, you can: Create and configure databases, tables, and indexes Insert, update, and delete information in databases Retrieve information from databases (commonly known as querying) Set and adjust database permissions Is it pronounced “S.Q.L.” or “sequel”? Opinions are split on the matter of pronunciation, with some of those opinions very strongly held. When computer scientists Donald Chamberlin and Raymond Boyce first developed the language specification in the early 1970s, they called it “SEQUEL” (pronounced “sequel”), but changed the language’s name to SQL when faced with a trademark dispute. The ANSI and ISO standards dictate that the official pronunciation is the initialism (“S.Q.L.”), but both pronunciations are common today. So go with whatever sounds best to you — just don’t be surprised when someone disagrees with you. Querying databases with SQL No matter how advanced or complex, all SQL queries involve telling a database to return certain columns from a table (or tables), and then optionally specifying conditions about which rows should be included in those results and how they should be presented. SQL is case-insensitive, but you’ll often see people capitalizing reserved words (e.g., functions and clauses like SELECT, WHERE, HAVING, or ORDER BY). You can format your SQL statements as a single line if you like, though people will usually break their queries out onto separate lines for readability. Example SQL query Here’s a SQL query that asks Metabase’s Sample Database to return a table of orders where the order subtotal was greater than $100: SELECT * FROM orders WHERE subtotal > 100 We can break this query down into three statements: SELECT * tells the database to return every column in the table. FROM orders tells the database which table that is. WHERE subtotal > 100 tells the database to filter results and only return rows where the value in the Subtotal field is greater than 100. The example query above is a pretty simple one; more advanced queries can include joins, aggregations, CTEs, and other tools for pulling and organizing data. SQL in Metabase You don’t have to write SQL when asking questions in Metabase (that’s what the query builder is for), but if you prefer SQL queries, the native query editor is there for you, along with features like: SQL variables (including field filters) SQL snippets SQL snippet controls (available in some plans) And if you do opt for using the query builder to ask questions in Metabase, you can always view the underlying SQL that powers your question or convert it to a native SQL query.

A SQL snippet is a named bit of SQL code that you can insert in a SQL query.

SSO is an authentication (auth) setup that lets people use one login to access independent apps. It’s a bit like using your passport to get into different countries. With SSO, you don’t need to use one login for each account, just as you don’t need to travel with different pieces of ID specific to each country. For example, you might have two different logins that are used with two different sign-in pages for your email and your online banking. If the IT teams at your email provider and bank each set up SSO, you would be able to use one sign-in page and login to access both websites. {% include image_and_caption.html url=’https://cdn.metabase.com/_glossary/images/sso/google-sign-in.png’ description=”Fig. 1. You’ll probably recognize this Sign in with Google prompt from other apps. This is an example of SSO set up with Google sign-in.” %} How does SSO work? Since digital authentication can’t be done in person, your identity on the internet is checked by a service that asks you for proof of something you know (like a password), or proof of something you physically have (like sending a code to your phone). These pieces of proof are called identity factors. The most common way to authenticate is through a single identity factor, such as a password. If you add another factor, like a phone number, you get two-factor authentication (2FA). Naturally, you can continue adding factors, (an email, a verification app, etc.), to get multi-factor authentication (MFA). Instead of using information that you know or have, SSO uses an identity factor called an authentication token that belongs to the SSO provider. An authentication token is a unique, anonymous piece of information that’s generated when you sign in to the SSO provider. The token is temporarily stored in your browser (like a browser cookie) or on the provider’s servers, and is only valid for a period of time — usually until you close the browser, or within an expiry window set by your security team. When you go to an app that is set up with SSO, it’ll automatically ask for the authentication token from the SSO provider, instead of asking you to log in. If the token is still valid (you’ve signed in to the SSO provider in the same session, and it hasn’t expired), your identity is considered authenticated, and you’ll be allowed into the app. If the token has become invalid, you’ll be prompted to sign in with the SSO provider to create a fresh one. Where does SSO fit into the bigger picture? Authentication only deals with who you are (identity). From there, other services keep track of where you are allowed to go, and what you can do once you get there (access management). Identity and access management (IAM) is the umbrella term for these tools and processes. SSO and other parts of an IAM toolkit are often packaged up by identity providers (IdPs), such as Okta, Auth0, or OneLogin, and implemented as part of cloud security. SSO in Metabase Setting up SSO in Metabase means that people won’t need to create a separate Metabase username and password to access your organization’s data. They can simply log in through the same account as your chosen identity provider. The open-source edition of Metabase can be set up with Google SSO or LDAP. Pro and Enterprise editions of Metabase work with SAML and JWT standards (in addition to Google SSO and LDAP). SSO can also be combined with data sandboxing in Metabase Pro and Enterprise plans to define the data that people can see and interact with, based on user attributes such as their department or role.

The Sample Database is a small database that ships with Metabase so you can start asking questions and creating dashboards before you connect to your database. The Sample Database contains four tables: Orders, People, Products, and Reviews. It’s an H2 database, and you’ll see the Sample Database used in examples all over Learn.

A saved question is a Metabase question that has been saved and given a name. The results of saved questions can be used as the input to other questions.

A schema is the design or structure that defines the organization of a dataset: which columns are grouped into tables, how those tables relate to each other, and the rules and data types that define those columns. Schema is an overloaded term; it’s an abstract word that has accumulated a lot of different definitions, and as a result can be confusing to sort out. Depending on the context, schema can mean: The overall structure, specification, or “blueprints” of your database A diagram that demonstrates how tables in your database relate to each other A single collection of tables (among many) within your database Finally, schema sometimes means something specific to whichever database platform you’re using, like in Oracle, where schema refers to all objects in a database created by the same user. Schema as overall structure: design and implementation Once you’ve figured how your data fits together from a high-level standpoint (that is, your conceptual data model), the next step is creating a schema that reflects that data model, bringing it from the abstract to a database that your organization can use and populate with information. Broadly speaking, this process is made up of two major steps: Design: map out the structure of your database, creating an entity relationship diagram (ERD) in the process. Implementation: Use that ERD to generate the SQL commands that, when run in your database, will create your desired schema. What your schema design process looks like depends on whether you’re dealing with transactional or analytical databases, and whether you’re starting from scratch or have already begun collecting data. Regardless of at which point you’re designing schemas, you’ll have to think deeply about the needs of your organization and what questions you anticipate asking of your data. Schema-on-write vs. schema-on-read Most traditional relational databases use a schema-on-write system, where data gets verified and formatted into a schema before it’s written to that database. Since the data being written must conform to whatever specific data integrity rules you’ve established (like requiring that all values in a field be unique, not accepting null values in a field, or formatting dates a certain way), adding this new data to your database can be slow. However, the read times are fast, since that data has already been verified. In a schema-on-read system, data (like in a data lake) is only verified once it’s been read, or pulled from that database. Schema-on-read systems tend to be more flexible, as you can store unstructured data without worrying about it conforming to a rigid data model. In this case, writing data is faster (since that data doesn’t need to be verified as it gets loaded in), but queries take more time to execute. Whether you opt for a schema-on-write or schema-on-read strategy will depend on your organization’s needs and specific use cases. If having meticulously structured and consistent datasets is important to your organization, a schema-on-write system may be your best bet. By contrast, if you regularly need to pull in a wide variety of data without always knowing exactly what it that data looks like, you may want to use a schema-on-write system. Logical and physical schemas Regardless of whether you’re working with a schema-on-write or schema-on-read system, you’ll also need to think about database structure and its implementation — that is, your logical and physical schemas. Logical schemas define the structure of your data, while the actual implementation of that structure (like how and where you store the files and code that make up your database) belongs to a physical schema. Logical schemas Logical schemas are created by diagramming how tables and their fields relate to each other. In creating a logical schema, you’ll establish tables, relationships, fields, and views, answering questions like: What data are we collecting, or do we want to collect? What tables does your database (or individual schema within it) need? How do those tables relate to each other? What fields does does each table need? What are the data types for each of those fields? Which fields are required? Schema as diagram: mapping out entities and relationships In answering these and other questions, you’ll likely sketch out an entity relationship diagram (ERD) that defines each table, its fields, their integrity constraints, and the relationships between those tables, including the primary and foreign keys that establish those connections and whether those relationships between tables are one-to-one, one-to-many, or many-to-one. Visualizing your tables and how they relate to each other can also bring to light any major omissions or conflicts. And yes, sometimes you’ll see these diagrams themselves referred to as schemas. The image below shows an entity relationship diagram of a schema with two tables, PRODUCTS and MANUFACTURER. The “(PK)” and “(FK)” notations tell us which fields are primary and foreign keys, and the line linking these tables indicates a one-to-many relationship, in that one manufacturer can be linked to many products. {% include image_and_caption.html url=”/glossary/images/schema/simple-erd.png” description=”Fig. 2. An entity relationship diagram of a schema with two tables.” %} You can map out your schema on paper or using design software that can directly translate your diagram to the SQL commands that you’ll need to implement your database. At this point your schema is platform-agnostic; mapping out those rules and relationships doesn’t tie you to any single database software. Physical schemas Once you’ve identified the logical configuration of your database, you’ll create a physical schema to implement it into a specific RDBMS, defining where your database files will live as well as their storage allocation on a disk. Schema as one collection of tables among many While a single collection of tables may be sufficient if your database only sees a few users and contains data that everyone needs to access, you may find that a relying on a single schema in your database doesn’t cut it for your organization. If you’re handling data across a lot of tables (think in the dozens, hundreds, or thousands), grouping those tables into separate schemas will help from an organizational standpoint, making it so you can store similar information together while retaining the ability to query across schemas when necessary. Keeping multiple schemas within a database can be helpful from a security standpoint as well, like separating tables that hold sensitive information into a schema that only those who need to can access, usually in combination with views. Schema design for transactional vs. analytical databases When thinking about schemas for transactional databases (also known as operational databases), your data will need to be normalized to some extent and adhere to data integrity standards, since efficiency and performance for those small transactions and OLTP are crucial. Designing a schema for an analytical databases will look different. For starters, you’ve probably already collected raw data, possibly from multiple sources, and now need to impose some structure in order to analyze it. In this case, redundancy is okay, as analytical databases place greater emphasis on explorability and less on performance. Here your schema can also be more loosely defined, as no fixed patterns (like normalization) are needed. Schema design for analytical databases is more about understanding where data from your various sources lives, and knowing what tables you’ll need to join to answers questions you have. Star schema One common structure you’ll see applied to analytical databases is a star schema, which separates data into fact tables (that is, quantitative data) that relate to multiple dimension tables describing those facts. In a simple implementation of a star schema, several dimension tables all surround and relate to a single fact table, looking like a star in diagram form with the fact table at its center, like so: {% include image_and_caption.html url=”/glossary/images/schema/star-schema.png” description=”Fig. 2. An entity relationship diagram of a simple star schema.” %} Tables within a star schema are typically denormalized, which leads to better performance for analytical queries. Creating a database schema Most database platforms (such as Redshift and PostgreSQL) use “schema” to mean the configuration of a dataset and non-nested groupings of tables and other named objects within that dataset, though Oracle defines schema as all of the objects created by and belonging to a single database user. To create a schema within your RDBMS, use the query CREATE SCHEMA, like in this example, where we create a schema with two tables that are linked by the customer_id field: CREATE SCHEMA new_schema; CREATE TABLE new_schema.orders ( order_id product_id customer_id subtotal order_date ) CREATE TABLE new_schema.customers ( customer_id customer_name customer_address customer_email ); This is a very simple schema; we didn’t specify data types or any other constraints on the fields in our tables. If we wanted to require the customer_id field in the customers table and indicate that its data type is an integer, we’d format that field like this: customer_id INT NOT NULL Note that in MySQL, CREATE SCHEMA is synonymous with CREATE DATABASE.

A search filter is a filter that hides any rows in a table that don’t match some criteria.

A search widget can be added to a dashboard that applies a search filter to the values used in one or more cards.

A secure embed is a method for embedding an iframe displaying a question or dashboard that uses signed tokens for authentication.

A segment is a specific subset of a larger group of items, like a certain grouping of customers. The process of defining and creating these subsets is known as segmentation. For example, you may want to segment your customers based on demographic, recent activity, or some other attribute. In Metabase, Segments are named filters or sets of filters that people can apply to questions asked using the query builder. Admins can define segments to get everyone on the same page about which customers count as “New user” or which are a “Churn risk”, or however else you want to slice up your data. If you’re partial to native SQL queries, SQL snippets serve a similar purpose as segments do for GUI questions, ensuring consistent definitions that people can plug into their queries. Example segment in Metabase Figure 1 shows a look at the Products table in Metabase’s Sample Database. You’ll notice that the filter sidebar shows three segments at the top of the list, identifiable by a star icon. With these segments in place, we won’t need to recalculate what a high margin, new, or top rated product is each time we want to draw on those definitions. {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/segment/segment-example.png” description=”Fig. 1. Three segments that we’ve added to the Products table: high margin, new products, and top rated products.” %}

A semantic layer is a shared set of definitions for the metrics and dimensions in your data, sitting between the raw data warehouse tables and the tools people use to ask questions. It’s where “revenue,” “active customer,” and “region” get pinned down once, in a form that every chart and query can reuse. The problem it solves Ask three teams for last quarter’s revenue and you’ll often get three numbers. Finance excludes refunds and recognizes revenue over the subscription term. Sales counts bookings on the day the contract is signed. The growth team pulls gross charges straight from the payments table. Nobody is wrong, exactly — they’re each answering a slightly different question with a slightly different SQL query, and the differences only surface in a meeting where the numbers don’t match. The same thing happens to dimensions. “Enterprise account” might mean seat count in one query, contract value in another, and a manually-maintained list in a third. Once those definitions live only inside individual queries, they multiply quietly, and reconciling them becomes a recurring tax on everyone’s time. A semantic layer moves the definition out of the query and into a shared, named object. Instead of rewriting the WHERE clause that excludes internal test accounts, you reference the definition that already excludes them. When the definition changes, it changes everywhere. What’s in it Most semantic layers describe three things: the entities you report on and how they join, the dimensions you can group and filter by, and the metrics — the aggregations, with their filters and their grain. A good one also carries metadata that makes the data legible: human-readable column names, descriptions, units, and formatting. Note the difference from normalization or star schema design. Those are decisions about how the data is physically stored. A semantic layer is a description of what the stored data means, and it typically compiles down to SQL against whatever tables you already have. Semantic layers and Metabase Metabase covers this ground with two building blocks. A model is a curated dataset saved from a question or a SQL query, which you and everyone else can use as the starting point for new questions instead of going back to the raw tables — that’s where joins, cleanup, and column metadata get standardized. A metric is a saved aggregation, optionally with filters, that people can reuse when building questions rather than re-deriving the calculation each time. Together they let you define “monthly recurring revenue” once and have it mean the same thing on every dashboard that uses it. If you already maintain definitions in a dedicated tool like dbt, the sensible pattern is to keep that as the upstream source and mirror the definitions into models and metrics, so there’s still exactly one place where a change has to be made.

Serialization is the process of storing the state of an application or other object as a file, so that it can be reloaded (or deserialized) later. In the serialization process, that complex object gets converted to a stream of data, one that’s much easier to transfer over a network or copy to a new instance. Think of serialization like taking a snapshot of some application, but with the power to turn that flattened snapshot back into a live object whenever you’re ready. Serialization comes in handy if you need to manage multiple instances of an app, like development or staging instances in addition to the ones you use for production. Serialization can also be used to customize an initial state of an application, like preloading a Metabase instance with connection details to a database, predefined groups, dashboards, and so on. Serialization in Metabase {% include plans-blockquote.html feature=”Serialization” self-hosted-only=true %} Metabase’s serialization feature lets you capture and export the contents of your Metabase so that you can reload them into another instance — or several. Those Metabase artifacts you capture when serializing are collectively known as a data dump, containing YAML files that hold your collections, dashboards, saved questions, and some system settings.

A session metric is a measure of how someone interacted with a system, such as the number of pages they viewed in succession.

A session token is a random value generated by a server to identify a specific user for the duration of one interaction session.

A soft delete marks a record as no longer active or valid without actually removing it from the database. Instead of running DELETE, the application runs an UPDATE that sets a deleted_at timestamp or an is_deleted flag, and every part of the app that reads that table filters the marked rows out. Why applications do it Recovery. A customer deletes a project by accident; you flip the flag back instead of restoring a backup. Referential integrity. Deleting a user row would orphan their orders, comments, and audit trail. Marking it deleted leaves the foreign keys intact. Audit and compliance. You often need to prove what existed and when, which a hard delete makes impossible. Performance. Cascading deletes across large tables can lock things up; a single-column update is cheap. The cost is that your tables now contain rows nobody is supposed to see, and nothing in the schema forces you to remember that. The analytics consequence This is where soft deletes stop being a backend implementation detail and become your problem. The application filters deleted rows automatically. Your queries do not. Query a soft-deleted table directly and every count, sum, and average silently includes rows that the product considers gone: -- Overstates active customers SELECT count(*) FROM customers; -- What the app actually shows SELECT count(*) FROM customers WHERE deleted_at IS NULL; The symptom is a slow drift: your dashboard’s customer count creeps above what the app reports, and the gap grows every month as more records get soft-deleted. Nobody notices for a quarter, because the numbers are wrong by a plausible amount rather than an absurd one. Then someone reconciles churn rate against the product and you spend a day figuring out which of the two numbers to believe. Deleted rows also skew retention and cohort analysis in a particular way — the records most likely to be soft-deleted are the ones that churned, so leaving them in makes retention look better than it is. Handling it well The reliable fix is to apply the filter once, centrally, rather than in every question. Build a model (or a database view) that selects only the live rows, and point your questions at that instead of the raw table. Anyone using the query builder then gets the right rows by default without knowing the convention exists. A few things worth checking when you first connect to an application database: Which tables have a deleted_at, is_deleted, archived_at, or status column that means “gone”? Is the convention consistent across tables, or did different teams pick different column names? Do joins to those tables need the filter too? A join to a soft-deleted products row will quietly resurrect it in your results.

A stacked bar chart is a bar chart in which several values are shown for each category by stacking bars sized according to each value.

A star schema is a way of organizing analytical data into one central fact table surrounded by dimension tables that describe it. Drawn as a diagram, the fact table sits in the middle with dimension tables radiating out from it — hence the name. Facts and dimensions A fact table holds the events you want to measure, one row per event, at a consistent grain: one row per order line, one row per page view, one row per support ticket. Its columns are the numeric measures you’ll aggregate (quantity, amount, duration) plus foreign keys pointing at the dimensions. Fact tables are the tall ones — hundreds of millions of rows is normal. Dimension tables hold the descriptive context you slice by: customers, products, stores, dates. One row per customer, with every attribute you might want to group or filter on. Dimensions are wide and short. A query against a star schema then reads the same way every time — aggregate something from the fact table, group by an attribute from a dimension: SELECT d_product.category, SUM(f_order_items.amount) AS revenue FROM f_order_items JOIN d_product ON f_order_items.product_key = d_product.product_key JOIN d_date ON f_order_items.date_key = d_date.date_key WHERE d_date.year = 2024 GROUP BY d_product.category; Why denormalize This is where a star schema deliberately parts ways with normalization. In a normalized transactional database, product would be split across products, categories, subcategories, and brands, each in its own table, so that a category name is stored exactly once and can’t fall out of sync. That’s the right call when the priority is fast, correct writes. In a star schema you flatten those into a single d_product dimension with category, subcategory, and brand as columns. The category name repeats across thousands of rows, which would be a defect in an OLTP design and is fine here: Fewer joins. One join instead of four. Query planners handle the star pattern well, and the SQL people write by hand is dramatically simpler. Writes aren’t the constraint. Analytical tables are loaded by a data pipeline on a schedule, not updated by users, so the update anomalies normalization protects against mostly don’t arise. Storage is cheap. On an analytical database with columnar storage, a repeated string compresses down to almost nothing. It’s legible. “Group orders by product category” maps onto one obvious column, which matters when non-analysts are building their own questions in a query builder. The main variant you’ll meet is the snowflake schema, which normalizes the dimensions back out into sub-tables. It saves a little space and costs you joins and clarity — most teams building for BI stick with the star.

A summary table is the result of an aggregation that gets saved in a database or data warehouse so that people can work with those precomputed metrics. The term “summary table” can get confusing, since some people use “summary table” to describe any result of an aggregate function, like the table that you get after filtering and grouping by some measures and dimensions. By this definition, a summary table is basically the same thing as a pivot table, minus the pivoting. The difference here comes down to whether or not those tables get saved within your data warehouse. Creating summary tables in your data warehouse can make it easier for people to generate reports without having to query raw data. In this sense, summary tables function a lot like materialized views (which don’t necessarily aggregate data). Example: summary tables in a data warehouse For example, maybe you’re working with an analytical database that uses a star schema setup, with a fact table containing tens of thousands of individual orders records, surrounded by dimension tables that describe those orders. If someone at your organization wants to generate a weekly report containing sales data by product category from the last seven days, calculating that from your raw fact and dimension tables every time will be inefficient and costly. Instead, creating a summary table lets you join those tables and aggregate that data far less often. Then in the future when someone creates that report, they can do so using the summary table as a base, rather than needing to calculate those numbers every time from scratch. While there’s some maintenance associated with summary tables (like making sure your data refreshes on a schedule or adjusting the filters and groupings if they aren’t exactly what people need), they still tend to be a highly efficient way of working with large datasets.

A table is data’s natural habitat. In a database, a table is a series of fields, with the values of those fields arranged in rows, each row with a value corresponding to a field. Tables in databases are connected via entity and foreign keys. Databases are made up of tables, but table can also refer to a type of data visualization, or chart. The table visualization resembles a spreadsheet, with columns corresponding to fields (or aggregations, in the case of custom columns). Example table in Metabase Metabase’s Sample Database contains four tables: Products, Orders, People, and Reviews. Figure 1 shows a question in Metabase visualized as a table. This question adds one filter to the People table, so that our result is a table of our customers whose State is Georgia: {% include image_and_caption.html url=”/glossary/images/table/example-table.png” description=”Fig. 1. A table showing People in Georgia (GA).” %} With the toggle below the table, we can alternate between viewing this question as a table and as a pin map (or whatever other visualization you’ve chosen).

A text card is a card on a dashboard where you can enter Markdown text to give context for your dashboard. Text cards can also be used as dividers on a dashboard.

A time series is a sequence of data points over time. Times series are used for both analysis (how has the data performed over time) and forecasting (given the line’s trend, what can we expect next week, month, etc.). Typically you represent a time series as a line chart (called a run chart), but sometimes as a bar chart or area chart. In each case, the horizontal x-axis plots time, and each point plotted on the line is a value at a fixed interval (once an hour, or once a day, etc.). People will use the term “time series” to refer to the actual data by date/time as well as the chart that visualizes that time series. The values plotted on a time series can be measures (like a temperature) or metrics, such as the total number of orders placed in a week. If you plot the total numbers of orders for each week over fifty-two weeks (or some other period of time), the resulting chart is a visualization of a time series. Example time series Here is a time series of orders per month depicted as a line chart: {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/time-series/time-series-example.png” description=”Fig. 1. A time series showing the count of orders grouped by month.” %}

A transactional database is a database optimized for a large number of small operations rather than a few large transactions. OLTP usually relies on transactional databases.

A variable is any value in a program or query that can change. In SQL, declaring a variable allows you to temporarily store a single value while running a query. Parameters are a type of variable, but not all variables are parameters. When people talk about parameters, they’re usually referring specifically to variables that get modified by the end user of a dashboard or report, rather within the text of a query itself. Example variable in Metabase In Metabase, variables are placeholders in SQL queries for values that people can change without needing to rewrite the query itself. Using variables allows you to filter your data, often by adding a filter widget above that question in the SQL editor. Variables get enclosed in double braces, like this: {% raw %}{{variable_name}}{% endraw %}. In the example below, we create a variable to filter based on the Source field in the Sample Database’s People table: SELECT * FROM people WHERE source = {% raw %}{{source}}{% endraw %} When you include a variable in your query (in this case {% raw %}{{source}}{% endraw %}), Metabase adds a filter widget above the SQL editor, like in figure 1. Since the filter widget maps to the variable we created, we can plug different values into it to filter for different sources. {% include image_and_caption.html url=”https://cdn.metabase.com/_glossary/images/variable/variable-example-source.png” description=”Fig. 1. The filter widget above the query editor maps to the variable wrapped between double braces.” %}

Vertical scaling means making a single machine more powerful: more CPU cores, more memory, faster disks. It’s usually called scaling up, as opposed to horizontal scaling, which adds more machines. On a cloud provider this is often a dropdown and a restart. You change the instance type, the machine reboots on bigger hardware, and nothing about your application has to change. That is the entire argument for it, and it’s a strong one. Why to reach for it first No architectural work. Scaling out requires stateless servers, a load balancer, shared session storage, and a plan for scheduled jobs. Scaling up requires none of that. Nothing new to debug. One machine has one set of logs and no partial-failure states — no split brain, no instance running last week’s build. Some workloads only scale up. A single query that needs 40 GB of working memory to sort needs a machine with 40 GB. Adding a second small server does not help it. It’s often cheaper than the engineering. A month of a larger instance usually costs less than a week of the work needed to shard something. Where it runs out Two limits eventually bite. The first is physical. Instance sizes stop at some maximum, and long before that the pricing turns unfriendly — the top-end machine is frequently more than twice the price of the one at half its size, so your cost growth rate outpaces the capacity you’re buying. Adding cores also stops helping once the workload is bound by something else, usually disk or the fact that a single query only parallelizes so far. The second is availability. One machine is one thing that can fail, and resizing it means downtime. If the service needs to survive a host failure, no instance size fixes that — you need more than one machine, and that’s a horizontal problem regardless of budget. In practice For a BI tool instance, scaling up is often the right answer for a long time: memory is what usually runs short, since result sets and query processing live in the application’s heap, and a bigger box solves it directly. For an analytical database, scaling up buys you room while your data grows, but at some point the workload wants a distributed engine. The realistic pattern is not either/or. Scale up until the cost curve or the availability requirement makes it a bad deal, then scale out — and run a few larger instances behind a load balancer rather than dozens of tiny ones. Watch resource utilization before resizing: buying more CPU when the machine is actually waiting on disk is a common and expensive mistake.

A view is a saved query that functions like a virtual table in your database. You can interface with a view as if it was a real table, but views don’t store data themselves—instead, the database runs the underlying query every time you reference the view. Materialized views, on the other hand, are like regular tables in a database. While views require that a query be rerun every time that view is referenced, a materialized view is a precomputed view that’s saved in the database.

White labeling is the business practice of applying branding to someone else’s product. It generally involves changes to elements such as colors, logos, font, or content. White labeling can be used to create a sense of cohesion across products or services sourced from different places. Example of white labeling in Metabase {% include image_and_caption.html url=’https://cdn.metabase.com/_glossary/images/white-labeling/home-normally.png’ description=”Fig. 1. The clean, classic home page for Metabase without any white label changes.” %} {% include image_and_caption.html url=’https://cdn.metabase.com/_glossary/images/white-labeling/new-home-page.png’ description=”Fig. 2. The Metabase home page with a modified logo, color palette, and home page location.” %}

An X-ray is a summary or visualization of a table or column generated automatically to give users a quick insight into their data.