v0.8.0 dbt_shopify

Lots of new features ahead!! We've revamped the package to keep up-to-date with new additions to the Shopify connector and feedback from the community.

This release does include 🚨 Breaking Changes 🚨.

Documentation

• Updated README documentation updates for easier navigation and setup of the dbt package (PR #44).
• Created the DECISIONLOG to log discussions and opinionated stances we took in designing the package (PR #43).

Under the Hood

• Ensured Postgres compatibility! (PR #44)
• Addition of the calogica/dbt_expectations package for more robust testing (PR #50).
• Got rid of the shopify__using_order_adjustment, shopify__using_order_line_refund, and shopify__using_refund variables. Instead, the package will automatically create empty versions of the related models until the source refund, order_line_refund, and order_adjustment tables exist in your schema. See DECISIONLOG for more details (Source PR #45, PR #46).

Bug Fixes

• In the intermediate models, we aggregate a lot of metrics and join them together. In previous versions of the package, some order line aggregates were being doubled if their parent order had multiple kinds of transactions, ie a customer used a gift card for part of the purchase (PR #51).

Feature Updates

• New end model alert:
  • The package now includes customer models that are based on email rather than customer_id (PR #45):
    • shopify__customer_emails
    • shopify__customer_email_cohorts
  • shopify__daily_shop (PR #48)
  • shopify__inventory_levels (PR #46)
  • shopify__discounts (PR #47, PR #48)
• Metafield support! This package now supports metafields for the collection, customer, order, product_image, product, product_variant, and shop objects. If enabled (see the README for more details), respective shopify__[object]_metafields models will materialize with all metafields defined within the metafield source table appended to the object. (PR #50)
• shopify_<default_source_table_name>_identifier variables added if an individual source table has a different name than the package expects (PR #38).
• Addition of the shopify_timezone variable, which converts ALL timestamps included in the package (including _fivetran_synced) to a single target timezone in IANA Database format, ie "America/Los_Angeles" (PR #41).
• The declaration of passthrough variables within your root dbt_project.yml has changed (but is backwards compatible). To allow for more flexibility and better tracking of passthrough columns, you will now want to define passthrough columns in the following format (PR #40):

This applies to all passthrough columns within the dbt_shopify_source package and not just the customer_pass_through_columns example. See the README for which models have passthrough columns.

vars:
  customer_pass_through_columns:
    - name: "my_field_to_include" # Required: Name of the field within the source.
      alias: "field_alias" # Optional: If you wish to alias the field within the staging model.
      transform_sql: "cast(field_alias as string)" # Optional: If you wish to define the datatype or apply a light transformation.

• The following source fields have been added to (➕) or removed from (➖) their respective models (PR #39, PR #40):
  • shopify__orders:
    • ➕ total_discounts_set
    • ➕ total_line_items_price_set
    • ➕ total_price_usd
    • ➕ total_price_set
    • ➕ total_tax_set
    • ➕ total_tip_received
    • ➕ is_deleted
    • ➕ app_id
    • ➕ checkout_id
    • ➕ client_details_user_agent
    • ➕ customer_locale
    • ➕ order_status_url
    • ➕ presentment_currency
    • ➕ is_confirmed
  • shopify__customers:
    • ➕ note
    • ➕ lifetime_duration
    • ➕ currency
    • ➕ marketing_consent_state (coalescing of email_marketing_consent_state and deprecated accepts_marketing field)
    • ➕ marketing_opt_in_level (coalescing of email_marketing_consent_opt_in_level and deprecated marketing_opt_in_level field)
    • ➕ marketing_consent_updated_at (coalescing of email_marketing_consent_consent_updated_at and deprecated accepts_marketing_updated_at field)
    • ➖ accepts_marketing/has_accepted_marketing
    • ➖ accepts_marketing_updated_at
    • ➖ marketing_opt_in_level
  • shopify__order_lines:
    • ➕ pre_tax_price_set
    • ➕ price_set
    • ➕ tax_code
    • ➕ total_discount_set
    • ➕ variant_title
    • ➕ variant_inventory_management
    • ➕ properties
    • ( ) is_requiring_shipping is renamed to is_shipping_required
  • shopify__products:
    • ➕ status
• The following transformed fields have been added to their respective models:
  • shopify__orders
    • shipping_discount_amount (PR #47)
    • percentage_calc_discount_amount (PR #47)
    • fixed_amount_discount_amount (PR #47)
    • count_discount_codes_applied (PR #47)
    • order_tags (PR #49)
    • order_url_tags (PR #49)
    • number_of_fulfillments (PR #49)
    • fulfilmment_services (PR #49)
    • tracking_companies (PR #49)
    • tracking_numbers (PR #49)
  • shopify__products
    • collections (PR #46)
    • tags (PR #46)
    • count_variants (PR #46)
    • has_product_image (PR #46)
    • quantity_sold renamed to total_quantity_sold (PR #49)
    • avg_quantity_per_order_line (PR #49)
    • product_total_discount (PR #49)
    • product_avg_discount_per_order_line (PR #49)
    • product_total_tax (PR #49)
    • product_avg_tax_per_order_line (PR #49)
  • shopify__customers (PR #49)
    • lifetime_abandoned_checkouts
    • customer_tags
    • average_order_value renamed to avg_order_value
    • lifetime_total_amount renamed to lifetime_total_net
    • avg_quantity_per_order
    • lifetime_total_tax
    • avg_tax_per_order
    • lifetime_total_discount
    • avg_discount_per_order
    • lifetime_total_shipping
    • avg_shipping_per_order
    • lifetime_total_shipping_with_discounts
    • lifetime_total_shipping_tax
    • avg_shipping_tax_per_order
    • avg_shipping_with_discounts_per_order
  • shopify__order_lines (PR #49)
    • restock_types
    • order_line_tax
  • shopify__transactions (PR #49)
    • payment_method
    • parent_kind
    • parent_created_timestamp
    • parent_amount
    • parent_status

dbt_shopify v0.7.0

🚨 Breaking Changes 🚨:

PR #40 includes the following breaking changes:

• Dispatch update for dbt-utils to dbt-core cross-db macros migration. Specifically {{ dbt_utils.<macro> }} have been updated to {{ dbt.<macro> }} for the below macros:
  • any_value
  • bool_or
  • cast_bool_to_text
  • concat
  • date_trunc
  • dateadd
  • datediff
  • escape_single_quotes
  • except
  • hash
  • intersect
  • last_day
  • length
  • listagg
  • position
  • replace
  • right
  • safe_cast
  • split_part
  • string_literal
  • type_bigint
  • type_float
  • type_int
  • type_numeric
  • type_string
  • type_timestamp
  • array_append
  • array_concat
  • array_construct
• For current_timestamp and current_timestamp_in_utc macros, the dispatch AND the macro names have been updated to the below, respectively:
  • dbt.current_timestamp_backcompat
  • dbt.current_timestamp_in_utc_backcompat
• Dependencies on fivetran/fivetran_utils have been upgraded, previously [">=0.3.0", "<0.4.0"] now [">=0.4.0", "<0.5.0"].

shopify 0.6.0

🎉 dbt v1.0.0 Compatibility 🎉

🚨 Breaking Changes 🚨

• Adjusts the require-dbt-version to now be within the range [">=1.0.0", "<2.0.0"]. Additionally, the package has been updated for dbt v1.0.0 compatibility. If you are using a dbt version <1.0.0, you will need to upgrade in order to leverage the latest version of the package.

  • For help upgrading your package, I recommend reviewing this GitHub repo's Release Notes on what changes have been implemented since your last upgrade.
  • For help upgrading your dbt project to dbt v1.0.0, I recommend reviewing dbt-labs upgrading to 1.0.0 docs for more details on what changes must be made.

• Upgrades the package dependency to refer to the latest dbt_shopify_source. Additionally, the latest dbt_shopify_source package has a dependency on the latest dbt_fivetran_utils. Further, the latest dbt_fivetran_utils package also has a dependency on dbt_utils [">=0.8.0", "<0.9.0"].

  • Please note, if you are installing a version of dbt_utils in your packages.yml that is not in the range above then you will encounter a package dependency error.

• The union_schemas and union_databases variables have been replaced with shopify_union_schemas and shopify_union_databases respectively. This allows for multiple packages with the union ability to be used and not locked to a single variable that is used across packages.

README Update

Happy Monday! 🌮

This extra-tiny release just adds instructions regarding passthrough columns to the README. Originally, these guidelines only existed in the source package.

Ability to Disable Order Adjustment and Refunds Models

Happy Monday!

This release includes the following added features to the Fivetran Shopify Source dbt package:

• Ability to disable the order_adjustment, order_line_refund, and refund staging models references if you do not currently sync them by leveraging the variables highlighted within the README. PR #24
• Added order_line_refund_id to the intermediate shppify__orders__order_refunds model. PR #23

Shout out to @lizdeika for opening a PR to add the order_line_refund_id 🎉

dbt 0.20.0 Compatibility

🎉 dbt 0.20.0 Compatibility 🎉

🚨 This is a breaking change! 🚨 dbt v0.20.0 or greater is required for this release. If you are not ready to upgrade, consider using a previous release of this package.

Additional considerations when upgrading to this package:

• This package utilizes the latest v0.7.x release of the dbt-labls/dbt_utils package. If your project also utilizes a version of the fishtown-analytics/dbt_utils package then you will receive a duplicate package error. If this is the case you will need to consider upgrading your other packages to be compatible with this update or use a previous release of this package.
• Similar to the above point, all previous Fivetran dbt packages utilize the fishtown-analytics/dbt_utils package and you will need to upgrade all Fivetran dbt packages to the latest dbt 0.20.0 compatibility releases in order for your packages to run without package conflicts.
• For Databricks users this compatibility is currently only available for >=dbt 0.20.1rc1. If you want more information on upgrading your dbt-spark version, you can refer to the dbt-spark release for further details.

Null Exchange Rate Bug Fix

🐞 Bug Fix 🐞

Happy Friday! This release of the Shopify package includes the following non-breaking changes:

• Bug Fix that correctly parses the shopify__transactions exchange rate value within redshift when the exchange rate object is null. Issue #20

README Typo Fix

Happy Monday!

This release includes the following non-breaking changes:

• Typo fix within the README to reference the correct union_schemas variable. PR #18

Union Source Data Functionality (Breaking Change)

🚨 BREAKING CHANGE 🚨 Unioning Shopify source data has been a frequent request from package users. This release incorporates just that functionality!

• The ability to union Shopify source data using this package. This is done by utilizing the union_schema and union_databases variables. A user can add the schemas they would like to union or the databases they would like to union (must be one or the other) together and produce unioned results downstream.

Thanks so much to @DylanBaker for his stellar work on getting this functionality to work in this package! 🎉

Features Updates + custom schema (breaking change 🚨)

This release incorporates the following new features:

• Databricks and Postgres compatibility
• Custom schema integration (breaking change 🚨)
• Addition of the shopify__transactions table which includes all transactions within shopify and an exchange rate calculation applied
• Overhauled shopify__orders, shopify__order_lines, and shopify__customer_cohorts tables to include refund calculations towards the order totals.
• Removed duplicate customer total orders and total amount fields from the shopify__customers final model (breaking change 🚨)