Using Metabase

Metabase is how we query data without asking an engineer. It runs in the browser and needs no install.

How safe is it? Our PostgreSQL connections are verified read-only: they use dedicated accounts with SELECT and nothing else, no ownership of any table, and no superuser rights. A DELETE or DROP against those fails with a permission error. The MongoDB connections are named read-only but have not been verified the same way, so treat them as unconfirmed until they are. Two honest caveats even so: a heavy query can still put load on a production database, and in a Mongo pipeline the stages $out and $merge write data, with $out replacing an entire collection. Never put either in a query. Everywhere else, the worst outcome is a slow result.

This page is how to use it. Reading a Schema is how to think while you do, and Database Design Principles is the picture underneath both.

What it actually is#

A window onto databases. Each connected database is a separate world, and picking the wrong one is the most common beginner mistake, because a plausible-looking table name often exists in more than one.

We connect several, and they are not interchangeable:

Client product databases, one or more per client, some MongoDB and some PostgreSQL.
Internal databases for our own operations and spend.
A Sample Database that ships with Metabase. It is fake demo data about a made-up company. It is genuinely useful for practising with zero risk, and genuinely embarrassing if a number from it reaches a client. Check the database name before you trust a result.

Before every question, confirm which database you are pointed at.

Collections: where questions live#

Saved questions and dashboards live in collections, which work like folders. Ours are organised mostly per client or per area.

Two rules keep it usable:

Your Personal Collection is your workbench. Anything exploratory, half-finished, or unverified goes there. Nobody else has to trust it.
A shared collection means “this has been checked.” Only move something in once you have verified it and written its definition into the title or description. A wrong question saved in a shared collection outlives your memory of it and will be cited back at you months later.

The same caution applies in reverse: a saved question you did not write is not automatically true. It was correct for someone’s question on the day they wrote it. Open it and read the filters before reusing it.

Start by browsing, not querying#

The fastest way to learn a schema is Metabase’s data browser. Pick a database and it lists the tables; pick a table and it shows you real rows immediately.

This is step three of Reading a Schema with no query written: read one real record, field by field, and see what the values actually look like. Do this before building anything. Ten minutes of browsing saves an hour of querying the wrong table.

Building a question without code#

The query builder is the point of Metabase: it produces a correct query without you writing one. Four moves, in this order.

Data. Choose the database, then the table. Confirm both.
Filter. Cut to the rows that count: a date range, a status, excluding cancelled. Add filters one at a time and watch the row count fall, so you can see what each one costs you.
Summarize. “Count of rows” grouped by a column is most of analytics. Grouping by a date column with a monthly or weekly bucket gives you a trend in one move.
Visualize. Leave it as a table while you are still checking. Switch to a line or bar only once you believe the numbers.

The most valuable habit here: read the generated query. Metabase will show you the SQL or Mongo query it built. Building in the UI and then reading what it produced is the least painful way to learn query syntax, because you already know what the answer should be.

When to write the query yourself#

The builder handles most questions. Drop into the native editor when you need something it will not express: several joins at once, a window function, a subquery, or awkward nested document fields.

Two things to know before you do:

The language depends on the database. A PostgreSQL connection takes SQL. A MongoDB connection takes a Mongo aggregation pipeline written as JSON, not SQL. They are not interchangeable, and this surprises people who learned SQL first. Querying MongoDB is that syntax, worked end to end.
Native questions lose some UI features. Filters and drill-downs that work on builder questions may not apply. Use the builder unless you have a reason not to.
Never write $out or $merge. Every other pipeline stage only reads. These two write, and $out replaces the whole target collection. There is no undo and no confirmation prompt.

The traps#

Every one of these produces a confident, plausible, wrong number rather than an error.

Wrong database. Similar table names exist in several. Check the connection name first, every time.
The row limit. Metabase caps displayed rows. If you are eyeballing a list and doing mental arithmetic, you may be reading a truncated one. Use a Summarize count for counting, never the length of a visible list.
Cached results. A dashboard may be showing an earlier run. If a number matters, refresh it and note when it was last run.
Timezones. Timestamps are often stored in UTC while you think in local time. Around midnight boundaries this quietly moves records between days, which matters most for exactly the daily counts people report.
Deleted and cancelled rows. Most systems flag rather than remove. Unless you excluded them, you counted them.
Staff and test accounts. Real rows, in production, inflating your totals.
Someone else’s saved question. Correct for their question on their day. Read the filters before reusing.

Your first week#

Work these in order, in your Personal Collection. They take an afternoon and they teach more than any course.

Browse one table on a real product database and write, in a sentence, what one row is.
Count its rows with no filters. That is your baseline; every filter from here should only shrink it.
Get the real status vocabulary: Summarize, count of rows, grouped by the status column. Note any value you did not expect.
Build a trend: count grouped by a date column, bucketed weekly. Look at it as a table first, then as a line.
Add one filter (exclude cancelled) and watch the number move. Ask whether the size of the drop makes sense.
Read the generated query. Find the filter you added in it.
Verify it: pull three individual rows the query says qualify, and read them.
Write the number down with its definition, then show it to your buddy and see whether they can break it.

Step 8 is the one that matters. A number you cannot defend is not an answer yet.