Amazon Selling Partner dbt Package

This dbt package transforms data from Fivetran's Amazon Selling Partner connector into analytics-ready tables.

Resources

• Number of materialized models¹: 31
• Connector documentation
  • Amazon Selling Partner connector documentation
  • Amazon Selling Partner ERD
• dbt package documentation
  • GitHub repository
  • dbt Docs
  • DAG
  • Changelog
• dbt Core™ supported versions
  • >=1.3.0, <3.0.0

What does this dbt package do?

This package enables you to transform core Seller Central object tables into analytics-ready models and enrich orders, order items, and listed items with catalog information. It creates enriched models with metrics focused on sales aggregates and current inventory levels.

This package is currently not compatible with any Vendor Central modules. If you would like to see Vendor Central compatibility (or the use of any other Seller Central modules), please open up a Feature Request.

Output schema

Final output tables are generated in the following target schema:

<your_database>.<connector/schema_name>_amazon_selling_partner

Final output tables

By default, this package materializes the following final tables:

Table	Description
amazon_selling_partner__orders	Tracks all orders placed on Amazon with payment methods, order totals, and item counts to analyze sales performance, order trends, and revenue patterns for your seller account. Example Analytics Questions: What are daily or monthly sales trends by order volume and total revenue? Which payment methods are customers using most frequently for their purchases? How do order values and fulfillment channels vary across different marketplaces?
amazon_selling_partner__order_items	Provides line-item details for every product sold on Amazon, enriched with catalog information including ASIN, SKU, pricing, and quantities to analyze product performance at the item level. Example Analytics Questions: Which products (by ASIN or SKU) generate the highest sales volume and revenue? How do item prices, quantities ordered, and quantities shipped vary across orders? What is the average order item value and discount rate by product?
amazon_selling_partner__item_inventory	Shows current inventory levels for all catalog items with product details, dimensions, and sales rankings to manage stock levels and optimize inventory across your Amazon catalog. Example Analytics Questions: Which products have low inventory levels that need restocking soon? How do current inventory levels correlate with sales rank trends? What products consume the most warehouse space based on item dimensions?

¹ Each Quickstart transformation job run materializes these models if all components of this data model are enabled. This count includes all staging, intermediate, and final models materialized as view, table, or incremental.

---

Prerequisites

To use this dbt package, you must have the following:

• At least one Fivetran Amazon Selling Partner connection syncing data into your destination.
• A BigQuery, Snowflake, Redshift, Databricks, or PostgreSQL destination.

How do I use the dbt package?

You can either add this dbt package in the Fivetran dashboard or import it into your dbt project:

• To add the package in the Fivetran dashboard, follow our Quickstart guide.
• To add the package to your dbt project, follow the setup instructions in the dbt package's README file to use this package.

Install the package

Include the following Amazon Selling Partner package version in your packages.yml file:

TIP: Check dbt Hub for the latest installation instructions or read the dbt docs for more information on installing packages.

packages:
  - package: fivetran/amazon_selling_partner
    version: [">=0.6.0", "<0.7.0"] # we recommend using ranges to capture non-breaking changes automatically

Define database and schema variables

Option A: Single connection

By default, this package runs using your destination and the amazon_selling_partner schema. If this is not where your Amazon Selling Partner data is (for example, if your Amazon Selling Partner schema is named amazon_selling_partner_fivetran), add the following configuration to your root dbt_project.yml file:

vars:
    amazon_selling_partner_database: your_destination_name
    amazon_selling_partner_schema: your_schema_name

Option B: Union multiple connections

If you have multiple Amazon Selling Partner connections in Fivetran and would like to use this package on all of them simultaneously, we have provided functionality to do so. For each source table, the package will union all of the data together and pass the unioned table into the transformations. The source_relation column in each model indicates the origin of each record.

To use this functionality, you will need to set the amazon_selling_partner_sources variable in your root dbt_project.yml file:

# dbt_project.yml

vars:
  amazon_selling_partner:
    amazon_selling_partner_sources:
      - database: connection_1_destination_name # Required
        schema: connection_1_schema_name # Required
        name: connection_1_source_name # Required only if following the step in the following subsection

      - database: connection_2_destination_name
        schema: connection_2_schema_name
        name: connection_2_source_name

Optional: Incorporate unioned sources into DAG

