02 Apr Insights from the Experts on Scaling with dbt Cloud, AutomateDV and Data Vault
Insights from the Experts on Scaling with dbt Cloud, AutomateDV and Data Vault
AutomateDV product manager, Alex Higgs, and dbt Labs solutions architect, Sean McIntyre, shared tips and tricks on building for scale, agility, and reliability with dbt Cloud, AutomateDV, and Data Vault in March 2024.
During the webinar, attendees were encouraged to ask questions. However, there were so many questions that Alex and Sean couldn’t get to all of them. We’ve got you covered, though. Here is the full list of questions asked by attendees, answered below.
Q: I am new to dbt and Data Vault, I am going to work on this combination. Any recommended publications?
A: Explore the official dbt documentation and community forums for guides. For a focus on Data Vault, “Building a Scalable Data Warehouse with Data Vault 2.0” by Dan Linstedt offers a good starting point. Also, the Data Vault User Group Q&A forum is a great place where Data Vault experts regularly answer the community’s questions.
Q: How do you do incremental loads since the data is immutable?
A: Data Vault is an insert-only architecture which handles incremental loads by inserting new records instead of updating or deleting existing ones, maintaining data immutability whilst allowing changes to be tracked against existing records.
Q: When will you support Teradata?
A: There are no current plans for AutomateDV to provide Teradata support. Development roadmap evolves based on customer needs and industry trends.
Q: Could you please explain the difference between daily run and CI job?
A: Daily runs generally include the loading of new data and running quality checks to gauge quality of the operational live system for end-users. CI jobs encompass broader tasks like testing changes to the code and implementation, ensuring that the code works and the expected behaviour is in place. CI tasks can include loading based on the new deployed implementation, though this is usually handled separately.
Q: How was the integration job from GitHub created to run only the tests?
A: Integration job from GitHub to run tests was set up to run just the “dbt test” command, ensuring only tests are executed.
Q: Is there a way to autogenerate models.yml for an existing data mart?
A: There is not yet a built-in feature for generating models.yml files.
Q: What are the advantages to the Data Vault method vs other methods? (Kimball, Inmon, etc.)
A: Data Vault offers scalability, smoother agile project delivery, powerful integration features, and built-in audit and historic point-in-time query capabiltities making it suitable for large, enterprise-level architectures and evolving business needs. It also has a strong business-focused approach, rather than a technical one – it’s intended for Enterprise Data Warehousing. Other architectures seek to do this but don’t operate very well at large scales.
Q: How do you incrementally load the links so that only fresh IDs come in from hubs?
A: Data Vault structures are intended to be loaded in parallel. Whilst links ‘relate’ two or more hubs, there are no direct dependencies (i.e. you do not actually load data from the hub to the link directly). Parallel loading of data into Hubs and Links using dbt with multiple threads allows for incremental loading of Links with only fresh IDs, which supports Data Vault’s built-in parallel load capabilities.
Q: How do we ensure that the hub-to-hub relationship keeps track of the old instance of satellite that has been updated?
A: Hubs cannot be directly related to hubs, this is what Links must be used for. Updating a satellite with additional columns to accommodate updated information while maintaining historical data involves creating a new satellite and deprecatig the old one, which can be managed through dbt’s model versioning capabilities. Like anything else in the development world, we should version control rather than just update and add columns.
Q: How do you keep the history of the links between the hubs?
A: Maintain history of Links between Hubs using Effectivity Satellites to capture changes to the business effectivity of a relationship, ensuring accuracy and consistency over time.
Q: How do you manage different sources with various ingestion intervals within one dbt project?
A: Adhere to Data Vault standards by splitting your satellites by update frequency/cadence (as well as source) and leverage dbt’s flexibility to manage different sources with varying ingestion intervals within a single project.
Q: When would Data Vault be a not-so-good idea to implement?
A: Data Vault may not be ideal in certain scenarios:
- If you have few source systems: One of Data Vault’s core advantages lies in the powerful passive integration capabilities due to the architectural design, making it overkill for single-source (or few sources) systems.
- If your data volume is low: Data Vault is optimised for handling large volumes of data; for smaller datasets, simpler solutions may suffice.
- Limited growth potential: Data Vault is ideal for handling datasets which must scale over time and growing business. Alternative approaches may be more suitable if you are a small business which is not expecting to grow the size or complexity of its data landscape.
- Situations where data lineage is unimportant: If your industry doesn’t require detailed historical data or robust audit capabilities, the added intricacies of Data Vault may not be justified.
Q: I like the dbt test capability; but what if I need to track anomalies and send the corresponding records into a different table?
A: You can handle anomalies by leveraging dbt’s failure handling features:
Store failures: dbt provides functionality to store failed records, enabling you to analyse and address anomalies effectively.
Utilise dbt packages: Explore specialised dbt packages designed for high-level, large-scale anomaly detection and management.
Q: Will AutomateDV support GCP Cloud SQL, BigQuery, and other DB support?
A: Yes, AutomateDV currently supports five platforms:
- SQL Server
- Google BigQuery
- Snowflake
- Postgres
- Databricks
Talk to us about your requirements for alternative platforms.
Conclusion
In conclusion, combining dbt and Data Vault offers a powerful approach to enhance data management and analytics.
Leveraging the Data Vault method alongside tools like dbt Cloud and AutomateDV facilitates efficient and effective data pipeline management, empowering businesses like McDonald’s Nordics to harness the full potential of their data for strategic decision-making and growth. Get started on your AutomateDV and Data Vault journey now by joining our free training.