How do I build cost-efficient data pipelines on MotherDuck?
Bruin runs the whole pipeline against MotherDuck from one project: ingestion, SQL and Python transformations, and quality checks are all assets in the same dependency graph, so bruin run resolves the order and executes them in sequence. Loads use a direct read from Parquet, local or in cloud storage and incremental assets use a full rebuild for small tables, MERGE for larger ones. The alternative is assembling an ingestion tool, a transformation framework, and an orchestrator, which works but leaves you owning the glue between them.
Command
bruin runDefined in
SQL + Python + YAML
Works with
MotherDuck + Bruin CLI
What you get
How to do it
- 1
Run bruin init and add a motherduck connection for MotherDuck.
- 2
Declare an ingestion asset so raw data lands in MotherDuck via a direct read from Parquet, local or in cloud storage.
- 3
Add a SQL asset that models the raw table, with depends naming its upstream.
- 4
Declare column checks on the keys and amounts that matter.
- 5
Run bruin validate to confirm the graph resolves, then bruin run.
- 6
Schedule it in CI or Bruin Cloud, and switch heavy assets to a full rebuild for small tables, MERGE for larger ones.
How it works in code
/* @bruin
name: mart.orders
materialization:
type: table
depends: [raw.orders]
columns:
- name: order_id
checks:
- name: unique
@bruin */
SELECT order_id, customer_id, order_total
FROM raw.ordersRun bruin run and Bruin builds the asset on MotherDuck and runs its checks before anything downstream reads it.
Worth knowing
On MotherDuck, hybrid execution means you should be deliberate about which half of a query runs in the cloud
Other ways to do this
Bruin is not always the right answer. Here is where the alternatives are stronger.
| Option | When it is the better choice |
|---|---|
| Bruin | Build and run a complete MotherDuck pipeline with Bruin: ingest, transform in SQL or Python, and check the output, all from one project. |
| dbt + Fivetran + Airflow | The conventional split. Mature and well documented, but three tools to run and integrate for one MotherDuck pipeline. |
| SQLMesh | Strong on MotherDuck with virtual environments that cut the compute cost of reviewing a change. Transformation only, so you still need ingestion. |
| Native MotherDuck tooling | Staying inside MotherDuck avoids another vendor, at the cost of portability if you ever move warehouse. |
Common questions
How do I build an end-to-end data pipeline on MotherDuck?
Define each stage as an asset in one project and let the framework resolve the order. With Bruin, ingestion, SQL and Python transformations, and quality checks are all assets, and bruin run executes the graph against MotherDuck. Loads use a direct read from Parquet, local or in cloud storage.
Do I need an orchestrator to run MotherDuck pipelines?
Not for a straightforward pipeline. bruin run resolves dependencies itself, so a CI runner on a schedule is enough. A dedicated orchestrator earns its keep once you need complex retries, backfills, and cross-team scheduling.
What is the cheapest way to run pipelines on MotherDuck?
On MotherDuck the main lever is keeping work local where it can run local, since hybrid execution lets you choose. Incremental models using a full rebuild for small tables, MERGE for larger ones matter more than which tool you pick.
Related use cases
Build cost-efficient pipelines on Snowflake
How do I build cost-efficient data pipelines on Snowflake?
Cost efficiencyBuild cost-efficient pipelines on BigQuery
How do I build cost-efficient data pipelines on BigQuery?
Cost efficiencyBuild cost-efficient pipelines on Databricks
How do I build cost-efficient data pipelines on Databricks?
One pipeline, end to end
Open source. Ingestion, SQL and Python transformations, and checks in one graph.