Getting metadata in¶
Murakumo builds the raw layer of the model from your sources' metadata. It reads table and column definitions, keys and descriptions. It never reads data rows from databases, and from files it reads only what it needs to learn the schema.
| Command | Reads | Needs |
|---|---|---|
murakumo import csv FILE |
A data dictionary CSV, one row per column | - |
murakumo import excel FILE |
A dictionary workbook, every sheet or --sheet NAME |
- |
murakumo import database URL --source NAME |
A live database's tables, columns, keys and comments | murakumo[duckdb], [postgres] or [snowflake] |
murakumo import file LOCATION --source NAME |
A Parquet, CSV or JSON file's schema, local, glob or https:// |
murakumo[duckdb] |
murakumo import openapi LOCATION --source NAME |
One table per object schema in an OpenAPI or Swagger 2 spec | - |
murakumo import json LOCATION --source NAME --name TABLE |
A sample JSON response; types are inferred | - |
murakumo import |
Every source in murakumo.yaml with a from: line |
as above |
Every import command takes --project/-p (default: the current folder) and --dry-run, which
shows what would change without writing anything.
Data dictionaries¶
The format is in examples/README.md. Only
table_name and column_name are required. Headers are matched loosely (Table, table name
and TABLE_NAME all work, and so do common names like Field, Data Type and PK), and the
delimiter can be a comma, semicolon, tab or pipe.
logical_typeis used when it is a Murakumo type; otherwisesource_type(or a SQL type written inlogical_type, such asVARCHAR(40)) is mapped to one.foreign_keyholdstable.column(several separated by;). Columns that point at the same table become one composite relationship only when together they are that table's whole primary key; otherwise each column is its own relationship. So a trip's pickup zone and drop-off zone stay separate, andlineitemtopartsuppis a single two-column join.update_patternsets the load pattern: anything containingcdc,stream,append,incrementorfull/refresh/snapshot. The original text is kept inproperties.accepted_valuesiscode=label;code=label, or justa;b;c.
Bad rows are reported with their row number and skipped; the rest still import.
Databases¶
murakumo import database duckdb:///warehouse.duckdb --source erp
murakumo import database postgresql://reader@db.internal:5432/erp --source erp --schema sales
murakumo import database env:ERP_DATABASE_URL --source erp
murakumo import database snowflake:///SALES_DB --source sales # account and key from SNOWFLAKE_*
murakumo import database env:ERP_DATABASE_URL --source erp --table sales.orders --table customers
--table imports only those tables (table or schema.table, repeated for several); tables
already in the model from that source are left as they are, so you can add a database's tables a
few at a time. The canvas does the same with a list of tick boxes: see
the canvas.
Primary keys, foreign keys (composite ones too), NOT NULL, and table and column comments all
carry across. Table names stay as they are; the schema is added (sales__orders) only when two
schemas have a table with the same name.
Credentials never go into the model. Put the password in PGPASSWORD or ~/.pgpass, or the
whole connection string in an environment variable and pass env:NAME. If a URL does contain a
password, it is removed before the connection is saved in model/sources/.
Snowflake works the same way with snowflake:///DATABASE; see snowflake.md for the
key-pair setup. Databricks, BigQuery and the other warehouses follow in later milestones.
Files¶
murakumo import file https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet --source nyc_tlc
murakumo import file "s3-export/orders/*.parquet" --source erp --name orders
The table is named after the file without dates (yellow_tripdata_2024-01.parquet becomes
yellow_tripdata), or after the folder for a glob. Use --name to choose.
APIs¶
murakumo import openapi https://api.example.com/openapi.json --source shop
murakumo import json "https://archive-api.open-meteo.com/v1/archive?latitude=40.78&longitude=-73.97&start_date=2024-01-01&end_date=2024-01-07&hourly=temperature_2m,precipitation" \
--source open_meteo --name weather_hourly --path hourly
For OpenAPI, required decides nullability, enum becomes accepted values, allOf is merged,
and nested objects become json columns. For JSON samples, --path points at the rows; it can be
a list of objects, a single object, or columnar arrays like Open-Meteo's
{"time": [...], "temperature_2m": [...]}. Inferred types are only as good as the sample, so
use a sample with plenty of rows and check the result.
Protobuf¶
murakumo import protobuf protos/orders.proto --source events
murakumo import protobuf protos/orders.proto --source events --message Order --message Refund
Each message type becomes a raw table, its fields columns:
- integers and floats become numbers;
google.protobuf.Timestampbecomes a timestamp;- enums become text;
- nested messages and maps become
json; - repeated scalars become arrays.
Comments before a message, or before or after a field, become descriptions.
A data contract (ODCS)¶
murakumo import odcs orders.odcs.yaml --source shop_db --dry-run
murakumo import odcs orders.odcs.yaml --source shop_db
A contract in the Open Data Contract Standard (v3) becomes a raw table per table it describes: its columns and types, which may be empty, its key, and its quality rules. See operating.md. In the canvas: Health → Data contracts.
An existing dbt project¶
murakumo import dbt ../their_dbt_project --dry-run # see what it would add
murakumo import dbt ../their_dbt_project
For a team that already has a dbt project. It adds:
- each dbt source as a source system, with its tables as raw tables;
- each model as a modelled table:
fct_anddim_as facts and dimensions in marts, the rest as intermediate tables, with columns, descriptions and keys (fromuniqueandnot_nulltests, or aprimary_keyconstraint); relationshipstests as relationships;- where each column comes from, read from the model's compiled SQL. A column that is another
column under a new name, or a cast of one, gets a mapping. A computed column is left unmapped
and listed with what it reads, to be written as an expression by hand or with
murakumo ai map.
Staging models that only prepare one source table are not imported, because Murakumo generates staging from the raw tables itself. Their renames are followed, so a mart column maps to the raw column it began as.
Run dbt compile in the dbt project first: where columns come from is read from
target/manifest.json. Run dbt docs generate too if its YAML does not give column types: they
are read from target/catalog.json. Without a compile, the tables and columns still come in, and
the import says what is missing. Tables already in the model are left as they are, and the dbt
project is only read.
In the canvas: the Health panel, Bring in a dbt project.
Fetching the data¶
Importing reads a source's structure. Fetching its rows (from an API, SFTP, a database or a folder, on a schedule) is the source's connection: see stack.md.
Sources in the manifest¶
sources:
- name: erp
from: csv:dictionaries/erp.csv # paths are relative to the project folder
ingestion: { tool: airbyte, schedule: hourly }
- name: crm
from: introspect:env:CRM_DATABASE_URL
- name: nyc_tlc
from: file:https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv
- name: shop
from: openapi:https://api.example.com/openapi.json
murakumo import then refreshes all of them. ingestion.tool and ingestion.schedule are
copied to the source system.
The canvas's Sources panel adds entries to this list (keeping the file's comments), browses a
database source's tables, and imports or re-imports a source after showing what would change. A
database is always named by an environment variable there (introspect:env:CRM_DATABASE_URL) or,
for Snowflake, by its database name (introspect:snowflake:///SALES_DB), so no connection string
is ever written to the file.
Importing again¶
Run an import again whenever the source changes. Murakumo compares and updates the model:
- tables and columns that already exist keep their ids, so a changed type is an update, not a drop and a re-create;
- only what the source describes is overwritten; tests, tags, partitioning, custom properties and anything else you added by hand stay;
- new columns are added; columns the source no longer has are removed, because raw tables mirror their source;
- tables the source no longer has are reported and kept; add
--pruneto remove them and their foreign keys; - a table that belongs to a different source, or to a modelled layer, is never overwritten.
Imports from a single file or JSON sample describe only part of a source, so they never report other tables as missing.
Adding things by hand¶
For sources without metadata, or for modelled layers:
murakumo add source crm --type api
murakumo add entity dim_customer --layer marts --kind dimension
murakumo add column dim_customer customer_key bigint --pk
murakumo add column dim_customer email string --pii --classification confidential
murakumo add relationship fct_order dim_customer --on customer_key=customer_key
murakumo rename entity dim_customer dim_client # relationships, mappings, metrics and the file follow
murakumo rename column dim_client customer_key client_key # so do keys, grain, tests and expressions
murakumo remove column dim_client email
murakumo remove entity dim_client # also removes its relationships and file
Every change is saved and then validated, so you see straight away what else needs fixing. The canvas makes the same changes through the same code.