If you use Fivetran Transformations for dbt Core™ and are unioning multiple Amazon Selling Partner connections, you can define your sources in a property .yml file, using this as a template. Set the variable has_defined_sources: true under the Amazon Selling Partner namespace in your dbt_project.yml. Otherwise, your Amazon Selling Partner connections won't appear in your DAG. See the union_connections macro documentation for full configuration details.

Enable/Disable models for unused modules

By default, this package transforms tables from the ORDERS, CATALOG, and FBA Seller Central modules described in this ERD. The package currently uses the following tables from each module:

ORDERS module:

• orders
• order_item
• payment_method_detail_item
• order_item_promotion_id

CATALOG module:

• item_summary
• item_relationship
• item_product_type
• item_identifier
• item_display_group_sales_rank
• item_classification_sales_rank
• item_dimension
• item_image

FBA module

• fba_inventory_summary
• fba_inventory_researching_quantity_entry

If you do not have a module enabled in your Amazon Selling Partner connection(s), you may still run the package successfully by configuring the appropriate amazon_selling_partner__using_<module_name>_module variable. To do so, add the following configuration to your dbt_project.yml:

vars:
    amazon_selling_partner__using_orders_module: False # default = True. Disables materialization of the amazon_selling_partner__orders and amazon_selling_partner__order_items models
    amazon_selling_partner__using_catalog_module: False # default = True. Disables materialization of the amazon_selling_partner__item_inventory model and removes item columns from amazon_selling_partner__order_items
    amazon_selling_partner__using_fba_module: False # default = True. Removes inventory columns from the amazon_selling_partner__item_inventory model

Quickstart

For users running the package through Fivetran Quickstart, these variables are dynamically assigned based on the presence of core tables in each module:

• amazon_selling_partner__using_orders_module is disabled if the orders or order_item source tables are missing.
• amazon_selling_partner__using_catalog_module is disabled if the item_summary source table is missing.
• amazon_selling_partner__using_fba_module is disabled if the fba_inventory_summary source table is missing.

If a non-core table is missing, the package will create an empty staging model with all the proper columns and data types so as to not disrupt downstream transformations.

(Optional) Additional configurations

Changing the Build Schema

By default this package will build the Amazon Selling Partner staging models within a schema titled (<target_schema> + _stg_amazon_selling_partner) and the Amazon Selling Partner final models within a schema titled (<target_schema> + _amazon_selling_partner) in your target database. If this is not where you want your modeled qualtrics data to be written to, add the following configuration to your dbt_project.yml file:

# dbt_project.yml

models:
  amazon_selling_partner:
    +schema: my_new_schema_name # leave blank for just the target_schema
    staging:
        +schema: my_new_schema_name # leave blank for just the target_schema

Change the source table references (available for single-connection runs only)

If an individual source table has a different name than the package expects, add the table name as it appears in your destination to the respective variable:

IMPORTANT: See this project's dbt_project.yml variable declarations to see the expected names.

# dbt_project.yml

vars:
    amazon_selling_partner_<default_source_table_name>_identifier: your_table_name 

Source casing for case-sensitive destinations

By default, the package applies case-insensitive comparisons when resolving source_relation values. If your destination is case-sensitive and you want downstream transformations to respect the exact casing of your source database and schema names, set the following variable:

vars:
    fivetran_using_source_casing: true

(Optional) Orchestrate your models with Fivetran Transformations for dbt Core™

Expand for details

Fivetran offers the ability for you to orchestrate your dbt project through Fivetran Transformations for dbt Core™. Learn how to set up your project for orchestration through Fivetran in our Transformations for dbt Core setup guides.

Does this package have dependencies?

This dbt package is dependent on the following dbt packages. These dependencies are installed by default within this package. For more information on the following packages, refer to the dbt hub site.

IMPORTANT: If you have any of these dependent packages in your own packages.yml file, we highly recommend that you remove them from your root packages.yml to avoid package version conflicts.

packages:
    - package: fivetran/fivetran_utils
      version: [">=0.4.0", "<0.5.0"]

    - package: dbt-labs/dbt_utils
      version: [">=1.0.0", "<2.0.0"]

How is this package maintained and can I contribute?

Package Maintenance

The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.

Contributions

A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions.

We highly encourage and welcome contributions to this package. Learn how to contribute to a package in dbt's Contributing to an external dbt package article.

Are there any resources available?

• If you have questions or want to reach out for help, refer to the GitHub Issue section to find the right avenue of support for you.
• If you want to provide feedback to the dbt package team at Fivetran or want to request a new dbt package, fill out our Feedback Form.