> ## Documentation Index > Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # SM Pipeline Builder > Build and operate your own data pipelines that land in your BigQuery datasets alongside SourceMedium data. Build data pipelines you own: pull from a source SourceMedium does not connect to, land it in your own BigQuery datasets, model it, check it, and run it on your own schedule. The agent writes a pipeline spec first, then builds against that spec rather than improvising. This skill is for technical staff who are comfortable owning a pipeline. If you want to query data that is already in your warehouse, use [SM BigQuery Analyst](/docs/ai-analyst/agent-skills/sm-bigquery-analyst) instead. ## Prerequisites The install command requires `npx`, which comes with Node.js 18+. Run `node -v` to check. If you see "command not found," install Node.js from [nodejs.org](https://nodejs.org) first. You also need: * **Write access to your own BigQuery datasets.** The skill reads SourceMedium data and writes only to datasets you own. * **A scheduler you run.** Cron, Airflow, dbt Cloud, GitHub Actions, Cloud Scheduler: the skill is scheduler-agnostic and does not install one. * **Python 3.9+ with PyYAML** for the bundled spec validator. The validator is optional; without it the agent runs the same checks by hand. Pipelines built with this skill run in your infrastructure, not SourceMedium's. If a SourceMedium-managed connector is failing, that is a support request, not a pipeline to rebuild. ## Install ```bash theme={null} npx skills add source-medium/skills --skill sm-pipeline-builder ``` Installing the analyst skill alongside it is recommended but not required. When both are present, the pipeline skill uses the analyst skill for warehouse discovery and access checks instead of doing it by hand: ```bash theme={null} npx skills add source-medium/skills --skill sm-bigquery-analyst npx skills add source-medium/skills --skill sm-pipeline-builder ``` If your agent does not support automatic install through the `skills` CLI, copy the skill folder into that agent's configured skills directory. ## Update ```bash theme={null} npx skills update sm-bigquery-analyst -y npx skills update sm-pipeline-builder -y ``` *** ## Quick Start (Copy/Paste) Copy the block below into your coding agent to spec your first pipeline. ``` Use the SourceMedium Pipeline Builder skill to design a nightly pipeline that loads our [source system] into our own BigQuery datasets in project [your-project]. Before writing any code: 1. Probe the source API with a small bounded sample and tell me what you actually observed: pagination, money units, timezone, restatement window. 2. Write the pipeline spec and validate it. 3. Show me the spec and the first-build window before you load anything. ``` *** ## What It Does **Spec before code.** The agent writes a pipeline spec naming the grain, primary key, cursor field, overlap window, first-build window, delete behavior, and quality checks. A bundled validator checks the spec and fails it for the mistakes that are expensive to find later. Nothing gets built against an unvalidated spec. **Measures your source instead of trusting its docs.** Pagination stop conditions, money units, history limits, restatement windows, and business-day timezone are established with a small live probe and written into the spec. Vendor docs describe the happy path; the probe describes the real one. **Loads that can be safely re-run.** Every load is a MERGE on a stable key or a windowed delete-and-reinsert over an identical window, so re-running any window any number of times produces the same table. Cursors never advance past data that was not written, which is what turns a truncated run into a retry instead of a silent gap. **Money that does not drift.** Currency is decimal, quantized before load, with the unit recorded in the spec. Rates and ratios stay floating point. This is the difference between a revenue column that reconciles and one that is off by cents in a direction nobody can explain. **Checks on the data, not the job status.** A green scheduler proves a run happened. The skill ships row-count floors, business-date coverage, freshness per stream, primary key uniqueness, and a reconciliation of sample totals against the source, all on a schedule. **A clean boundary with SourceMedium data.** The skill reads SourceMedium datasets and writes only to yours. It never creates, modifies, or drops anything in a SourceMedium dataset, and the spec validator rejects a spec that tries. **Publishing back out.** The same doctrine covers sending your tables from BigQuery into a system you own, such as a Snowflake instance or an object store, including declaring which columns leave the warehouse. *** ## Example Prompts ### New source into your warehouse ``` Design a pipeline that loads our loyalty platform's members and point transactions into our own BigQuery datasets. Probe the API first and report what you find. Then write the spec with the grain, primary key, cursor, and overlap. Backfill one month, reconcile the totals to the vendor UI, and only widen after that reconciles. ``` ### A mart joining your data to SourceMedium data ``` Build a dbt mart that joins our inventory snapshots to SourceMedium orders by store and week. Check the join cardinality before joining to revenue, apply the order validity filter, and gate freshness on SourceMedium's publish date rather than assuming today's data is there. ``` ### A backfill after fixing a bug ``` Backfill our custom pipeline for all of last year after the cursor fix. Run it in checkpointed windows so an interruption resumes instead of restarting, declare a ceiling before starting, and verify rows landed per window rather than trusting the exit code. ``` ### A source that rewrites its own history ``` Our ad platform recomputes the last 14 days and drops rows that go to zero. How should the daily spend table load, and what breaks if we use MERGE? ``` ### Publishing out of BigQuery ``` Publish our margin table from BigQuery into our Snowflake instance nightly. Tell me which columns cross the boundary and how a re-run avoids duplicating rows on the far end. ``` *** ## What You Get | Output | Description | | ------------------- | -------------------------------------------------------------------------------------------------------------------------------- | | Pipeline spec | The contract: grain, keys, cursor and overlap, windows, business-day close, declared checks. Validated before anything is built. | | Build | Load code, SQL, and model definitions, with the layer each belongs to stated explicitly: raw landing, clean, or mart. | | Validation evidence | Canary results, sample totals reconciled against the source, dry-run byte estimates for large queries. | | Runbook | Schedule, error handling, freshness expectation, and the backfill procedure. | | Readiness checklist | A per-pipeline checklist where each item needs evidence, such as a command or a row count, rather than an assertion. | *** ## Guardrails These hold regardless of how the request is phrased: * **Your datasets only.** SourceMedium datasets are read-only to your pipeline. The spec validator rejects a destination under `sm_`. * **Destructive operations pause for you.** Table replaces, truncates, window deletes, dataset drops, and full-history rebuilds state their scope, effect, and rollback and wait for your confirmation. A load job that overwrites a table counts as a delete even though the word never appears. * **Bounded first builds.** The first load is a small window, never all history. Backfills declare a ceiling before they start, so a loop cannot quietly run up a bill. * **Secrets stay named, never written.** The spec names the secret your runtime resolves. The validator rejects a spec carrying an actual credential value and tells you to rotate it. * **Nothing is fabricated.** If a check cannot run, the agent reports the exact failure and stops that scope rather than reporting a number it did not verify. *** ## After Building ### Validate the spec before going live ``` Run the spec validator in strict mode on our pipeline spec and explain anything it flags. ``` Strict mode promotes the easily-skipped declarations, such as the first-build window, delete behavior, and cost ceiling, into failures. ### Debug a load that "succeeded" with no data ``` Last night's run reported success but loaded zero rows. Check the run log and the target table coverage, and tell me whether the cursor advanced. ``` ### Change the grain of a table ``` We need to add a dimension to our daily rollup. Walk me through what that means for the existing table and history. ``` Adding a dimension changes what a row means, so it is a rebuild plus a re-walk of history rather than an in-place change. The agent will say so rather than altering the table in place. *** ## Related For querying data that is already in your warehouse. For dashboards built on the tables your pipeline produces. Schema-level documentation for the SourceMedium tables you join to. Canonical metric definitions, so a custom metric does not shadow one of ours.