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.

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: organize models into staging, intermediate, and marts. Medallion names describe the data's role:

The pipeline starts at Bronze. Connect an external catalog 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:

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 before transforming it. Rebuilding a staging table replaces its prior contents.

Before you start

• Create a writable Altertable catalog. This example uses analytics; substitute your catalog name throughout.
• Install and configure the dbt adapter with your lakehouse credentials.
• Check the adapter's 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. 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:

Copy
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:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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, keep its default <target_schema>_<custom_schema> naming:

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:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

Copy
1
2
3
4
5
6
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:

Copy
1
2
3
4
5
6
7
8
9
10
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:

Copy
1
2
3
4
5
6
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, keep the mart at the order grain so consumers can calculate daily sales or group by other reporting periods:

Copy
1
2
3
4
5
select
    order_id,
    order_date,
    amount
from {{ ref('int_orders__completed') }}

In models/marts/sales/_models.yml, document the grain and test it:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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:

Copy
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:

Copy
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:

Copy
1
2
3
4
5
6
7
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:

Copy
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.
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:

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 makes definitions and lineage available to Altertable; the dbt task executes the transformations. See Tasks for more about scheduled work.