# Medallion architecture with dbt Turn raw orders into tested tables your teams can use to report on sales. With Altertable federation, dbt reads the orders where they already live, without copying them first. Your dbt models then clean them into staging tables in your lakehouse, apply shared business rules, and publish a mart, all running through [dbt-altertable](https://github.com/altertable-ai/dbt-altertable). This guide walks through an orders pipeline from a connected database to a completed-orders mart, then uses that mart to calculate daily sales. We recommend following dbt's [project structure](https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview?version=2): organize models into `staging`, `intermediate`, and `marts`. Medallion names describe the data's role: | Layer | dbt equivalent | Responsibility | | ------ | ------------------------------- | --------------------------------------------------------------------------------------------------- | | Bronze | Sources | Read raw records from external catalogs or ingested tables. | | Silver | Staging and intermediate models | Clean, type, and deduplicate raw records into one row per entity, then apply shared business rules. | | Gold | Marts | Publish tested, business-ready tables your dashboards and teams query directly. | The pipeline starts at Bronze. Connect an [external catalog](/docs/lakehouse/external-catalogs.md) and read its raw tables through `source()`, without first copying them into a separate raw schema in Altertable. Already-ingested raw tables work too. Downstream models use `ref()` to declare their dependencies. ## Materialize at the staging layer Use federation for source access and staging tables for repeated analytical work: External catalog app\_postgres.public.orders (bronze) → Altertable catalog analytics: staging tables (silver) → intermediate views (silver) → marts (gold). dbt reads the external source through federation and materializes staging; downstream models read the stored data. Each staging build reads the source through Altertable, cleans and types its records, and stores the normalized rows in a managed table. Intermediate models and marts reuse those prepared rows without repeating the cleanup or querying the external source again. The staging refresh still puts load on the source; choose its frequency and scope accordingly. Organize your dbt models into three folders: `staging` for cleaning source records, `intermediate` for reusable transformations, and `marts` for datasets ready for analysis. This guide uses SQL models. For the staging models, set `+materialized: table` in `dbt_project.yml`, as shown below. This tells dbt to run each staging query and save its results as a table in Altertable. The next dbt build refreshes that table. Using `view` would save only the query definition, so reading it would query the external source again. Federation exposes the data the source retains; it does not create an immutable bronze history. If you need replay after source updates or deletes, retain raw history upstream or [ingest an archival copy](/docs/ingest-data.md) before transforming it. Rebuilding a staging table replaces its prior contents. ## Before you start - Create a writable [Altertable catalog](/docs/lakehouse/catalogs.md). This example uses `analytics`; substitute your catalog name throughout. - Install and configure the [dbt adapter](/docs/ingest-data/integrations/dbt.md) with your [lakehouse credentials](/docs/lakehouse/authentication.md). - Check the adapter's [README](https://github.com/altertable-ai/dbt-altertable#readme) for current Python and dbt version requirements and supported features. The dbt best-practice links describe project organization. - Connect your source database as an [external catalog](/docs/lakehouse/external-catalogs.md). This example calls it `app_postgres` and expects `app_postgres.public.orders` with `id`, `ordered_at`, `amount_cents`, and `status`, with one row per order and amounts in a single currency. Substitute your external catalog and schema names. Keep raw values and any source identifiers or ingestion timestamps you need to replay and audit data. This example reads a source with one row per order. If your source contains duplicates or multiple versions of each order, deduplicate in staging using the source's change timestamps and delete markers before applying the completed-order rule. ## Organize the project Create these files in your dbt project: ```text dbt_project.yml models/ staging/ app/ _sources.yml _models.yml stg_app__orders.sql intermediate/ int_orders__completed.sql marts/ sales/ orders.sql _models.yml ``` In `dbt_project.yml`, configure the project and folder defaults: ```yaml name: orders_analytics profile: orders_analytics models: orders_analytics: staging: +schema: staging +materialized: table intermediate: +schema: intermediate +materialized: view marts: +schema: marts +materialized: table ``` The key under `models` must match the project's `name`. ## Map folders to Altertable schemas The adapter's `database` field identifies an Altertable catalog. `schema` identifies the target namespace inside it. Set up two targets in `~/.dbt/profiles.yml`, supplying credentials through environment variables: ```yaml orders_analytics: target: dev outputs: dev: type: altertable username: "{{ env_var('ALTERTABLE_LAKEHOUSE_USERNAME') }}" password: "{{ env_var('ALTERTABLE_LAKEHOUSE_PASSWORD') }}" database: analytics schema: alice_dev prod: type: altertable username: "{{ env_var('ALTERTABLE_LAKEHOUSE_USERNAME') }}" password: "{{ env_var('ALTERTABLE_LAKEHOUSE_PASSWORD') }}" database: analytics schema: analytics_prod ``` Replace `alice_dev` with a developer-specific name. This example uses one Altertable environment and catalog, with separate output schemas. dbt target names do not create or select Altertable environments; use the appropriate environment's credentials and catalog when separating environments. Following dbt's [folder-to-schema mapping](https://docs.getdbt.com/best-practices/how-we-structure/6-mapping-folders-to-warehouse?version=2), keep its default `_` naming: | Folder | Development schema | Production schema | | -------------- | ------------------------ | ----------------------------- | | `staging` | `alice_dev_staging` | `analytics_prod_staging` | | `intermediate` | `alice_dev_intermediate` | `analytics_prod_intermediate` | | `marts` | `alice_dev_marts` | `analytics_prod_marts` | All these output schemas live in `analytics`. Both targets read `app_postgres.public.orders` through federation; neither writes models into the external catalog. Avoid replacing dbt's schema-generation macro just to remove the prefix: shared output names can let a development run overwrite production models. You can use `+schema: silver` and `+schema: gold` if your team requires medallion names. Assigning `silver` to both staging and intermediate puts them in the same schema. Keeping folder names makes those transformation steps easier to distinguish. ## Declare the bronze source In `models/staging/app/_sources.yml`, point dbt at the table exposed by the external catalog. This source's `database` differs from the output catalog in your profile: ```yaml version: 2 sources: - name: app database: app_postgres schema: public tables: - name: orders description: Source orders with amounts in cents, in a single currency. columns: - name: id tests: - not_null ``` Declaring a source does not connect the external catalog or create a table. Configure the connection in Altertable first and make sure your dbt credentials can read it. dbt connects to Altertable; Altertable accesses the external system using that catalog's connection. If you already ingest raw orders into `analytics.raw.orders`, use `database: analytics` and `schema: raw` in this source definition instead. The transformation models stay the same. ## Build the silver models In `models/staging/app/stg_app__orders.sql`, normalize the source fields with DuckDB-compatible SQL: ```sql select cast(id as bigint) as order_id, cast(ordered_at as timestamp) as ordered_at, cast(amount_cents as decimal(18, 2)) / 100.0 as amount, lower(trim(status)) as status from {{ source('app', 'orders') }} ``` With the folder configuration above, dbt reads `app_postgres.public.orders` and materializes this model as `analytics.alice_dev_staging.stg_app__orders` in development. Its `source()` reference crosses catalogs; downstream `ref()` references resolve to models in `analytics`. In `models/staging/app/_models.yml`, test that Silver has one row per order. Bronze records may contain duplicates; the uniqueness check belongs on the prepared staging model: ```yaml version: 2 models: - name: stg_app__orders description: Cleaned orders with one row per order. columns: - name: order_id tests: - not_null - unique ``` Choose a timestamp convention that matches your source. This example assumes `ordered_at` is already expressed in the timezone you use for daily reporting. In `models/intermediate/int_orders__completed.sql`, isolate the completed-order rule: ```sql select order_id, cast(ordered_at as date) as order_date, amount from {{ ref('stg_app__orders') }} where status = 'completed' ``` This step gives other models a shared definition of a completed order. Add intermediate models when they isolate useful logic; a small pipeline can go directly from staging to a mart. ## Publish a gold mart In `models/marts/sales/orders.sql`, publish one row per completed order. Following dbt's [mart guidance](https://docs.getdbt.com/best-practices/how-we-structure/4-marts), keep the mart at the order grain so consumers can calculate daily sales or group by other reporting periods: ```sql select order_id, order_date, amount from {{ ref('int_orders__completed') }} ``` In `models/marts/sales/_models.yml`, document the grain and test it: ```yaml version: 2 models: - name: orders description: One row per completed order, with amounts in the source currency. columns: - name: order_id tests: - not_null - unique - name: order_date description: Calendar date of the completed order. tests: - not_null - name: amount tests: - not_null ``` These are dbt data tests that query the data, not database constraints. Add business-specific checks for cancellations, refunds, currencies, and late-arriving updates before treating this as a financial reporting model. ## Build and verify From the project directory, verify the connection, test the raw input, and build the models: ```bash dbt debug --target dev dbt test --select source:app.orders --target dev dbt build --target dev ``` Source tests query the external system through federation too. Proceed only if they pass. After a successful build, inspect the materialized staging table in Altertable: ```sql select * from analytics.alice_dev_staging.stg_app__orders limit 10; ``` Then calculate daily sales from the development mart. This query aggregates the order-level data into daily metrics: ```sql select order_date, count(*) as completed_orders, sum(amount) as completed_order_amount from analytics.alice_dev_marts.orders group by order_date order by order_date desc; ``` After reviewing the results, run the same source checks and build against production: ```bash dbt test --select source:app.orders --target prod dbt build --target prod ``` Point production queries, insights, and dashboards at `analytics.analytics_prod_marts.orders`. The daily sales query returns dates with completed orders; add a date spine if consumers need rows for days with no orders. ## Schedule dbt in Altertable Altertable can run this project for you on a schedule with a **dbt task** (beta). Each run executes a dbt command from a connected repository and writes models into your chosen Altertable catalog. 1. Commit the dbt project to a [connected knowledge repository](/docs/knowledge/entries.md#dbt-repositories). 2. Open **Tasks**, open the **New task** menu, and choose **dbt task**. 3. Select your **Repository**. Set **Project directory** if `dbt_project.yml` is in a subdirectory; otherwise leave it empty. Set **Git ref** to the branch, tag, or commit you want to run, or leave it empty for the default branch. 4. Configure the command and destination: | Field | Value for this guide | Purpose | | ----------- | -------------------- | ------------------------------------------------------- | | dbt command | `build` | Build the models and run their tests. | | Selector | `*` | Include all models in this example project. | | Target | `prod` | Expose `prod` as `target.name` in the project. | | Catalog | `analytics` | Store the resulting tables and views in Altertable. | | Schema | `analytics_prod` | Set the base schema used by dbt's custom schema naming. | With the project's `+schema` settings, the outputs go into `analytics_prod_staging`, `analytics_prod_intermediate`, and `analytics_prod_marts`. The **Target** name and destination are separate settings: entering `prod` does not select a catalog or schema for you. 5. Choose **Hourly**, **Daily**, **Weekly**, or **Custom cron** in **Schedule**. Schedule times are in UTC. For example, `0 6 * * *` runs every day at 06:00 UTC. Choose a time after your upstream data is ready. 6. Create the task. You can also leave it on a manual schedule, run it from its task page, and inspect the run history before setting up recurring runs. Each scheduled build refreshes the staging tables from the federated source, then builds the dependent intermediate models and marts. For a larger project, narrow **Selector** to the models or tags you want to refresh. The task also supports `run` for model execution without tests and **source freshness** for checking configured source freshness thresholds. Repository knowledge sync and dbt execution are separate: [syncing project metadata](/docs/ingest-data/integrations/dbt.md#sync-project-knowledge) makes definitions and lineage available to Altertable; the dbt task executes the transformations. See [Tasks](/docs/automation/tasks.md) for more about scheduled work.