SQL Analytics Guide for Reproducible Pipelines

Modeling philosophy

Published
February 10, 2024
Author
Reading time
2 mins read
Difficulty
Intermediate
Saved articles

Online at https://diogoribeiro7.github.io/analytics-blog-jekyll/tutorials/2024/02/10/sql-analytics-guide/

Modeling philosophy

  • Separate staging, intermediate, and mart layers to isolate concerns.
  • Document every model with description blocks to power dbt docs.
  • Pair assertions with automated tests using unique, not_null, and relationships.

Example staging model

1
2
3
4
5
6
7
8
9
10
11
12
13
14

with source as (
    select *
    from {{ source('stripe', 'charges') }}
),
renamed as (
    select
        id as charge_id,
        customer_id,
        amount / 100.0 as amount_eur,
        created::date as charge_date,
        status
    from source
)
select * from renamed

Building aggregate marts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

with charges as (
    select * from {{ ref('stg_stripe_charges') }}
),
customers as (
    select * from {{ ref('dim_customers') }}
)
select
    c.customer_id,
    c.segment,
    date_trunc('month', ch.charge_date) as charge_month,
    sum(ch.amount_eur) as monthly_revenue,
    count_if(ch.status = 'failed') as failed_payments
from charges ch
join customers c using (customer_id)
group by 1, 2, 3

Testing critical assumptions

1
2
3
4
5
6
7
8
9
10
11
12

version: 2
models:
  - name: fct_billing_health
    description: "Monthly revenue and failure counts per customer."
    tests:
      - unique:
          column_name: "customer_id || '-' || charge_month"
      - not_null:
          column_name: monthly_revenue
      - relationships:
          to: ref('dim_customers')
          field: customer_id

Automation tips

  1. Schedule CI builds with dbt-cloud or GitHub Actions to run on each PR.
  2. Export lineage metadata to the technical search index for discoverability.
  3. Snapshot slowly changing dimensions using dbt snapshot for audit trails.

Download the project template and explore the generated docs site to navigate dependencies visually.

Your private highlights

Kept in this browser only and never sent anywhere. These are your notes, not comments. With text selected, Alt+Shift+H highlights it and Alt+Shift+N adds a note.

Select a passage of the article to highlight it.

    Your reading data

    • dbt docs site preview

      Explore the interactive lineage graph generated from the project.

      Launch demo

    © 2024 Diogo Ribeiro. Text and figures under CC BY 4.0.

    How to cite

    Use the quick export buttons to save citations for reference managers or copy the formatted text directly.

    Diogo Ribeiro (2024). SQL Analytics Guide for Reproducible Pipelines. DataLog | Data Science & Research Theme. https://diogoribeiro7.github.io/analytics-blog-jekyll/tutorials/2024/02/10/sql-analytics-guide/.

    BibTeX

    RIS

    EndNote

    Open science & reproducibility badges

    These badges highlight the transparency practices applied to this work. Hover or focus on each badge to learn more about the criteria.

    • Open Data Dataset and code repository published with permissive license. Public repository, DOI issued, README with reproduction steps.
    • Reproducible Workflow Containerized environment and automated tests provided. Continuous integration pipeline with reproducibility checks.
    • Transparent Peer Review Peer review reports archived with DOI and linked to article. Open peer review statement and archived reports on Zenodo.

    Related posts